seq搜索引擎优化至少包括那几步?(MySQL优化器如何选择索引和JOIN顺序(组图))

　　seq搜索引擎优化至少包括那几步?(MySQL优化器如何选择索引和JOIN顺序(组图))

　　本文文章主要介绍了MySQL优化器对索引和JOIN顺序选择的探索，包括优化器做出错误判断时的选择。有需要的朋友可以参考

　　本文通过一个案例来看看MySQL优化器是如何选择索引和JOIN顺序的。表结构和数据准备参考本文末尾的“测试环境”。本文主要介绍MySQL优化器的主要执行过程，而不是介绍一个优化器的各个组成部分（这是另一个话题）。

　　我们知道MySQL优化器只有两个自由度：顺序选择；单表访问模式；这里我们将详细分析下面的SQL，看看MySQL优化器在每一步是如何做出选择的。

　　explainselect*fromemployeeeasA,departmentasBwhereA.LastName='zhou'andB.DepartmentID=A.DepartmentIDandB.DepartmentName='TBX';

　　1. 可能的选项

　　JOIN的顺序可以是A|B也可以是B|A，单表的访问方式有很多种。对于表A，可以选择：全表扫描和索引`IND_L_D`(A.LastName ='zhou') 或者`IND_DID`(B.DepartmentID = A.DepartmentID)。 B也有3个选项：全表扫描、索引IND_D、IND_DN。

　　2. MySQL 优化器如何做2.1 概述

　　MySQL优化器的主要工作包括以下几个部分：Query Rewrite（包括Outer Join转换等）、const表检测、范围分析、JOIN优化（顺序和访问方式选择）、计划细化。这个案例从范围分析开始。

　　2.2 范围分析

　　这部分包括了所有的Range和index合并成本评估（参考1参考2)。这里等价表达式也是一个范围，所以这里会评估它的成本，找到记录（代表对应的等价） value) 表达式，大概会选择多少条记录）。

　　这种情况下，极差分析会分别分析A表的条件A.LastName='zhou'和B表的B.DepartmentName='TBX'。哪里：

　　表 A A. LastName ='zhou' 找到记录：51

　　表 B B. DepartmentName ='TBX' 找到的记录：1

　　这两个条件都不是range，但是这里计算出来的值还是会被存储起来，用于后面的ref访问方法的求值。这里的值是根据records_in_range接口返回的，InnoDB每次调用这个函数都会对索引页进行采样。这是一个非常消耗性能的操作。对于许多其他关系数据库，使用“直方图”统计数据。避免这种操作（相信后续版本的 MariaDB 也会实现直方图统计）。

　　2.3 选择顺序和访问方式：穷举列表

　　MySQL通过枚举所有的左深树来寻找最优的执行顺序和访问方式（也可以说所有的左深树都是整个MySQL优化器的搜索空间）。

　　2.3.1 个排序

　　优化器首先根据找到的记录对所有表进行排序，将记录较少的放在最前面。因此，这里的顺序是B，A。

　　2.3.2贪婪搜索

　　当表数较少时（小于search_depth，默认为63)，这样直接降为穷举搜索，优化器会穷尽所有左深树寻找最优执行计划另外，为了减少巨大的搜索空间带来的巨大的耗尽消耗，优化器使用了一个“懒惰”的参数prune_level（默认开启），具体如何“懒惰”请参考JOIN的复杂度顺序选择。但是，必须至少关联三个表才能“懒惰”，所以这种情况不适用。

　　2.3.3 用尽选择

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

　　因为前面的排序，B表找到的记录比较少，所以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'

　　在极差分析阶段，A.LastName ='zhou'对应的记录大约有51条。

　　所以，IO成本的计算是：51； ref在计算IO成本时会做修正，修改为worst_seek（参考）

　　修改后的IO开销为：15，总开销为：25.2

　　(***) IND_DID B.DepartmentID = A.DepartmentID

　　这是一个成本，需要知道上表的结果才能计算。所以无法分析范围分析

　　这里，我们看到前面的表是B，found_record是1，所以A.DepartmentID只需要对应一条记录即可。

　　因为具体数值未知，也没有直方图，所以只能根据指标统计来计算：

