MySQL 8.0 和 MySQL 5.7 的区别_数据库5.7和8.0

准备工作：

1、准备两个MySQL，一个 5.7 版本，一个 8.0版本

2、在两个版本的数据库中 创建相同的表，以及加一些测试数据

create table t1(c1 int,c2 int,index idx_c1_c2(c1,c2 desc));

insert into t1 (c1,c2) values(1, 10),(2,50),(3,50),(4,100),(5,80);

一、新增降序索引

使用同样的查询语句，查询 t1 表中所有数据 并且基于 c1 c2 字段倒序排列

explain select * from t1 order by c1,c2 desc;

对比 5.7 和 8.0 结果

5.7

可以看到在 5.7 版本中，查询使用了索引，但是排序并没有使用索引，而是基于文件排序，说明索引并不能支持倒序排序

8.0

但是在 8.0 版本中，排序也使用了索引，说明 8.0 版本索引是支持倒序排序的

二、group by 不在隐式排序

正常在 5.7 版本中，通过 group by 分组查询 默认会进行排序，8.0 版本默认取消了隐式排序

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

5.7

8.0

三、增加隐藏索引的功能

使用 invisible 关键字在创建表或者进行表变更中设置索引为隐藏索引。索引隐藏只是不可见，但是数据库后台还是会维护隐藏索引的，在查询时优化器不使用该索引，即使用force index，优化器也不会使用该索引，同时优化器也不会报索引不存在的错误，因为索引仍然真实存在，必要时，也可以把隐藏索引快速恢复成可见。注意，主键不能设置为 invisible。

软删除就可以使用隐藏索引，比如我们觉得某个索引没用了，删除后发现这个索引在某些时候还是有用的，于是又得把这个索引加回来，如果表数据量很大的话，这种操作耗费时间是很多的，成本很高，这时，我们可以将索引先设置为隐藏索引，等到真的确认索引没用了再删除。

# 创建t2表，里面的c2字段为隐藏索引
 create table t2(c1 int, c2 int, index idx_c1(c1), index idx_c2(c2) invisible);

可以看到 idx_c2 索引处于隐藏状态

explain select * from t2 where c2=1;

但是使用 这个索引查询 则走到全表扫描

四、新增函数索引

原理就是将表达式的结果放到索引树了

create table t3(c1 varchar(10),c2 varchar(10));

create index idx_c1 on t3(c1);  -- 创建普通索引
create index func_idx on t3((UPPER(c2))); -- 创建一个大写的函数索引

show index from t3;

explain select * from t3 where upper(c2)='ZHUGE';  -- 使用了函数索引

可以看到是走了函数索引的

五、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可以跳过被锁定的记录，只返回没有锁定的记录，提高系统性能。

等待锁超时

select * from t1;

有上面这几条数据，我们使用开启事务，锁住第二条数据

begin;
update t1 set c2 = 60 where c1 = 2;

在另一个 session 中查询 第二条数据

select * from t1 where c1 = 2 for update; -- 等待超时

可以看到50秒超时

跳过锁等待

然后我们在 后面加一个 nowait 参数

可以看到，直接返回错误信息

跳过锁定数据

select * from t1 for update skip locked;  -- 查询立即返回，过滤掉了第二行记录

可以看到，返回的数据中没有第二条数据

六、新增innodb_dedicated_server自适应参数

能够让InnoDB根据服务器上检测到的内存大小自动配置innodb_buffer_pool_size，innodb_log_file_size等参数，会尽可能多的占用系统可占用资源提升性能。解决非专业人员安装数据库后默认初始化数据库参数默认值偏低的问题，

前提是服务器是专用来给MySQL数据库的，如果还有其他软件或者资源或者多实例MySQL使用，不建议开启该参数，不然会影响其它程序。

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

七、死锁检查控制

MySQL 8.0 （MySQL 5.7.15）增加了一个新的动态变量 innodb_deadlock_detect，用于控制系统是否执行 InnoDB 死锁检查，默认是打开的。

死锁检测会耗费数据库性能的，对于高并发的系统，我们可以关闭死锁检测功能，提高系统性能。

但是我们要确保系统极少情况会发生死锁，同时要将锁等待超时参数调小一点，以防出现死锁等待过久的情况。

show variables like '%innodb_deadlock_detect%'; -- 默认是打开的

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

5.7默认是放在

里面，可以通过配置生成undo文件

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

九、binlog日志过期时间精确到秒

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

十、窗口函数(Window Functions)：也称分析函数

