Mysql查询优化器浅析

文章转载自：http://blog.csdn.net/whyangwanfu/article/details/1932828

 

 

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;

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

 

7 存取类型

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

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

const … 常量表

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

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

ref_or_null … 索引使用 '=' 进行存取，并且有可能为 NULL

range … 索引使用 BETWEEN 、 IN 、 >= 、 LIKE 等进行存取

index … 索引全扫描

ALL … 表全扫描

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

SELECT *

FROM Table1

WHERE indexed_column = 5 AND unindexed_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 的查询的格式为： <condition> AND <condition> ，考虑如下的查询语句：

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 的查询格式为： <condition> OR <condition> ，考虑如下的查询语句：

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

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

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

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

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

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

13 UNION

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

SELECT * FROM Table1 WHERE column1='x'

UNION ALL

SELECT * FROM Table1 WHERE column2='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() 的优化情况。考虑下面的查询语句：

SELECT MAX(column1)

FROM Table1

WHERE column1 < 'a';

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

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

SELECT DISTINCT column1 FROM Table1;

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

SELECT column1 FROM Table1 GROUP BY column1;

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