7.2.4. MySQL怎样优化WHERE子句
该节讨论为处理WHERE子句而进行的优化。例子中使用了SELECT语句，但相同的优化也适用DELETE和UPDATE语句中的WHERE子句。
请注意对MySQL优化器的工作在不断进行中，因此该节并不完善。MySQL执行了大量的优化，本文中所列的并不详尽。 下面列出了MySQL执行的部分优化： · 去除不必要的括号：
????????????????? ((a AND b) AND c OR (((a AND b) AND (c AND d)))) ?????????????????-> (a AND b AND c) OR (a AND b AND c AND d)
· 常量重叠：
????????????????? (a b>5 AND b=c AND a=5
· 去除常量条件(由于常量重叠需要)：
????????????????? (B>=5 AND B=5) OR (B=6 AND 5=5) OR (B=7 AND 5=6) ?????????????????-> B=5 OR B=6
· 索引使用的常数表达式仅计算一次。
? ? ? ? ?
对于MyISAM和HEAP表，在一个单个表上的没有一个WHERE的COUNT(*)直接从表中检索信息。当仅使用一个表时，对NOT NULL表达式也这样做。 无效常数表达式的早期检测。MySQL快速检测某些SELECT语句是不可能的并且不返回行。 如果不使用GROUP BY或分组函数(COUNT()、MIN()??)，HAVING与WHERE合并。
对于联接内的每个表，构造一个更简单的WHERE以便更快地对表进行WHERE计算并且也尽快跳过记录。
所有常数的表在查询中比其它表先读出。常数表为： o 空表或只有1行的表。 o 与在一个PRIMARY KEY或UNIQUE索引的WHERE子句一起使用
的表，这里所有的索引部分使用常数表达式并且索引部分被定义为NOT NULL。 下列的所有表用作常数表：
mysql> SELECT * FROM t WHERE primary_key=1; mysql> SELECT * FROM t1,t2
WHERE t1.primary_key=1 AND t2.primary_key=t1.id;
尝试所有可能性便可以找到表联接的最好联接组合。如果所有在ORDER BY和GROUP BY的列来自同一个表，那么当联接时，该表首先被选中。 ? 如果有一个ORDER BY子句和不同的GROUP BY子句，或如果ORDER BY或GROUP BY包含联接队列中的第一个表之外的其它表的列，则创建一个临时表。 ? 如果使用SQL_SMALL_RESULT，MySQL使用内存中的一个临时表。
?
每个表的索引被查询，并且使用最好的索引，除非优化器认为使用表扫描更有效。是否使用扫描取决于是否最好的索引跨越超过30%的表。优化器更加复杂，其估计基于其它因素，例如表大小、行数和I/O块大小，因此固定比例不再决定选择使用索引还是扫描。 ? 在一些情况下，MySQL能从索引中读出行，甚至不查询数据文件。如果索引使用的所有列是数值类，那么只使用索引树来进行查询。 ? 输出每个记录前，跳过不匹配HAVING子句的行。
?
下面是一些快速查询的例子：
SELECT COUNT(*) FROM tbl_name;
SELECT MIN(key_part1),MAX(key_part1) FROM tbl_name;
SELECT MAX(key_part2) FROM tbl_name WHERE key_part1=constant;
SELECT ... FROM tbl_name
ORDER BY key_part1,key_part2,... LIMIT 10;
SELECT ... FROM tbl_name
ORDER BY key_part1 DESC, key_part2 DESC, ... LIMIT 10;
下列查询仅使用索引树就可以解决(假设索引的列为数值型)：
SELECT key_part1,key_part2 FROM tbl_name WHERE key_part1=val;
SELECT COUNT(*) FROM tbl_name
WHERE key_part1=val1 AND key_part2=val2;
SELECT key_part2 FROM tbl_name GROUP BY key_part1;
下列查询使用索引按排序顺序检索行，不用另外的排序：
SELECT ... FROM tbl_name
ORDER BY key_part1,key_part2,... ;
SELECT ... FROM tbl_name
ORDER BY key_part1 DESC, key_part2 DESC, ... ;
7.2.5. 范围优化
7.2.5.1. 单元素索引的范围访问方法 7.2.5.2. 多元素索引的范围访问方法
range访问方法使用单一索引来搜索包含在一个或几个索引值距离内的表记录的子集。可以用于单部分或多元素索引。后面的章节将详细描述如何从WHERE子句提取区间。
7.2.5.1. 单元素索引的范围访问方法
对于单元素索引，可以用WHERE子句中的相应条件很方便地表示索引值区间，因此我们称为范围条件而不是“区间”。 单元素索引范围条件的定义如下：
· 对于BTREE和HASH索引，当使用=、<=>、IN、IS NULL或者IS NOT NULL
操作符时，关键元素与常量值的比较关系对应一个范围条件。
· 对于BTREE索引，当使用>、<、>=、<=、BETWEEN、!=或者<>，或者LIKE 'pattern'(其中 'pattern'不以通配符开始)操作符时，关键元素与常量值的比较关系对应一个范围条件。 · 对于所有类型的索引，多个范围条件结合OR或AND则产生一个范围条件。
前面描述的“常量值”系指：
· 查询字符串中的常量
· 同一联接中的const或system表中的列 · 无关联子查询的结果
· 完全从前面类型的子表达式组成的表达式 下面是一些WHERE子句中有范围条件的查询的例子：
SELECT * FROM t1 WHERE key_col > 1 AND key_col < 10;
SELECT * FROM t1 WHERE key_col = 1 OR key_col IN (15,18,20);
SELECT * FROM t1
WHERE key_col LIKE 'ab%'
OR key_col BETWEEN 'bar' AND 'foo';
请注意在常量传播阶段部分非常量值可以转换为常数。
MySQL尝试为每个可能的索引从WHERE子句提取范围条件。在提取过程中，不能用于构成范围条件的条件被放弃，产生重叠范围的条件组合到一起，并且产生空范围的条件被删除。
例如，考虑下面的语句，其中key1是有索引的列，nonkey没有索引：
SELECT * FROM t1 WHERE
(key1 < 'abc' AND (key1 LIKE 'abcde%' OR key1 LIKE '%b')) OR (key1 < 'bar' AND nonkey = 4) OR (key1 < 'uux' AND key1 > 'z');
key1的提取过程如下：
1. 用原始WHERE子句开始：
2. (key1 < 'abc' AND (key1 LIKE 'abcde%' OR key1 LIKE '%b')) OR 3. (key1 < 'bar' AND nonkey = 4) OR 4. (key1 < 'uux' AND key1 > 'z')
5. 删除nonkey = 4和key1 LIKE '%b'，因为它们不能用于范围扫描。删除它们的正确途径是用TRUE替换它们，以便进行范围扫描时不会丢失匹配的记录。用TRUE替换它们后，可以得到：
6. (key1 < 'abc' AND (key1 LIKE 'abcde%' OR TRUE)) OR 7. (key1 < 'bar' AND TRUE) OR 8. (key1 < 'uux' AND key1 > 'z')
9. 取消总是为true或false的条件：
· (key1 LIKE 'abcde%' OR TRUE)总是true · (key1 < 'uux' AND key1 > 'z')总是false 用常量替换这些条件，我们得到：
(key1 < 'abc' AND TRUE) OR (key1 < 'bar' AND TRUE) OR (FALSE)
删除不必要的TRUE和FALSE常量，我们得到
(key1 < 'abc') OR (key1 < 'bar')
10.将重叠区间组合成一个产生用于范围扫描的最终条件：
11. (key1 < 'bar')
总的来说(如前面的例子所述)，用于范围扫描的条件比WHERE子句限制少。MySQL再执行检查以过滤掉满足范围条件但不完全满足WHERE子句的行。
范围条件提取算法可以处理嵌套的任意深度的AND/OR结构，并且其输出不依赖条件在WHERE子句中出现的顺序。 7.2.5.2. 多元素索引的范围访问方法
多元素索引的范围条件是单元素索引的范围条件的扩展。多元素索引的范围条件将索引记录限制到一个或几个关键元组内。使用索引的顺序，通过一系列关键元组来定义关键元组区间。
例如，考虑定义为key1(key_part1, key_part2, key_part3)的多元素索引，以及下面的按关键字顺序所列的关键元组：
key_part1 key_part2 key_part3
NULL 1 'abc' NULL 1 'xyz' NULL 2 'foo' 1 1 'abc' 1 1 'xyz' 1 2 'abc' 2 1 'aaa'
条件key_part1 = 1定义了下面的范围：
(1，-inf，-inf) <= (key_part1，key_part2，key_part3) < (1，+inf，+inf)
范围包括前面数据集中的第4、5和6个元组，可以用于范围访问方法。 通过对比，条件key_part3 = 'abc'不定义单一的区间，不能用于范围访问方法。 下面更加详细地描述了范围条件如何用于多元素索引中。
· 对于HASH索引，可以使用包含相同值的每个区间。这说明区间只能由下面形式的条件产生：
????????????????? key_part1 cmp const1
????????????????? AND key_part2 cmp const2 ????????????????? AND ... ?????????????????AND key_partN cmp constN;
这里，const1，const2，...为常量，cmp是=、<=>或者IS NULL比较操作符之一，条件包括所有索引部分。(也就是说，有N 个条件，每一个对应N-元素索引的每个部分）。
关于常量的定义，参见7.2.5.1节，“单元素索引的范围访问方法”。 例如，下面为三元素HASH索引的范围条件：
key_part1 = 1 AND key_part2 IS NULL AND key_part3 = 'foo'
· 对于BTREE索引，区间可以对结合AND的条件有用，其中每个条件用一个常量值通过=、<=>、IS NULL、>、<、>=、<=、!=、<>、BETWEEN或者LIKE 'pattern' (其中'pattern'不以通配符开头)比较一个关键元素。区间可以足够长以确定一个包含所有匹配条件(或如果使用<>或!=，为两个区间)的记录的单一的关键元组。例如，对于条件： ????????????????? key_part1 = 'foo' AND key_part2 >= 10 AND key_part3 > 10
单一区间为：
('foo'，10，10)
< (key_part1，key_part2，key_part3) < ('foo'，+inf，+inf)
创建的区间可以比原条件包含更多的记录。例如，前面的区间包括值('foo'，11，0)，不满足原条件。
· 如果包含区间内的一系列记录的条件结合使用OR，则形成包括一系列包含在区间并集的记录的一个条件。如果条件结合使用了AND，则形成包括一系列包含在区间交集内的记录的一个条件。例如，对于两部分索引的条件：
?????????????????(key_part1 = 1 AND key_part2 < 2) ?????????????????OR (key_part1 > 5)
区间为： (1, -inf) < (key_part1, key_part2) < (1, 2) (5, -inf) < (key_part1, key_part2)
在该例子中，第1行的区间左侧的约束使用了一个关键元素，右侧约束使用了两个关键元素。第2行的区间只使用了一个关键元素。EXPLAIN输出的key_len列表示所使用关键字前缀的最大长度。
在某些情况中，key_len可以表示使用的关键元素，但可能不是你所期望的。假定key_part1和key_part2可以为NULL。则key_len列显示下面条件的两个关键元素的长度：
key_part1 >= 1 AND key_part2 < 2
但实际上，该条件可以变换为：
key_part1 >= 1 AND key_part2 IS NOT NULL
7.2.5.1节，“单元素索引的范围访问方法”描述了如何进行优化以结合或删除单元素索引范围条件的区间。多元素索引范围条件的区间的步骤类似。