p231~294. 分为2部分, p231~259, p260~p294. 此为第2部分.
查询优化器的局限性
关联子查询可能很慢
子查询没有索引
UNION可能很慢
UNION比UNION ALL慢,UNION完要做Distinct动作, 导致很慢.- 将条件下沉到每个子句可以提高性能.
参考how-to-optimize-mysql-union-for-high-speed
IN()列表不能太大
IN()太大可能会很慢
MySQL无法并行执行
MIN()和MAX()优化
对非索引列进行MIN()和MAX()会做全表扫描. 包括主键. 所以MAX(索引列) 比 MAX(主键)快.
查询优化器提示(hint)
对优化器选择的执行计划不满意, 可以使用优化器提示的几个提示(hint)来控制最终的执行计划.
- STRAIGHT_JOIN, 2种用法. 1放到SELECT之后, 让所有表按照join顺序来关联.2放到2个表join之间, 固定前后这2个表的关联顺序.
- SQL_CACHE和SQL_NO_CACHE, 提示这个结果集是否应该缓存在查询缓存中.
- optimizer_search_depth, 控制优化器在穷举执行计划时的限度. 如果查询长时间处于"Statistics"状态, 可以调低此参数.
优化特定类型查询
优化COUNT()查询
- count(col)为列值不为NULL的数量
- count(*)是统计总行数
- 对UNION后结果的count()效率不如各子句的count()后求和的效率
- MyISAM保存了行数的值作为表的属性. 只有没有条件的count(*)效率才会很高.
- 使用近似值. 利用
explain给出估算结果.
优化关联查询
- 确保关联列上有索引.
- 确保任何的Group by和Order by只涉及一个表的列. 这样才有可能使用索引来优化.
优化子查询
尽可能少用子查询, 用关联查询替代.
优化Group By和Distinct
当无法使用索引的时候, Group By使用2种策略完成: 使用临时表或文件排序来做分组.
可以通过使用SQL_BIG_RESULT和SQL_SMALL_RESULT来让优化器按你希望的方式运行.
优化LIMIT分页
在偏移量非常大的时候, 如limit 100000,20这样的查询, 会先扫描100000行数据, 再取20条, 效率很慢.
方法1: 索引覆盖.
方法2: 记录上一次查询的id, 如100000条的id是105030, 则语句改写为"where id>105030 limit 20";
优化UNION查询
用UNION ALL代替