MySQL 8.0 新特性

1、新增降序索引

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

2、group by 不再隐式排序，如果要排序需要手动增加 order by

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

3、利用 invisible 关键字，增加隐藏索引

create table t2(c1 int, c2 int, index idx_c1(c1), index idx_c2(c2) invisible);

其中 c2 字段为隐藏索引，即这个索引存在，MySQL 也会维护这个索引，但是优化器不会选择 c2 索引，即使使用 force index，优化器也不会选择。主键不能设置为隐藏索引。

如果要使用这个索引，可以把这个索引恢复成可见。

alter table t2 alter index idx_c2 visible;

4、新增函数索引

create index func_idx on t3((UPPER(c2)))

把 c2 字段转为大写的值之后构建索引。

5、InnoDB 存储引擎 select … for update 跳过锁等待

select * from t1 where c1 = 2 for update nowait;

如果获取不到锁立马返回报错信息。

select * from t1 for update skip locked;

如果 c1 = 2 这条数据被锁住了，会排出这条数据，返回其他的数据。

6、死锁检查控制

show variables like '%innodb_deadlock_detect%';

默认是打开的。

7、binlog 过期时间精确到秒
之前通过设置 expire_logs_days 参数，控制过期时间是几天。在 8.0 版本，默认使用 binlog_expire_logs_seconds 这个参数控制过期时间。

8、窗口函数
在聚合函数后面加上 over() 就变成窗口函数了

mysql> SELECT * from users u 
id|username|balance|
--+--------+-------+
 1|lisi    |     20|
 2|zhangsan|     10|
 3|wangwu  |     30|
 4|lisi    |     90|
 5|lisi    |     30|
 
 
mysql> SELECT username,sum(balance) from users u group by username 
username|sum(balance)|
--------+------------+
lisi    |         140|
zhangsan|          10|
wangwu  |          30|


mysql> SELECT username, sum(balance) over(PARTITION by username) as balances from users 
username|balances|
--------+--------+
lisi    |     140|
lisi    |     140|
lisi    |     140|
wangwu  |      30|
zhangsan|      10|


mysql> SELECT username, balance ,sum(balance) over(PARTITION by username) as balances from users 
username|balance|balances|
--------+-------+--------+
lisi    |     20|     140|
lisi    |     90|     140|
lisi    |     30|     140|
wangwu  |     30|      30|
zhangsan|     10|      10|


mysql> SELECT username, balance, SUM(balance) over() FROM  users 
-- 如果 over 括号内为空，计算所有数据的总和
username|balance|SUM(balance) over()|
--------+-------+-------------------+
lisi    |     20|                180|
zhangsan|     10|                180|
wangwu  |     30|                180|
lisi    |     90|                180|
lisi    |     30|                180|

mysql> SELECT username, balance, AVG(balance) over(PARTITION by username) as balance_avg FROM users
username|balance|balance_avg|
--------+-------+-----------+
lisi    |     20|    46.6667|
lisi    |     90|    46.6667|
lisi    |     30|    46.6667|
wangwu  |     30|    30.0000|
zhangsan|     10|    10.0000|

可以发现 PARTITION by username 代替了 group by name，并且可以保留表的原有结构。

9、自增变量持久化
之前的版本对于自增主键，如果删除一行数据，重启后，再插入一行数据，新的数据的主键值是删除那行数据的主键值，即如果删除那行主键值是3，新插入这行数据的主键值也是3。

更新之后，同样的情况，新的数据的主键值是删除那行数据主键值加1，即这行数据的主键值是4。

10、DDL 原子化
之前的版本同时删除两张表 t1, t2，但是 t2 不存在，执行 delete 语句后会报错，但会把 t1 这张表删除了。

更新之后 t1 这张表也不会删除。

可以实现原子化是因为在 8.0 版本，系统表(mysql)和数据字典表全部改为InnoDB存储引擎，支持可事务。

11、利用 persist 关键字实现参数修改持久化

mysql> set persist innodb_lock_wait_timeout=25;
系统会在数据目录下生成一个包含json格式的mysqld-auto.cnf 的文件，格式化后如下所示，当my.cnf 和mysqld-auto.cnf 同时存在时，后者具有更高优先级。