Mysql查询优化器浅析（下）

 

Mysql 查询优化器浅析（下）

译者：杨万富

 

 

 

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 条件。