【技术分享】浅谈MYSQL 8.0新特性

1. 新的系统字典表

整合了存储有关数据库对象信息的事务数据字典，所有的元数据都用InnoDB引擎进行存储。

2. 支持DDL 原子操作

InnoDB表的DDL支持事务完整性，要么成功，要么回滚。将DDL操作回滚日志写入到data dictionary 数据字典表 mysql.innodb_ddl_log 中，用于回滚操作。

3. 安全和用户管理

1) 新增caching_sha2_password认证插件，并且是默认的身份认证插件。性能和安全方面加强权限支持role。

2) 支持密码过期策略，需要周期性修改密码。

设置密码永不过期：

alter user test_mysql password expire never;

增加历史密码检测机制，防止总是几个密码反复使用,检测最近的几次密码。

3) 需要提供旧密码才能修改新密码，防止被篡改。

4) 支持双密码机制，可以选择采用主密码还是第二密码连接，安全性更高。

5) 增加密码强度约束，避免使用弱密码。

6) 支持生成随机密码。

4. innodb 增强

1) 在8.0之前的版本，自增值是保存在内存中,自增主键AUTO_INCREMENT的值如果大于max(primary key)+1，在MySQL重启后，会重置AUTO_INCREMENT=max(primary key)+1。这种现象在某些情况下会导致业务主键冲突或者其他难以发现的问题。自增主键重启重置的问题很早就被发现，一直到8.0才被解决。8.0版本将会对AUTO_INCREMENT值进行持久化，MySQL重启后，该值将不会改变。MySQL server重启后不再取消AUTO_INCREMENT = N表选项的效果。如果将自增计数器初始化为特定值，或者将自动递增计数器值更改为更大的值，新的值被持久化，即使服务器重启。

2) 新增INFORMATION_SCHEMA.INNODB_CACHED_INDEXES，查看每个索引缓存在InnoDB缓冲池中的索引页数。

3) InnoDB临时表都将在共享临时表空间ibtmp1中创建。

4) 对于SELECT ... FOR SHARE和SELECT ... FOR UPDATE语句，InnoDB支持NOWAIT和SKIP LOCKED。

5) innodb_undo_tablespaces的最小值为2，并且不再允许将innodb_undo_tablespaces设置为0。最小值2确保回滚段始终在撤消表空间中创建，而不是在系统表空间中创建。

6) 支持 ALTER TABLESPACE ... RENAME TO 语法。

7) 新增innodb_dedicated_server，让InnoDB根据服务器上检测到的内存量自动配置innodb_buffer_pool_size，innodb_log_file_size，innodb_flush_method。

单机多实例场景、或者MySQL和Redis等应用混布的情况下不建议使用，单机单实例场景下建议使用。

buffer pool配置规则：

当检测到的机器内存<1GB时，buffer_pool_size设置为128MB；

当检测到的机器内存在1GB~4GB之间时，buffer_pool_size设置为机器内存*0.5；

当检测到的机器内存大于4GB时，buffer_pool_size设置为机器内存*0.75。

8.0.14之后，log file配置规则：

buffer pool小于8G，则log file配置为512MB；

buffer pool在8G~128G之间，则log file配置为1024MB；

buffer pool大于128G，则logfile配置为2048MB。

8.0.14之前，log file配置规则：

系统内存<1G，log file配置为48MB；

系统内存<4G，log file配置为128MB；

系统内存<8G，log file配置为512MB；

系统内存<16G，log file配置为1024MB；

系统内存>16G，log file配置为2048MB。

redo 日志组内的日志个数 innodb_log_files_in_group：

当buffer pool<8GB，log file个数为round(buffer pool size)，如果结果小于2，则设置为2；

当buffer pool在8G~128G，log file个数为round(0.75* buffer pool size)；

当buffer pool大于128G，则log file个数为64个。

8) 新增INFORMATION_SCHEMA.INNODB_TABLESPACES_BRIEF视图。

9) 新增了动态配置项 innodb_deadlock_detect，用来禁用死锁检查，因为在高并发系统中，当大量线程等待同一个锁时，死锁检查会大大拖慢数据库。

10) 支持使用innodb_directories选项在服务器脱机时将表空间文件移动或恢复到新位置。

5. 优化

不可见索引，开始支持invisible index(感觉又和Oracle一样了)，在优化SQL的过程中可以设置索引为不可见，优化器变不会利用不可见索引。支持降序索引，可以对索引定义 DESC，之前，索引可以被反序扫描，但影响性能，而降序索引就可以高效的完成。

6. 强制每个表都要有主键

sql_require_primary_key参数控制

如果该参数设为on，创建表的时候没有主键，MySQL会直接报错，并给出提示。如下：

