【MySQL 8.0】MySQL 8.0新特性介绍与升级MySQL 8.0

一、MySQL 8.0主要新特性

截至2023年12月，MySQL官方发布的稳定版为8.0.35，另有一个MySQL8.2为创新版，所以暂不做考虑

· 快速新增/删除列

虽然 MySQL 在8.0 以前就已经支持通过 Online DDL实现DDL操作不阻塞DML操作，但在对一些大表进行DDL操作时仍然需要等待很长时间，这样就面临从库延时、耗时过久或者因为DDL操作而创建的临时表产生的资源开销问题。在8.0.12版本开始支持了instant add new column特性，在DDL执行过程中不需要修改存储数据，只用修改存储在系统表中的结构，这样就避免了最耗时的rebuild和apply row log过程，因此效率非常高。经过实际测试，在同服务器下对一个500W的表进行新增或删除字段操作，MySQL 5.7 耗时接近6分钟，MySQL 8.0.35 下仅需要5秒左右

· 自增列持久化

在8.0之前的版本中，如果在自增主键AUTO_INCREMENT值大于max(primary key) + 1 的时候重启了服务，会重置AUTO_INCREMENT的值为max(primary key) + 1。如果主键发生过删除行为，并且对主键列有过更新操作，会触发BUG导致主键冲突。而8.0以后AUTO_INCREMENT会进行持久化存储，不管是删除还是更新操作都会记录到文件中，不会再有这种情况

#5.7 BUG复现过程

#t1表有3行数据
select * from t1  ;
id   c1 
1   test1
2   test2
3   test3

#此时删除AUTO_INCREMENT为3的数据
delete from t1 where id = 3;

select * from t1  ;
id   c1 
1   test1
2   test2

#重启服务后插入新数据
insert into t1(c1) values('test4') ;

#查看新插入的数据，AUTO_INCREMENT将回退为3
select * from t1  ;
id   c1 
1   test1
2   test2
3   test4

#手动修改一条数数据，用到了AUTO_INCREMENT为5
update t1 set id = 5 where c1 = "test1";

select * from t1 ; 
id   c1 
2   test2
3   test4
5   test1

#继续插入数据
insert into t(c1) values('test5') ;

select * from t1  ;
id   c1 
2   test2
3   test4
4   test5
5   test1

#继续插入数据会出现主键冲突，因为mysql此时自动生成的AUTO_INCREMENT为5
insert into t(c1) values('test6') ;

· 新增函数索引

在MySQL 8.0.13之前的版本中如果在查询中使用了函数，会导致索引失效。而从MySQL 8.0.13开始，支持在索引中使用函数或表达式的值。函数索引基于虚拟列功能实现，该列的值都是通过前面的函数或者表达式计算出来的，当使用函数索引的时候就会使用这个计算后的列作为索引

#在未创建函数索引的列上使用函数，不会用到索引
EXPLAIN SELECT * FROM `t_audit_info` WHERE DATE(create_time) > '2021-11-21';

#在t3表的create_time列创建一个使用了DATE函数的索引
ALTER TABLE t3 ADD INDEX idx_create_time_func((DATE(create_time)));

#再次执行查询可以使用索引
EXPLAIN SELECT * FROM `t_audit_info` WHERE DATE(create_time) > '2021-11-21';

· 新增降序索引

倒叙索引的语法在5.7的时候虽然可以使用，但是不会真正生效，添加索引后再查看表结构会发现依然使用的是升序索引。降序索引从8.0 正式支持使用，在对多个字段进行排序时，不同的字段的排序规则可能不同，通过倒叙索引可以提高反向扫描的效率

#创建一个包含降序索引的表格
create table t1(
c1 int,
c2 int,
index idx_c1_c2(c1,c2 desc)
);

#下列包含降序查询的语句可以直接使用索引，执行计划中没有using filesort而是using index
select * from t1 order by c1,c2 desc;

· 新增隐藏索引（不可见索引）

使用invisible关键词可以将索引设置为隐藏索引，查询语句不会使用被隐藏的索引（即便使用force index也不会使用）。该特性一般用于对需要删除的索引进行观察，当索引被隐藏后如果不会对业务带来影响那么就可以正式删除，这样可以减少对大表误删索引后再添加带来的性能成本。需要注意的是索引被隐藏后，如果数据发生更新，索引依然会被更新，长期被隐藏的索引一定要记得删除

creata table t1(
c1 int,
c2 int,
index idx_c1(c1),
index idx_c2(c2) invisible
);

#也可以修改原索引不可见
alter table t1 alter index idx_c1 invisiable;

#不会使用索引
select * from t1 where c2=1;

· Innodb存储引擎支持跳过锁等待

FOR UPDATE语句支持添加NOWAIT、SKIP LOCKED语句。NOWAIT表示如果查询行已经被加锁，那么直接报错返回，不用等待锁超时；SKIP LOCKED可以跳过被锁的行，返回其他没有锁定的记录

