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 <operator> column2，只要<operator>是属于下面的操作符之一就可以进行类似的转化：

=,<,>,<=,>=,<>,<=>,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;

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