关于mysql8.0新特性的一些简单总结

在前面的文章中分享了一些mysql升级到8.0.x的一些实际做法，这里对mysql8.0.x的一些新特性或者是改良优化的地方做一些简单总结，可能涉及的方面不全或者个人理解不一样，见谅

參考参考官网：MySQL ：： MySQL 8.0 参考手册https://dev.mysql.com/doc/refman/8.0/en/

1、隐藏索引

隐藏索引的特性对于性能调试非常有用。在 8.0 中，索引可以被“隐藏”和“显示”。当一个索引隐藏时，它不会被查询优化器所使用。

也就是说可以隐藏一个索引，然后观察对数据库的影响。如果数据库性能有所下降，就说明这个索引是有用的，于是将其“恢复显示”即可；如果数据库性能看不出变化，说明这个索引是多余的，可以删掉了。

隐藏一个索引的语法是：

ALTER TABLE t ALTER INDEX i INVISIBLE;

恢复显示该索引的语法是：

ALTER TABLE t ALTER INDEX i VISIBLE;

当一个索引被隐藏时，我们可以从 show index 命令的输出中看到，该索引的 Visible 属性值为 NO。也就是说这个索引其实是存在的。

注意：当索引被隐藏时，它的内容仍然是和正常索引一样实时更新的，这个特性本身是专门为优化调试使用。如果你长期隐藏一个索引，那还不如干脆删掉，因为毕竟索引的存在会影响插入、更新和删除的性能。

2、UTF-8 编码

 从 MySQL 8 开始，数据库的缺省编码将改为 utf8mb4（之前是latin）这个编码包含了所有 emoji 字符

3、通用表表达式CTE

复杂的查询会使用嵌入式表，例如：

SELECT t1.*, t2.* FROM
 (SELECT col1 FROM table1) t1,
 (SELECT col2 FROM table2) t2;

 而有了 CTE，我们可以这样写：

WITH
 t1 AS (SELECT col1 FROM table1),
 t2 AS (SELECT col2 FROM table2)
SELECT t1.*, t2.* 
FROM t1, t2;

 并且还支持了递归相关的语法。

4、窗口函数（Window Functions）

当需要在查询当中实现排名时，必须手写 @ 变量。但是从 8.0 开始，MySQL 新增了一个叫窗口函数的概念，它可以用来实现若干新的查询方式。

窗口函数有点像是 SUM()、COUNT() 那样的集合函数，但它并不会将多行查询结果合并为一行，而是将结果放回多行当中。也就是说，窗口函数是不需要 GROUP BY 的。

 

select *,rank() over w as `rank` from users window w as (order by u_count);

 说明：在这里创建了名为 w 的 window，规定它对 u_count 字段进行排序，然后在 select 子句中对 w 执行 rank() 方法，将结果输出为 rank 字段。

注：但是感觉日常开发中，我们更习惯是先进行查询然后自己排序后通过后台程序给一个rank值，这个功能用处暂时不咋看到。

5、秒级加列 

• 只改数据字典表元数据信息
• 5.7和8.0 sbteset1表数据分别为3000w

# mysql8.0.18
mysql> alter table sbtest1 add str varchar(200) not null default 'mysql8.0 新加字段';
Query OK, 0 rows affected (0.13 sec)
Records: 0  Duplicates: 0  Warnings: 0

# mysql5.7.16
mysql>  alter table sbtest1 add str varchar(200) not null default 'mysql5.7 新加字段';
Query OK, 0 rows affected (6 min 8.36 sec)
Records: 0  Duplicates: 0  Warnings: 0

 以前表加列操作需要重建表（消耗大量的IO资源和时间），现在可以快速的增加字段，只修改数据字典，而不用修改表中的记录本身。所以8.0加列没有这个步骤。秒级加列(不要指定列位置，如after str1)

6、性能提升 

有一些对于查询或者统计相关的速度优化。

# mysql5.7.16
mysql> select count(*) from sbtest1;
+----------+
| count(*) |
+----------+
| 29991137 |
+----------+
1 row in set (3 min 12.24 sec)

