MySQL8.0新特性解读

一、功能增强

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。

二、性能提升

2.1-基于竞争感知的事务调度

MySQL 在 8.0.3 版本引入了新的事务调度算法，基于竞争感知的事务调度，Contention-Aware Transaction Scheduling，简称CATS。在CATS算法之前，MySQL使用FIFO算法，先到的事务先获得锁，如果发生锁等待，则按照FIFO算法进行排队。CATS相比FIFO更加复杂，也更加聪明，在高负载、高争用的场景下，性能提升显著。

2.2-基于WriteSet的并行复制

总的来说MySQL关于并行复制到目前为止经历过三个比较关键的时间结点“库间并发”，“组提交”，“写集合”；真可谓是江山代有人才出，前浪死在沙滩上；总的来说就后面的比前面的不知道高到哪里去了！

MySQL 8.0 版本引入了一个新的机制 WriteSet，来追踪事务之间的依赖性，这个特性被用于优化从库应用binlog的速度，在主库并发较低的场景下，能够显著提高从库回放binlog的速度，基于WriteSet 的并行复制方案，彻底解决了MySQL复制延迟问题。只需要设置这2个参数即可

binlog_transaction_dependency_tracking  = WRITESET                 #    COMMIT_ORDER     
transaction_write_set_extraction        = XXHASH64

2.3-JSON特性增强

MySQL 8 大幅改进了对 JSON 的支持，添加了基于路径查询参数从 JSON 字段中抽取数据的 JSON_EXTRACT() 函数，以及用于将数据分别组合到 JSON 数组和对象中的 JSON_ARRAYAGG() 和 JSON_OBJECTAGG() 聚合函数。

在主从复制中，新增参数 binlog_row_value_options，控制JSON数据的传输方式，允许对于Json类型部分修改，在binlog中只记录修改的部分，减少json大数据在只有少量修改的情况下，对资源的占用。

2.4-空间数据类型增强

MySQL 8 大幅改进了空间数据类型和函数，支持更多的空间分析函数和空间类型对象，空间分析功能和性能得到大幅提升。

2.5-doublewrite改进

在MySQL 8.0.20 版本之前，doublewrite 存储区位于系统表空间，从 8.0.20 版本开始，doublewrite 有自己独立的表空间文件，这种变更，能够降低doublewrite的写入延迟，增加吞吐量，为设置doublewrite文件的存放位置提供了更高的灵活性。

2.6-hash join

MySQL 8.0.18 版本引入 hash join 功能，对于没有走索引的等值 join 连接可以使用 hash join 进行优化。8.0.20 版本对 hash join 进行了加强，即使 join 连接没有使用等值条件也可以使用 hash join 优化，原来使用 BNL 算法的 join 连接将全部由 hash join 代替。

2.6.1-NestLoopJoin算法

简单来说，就是双重循环，遍历外表(驱动表)，对于外表的每一行记录，然后遍历内表，然后判断join条件是否符合，进而确定是否将记录吐出给上一个执行节点。

从算法角度来说，这是一个M*N的复杂度。

2.6.2-Hash Join

是针对equal-join场景的优化，基本思想是，将外表数据load到内存，并建立hash表，这样只需要遍历一遍内表，就可以完成join操作，输出匹配的记录。

如果数据能全部load到内存当然好，逻辑也简单，一般称这种join为CHJ(Classic Hash Join)，之前MariaDB就已经实现了这种HashJoin算法。

如果数据不能全部load到内存，就需要分批load进内存，然后分批join，下面具体介绍这几种join算法的实现。

2.7-anti join（反连接）

MySQL 8.0.17版本引入了一个anti join的优化，这个优化能够将where条件中的not in(subquery)， not exists(subquery)，in(subquery) is not true，exists(subquery) is not true，在内部转化成一个anti join，以便移除里面的子查询subquery，这个优化在某些场景下，能够将性能提升20%左右。

anti join适用的场景案例通常如下：

• 找出在集合A且不在集合B中的数据
• 找出在当前季度里没有购买商品的客户
• 找出今年没有通过考试的学生
• 找出过去3年，某个医生的病人中没有进行医学检查的部分

2.8-redo优化

mysql8.0一个新特性就是redo log提交的无锁化。在8.0以前，各个用户线程都是通过互斥量竞争，串行的写log buffer，因此能保证lsn的顺序无间隔增长。

mysql8.0通过redo log无锁化，解决了用户线程写redo log时竞争锁带来的性能影响。同时将redo log写文件、redo log刷盘从用户线程中剥离出来，抽成单独的线程，用户线程只负责将redo log写入到log buffer，不再关心redo log的落盘细节，只需等待log_writer线程或log_flusher线程的通知。

