MySQL 8.0备受瞩目的新特性大放送！

2017 年 10 月 31 日 DBAplus社群 杨奇龙

作者介绍

杨奇龙，目前就职于有赞科技，负责数据库运维工作，熟悉MySQL性能优化、故障诊断、性能压测。

MySQL于 2016-09-12正式发布8.0 DM 版本到现在的 8.0.3 RC版本,进度还是蛮快的。对Oracle比较了解的朋友看到最近的MySQL 8.0的功能，会有一致的想法：在Oracle的带领下MySQL越来越像Oracle，包括安装包也越来越大。

本文从字符集、数据库管理、性能优化、安全等方面为大家汇总介绍 8.0 版本的比较吸引人的新特性，详细的新的功能特性还需要去官方文档查看。

新的系统字典表

由于MySQL采用Server层和插件式存储引擎层的架构模式，在Server层MySQL为每个表创建frm文件来保存表定义相关的元数据信息。

然而部分存储引擎(比如InnoDB)本身也会存储元数据，这样不仅产生了元数据冗余，而且由于Server层和引擎层各自管理表的元数据，在执行DDL类似的操作时，遇到异常情况，系统很难做到crash-safe，DDL操作也做不到原子性。

从MySQL 8.0 开始采用新的Data Dictionary 系统，所有的元数据都用InnoDB引擎进行存储，使用新的系统带来如下好处：

解决DDL的原子性问题。
支持事务型的字典存储系统和crash safe 。
去掉基于文件的元数据存储，比如表定义文件frm文件及其他server层的元数据文件(frm, par, trn, trg,isl, db.opt)
支持使用统一集中的缓存系统来缓存数据字典信息。

新的Information Schema定义

MySQL 8.0版本使用视图重构I_S 库中的表，隐藏了系统表，通过查询物理表的方式避免了为I_S查询创建临时表(临时表的操作包含了server层创建frm, 引擎层获取数据or需要锁保护的全局数据)，查询性能大幅度提升。

有兴趣的朋友可以阅读官方团队的文章新的IS系统带来的性能提升。

http://mysqlserverteam.com/mysql-8-0-scaling-and-performance-of-information_schema/?spm=5176.100239.blogcont61286.10.f8hlVB
http://mysqlserverteam.com/mysql-8-0-improvements-to-information_schema/?spm=5176.100239.blogcont61286.9.f8hlVB

角色管理

增加角色role，看到这里Oracle DBA朋友是不是觉得似曾相识。角色是一些列权限的集合，可以给某个用户授予和回收角色，使用角色可以更方便进行权限管理。参考一个简单的例子：

创建role

CREATE ROLE 'app_read', 'app_write';

给role赋权

GRANT SELECT ON app_db.* TO 'app_read';

GRANT INSERT, UPDATE, DELETE ON app_db.* TO 'app_write';

创建用户

CREATE USER 'ro_user'@'localhost' IDENTIFIED BY 'read_user1pass';

CREATE USER 'rw_user'@'localhost' IDENTIFIED BY 'rw_user1pass';

给用户赋予角色

GRANT 'app_read' TO 'ro_user'@'localhost';

GRANT 'app_read','app_write' TO 'rw_user'@'localhost'; ##将两种角色的权限赋予账号

说点自己的关于role的想法，对于精细化管理的MySQL运维系统会基于业务来区分权限，role的用处可能用处不大。但是对于很多库混合在一起的实例场景，又有不同的开发同学需要申请个人账户访问db，可以通过role类维护权限分配。

配置文件

MySQL 8.0版本支持在线修改全局参数持久化，通过加上PERSIST关键字，可以将调整持久化到新的配置文件中，再次重启db还可以应用到最新的参数。对于加上 PERSIST 关键字修改参数命令，MySQL系统会生成一个包含json格式数据的 mysqld-auto.cnf 文件，比如执行：

set globa sync_binlog=1;

