MySQL查询优化器浅析

1 定义

　　Mysql查询优化器的工作是为查询语句选择合适的执行路径。查询优化器的代码一般是经常变动的，这和存储引擎不太一 样。因此，需要理解最新版本的查询优化器是如何组织的，请参考相应的源代码。整体而言，优化器有很多相同性，对mysql一个版本的优化器做到整体掌握， 理解起mysql新版本以及其他数据库的优化器都是类似的。

　　优化器会对查询语句进行转化，转化等价的查询语句。举个例子，优化器会将下面语句进行转化：

　　SELECT … WHERE 5=a;

　　转化后的等价语句为：

　　SELECT … WHERE a=5;

　　因为这两个语句的结果集是一致的，所以这两个语句是等价的。

　　这里我需要提出一点需要注意的，如果查询语句没带order by。查询语句1出现的结果为(1,1),(2,2)，查询语句2出现的结果为(2,2),(1,1)，我们会认为这是等价的，因为不带order by的查询语句是无序的，怎么排序都行。

　　2 代码组织

　　在内核当中handle_select()函数是处理查询语句的顶层函数，里面有两个分支，一个是处理带union的情况，另外一个是处理不带union的情况，这里我们只是列出一个简单的路径便于说明，调用层次见下图。　

handle_select()
　 mysql_select()
　　 JOIN::prepare()
　　　 setup_fields()
　　 JOIN::optimize()　　　　　　/* optimizer is from here ... */
　　　 optimize_cond()
　　　 opt_sum_query()
　　　 make_join_statistics()
　　　　 get_quick_record_count()
　　　　 choose_plan()
　　　　　 /* Find the best way to access tables */
　　　　　 /* as specified by the user.　　　　　*/
　　　　　 optimize_straight_join()
　　　　　　 best_access_path()
　　　　　 /* Find a (sub-)optimal plan among all or subset */
　　　　　 /* of all possible query plans where the user　　*/
　　　　　 /* controlls the exhaustiveness of the search.　 */
　　　　　 greedy_search()
　　　　　　 best_extension_by_limited_search()
　　　　　　　 best_access_path()
　　　　　 /* Perform an exhaustive search for an optimal plan */
　　　　　 find_best()
　　　 make_join_select()　　　　/* ... to here */
　　 JOIN::exec()上面的缩进表示函数的相互调用关系，因此可以看出handle_select()调用函数mysql_select(),mysql_select()调用JOIN::prepare()，等等。

　　mysql_select()首先调用函数JOIN::prepare()进行语句分析、元数据设置、子查询转化等等。然后调用函数JOIN::optimize()进行优化，选出最后的执行计划。最后调用函数JOIN::exec()执行该执行计划。

　　尽管出现了单词“JOIN”，这些优化函数是为所有的查询语句服务的，不管你是什么查询类型。

　　函数optimize_cond()和函数opt_sum_query()是执行一些转化操作。函数make_join_statistics()对所有可用索引统计信息进行分析。

　　3 常量转化

　　对类似下面的表达式可以进行转化：

　　WHERE column1 = column2 AND column2 = 'x';

　　因为我们知道：如果A=B and B=C，那么A=C。所以上面的表达式可以转化为：

　　WHERE column1 = 'x' AND column2 = 'x';

　　对于column1 column2，只要是属于下面的操作符之一就可以进行类似的转化：

　　=,<,>,<=,>=,<>,<=>,LIKE

　　从中我们也可以看出，对于BETWEEN的情况是不进行转换的。

　　4 无效代码的排除

　　见如下表达式：

　　WHERE 0=0 AND column1='y'

　　因为第一个条件是始终为true的，所以可以移除该条件，变为：

　　WHERE column1='y'

　　再见如下表达式：

　　WHERE (0=1 AND s1=5) OR s1=7

　　因为前一个括号内的表达式始终为false，因此可以移除该表达式，变为：

　　WHERE s1=7

　　一些情况下甚至可以将整个WHERE子句去掉，见下面的表达式：

