count(*)
的实现方式
不同引擎实现方式不同:
-
MyISAM:将总行数直接存在磁盘上,查询的时候返回,因此速度很快
-
Innodb:因为需要支持事务,而事务是由 MVCC 实现的,一行记录需要先判断对查询事务的可见性,所以“应该返回多少行” 是不确定的。因此Innodb的实现方式是把数据一行一行的从引擎中读出来,可见的行才会被累计进来。
举个例子,假设表
t
有 10000 条数据:sessionA sessionB sessionC begin; select count(*) from t; insert into t(插入一行); begin; insert into t(插入一行); select count(*) from t; (返回10000) select count(*) from t; (返回10002) select count(*) from t; (返回10001) 可见同一时刻的并发查询,返回的结果是不一致的。
由于需要遍历全表,因此对于大表,
count
会变得很慢。
为 count
提速
用缓存保存计数
这是第一时间会想到的方式,比如使用 redis
。更新数据时顺便更新 redis
中的计数值,count
的时候直接查询 redis
即可,速度很快。
但是这会有2个问题:
-
缓存异常宕机会丢失更新
redis持久化有RDB和AOF两种方式;RDB按照备份策略,比如60秒1000个k-v被修改,备份过程中宕机,那么这个阶段的所有更新都会丢失;AOF按照备份策略,比如 appendfsync always 策略,同步记录所执行的指令到日志文件,但是它的日志和mysql的WAL(先写日志)不同,它是后写日志,可能指令执行后写日之前宕机,那这个数据就丢失了,虽然丢失数据较少且概率较低,但依然存在这个可能。
可以在重启后到数据库中执行一次
count(*)
获取行数后填到redis
中,毕竟异常宕机重启不会经常发生,偶尔一次全表扫描的成本还是可以接受的。 -
结果可能不准
由于更新数据和更新
redis
统计计数不是一个原子操作,可能会出现统计计数和数据不一致的问题。比如以下场景:
sessionA sessionB redis
计数+1读 redis
计数查询近100条记录 插入一行数据 sessionB 在查询计数时已经加了1,但是查不到新插入的数据(将sessionA的两个操作调换顺序也是类似的)。究其原因,
redis
和数据库可以看做两个不同的数据源,不能保证两个操作的原子性(不可分割)。这类问题属于分布式一致性问题,虽可通过引入其他手段解决,但会使一个简单的计数查询需求变得很复杂,没有必要。
在数据库保存计数
那不存到 redis
中,存到数据库计数表中,会出现上面的问题吗?
- 首先,Innodb 支持崩溃恢复,所以不存在更新丢失的问题。
- 将上图中
redis
的操作换成对数据库计数表的操作,不会出现数据不一致的问题。很明显,Innodb的事务保证了操作的原子性。
所以,对于大表,推荐使用数据库计数表来提速 count
。
进一步优化
根据 两阶段锁
协议,可以通过调整事务内更新语句的顺序:将 update
计数表放到最后,来减少计数表行锁等待的时间,提高并发度。
不同 count
用法的区别
首先,count
是一个聚合函数,它的逻辑是,对于返回的结果集,一行一行的判断,不为 NULL
就+1。
-
count(主键id)
遍历整张表,把每一行的
id
取出来返回给 server 层,server 层判断id
不可能为空,+1。这里可能会觉得奇怪,
id
根本不可能为空,为什么还要多此一举去判断一下。的确是没什么必要,但类似需要优化的地方太多了,MySQL专门对
count(*)
优化过了,直接使用count(*)
就好了。 -
count(1)
遍历整张表,但不取值。Server 层对于返回的每一行,放一个 “1“ 进去,判断不可能为空,+1。
很明显,相较于
count(主键id)
,count(1)
效率更高,因为count(id)
还需要解析数据行、拷贝字段值等操作。 -
count(字段)
遍历整张表,把每一行对应的字段值取出来返回给 server 层,server 层判断是否为空,不为空+1。所以效率
count(字段)
≤count(*)
。 -
count(*)
做了专门的优化,并不会把全部字段取出来,而是直接不取值,认定
count(*)
肯定不为空,直接按行累加。
按效率排序:count(字段) < count(主键id) < count(1) ≈ count(*)。
推荐直接使用 count(*)
。