MySQL 5.6的优化器改进-阿里云开发者社区

本文整理了下MySQL5.6在优化器部分的相关知识点，不涉及任何代码内容，主要搜集了网上的一些相关资料（这是重点 :)）

首先要提的当然是臭名昭著的MySQL子查询问题，在MySQL5.5及之前的版本，所有有经验的MySQL DBA都会告诉你：绝不能在SQL的WHERE子句中使用子查询，因为那将可能产生灾难性的后果，因为很有可能每扫描一条数据，Where子查询都会被重新执行一遍，workaround的办法就是把WHERE里的子查询提升到FROM中，做成join操作；

semi join的定义点wiki， MySQL需要满足如下条件，才会转换成Semi-join
#子查询是IN或者=ANY的，不可以是NOT IN
#子查询只能包含一个Query block，不可以有UNION等操作
#子查询不能包含GROUP BY或者HAVING
#子查询不能包含聚合函数
#子查询谓语不可以是外接查询条件或者否定查询条件
#不可以包含STRAIGHT_JOIN限定词
#Semi-join只能用于SELECT或者INSERT,不可用于UPDATE和DELETE

偷个懒，上述总结翻译自这篇博文，官方文档上也有说明

和普通join查询不同的是，在semi join中，inner table 的结果集没有重复数据，当两表关联时，例如t1 semi join t2, 当t2存在匹配的记录时，返回t1的记录（t2的记录不会加入操作的结果集中），并且t1的记录最多只返回一次；不像inner join，每一个匹配的记录都会返回，对于semi join，在乎的只是是否匹配子查询而已。

当满足转换成semi join的条件时，就会将子查询进行转换，并选择如下策略之一去对记录进行去重：
#TABLE PULLOUT, 直接使用TABLE PULLOUT，将子查询的表和outer表进行inner join；子查询上产生的记录本身需要具有唯一性，例如是primary key或者Unique key

#Duplicate Weedout, 先和子查询做简单的inner join操作，并使用临时表（建有primary key）来消除重复记录；
在explain的extra字段会看到Start temporary和End temporary标识

下面几个图都偷的mariadb的博客上的，嘿嘿

mariadb的博客：duplicate weedout

#FirstMatch,当扫描inner table来组合数据时，并且有多个符合条件的数据时，只选择第一条满足条件的记录
在explain的extra字段显示FirstMatch(tbl_name)

Mariadb 博客： FirstMatch

#LooseScan,使用索引扫描，基于索引进行分组，只取分组的第一条记录与外部表进行匹配；
在explain的extra字段显示LooseScan(m..n)

Mariadb 博客： LooseScan

#Materialize, 创建临时表（拥有索引）并将子查询的结果存储到临时表中，然后使用临时表来做join.临时表索引的主要目的是用来移除重复记录，并且随后也可能用于在做join时做查询使用；
在explain的select_type字段显示MATERIALIZED
当子查询能够独立执行，不依赖SQL的其他结果时，可能选择该策略，在子查询物化后，和外部查询结果做join时，也可以作为驱动表或被驱动表

Mariadb 博客：Materialize

可以通过设置optimizer_switch来设置是否开启firstmatch, loosescan,materialization

Subquery Materialize

对于类似NOT IN这样的子查询，也能受益于subquery materialize，将子查询的结果集cache到临时表里，使用hashindex来进行检索；物化的子查询可以看到select_type字段为SUBQUERY，而在MySQL5.5里为DEPENDENT SUBQUERY
关于子查询物化的官方开发人员的文章

优化FROM子查询（Derived Table）

对FROM子查询的物化延迟到只有查询需要时才进行
#在之前的版本中，FROM子查询的物化主要是为了EXPLAIN SELECT操作，这会导致在做EXPLAIN时也会执行SELECT，由于延迟了子查询物化，可以加速EXPLAIN
#对于非EXPLAIN查询，也可能受益，例如t1 join 子查询，如果t1没有数据的话，就直接返回空结果集而无需去执行子查询
在执行的过程中，优化器还可能为derived table增加索引来加速查询

不过需要注意这里有一个性能退化的bug，是在去年report的: http://bugs.mysql.com/bug.php?id=68979

Index Condition Pushdown

当MySQL使用索引进行数据检索时，不可用于在Innodb进行索引检索的WHERE条件，也可以下推到Innodb层，以减少返回存储引擎层的数据量，降低回表数据量
#对于innodb表，ICP只应用于二级索引
#在MySQL5.6里还不支持对分区表ICP（5.7支持）