　　WHERE (0=1 AND s1=5)我们可以看到，WHERE子句始终为FALASE，那么WHERE条件是不可能发生的。当然我们也可以讲，WHERE条件被优化掉了。

　　如果一个列的定义是不允许为NULL，那么：

　　WHERE not_null_column IS NULL

　　该条件是始终为false的，再看：

　　WHERE not_null_column IS NOT NULL

　　该条件是始终为true的，因此这样的表达式也是可以从条件表达式中删除的。

　　当然，也是有特殊情况的，比如在out join中，被定义为NOT NULL的列也可能包含NULL值。在这种情况下，IS NULL条件是被保留的。

　　当然优化器没有对所有的情况进行检测，因为这实在太复杂了。举个例子：

　　CREATE TABLE Table1(column1 CHAR(1));

　　…

　　SELECT * FROM Table1 WHERE column1 = 'Canada';

　　尽管该条件是无效条件，优化器也不会将它移除。

　　5 常量计算

　　如下表达式：

　　WHERE columb1 = 1 + 2

　　转化为：

　　WHERE columb1 = 3

　　6 常量以及常量表

　　常量表的定义如下：

　　1) 一个表只有0行或者1行数据。

　　2) 在WHERE子句中包含条件column = constant，并且这些列是primary key，或者这些列是UNIQUE(假设该UNIQUE同时被定义为NOT NULL)。这样生成的查询结果也可以成为常量表。

　　如果表Table0定义中包含：

　　… PRIMARY KEY(column1,column2)

　　再看下面的语法：

　　FROM Table0 … WHERE column1=5 AND column2=7 …

　　那么该语句返回的就是常量表。

　　举个更简单的情况，建设Table1定义中包含:

　　… unique_not_null_column INT NOT NULL UNIQUE

　　再看下面的语法：

　　FROM Table1 ... WHERE unique_not_null_column=5

　　该语句返回的也是常量表。

　　从例子中我们可以看出常量表最多只有1个行值。MySQL会预先评估常量表，找出这个值，然后将这个值引入到查询语句中进行优化，举例如下：

　　SELECT Table1.unique_not_null_column, Table2.any_column

　　FROM Table1, Table2

　　WHERE Table1.unique_not_null_column = Table2.any_column

　　AND Table1.unique_not_null_column = 5;

　　在评估这个查询语句时，MySQL首先发现通过Table1.unique_not_null_column条件的限制，Table1会变成一个常量表。然后，取回该值。

　　如果取回操作失败(Table1中没有行满足条件unique_not_null_column = 5)，那么该常量表就包含0行，那么如果对该语句执行EXPLAIN操作，会得到提示信息：

　　Impossible WHERE noticed after reading const tables

　　另外一种情况是取回操作成功(Table1中严格只有一行满足条件unique_not_null_column = 5)，那么常量表中包含一条数据，并且MySQL会将查询语句转化为：

　　SELECT 5, Table2.any_column

　　FROM Table1, Table2

　　WHERE 5 = Table2.any_column

　　AND 5 = 5;

　　实际上，这个例子是个复杂的例子，这里面也用到了上文所说的常量转化。　　

　　7 存取类型

　　当我们评估一个条件表达式，MySQL判断该表达式的存取类型。下面是一些存取类型，按照从最优到最差的顺序进行排列：

　　system … 系统表，并且是常量表

　　const … 常量表

　　eq_ref … unique/primary索引，并且使用的是'='进行存取

　　ref … 索引使用'='进行存取

　　ref_or_null … 索引使用'='进行存取，并且有可能为NULLrange … 索引使用BETWEEN、IN、>=、LIKE等进行存取

　　index … 索引全扫描

　　ALL … 表全扫描

　　优化器根据存取类型选择合适的驱动表达式。考虑如下的查询语句：

以下是引用片段：
　　SELECT*
　　FROMTable1
　　WHEREindexed_column=5ANDunindexed_column=6

