摘要：?Mysql查询优化器浅析（下）译者：杨万富???7存取类型?当我们评估一个条件表达式，MySQL判断该表达式的存取类型。下面是一些存取类型，按照从最优到最差的顺序进行排列：system?…?系统表，并且是常量表const??…?常量表eq_re
?
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
条件。
?
