MySQL优化特定类型查询

优化COUNT()查询

COUNT()的作用

COUNT()是一个特殊的函数，有两种非常不同的作用：他可以统计某个列值的数量，也可以统计行数。在统计列值时要求列值是非空的(不统计NULL)。如果在COUNT()的括号中指定了列或者列的表达式，则统计的就是这个表达式有值的结果数。

COUNT()的另一个作用是统计结果记得行数。当MySQL确认括号内的表达式值不可能为空时，实际上就是在统计行数。最简单的就是当我们使用COUNT( * )的时候，这种情况下通配符 * 并不会像我们猜想的那样扩展成所有的列，实际上，它会忽略所有的列而直接统计所有的行数。

一个最常见的错误就是，在括号内指定了一个列却希望统计结果集的行数，如果希望知道的是结果集的行数，做好用COUNT( * )，这样写意义清晰，性能也会更好。

MyISAM引擎执行没有任何WHERE条件的COUNT( * )非常快，因为此时无需实际的去计算表的行数，MySQL可以直接利用存储引擎的特性直接获得这个值。如果MySQL知道某列col不可能为NULL值，那么MySQL内部会将COUNT(col)优化为COUNT( * )。

简单的优化

有时候可以使用MyISAM在COUNT( * )全表非常快的这个特性，来加速一些特定条件的COUNT()的查询。

比如一个条件占数据库行数太多的话，可以把条件反转一下，用全表COUNT( * )子查询减去反转条件查找的值。

使用近似值

有时候某些业务场景并不要求完全精确的COUNT值，此时可以用近似值来代替。EXPLAIN出来的优化器估算的行数就是一个不错的近似值，执行EXPLAIN并不需要真正的去执行查询，所以成本很低。很多时候，计算精确值的成本非常高，而计算近似值非常简单。

比如一个网站需要统计当前活跃用户数，这个活跃用户数保存在缓存中，过期时间为30分钟，所以每隔30分钟需要重新计算并放入缓存。因此这个活跃用户数本身就不是精确值，使用近似值是可以接受的。另外，如果要精确统计在线人数，通常WHERE条件会很复杂，一方面需要剔除当前非活跃用户，另一方面还要剔除系统中某些特定ID的默认用户，去掉这些约束条件对总数的影响很小，但却可能很好地提升该查询的性能。更进一步的优化则可以尝试删除DISTINCT这样的约束来避免排序。这样重写的查询要比原来的精确统计的查询快很多，而返回的结果则几乎相同。

更复杂的优化

通常来说，COUNT()都需要扫描大量的行(意味着要访问大量数据)才能获得精确的结果，因此是很难优化的。除了前面的方法，在MySQL层面还能做的就只有索引覆盖扫描了。如果这还不够，就需要考虑修改应用的架构，可以增加汇总表，或者增加类似Memcached这样的外部缓存系统。

优化关联查询

优化关联查询需要注意以下几点：

• 确保ON或USING子句的列上有索引。在创建索引的时候要考虑到关联的顺序。当表A和表B用列C关联的时候，如果优化器的关联顺序是B、A，那就不需要在B表的列上建立索引。没有用到的索引只会带来额外的负担。一般来说，除非有其他理由，否则只需要在关联顺序中的第二个表的相应列上创建索引；
• 确保任何的GROUP BY和ORDER BY中的表达式只涉及到一个表中的列，这样MySQL才有可能使用索引来优化这个过程；
• 当升级MySQL的时候需要注意关联语法、运算符优先级等其他可能会发生变化的地方。因为以前是普通关联的地方可能会变成笛卡尔积，不同类型的关联可能会生成不同的结果。

优化GROUP BY和DISTINCT

在很多情况下，MySQL都使用同样的办法优化这两种查询，事实上，MySQL优化器会在内部处理的时候相互转化这两类查询。他们都可以使用索引来优化，这也是最有效的优化办法。

在MySQL中，当无法使用索引的时候，GROUP BY使用两种策略来完成：使用临时表或者文件排序来做分组。对于任何查询语句，这两种策略的性能都有可以提升的地方。

如果需要对关联查询做分组，并且是按照查找表中的某个列进行分组，那么通常采用查找表的标识列分组的效率会比其他列更高。

不是所有的关联语句的分组都可以改写成在SELECE中直接使用非分组列的形式，设置可能会在服务器上设置SQL_MODE来禁止这样的写法。在分组查询的SELECT中直接使用非分组列通常都不是什么好主意，因为这样的结果通常是不定的，当索引改变，或者优化器选择不同的优化策略时都可能导致结果不一样。

如果没有通过ORDER BY子句显式的指定排序列，当查询使用GROUP BY子句的时候，结果集会自动按照分组的字段进行排序。如果不关心结果集的顺序，而这种默认排序又导致了需要文件排序，则可以使用ORDER BY NULL，让MySQL不再进行文件排序。也可以在GROUP BY子句中直接使用DESC或者ASC关键字，使分组的结果集按需要的方向排序。

优化GROUP BY WITH ROLLUP

分组查询的一个变种就是要求MySQL对返回的分组结果再做一次超级聚合。可以使用WITH ROLLUP子句来实现这种逻辑，但可能会不够优化。可以通过EXPLAIN来观察其执行计划，特别要注意分组是否通过文件排序或者临时表实现的。然后再去掉WITH ROLLUP子句看执行计划是否相同。

很多时候，如果可以，在应用程序中做超级聚合是更好的，虽然这需要返回给客户端更多的结果。也可以在FROM子句中嵌套使用子查询，或者是通过一个临时表存放中间数据，然后和临时表执行UNION来得到最终结果。

最好的办法是尽可能的将WITH ROLLUP功能转移到应用程序中处理。

优化LIMIT分页

在系统需要分页操作的时候，我们通常会使用LIMIT加上偏移量的办法实现，同时加上合适的ORDER BY子句。如果有对应的索引，通常效率会不错，否则，MySQL需要做大量的文件排序操作。

一个常见又令人头疼的问题是，在偏移量非常大的时候，例如LIMIT 1000,20这样的查询，这时MySQL需要查询10020条记录然后只返回最后20条，前面10000条记录都将被抛弃，这样的代价非常高。如果所有的页面被返回的频率都相同，那么这样的查询平均需要访问半个表的数据。要优化这种查询，要么是在页面中限制分页的数量，要么是优化大偏移量的性能。

优化此类分页查询的一个最简单的方法就是尽可能的使用索引覆盖扫描，而不是查询所有的列。然后根据需要做一次关联操作再返回所需的列。对于偏移量很大的时候，这样做的效率会提升非常大。如下面的查询：

SELECT film_id，description