乐趣区

关于mysql-8.0:技术分享-可能是目前最全的-MySQL-80-新特性解读上

jiezi

作者：马文斌

MySQL 爱好者, 任职于蓝月亮 (中国) 有限公司。

本文起源：原创投稿

* 爱可生开源社区出品，原创内容未经受权不得随便应用，转载请分割小编并注明起源。

一、性能加强

1.1- 所有零碎表更换为 InnoDB 引擎

零碎表全副换成事务型的 innodb 表，默认的 MySQL 实例将不蕴含任何 MyISAM 表，除非手动创立 MyISAM 表。

1.2-DDL 原子化

InnoDB 表的 DDL 反对事务完整性，要么胜利要么回滚，将 DDL 操作回滚日志写入到 data dictionary 数据字典表 mysql.innodb_ddl_log 中用于回滚操作，该表是暗藏的表，通过 show tables 无奈看到。通过设置参数，可将 ddl 操作日志打印输出到 mysql 谬误日志中。

mysql> set global log_error_verbosity=3;
mysql> set global innodb_print_ddl_logs=1;

1.3-DDL 秒加列

只有在 MySQL 8.0.12 以上的版本才反对

mysql> show create table sbtest1;
 CREATE TABLE `sbtest1` (
  `id` int NOT NULL AUTO_INCREMENT,
  `k` int NOT NULL DEFAULT '0',
  `c` char(120) NOT NULL DEFAULT '',
  `pad` char(60) NOT NULL DEFAULT '',
  `d` int NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`),
  KEY `k_1` (`k`)
) ENGINE=InnoDB AUTO_INCREMENT=1000001 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci 
1 row in set (0.00 sec)

mysql> alter table sbtest1 drop column d ;
Query OK, 0 rows affected (0.05 sec)
Records: 0  Duplicates: 0  Warnings: 0
mysql> 
mysql> insert into sbtest1(k,c,pad) select k,c,pad from sbtest1;
Query OK, 1000000 rows affected (19.61 sec)
Records: 1000000  Duplicates: 0  Warnings: 0

mysql> insert into sbtest1(k,c,pad) select k,c,pad from sbtest1;
Query OK, 2000000 rows affected (38.25 sec)
Records: 2000000  Duplicates: 0  Warnings: 0

mysql> insert into sbtest1(k,c,pad) select k,c,pad from sbtest1;
Query OK, 4000000 rows affected (1 min 14.51 sec)
Records: 4000000  Duplicates: 0  Warnings: 0

mysql> select count(*) from sbtest1;
+----------+
| count(*) |
+----------+
|  8000000 |
+----------+
1 row in set (0.31 sec)

mysql> alter table sbtest1 add column d int not null default 0;
Query OK, 0 rows affected (1.22 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> alter table sbtest1 add column e int not null default 0;
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0

1.4- 专用表表达式（CTE：Common Table Expression）

CTE(Common Table Expression)能够认为是派生表 (derived table) 的代替，在肯定水平上，CTE 简化了简单的 join 查问和子查问，另外 CTE 能够很不便地实现递归查问，进步了 SQL 的可读性和执行性能。CTE 是 ANSI SQL 99 规范的一部分，在 MySQL 8.0.1 版本被引入。

1.4.1-CTE 劣势

  • 查问语句的可读性更好
  • 在一个查问中，能够被援用屡次
  • 可能链接多个 CTE
  • 可能创立递归查问
  • 可能进步 SQL 执行性能
  • 可能无效地代替视图

1.5- 默认字符集由 latin1 变为 utf8mb4

在 8.0 版本之前，默认字符集为 latin1，utf8 指向的是 utf8mb3，8.0 版本默认字符集为 utf8mb4，utf8 默认指向的也是 utf8mb4。

1.6-Clone 插件

MySQL 8.0 clone 插件提供从一个实例克隆出另外一个实例的性能，克隆性能提供了更无效的形式来疾速创立 MySQL 实例，搭建主从复制和组复制。

1.7- 资源组

MySQL 8.0 新增了一个资源组性能，用于调控线程优先级以及绑定 CPU 核。MySQL 用户须要有 RESOURCE_GROUP_ADMIN 权限能力创立、批改、删除资源组。在 Linux 环境下，MySQL 过程须要有 CAP_SYS_NICE 权限能力应用资源组残缺性能。

1.8- 角色治理

角色能够认为是一些权限的汇合，为用户赋予对立的角色，权限的批改间接通过角色来进行，无需为每个用户独自受权。

# 创立角色
mysql>create role role_test;
QueryOK, 0rows affected (0.03sec)
 
# 给角色授予权限
mysql>grant select on db.*to 'role_test';
QueryOK, 0rows affected (0.10sec)
 
# 创立用户
mysql>create user 'read_user'@'%'identified by '123456';
QueryOK, 0rows affected (0.09sec)
 
# 给用户赋予角色
mysql>grant 'role_test'to 'read_user'@'%';
QueryOK, 0rows affected (0.02sec)
 
# 给角色 role_test 减少 insert 权限
mysql>grant insert on db.*to 'role_test';
QueryOK, 0rows affected (0.08sec)
 
# 给角色 role_test 删除 insert 权限
mysql>revoke insert on db.*from 'role_test';
QueryOK, 0rows affected (0.10sec)
 
# 查看默认角色信息
mysql>select * from mysql.default_roles;

# 查看角色与用户关系
mysql>select * from mysql.role_edges;

# 删除角色
mysql>drop role role_test;

1.9- 多值索引

从 MySQL 8.0.17 开始，InnoDB 反对创立多值索引，这是在存储值数组的 JSON 列上定义的二级索引，单个数据记录能够有多个索引记录。这样的索引应用要害局部定义，例如 CAST(data->’$.zipcode’AS UNSIGNED ARRAY)。MySQL 优化器主动应用多值索引来进行适合的查问，能够在 EXPLAIN 的输入中查看。

1.10- 函数索引

MySQL 8.0.13 以及更高版本反对函数索引（functional key parts），也就是将表达式的值作为索引的内容，而不是列值或列值前缀。将函数作为索引键能够用于索引那些没有在表中间接存储的内容。

其实 MySQL5.7 中推出了虚构列的性能，而 MySQL8.0 的函数索引也是根据虚构列来实现的。

  • 只有那些可能用于计算列的函数才可能用于创立函数索引。
  • 函数索引中不容许应用子查问、参数、变量、存储函数以及自定义函数。
  • SPATIAL 索引和 FULLTEXT 索引不反对函数索引。

1.11- 不可见索引

在 MySQL 5.7 版本及之前，只能通过显式的形式删除索引。此时，如果发现删除索引后呈现谬误，又只能通过显式创立索引的形式将删除的索引创立回来。如果数据表中的数据量十分大，或者数据表自身比拟大，这种操作就会耗费零碎过多的资源，操作老本十分高。

从 MySQL 8.x 开始反对暗藏索引（invisible indexes），只须要将待删除的索引设置为暗藏索引，使查问优化器不再应用这个索引（即便应用 force index（强制应用索引），优化器也不会应用该索引），确认将索引设置为暗藏索引后零碎不受任何响应，就能够彻底删除索引。这种通过先将索引设置为暗藏索引，再删除索引的形式就是软删除。

mysql> show create table t1\G
*************************** 1. row ***************************
       Table: t1
Create Table: CREATE TABLE `t1` (
  `c1` int DEFAULT NULL,
  `c2` int DEFAULT NULL,
  `create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  KEY `idx_c1` (`c1`) /*!80000 INVISIBLE */
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.00 sec)

