Mysql8.0版本新特性

Mysql 8.0 新特性

1. 新增降序索引
Mysql 在语法上 已经支持降序索引,但是创建时依然使用升序索引.

2. group by 不再隐式排序
Mysql 8.0 对于group by 字段不再隐式排序,如果需要排序,那么还需要添加 order by 条件
Mysql5.7:

Mysql8.0

Mysql 8.0排序后

3. 增加隐藏索引
使用 invisible 创建表 或者 进行 表变更中设置索引为 隐藏索引. 这时,索引隐藏不可见, 但是数据库会在后台继续维护隐藏. 这个隐藏索引, 在查询时,甚至 force index 都不会使用. 这个隐藏索引的主要作用是: 一些索引不确定是否有需要, 如果进行删除,后续又需要恢复这个索引, 假如数据量很大,那么就会耗费大量时间. 这时就可以将索引设置为隐藏索引, 等需要时 直接恢复即可.

#创建隐藏索引方式
create table table1 (field1 int ,field2 int ,index idx_field1(field1), index idx_field2(field2) invisible);
#设置索引可见
alter table table1 alter index idx_field2 visible;
#设置索引不可见
alter table table1 alter index idx_field2 invisible;

4. 新增函数索引
在Mysql 8.0之前,如果在查询中 加入了函数,会导致索引失效. Mysql 8.0 就引入了函数索引.
函数索引基于虚拟列功能实现 , 在Mysql 中相当于新增了一个列. 这个列会根据函数来计算结果, 使用函数索引来进行计算结果, 之后使用函数索引的时候 就会使用这个计算后的列作为索引.

#创建普通索引
create table table3(c1 varchar(10),c2 varchar(10));
create index idx_c1 on table3(c1);
#创建大写函数索引
 create index func_idx on table3((UPPER(c2)));

5. innodb 存储引擎 select for update 跳过锁等待
Mysql 8.0 添加了共享锁语法: select … for share;
Mysql 8.0 之前,如果 select … for update 一直获取不到锁,那么会一直等待, 直到 innodb_lock_wait_timeout 超时.
Mysql 8.0 在 select … for update 或者 select … for share 语句后面添加 nowait , skip locked 语法可以跳过锁等待. nowait 会立即返回报错信息, 而 skip locked 会返回 不包含被锁定的 行的其他数据.

# nowait 样例
select * from t1 where c1 = 2 for update nowait;
#skip locked
select * from t1 for update skip locked;

6. 新增 innodb_dedicated_server 自适应参数
可以让InnoDB引擎 根据服务器上检测到的内存大小自动配置innodb_buffer_pool_size , innodb_log_file_size 等参数, 尽可能多的占用系统可用资源提升性能 . 解决安装数据库后默认初始化数据库参数默认值偏低的问题. 但是需要服务器专门用于 数据库 , 避免其他软件需要使用系统资源, 使得其他软件工作效率降低.

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

**7.**死锁检查控制
Mysql 8.0 添加一个动态变量 innodb_deadlock_detect , 用于控制系统是否执行 InnoDB死锁检查, 默认是打开的. 但是,死锁检测会损耗数据库性能, 对于高并发系统, 可以考虑关闭死锁检测功能, 提高系统新能 . 但是还是需要在 程序 上规避死锁的情况, 同时 需要将锁等待超时参数 调小, 避免出现死锁 等待过久的情况.

#默认打开状态
show variables like '%innodb_deadlock_detect%';

8. undo log 文件不再使用系统表空间
Mysql 8.0 会默认创建 2个undo 表空间,不再使用系统表空间.

9. binlog 日志文件过期时间精确到秒
在Mysql 8.0之前, binlog 日志文件过期时间单位为 天 , 其日志过期时间设置参数为 : expire_logs_days .
在Mysql 8.0 默认使用 binlog_expire_logs_seconds 参数.

10. 窗口函数( window functions, 也称为分析函数)
在Mysql8.0 版本, 新增了窗口函数, 用来实现若干新的查询方式 . 窗口函数 与 sum() , count() 这些 分组聚合函数相类似, 在聚合函数后面加上over() 就变成窗口函数. 在括号中加上partition by 等分组关键字 指定如何分组, 窗口函数即便分组 也不会将多行查询合并为一行, 而是将结果放入到多行之中, 就是说 窗口函数 不需要 使用group by.

 # 创建一张账户余额表
 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 `account_channel` (`id`, `name`, `channel`, `balance`) VALUES ('1', 'zhuge', 'wx', '100');
