首页 / MYSQL / 探究MySQL优化器对索引和JOIN顺序的选择

探究MySQL优化器对索引和JOIN顺序的选择

内容导读

互联网集市收集整理的这篇技术教程文章主要介绍了探究MySQL优化器对索引和JOIN顺序的选择，小编现在分享给大家，供广大互联网技能从业者学习和参考。文章包含9849字，纯文字阅读大概需要15分钟。

内容图文

本文通过一个案例来看看MySQL优化器如何选择索引和JOIN顺序。表结构和数据准备参考本文最后部分"测试环境"。这里主要介绍MySQL优化器的主要执行流程，而不是介绍一个优化器的各个组件(这是另一个话题)。

我们知道，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，单表访问方式也有多种，对于A表可以选择：全表扫描和索引`IND_L_D`(A.LastName = 'zhou')或者`IND_DID`(B.DepartmentID = A.DepartmentID)。对于B也有三个选择：全表扫描、索引IND_D、IND_DN。
2. MySQL优化器如何做
2.1 概述

MySQL优化器主要工作包括以下几部分：Query Rewrite(包括Outer Join转换等)、const table detection、range analysis、JOIN optimization(顺序和访问方式选择)、plan refinement。这个案例从range analysis开始。
2.2 range analysis

这部分包括所有Range和index merge成本评估(参考1 参考2)。这里，等值表达式也是一个range，所以这里会评估其成本，计算出found records(表示对应的等值表达式，大概会选择出多少条记录)。

本案例中，range analysis会针对A表的条件A.LastName = 'zhou'和B表的B.DepartmentName = 'TBX'分别做分析。其中：

表A A.LastName = 'zhou' found records: 51
表B B.DepartmentName = 'TBX' found records: 1

这两个条件都不是range，但是这里计算的值仍然会存储，在后面的ref访问方式评估的时候使用。这里的值是根据records_in_range接口返回，而对于InnoDB每次调用这个函数都会进行一次索引页的采样，这是一个很消耗性能的操作，对于很多其他的关系数据库是使用"直方图"的统计数据来避免这次操作(相信MariaDB后续版本也将实现直方图统计信息)。
2.3 顺序和访问方式的选择：穷举

MySQL通过枚举所有的left-deep树(也可以说所有的left-deep树就是整个MySQL优化器的搜索空间)，来找到最优的执行顺序和访问方式。
2.3.1 排序

优化器先根据found records对所有表进行一个排序，记录少的放前面。所以，这里顺序是B、A。
2.3.2 greedy search

当表的数量较少(少于search_depth，默认是63)的时候，这里直接蜕化为一个穷举搜索，优化器将穷举所有的left-deep树找到最优的执行计划。另外，优化器为了减少因为搜索空间庞大带来巨大的穷举消耗，所以使用了一个"偷懒"的参数prune_level(默认打开)，具体如何"偷懒"，可以参考JOIN顺序选择的复杂度。不过至少需要有三个表以上的关联才会有"偷懒"，所以本案例不适用。
2.3.3 穷举

JOIN的第一个表可以是：A或者B；如果第一个表选择了A，第二个表可以选择B；如果第一个表选择了B，第二个表可以选择A；

因为前面的排序，B表的found records更少，所以JOIN顺序穷举时的第一个表先选择B(这个是有讲究的)。

(*) 选择第一个JOIN的表为B
(**) 确定B表的访问方式
    因为B表为第一个表，所以无法使用索引IND_D(B.DepartmentID = A.DepartmentID)，而只能使用IND_DN(B.DepartmentName = 'TBX')
      使用IND_DN索引的成本计算：1.2；其中IO成本为1。
      是否使用全表扫描：这里会比较使用索引的IO成本和全表扫描的IO成本，前者为1，后者为2；所以忽略全表扫描
    所以，B表的访问方式ref，使用索引IND_D

