COUNT()聚合函数,以及如何优化使用了该函数的查询,很可能是MySQL中最容易被误解的前10个话题之一,在网上随便搜索一下就能看到很多错误的理解,可能比我们想象的多得多。
在做优化之前,先来看看COUNT()函数的真正作用是什么。
COUNT()的作用
COUNT()是一个特殊的函数,有两种非常不同的作用:它可以统计某个列值的数量,也可以统计行数。在统计列值时要求列值时非空的(不统计NULL)。如果在COUNT()的括号中指定了列或列的表达式,统计的就是这个表达式有值的结果数。因为很多人对NULL理解有问题,所以这里很容易产生误解。如果想了解更多关于SQL语句中NULL的含义,建议阅读一些关于SQL语句基础的书籍。(关于这个话题,互联网上的一些信息是不够精确的)
COUNT()的另外一个作用是统计结果集的行数。当MySQL确认括号内的表达式值不可能为空时,实际上就是在统计行数。最简单的就是当我们使用COUNT(*)的时候,这种情况下通配符*并不会像我们猜想的那样扩展成所有的列,实际上,它会忽略所有的列而直接统计所有的行数。
我们发现一个最常见的错误就是,在括号内指定了一个列却希望统计结果集的行数。如果希望知道的是结果集的行数,最好使用COUNT(*),这样写意义清晰,性能也会很好。
关于MyISAM的神话
一个容易产生的误解就是:MyISAM的COUNT()函数总是非常快,不过这是有前提条件的,即只有没有任何where条件的COUNT(*)才非常快,因为此时无需实际地去计算表的行数。MySQL可以利用存储引擎的特性直接获得这个值。如果MySQL知道某列col不可能为NULL值,那么MySQL内部会将COUNT(col)表达式优化为COUNT(*)。
当统计带WHERE子句的结果集行数,可以是统计某个列值的数量时,MySQL的COUNT()和其它存储引擎没有任何不同,就不再有神话般的速度了。所以在MyISAM引擎表上执行COUNT()有时候比别的引擎快,有时候比别的引擎慢,这受很多因素影响,要视具体情况而定。
简单的优化
有时候可以使用MyISAM在COUNT()全表非常快的这个特性,来加速一些特定条件COUNT()的查询。在下面的例子中,我们使用标准数据库world来看看如何快速查找到所有ID大于5的城市。可以像下面这样来写这个查询:
mysql>SELECT COUNT(*) FROM world.city WHERE ID > 5;
通过SHOW STATUS的结果可以看到该查询需要扫描4097行数据。如果将条件反转一下,先查找ID小于等于5的城市数,然后用总城市数一减就能得到同样的结果,却可以将扫描的行数减少到5行以内:
mysql>SELECT (SELECT COUNT(*) FROM world.city) - COUNT(*) FROM world.city WHERE ID <= 5;
这样做可以大大减少需要扫描的行数,是因为在查询优化阶段会将其中的子查询直接当成一个常数来处理,我们可以通过EXPLAIN来验证这点。
在邮件组和IRC聊天频道中,通常会看到这样的问题:如果在同一个查询中统计同一个列的不同值的数量,以减少查询的语句量。例如,假设可能需要通过一个查询返回各种不同颜色的商品数量,此时不能使用OR语句,因为这样做就无法区分不同颜色的商品数量。下面的查询可以在一定程度上解决这个问题。
mysql>SELECT SUM(IF(color = 'blue', 1, 0)) AS blue,SUM(IF(color = 'red', 1, 0)) AS red FROM items;
也可以使用COUNT()而不是SUM()实现同样的目的,只需要将满足条件设置为真,不满足条件设置为NULL即可:
mysql>SELECT COUNT(color = 'blue' OR NULL) AS blue,COUNT(color = 'red' OR NULL) AS red FROM items;
本文内容转载自《高性能MySQL第三版-第6.7.1章节》