当使用ICP时，explain会显示Using index condition，例如：

+—-+————-+——-+——-+—————+——+———+——+——+———————–+

| 1 | SIMPLE | t1 | range | b | b | 10 | NULL | 1 | Using index condition |

+—-+————-+——-+——-+—————+——+———+——+——+———————–+

1 row in set (0.00 sec

需要注意，ICP特性有一个有趣的bug(bug#68554)，当能使用覆盖索引时，将无法使用ICP，这可能导致性能退化，更多细节可以阅读Percona的这篇博客

ref:
Percona对ICP的测试

Index Extension

我们知道，在Innodb的二级索引中，除了我们显式定义的key外，还包含了primary key的列值，在早期版本中，MySQL优化器选择索引时不会考虑到这些列，但在MySQL5.6.9版本之后，在做优化路径选择时，会考虑到二级索引上的primary key，就好像我们显示的在二级索引上也包含了pk一样

例如：
mysql> show create table t3;

+——-+———————————————————————————————————————————————————————————————————-+

| Table | Create Table |

| t3 | CREATE TABLE `t3` ( `i1` int(11) NOT NULL DEFAULT ‘0’, `i2` int(11) NOT NULL DEFAULT ‘0’, `d` date DEFAULT NULL, PRIMARY KEY (`i1`,`i2`),
KEY `k_d` (`d`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 |

1 row in set (0.00 sec)

mysql> EXPLAIN SELECT COUNT(*) FROM t3 WHERE i1 = 3 AND d = ‘2000-01-01′;

+—-+————-+——-+——+—————+——+———+————-+——+————-+

+—-+————-+——-+——+—————+——+———+————-+——+————-+

| 1 | SIMPLE | t3 | ref | PRIMARY,k_d | k_d | 8 | const,const | 1 | Using index |

+—-+————-+——-+——+—————+——+———+————-+——+————-+

1 row in set (0.00 sec)

当使用了Index Extension后，就可以使用(d, i1)来检索二级索引k_d，无需回表查聚集索引(没有Using where)

ref：网上的一篇介绍的博客，配有例子

Multi-Range Read

当根据二级索引检索到的数据再回表查询时，可能产生大量的随机IO，因为二级索引和聚集索引的数据顺序很可能是不一样的； MRR能够预先扫描二级索引，搜集key值，排号顺序后再回表查询；每次搜集的key值存储在一个buffer中，buffer的大小受参数 read_rnd_buffer_size 控制

ref:
Percona的评测
 Mariadb MRR

Batched Key Access

BKA算法主要用于提升JOIN的性能，当被join的表能够使用索引时，就先排好顺序，然后再去检索被join的表，听起来和MRR类似，实际上MRR也可以想象成二级索引和primary key的join
如果被Join的表上没有索引，则使用老版本的BNL策略(BLOCK Nested-loop)

默认情况下batched_key_access是关闭的，需要通过optimizer_switch打开;另外由于BKA将排号顺序的key投递到存储引擎是通过MRR的接口，因此MRR也要打开

root@test 10:54:58>SET optimizer_switch=’mrr=on,mrr_cost_based=off,batched_key_access=on';

Query OK, 0 rows affected (0.00 sec)

一个简单的例子：
root@test 10:56:18>show create table t1\G

*************************** 1. row ***************************

Table: t1 Create Table: CREATE TABLE `t1` ( `a` int(11) NOT NULL AUTO_INCREMENT, `b` int(11) DEFAULT NULL, `c` int(11) DEFAULT NULL, `d` int(11) DEFAULT NULL, PRIMARY KEY (`a`), KEY `b` (`b`), KEY `b_2` (`b`,`c`) ) ENGINE=InnoDB AUTO_INCREMENT=786406 DEFAULT CHARSET=gbk

1 row in set (0.00 sec)

root@test 10:56:26>show create table t2\G

*************************** 1. row ***************************

Table: t2 Create Table: CREATE TABLE `t2` ( `a` int(11) DEFAULT NULL, `b` int(11) DEFAULT NULL, KEY `a` (`a`) ) ENGINE=InnoDB DEFAULT CHARSET=gbk

1 row in set (0.00 sec)

root@test 10:56:28>explain select * from t1, t2 where t1.b = t2.a;

+—-+————-+——-+——+—————+——+———+———–+——–+—————————————-+

+—-+————-+——-+——+—————+——+———+———–+——–+—————————————-+

| 1 | SIMPLE | t1 | ALL | b,b_2 | NULL | NULL | NULL | 523502 | Using where |

| 1 | SIMPLE | t2 | ref | a | a | 5 | test.t1.b | 1 | Using join buffer (Batched Key Access) |

+—-+————-+——-+——+—————+——+———+———–+——–+—————————————-+

2 rows in set (0.00 sec)
没有索引，使用BNL
root@test 11:00:32>explain select * from t1, t2 where t1.d = t2.b;

+—-+————-+——-+——+—————+——+———+——+——–+—————————————————-+

+—-+————-+——-+——+—————+——+———+——+——–+—————————————————-+

+—-+————-+——-+——+—————+——+———+——+——–+—————————————————-+

2 rows in set (0.00 sec)

ref:
Percona对BKA的评测
 官方开发人员关于MRR和BKA的PPT

优化ORDER BY LIMIT

在MySQL5.6中，对类似如下的SQL进行优化：

SELECT … FROM single_table … ORDER BY non_index_column [DESC] LIMIT N [OFFSET M];

当有足够的内存( sort_buffer_size)来存储N+M行记录时，会在内存中创建一个优先队列来存储数据，这意味着一次扫描表就可以获得想要的数据，避免了创建/写入临时表及归并排序操作（之前版本的逻辑）
大概逻辑为：
1.扫描表，将数据有序的插入到优先队列中，如果队列满了，则按顺序移除多余的记录
2.返回队列中的N行记录，如果指定了OFFSET M，则忽略开始的M条记录，然后返回剩下的N条

例如对于如下简单的SQL：
root@sbtest 10:32:43>select * from sbtest1 order by pad limit 10;

root@sbtest 10:32:54>show status like ‘%sort%';

+——————-+——-+

| Variable_name | Value |

+——————-+——-+

| Sort_merge_passes | 0 |

| Sort_range | 0 |

| Sort_rows | 10 |

| Sort_scan | 1 |

+——————-+——-+

4 rows in set (0.00 sec)

可以看到Sort_merge_passes 值为0，表示不是通过归并排序获得结果集，我们也可以通过optimizer trace查看执行计划，来判断是否使用了优先级队列，如下：

{ “join_execution”: { “select#”: 1, “steps”: [ { “filesort_information”: [ { “direction”: “asc”, “table”: “`sbtest1`”, “field”: “pad” } ], “filesort_priority_queue_optimization“: { “limit”: 10, “rows_estimate”: 4645146, “row_size”: 490, “memory_available”: 409600, “chosen”: true }, “filesort_execution”: [ ], “filesort_summary”: { “rows”: 11, “examined_rows”: 300000, “number_of_tmp_files”: 0, “sort_buffer_size”: 5478, “sort_mode”: “<sort_key, additional_fields>” } } ] }

ref：
官方开发人员的一篇博客

等值范围优化

MySQL5.6对如下类似的SQL优化
col_name IN(val1, …, valN)
col_name = val1 OR … OR col_name = valN

MySQL实际上将这种等值操作当中所RANGE来处理的，如果col_name是uk，那么range就是1，否则优化器需要去估算range内的记录（Index dive, 调用存储引擎的records_in_range）

Index dive调用存储引擎的records_in_range，因此能够获得更精确的记录数估算值，但随着IN中值越来越多，估算记录数的开销也越来越大；如果直接使用Index的统计信息的话，速度将更快，但可能没那么精确；

如果你总是想使用索引统计信息而禁止index dive，则将 eq_range_index_dive_limit 设置为0，或者将值设置到N+1，以允许最多N个等值条件时使用index dive.

这后面有个小插曲，Facebook的工程师抱怨eq_range_index_dive_limit的默认取值不合理，而官方也接受了该建议，在MySQL5.7.4中将其默认值调整到200

新的优化器功能

5.6为优化器问题诊断提供了更多的接口，包括
#允许对INSERT/UPDATE/DELETE操作进行EXPLAIN
#JSON格式的EXPLAIN结果输出
#能够记录SQL执行的优化器选择Optimizer trace

一些和优化器相关的有趣的老bug和链接
http://bugs.mysql.com/bug.php?id=18454
http://bugs.mysql.com/bug.php?id=16555
http://bugs.mysql.com/bug.php?id=50674

http://bugs.mysql.com/bug.php?id=59326

mysql 5.6对大量表join做的优化:http://jorgenloland.blogspot.fr/2012/04/improvements-for-many-table-joins-in.html

MySQL 5.6的优化器改进

热门文章

最新文章

相关课程

相关电子书

相关实验场景

推荐镜像