MySQL 8.0 新特性梳理汇总

一 历史版本发布回顾

从上图可以看出，基本遵循 5+3+3 模式

5—GA发布后，5年 就停止通用常规的更新了（功能不再更新了）；

3—企业版的，+3年功能不再更新了；

3 —完全停止更新了。

总之，一句话， 已经到了升级的时候了。

二 InnoDB增强

2.1自增列

官方定义

当前最大自动递增计数器值每次写入重做日志时更改、并保存到每个检查点上的引擎专用系统表中。这些更改使当前最大自动增量计数器值在服务器重新启动时保持不变。

数据类型–auto_increment。

新特性：

自增列方面，现在自增列计数器会在每次值修改时，将值写到redo_log中，并且在checkpoint时写到存储引擎私有的系统表中。这样就消除了以往重启实例自增列不连续的问题。

2.2 索引损坏

官方定义

当遇到索引树损坏时，InnoDB会在redo日志中写入一个损坏标志，这会使损坏标志安全崩溃。InnoDB还将内存损坏标志数据写入每个检查点的私有系统表中。

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

含义

InnoDB会向redolog中写入一个损坏标志。同时也会 checkpoint时将内存中损坏页的数据记录到存储引擎私有的系统表中。

如果涉及数据修复，会将两个地方的结果进行合并，确保损坏的索引不再恢复，保障了数据的已执行。

降低了之前使用innodb_force_recovery 和innodb_fast_shutdown的必要。

2.3 InnoDB memcached插件

官方定义

InnoDB memcached插件支持多个get操作（在一个memcached查询中获取多个键值对）和范围查询。

实现

安装daemon_memcached插件;

在innodb_memcache schema 中，一张containers表用来与innodb表之间做映射。

优势

减少客户端和服务器之间的通信流量，在单个memcached查询中获取多个键、值对的功能可以提高读取性能。

更少的事务和开放式表操作。

2.4 死锁检测

A new dynamic variable, innodb_deadlock_detect, may be used to disable deadlock detection. On high concurrency systems, deadlock detection can cause a slowdown when numerous threads wait for the same lock. At times, it may be more efficient to disable deadlock detection and rely on the innodb_lock_wait_timeout setting for transaction rollback when a deadlock occurs.

2.5 数据字典

官方定义

MySQL 现在合并了一个事务数据字典，它存储有关数据库对象的信息。在以前的MySQL本版中，字典数据存储在元数据文件和非事务表中。

数据字典的定义

数据字典是指对数据的数据项、数据结构、数据流、数据存储、处理逻辑等进行定义和描述，其目的是对数据流程图中的各个元素做出详细的说明，使用数据字典为简单的建模项目。简而言之，数据字典是描述数据的信息集合，是对系统中使用的所有数据元素的定义的集合。

老版本

在之前的版本中，字典数据存储在元数据文件和非事务表中。（存储在.frm文件中）

8.0版本

数据字典以InnoDB表存储字典数据，位于mysql数据库下，对外不可见。.frm文件也不存在了。

好处

（1）统一集中存储字典数据，方便了系统管理；

（2）移除了基于文件的元数据存储；

（3）数据字典的事务性，安全性；

（4）字典对象的集中和统一的缓存；

（5）一些 information_schema中表的更简单和改进的实现。

2.6 原子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;

2.7 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线程的通知。

更多内容那个可以参考: https://www.h5w3.com/231084.html

2.8 instant特性（Online DDL ）

As of MySQL 8.0.12, ALGORITHM=INSTANT is supported for the following ALTER TABLE operations:

• Adding a column. This feature is also referred to as “Instant ADD COLUMN”. Limitations apply.

• Adding or dropping a virtual column.

• Adding or dropping a column default value.

• Modifying the definition of an ENUM or SET column.

• Changing the index type. • Renaming a table.

instant 的好处

