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，例如：

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


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; 

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

| 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

 

时间： 2024-10-29 13:21:37

MySQL 5.6的优化器改进的相关文章

MySQL数据库中与优化器有关的事宜

MySQL采用了基于开销的优化器,以确定处理查询的最解方式.在很多情况下,MySQL能够计算最佳的可能查询计划,但在某些情况下,MySQL没有关于数据的足够信息,不得不就数据进行"有教养"的估测. 当MySQL未能做"正确的"事时,可使用下述工具来帮助MySQL: 使用EXPLAIN语句获取关于MySQL如何处理查询的信息.要想使用它,可在SELECT语句前添加关键字EXPLAIN: mysql> EXPLAIN SELECT * FROM t1, t2 WH

解决MySQL数据库中与优化器有关的问题

MySQL采用了基于开销的优化器,以确定处理查询的最解方式.在很多情况下,MySQL能够计算最佳的可能查询计划,但在某些情况下,MySQL没有关于数据的足够信息,不得不就数据进行"有教养"的估测. 当MySQL未能做"正确的"事时,可使用下述工具来帮助MySQL: 使用EXPLAIN语句获取关于MySQL如何处理查询的信息.要想使用它,可在SELECT语句前添加关键字EXPLAIN: mysql> EXPLAIN SELECT * FROM t1, t2 WH

美图秀秀DBA谈MySQL运维及优化

随着MySQL应用的不断普及和自身发展,如何更好的优化MySQL和使用MySQL,依然是一个比较有挑战的问题,尤其是在业务快速增长的场景下.本次分享主要介绍一些通用的运维优化实践和问题,以及未来的一些方向.  目录 MySQL的优势和劣势 数据库规范化 Sharding拆分 数据库备份 性能优化 从每个月的db engines排名可以看到,关系数据库依然占主导地位,nosql的种类和可选择空间更大,总共283种数据库,里面大多数也是NoSQL. 如何选择数据库,从以下几个因素考虑: 应用场景:O

MySQL之SQL分析三部曲实际案例（五）--临时表，优化器的选择

-------------------------------------------------------------------------------------------------正文---------------------------------------------------------------------------------------------------------------问题出现于生产环境,为了方便表述,在测试环境构造了类似的场景 所有操作都是基于M

MySQL优化器中一个Count和覆盖索引的问题

   前天在微薄上发了个优化器的问题,从评论来看,还是需要简单说明一下.     现象说明        其实这里主要要说明的是一个优化器还需要改进的地方.   优化器会根据where条件和select_list里面的字段决定在使用一个索引(sta)后,是否需要回表-回到聚集索引取数据.   基本的做法是:在确定了一个索引后,将select_list和where中出现的所有字段都拿来判断一下,如果字段都存在于sta索引中,则可以使用覆盖索引.   第一个explan可以用上覆盖索引(Using

案例：MySQL优化器如何选择索引和JOIN顺序

我们知道,MySQL优化器只有两个自由度:顺序选择:单表访问方式:这里将详细剖析下面的SQL,看看MySQL优化器如何做出每一步的选择. explain select * from employee as A,department as B where A.LastName = 'zhou' and B.DepartmentID = A.DepartmentID and B.DepartmentName = 'TBX'; 1. 可能的选择 这里看到JOIN的顺序可以是A|B或者B|A,单表访问方

MySQL优化器：index merge介绍

1. 什么是index merge MySQL优化器如果发现可以使用多个索引查找后的交集/并集定位数据,那么MySQL优化器就会尝试index merge这类访问方式.index merge主要分为两大类,多个索引交集访问(intersections),多个索引并集访问,当然这两类还可以组合出更为复杂的方式,例如多个交集后做并集. 1.1 index merge的限制:range优先 MySQL在5.6.7之前,使用index merge有一个重要的前提条件:没有range可以使用.这个限制降低

MySQL · 捉虫动态 · order by limit 造成优化器选择索引错误

问题描述 bug 触发条件如下: 优化器先选择了 where 条件中字段的索引,该索引过滤性较好: SQL 中必须有 order by limit 从而引导优化器尝试使用 order by 字段上的索引进行优化,最终因代价问题没有成功. 复现case 表结构 create table t1( id int auto_increment primary key, a int, b int, c int, key iabc (a, b, c), key ic (c) ) engine = innod

MySQL · 特性分析 · 优化器 MRR &amp; BKA

上一篇文章咱们对 ICP 进行了一次全面的分析,本篇文章小编继续为大家分析优化器的另外两个选项: MRR & batched_key_access(BKA) ,分析一下他们的作用.原理.相互关系.源码实现以及使用范围. 什么是 MRR MRR 的全称是 Multi-Range Read Optimization,是优化器将随机 IO 转化为顺序 IO 以降低查询过程中 IO 开销的一种手段,咱们对比一下 mrr=on & mrr=off 时的执行计划: 其中表结构如下: mysql>