系统会生成一个包含如下内容的 mysqld-auto.cnf 的文件：

{ "mysql_server": {"sync_binlog": "1" } }

当 my.cnf 和 mysqld-auto.cnf 同时存在时，后者具有高优先级。

自增列优化

其实这个不算是新特性，而是解决了之前一个历史悠久的bug(bugid为199).MySQL 8.0 版本之前InnoDB初始化AUTO_INCREMENT的方式:在DB重启时，MySQL会将表上最大的自增值作为最大值，下次分配是分配max(id)+1,如果是归档表或者其它模式删除数据之后，DB系统重启，自增值可能被重用。

8.0 版本不再重置auto_increment 的值，直接复用重启之前的最大值，具体解决方法请移步WL#7816

http://dev.mysql.com/worklog/task/?spm=5176.100239.blogcont60885.10.7NgPbk&id=7816

InnoDB 优化

1、直方图

MySQL 8.0 版本开始支持期待已久直方图。优化器会利用存储在I_S的column_statistics的数据，判断字段的值的分布，得到更准确的执行计划。我们可以使用

ANALYZE TABLE table_name [UPDATE HISTOGRAM|DROP HISTOGRAM]

来收集或者删除直方图信息。

2、不可见索引

开始支持invisible index，(是不是感觉又和Oracle一样了)，在优化SQL的过程中可以设置索引为不可见，优化器变不会利用不可见索引。另外如果对于具有历史原因的表，不清楚到底用不用索引，可以设置索引为不可见，观察一段时间再将索引下线。使用该功能的时候记得检查optimizer_switch中use_invisible_indexes参数是否为off。如果是on 优化器在优化的时候会依然使用不可见索引。

3、支持会话级别SET_VAR 动态调整部分参数，有利于提升语句性能。

select /*+ SET_VAR(sort_buffer_size = 16M) */ name from people order y name;

insert /*+ SET_VAR(foreign_key_checks=OFF) */ into t2 values(2);

拆或者删除mutex锁

对MySQL 技术发展比较了解的朋友肯定知道解决高并发的问题常用的手段就是“拆锁和移除锁”。 MySQL 8.0版本使用多个更细粒度的mutex代替buffer pool mutex这把大锁。

具体的替换方式如下：

- LRU_list_mutex for the LRU_list;

- zip_free mutex for the zip_free arrays;

- zip_hash mutex for the zip_hash hash and in_zip_hash flag;

- free_list_mutex for the free_list and withdraw list.

通过对锁的拆分，降低了全局锁的竞争，提升了申请buffer pool的并发处理能力。

行缓存

MySQL 8.0 版本的优化器可以估算将要读取的行数，因此可以提供给存储引擎一个合适大小的row buffer来存储需要的数据。大批量的连续数据扫描的性能将受益于更大的record buffer。

临时表

取消对压缩临时表的支持，并存储临时表的元数据到内存中，提高性能。

字符集

默认字符集由 latin1 修改为 utf8mb4,并增加支持 Unicode 9 ,字符集校验相应的修改为utf8mb4_800_ci_ai (大小写不敏感)。

另外一个大小写敏感的校验字符集是utf8mb4_ru_0900_as_cs。

默认参数

修改back_log的默认值，保持和 max_connections一致，增强突发流量带来的连接处理容量。
修改 event_scheduler 默认为ON，之前默认是关闭的。看来MySQL以后推荐大家使用任务job 这个功能了，对于规模化运维需要有一套平台工具为做管理，否则人员的流动会带来稳定性上面的风险。
调整max_allowed_packet 的默认值，从4M增加到64M，其实推荐512M 并且朱备库一定要保持一致，否则异常断电的情况下会报1236错误。
调整bin_log,log_slaves_update默认值为on。
增加binlog的过期时间为30天，老版本是7天，以后上生产环境时，请务必检查该参数，防止binlog过多造成空间紧张。