Operations that support ALGORITHM=INSTANT only modify metadata in the data dictionary. No metadata locks are taken on the table, and table data is unaffected, making the operations instantaneous. If not specified explicitly, ALGORITHM=INSTANT is used by default by operations that support it. If ALGORITHM=INSTANT is specified but not supported, the operation fails immediately with an error.

需要注意的是

Prior to MySQL 8.0.29, a column can only be added as the last column of the table. Adding a column to any other position among other columns is not supported. From MySQL 8.0.29, an instantly added column can be added to any position in the table.

三 性能增强

3.1 hash join 增强

MySQL 8.0.23 reimplements the hash table used for hash joins, resulting in several improvements in hash join performance.

The new hash table is generally faster than the old one, and uses less memory for alignment, keys/values, and in scenarios where there are many equal keys. In addition, the server can now free old memory when the size of the hash table increases.

3.2 anti join 优化

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

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

*** 找出在集合A且不在集合B中的数据

*** 找出在当前季度里没有购买商品的客户

*** 找出今年没有通过考试的学生

*** 找出过去3年，某个医生的病人中没有进行医学检查的部分

原文地址；

https://mytecdb.com/blogDetail.php?id=108

3.3 直方图

优化器会利用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的性能。

3.4 倒序索引

MySQL now supports descending indexes: DESC in an index definition is no longer ignored but causes storage of key values in descending order. Previously, indexes could be scanned in reverse order but at a performance penalty. A descending index can be scanned in forward order, which is more efficient. Descending indexes also make it possible for the optimizer to use multiple-column indexes when the most efficient scan order mixes ascending order for some columns and descending order for others.

3.5 不可见索引

在 MySQL 8.0 中，索引可以被”隐藏”和”显示”。

当对索引进行隐藏时，它不会被查询优化器所使用。我们可以使用这个特性用于性能调试，例如我们先隐藏一个索引，然后观察其对数据库的影响。如果数据库性能有所下降，说明这个索引是有用的，然后将其”恢复显示”即可；如果数据库性能看不出变化，说明这个索引是多余的，可以考虑删掉。

使用INVISIBLE关键字在创建表或者进行表变更中设置索引是否可见。索引不可见只是在查询时优化器不使用该索引，即使使用force index，优化器也不会使用该索引，同时优化器也不会报索引不存在的错误，因为索引仍然真实存在。

3.6 多值索引

Beginning with MySQL 8.0.17, InnoDB supports the creation of a multi-valued index, which is a secondary index defined on a JSON column that stores an array of values and which can have multiple index records for a single data record. Such an index uses a key part definition such as CAST(data->'$.zipcode' AS UNSIGNED ARRAY). A multi-valued index is used automatically by the MySQL optimizer for suitable queries, as can be viewed in the output of EXPLAIN.

3.7 函数索引

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

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

• 只有那些能够用于计算列的函数才能够用于创建函数索引。
• 函数索引中不允许使用子查询、参数、变量、存储函数以及自定义函数。
• SPATIAL 索引和 FULLTEXT 索引不支持函数索引。

3.8 关闭QC（ Query Cache ）

具体原因和效果可参照:https://blog.csdn.net/db_murphy/article/details/117392718

四 优化器增强

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日志解决方案中，现在Re’do日志由于其写入和刷缓存的操作都有专用的线程来处理。用户线程之间不在持有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 认值加密插件

老版本：认证方式为sha256_password

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

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

5.2 用户密码增强

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

_密码 次数 ：_无法从指定数量的最近密码中选择新密码。如果密码更改的最小数量设置为5，则新密码不能与最近最新的5个密码相同。

时间间隔：无法从历史记录中比指定天数更新的密码中选择新密码。例如，密码重用间隔设置为90，则新密码不能和过去90天内的密码相同。

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

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

（3）双密码

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

5.3 角色功能

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

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

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

5.4 redo & undo 日志加密

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

六 功能性增强

6.1 EXPLAIN ANALYZE

Explain 是我们常用的查询分析工具，可以对查询语句的执行方式进行评估，给出很多有用的线索。但他仅仅是评估，不是实际的执行情况，比如结果中的 rows，可能和实际结果相差甚大。