(**) 从剩余的表中穷举选出第二个JOIN的表，这里剩余的表为：A
(**) 将A表加入JOIN，并确定其访问方式
    可以使用的索引为：`IND_L_D`(A.LastName = 'zhou')或者`IND_DID`(B.DepartmentID = A.DepartmentID)
    依次计算使用索引IND_L_D、IND_DID的成本：
    (***) IND_L_D A.LastName = 'zhou'
          在range analysis阶段给出了A.LastName = 'zhou'对应的记录约为：51。
          所以，计算IO成本为：51；ref做IO成本计算时会做一次修正，将其修正为worst_seek(参考)
          修正后IO成本为：15，总成本为：25.2
    (***) IND_DID B.DepartmentID = A.DepartmentID
          这是一个需要知道前面表的结果，才能计算的成本。所以range analysis是无法分析的
          这里，我们看到前面表为B，found_record是1，所以A.DepartmentID只需要对应一条记录就可以了
          因为具体取值不知道，也没有直方图，所以只能简单依据索引统计信息来计算：
            索引IND_DID的列A.DepartmentID的Cardinality为1349，全表记录数为1349
            所以，每一个值对应一条记录，而前面表B只有一条记录，所以这里的found_record计算为1*1 = 1
            所以IO成本为：1，总成本为1.2
    (***) IND_L_D成本为25.2；IND_DID成本为1.2，所以选择后者为当前表的访问方式
(**) 确定A使用索引IND_DID，访问方式为ref
(**) JOIN顺序B|A，总成本为：1.2+1.2 = 2.4

(*) 选择第一个JOIN的表为A
(**) 确定A表的访问方式
       因为A表是第一个表，所以无法使用索引`IND_DID`(B.DepartmentID = A.DepartmentID)
       那么只能使用索引`IND_L_D`(A.LastName = 'zhou')
         使用IND_L_D索引的成本计算，总成本为25.2；参考前面计算；
(**) 这里访问A表的成本已经是25.2，比之前的最优成本2.4要大，忽略该顺序
       所以，这次穷举搜索到此结束

把上面的过程简化如下：

(*) 选择第一个JOIN的表为B
(**) 确定B表的访问方式
(**) 从剩余的表中穷举选出第二个JOIN的表，这里剩余的表为：A
(**) 将A表加入JOIN，并确定其访问方式
    (***) IND_L_D A.LastName = 'zhou'
    (***) IND_DID B.DepartmentID = A.DepartmentID
    (***) IND_L_D成本为25.2；IND_DID成本为1.2，所以选择后者为当前表的访问方式
(**) 确定A使用索引IND_DID，访问方式为ref
(**) JOIN顺序B|A，总成本为：1.2+1.2 = 2.4

(*) 选择第一个JOIN的表为A
(**) 确定A表的访问方式
(**) 这里访问A表的成本已经是25.2，比之前的最优成本2.4要大，忽略该顺序

至此，MySQL优化器就确定了所有表的最佳JOIN顺序和访问方式。
3. 测试环境

MySQL: 5.1.48-debug-log innodb plugin 1.0.9

CREATE TABLE `department` (
 `DepartmentID` int(11) DEFAULT NULL,
 `DepartmentName` varchar(20) DEFAULT NULL,
 KEY `IND_D` (`DepartmentID`),
 KEY `IND_DN` (`DepartmentName`)
) ENGINE=InnoDB DEFAULT CHARSET=gbk;

CREATE TABLE `employee` (
 `LastName` varchar(20) DEFAULT NULL,
 `DepartmentID` int(11) DEFAULT NULL,
 KEY `IND_L_D` (`LastName`),
 KEY `IND_DID` (`DepartmentID`)
) ENGINE=InnoDB DEFAULT CHARSET=gbk;

for i in `seq 1 1000` ; do mysql -vvv -uroot test -e 'insert into department values (600000*rand(),repeat(char(65+rand()*58),rand()*20))'; done
for i in `seq 1 1000` ; do mysql -vvv -uroot test -e 'insert into employee values (repeat(char(65+rand()*58),rand()*20),600000*rand())'; done

for i in `seq 1 50` ; do mysql -vvv -uroot test -e 'insert into employee values ("zhou",27760)'; done
for i in `seq 1 200` ; do mysql -vvv -uroot test -e 'insert into employee values (repeat(char(65+rand()*58),rand()*20),27760)'; done
for i in `seq 1 1` ; do mysql -vvv -uroot test -e 'insert into department values (27760,"TBX")'; done

show index from employee;
+----------+------------+----------+--------------+--------------+-----------+-------------+----------+--------+------+------------+---------+
| Table  | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+----------+------------+----------+--------------+--------------+-----------+-------------+----------+--------+------+------------+---------+
| employee |     1 | IND_L_D |      1 | LastName   | A     |    1349 |   NULL | NULL  | YES | BTREE   |     |
| employee |     1 | IND_DID |      1 | DepartmentID | A     |    1349 |   NULL | NULL  | YES | BTREE   |     |
+----------+------------+----------+--------------+--------------+-----------+-------------+----------+--------+------+------------+---------+

