8.2.1.2 How MySQL Optimizes WHERE Clauses[译]

下面是一些快速查询的例子:

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;

下列查询仅使用索引树就可以解决(假设索引的列为数值型，参看14)：

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, ... ;

你也许牺牲可读性，重写你的查询语句，使算法执行更快。但你能避免这个工作，因为MySQL自动做了相似的优化，使查询语句易于理解和维护。下面是些被MySQL自动做的优化：

注：因为对MySQL的优化一直再进行，所以下面并不是MySQL执行优化的全部

1、删除不必要的括号: 

((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)

2、常数合并：

   (a<b AND b=c) AND a=5
-> b>5 AND b=c AND a=5

3、常数条件删除(因为需要常数合并):

(B>=5 AND B=5) OR (B=6 AND 5=5) OR (B=7 AND 5=6)
-> B=5 OR B=6

4、索引使用的常量表达式只计算一次。

5、在一个没有where条件的单表中，对于MyISAM和MEMORY表，count(*)直接从表中检索信息。当仅使用一个表时，对NOT NULL表达式也这样做。

6、早发现无效的常数表达式，MySQL会快速检测到某些SELECT语句是不可能的并且返回空行。

7、如果不使用ORDER BY 或者 聚合函数（count(),min()），HAVING将和WHERE合并。

8、

9、常数表比其它表先读，常数表的定义：空表或只有一行的表；使用where子句中有主键索引或唯一索引的表，这里所有的索引部分使用常数表达式并且索引部分被定义为NOT NULL。

    常量表实例如下：

SELECT * FROM t WHERE primary_key=1;
SELECT * FROM t1,t2
  WHERE t1.primary_key=1 AND t2.primary_key=t1.id;

10、尝试所有可能性来找到表联接的最好联接组合。如果所有在ORDER BY和GROUP BY的列来自同一个表，那么当联接时，该表首先被选中。

11、如果有一个ORDER BY子句和不同的GROUP BY子句，或如果ORDER BY或GROUP BY包含联接队列中的第一个表之外的其它表的列，则创建一个临时表。

12、如果使用SQL_SMALL_RESULT,MySQL使用内存中的一个临时表。

13、每个表的索引被查询，并且使用最好的索引，除非优化器认为使用表扫描更有效。同时，是否表扫描取决于：使用最好的索引是否跨越超过表总数据的30%，但优化器更加复杂，其评估基于其它因素，如表大小、行数和I/O块大小，因此固定比例（30%）不再决定是选择使用索引还是使用扫描。

14、在一些情况下，MySQL能从索引中读出行，甚至不查询数据文件。如果索引使用的所有列是数值类，那么只使用索引树来进行查询。

15、输出每个记录前，跳过不匹配HAVING子句的行（having子句就是在聚合后对组记录进行筛选）。

用户评论

1、使用OR-situations时索引丢失

SELECT * FROM a WHERE index1 = 'foo'
UNION
SELECT * FROM a WHERE index2 = 'baar';

更快于

SELECT * FROM a WHERE index1 = 'foo' OR index2 = 'bar';

2、查询优化也有糟糕的例子，如

SELECT id FROM foo WHERE bar IN (SELECT bar FROM baz WHERE qux='foo')

当foo表是个大表而baz是个很小的表时，子select首先会使用使用消除大部分foo，假如使用下面条语句会怎样呢？

SELECT foo.id FROM foo, baz WHERE foo.bar=baz.bar and baz.qux='foo'

然而，如果第一条操作是DELETE，则后者的举措就不可能了。

3、<>操作，索引被忽略：

SELECT * FROM tab WHERE score <> 0;
使用UNION有明显的效果
(SELECT * FROM tab WHERE score > 0) UNION
(SELECT * FROM tab WHERE score < 0);

转载于:https://my.oschina.net/simaguo/blog/410442