Explain Analyze 是 MySQL 8 中提供的新工具,可贵之处在于可以给出实际执行情况。Explain Analyze 是一个查询性能分析工具，可以详细的显示出 查询语句执行过程中，都在哪儿花费了多少时间。Explain Analyze 会做出查询计划，并且会实际执行，以测量出查询计划中各个关键点的实际指标，例如耗时、条数，最后详细的打印出来。

这项新功能建立在常规的EXPLAIN基础之上，可以看作是MySQL 8.0之前添加的EXPLAIN FORMAT = TREE的扩展。EXPLAIN除了输出查询计划和估计成本之外，EXPLAIN ANALYZE还会输出执行计划中各个迭代器的实际成本。

6.2 CTE –公用表达式 Common Table Expression

MySQL 8.0 带来了支持递归的公用表达式的功能。非递归的公用表达式由于允许由form子句派生的临时表的原因可以被多次引用，因而被解释为改进型的派生表（from子句中的临时表）。而递归的公用表达式则由一组原始住居，经过处理后得到新的一组数据，再被带入处理得到更多的新数据，循环往复直到再也无法产生更多新数据为止。公用表达式也是一个用户呼声频繁的SQL功能。

CTE(Common Table Expression)可以认为是派生表(derived table)的替代，在一定程度上，CTE简化了复杂的join查询和子查询，提高了SQL的可读性和执行性能。

CTE优势

• 查询语句的可读性更好
• 在一个查询中，可以被引用多次
• 能够链接多个CTE
• 能够创建递归查询
• 能够提高SQL执行性能
• 能够有效地替代视图

原文地址:

https://mytecdb.com/blogDetail.php?id=75

6.3 窗口函数（Window Functions）

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

它可以用来实现若干新的查询方式。窗口函数与 SUM()、COUNT() 这种聚合函数类似，但它不会将多行查询结果合并为一行，而是将结果放回多行当中。即窗口函数不需要 GROUP BY。

在MySQL 5.7中字段名为rank是可以的，但是在8.0中因为有了窗口函数，字段名为rank就报错，顺着这个思路，其实我们一窥窗口函数

其实就会发现不光是rank,字段名是first_value也不可以了，随之带来的就是SQL语法错误，可能会让人开始有点抓不着头脑。

create table test3(id int primary key,first_value varchar(30));

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual  that corresponds to your MySQL server version for the right syntax to use near  'first_value varchar(30))' at line 1

6.4 clone plugin

MySQL 8.0 新增了一个clone插件，能够实现本地或者远程MySQL实例快速克隆。克隆的数据是InnoDB引擎的快照数据，包括数据库(schema)，表(tables)，表空间(tablespaces)，数据字典元数据(data dictionary metadata)。克隆的数据是一个功能齐全的数据目录，使用该目录能够快速添加从节点或者组复制节点。

从远程MySQL实例克隆数据时，远程的MySQL实例称之为donor，它是数据提供者，是克隆的数据源。本地克隆数据的接收者，称之为recipient。克隆的数据通过网络，从donor传输给recipient。recipient本地的数据默认会被清除，当然也可以为recipient指定一个不同的目录用于存储克隆数据。

克隆插件除了克隆数据之外，还支持搭建复制。克隆操作能够获取复制的坐标点，在克隆完成之后，利用复制的坐标点信息，能够很方便的搭建主从复制，或者增加组复制节点。克隆插件支持表数据加密和压缩，在使用clone插件之前，需要安装插件。

原文地址；

https://mytecdb.com/blogDetail.php?id=173

6.5 ReplicaSet

InnoDB ReplicaSet 由一个主节点和多个从节点构成. 可以使用MySQL Shell的ReplicaSet对象和AdminAPI操作管理复制集, 例如检查InnoDB复制集的状态, 并在发生故障时手动故障转移到新的主服务器.