2.9-直方图（统计信息）

优化器会利用column_statistics的数据，判断字段的值的分布，得到更准确的执行计划。

可以通过ANALYZE TABLE table_name [UPDATE HISTOGRAM on colume_name with N BUCKETS |DROP HISTOGRAM ON clo_name] 来收集或者删除直方图信息。

直方图统计了表中某些字段的数据分布情况，为优化选择高效的执行计划提供参考，直方图与索引有着本质的区别，维护一个索引有代价。每一次的insert、update、delete都需要更新索引，会对性能有一定的影响。而直方图一次创建永不更新，除非明确去更新它，因此不会影响insert、update、delete的性能。

2.10-关闭QC（Query Cache ）

从 MySQL 8.0开始，不再使用查询缓存（Query Cache）。

随着技术的进步，经过时间的考验，MySQL的工程团队发现启用缓存的好处并不多。

首先，查询缓存的效果取决于缓存的命中率，只有命中缓存的查询效果才能有改善，因此无法预测其性能。

其次，查询缓存的另一个大问题是它受到单个互斥锁的保护。在具有多个内核的服务器上，大量查询会导致大量的互斥锁争用。

MySQL8.0取消查询缓存的另外一个原因是，研究表明，缓存越靠近客户端，获得的好处越大。MySQL8.0新增加了一些其他对性能干预的工具来支持。另外，还有像ProxySQL这样的第三方工具，也可以充当中间缓存。

三、安全性增强

3.1-死锁检测

可以使用一个新的动态变量 innodb_deadlock_detect 来禁用死锁检测。在高并发系统上，当多个线程等待同一个锁时，死锁检测会导致速度变慢。有时，禁用死锁检测并在发生死锁时依靠 innodb_lock_wait_timeout 设置进行事务回滚可能更有效。

3.2-默认密码认证插件

MySQL 8.0.4 版本修改了默认的身份认证插件，从老的mysql_native_password插件变为新的caching_sha2_password，并将其作为默认的身份认证机制，同时客户端对应的libmysqlclient也默认使用新的认证插件。

3.3-升级密码过期，历史密码使用规则

设置历史密码检测规则，防止反复重用旧密码。

• password_history
• password_reuse_interval

双密码机制，修改密码时，创建新的密码，同时旧的密码也可以使用，保留一定的缓冲时间进行检查确认。

当修改一个账户密码时，需要去验证当前的密码，通过参数password_require_current来控制，默认关闭，当打开该选项时，如果要修改账户密码，必须要提供当前的密码才允许修改。

3.4-认值加密插件

老版本：认证方式为sha256_password

8.0 版本：在老版本的基础上，新增caching_sha2_password,可以使用缓存解决连接时的延时问题。

需要注意的问题是：如果客户端与服务端配置不同，无法进行连接，两者的加密认证方式需要一样。

3.5-用户密码增强

（1）密码的重复使用策略

历史密码重复次数检测：新密码不能与最近最新的5个密码相同。

password_history = 5 ; 

时间间隔：新密码不能和过去90天内的密码相同。

password_reuse_interval = 90 ; 

（2）修改密码必要的验证策略

修改密码，要输入当前的密码。增加了用户的安全性。

## 默认为off；为on 时 修改密码需要用户提供当前密码 (开启后修改密码需要验证旧密码，root 用户不需要)
password_require_current = on ;

（3）双密码

相比于一个用户只有一个密码最大优点就是:修改密码不会导致应用不可用。那么应用就可以自动使用副密码（副密码和当前密码保持一致）连接数据库库。确保了业务的不中断。修改密码不会导致应用不可用；应用就可以自动使用副密码连接数据库。

3.6-角色功能

MySQL角色是指定权限集合。像用户账户一样，角色可以拥有授予和撤销的权限。

可以授予用户账户角色，授予该账户与每个角色相关的权限。

方便了用户权限管理和维护。很好地解决了多个用户使用相同的权限集。权限–》角色–》用户。

3.7-redo & undo 日志加密

增加以下两个参数，用于控制redo、undo日志的加密。

innodb_redo_log_encrypt
innodb_undo_log_encrypt

四、优化器增强

4.1-Cost Model改进

优化器能够感知到页是否存在缓冲池中。5.7其实已经开放接口，但是不对内存中的页进行统计，返回都是1.0.

4.2-可伸缩的读写负载 Scaling Read/Write Workloads