# 创建一张账户余额表
CREATE TABLE `account_channel` (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL COMMENT '姓名',
  `channel` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL COMMENT '账户渠道',
  `balance` int DEFAULT NULL COMMENT '余额',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB;
 
# 插入一些示例数据
INSERT INTO `test`.`account_channel` (`id`, `name`, `channel`, `balance`) VALUES ('1', 'zhuge', 'wx', '100');
INSERT INTO `test`.`account_channel` (`id`, `name`, `channel`, `balance`) VALUES ('2', 'zhuge', 'alipay', '200');
INSERT INTO `test`.`account_channel` (`id`, `name`, `channel`, `balance`) VALUES ('3', 'zhuge', 'yinhang', '300');
INSERT INTO `test`.`account_channel` (`id`, `name`, `channel`, `balance`) VALUES ('4', 'lilei', 'wx', '200');
INSERT INTO `test`.`account_channel` (`id`, `name`, `channel`, `balance`) VALUES ('5', 'lilei', 'alipay', '100');
INSERT INTO `test`.`account_channel` (`id`, `name`, `channel`, `balance`) VALUES ('6', 'hanmeimei', 'wx', '500');

group by 分组求和：

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

使用窗口函数：

在聚合函数后面加上 over() 就变成分析函数了，后面可以不用再加group by制定分组，因为在over里已经用partition关键字指明了如何分组计算，这种可以保留原有表数据的结构，不会像分组聚合函数那样每组只返回一条数据

比如分组求和：

select name,channel,balance,sum(balance) over(partition by name) as sum_balance from account_channel;

比如分组求平均数：

select name,channel,balance,avg(balance) over(partition by name) as avg_balance from account_channel;

over()里如果不加条件，则默认使用整个表的数据做运算

select name, channel, balance, sum(balance) over() as sum_balance from account_channel;

专用窗口函数：

• 序号函数：ROW_NUMBER()、RANK()、DENSE_RANK()
• 分布函数：PERCENT_RANK()、CUME_DIST()
• 前后函数：LAG()、LEAD()
• 头尾函数：FIRST_VALUE()、LAST_VALUE()
• 其它函数：NTH_VALUE()、NTILE()

比如序号函数：

基于 balance 排序后显示序号

select name,channel,balance,row_number() over(order by balance) as row_number1 from account_channel;

按照 balance 排序后，选出排名在第一位的 balance

select name,channel,balance,first_value(balance) over(order by balance desc) as first1 from account_channel;

其他的函数就不一一赘述了，感兴趣的可以自行尝试

十一、默认字符集由latin1变为utf8mb4

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

十二、MyISAM系统表全部换成InnoDB表

将系统表(mysql)和数据字典表全部改为InnoDB存储引擎，

默认的MySQL实例将不包含MyISAM表，除非手动创建MyISAM表。

十三、元数据存储变动

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

十四、自增变量持久化

在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值进行持久化（写到redo log文件里面），MySQL重启后，该值将不会改变。

create table t(id int auto_increment primary key,c1 varchar(20)); -- 建一张新表
insert into t(c1) values('zhuge1'),('zhuge2'),('zhuge3');
select * from t;

mysql 5.7

delete from t where id = 3; -- 删除id=3的记录

这时候重启mysql

# 重启MySQL服务，并重新连接MySQL

insert into t(c1) values('zhuge4');
select * from t;

可以看到自增又使用了3

update t set id = 5 where c1 = 'zhuge1'; -- 修改主键为5
select * from t;

insert into t(c1) values('zhuge5');
select * from t;

这时候使用自增的主键插入一条是正常的

insert into t(c1) values('zhuge6');

再插入一条，主键冲突了

mysql 8.0

create table t(id int auto_increment primary key,c1 varchar(20)); -- 建一张新表
insert into t(c1) values('zhuge1'),('zhuge2'),('zhuge3');
select * from t;

delete from t where id = 3; -- 删除id=3的记录
select * from t;

这时候一样的重启mysql，重新连接

insert into t(c1) values('zhuge4');
select * from t;

可以看到主键id为4，而不是3了

update t set id = 5 where c1 = 'zhuge1';
select * from t;

insert into t(c1) values('zhuge5');
select * from t;

可以看到新增的记录主键为6，不会和主键5冲突

十五、DDL原子化

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

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等等。

mysql 5.7

show tables;

这时候执行语句

drop table t1, t2;

可以看到删除t2表失败，t2表不存在

show tables;

而t1也被删掉了

mysql 8.0

show tables;

drop table t1, t5;

show tables;

可见t1表还在

十六、参数修改持久化

MySQL 8.0版本支持在线修改全局参数并持久化，通过加上PERSIST关键字，可以将修改的参数持久化到新的配置文件（mysqld-auto.cnf）中，重启MySQL时，可以从该配置文件获取到最新的配置参数。set global 设置的变量参数在mysql重启后会失效。

set persist innodb_lock_wait_timeout=25;
系统会在数据目录下生成一个包含json格式的mysqld-auto.cnf 的文件，格式化后如下所示，
    当my.cnf 和mysqld-auto.cnf 同时存在时，后者具有更高优先级。
{
	"Version": 1,
	"mysql_server": {
		"innodb_lock_wait_timeout": {
			"Value": "25",
			"Metadata": {
				"Timestamp": 1675290252103863,
				"User": "root",
				"Host": "localhost"
			}
		}
	}
}

以上是比较常用的一些，感兴趣的同学可以翻阅 MySQL 官方网站自行研究

感谢观看！！！

萌新小白一枚，不喜勿喷，谢谢！