MySQL之优化特定类型的查询
文章目录
- 优化COUNT()查询
- 关于MyISAM的神话
- 简单的优化
- 使用近似值
- 优化关联查询
- 优化子查询
- 优化GROUP BY和DISTINCT
- 优化LIMIT分页
- 优化SQL_CALC_FOUND_ROWS
- 优化UNION查询
- 静态查询分析
- Percona Toolkit中的pt-query-advisor能够解析查询日志、分析查询模式,然后给出所有可能存在潜在问题的查询,并给出足够详细的建议。
优化COUNT()查询
- COUNT()可以统计某个列值的数量,也可以统计行数。
- 在统计列值时要求列值是非空的(不统计NULL)。如果在括号中指定了列或列的表达式,则统计的就是这个表达式有值的结果数。
- COUNT()的另一个作用是统计结果集的行数。当MySQL确认括号内的表达式值不可能为空时,实际上就是在统计行数。最好是使用COUNT(*),它并不会像我们猜想的那样扩展成所有的列,实际上,它会猜想所有的列而直接统计所有的行数。这样写意义清晰,性能也会很好。
关于MyISAM的神话
- 总是以为MyISAM的COUNT()函数非常快,这是有前提条件的,即没有任何WHERE条件的COUNT()才非常快。MyISAM可以直接利用存储引擎的特性直接获取这个值而无需实际计算。如果MySQL知道某列不可能为NULL值,那么MySQL内部会将COUNT(col)表达式转换为COUNT();
简单的优化
可以利用MyISAM在COUNT(*)全表非常快的特性,来加速一些特性条件的查询。例如:
mysql> select count(*) from city where id > 5;
通过SHOW STATUS的结果可以看到该查询需要扫描4097行数据。如果将条件反转一下,只查询ID小于5的城市数,然后用总城市数-结果就能得到同样的结构,却可以将扫描的行数减少到5行以内:
mysql> select (select count(*) from city) - count(*) from city where id <= 5;
经常需要统计列的不同值的数量,可以通过以下查询来解决:
- 使用SUM()
mysql> select sum(if(color=’blue’, 1, 0)) as blue, sum(if(color=’red’, 1, 0)) as red from items;
- 使用SUM()
- count()
mysql> select count(color=’blue’ OR NULL) as blue, count(color=’red’ OR NULL) as red from items;
- count()
使用近似值
- 有时候某些业务场景并不需要完全精确的COUNT值,此时可以使用近似值来代替。EXPLAIN出来的优化器估算的行数就是一个不错的近似值,执行EXPLAIN并不需要真正地去执行查询,所以成本很低。
- 去掉DISTINCT可以避免文件排序。
优化关联查询
- 确保ON或USING子句的列上有索引。在创建索引的时候需要考虑关联的顺序,一般来说,除非有其他理由,只在关联顺序的第二个表的相应列上创建索引。
- 确保任何的GROUP BY和ORDER BY中的表达式只涉及到一个表的列,这样MySQL才有可能使用索引来优化这个过程。
- 升级MySQL的时候要注意:关联语法、运算符的优先级问题。因为以前是普通关联的地方可能会变成笛卡尔积,不同类型的关联可能会生成不同的结果。
优化子查询
关于子查询的优化是尽可能使用关联查询代替,但这不是绝对的,使用测试来验证这一点。
优化GROUP BY和DISTINCT
- 使用索引来优化这两类查询,是最有效的优化方法。
- 在MySQL中,当无法使用索引的时候,GROUP BY使用两种策略来完成:使用临时表或者文件排序来做分组。可以通过查询提示
SQL_BIG_RESULT或SQL_SMALL_RESULT
来让优化器按照你希望的方式运行。 - 如果需要对关联查询做分组,并且按照查找表的某个列进行分组,那么通常按照查找表的标识列进行分组比较好。
- 子查询需要创建和填充临时表,而且子查询创建的临时表是没有任何索引的。
- 将要将MySQL的SQL_MODE设置为包含ONLY_FULL_GROUP_BY,这时SELECT仅包含GROUP BY后的表达式,或聚合函数。
- 如果没有指定ORDER BY排序列,当查询中使用GROUP BY子句的时候,结果集会自动按照分组的字段进行排序。如果不关心分组结果集的顺序,而这种默认排序会导致需要文件排序,则可以使用
ORDER BY NULL
,让MySQL不再进行文件排序。 - 优化GROUP BY WITH ROLLUP。表示对分组的结果再做一次超级聚合。但这样可能不够优化。最好的方法是尽可能的将WITH ROLLUP功能转移到应用程序中处理。
优化LIMIT分页
- ORDER BY子句在没有索引的时候,MySQL需要做大量的文件排序操作。
- 当偏移量非常大的时候,例如
LIMIT 1000, 20
这样的查询,这时MySQL需要查询10020条记录然后只返回最后20条,前面的10000条记录都将被抛弃,这样的代价是非常高的。如果所有的页面被访问的频率相同,那么这样的查询平均需要访问半个表的数据。要优化这样的查询,要么是在页面中限制分页的数量,要么是优化大偏移量的性能。 优化此类查询的最简单的方法是尽可能地使用索引覆盖扫描,而不是查询所有的列。然后根据需要再做一次关联操作返回需要的列。
mysql> select film_id, description from film order by title limit 50, 5;
-优化如下:
mysql> select film_id, description from film inner join(select film_id from film order by title limit 50,5) as lim using(film_id);
“延迟关联”将大大提升查询的效率。
- 有时候也可以将LIMIT查询转换为已知位置的查询,让MySQL通过范围扫描获取到对应的结果。
优化SQL_CALC_FOUND_ROWS
- 在LIMIT语句中加上SQL_CALC_FOUND_ROWS提示,这样就可以获得去掉LIMIT之后满足条件的行数,因此可以作为分页的总数。但MySQL只有在扫描了所有满足条件的行以后,才会知道行数,所有加上这个提示之后,MySQL不管是否需要,都会扫描所有满足条件的行,然后在抛弃掉不需要的行,而不是在满足LIMIT的行数后就终止扫描。所有该提示的代价可能非常高。
- 另一种做法是先获取并缓存较多的数据。例如,缓存1000条数据,如果结果集小于1000,则显示所有的分页链接。如果大于1000条,则显示类似“找到的结果多于1000条”之类的按钮。
- 有时候也可以考虑使用EXPLAIN的结果中的rows列的值来作为结果集总数的近似值。当需要精确值的时候,再单独使用COUNT(*)来满足需求。
优化UNION查询
- MySQL总是通过创建并填充临时表的方式来执行UNION查询。经常需要将WHERE、LIMIT、ORDER BY等子句“下推”到UNION的各个子查询中,以便优化器可以充分利用这些条件进行优化。
- 除非确实需要消除重复行,否则就一定要使用UNION ALL。如果没有ALL关键字,MySQL会给临时表加上DISTINCT选项,这会导致对整个临时表的数据做唯一性检查。这样做的代价非常高。
- 事实上,MySQL总是将结果放入临时表,然后再读出,再返回给客户端。
静态查询分析
Percona Toolkit中的pt-query-advisor能够解析查询日志、分析查询模式,然后给出所有可能存在潜在问题的查询,并给出足够详细的建议。
参考<高性能MySQL>
还没有评论,来说两句吧...