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的博客上的,嘿嘿

dw

 

 

 

 

mariadb的博客:duplicate  weedout

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

fisrstmatch

 

 

 

 

 

Mariadb 博客: FirstMatch

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

loosescan

 

 

 

 

Mariadb 博客: LooseScan

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

 

 

 

 

 

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,例如:

mysql> explain select * from t1 where b >= 2000000 and c < 1000000; +—-+————-+——-+——-+—————+——+———+——+——+———————–+ | id | select_type | table | type  | possible_keys | key  | key_len | ref  | rows | Extra                 | 

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

|  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’; 

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

| id | select_type | table | type | possible_keys | key  | key_len | ref         | rows | Extra       | 

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

|  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 控制

mrr
ref:
Percona的评测
Mariadb MRR

Batched Key Access

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

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

| id | select_type | table | type | possible_keys | key  | key_len | ref       | rows   | Extra                                  | 

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

|  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; 

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

| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows   | Extra                                              | 

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

|  1 | SIMPLE      | t2    | ALL  | NULL          | NULL | NULL    | NULL | 523668 | NULL                                               | 

|  1 | SIMPLE      | t1    | ALL  | NULL          | NULL | NULL    | NULL | 523502 | Using where; Using join buffer (Block Nested Loop)

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

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

 

原创文章,转载请注明: 转载自Simple Life

本文链接地址: MySQL 5.6的优化器改进

Post Footer automatically generated by wp-posturl plugin for wordpress.


Comments

  • a thought by 黄华亮

    请问ORDER BY LIMIT的优化,MySQL 使用的是数组还是链表?请指教。

    Reply

    • a thought by zhaiwx1987

      使用的是一个优先队列

      Reply

Leave a Reply

Your email address will not be published. Name and email are required


Current month ye@r day *