show index from department;
+------------+------------+----------+--------------+----------------+-----------+-------------+----------+--------+------+------------+---------+
| Table   | Non_unique | Key_name | Seq_in_index | Column_name  | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+------------+------------+----------+--------------+----------------+-----------+-------------+----------+--------+------+------------+---------+
| department |     1 | IND_D  |      1 | DepartmentID  | A     |    1001 |   NULL | NULL  | YES | BTREE   |     |
| department |     1 | IND_DN  |      1 | DepartmentName | A     |    1001 |   NULL | NULL  | YES | BTREE   |     |
+------------+------------+----------+--------------+----------------+-----------+-------------+----------+--------+------+------------+---------+

4. 构造一个Bad case

因为关联条件中MySQL使用索引统计信息做成本预估，所以数据分布不均匀的时候，就容易做出错误的判断。简单的我们构造下面的案例：

表和索引结构不变，按照下面的方式构造数据：

for i in `seq 1 10000` ; do mysql -uroot test -e 'insert into department values (600000*rand(),repeat(char(65+rand()*58),rand()*20))'; done
for i in `seq 1 10000` ; do mysql -uroot test -e 'insert into employee values (repeat(char(65+rand()*58),rand()*20),600000*rand())'; done

for i in `seq 1 1` ; do mysql -uroot test -e 'insert into employee values ("zhou",27760)'; done
for i in `seq 1 10` ; do mysql -uroot test -e 'insert into department values (27760,"TBX")'; done
for i in `seq 1 1000` ; do mysql -uroot test -e 'insert into department values (27760,repeat(char(65+rand()*58),rand()*20))';
done

explain
select *
from
 employee as A,department as B
where
   A.LastName = 'zhou'
 and B.DepartmentID = A.DepartmentID
 and B.DepartmentName = 'TBX';
+----+-------------+-------+------+-----------------+---------+---------+---------------------+------+-------------+
| id | select_type | table | type | possible_keys  | key   | key_len | ref         | rows | Extra    |
+----+-------------+-------+------+-----------------+---------+---------+---------------------+------+-------------+
| 1 | SIMPLE   | A   | ref | IND_L_D,IND_DID | IND_L_D | 43   | const        |  1 | Using where |
| 1 | SIMPLE   | B   | ref | IND_D,IND_DN  | IND_D  | 5    | test.A.DepartmentID |  1 | Using where |
+----+-------------+-------+------+-----------------+---------+---------+---------------------+------+-------------+

可以看到这里，MySQL执行计划对表department使用了索引IND_D，那么A表命中一条记录为(zhou,27760)；根据B.DepartmentID=27760将返回1010条记录，然后根据条件DepartmentName = 'TBX'进行过滤。

这里可以看到如果B表选择索引IND_DN，效果要更好，因为DepartmentName = 'TBX'仅仅返回10条记录，再根据条件A.DepartmentID=B.DepartmentID过滤之。

您可能感兴趣的文章:

浅析Mysql Join语法以及性能优化
MySQL查询优化:连接查询排序limit(join、order by、limit语句)介绍
MySQL优化之使用连接(join)代替子查询
MySQL中一些优化straight_join技巧
MySQL中（JOIN/ORDER BY）语句的查询过程及优化方法
Mysql巧用join优化sql的方法详解

内容总结

以上是互联网集市为您收集整理的探究MySQL优化器对索引和JOIN顺序的选择全部内容，希望文章能够帮你解决探究MySQL优化器对索引和JOIN顺序的选择所遇到的程序开发问题。如果觉得互联网集市技术教程内容还不错，欢迎将互联网集市网站推荐给程序员好友。

内容备注

版权声明：本文内容由互联网用户自发贡献，该文观点与技术仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容，请发送邮件至 gblab@vip.qq.com 举报，一经查实，本站将立刻删除。

内容手机端

扫描二维码推送至手机访问。

本文链接：https://qyyshop.com/info/584330.html

来源：【匿名】

【上一篇】10个MySQL性能调优的方法【下一篇】用IE远程创建Mysql数据库的简易程序

更多 ►

【探究MySQL优化器对索引和JOIN顺序的选择】教程文章相关的互联网学习教程文章

数据库 mysql 优化器原理

