MySQL COUNT函数的使用与优化

MySQL COUNT函数的使用与优化 目录

• COUNT 函数做什么用？
• MyISAM 的“神奇”之处
• 简单的COUNT优化
• 使用近似值
• 更复杂的优化

COUNT 函数做什么用？

COUNT 是一个专用的函数，通常有两种不同的方式：计算值和数据行。值指的是非空（Non-NULL）表达式（NULL表示值缺失）。如果我们在 COUNT的参数中指定了列名或其他表达式，则 COUNT 函数是计算该表达式拥有值的次数。这让很多人困惑，相当一部分的原因是值和 NULL 的概念是模糊的。

另一种 COUNT 的形式是简单地计算结果集的数据行数。这是在 MySQL 知道 COUNT 函数参数的表达式不可能为 NULL 时的计算方式。最为典型的例子是 COUNT(*)，你也许会以为这是展开数据表的全部列的一种替代形式。事实上，它会忽略了全部列而仅仅对数据行数进行记数。

一个经常犯的错误是我们在 COUNT 的参数里指定了列名然后以为是对数据行进行计数。如果你是想获取结果中的行数，你应该一直使用 COUNT(*)，这会使得你的查询语句意图更明确并且可以避免性能问题。

MyISAM 的“神奇”之处

一个常见的误解是 MyISAM 对于 COUNT 查询来说会非常快。MyISAM 的 COUNT 查询确实快，但这种快的场景十分有限：COUNT()查询并且没有 WHERE 条件时才能达到这样的效果，而实际这种场景很少见。MySQL 能够对这个语句进行优化的原因是存储引擎总是知道数据表的准确行数。如果 MySQL 知道一个列col不可能为 NULL，它也会将 COUNT(col) 转换为 COUNT()来进行优化。

MyISAM在 COUNT 查询中有 WHERE条件、或其他对值进行计数时 并没有“神奇”之处。相比其他存储引擎可能快也可能慢，这取决于很多其他因素。

简单的COUNT优化

当你想要对数据行的索引覆盖不高的情况，又需要统计所有行数量时可以采用 MyISAM 引擎的 COUNT(*)来进行优化。下面的例子使用了标准的世界数据库去展示查找 ID 大于5的城市数量时的优化力度，你写出的SQL 语句可能如下所示：

SELECT COUNT(*) FROM world.City WHERE ID > 5;

如果使用 SHOW STATUS 检查查询的话会发现扫描了4079行。而如果是采用负向条件查询，并且减去那些 ID 小于等于5的城市数量的话，你会发现可以将扫描结果减少到5行。

SELECT (SELECT COUNT(*) FROM world.City) - COUNT(*) FROM world.City WHERE ID <= 5;

这个查询会读取更少的行是因为在查询优化阶段将查询转换为了常量，使用 EXPLAIN 可以看到：

id select_type table rows Extra 1 PRIMARY City 6 Using where; Using index 2 SUBQUERY NULL NULL Select tables optimized way

一个常见的问题是如何在一个查询语句中完成对同一列的不同值的数量的查询。例如，你想通过一条查询语句查出不同颜色对应的数量。你不能使用诸如 SELECT COUNT(color = 'blue' OR color='red') FROM items来完成查询，因为这样不会区分出不同颜色相应的数量。而你也不能将颜色放入 WHERE 条件中，例如 SELECT COUNT(*) FROM items WHERE color = 'blue' AND color = 'red'由于颜色本身是互斥的，因此可以用下面的方法解决这一问题：

SELECT SUM(IF(color = 'blue', 1, 0)) AS blue, 
SUM(IF(color = 'red', 1, 0)) as red FROM items;

还有一种变通的形式是不是要 SUM，而是 COUNT，只是保证了没有值的表达式的判决表达式是 false：

SELECT COUNT(color = 'blue' OR NULL) as blue,
COUNT(color = 'red' OR NULL) as red FROM items;

使用近似值

有时候并不需要精确的数量，这个时候就可以使用近似值。在 EXPLAIN优化器中给出的估计行数通常可以满足这种场景，此时可以使用 EXPLAIN 来替代真实的查询。

在很多情况下，一个准确的数量与近似值相比低效很多。一个客户曾经要求统计他们网站的活跃用户数量。用户数量被缓存并每隔30分钟更新一次。这本身就不准确，因此使用估计值是可以接受的。这个查询使用了多个 WHERE 条件去保证不会统计非活跃用户或默认用户（拥有特殊的 ID）。移除这些条件，并稍微修改一下 count *** 作就可以变得更高效。一个更进一步的优化是移除不必要的 DISTINCT *** 作，从而移除掉一次 filesort *** 作。优化后的查询速度更快，且返回了几乎准确的结果。

更复杂的优化

通常来说，COUNT查询很难优化，这是因为它通常需要统计很多行（访问很多数据），在 MySQL 中其他可选的办法是使用覆盖索引。如果那还不够的话，可能需要对整个系统应用架构进行调整了。例如考虑统计数据表，或者使用外部的缓存系统（如 Memcached）。我们往往会面临一个类似的两难问题：快速、准确和简单——你只能从中选择两项！

以上就是MySQL COUNT函数的使用与优化的详细内容，更多关于MySQL COUNT的使用与优化的资料请关注脚本之家其它相关文章！