本文通过一个案例来看看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’分别做分析。其中: (more…)