select * from t1 for update skip locked;

· GROUP BY语句不再进行隐式排序

在5.7的时候经过group by的字段会默认进行隐式排序，8.0开始需要显式加上order by字句，这个主要是考虑对其他数据库没有自动隐式排序进行兼容，语法更严谨

#5.7支持隐式排序时语法
select count(*) from t1 group by c2;

#5.7版本结果
count(*)        c2
1               10
2               50
1               80
1               100

#8.0版本结果
count(*)        c2
1               10
2               50
1               100
1               80

#8.0开始必须指定order by语句
select count(*) from t1 group by c2 order by c2;

· 新增innodb_dedicated_server自适应参数

该参数可以让InnoDB根据服务器内存自动调整innodb_buffer_pool、innodb_log_file_size等参数，尽可能的使用内存资源。该参数默认关闭，如果服务器上存在其他组件或者是多实例部署，不建议打开该功能

show variables like '%innodb_dedicated_server%';

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

8.0 将为Undo log默认创建2个单独的表空间，不再使用系统表空间。Undo Log主要存储了回滚数据，是MySQL MVCC机制的根本。在MySQL 8以前这些回滚数据默认存储在了系统表空间ibdata1文件中（在5.7之前甚至无法分离出来）。在有大量并发事务或者大事务未提交的情况下会导致ibdata暴涨的问题，而这部分空间通常只有重启数据库\重建实例才能回收

· binlog日志过期时间可以精确到秒

· DDL事务原子化

MySQL 8.0开始支持了更多的数据字典，元数据全部采用InnoDB引擎进行存储，不再存在.frm文件。因此也让DDL开始支持事务完整性，一个事务中如果提交了多个DDL，它们要么全部成功要么全部回滚，不再出现部分成功的情况

· 可在线持久化配置

MySQL 8.0通过PERSIST命令可以在线修改全局参数并持久化到配置文件中，在线修改的参数会保存到data/mysqld.auto.cnf（该配置文件优先级高于my.cnf），启动MySQL时会从该配置文件拉取配置

SET PERSIST innodb_lock_wait_timeout=15;

· 支持公共表表达式，即WITH语句（CTE，Common Table Expression）

可以将复杂的子查询提取出来，主查询中直接使用，降低子查询复杂度

WITH cte_name AS (
    SELECT ...
)
SELECT * FROM cte_name;

· 支持窗口函数和通用表达式（主要用于OLAP场景）

窗口函数可以在一组查询行上执行类似于聚合的操作，但是相比原有的聚合函数，它不会修改原有语句的输出结果，而是直接添加新的聚合字段进行展现，就像是对查询结果返回的窗口进行函数处理。窗口函数主要用于OLAP场景，比如处理复杂的报表、统计分析等

select name,sum(balance) from account_channel group by name;

#不再需要group by，聚合结果在每一行中展示
select name,channel,balance,sum(balance) over(partition by name) as sum_balance from account_channel;

除了上述亮点特性以外，还有以下更新：

· 在官方测评中 MySQL 8.0 性能峰值几乎是5.7的两倍

· 调整默认字符集为utf8mb4

· 使用新的密码认证插件caching_sha2_password（替代原本的mysql_native_password）（客户端需要升级驱动），支持更高的密码策略，即便相同的密码加密后的密文也是不同的

· 重构SQL分析器，增强优化器和CBO特性

· 支持使用shutdown命令直接关闭服务

· 错误日志增加了不同错误的错误编号，方便查询

· 并行复制writeset机制，提升从库复制性能

· 支持创建系统级别或者用户级别资源组以限制SQL对服务器资源的占用

· 新增计算列功能，比如C列的数据是通过A列和B列计算后产生

· 增加CREATE TABLE ... SELECT的原子性和crash safe支持，解决了MGR架构下无法使用的问题

· 角色role功能

· 可并行写入redo log，提升大量事务写入时的性能

· 可在线调整redo log buffer和undo log相关设置

· 可针对单个会话SET_VAR设置变量，提升灵活性

二、升级8.0前的准备

1、先阅读官方What Is New In MySQL 8.0文档，关注被废弃的功能，避免配置文件中依然存在这些历史配置

2、需要考虑回滚方案

3、通过先升级从库的方式进行平稳过渡

4、升级前需要关注是否存在关键词兼容问题以及GROUP BY语句兼容问题，可以使用官方的检查工具

mysqlsh root:123456@192.168.1.101:3306 -e "util.checkForServerUpgrade();"

5、5.7版本支持直接升级8.0（5.6不支持），升级大版本前建议先把小版本升级到最新

6、建议升级方式采用逻辑备份+导入的方式

三、升级MySQL 8.0步骤

1、关闭当前MySQL

2、备份数据

3、使用新版本二进制包替换原版本

4、更改配置文件

5、启动服务，不需要mysql_upgrade，MySQL 8.0支持通过配置upgrade = AUTO参数来自动处理升级需要做的一切