探索索引和联接顺序的MySQL优化器的选择

本文使用一个案例来了解MySQL优化器如何选择索引和连接顺序。表结构和数据准备是指本文最后一部分测试环境。这主要是关于MySQL优化器的主要执行过程，而不是介绍优化器的各种组件（这是另一个主题）。

我们知道MySQL优化器只有两个自由度：顺序选择和单表访问。在这里，我们将详细分析下面的SQL，并了解MySQL优化器是如何做出每一步选择的。
解释
选择*
从
雇员为B的部门
哪里
a.lastname = 'zhou
和b.departmentid = a.departmentid
和b.departmentname = 'tbx；

1。可能的选择

在这里，我们看到，连接顺序可以| B或B |，有许多方法来访问单个表。对于一个表，我们可以选择：全表扫描和索引` ind_l_d `（a.lastname = 'zhou）或` ind_did `（b.departmentid = a.departmentid）。还有B三选项：全表扫描，索引ind_d，ind_dn。

2怎么办。MySQL优化器做

2.1概述

MySQL优化器的主要工作包括以下几个部分：查询重写（包括外部连接转换等）、const表检测、范围分析、连接优化（顺序和访问模式选择）和转到。

2.2范围分析

这一节包括所有范围和索引合并成本评估（参考1参考文献2）。在这里，等价表达式也是一个范围，因此它将在这里评估它的成本，并计算所发现的记录（表示相应的等价表达式，以及将选择多少记录）。

在这种情况下，分析的范围分析表的条件，条件a.lastname = 'zhou'and的b.departmentname = 'tbx的B表。其中：

表一a.lastname = 'zhou'found记录：51

表B b.departmentname = 'tbx'found记录：1

两个条件没有范围，但这里计算的价值仍在储存和使用时参考访问方法进行评价。这里是回归的价值根据records_in_range接口，和InnoDB为每个调用该函数将一个索引页的采样，这是一个非常消耗性能的操作，为许多其他的关系型数据库的统计数据使用直方图来避免这种操作（相信在MariaDB以后的版本也将实现直方图统计信息）。

2.3阶与存取方式的选择：用尽

MySQL通过枚举所有左侧的深树（或者所有遗留的深树都是MySQL优化器的搜索空间）找到最佳的执行顺序和访问模式。

2.3.1排序

优化器首先根据发现的记录对所有表进行排序，以记录该游戏的一个小前端。因此，顺序是B，A.

2.3.2贪婪搜索

当小表的数量（小于search_depth，默认值为63）当有穷尽搜索直接变性，优化器将用尽所有左深树找到最优的执行计划。另外，为了减少由于巨大的搜索空间的搜索空间的巨大的疲惫，优化器使用一个懒惰的参数prune_level（默认打开），具体如何懒。它可以参考连接顺序选择的复杂性，但是，至少需要三个表和以上表为懒惰。

2.3.3疲惫

第一个连接表可以是：A或B；如果第一个表选择A，第二个表可以选择B；如果第一个表选择B，第二个表可以选择a。

由于以前的排序，B表的查找记录较少，所以第一个表在连接序列不好时首先选择B（这是精致的）。

（*）为第一个联接选择的表是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表的访问是裁判，利用指数ind_d

（*）从其余表中选择第二个联接表，其余表是：

（**）添加一个表以便连接并确定访问的方式。

可使用的指标有：` ind_l_d `（a.lastname = 'zhou）或` ind_did `（b.departmentid = a.departmentid）

利用指数ind_l_d和ind_did反过来成本依次计算。

（***）ind_l_d a.lastname = 'zhou

在分析阶段，该a.lastname = 'zhou'corresponding记录是51。

因此，计算IO成本：51；裁判会修正当IO成本计算和修改它worst_seek（参考）。

修正后的IO成本为：15，总成本为：25.2。

（***）ind_did b.departmentid = a.departmentid

这是一个需要知道前表计算结果的成本，所以不能进行距离分析。

在这里，我们看到，前面的桌子是B，found_record是1，所以A.DepartmentID只需要一个单一的记录。

因为特定的值不知道，所以没有直方图，所以只能通过指数统计来计算。

该指数的ind_did列a.departmentid的基数是1349，和总台的记录是1349。

因此，每个值对应一个记录，和以前的B表只有一个记录，所以这里的found_record是1 * 1 = 1。

所以IO的成本是：1，总成本是1.2。

（* * * * * * * * *）ind_l_d成本25.2；ind_did成本的1.2，所以选择后者作为访问形成电流

（***）确定，采用指数ind_did，和访问方法是参考

（**）连接顺序B |，总费用为：1.2 + 1.2 = 2.4

（*）为第一个联接选择的表是

（**）确定通往桌子的途径

因为表是第一个表，索引` ind_did `（b.departmentid = a.departmentid）不能使用。

那么指数` ind_l_d `（a.lastname = 'zhou）只能用于。

使用ind_l_d指数，总费用是25.2；参考以前的计算；

（*）在这里访问一张表的成本已经是25.2，比以前的2.4的最优成本大，忽略订单。

所以，穷举搜索结束了

简化过程如下：

（*）为第一个联接选择的表是B。

（**）确定B表的存取方式

