源码下载网浅析MySQL 查询优化器

优化器（The Optimizer）

 

 这篇描述MySQL查询优化器的工作原理。MySQL查询优化器主要为执行的查询决断最有效的路线（routine，走向）。

 

 

 

一。源代码和概念

 

 这部分讨论优化器关键概念，术语，及在MySQL源代码怎么对应的。

 

 

 

1.定义

 

 狭义定义：优化器，就是DBMS为查询时决断要往哪种执行路径的一系列路线。

 

 MySQL是经常调整查询的路线，所以你得把这篇描述的逻辑和在源代码里的做比较。为了使比较容易些，这里会包含相关文件和路径的注解，例如源代码/sql/sql_select.cc，optimize_cond()函数。

 

 当一个查询被转成另一种查询时，其结果是一样的，就会发生语句转化。如下这个查询

 

SELECT ... WHERE 5 = a

就会被转化成为

 

SELECT ... WHERE a = 5

 最明显的语句转化是少的，有些语句转化，是为了更快的执行。

 

 

 

2.优化器源代码

 

 如下伪代码显示了/sql/sql_select.cc中handle_select()函数的逻辑结构。（源代码/sql/sql_select.cc处理SQL查询）

 

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    */

           /* controls 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()、JOIN::optimize()、JOIN::exec()，以及类推。mysql_select()函数的第一部分是调用JOIN::prepare()，此函数用来上下文分析、元数据建立和一些语句转化。查询优化器函数JOIN::optimize()和其所有优化处理中的子路线。当执行完JOIN::optimize()函数后，JOIN::exec()接管并完成JOIN::optimize()函数优化决断后的执行工作。

 

 虽然有JOIN字出现，其实查询优化器的工作会处理所有的查询类型，不单单JOIN联接查询。

 

 

二。首要优化

 

 这部分讨论服务器执行的最重要优化。

 

 

 

1.优化常数关系

 

常数等值传递

 

 如下的表达式会发生语句转化：

 

WHERE column1 = column2 AND column2 = 'x'

 这种表达式，众所周知，如果A=B && B=C => A=C（可等值传递），上句表达式转化后变成：

 

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

 

 

 当且仅当，操作符为如下的任何一个，在column1 <操作符> column2条件中就会发生语句转化：

 

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

注意：等值传递的转化，不适合于BETWEEN。可能也不适合于LIKE，这是后话。

 

 

 

 常数等值传递同样发生在循环中，前一步传递的输出作为后一步传递的输入。

 

源代码见/sql/sql_select.cc，change_cond_ref_to_const()函数。或/sql/sql_select.cc，propagate_cond_constants()函数。

 

 

 

剔除死代码

 

 总是TRUE的条件会发生语句转化，如：

 

WHERE 0=0 AND column1='y'

这种情况下，第一个条件会被剔除，最后为：

 

column1='y'

 源代码见/sql/sql_select.cc，remove_eq_conds()。

 

 

 

 总是FLASE的条件也会发生语句转化，如：

 

WHERE (0 = AND s1 = OR s1 = 7

小括号和前两个条件总是FLASE，最后为：

 

WHERE s1 = 7

 

 

 还有一些情况下，当WHERE语句中代表不可能的条件，查询优化器可能会全部剔除语句，如下：

 

WHERE (0 = AND s1 = 5)

因为这条件永远不为TRUE，在EXPLAIN分析中会显示Impossible WHERE。简单地说，MySQL会说WHERE条件被优化过。

 

 

 

如果一个字段不能为NULL，优化器会剔除所有不相关的IS NULL的条件，这样

 

WHERE not_null_column IS NULL

这种条件总为FLASE情况；且

 

WHERE not_null_column IS NOT NULL

这种条件总为TRUE情况，所以这种字段查询的条件也被剔除。这种判断是很微妙的。举个例：在一个OUT JOIN外联接，被定义成NOT NULL字段仍然含有NULL值，优化器就会单独排除IS NULL条件在这种特殊情况中。

 

 

 

 优化器不会检查所有的Impossible WHERE的条件，因为这方面可能性太多了。例如：

 

CREATE TABLE Table1 (column1 CHAR(1));

...

SELECT * FROM Table1 WHERE column1 = 'Popgo';

优化器不会剔除这种查询的条件，即使在CREATE TABLE定义中使之成为不可能的条件。

 

 

 

可合并的常数值

 

 如下表达式会发生语句转化：

 

WHERE column1 = 1 + 2

最后为：

 

WHERE column1 = 3

 在之前说的常数等值传递 ，优化器很容易将这种查询语句合并在一起。这操作就简化了结果。

 

 

 

常数值和常数表

 

 MySQL常数值，有时不单单指在查询的SQL语句的字面意思上，也可在常数表（constant tables）的内容里。常数表（constant tables）被定义为：

 

1。无记录或一条记录的表

 

2。表的表达式被WHERE条件约束，而且包含的表达式形式column = "constant"，或者表的主键的所有字段，或者任何唯一键的所有字段（唯一键的字段定义为NOT NULL）

 

 例如，Table0表的定义包含：

 

... PRIMARY KEY (column1,column2)

 然后，查询表达式：

 

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

会返回常数表（constant tables）。更多简单地，如果Table1表的定义包含：

 

... unique_not_null_column INT NOT NULL UNIQUE

然后，查询表达式：

 

FROM Table1 ... WHERE unique_not_null_column=5

也会返回常数表（constant tables）。

 

 

 

 这个规则指一个常数表（constant tables）至多有一条记录值。MySQL就会优先评估是否为常数表（constant tables），并找出那个值。这样，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评估这语句时，首先就会发现，按照常数表（constant tables）的第二点定义，查询条件为unique_not_null_column的表Table1是一个常数表（constant tables），它就会取得这个值。

 

如果取值失败，也就是在表Table1里unique_not_null_column = 没值，EXPLAIN后结果：

 

Impossible WHERE noticed after reading const tables

相反，如果取值成功，也就是在表Table1里unique_not_null_column = 为一条记录值，MySQL会转化为如下语句：

 

SELECT 5, Table2.any_column

    FROM Table1, Table2

    WHERE 5 = Table2.any_column

    AND 5 = 5;

 

 

 事实上，这是一个很好的例子。优化器因前面提到的常数等值传递进行一些语句转化。另外，为什么要先描述常数等值传递，因为它在MySQL确认什么是常数表（constant tables）前就先进行了。优化器步骤的顺序，有时是有差别。

 

 虽然很多查询都没常数表（constant tables）参考。应该牢记，以后无论什么时候，常数constant字被提及，它是指任何一个字面上的值或者一个常数表（constant tables）的内容。