# 不可见的状况下是不会走索引的，key=null
mysql> explain select * from t1 where c1=3;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | t1    | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    5 |    20.00 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

# 设置为索引可见，mysql> alter table t1 alter index idx_c1 visible;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show create table t1\G
*************************** 1. row ***************************
       Table: t1
Create Table: CREATE TABLE `t1` (
  `c1` int DEFAULT NULL,
  `c2` int DEFAULT NULL,
  `create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  KEY `idx_c1` (`c1`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.00 sec)

# 能够走索引，key=idx_c1
mysql> explain select * from t1 where c1=3;
+----+-------------+-------+------------+------+---------------+--------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key    | key_len | ref   | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+--------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | t1    | NULL       | ref  | idx_c1        | idx_c1 | 5       | const |    1 |   100.00 | NULL  |
+----+-------------+-------+------------+------+---------------+--------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

1.12- 新增降序索引

MySQL 在语法上很早就曾经反对降序索引，但实际上创立的依然是升序索引。从 8.0 开始，理论创立的为降序索引。

1.13-SET_VAR 语法

在 sql 语法中减少 SET_VAR 语法，动静调整局部参数，有利于晋升语句性能。

select /*+ SET_VAR(sort_buffer_size = 16M) */ id from test order id ;
insert /*+ SET_VAR(foreign_key_checks=OFF) */ into test(name) values(1);

1.14- 参数批改长久化

MySQL 8.0 版本反对在线批改全局参数并长久化，通过加上 PERSIST 关键字，能够将批改的参数长久化到新的配置文件（mysqld-auto.cnf）中，重启 MySQL 时，能够从该配置文件获取到最新的配置参数。

例如执行：

set PERSIST expire_logs_days=10 ;

零碎会在数据目录下生成一个蕴含 json 格局的 mysqld-auto.cnf 的文件，格式化后如下所示，当 my.cnf 和 mysqld-auto.cnf 同时存在时，后者具备更高优先级。

1.15-innodb select for update 跳过锁期待

select … for update，select … for share(8.0 新增语法) 增加 NOWAIT、SKIP LOCKED 语法，跳过锁期待，或者跳过锁定。

在 5.7 及之前的版本，select…for update，如果获取不到锁，会始终期待，直到 innodb_lock_wait_timeout 超时。

在 8.0 版本，通过增加 nowait，skip locked 语法，可能立刻返回。如果查问的行曾经加锁，那么 nowait 会立刻报错返回，而 skip locked 也会立刻返回，只是返回的后果中不蕴含被锁定的行。

1.16-group by 不再隐式排序

目标是为了兼容 sql 的规范语法，不便迁徙

mysql 5.7

mysql> select count(*),age from t5 group by age;
+----------+------+
| count(*) | age  |
+----------+------+
|        1 |   25 |
|        1 |   29 |
|        1 |   32 |
|        1 |   33 |
|        1 |   35 |
+----------+------+
5 rows in set (0.00 sec)

mysql 8.0

mysql> select count(*),age from t5 group by age;
+----------+------+
| count(*) | age  |
+----------+------+
|        1 |   25 |
|        1 |   32 |
|        1 |   35 |
|        1 |   29 |
|        1 |   33 |
+----------+------+
5 rows in set (0.00 sec)

能够看到，MySQL5.7 在 group by 中对分组字段进行了隐式排序，而 MySQL8.0 勾销了隐式排序。如果要增加排序的话，须要显示减少，比方 select count(*),age from t5 group by age order by age;

1.17- 自增变量长久化

在 8.0 之前的版本，自增值是保留在内存中, 自增主键 AUTO_INCREMENT 的值如果大于 max(primary key)+1，在 MySQL 重启后，会重置 AUTO_INCREMENT=max(primary key)+1。这种景象在某些状况下会导致业务主键抵触或者其余难以发现的问题。自增主键重启重置的问题很早就被发现(https://bugs.mysql.com/bug.php?id=199)，始终到 8.0 才被解决。8.0 版本将会对 AUTO_INCREMENT 值进行长久化，MySQL 重启后，该值将不会扭转。

8.0 开始，以后最大的自增计数器每当发生变化，值会被写入 redo log 中，并在每个检查点时候保留到 private system table 中。这一变动，对 AUTO_INCREMENT 值进行长久化,MySQL 重启后，该值将不会扭转。

  • MySQL server 重启后不再勾销 AUTO_INCREMENT = N 表选项的成果。如果将自增计数器初始化为特定值，或者将主动递增计数器值更改为更大的值，新的值被长久化，即便服务器重启。
  • 在回滚操作之后立刻重启服务器将不再导致从新应用调配给回滚事务的主动递增值。
  • 如果将 AUTO_INCREMEN 列值批改为大于以后最大自增值 (例如，在更新操作中) 的值，则新值将被长久化，随后的插入操作将从新的、更大的值开始调配主动增量值。
-- 确认下本人的版本
select VERSION()
/*
VERSION() |
----------+
5.7.26-log|
*/

-- 创立表
create table testincr(
    id int auto_increment primary key,
    name varchar(50)
)

-- 插入数据
insert into testincr(name) values
    ('刘备'),
    ('关羽'),
    ('张飞');

-- 查看以后的自增量
select t.`AUTO_INCREMENT` from information_schema.TABLES t where TABLE_NAME ='testincr'
/* 
AUTO_INCREMENT|
--------------+
             4|
*/
-- 更改列值
update testincr set id=4 where id=3

-- 查看当初的表值
/*
id|name|
--+----+
 1| 刘备  |
 2| 关羽  |
 4| 张飞  |
*/
-- 插入新值 问题呈现
insert into testincr(name) values('赵云');
/*
SQL 谬误 [1062] [23000]: Duplicate entry '4' for key 'PRIMARY'
*/

-- 如果咱们再次插入，它就是失常的，因为 id 到 5 了。。。mysql> insert into testincr(name) values('赵云');
Query OK, 1 row affected (0.01 sec)

1.18-binlog 日志事务压缩

MySQL 8.0.20 版本减少了 binlog 日志事务压缩性能，将事务信息应用 zstd 算法进行压缩，而后再写入 binlog 日志文件，这种被压缩后的事务信息，在 binlog 中对应为一个新的 event 类型，叫做 Transaction_payload_event。

1.19- 分区表改良

MySQL 8.0 对于分区表性能进行了较大的批改，在 8.0 之前，分区表在 Server 层实现，反对多种存储引擎，从 8.0 版本开始，分区表性能移到引擎层实现，目前 MySQL 8.0 版本只有 InnoDB 存储引擎反对分区表。

1.20- 主动参数设置

将 innodb_dedicated_server 开启的时候，它能够主动的调整上面这四个参数的值：

innodb_buffer_pool_size 总内存大小
innodb_log_file_size redo 文件大小
innodb_log_files_in_group redo 文件数量
innodb_flush_method 数据刷新办法

只需将 innodb_dedicated_server = ON 设置好，下面四个参数会主动调整，解决非专业人员装置数据库后默认初始化数据库参数默认值偏低的问题，让 MySQL 自适应的调整下面四个参数，前提是服务器是专用来给 MySQL 数据库的，如果还有其他软件或者资源或者多实例 MySQL 应用，不倡议开启该参数，本文以 MySQL8.0.19 为例。

那么依照什么规定调整呢？MySQL 官网给出了相干参数调整规定如下：

1. innodb_buffer_pool_size 主动调整规定：

专用服务器内存大小 buffer_pool_size 大小
小于 1G 128MB（MySQL 缺省值）
1G to 4G OS 内存 *0.5
大于 4G OS 内存 *0.75

2. innodb_log_file_size 主动调整规定：

buffer_pool_size 大小 log_file_size 大小
小于 8G 512MB
8G to 128G 1024MB
大于 128G 2048MB

3. innodb_log_files_in_group 主动调整规定：
（innodb_log_files_in_group 值就是 log file 的数量）

buffer_pool_size 大小 log file 数量
小于 8G ROUND(buffer pool size)
8G to 128G ROUND(buffer pool size * 0.75)
大于 128G 64

阐明：如果 ROUND(buffer pool size)值小于 2GB，那么 innodb_log_files_in_group 会强制设置为 2。

4. innodb_flush_method 主动调整规定：

该参数调整规定间接援用官网文档的解释：The flush method is set to O_DIRECT_NO_FSYNC when innodb_dedicated_server is enabled. If the O_DIRECT_NO_FSYNC setting is not available, the default innodb_flush_method setting is used.
如果零碎容许设置为 O_DIRECT_NO_FSYNC；如果零碎不容许，则设置为 InnoDB 默认的 Flush method。

1.20.1- 自适应参数的益处：

  • 主动调整，简略不便，让 DBA 更省心
  • 自带优化光环：没有该参数前，innodb_buffer_pool_size 和 log_file_size 默认装置初始化后只有 128M 和 48M，这对于一个生产环境来说是远远不够的，通常 DBA 都会手工依据服务器的硬件配置来调整优化，该参数呈现后基本上能够解决入门人员装置 MySQL 后的性能问题。
  • 云厂商，虚拟化等动静资源扩容或者缩容后，不用再操心 MySQL 参数配置问题。

1.20.2- 自适应参数的限度：

  • 专门给 MySQL 独立应用的服务器
  • 单机多实例的状况不实用
  • 服务器上还跑着其他软件或利用的状况不实用

1.21- 窗口函数

从 MySQL 8.0 开始，新增了一个叫窗口函数的概念。

什么叫窗口?

它能够了解为记录汇合，窗口函数也就是在满足某种条件的记录汇合上执行的非凡函数。对于每条记录都要在此窗口内执行函数，有的函数随着记录不同，窗口大小都是固定的，这种属于动态窗口；有的函数则相同，不同的记录对应着不同的窗口，这种动态变化的窗口叫滑动窗口。

它能够用来实现若干新的查问形式。窗口函数与 SUM()、COUNT() 这种聚合函数相似，但它不会将多行查问后果合并为一行，而是将后果放回多行当中。即窗口函数不须要 GROUP BY。

窗口函数内容太多，前期我会专门写一篇文章介绍窗口函数

1.22- 索引损坏标记

当遇到索引树损坏时，InnoDB 会在 redo 日志中写入一个损坏标记，这会使损坏标记平安解体。InnoDB 还将内存损坏标记数据写入每个检查点的公有零碎表中。

在复原的过程中，InnoDB 会从这两个地位读取损坏标记，并合并后果，而后将内存中的表和索引对象标记为损坏。

1.23-InnoDB memcached 插件

InnoDB memcached 插件反对批量 get 操作（在一个 memcached 查问中获取多个键值对）和范畴查问。缩小客户端和服务器之间的通信流量，在单个 memcached 查问中获取多个键、值对的性能能够进步读取性能。

1.24-Online DDL

从 MySQL 8.0.12 开始（仅仅指 InnoDB 引擎），以下 ALTER TABLE 操作反对 ALGORITHM=INSTANT：

  • 增加列。此性能也称为“即时增加列”。限度实用。
  • 增加或删除虚构列。
  • 增加或删除列默认值。
  • 批改 ENUM 或 SET 列的定义。
  • 更改索引类型。
  • 重命名表。

Online DDL 的益处：

反对 ALGORITHM=INSTANT 的操作只批改数据字典中的元数据。表上没有元数据锁，表数据不受影响，操作是即时的，并不会造成业务抖动。这在一些服务级别要求比拟高（7*24）的零碎中，是十分不便的。该个性是由腾讯游戏 DBA 团队奉献的。

如果未明确指定，则反对它的操作默认应用 ALGORITHM=INSTANT。如果指定了 ALGORITHM=INSTANT 但不受反对，则操作会立刻失败并呈现谬误。须要留神的是，在 MySQL 8.0.29 之前，一列只能作为表的最初一列增加。不反对将列增加到其余列中的任何其余地位。从 MySQL 8.0.29 开始，能够将即时增加的列增加到表中的任何地位。

1.25-EXPLAIN ANALYZE

Explain 是咱们罕用的查问剖析工具，能够对查问语句的执行形式进行评估，给出很多有用的线索。但他仅仅是评估，不是理论的执行状况，比方后果中的 rows，可能和理论后果相差甚大。

Explain Analyze 是 MySQL 8 中提供的新工具, 可贵之处在于能够给出理论执行状况。Explain Analyze 是一个查问性能剖析工具，能够具体的显示出 查问语句执行过程中，都在哪儿破费了多少工夫。Explain Analyze 会做出查问打算，并且会理论执行，以测量出查问打算中各个关键点的理论指标，例如耗时、条数，最初具体的打印进去。

这项新性能建设在惯例的 EXPLAIN 根底之上，能够看作是 MySQL 8.0 之前增加的 EXPLAIN FORMAT = TREE 的扩大。EXPLAIN 除了输入查问打算和预计老本之外，EXPLAIN ANALYZE 还会输入执行打算中各个迭代器的实际成本。

1.26-ReplicaSet

InnoDB ReplicaSet 由一个主节点和多个从节点形成. 能够应用 MySQL Shell 的 ReplicaSet 对象和 AdminAPI 操作治理复制集, 例如查看 InnoDB 复制集的状态, 并在产生故障时手动故障转移到新的主服务器.

ReplicaSet 所有的节点必须基于 GTID，并且数据复制采纳异步的形式。应用复制集还能够接管既有的主从复制，然而须要留神，一旦被接管，只能通过 AdminAPI 对其进行治理。

1.27- 备份锁

在 MySQL 8.0 中，引入了一个轻量级的备份锁，这个锁能够保障备份一致性，而且阻塞的操作绝对比拟少，是一个十分重要的新个性。

在 MySQL 8.0 中，为了解决备份 FTWRL 的问题，引入了轻量级的备份锁；能够通过 LOCK INSTANCE FOR BACKUP 和 UNLOCK INSTANCE，以获取和开释备份锁，执行该语句须要 BACKUP_ADMIN 权限。

backup lock 不会阻塞读写操作。不过，backup lock 会阻塞大部分 DDL 操作，包含创立 / 删除表、加 / 减字段、增 / 删索引、optimize/analyze/repair table 等。

总的来说，备份锁还是十分实用的，毕竟其不会影响业务的失常读写；至于备份锁和 DDL 操作的抵触，还是有很多办法能够防止，比方错开备份和变更的工夫、通过 pt-online-schema-change/gh-ost 防止长时间阻塞等等。随着备份锁的引入，Oracle 官网备份工具 MEB 8.0 和 Percona 开源备份工具 XtraBackup 8.0，也是更新了对 backup lock 的反对。

1.28-Binlog 加强

MySQL 8.0.20 版本减少了 binlog 日志事务压缩性能，将事务信息应用 zstd 算法进行压缩，而后再写入 binlog 日志文件，这种被压缩后的事务信息，在 binlog 中对应为一个新的 event 类型，叫做 Transaction_payload_event。

退出移动版