MySQL8.0新特性学习笔记（六）：新特性介绍

目录

一，redo log重构

二，原子DDL

三，直方图

四，Hash Join

五，降序索引

六，隐藏索引

七，InnoDB读锁优化

八，窗口函数

九，新的优化器提示 SET_VAR

十，字符集优化

十一，binlog复制优化

十二，持久化AUTO_INCREMENT值

十三，JSON语法

---

19年10月14日MySQL发布了8.0.18版本，GA稳定版，介绍一下MySQL8.0中出现的新玩意和新修改。

一，redo log重构

redo log是重做日志，提供前滚操作，他是innodb事务日志的一部分，另一部分是undo log，提供回滚操作。

redo log是一种物理日志，记录的是数据页的物理修改，而undo是行记录。

redo log分为两部分，一是写入内存的日志，二是从内存写入磁盘的日志。

在8.0版本以前，这两部分日志在写入时都需要加锁，也是性能瓶颈所在，而MySQL8.0版本去掉了这个锁，采用异步操作，用户线程只需要把日志写入缓存，刷盘的操作由其他的线程完成，提高了整体的运行效率。

 

二，原子DDL

简洁的来看就是这样，有一个sql：

DROP TABLE table1,table2;

如果删除了table1之后服务挂了，

8.0之前：table1成功删除，table2没有删除。非原子操作。

8.0之后：table1和table2都不会删除。原子操作。

 

MySQL8.0的原子DDL包括了这些命令：

• DROP TABLES
• DROP SCHEMA
• CREATE TABLE
• TRUNCATE TABLE
• RENAME TABLES
• ALTER TABLE

8.0版本原子DDL的实现得益于MySQL临时文件系统的调整，在DDL过程中可能用到的临时文件都被整合到了一个文件中：

8.0之前：系统层的数据字典文件（.frm .trg .opt等），innodb层的元数据文件（表信息等），分开存储，二者可能会出现不一致的情况。

8.0之后：都在innoDB的元数据文件中。DDL日志写入mysql.innodb_ddl_log表。

在此基础上，Mysql把DDL操作中的数据字典修改，innoDB层的修改，写binlog等操作合成了一个事务，保证了原子性。

暂时只有innoDB引擎支持原子DDL。

 

三，直方图

MySQL中的直方图实际上是对表的某个字段数据分布的统计，借此可以向查询优化器提供更精确的字段信息，帮助优化器得到更优的查询计划。

详细介绍请看这一篇：

MySQL8.0新特性学习笔记（三）：直方图

 

四，Hash Join

Hash Join可以在被驱动表没有索引的情况下进行快速的连接并查询。

大表连接时效果更好。

详细介绍请看这一篇：

MySQL8.0新特性学习笔记（四）： Hash Join

 

五，降序索引

降序索引出现之前：

MySQL8.0开始真正支持降序索引，之所以说是真正，是因为相关的语法在之前的版本中就出现了，像这样的：

ALTER TABLE `tableA` ADD INDEX `indexA` (`columnA ` desc) ;

但是实际上8.0之前MySQL会忽略这个desc的定义，依然创建一个普通的升序索引。

对于单个字段的索引，升序索引和降序索引其实差不多，因为索引的数据结构是个B+树，一个升序索引倒着查询就能当降序索引用，效率比正着查低一点。

 

降序索引的意义：

引入降序索引后，降序查询的效率和正序可以一致了。

降序索引的另一项重要意义在于联合索引，可以应对一个sql中有的字段升序排列有的字段降序排列的场景。

 

举个例子：

当我们建立一个包含两个字段的联合索引：

ALTER TABLE `tableA` ADD INDEX `indexA` (`columnA`, `columnB`) ;

显然索引中的两个字段都是升序索引，那么我们使用下面的sql查询：

select * from `tableA` order by `columnA` asc, `columnB` desc;

上面建立的索引会被用到，MySQL会给columnB做一个排序操作，优化器会在Extra字段中展示：

Using index; Using filesort。

所以，如果我们对columnA使用升序索引，columnB使用降序索引：

ALTER TABLE `tableA` ADD INDEX `indexA` (`columnA`, `columnB` desc) ;

再次执行上面的查询语句，MySQL8.0就不会再进行排序了，explain之后的结果里Extra字段也只会有：

Using index

 

注意：

暂时只有InnoDB引擎支持降序索引。

降序索引只能用于BTREE索引，不能用于Hash索引。

 

六，隐藏索引

所谓隐藏索引，意思就是建了索引之后可以设为隐藏，不让查询优化器用。