mysql> create table testc (id int);

ERROR 3750 (HY000): Unable to create

or change a table without a primary key,

 when the system variable 'sql_require_primary_key' is set.

 Add a primary key to the table

or unset this variable to avoid this message.

 Note that tables without a primary key

7. 新增备份锁

允许在线备份期间的DML，同时防止可能导致快照不一致的操作。备份锁由LOCK INSTANCE FOR BACKUP和UNLOCK INSTANCE语法支持。

8. 默认字符集

默认字符集由latin1变为utf8mb4。

9. 配置文件增强

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

set PERSIST  expire_logs_days=10 ; #内存和json文件都修改，重启还生效。

set GLOBAL  expire_logs_days=10 ; #只修改内存，重启丢失。

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

{ "mysql_server": {"expire_logs_days": "10" } }

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

10. 直方图

MySQL 8.0 版本开始支持期待已久直方图。优化器会利用column_statistics的数据，判断字段的值的分布，得到更准确的执行计划。可以使用 ANALYZE TABLE table_name [UPDATE HISTOGRAM on col_name with N BUCKETS |DROP HISTOGRAM ON  clo_name]来收集或者删除直方图信息。

11. 会话参数的调整

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

select *+ SET_VAR(sort_buffer_size = 16M) */ id  from test order id ;

insert  /*+ SET_VAR(foreign_key_checks=OFF) */ into test(name) values(1);

12. 默认参数的调整

调整back_log的默认值，保持和 max_connections一致，增强突发流量带来的连接处理容量。

修改 event_scheduler 默认为ON，之前默认是关闭的。

调整max_allowed_packet 的默认值，从4M增加到64M。

调整bin_log,log_slave_updates默认值为on。

调整expire_logs_days的过期时间为30天，老版本是7天，生产环境时，检查该参数，防止binlog过多造成空间紧张。

调整innodb_undo_log_truncate 默认为ON。

调整innodb_undo_tablespaces 默认值为2。

调整innodb_max_dirty_pages_pct

_lwm 默认值10。

调整innodb_max_dirty_pages_pct默认值为90。

新增innodb_autoinc_lock_mode 默认值为2。

13. InnoDB性能提升

废除buffer pool mutex,将原来一个mutex拆分成多个，提高并发。拆分LOCK_thd_list 和 LOCK_thd_remove 这两个mutex，大约可提高线程链接效率5%。

14. 行缓存

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

15. 改进扫描性能

改进InnoDB范围查询的性能，可提升全表查询和范围查询 5-20%的性能。

16. 成本模型

InnoDB缓冲区可以估算缓存区中的有多少表和索引，这可以让优化器选择访问方式时知道数据是否可以存储在内存中还是必须存储到磁盘上。

17. mysqldump和mysqlpump备份

之前版本中，使用-all-databases参数导出数据的时候，不加--routines、--triggers和--events参数，也可以导出触发器和存储过程等信息，因为这些信息都在proc和event表中，现在不行了，需要加上--event和--routines选项才能导出存储过程和触发器。之前版本中--routines选项导出时候，备份账户需要有proc表的select权限，现在8.0需要有所有表的select权限。之前版本中导出触发器、处处过程可以同时导出其创建和修改的时间戳，8.0版本以后不可以了。

18. DDL操作会耗时加长

旧版本中是直接对.frm文件进行更改操作，只写一个文件，新版本中需要更新数据字典表，需要将数据写到存储引擎、redo log 、undo log中。

19. 撤销远程状态下的skip-grant-tables

--skip-grant-tables参数使用后，不能通过远程连接MySQL，而只能通过本地进行连接。

废弃validate_password 插件。

废弃ALTER TABLESPACE和DROP TABLESPACE ENGINE子句。

废弃JSON_MERGE()  -> JSON_MERGE_PRESERVE()取代。

废弃 have_query_cache 系统变量。

query cache功能被移除，相关的系统变量也被移除。

mysql_install_db被mysqld --initialize or --initialize-insecure替代。

INFORMATION_SCHEM下的INNODB_LOCKS和INNODB_LOCK_WAITS表已被删除，用Performance Schema data_locks和data_lock_waits表替代。

INFORMATION_SCHEMA下的四张表移除：GLOBAL_VARIABLES、SESSION_VARIABLES、GLOBAL_STATUS、SESSION_STATUS。

InnoDB不再支持压缩的临时表。

不再支持 PROCEDURE ANALYSE()语法。

总的来说Mysql 8.0对比Mysql 5.7做了不小的升级和改进，大家通过阅读这篇文章，可以对Mysql 8.0的新特性有所了解，希望对大家未来工作有所帮助。