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

作者：马文斌
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: 0mysql> 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: 0mysql> 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: 0mysql> 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: 0mysql> 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: 0mysql> 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: t1Create 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_ci1 row in set (0.00 sec)# 不可见的状况下是不会走索引的，key=nullmysql> 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: 0mysql> show create table t1\G*************************** 1. row ***************************       Table: t1Create 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_ci1 row in set (0.00 sec)# 能够走索引，key=idx_c1mysql> 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的反对。