MySQL8新特性
目录
1.降序索引
2.group by不再隐式排序
3.隐藏索引
4.函数索引
6.跳过锁等待
7.自适应参数innodb_dedicated_server
8.死锁检测控制
9.undo日志文件独立，不再使用系统表空间
10.binlog日志过期时间精确到秒
11.窗口函数
12.默认字符集由latin1变为uft8mb4
13.MyISAM系统表全部换成InnoDB表
14.元数据存储变动
15.自增变量持久化
16.DDL原子化
17.参数修改持久化
参考：
MySQL :: MySQL 8.0 Reference Manual :: 1.3 What Is New in MySQL 8.0
MySQL :: MySQL 8.0 Reference Manual :: 1.4 Server and Status Variables and Options Added, Deprecated, or Removed in MySQL 8.0
1.降序索引
- 仅InnoDB存储引擎支持降序索引
- 添加测试数据
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);
- 验证
2.group by不再隐式排序
- MySQL7默认排序
- MySQL8不再排序
3.隐藏索引
- 使用invisible设置索引为隐藏索引
- 隐藏索引只是不可以见，在查询时不能使用该索引
- 数据新增或者更新时还是会维护该索引
- 主键不能设置为invisible
ALTER TABLE t1 ALTER INDEX idx_c1_c2 INVISIBLE;
- show index查看索引
- explain查看执行计划索引不可用
- 如果想要临时使用隐藏索引，可以设置
set session optimizer_switch="use_invisible_indexes=on"
4.函数索引
- mysql8之前，如果在查询列上加入函数操作，将导致索引失效
- mysql8开始支持在索引中使用函数
- 函数索引基于虚拟列功能实现，相当于增加了一个新列，这个列会根据函数计算结果存储，使用函数索引时就会使用该虚拟列作为索引
- 添加测试数据
create table t3(c1 varchar(10),c2 varchar(10));create index idx_c1 on t3(c1);create index func_idx on t3((UPPER(c2)));insert into t3 (c1,c2) values ('a1','b1'),('a2','b2'),('a3','b3');
- show index查看索引
- explain查看使用索引情况
5.增加select ... for share查询共享锁
mysql8以前仅支持select ... for update查询排它锁
mysql8以后增加select .. for share 查询共享锁
6.跳过锁等待
- 在select ... for update/share 后面允许跟nowait，skip locked等语法
- 当查询行已加锁时，nowait会立即返回报错
- skip locked会立即返回，
- 当表无主键时返回结果为空
-
- 当表有主键时返回未锁定行
7.自适应参数innodb_dedicated_server
- 默认关闭
- 开启后InnoDB根据检测到内存大小自动配置innodb_buffer_pool_size，innodb_log_file_size等参数，
- 会尽可能多的占用系统资源可占用资源提升性能
8.死锁检测控制
- 增加配置innodb_deadlock_detect，用于控制系统是否执行InnoDB死锁检测
- 默认开启
- 死锁检测会消耗数据库性能
- 对于高并发系统，可以关闭死锁检测，提升系统性能
9.undo日志文件独立，不再使用系统表空间
10.binlog日志过期时间精确到秒
- 废弃expire_logs_days参数
- 增加参数binlog_expire_logs_seconds
11.窗口函数
- 测试数据
#创建一张账户余额表
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');
- 聚合函数后跟over函数转换为窗口函数可不再使用group 分组
- over内partition表明分组计算方式
- over中若果不加条件则使用整张表做运算
- 专用窗口函数
- ROW_NUMBER()：为分区中每一行分配一个唯一行号，从1开始
- RANK()：给排序的结果添加序号，需要在over中使用order by语句
- 存在相同排名时，并列为相同排名序号
- 存在相同排名时，下一行会跳过本应占用的排序序号
- DENSE_RANK（）：与RANK()不同的是存在相同排名时，不会跳过本应占用的排名序号
12.默认字符集由latin1变为uft8mb4
- 8.0之前默认字符集是latin1，utf8指向的是uft8mb3
- 8.0版本默认字符集是uft8mb4，utf8默认指向的也是utf8mb4
13.MyISAM系统表全部换成InnoDB表
- 将系统表（mysql库）和数据字典表全部改为InnoDB存储引擎
- 默认的MySQL实例将不包含MyISAM表，除非手动创建MyISAM表
14.元数据存储变动
表结构.frm文件移除，全部集中保存在mysql.ibd中
15.自增变量持久化
- 测试数据
create table t(id int auto_increment primary key,c1 varchar(20));insert into t(c1) values('zhuge1'),('zhuge2'),('zhuge3');
情景一：删除id为3的数据后重启mysql再插入数据，自增id为4而不是3
情景二：将id为1的数据修改id为5，再插入数据，自增id为6而不是5
16.DDL原子化
当一条DDL语句中涉及多个操作时，要么同时成功，要么同时失败
17.参数修改持久化
- mysql8支持在线修改参数并持久化值配置文件mysqld-auto.cnf中
- 重启mysql时，mysqld-auto.cnf中配置优先级最高
