MySQL之优化特定类型的查询

心已赠人 2022-05-16 15:48 198阅读 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总是将结果放入临时表，然后再读出，再返回给客户端。

# 静态查询分析 #

## Percona Toolkit中的pt-query-advisor能够解析查询日志、分析查询模式，然后给出所有可能存在潜在问题的查询，并给出足够详细的建议。 ##

**参考<高性能MySQL>**