在不同的MySQL引擎中, count(*)有不同的实现方式。
- MyISAM引擎把一个表的总行数存在了磁盘上, 因此执行count()的时候会直接返回这个数,*效率很高;如果加了where 条件的话, MyISAM表也是不能返回得这么快的。
- 而InnoDB引擎就麻烦了, 它执行count(*)的时候, 需要把数据一行一行地从引擎里面读出来, 然后累积计数。
为什么InnoDB不跟MyISAM一样, 也把数字存起来呢?
这是因为即使是在同一个时刻的多个查询, 由于多版本并发控制(MVCC) 的原因, InnoDB表“应该返回多少行”也是不确定的。 这里, 我用一个算count(*)的例子来为你解释一下。
假设表t中现在有10000条记录, 我们设计了三个用户并行的会话
- 会话A先启动事务并查询一次表的总行数;
- 会话B启动事务, 插入一行后记录后, 查询表的总行数;
- 会话C先启动一个单独的语句, 插入一行记录后, 查询表的总行数。
我们假设从上到下是按照时间顺序执行的, 同一行语句是在同一时刻执行的。
你会看到, 在最后一个时刻, 三个会话A、 B、 C会同时查询表t的总行数, 但拿到的结果却不同。
这和InnoDB的事务设计有关系, 可重复读是它默认的隔离级别, 在代码上就是通过多版本并发控制, 也就是MVCC来实现的。 每一行记录都要判断自己是否对这个会话可见,count(*)做了优化,引擎会选择最小的普通索引树,来计数。而不是直接统计聚集索引树。
为什么不直接使用show table status 命令输出行数?
show table status 命令输出TABLE_ROWS 显示这个表当前有多少行,但它也是采样估算来的。官方文档说误差可能达到 40% 到 50%。
用缓存系统保存计数
对于更新很频繁的库来说, 你可能会第一时间想到, 用缓存系统来支持。但会暴露一下问题
- 缓存会丢失-工作过程中异常重启
- 缓存不准确,因为缓存计数和插入数据不是原子操作,有可能在中间过程,其他事务读取了数据,导致计数与数据逻辑不一致。
用数据库保存计数
使用一张表保存计数,由于事务可以解决使用缓存问题。
不同的 count 用法
下面的讨论还是基于 InnoDB 引擎的
- count(主键 id) ,InnoDB 引擎会遍历整张表,把每一行的 id 值都取出来,返回给 server 层。server 层拿到 id 后,判断是不可能为空的,就按行累加。
- count(1),InnoDB 引擎遍历整张表,但不取值。server 层对于返回的每一行,放一个数字“1”进去,判断是不可能为空的,按行累加。
- count(字段)
- 如果这个“字段”是定义为 not null 的话,一行行地从记录里面读出这个字段,判断不能为 null,按行累加;
- 如果这个“字段”定义允许为 null,那么执行的时候,判断到有可能是 null,还要把值取出来再判断一下,不是 null 才累加
- count(),并不会把全部字段取出来,而是专门做了优化,不取值。count() 肯定不是 null,按行累加。
按照效率排序的话,count(字段)<count(主键 id)<count(1)≈count(),所以建议尽量使用 count()。