8.0版本对于读写皆有和高写负载的拿捏恰到好处。在集中的读写均有的负载情况下，我们观测到在4个用户并发的情况下，对于高负载，和5.7版本相比有着两倍性能的提高。在5.7上我们显著了提高了只读情况下的性能，8.0则显著提高了读写负载的可扩展性。为MySQL提升了硬件性能的利用率，其改进是基于重新设计了InnoDB写入Redo日志的方法。对比之前用户线程之前互相争抢着写入其数据变更，在新的Redo日志解决方案中，现在Redo日志由于其写入和刷缓存的操作都有专用的线程来处理。用户线程之间不在持有Redo写入相关的锁，整个Redo处理过程都是时间驱动。

8.0版本允许马力全开的使用存储设备，比如使用英特尔奥腾闪存盘的时候，我们可以在IO敏感的负载情况下获得1百万的采样 QPS（这里说的IO敏感是指不在IBP中，且必须从二级存储设备中获取）。这个改观是由于我们摆脱了 file_system_mutex全局锁的争用。

4.3-在高争用（热点数据）负载情况下的更优性能

Better Performance upon High Contention Loads (“hot rows”)

8.0版本显著地提升了高争用负载下的性能。高争用负载通常发生在许多事务争用同一行数据的锁，导致了事务等待队列的产生。在实际情景中，负载并不是平稳的，负载可能在特定的时间内爆发（80/20法则）。8.0版本针对短时间的爆发负载无论在每秒处理的事务数（换句话，延迟）还是95%延迟上都处理的更好。对于终端用户来说体现在更好的硬件资源利用率（效率）上。因为系统需要尽量使用榨尽硬件性能，才可以提供更高的平均负载。

五、其他增强

5.1-支持在线修改全局参数并持久化

通过加上PERSIST关键字，可以将修改的参数持久化到新的配置文件（mysqld-auto.cnf）中，重启MySQL时，可以从该配置文件获取到最新的配置参数。

系统会在数据目录下生成mysqld-auto.cnf 文件，该文件内容是以json格式存储的。当my.cnf 和mysqld-auto.cnf 同时存在时，后者优先级更高。

例如：

SET PERSIST max_connections = 1000;
SET @@PERSIST.max_connections = 1000;

此 SET 语法使您能够在运行时进行配置更改，这些更改也会在服务器重新启动后持续存在。与 SET GLOBAL 一样，SET PERSIST 设置全局变量运行时值，但也将变量设置写入 mysqld-auto.cnf 文件（如果存在则替换任何现有变量设置）。

5.2-binlog日志过期时间精确到秒

之前是天，并且参数名称发生变化. 在8.0版本之前，binlog日志过期时间设置都是设置expire_logs_days参数，而在8.0版本中，MySQL默认使用binlog_expire_logs_seconds参数。

5.3-undo空间自动回收

innodb_undo_log_truncate参数在8.0.2版本默认值由OFF变为ON，默认开启undo日志表空间自动回收。

innodb_undo_tablespaces参数在8.0.2版本默认为2，当一个undo表空间被回收时，还有另外一个提供正常服务。

innodb_max_undo_log_size参数定义了undo表空间回收的最大值，当undo表空间超过这个值，该表空间被标记为可回收。

5.4-地理信息系统 GIS

8.0 版本提供对地形的支持，其中包括了对空间参照系的数据源信息的支持，SRS aware spatial数据类型，空间索引，空间函数。总而言之，8.0版本可以理解地球表面的经纬度信息，而且可以在任意受支持的5000个空间参照系中计算地球上任意两点之间的距离.

注意:升级前，一定要验证jdbc驱动是否匹配，是否需要随着升级。

5.5-参数开关表

select @@optimizer_switch \G

mysql> select @@optimizer_switch \G
*************************** 1. row ***************************
@@optimizer_switch: index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on,index_condition_pushdown=on,mrr=on,mrr_cost_based=on,block_nested_loop=on,batched_key_access=off,materialization=on,semijoin=on,loosescan=on,firstmatch=on,duplicateweedout=on,subquery_materialization_cost_based=on,use_index_extensions=on,condition_fanout_filter=on,derived_merge=on,use_invisible_indexes=off,skip_scan=on,hash_join=on,subquery_to_derived=off,prefer_ordering_index=on,hypergraph_optimizer=off,derived_condition_pushdown=on

session 开关
set session optimizer_switch="use_invisible_indexes=off";  
set session optimizer_switch="use_invisible_indexes=on";  

global 开关
set global optimizer_switch="use_invisible_indexes=off";  
set global optimizer_switch="use_invisible_indexes=on";