# mysql8.0.18
mysql> select count(*) from sbtest1;
+----------+
| count(*) |
+----------+
| 30000000 |
+----------+
1 row in set (45.70 sec)

 分别在只更新和只读场景下，8.0相比5.7在高并发时性能提升近1倍；求表总数据量8.0响应时间也将近提升5倍

7、Json加强

在Json上，8.0 增加了更多的功能性。支持更新JSON中部分字段

8、查询lock优化 

• SKIP LOCKED

 需要加锁的记录若被其它线程占有锁，则跳过，而不是等待

select * from tx where c1=12 for update skip locked;
Empty set (0.00 sec)

• NOWAIT需要加锁的记录有锁则报错

 需要加锁的记录有锁则报错

select * from tx where c1=12 for update nowait;
ERROR 3572 (HY000): Statement aborted because lock(s) could not be acquired immediately and NOWAIT is set.

其实这个特性感觉对死锁或者锁循环导致一直阻塞会有用，还有在一些秒杀等大流量场景也可以

9、角色管理 

角色可以认为是一些权限的集合，为用户赋予统一的角色，权限的修改直接通过角色来进行，无需为每个用户单独授权。

10、函数索引 

MySQL 8.0.13 以及更高版本支持函数索引（functional key parts），也就是将表达式的值作为索引的内容，而不是列值或列值前缀。 将函数作为索引键可以用于索引那些没有在表中直接存储的内容。

其实MySQL5.7中推出了虚拟列的功能，而MySQL8.0的函数索引也是依据虚拟列来实现的。

• 只有那些能够用于计算列的函数才能够用于创建函数索引。
• 函数索引中不允许使用子查询、参数、变量、存储函数以及自定义函数。
• SPATIAL 索引和 FULLTEXT 索引不支持函数索引。

11、备份锁 

在MySQL 8.0中，引入了一个轻量级的备份锁，这个锁可以保证备份一致性，而且阻塞的操作相对比较少，是一个非常重要的新特性。

在MySQL 8.0中，为了解决备份FTWRL的问题，引入了轻量级的备份锁；可以通过LOCK INSTANCE FOR BACKUP和UNLOCK INSTANCE，以获取和释放备份锁，执行该语句需要BACKUP_ADMIN权限。

backup lock不会阻塞读写操作。不过，backup lock会阻塞大部分DDL操作，包括创建/删除表、加/减字段、增/删索引、optimize/analyze/repair table等。

总的来说，备份锁还是非常实用的，毕竟其不会影响业务的正常读写；至于备份锁和DDL操作的冲突，还是有很多方法可以避免，比如错开备份和变更的时间、通过pt-online-schema-change/gh-ost避免长时间阻塞等等。随着备份锁的引入，Oracle官方备份工具MEB 8.0和Percona开源备份工具XtraBackup 8.0，也是更新了对backup lock的支持。

 

12、redo优化 

mysql8.0一个新特性就是redo log提交的无锁化。在8.0以前，各个用户线程都是通过互斥量竞争，串行的写log buffer，因此能保证lsn的顺序无间隔增长。

mysql8.0通过redo log无锁化，解决了用户线程写redo log时竞争锁带来的性能影响。同时将redo log写文件、redo log刷盘从用户线程中剥离出来，抽成单独的线程，用户线程只负责将redo log写入到log buffer，不再关心redo log的落盘细节，只需等待log_writer线程或log_flusher线程的通知。

13、 关闭Query Cache

从 MySQL 8.0开始，不再使用查询缓存（Query Cache）。

随着技术的进步，经过时间的考验，MySQL的工程团队发现启用缓存的好处并不多。

首先，查询缓存的效果取决于缓存的命中率，只有命中缓存的查询效果才能有改善，因此无法预测其性能。

其次，查询缓存的另一个大问题是它受到单个互斥锁的保护。在具有多个内核的服务器上，大量查询会导致大量的互斥锁争用。

MySQL8.0取消查询缓存的另外一个原因是，研究表明，缓存越靠近客户端，获得的好处越大。MySQL8.0新增加了一些其他对性能干预的工具来支持。另外，还有像ProxySQL这样的第三方工具，也可以充当中间缓存。

剩余的还有一些例如用户修改密码，安全性保障等等，GIS信息的优化等