　　因为indexed_column拥有更好的存取类型，所以更有可能使用该表达式做为驱动表达式。这里只考虑简单的情况，不考虑特殊的情况。

　　那么驱动表达式的意思是什么呢?考虑到这个查询语句有两种可能的执行方法:

　　1) 不好的执行路径：读取表的每一行(称为“全表扫描”)，对于读取到的每一行，检查相应的值是否满足indexed_column以及unindexed_column对应的条件。

　　2) 好的执行路径：通过键值indexed_column=5查找B树，对于符合该条件的每一行，判断是否满足unindexed_column对应的条件。

　　一般情况下，索引查找比全表扫描需要更少的存取路径，尤其当表数据量很大，并且索引的类型是UNIQUE的时候。因此称它为好的执行路径，使用indexed_column列作为驱动表达式。

　　8 范围存取类型

　　一些表达式可以使用索引，但是属于索引的范围查找。这些表达式通常对应的操作符是：>、>=、<、<=、IN、LIKE、BETWEEN。

　　对优化器而言，如下表达式：

　　column1 IN (1,2,3)

　　该表达式与下面的表达式是等价的：

　　column1 = 1 OR column1 = 2 OR column1 = 3

　　并且MySQL也是认为它们是等价的，所以没必要手动将IN改成OR,或者把OR改成IN。

　　优化器将会对下面的表达式使用索引范围查找：

　　column1 LIKE 'x%'但对下面的表达式就不会使用到索引了：

　　column1 LIKE '%x'

　　这是因为当首字符是通配符的时候，没办法使用到索引进行范围查找。

　　对优化器而言，如下表达式：

　　column1 BETWEEN 5 AND 7

　　该表达式与下面的表达式是等价的：

　　column1 >= 5 AND column1 <= 7

　　同样，MySQL也认为它们是等价的。

　　如果需要检查过多的索引键值，优化器将放弃使用索引范围查找，而是使用全表扫描的方式。这样的情况经常出现如下的情况下：索引是多层次的二级索引，查询条件是'<'以及是'>'的情况。

　　9 索引存取类型

　　考虑如下的查询语句：

　　SELECT column1 FROM Table1;

　　如果column1是索引列，优化器更有可能选择索引全扫描，而不是采用表全扫描。这是因为该索引覆盖了我们所需要查询的列。

　　再考虑如下的查询语句：

　　SELECT column1,column2 FROM Table1;

　　如果索引的定义如下，那么就可以使用索引全扫描：

　　CREATE INDEX … ON Table1(column1,column2);

　　也就是说，所有需要查询的列必须在索引中出现。

　　10　转换

　　MySQL对简单的表达式支持转换。比如下面的语法：

　　WHERE -5 = column1

　　转换为：

　　WHERE column1 = -5

　　尽管如此，对于有数学运算存在的情况不会进行转换。比如下面的语法：

　　WHERE 5 = -column1

　　不会转换为：

　　WHERE column1 = -5

　　11 AND

　　带AND的查询的格式为： AND ，考虑如下的查询语句：

　　WHERE column1='x' AND column2='y'

　　优化的步骤：

　　1) 如果两个列都没有索引，那么使用全表扫描。　2) 否则，如果其中一个列拥有更好的存取类型(比如，一个具有索引，另外一个没有索引;再或者，一个是唯一索引，另外一个是非唯一索引)，那么使用该列作为驱动表达式。

　　3) 否则，如果两个列都分别拥有索引，并且两个条件对应的存取类型是一致的，那么选择定义索引时的先定义的索引。

　　举例如下：

　　CREATE TABLE Table1 (s1 INT,s2 INT);

　　CREATE INDEX Index1 ON Table1(s2);

　　CREATE INDEX Index2 ON Table1(s1);

　　…

　　SELECT * FROM Table1 WHERE s1=5 AND s2=5;

　　优化器选择s2=5作为驱动表达式，因为s2上的索引是新建的。

　　12 OR

　　带OR的查询格式为： OR ，考虑如下的查询语句：