id smallint(10) not null auto_increment primary key, username char(8) not null, password char(4) not null, `level` tinyint (1) default 0, last_login char(15) not null, index(username,password,last_login))engine=innodb;这是test表，其中id是主键，多列索引(username,password,last_login),里面有10000多条数据.(1) 最左前缀原则定义：最左前缀原则指的的是在sql where 字句中一些条件或表达式中出现的列的顺序要...

MySQL优化器不使用索引的情况【代码】【图】

* FROM orderdetails WHERE orderid>10000 and orderid<102000; 通过SHOW INDEX FROM orderdetails可以看到可以看到orderdetails有(orderID,ProductID)的联合主键。此外还有对于列OrderID的单个索引。上述SQL显然是可以通过扫描orderID上的索引进行数据查询的，但通过EXPLAIN发现优化器并没有按照OrderID来查找数据在possable_keys中看到查询可以使用primary、OrderID,OrdersOrder_Details这三个索引。但是在最后的索引中，优化器...

MySQL优化器可选开关详解【图】

欢迎进入Linux社区论坛，与200万技术人员互动交流 >>进入 MySQL 从5.1开始，提供了关于优化器选项的开关选项。这样，我们可以强制在优化器糊涂的时候，关闭一些我们认为不该做的事情；或者是当我们想查看优化器为什么要这么选择的时候，可以手动关闭或者打欢迎进入Linux社区论坛，与200万技术人员互动交流 >>进入　　MySQL 从5.1开始，提供了关于优化器选项的开关选项。这样，我们可以强制在优化器糊涂的时候，关闭一些我们认为...

记一次因线上mysql优化器误判引起慢查询事件的分享【图】

这篇文章主要介绍了记一次因线上mysql优化器误判引起慢查询事件的相关资料以及最终的解决方案，分享给大家，希望能够给大家一点启发。前言: 收到疯狂的慢查询及请求超时报警，通过metrics分析出来自mysql请求的异常，cli —> show proceslist 看到很多慢查询。先前该sql是没有的，后面因为数据量的增长才出现了这问题。虽然feeds表大到一个亿，但因为feeds流信息有近期热的特征，所以不是因为 innodb_buffer_pool_size 低效引...

【原创】MySQL优化器可选开关详解

MySQL从5.1开始，提供了关于优化器选项的开关选项。这样，我们可以强制在优化器糊涂的时候，关闭一些我们认为不该做的事情；或者是当我们想查看优化器为什么要这MySQL 从5.1开始，提供了关于优化器选项的开关选项。这样，我们可以强制在优化器糊涂的时候，关闭一些我们认为不该做的事情；或者是当我们想查看优化器为什么要这么选择的时候，可以手动关闭或者打开这些选项来进行优化策略的改变。MySQL 从5.5 和 5.6 开始，增加了更多...

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

本文通过一个案例来看看MySQL优化器如何选择索引和JOIN顺序。表结构和数据准备参考本文最后部分"测试环境"。这里主要介绍MySQL优化器的主要执行流程，而不是介绍一个优化器的各个组件(这是另一个话题)。我们知道，MySQL优化器只有两个自由度：顺序选择；单本文通过一个案例来看看MySQL优化器如何选择索引和JOIN顺序。表结构和数据准备参考本文最后部分"测试环境"。这里主要介绍MySQL优化器的主要执行流程，而不是介绍一个优化器的...

MySQL优化器：indexmerge介绍

在MySQL官方手册上，关于index merge的介绍非常非常少。甚至还有不少误导的地方，这次把5.1版本关于此类优化处理的代码细看了一遍，以案例的方式介绍了各种实用index merge访问类型的SQL。后续的还会继续介绍index merge实现的主要数据结构，以及成本评估。在MySQL官方手册上，关于index merge的介绍非常非常少。甚至还有不少误导的地方，这次把5.1版本关于此类优化处理的代码细看了一遍，以案例的方式介绍了各种实用index merge访...

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

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

MySQL优化器可选开关详解_MySQL

bitsCN.comMySQL 从5.1开始，提供了关于优化器选项的开关选项。这样，我们可以强制在优化器糊涂的时候，关闭一些我们认为不该做的事情；或者是当我们想查看优化器为什么要这么选择的时候，可以手动关闭或者打开这些选项来进行优化策略的改变。MySQL 从5.5 和 5.6 开始，增加了更多的开关选项。以便使查询更加优化。下面我来一个一个解释下这些优化开关的含义以及用途。MySQL 5.1 增加以下开关：index_merge 1). index_merge_in...