　　A列的Cardinality索引IND_DID的DepartmentID为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-loginnodbplugin1.0.9CREATETABLE`department`(`DepartmentID`int(11)DEFAULTNULL,`DepartmentName`varchar(20)DEFAULTNULL,KEY`IND_D`( `DepartmentID`),KEY`IND_DN`(`DepartmentName`))ENGINE=InnoDBDEFAULTCHARSET=gbk;CREATETABLE`employee`(`LastName`varchar(20)DEFAULTNULL,`DepartmentID`int(11)) DEFAULTNULL,KEY`IND_L_D`(`LastName`),KEY`IND_DID`(`DepartmentID`))ENGINE=InnoDBDEFAULTCHARSET=gbk;foriin`seq11000`;domysql-vvv-uroottest-e'insertintodepartmentvalues(6000000)rerandtpeat* (char(65+rand()*58),rand()*20))';doneforin`seq11000`;domysql-vvv-uroottest-e'insertintoemployeevalues(repeat(char (65+) rand()*58),rand()*20),600000*rand())';doneforin`seq150`;domysql-vvv-uroottest-e'insertintoemployeevalues("zhou ",2776 0)';doneforiin`seq1200`;domysql-vvv-uroottest-e'insertintoemployeevalues(repeat(char(65+rand()*58),rand()*20),第 2776 章ndexfr omemployee;+----------+------------+----------+----------- - --+--------------+-----------+------------+----- - ----+--------+------+------------+---------+|表格|非唯一| Key_name |Seq_in_index|Column_name|Collat​​ion|Cardinality|Sub_part|Packed|Null|Index_type|Comment|+----------+------------+----- - ----+--------------+--------------+-----------+-- - ----------+---------+--------+------+--------- - +---------+|员工|1|IND_L_D|1|姓氏|A|1349|NULL|NULL|YES|BTREE|||员工|1|IND_DID|1|部门ID|A|1349 | NULL|NULL|YES|BTREE||+----------+------------+----------+----- ---------+--------------+-----------+------------ +------------+--------+------+------------+--------- +showindexfromdepartment;+------------+------------+----------+--------- -----+----------------+-----------+------------+- ---------+--------+------+------------+---------+|表|Non_unique|Key_name|Seq_in_index|Column_name|Collat​​ion|Cardinality|Sub_part|Packed|Null|Index_type|Comment|+------------+------------ +-----------+----- ---------+----------------+------ -----+----------- --+---------+--------+------+--- ---------+------- --+|部门|1|IND_D|1|部门ID|A|1001|NULL|NULL|YES|BTREE|||部门|1|IND_DN |1|部门名称|A|1001|NULL|NULL|YES|BTREE| |+------------+------------+------------+----------- ---+----------------+-----------+------------+--- -------+--------+------+------------+---------+

　　4. 构建一个坏案例

　　由于MySQL在关联条件中使用索引统计进行成本估算，在数据分布不均的情况下很容易做出错误的判断。简单地我们构造如下案例：

　　表和索引的结构不变，数据构造如下：

　　foriin`seq110000`;domysql-uroottest-e'insertintodepartmentvalues(600000*rand(),repeat(char(65+rand()*58),rand()*20)) ';doneforin`seq110000`;domysql-uroottest-e'insertintoemployeevalues(repeat(char(65+rand()*58),rand()*20),600000*rand())' ;doneforiin`seq11`;domysql-uroottest-e'insertintoemployeevalues("zhou",27760)';doneforiin`seq110`;domysql-uroottest-e'insertintodepartmentvalues(27760,"TBX")';eq1doneforiin`s `;domysql-uroottest-e'insertintodepartmentvalues(27760,repeat(char(65+rand()*58),rand()*20))';doneexplainselect*fromemployeeeasA,departmentasBwhereA.<//p

pLastName='zhou'andB.部门 ID=A。部门 ID 和 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|使用地点||1|SIMPLE|B|ref|IND_D,IND_DN|IND_D|5|测试. A. DepartmentID|1|使用地点|+----+-------------+-------+------+------- -- --+---------+---------+----------- -- +------+-------------+/p

p这里可以看到，MySQL执行计划使用了表部门上的索引IND_D，那么A表中的一条记录是(zhou,27760);根据B.DepartmentID=27760，1010条记录为返回，然后根据Condition DepartmentName='TBX'进行过滤。/p

p这里可以看到如果B表选择索引IND_DN，效果更好，因为DepartmentName='TBX'只返回10条记录，然后根据条件A.DepartmentID=B.DepartmentID进行过滤。/p

p>