mysql 8.0的新特性

 

• MySQL8中的新增特性

　　MySQL8在功能上的我们需要关注增强主要有：账户与安全方面；系统方面；索引方面；InnoDB方面。

• 　　账户与安全

　　用户的创建与授权

　　用户的创建与授权需要两条单独的SQL语句执行

在MySQL5.7的版本：

> grant all privileges on *.* to '用户名'@'主机' identified by '密码'；

在MySQL8.0需要分开执行：

>create user '用户名'@'主机' identified by '密码'；

>grant all privileges on *.* to '用户名'@'主机'；

用以前的一条命令在8.0里面创建用户，会出现sql语法错误

　　密码管理

MySQL8.0的密码管理策略提供了3个变量：

1、password_history 修改密码不允许与最近几次使用或的密码重复，默认是0，即不限制；

2、password_reuse_interval 修改密码不允许与最近多少天的使用过的密码重复，默认是0,即不限制；

3、password_require_current 修改密码是否需要提供当前的登录密码，默认是OFF,即不需要；如果需要，则设置成ON。

还可针对某一个用户单独设置密码管理策略

alter user '用户名'@'主机' password history 5;

其他

认证插件更新；角色管理也有调整。

索引

隐藏索引，被隐藏的索引不会被优化器使用，但依然真实存在，主要用于软删除，可以根据需要后续真正删除或者重新可视化。我们可以隐藏一个索引，然后观察对数据库的影响。如果数据库性能有所下降，就说明这个索引是有用的，于是将其“恢复显示”即可；如果数据库性能看不出变化，说明这个索引是多余的，可以考虑去除了。当索引被隐藏时，它的内容仍然是和正常索引一样实时更新的，这个特性本身是专门为优化调试使用。如果你长期隐藏一个索引，那还不如干脆删掉，因为毕竟索引的存在会影响插入、更新和删除的性能。

开始真正支持降序索引，以往的MySQL虽然支持降序索引，但是写盘的时候依然是升序保存。MySQL8.0中则是真正的按降序保存。

不再对group by操作进行隐式排序。

索引中可以使用函数表达式，创建表时创建一个函数索引，查询的时候使用同样的函数就可以利用索引了。

• 系统方面

原子ddl操作

MySQL5.7执行drop命令 drop table t1,t2; 如果t1存在，t2不存在，会提示t2表不存在，但是t1表仍然会被删除，MySQL8.0执行同样的drop命令，会提示t2表不存在，而且t1表不会被删除，保证了原子性。

自增列持久化

解决了之前的版本，主键重复的问题。MySQL5.7及其以前的版本，MySQL服务器重启，会重新扫描表的主键最大值，如果之前已经删除过id=100的数据，但是表中当前记录的最大值如果是99，那么经过扫描，下一条记录的id是100，而不是101。MySQL8.0则是每次在变化的时候，都会将自增计数器的最大值写入redo log，同时在每次检查点将其写入引擎私有的系统表。则不会出现自增主键重复的问题。

重构SQL分析器

改进SQL分析器。旧的分析器由于其语法复杂性和自顶向下的分析方式从而有严重的限制，导致难以维护和扩展。

新的系统字典表

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

支持资源管理

支持创建和管理资源组，并允许将服务器运行的线程分配给特定的组，以便线程根据资源组可用的资源执行

更好支持文档型数据库和JSON

1. 聚合函数

JSON_ARRAYAGG(),将多行数据组合成json数组

JSON_OBJECTAGG()，用于生成json对象

1. JSON实用函数

JSON_PRETTY() 输出json数据的时候，格式化。

JSON_STORAGE_SIZE() json数据所占用的存储空间(单位：字节)

JSON_STORAGE_FREE() json数据更新后所释放的空间(单位：字节)

1. JSON合并函数

MySQL8.0废弃了JSON_MERGE()函数，推荐使用以下两个函数合并JSON数据

JSON_MERGE_PATCH()

JSON_MERGE_PRESERV()

1. JSON表函数

MySQL8.0新增了JSON_TABLE()函数，将JSON数据转换成关系表，可以将该函数的返回结果当做一个普通的临时表进行sql查询。

• 支持RANK(), LAG()、NTILE()等函数

• 正则表达式增强

提供了REGEXP_LIKE()，EGEXP_INSTR(), REGEXP_REPLACE(), REGEXP_SUBSTR()等函数

1. 新增备份锁

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

1. 默认字符集

默认字符集由latin1变为utf8mb4

• 配置参数

1. 全局参数持久化

MySQL 8.0版本支持在线修改全局参数持久化，通过加上PERSIST关键字，可以将调整持久化到新的配置文件中，再次重启db还可以应用到最新的参数。

支持会话级别动态调整部分参数

有利于提升语句性能。

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

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

1. 默认参数的调整

调整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

1. InnoDB

1. innodb 增强

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

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

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

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

支持 ALTER TABLESPACE ... RENAME TO 语法

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

新增INFORMATION_SCHEMA.INNODB_TABLESPACES_BRIEF视图

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

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

InnoDB性能提升

1. 并发

废除buffer pool mutex,将原来一个mutex拆分成多个，提高并发

拆分LOCK_thd_list 和 LOCK_thd_remove 这两个mutex，大约可提高线程链接效率5%。

1. 行缓存

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

1. 改进扫描性能

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

1. 改进成本模型

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

1. 废弃特性

移除了一些功能，例如query cache