p231~294. 分为2部分, p231~259, p260~p294. 此为第2部分.

查询优化器的局限性

关联子查询可能很慢

子查询没有索引

UNION可能很慢

  • UNION比UNION ALL慢, UNION完要做Distinct动作, 导致很慢.
  • 将条件下沉到每个子句可以提高性能.

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代替

使用用户自定义变量