设置隐藏索引和非隐藏索引使用的是INVISIBLE和VISIBLE关键字。

隐藏索引可以用来测试索引效率，比不断新增删除索引效率高的多。

主键索引不能被设为隐藏。

当一个表没有显式定义主键索引，而且有一个NOT NULL的列有UNIQUE索引时，第一个这种UNIQUE索引不能被设为隐藏索引：

CREATE TABLE t2 ( i INT NOT NULL, j INT NOT NULL, UNIQUE j_idx (j) ) ENGINE = InnoDB; mysql> ALTER TABLE t2 ALTER INDEX j_idx INVISIBLE; ERROR 3522 (HY000): A primary key index cannot be invisible.

把主键索引设为隐藏索引时也会有这个提示。

上面的场景，先给表加个主键索引，然后这个UNIQUE索引就可以被设为隐藏了。

 

建表语句中的sql是这样的：

CREATE TABLE t1 ( i INT, j INT, k INT, INDEX i_idx (i) INVISIBLE ) ENGINE = InnoDB; CREATE INDEX j_idx ON t1 (j) INVISIBLE; ALTER TABLE t1 ADD INDEX k_idx (k) INVISIBLE;

修改表语句中的sql是这样的：

ALTER TABLE t1 ALTER INDEX i_idx INVISIBLE; ALTER TABLE t1 ALTER INDEX i_idx VISIBLE;

查看索引是否是隐藏的，可以从INFORMATION_SCHEMA.STATISTICS表中获取，或从SHOW INDEX获取：

mysql> SELECT INDEX_NAME, IS_VISIBLE FROM INFORMATION_SCHEMA.STATISTICS WHERE TABLE_SCHEMA = 'db1' AND TABLE_NAME = 't1'; +------------+------------+ | INDEX_NAME | IS_VISIBLE | +------------+------------+ | i_idx | YES | | j_idx | NO | | k_idx | NO | +------------+------------+

系统变量optimizer_switch中有个use_invisible_indexes参数，默认为off，当他被设置为on时，即使索引被设为隐藏的，优化器也会使用这个索引。

 

七，InnoDB读锁优化

MySQL8.0的读锁优化涉及两组概念：

1，SELECT FOR SHARE和SELECT FOR UPDATE

2，NOWAIT 和SKIP LOCKED

下面分别介绍。

 

SELECT FOR SHARE和SELECT FOR UPDATE

SELECT FOR SHARE是共享锁的查询，此查询的行不可以被别的事务修改，但是别的事务可以查询这些行。这种写法可以替换之前的SELECT LOCK IN SHARE MODE，其实功能是一样的，不过这种查询可以和NOWAIT 和SKIP LOCKED一起使用。

SELECT FOR UPDATE是排他锁的查询，此查询的行不可以被别的事务查询或修改。这种语法在之前的MySQL版本中就存在。这种查询可以和NOWAIT 和SKIP LOCKED一起使用。

注：

如果子查询的中没写SELECT FOR UPDATE，那么子查询中的行不会被锁，比如：

SELECT * FROM t1 WHERE c1 = (SELECT c1 FROM t2) FOR UPDATE;

这个sql中t2表的行不会被锁。

 

NOWAIT 和SKIP LOCKED

MySQL8.0新增的语法，这两种语法是和SELECT FOR SHARE或SELECT FOR UPDATE一起使用的。

在不NOWAIT 和SKIP LOCKED的情况下，被SELECT FOR SHARE或SELECT FOR UPDATE锁定的行是不能被其他事务用SELECT FOR SHARE或SELECT FOR UPDATE语法再次锁定的，而NOWAIT 和SKIP LOCKED这两种语法提供了其他的解决方案：

NOWAIT ：如果查询结果中有被锁定的行，则直接返回错误。此方法不存在锁的等待。

SKIP LOCKED，如果查询结果中有被锁定的行，则忽略这些被锁定的行，返回那些没被锁定的行。此方法不存在锁的等待。

注：

NOWAIT 和SKIP LOCKED只对行级锁有效。

 

八，窗口函数

MySQL8.0引入的窗口函数，可以比较方便的实现一些分析和统计功能，这些功能不用窗口函数也能实现，不过可能sql会比较复杂。

详细介绍请见此文：

MySQL8.0新特性学习笔记（二）：窗口函数

 

九，新的优化器提示 SET_VAR

MySQL8.0.3加入的SET_VAR，是一种新的优化器提示，可以在一条sql中修改session级的系统变量，sql执行完后系统变量就会恢复。

