MySQL之优化特定类型的查询

最新推荐文章于 2022-07-12 20:38:26 发布

菜鸟=大神

最新推荐文章于 2022-07-12 20:38:26 发布

阅读量275

点赞数

分类专栏： MySQL 文章标签： MySQL之优化特定类型的查询

本文链接：https://blog.csdn.net/java852987/article/details/83584688

版权

MySQL 专栏收录该内容

14 篇文章 0 订阅

订阅专栏

文章目录

优化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;

经常需要统计列的不同值的数量，可以通过以下查询来解决：

- 1. 使用SUM()
mysql> select sum(if(color='blue', 1, 0)) as blue, sum(if(color='red', 1, 0)) as red from items;
- 2. count()
mysql> select count(color='blue' OR NULL) as blue, count(color='red' OR NULL) as red from items;

使用近似值

有时候某些业务场景并不需要完全精确的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总是将结果放入临时表，然后再读出，再返回给客户端。