Mysql8.0.x新特性及其性能优化分析

Q.ZHA

已于 2024-03-30 18:04:53 修改

阅读量1k

点赞数 29

分类专栏：数据库文章标签：数据库性能优化

于 2024-03-30 00:52:27 首次发布

本文链接：https://blog.csdn.net/qq_21886255/article/details/137159940

版权

数据库专栏收录该内容

4 篇文章 0 订阅

订阅专栏

8.0.17及之后的版本更新的内容比较多，推荐使用8.0.17及之后的版本。

参考文档：

添加弃用和删除的特性：https://dev.mysql.com/doc/refman/8.0/en/mysql-nutshell.html

添加弃用和删除的参数： https://dev.mysql.com/doc/refman/8.0/en/added-deprecated-removed.ht

Mysql8 InnoDB 架构： https://dev.mysql.com/doc/refman/8.0/en/innodb-architecture.html

本文对mysql8.0新特性整理了17个，从实用性优先级先后介绍和分析：

1 隐藏索引

隐藏某个索引时，不被查询优化器使用，通过设置索引隐藏，观察数据库的性能变化来判断索引是否有作用，来决定是否删除某个索引，在优化调试时起了很大的作用。

用法：

ALTER TABLE t ALTER INDEX i INVISIBLE;

恢复语句是：

ALTER TABLE t ALTER INDEX i VISIBLE;

2 设置持久化

MySQL 的设置可以在运行时通过 SET GLOBAL 命令来更改，但是这种更改只会临时生效，到下次启动时数据库又会从配置文件中读取。

MySQL 8 新增了 SET PERSIST 命令，例如：

SET PERSIST max_connections = 500;

MySQL 会将该命令的配置保存到数据目录下的 mysqld-auto.cnf 文件中，下次启动时会读取该文件，用其中的配置来覆盖缺省的配置文件。

3 默认编码改为UTF-8 编码

从 MySQL 8 开始，数据库的缺省编码将改为 utf8mb4，这个编码包含了所有 emoji 字符。

在之前默认是 latin，不手动修改编码会导致中文乱码。

4 通用表表达式（Common Table Expressions）

以前复杂的子查询：

SELECT t1.*, t2.* FROM 

  (SELECT col1 FROM table1) t1,

  (SELECT col2 FROM table2) t2;

而有了 CTE之后：

WITH

  t1 AS (SELECT col1 FROM table1),

  t2 AS (SELECT col2 FROM table2)

SELECT t1.*, t2.* 

FROM t1, t2;

这样看上去层次和区域都更加分明，维护性更好；

关于 CTE 的更详细介绍请看官方文档。

5 窗口函数（Window Functions）

MySQL 被吐槽最多的特性之一就是缺少 rank() 函数，当需要在查询当中实现排名时，必须手写 @ 变量。但是从 8.0 开始，MySQL 新增了一个叫窗口函数的概念，它可以用来实现若干新的查询方式。

窗口函数有点像是 SUM()、COUNT() 那样的集合函数，但它并不会将多行查询结果合并为一行，而是将结果放回多行当中。

select *, rank() over w as `rank` from classes window w as (order by stu_count);

6 新增降序索引

对于业务场景常用降序的字段，可以设置降序索引，大大提升了性能。

7 group by 不再隐式排序

mysql 8.0 对于group by 字段不再隐式排序，如需要排序，必须显式加上order by 子句。

select count(*),c2 from t1 group by c2 order by c2;

8 新增函数索引

之前我们知道，如果在查询中加入了函数，索引不生效，所以MySQL 8引入了函数索引，MySQL

8.0.13开始支持在索引中使用函数(表达式)的值。

函数索引基于虚拟列功能实现，在MySQL中相当于新增了一个列，这个列会根据你的函数来进行计算结果，然后使用函数索引的时候就会用这个计算后的列作为索引。

9 innodb存储引擎select for update跳过锁等待

对于select ... for share(8.0新增加查询共享锁的语法)或 select ... for update，在语句后面添加

NOWAIT、SKIP LOCKED语法可以跳过锁等待，或者跳过锁定。

在5.7及之前的版本，select...for update，如果获取不到锁，会一直等待，直到

innodb_lock_wait_timeout超时。

在8.0版本，通过添加nowait，skip locked语法，能够立即返回。如果查询的行已经加锁，那么nowait

会立即报错返回，而skip locked也会立即返回，只是返回的结果中不包含被锁定的行。

应用场景比如查询余票记录，如果某些记录已经被锁定，用skip locked可以跳过被锁定的记录，只返

回没有锁定的记录，提高系统性能。

10 新增innodb_dedicated_server自适应参数

能够让InnoDB根据服务器上检测到的内存大小自动配置innodb_buffer_pool_size，

innodb_log_file_size等参数，会尽可能多的占用系统可占用资源提升性能。解决非专业人员安装数据

库后默认初始化数据库参数默认值偏低的问题，前提是服务器是专用来给MySQL数据库的，如果还有

其他软件或者资源或者多实例MySQL使用，不建议开启该参数，不然会影响其它程序。

11 死锁检查控制

MySQL 8.0 （MySQL 5.7.15）增加了一个新的动态变量 innodb_deadlock_detect，用于控制系统是否

执行 InnoDB 死锁检查，默认是打开的。死锁检测会耗费数据库性能的，对于高并发的系统，我们可

以关闭死锁检测功能，提高系统性能。但是我们要确保系统极少情况会发生死锁，同时要将锁等待超

时参数调小一点，以防出现死锁等待过久的情况。

show variables like '%innodb_dedicated_server%'; --默认是OFF关闭，修改为ON打开

12 undo文件不再使用系统表空间

默认创建2个UNDO表空间，不再使用系统表空间。

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

之前是天，并且参数名称发生变化. 在8.0版本之前，binlog日志过期时间设置都是设置

expire_logs_days参数，而在8.0版本中，MySQL默认使用binlog_expire_logs_seconds参数。

14 MyISAM系统表全部换成InnoDB表

将系统表(mysql)和数据字典表全部改为InnoDB存储引擎，默认的MySQL实例将不包含MyISAM表，除非手动创建MyISAM表。

15 元数据存储变动

MySQL 8.0删除了之前版本的元数据文件，例如表结构.frm等文件，全部集中放入mysql.ibd文件里。

可以看见下图test库文件夹里已经没有了frm文件。

16 自增变量持久化

在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重启后，该值将不会改变。

17 DDL原子化

InnoDB表的DDL支持事务完整性，要么成功要么回滚。

MySQL 8.0 开始支持原子 DDL 操作，其中与表相关的原子 DDL 只支持 InnoDB 存储引擎。一个原子DDL 操作内容包括：更新数据字典，存储引擎层的操作，在 binlog 中记录 DDL 操作。支持与表相关的 DDL：数据库、表空间、表、索引的 CREATE、ALTER、DROP 以及 TRUNCATE TABLE。支持的其它 DDL ：存储程序、触发器、视图、UDF 的 CREATE、DROP 以及ALTER 语句。支持账户管理相关的 DDL：用户和角色的 CREATE、ALTER、DROP 以及适用的 RENAME等等。