INSERT INTO `account_channel` (`id`, `name`, `channel`, `balance`) VALUES ('2', 'zhuge', 'alipay','200');
INSERT INTO `account_channel` (`id`, `name`, `channel`, `balance`) VALUES ('3', 'zhuge', 'yinhang','300');
INSERT INTO `account_channel` (`id`, `name`, `channel`, `balance`) VALUES ('4', 'lilei', 'wx','200');
INSERT INTO `account_channel` (`id`, `name`, `channel`, `balance`) VALUES ('5', 'lilei', 'alipay','100');
INSERT INTO `account_channel` (`id`, `name`, `channel`, `balance`) VALUES ('6', 'hanmeimei', 'wx','500');

普通查询结果

	select * from account_channel;

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;

#根据 名字进行分组 balance 进行排序
	select name,channel,balance,sum(balance) over(partition by name order by balance) as sum_balance from account_channel;

# over()里如果不加条件，则默认使用整个表的数据做运算
select name,channel,balance,sum(balance) over() as sum_balance from account_channel;

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

相关专用窗口函数
(1)序号函数 : ROW_NUMBER()、RANK()、DENSE_RANK() ;
(2)分布函数: PERCENT_RANK()、CUME_DIST() ;
(3)前后函数: LAG()、LEAD() ;
(4)头尾函数: FIRST_VALUE()、LAST_VALUE();
(5)其他函数: NTH_VALUE()、NTILE() ;

#按照balance字段进行排序,并展示序号
select name,channel,balance,row_number() over(order by balance) as row_number1 from account_channel;

#按照balance字段排序,first_value()选出排第一的余额
select name,channel,balance,first_value(balance) over(order by balance) as first1 from account_channel;

12. 默认字符集由 latin1 变成utf8mb4
在Mysql 8.0之前, 默认字符集 为 latin1 , utf8指向 utf8mb3 , Mysql8.0 默认字符集尾utf8mb4 ,utf8默认只想utf8mb4.

13. MyISAM系统表全部替换成InnoDB表
将系统表(mysql) 和 数据字典表全部替换成 InnoDB 存储引擎 . 默认的Mysql实例 将不包含MyISAM表,除非手动创建MyISAM表.

14. 元数据存储变动
Mysql8.0删除了 之前版本的元数据文件, 如 表结构 .frm文件, 全部集中到mysql.idb 文件中.

15. 自增变量持久化
在Mysql 8.0 之前,自增住建AUTO_INCREMENT 的值如果大于max(primary key) +1 , 在Mysql重启后, 会重置 AUTO_INCREMENT= max(primary key +1), 这种现象 在某些情况会导致业务住建冲突或者其他难以发现的问题. 自增主键 重启重置的问题 在Mysql8.0才被解决, 在Mysql8.0版本 将会对AUTO_INCREMENT 的值进行持久化, 在重启后不会发生变更.

16. DDL原子化
Innodb 表的DDL 支持事务完整性, 要么成功 要么回滚.
Mysql8.0 开始支持原子DDL操作, 其中与表相关的原子 DDL只支持 Innodb存储引擎. 一个原子DDL 操作内容包含: 更新数据字典, 存储引擎层的操作. 在binlog 中记录DDL操作, 支持与表相关的DDL : 数据库, 表空间, 表, 索引的 create ,alter , drop , 以及truncate table 操作. 支持其他的DDL ,如: 存储程序, 触发器, 视图, UDF 的create ,drop 和 alter 语句. 支持账户管理相关的DDL : 用户和角色的create , alter, drop 以及适用的rename等操作;

17. 参数修改持久化
Mysql8.0 版本支持 在线修改全局参数并持久化, 通过加上presist 关键字, 可以将修改的参数持久化到新的配置文件(mysqld-auto.cnf)中,重启Mysql时, 可以从这个配置文件中获取到最新的配置参数. set global 设置的变量参数 在mysql重启后失效.