这一章节讨论能够在 WHERE 处理语句中使用的优化. 样例使用 SELECT 语句, 但是同样适用于 DELETE,UPDATE 语句中的 WHERE 语句.
注意
因为 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 // 可以推导出 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. 索引使用的常量表达只预估一次 (MySQL 索引基于代价模型)
5.COUNT(*) 在单个数据表上执行, 且没有 where 语句, 结果直接从表信息中取回 (对于 MyISAM 和 MEMORY 数据表). 同样也适用于在一个数据表上使用非空的表达式.
6. 尽早的检测非法的常量表达式. 某些 SELECT 可能返回空的结果集.
7. 如果没有使用 GROUP BY 或者其他聚合函数 (COUNT(),MIN().... 其他的聚合函数)HAVING 会被合并到 where
8. 对于每一个 join 的数据表, 会构造一个简单的 WHERE 语句来得到一个快速的 WHERE 评估, 以此对表中的无用的行尽可能的跳过.
9. 查询中所有常量表在其他表之前读取. 常量表的定义如下:
1. 空表或者只有一行的表
2. 数据表的 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. 通过尝试 join 的所有组合来找到最好的组合方式. 如果 ORDER BY 和 GROUP BY 语句里面所有的列都来自同一个数据表, 这个数据表会是 join 的第一个数据表.
11. 如果 ORDER BY 和 GROUP BY 语句不同, 或者如果 ORDER BY,GROUP BY 包含列来自的数据表和 join 队列里第一个数据表不同, 一个临时表将会被创建.
12. 如果你使用了 SQL_SMALL_RESULT 选项, MySQL 使用内存临时表.
13. 每个数据表的索引都会被查询, 会使用一个最好的索引, 除非优化器相信当前选择直接表扫描更加高效. 从前, 最佳索引判断是索引是否能够过滤表的
百分之 30 的数据. 但是固定的百分比将不会是决定使用索引还是表扫描的因素. 当前的优化器现在更加复杂, 基于包含其他因素的估价模型, 比如表的大小, 行的数目,
I/O 块数目.
14. 在有些样例下, MySQL 能够从索引中直接读取行并且不用读取数据文件. 如果索引中所有的行都是数字类型, 仅使用索引树来解决查询.
15. 在每行被输出前, 不满足 HAVING 条件的行被跳过.
某些查询样例非常的快.
- SELECT COUNT(*) FROM tbl_name;
- SELECT MIN(key_part1),MAX(key_part1) FROM tb1_name;
- SELECT MAX(key_part2) FROM tb1_name WHERE key_part1=constant
- SELECT ... FROM tb1_name ORDER BY key_part1,key_part2,....LIMIT 10
- SELECT ... FROM tb1_name ORDER BY key_part1 DESC,key_part2 DESC,.... LIMIT 10;
对于以下查询, MySQL 只用索引树, 假定索引的列是数字类型 (numeric)
- SELECT key_part1,key_part2 FROM tb1_name WHERE key_part1 =val;
- SELECT COUNT(*) FROM tb1_name
- WHERE key_part1=val1 AND key_part2=val2;
- SELECT key_part2 FROM tb1_name GROUP BY key_part1;
以下查询使用索引按照排序顺序取回行而不用再次排序.
- SELECT ... FROM tb1_name
- ORDER BY key_part1,key_part2,...
- SELECT ... FROM tb1_name ORDER BY key_part1 DESC,key_part2 DESC,....;
来源: http://www.bubuko.com/infodetail-2825692.html