（*）从其余表中选择第二个联接表，其余表是：

（**）添加一个表以便连接并确定访问的方式。

（***）ind_l_d a.lastname = 'zhou

（***）ind_did b.departmentid = a.departmentid

（* * * * * * * * *）ind_l_d成本25.2；ind_did成本的1.2，所以选择后者作为访问形成电流

（***）确定，采用指数ind_did，和访问方法是参考

（**）连接顺序B |，总费用为：1.2 + 1.2 = 2.4

（*）为第一个联接选择的表是

（**）确定通往桌子的途径

（*）在这里访问一张表的成本已经是25.2，比以前的2.4的最优成本大，忽略订单。

此时，MySQL优化器确定所有表的最佳联接顺序和访问模式。

三.测试环境
MySQL InnoDB Plugin：5.1.48-debug-log 1.0.9

创建表（部门）
` DepartmentID ` int（11）默认为空，
` departmentname ` varchar（20）默认为空，
关键` ind_d `（` DepartmentID `），
关键` ind_dn `（` DepartmentName `）
InnoDB引擎=默认的字符集GBK）；

创建表（雇员）
`姓` varchar（20）默认为空，
` DepartmentID ` int（11）默认为空，
关键` ind_l_d `（`姓`），
关键` ind_did `（` DepartmentID `）
InnoDB引擎=默认的字符集GBK）；

我在` SEQ 1 1000 `；做MySQL VVV -中试e'insert。
我在` SEQ 1 1000 `；做MySQL VVV -中试e'insert。

我在` SEQ 1 50 `；做MySQL VVV -中试e'insert（27760）；
我在` SEQ 1 200 `；做MySQL VVV -中试e'insert。
我在` SEQ 1 1 `；做MySQL VVV -中试e'insert（27760）；

从员工显示索引；
+ + + + ---------- ------------ ---------- -------------- + -------------- + + + + ----------- ------------- ---------- -------- + + + + ------ ------------ ---------
表non_unique key_name | | | | seq_in_index column_name整理基数| | | | sub_part |填充空index_type评论| | | |
+ + + + ---------- ------------ ---------- -------------- + -------------- + + + + ----------- ------------- ---------- -------- + + + + ------ ------------ ---------
|员工| 1 | ind_l_d | 1 |姓|一| 1349 |空值是B树| | | | |
|员工| 1 | ind_did | 1 | DepartmentID |一| 1349 |空值是B树| | | | |
+ + + + ---------- ------------ ---------- -------------- + -------------- + + + + ----------- ------------- ---------- -------- + + + + ------ ------------ ---------

从部门显示索引；
+ + + + ------------ ------------ ---------- -------------- + ---------------- + + + + ----------- ------------- ---------- -------- + + + + ------ ------------ ---------
表non_unique key_name | | | | seq_in_index column_name整理基数| | | | sub_part |填充空index_type评论| | | |
+ + + + ------------ ------------ ---------- -------------- + ---------------- + + + + ----------- ------------- ---------- -------- + + + + ------ ------------ ---------
|部| 1 | ind_d | 1 | DepartmentID |一| 1001 |空值是B树| | | | |
|部| 1 | ind_dn | 1 | departmentname |一| 1001 |空值是B树| | | | |
+ + + + ------------ ------------ ---------- -------------- + ---------------- + + + + ----------- ------------- ---------- -------- + + + + ------ ------------ ---------

4。造恶案

由于MySQL使用索引统计来估计相关条件的成本，所以当数据分布不均匀时，很容易做出错误判断：

表和索引结构不一样，数据以如下方式构建：
我在` SEQ 1 10000 `；做MySQL中测试e'insert成。
我在` SEQ 1 10000 `；做MySQL中测试e'insert成。

我在` SEQ 1 1 `；做MySQL中测试e'insert成（27760）；
我在` SEQ 1 10 `；做MySQL中测试e'insert为（27760）；
我在` SEQ 1 1000 `；做MySQL中测试e'insert成。
多恩

解释
选择*
从
雇员为B的部门
哪里
a.lastname = 'zhou
和b.departmentid = a.departmentid
和b.departmentname = 'tbx；
+ -- + + + + ------------- ------- ------ ----------------- + --------- + --------- + --------------------- ------ ------------- + + +
我select_type表| | | |型possible_keys关键key_len | | | | REF |行|额外|
+ -- + + + + ------------- ------- ------ ----------------- + --------- + --------- + --------------------- ------ ------------- + + +
| 1 |简单|一| | REF ind_l_d，ind_did ind_l_d | | 43 | const | 1 |使用|
| 1 |简单| B | | REF ind_d，ind_dn ind_d | | 5 | test.a.departmentid | 1 |使用|
+ -- + + + + ------------- ------- ------ ----------------- + --------- + --------- + --------------------- ------ ------------- + + +

它可以在这里看到，MySQL的执行计划使用索引ind_d表部，所以表命中纪录（周，27760）；根据b.departmentid = 27760，它将返回1010条记录，然后过滤，根据条件departmentname = 'tbx。

在这里我们可以看到，如果B表选择指数ind_dn，效果更好，因为departmentname = 'tbx'only返回10条记录，并根据条件a.departmentid = b.departmentid过滤。