　　WHERE column1='x' OR column2='y'

　　优化器做出的选择是采用全表扫描。

　　当然，在一些特定的情况，可以使用索引合并，这里不做阐述。

　　如果两个条件里面设计的列是同一列，那么又是另外一种情况，考虑如下的查询语句：

　　WHERE column1='x' OR column1='y'

　　在这种情况下，该查询语句采用索引范围查找。

　　13 UNION

　　所有带UNION的查询语句都是单独优化的，考虑如下的查询语句：

以下是引用片段：
　　SELECT*FROMTable1WHEREcolumn1='x'
　　UNIONALL
　　SELECT*FROMTable1WHEREcolumn2='y'

　　如果column1与column2都是拥有索引的，每个查询都是使用索引查询，然后合并结果集。

　　14 NOT,<>

　　考虑如下的表达式：

　　Column1<> 5

　　从逻辑上讲，该表达式等价于下面的表达式：

　　Column1<5 OR column1>5然而，MySQL不会进行这样的转换。如果你觉得使用范围查找会更好一些，应该手动地进行转换。

　　考虑如下的表达式：

　　WHERE NOT (column1!=5)

　　从逻辑上讲，该表达式等价于下面的表达式：

　　WHERE column1=5

　　同样地，MySQL也不会进行这样的转换。

　　15 ORDER BY

　　一般而言，ORDER BY的作用是使结果集按照一定的顺序排序，如果可以不经过此操作就能产生顺序的结果，可以跳过该ORDER BY操作。

　　考虑如下的查询语句：

　　SELECT column1 FROM Table1 ORDER BY 'x';

　　优化器将去除该ORDER BY子句，因为此处的ORDER BY子句没有意义。

　　再考虑另外的一个查询语句：

　　SELECT column1 FROM Table1 ORDER BY column1;

　　在这种情况下，如果column1类上存在索引，优化器将使用该索引进行全扫描，这样产生的结果集是有序的，从而不需要进行ORDER BY操作。

　　再考虑另外的一个查询语句：

　　SELECT column1 FROM Table1 ORDER BY column1+1;

　 　假设column1上存在索引，我们也许会觉得优化器会对column1索引进行全扫描，并且不进行ORDER BY操作。实际上，情况并不是这样，优化器是使用column1列上的索引进行全扫表，仅仅是因为索引全扫描的效率高于表全扫描。对于索引全扫描的结果集 仍然进行ORDER BY排序操作。

　　16 GROUP BY

　　这里列出对GROUP BY子句以及相关集函数进行优化的方法：

　　1) 如果存在索引，GROUP BY将使用索引。

　　2) 如果没有索引，优化器将需要进行排序，一般情况下会使用HASH表的方法。

　　3) 如果情况类似于“GROUP BY x ORDER BY x”,优化器将会发现ORDER BY子句是没有必要的，因为GROUP BY产生的结果集是按照x进行排序的。

　　4) 尽量将HAVING子句中的条件提升中WHERE子句中。

　 　5) 对于MyISAM表，“SELECT COUNT(*) FROM Table1;”直接返回结果，而不需要进行表全扫描。但是对于InnoDB表，则不适合该规则。补充一点，如果column1的定义是NOT NULL的，那么语句“SELECT COUNT(column1) FROM Table1;”等价于“SELECT COUNT(*) FROM Table1;”。

　　6) 考虑MAX()以及MIN()的优化情况。考虑下面的查询语句：

以下是引用片段：
　　SELECTMAX(column1)
　　FROMTable1
　　WHEREcolumn1<'a';

　　如果column1列上存在索引，优化器使用'a'进行索引定位，然后返回前一条记录。

　　7) 考虑如下的查询语句:

　　SELECT DISTINCT column1 FROM Table1;

　　在特定的情况下，语句可以转化为：

　　SELECT column1 FROM Table1 GROUP BY column1;

　　该转换的前提条件是：column1上存在索引，FROM上只有一个单表，没有WHERE条件并且没有LIMIT条件。