在实际项目开发中一定会有统计记录数的需求,比如统计用户数,像一些电商系统后台首页会统计订单量、上架商品数量等等,涉及到统计就离不开聚合函数 count() ,开发中常用写法如下:
select count(1) from emp;select count(*) from emp;
一开始受sql语句的影响,我以为(看起来)count(1) 的执行效率要高于count(*),count(*) 会存在全表扫描,而 count(1) 可以针对一个字段进行查询,但是真的是这样吗?其实并非如此,count(1) 和 count(*) 都会对全表进行扫描,统计所有记录的条数,包括那些为null的记录,因此,它们的效率可以说是相差无几。
那究竟是为什么呢,让我们来一探究竟。
有WHERE条件的count(),执行效率有部分原因取决于WHERE条件,比如判断条件字段是否有加索引等,本篇文章仅对没有WHERE语句的情况进行说明。
正文
在MySQL中,不同的存储引擎对count()函数实现方式不同,这里以最常见的两种存储引擎MyISAM和InnoDB进行说明。
MySQL5.6支持的存储引擎包括InnoDB、MyISAM、MEMORY、CSV、BLACKHOLE、FEDERATED、MRG_MYISAM、ARCHIVE、PERFORMANCE_SCHEMA。其中NDB和InnoDB提供事务安全表,其他存储引擎都是非事务安全表。
MyISAM 引擎会把一个表的总行数记录下来,所以在执行 count(*) 的时候会直接返回数量,执行效率很高。在 MySQL 5.5 以后默认引擎切换为 InnoDB,InnoDB 因为增加了版本控制(MVCC)的原因,同时有多个事务访问数据并且有更新操作的时候,每个事务需要维护自己的可见性,那么每个事务查询到的行数也是不同的,所以不能缓存具体的行数,他每次都需要 count 一下所有的行数。细心的同学会发现,InnoDB也有这么一个存储了表行数的变量,但是很遗憾这个值是一个估计值,没有什么实际意义。
那么 count(1) 和 count(*)有什么区别?
InnoDB handles SELECT COUNT(*) and SELECT COUNT(1) operations in the same way. There is no performance difference.
这是官网的解释:“ InnoDB引擎中count(*)和count(1)操作方式是一样的,两者没有什么不同。”,所以两种实现其实一样,那么具体为什么一样呢?
探究这个问题首先我们需要理解 count 的含义,如下是官网给出的定义:
Returns a count of the number of non-NULL values of expr in the rows retrieved by a SELECT statement. The result is a BIGINT value.
大致的解释是返回 SELECT 语句检索的行中 expr 的非 NULL 值的计数,到这里我们就明白了,首先它是一个聚合函数,然后对 SELECT 的结果集进行计数,但是需要参数不为 NULL。那么我们继续阅读官网的内容:
COUNT(*) is somewhat different in that it returns a count of the number of rows retrieved, whether or not they contain NULL values.
大致的内容是说,count(*) 不同,他不关心这个返回值是否为空都会计算他的count,因为 count(1) 中的 1 是恒真表达式(不管是count(1)还是count(2)还是其他非NULL字符参数,执行结果差别不大),那么 count(*) 还是 count(1) 都是对所有的结果集进行 count,所以他们本质上没有什么区别。
当然这个地方 InnoDB 本身也做了一些优化,它会使用最小的二级索引来进行 count 的查询优化。如果没有二级索引才会选择聚簇索引,这样的设计单从 IO 的角度就节省了很多开销。
聚簇索引
如果表设置了主键,则主键就是聚簇索引。
如果表没有主键,则会默认第一个NOT NULL,且唯一(UNIQUE)的列作为聚簇索引。
以上都没有,则会默认创建一个隐藏的row_id作为聚簇索引。
InnoDB的聚簇索引的叶子节点存储的是行记录(其实是页结构,一个页包含多行数据),InnoDB必须要有至少一个聚簇索引,由此可见,使用聚簇索引查询会很快,因为可以直接定位到行记录。
二级索引
普通索引也叫二级索引,除聚簇索引以外的索引即非聚簇索引。
InnoDB的普通索引叶子节点存储的是主键(聚簇索引的值),而MyISAM的普通索引存储的是记录指针。
那既然COUNT(*)和COUNT(1)一样,建议用哪个呢?
建议使用COUNT(*),因为这个是SQL92定义的标准统计行数的语法,而且本文只是基于MySQL做了分析,关于Oracle中的这个问题,也是众说纷纭的呢。
到这里我们明白了 count(*) 和 count(1) 本质上面其实是一样的,那么 count(column) 又是怎么回事呢?
count(column) 也是会遍历整张表,这里分两种情况,若"column"定义为not null,会取出该列的值,然后按行累加;若"column"定义允许为null,它会拿到 column 的值以后判断是否为空(这里的"空",不是空字符串,不是0,不是null,不是Null,也不是NULL,是数据库列从未赋过值的初始状态),不为空则进行累加,即 count(column)是统计该字段列非null的行数。相比COUNT(*),COUNT(字段)多了一个步骤就是判断所查询的字段是否为空,所以他的性能要比COUNT(*)慢。
如果你要统计行数就用count(*)或者count(1),推荐前者。
如果要统计某个字段不为NULL值的个数就用count(字段)。
1.当mysql确认括号内的表达式值不可能为空时,实际上就是在统计行数。
2.如果mysql知道某列不可能为NULL值,那么mysql内部会将count(column)表达式优化为count(*)。
这里有一组测试数据,分别使用count(1)、count(*)、count(column)执行。
SELECT COUNT(1) FROM `user`; ------12行记录SELECT COUNT(*) FROM `user`; ------12行记录SELECT COUNT(`name`) FROM `user`; ------12行记录SELECT COUNT(`age`) FROM `user`; ------11行记录SELECT COUNT(`email`) FROM `user`; ------10行记录
再来说说count(主键),上文中有说如果mysql知道某列不可能为null,那么mysql内部会将count(column)表达式优化成count(*),我们知道主键是不可能为空的,也就是说count(主键)会被优化为count(*)。
补充
MyISAM 与 InnoDB 在 COUNT( * ) 操作的执行过程在哪里开始分道扬镳?
共性:共性存在于 SQL 层,即 SQL 解析之后的数据结构是一致的,count 变量都是存在于作为结果列的 Item_sum_count 类型对象中;返回给客户端的过程也类似,对该 count 变量进行赋值并经由 MySQL 通信协议返回给客户端。
区别:InnoDB 的 count 值计算是在 SQL执行阶段进行的;而 MyISAM 表本身在内存中有一份包含了表 row_count 值的 meta 信息,在 SQL 优化阶段通过存储引擎的标记给优化器一个 hint,表明该表所用的存储引擎保存了精确行数,可以直接获取到,无需再进入执行器。
总结
在开发中确实需要用到count()聚合,那么优先考虑count(*),因为mysql本身对于count(*)做了特别的优化处理;如果你的表只有一个字段的话那count(*)就是最快的。
count(*) 跟 count(1) 的结果一样,都包括对NULL的统计,而count(column) 是不包括NULL的统计。
在有主键或联合主键的情况下,count(*)>count(1);在没有主键的情况下,count(1)>count(*);如果表只有一个字段,count(*)是最快的。
使用count()聚合函数后,最好不要跟where age = 1;这样的条件,会导致不走索引,降低查询效率。除非该字段已经建立了索引。
使用count()聚合函数后,若有where条件,且where条件的字段未建立索引,则查询不会走索引,直接扫描了全表。
count(主键)、count(非空列)会首先选择主键上的索引快速全扫描,若主键不存在则会选择非空列上的索引,若非空列上没有索引则肯定走全表扫描。
如何提升count效率:既然 count(*) 在查询上依赖于所有的数据集,是不是我们在设计上也需要尽量的规避全量 count 呢?通常情况我们针对可预见的 count 查询会做适当的缓存,可以是 Redis,也可以是独立的 MySQL count 表,当然无论是哪种方式我们都需要考虑一致性的问题。
参考
Owen Yu https://zhuanlan.zhihu.com/p/28397595
小明 https://zhuanlan.zhihu.com/p/110729439
小知 https://zhuanlan.zhihu.com/p/70089384
张德检 https://zhuanlan.zhihu.com/p/107125866