这是一种注释模式的优化器提示，这种模式在注释中的第一个字符必须是加号+。

如果我们只想在一个sql中使用不同的系统变量值，在以前是这样执行的：

SET @old_optimizer_switch = @@optimizer_switch; SET optimizer_switch = 'mrr_cost_based=off'; SELECT * FROM t1; SET optimizer_switch = @old_optimizer_switch;

使用SET_VAR就是这样的：

SELECT /*+ SET_VAR(optimizer_switch = 'mrr_cost_based=off') */ * FROM t1;

显然sql简洁了很多。

 

可以同时修改多个系统变量的值：

SELECT /*+ SET_VAR(optimizer_switch = 'mrr_cost_based=off') SET_VAR(max_heap_table_size = 1G) */ * FROM t1;

 

不是所有的系统变量都是可以在这里面修改的，目前支持的变量有：

• auto_increment_increment
• auto_increment_offset
• big_tables
• bulk_insert_buffer_size
• default_tmp_storage_engine
• div_precision_increment
• end_markers_in_json
• eq_range_index_dive_limit
• foreign_key_checks
• group_concat_max_len
• insert_id
• internal_tmp_mem_storage_engine
• join_buffer_size
• lock_wait_timeout
• max_error_count
• max_execution_time
• max_heap_table_size
• max_join_size
• max_length_for_sort_data
• max_points_in_geometry
• max_seeks_for_key
• max_sort_length
• optimizer_prune_level
• optimizer_search_depth variables
• optimizer_switch
• range_alloc_block_size
• range_optimizer_max_mem_size
• read_buffer_size
• read_rnd_buffer_size
• sort_buffer_size
• sql_auto_is_null
• sql_big_selects
• sql_buffer_result
• sql_mode
• sql_safe_updates
• sql_select_limit
• timestamp
• tmp_table_size
• updatable_views_with_limit
• unique_checks
• windowing_use_high_precision

 

十，字符集优化

MySQL8.0开始支持Unicode9.0。

Unicode9.0新增支持了7500中新字符，包括72种emoji表情。另外还支持了一些小语种，其中一种是中国古代的西夏语。

新增的emoji表情：

MySQL8.0的默认字符集改为utf8mb4。可以放心大胆的用emoji表情了。

 

十一，binlog复制优化

8.0版本使用WriteSet，对binlog的复制进行了优化，效率得以提高。

详细介绍请见此文：

MySQL8.0新特性学习笔记（一）：binlog复制策略优化

 

十二，持久化AUTO_INCREMENT值

在8.0版本之前，表的AUTO_INCREMENT值是存在内存中的，没有持久化到磁盘中，所以会出现这样的情况：

1，向表中插入3条记录，不指定id，则id自增为1,2,3。

2，删除id为3的行。

3，重启MySQL服务。

4，向表中插入一条记录，不指定id，则新增的这条记录的id将会是3，和之前一条记录的id重复。

 

这种id会重复的情况被记录在bug#199下，有些情况下这个bug还是很烦人的，比如把主键当业务主键的场景（业务主键理论上不应该重复），或者数据归档的场景：

1，向表A中插入3条记录，不指定id，则id自增为1,2,3。

2，把表A的数据整体归档到表B，于是表A中无数据，表B中有3条数据。

3，重启MySQL服务。

4，再向表A中插入一条记录，id就会是1。于是这个表A以后也别想再归档了。

 

MySQL8.0版本修复了这个bug，表的AUTO_INCREMENT被持久化了，任意时间MySQL服务重启，AUTO_INCREMENT值都会正确的加1。

 

十三，JSON语法

8.0版本开始正式支持JSON相关的语法，提供了JSON格式的数据库字段类型，制定了JSON格式相关的规则，添加了大量的JSON相关的函数。

详细介绍请见此文：

MySQL8.0新特性学习笔记（五）：JSON格式简介和JSON函数详解

 

除了上面提到的修改和功能之外，MySQL8.0版本还有很多的修改，优化，和bug修复，以后慢慢学习。

 

完

 

MySQL8.0全部学习笔记：

MySQL8.0新特性学习笔记（一）：binlog复制策略优化

MySQL8.0新特性学习笔记（二）：窗口函数

MySQL8.0新特性学习笔记（三）：直方图

MySQL8.0新特性学习笔记（四）：Hash Join

MySQL8.0新特性学习笔记（五）：JSON格式简介和JSON函数详解

MySQL8.0新特性学习笔记（六）：新特性介绍