ReplicaSet 所有的节点必须基于GTID，并且数据复制采用异步的方式。使用复制集还可以接管既有的主从复制，但是需要注意，一旦被接管，只能通过AdminAPI对其进行管理。

6.6 备份锁

在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的支持。

原文地址:

https://cloud.tencent.com/developer/article/1747961

6.7 Binlog增强（例如更新Json字段，只记录部分内容）

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

原文地址：

https://mytecdb.com/blogDetail.php?id=131

6.8 默认字符集由latin1变为utf8mb4

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

6.9 新增innodb_dedicated_server参数

能够让InnoDB根据服务器上检测到的内存大小自动配置innodb_buffer_pool_size，innodb_log_file_size，innodb_flush_method三个参数。

6.10 JSON特性增强

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

七.其他增强

7.1.组复制

消息碎片化 、通信协议设

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

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

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

It is created by the server upon execution of SET PERSIST or SET PERSIST_ONLY statements.

例如：

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

This SET syntax enables you to make configuration changes at runtime that also persist across server restarts. Like SET GLOBAL, SET PERSIST sets the global variable runtime value, but also writes the variable setting to the mysqld-auto.cnf file (replacing any existing variable setting if there is one).

7.3 binlog日志过期时间精确到秒

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

7.4 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表空间超过这个值，该表空间被标记为可回收。

7.5 地理信息系统 GIS

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

八. MySQL 升级

已升级到MySQL 8.0.32 为例.

8.1. 准备工作

step 1 上传 bin 文件包

上传 bin 文件包 mysql-8.0.32-linux-glibc2.12-x86_64.tar.xz

Step 2 解压

xz -d mysql-8.0.32-linux-glibc2.12-x86_64.tar.xz
tar -xvf mysql-8.0.32-linux-glibc2.12-x86_64.tar -C /usr/local/

Step 3 其它

如果有MHA高可用,建议先暂停;

如果my.cnf需要调整参数,建议在临时目录中先提前准备好.

8.2 升级操作

step 1 关闭 MySQL 服务

service mysql stop

step 2 备份 mysql data 的数据文件. (以防升级失败,需要回滚)

step 3 删除既有的mysql软连接，创建新的链接

cd /usr/local/
rm -rf mysql
ln -sf mysql-8.0.32-linux-glibc2.12-x86_64 mysql

step 4 为新建的mysql 软连接赋权

chown -R mysql:mysql /usr/local/mysql

step 5 新版本服务的配置

cd /usr/local/mysql
cp support-files/mysql.server /etc/init.d/mysql
echo "export PATH=$PATH:/usr/local/mysql/bin" >> /etc/profile   -----如果已存在,这一条命令可忽略.

source /etc/profile

step 6 检查my.cnf

如果有需要调整的配置,请在此时调整.

如果需要调整,可以备份后,用准备阶段已准备好的my.cnf 覆盖.

step 7 Mysql 服务 reload

systemctl daemon-reload

step 8 启动（升级）

service mysql start

8.3 检查 及留意应用

详细资料请参考: https://dev.mysql.com/doc/refman/8.0/en/upgrade-binary-package.html

九.参考

1.我为什么强烈建议升级到MySQL 8.0

https://www.bilibili.com/video/av200848981/

2.MySQL性能基准测试对比：MySQL 5.7与MySQL 8.0

https://www.yisu.com/zixun/11215.html

3.MySQL8.0 GA版本的新特性有哪些

https://www.yisu.com/zixun/30454.html

4.MySQL5.6、5.7、8.0在线DDL对比情况

. https://www.yisu.com/zixun/29320.html

5.MySQL 8.0 新特性完整汇总

https://mytecdb.com/blogDetail.php?id=170

6.MySQL 8.0 默认值发生改变的参数汇总

https://mytecdb.com/blogDetail.php?id=140

Original: https://www.cnblogs.com/xuliuzai/p/16369081.html
Author: 东山絮柳仔
Title: MySQL 8.0 新特性梳理汇总