MySQL -- 无过滤条件的count
count(*)
实现
- MyISAM:将表的总行数存放在磁盘上,针对无过滤条件的查询可以直接返回
- 如果有过滤条件的count(*),MyISAM也不能很快返回
- InnoDB:从存储引擎一行行地读出数据,然后累加计数
- 由于MVCC,在同一时刻,InnoDB应该返回多少行是不确定
样例
假设表t有10000条记录
session A | session B | session C |
---|---|---|
BEGIN; | ||
SELECT COUNT(*) FROM t;(返回10000) | ||
INSERT INTO t;(插入一行) | ||
BEGIN; | ||
INSERT INTO t(插入一行); | ||
SELECT COUNT(*) FROM t;(返回10000) | SELECT COUNT(*) FROM t;(返回10002) | SELECT COUNT(*) FROM T;(返回10001) |
- 最后时刻三个会话同时查询t的总行数,拿到的结果却是不同的
- InnoDB默认事务隔离级别是RR,通过MVCC实现
- 每个事务都需要判断每一行记录是否对自己可见
优化
- InnoDB是索引组织表
- 聚簇索引树:叶子节点是数据
- 二级索引树:叶子节点是主键值
- 二级索引树占用的空间比聚簇索引树小很多
- 优化器会在保证逻辑正确的前提下,遍历最小的索引树,尽量减少扫描的数据量
- 针对无过滤条件的count操作,无论遍历哪一颗索引树,效果都是一样的
- 优化器会为count(*)选择最优的索引树
show table status
1 | mysql> SHOW TABLE STATUS\G; |
SHOW TABLE STATUS
同样通过采样来估算(非常不精确),误差能到40%~50%
维护计数
缓存
方案
- 用Redis来保存表的总行数(无过滤条件)
- 这个表每插入一行,Redis计数+1,每删除一行,Redis计数-1
缺点
丢失更新
- Redis可能会丢失更新
- 解决方案:Redis异常重启后,到数据库执行一次count(*)
- 异常重启并不常见,这时全表扫描的成本是可以接受的
逻辑不精确 – 致命
- 场景:显示操作记录的总数和最近操作的100条记录
- Redis和MySQL是两个不同的存储系统,_不支持分布式事务_,因此无法拿到精确的一致性视图
时序A
session B在T3时刻,查到的100行结果里面有最新插入的记录,但Redis还没有+1,_逻辑不一致_
时刻 | session A | session B |
---|---|---|
T1 | ||
T2 | 插入一行数据R; | |
T3 | 读取Redis计数; 查询最近100条记录; |
|
T4 | Redis计数+1; |
时序B
session B在T3时刻,查到的100行结果里面没有最新插入的记录,但Redis已经+1,_逻辑不一致_
时刻 | session A | session B |
---|---|---|
T1 | ||
T2 | Redis计数+1; | |
T3 | 读取Redis计数; 查询最近100条记录; |
|
T4 | 插入一行数据R; |
数据库
- 把计数值放到数据库单独的一张计数表C中
- 利用InnoDB的crash-safe的特性,解决了崩溃丢失的问题
- 利用InnoDB的支持事务的特性,解决了一致性视图的问题
- session B在T3时刻,session A的事务还未提交,表C的计数值+1对自己不可见,_逻辑一致_
时刻 | session A | session B |
---|---|---|
T1 | ||
T2 | BEGIN; 表C中的计数值+1; |
|
T3 | BEGIN; 读表C计数值; 查询最新100条记录; COMMIT; |
|
T4 | 插入一行数据R; COMMIT; |
count的性能
语义
- count()是一个聚合函数,对于返回的结果集,一行一行地进行判断
- 如果count函数的参数值不是NULL,累计值+1,否则不加,最后返回累计值
- count(字段F)
- 字段F有可能为NULL
- 表示返回满足条件的结果集里字段F不为NULL的总数
- count(主键ID)、count(1)、count(*)
- 不可能为NULL
- 表示返回满足条件的结果集的总数
- Server层要什么字段,InnoDB引擎就返回什么字段
- count(*)例外,_不返回整行_,只返回空行
性能对比
count(字段F)
- 如果字段F定义为不允许为NULL,一行行地从记录里读出这个字段,判断通过后按行累加
- 通过表结构判断该字段是不可能为NULL
- 如果字段F定义为允许NULL,一行行地从记录里读出这个字段,判断通过后按行累加
- 通过表结构判断该字段是有可能为NULL
- 判断该字段值是否实际为NULL
- 如果字段F上没有二级索引,只能遍历整张表(聚簇索引)
- 由于InnoDB必须返回字段F,因此优化器能做出的优化决策将减少
- 例如不能选择最优的索引来遍历
count(主键ID)
- InnoDB会遍历整张表(聚簇索引),把每一行的id值取出来,返回给Server层
- Server层拿到id后,判断为不可能为NULL,然后按行累加
- 优化器可能会选择最优的索引来遍历
count(1)
- InnoDB引擎会遍历整张表(聚簇索引),但不取值
- Server层对于返回的每一行,放一个数字1进去,判断是不可能为NULL,按行累加
- count(1)比count(主键ID)快,因为count(主键ID)会涉及到两部分操作
- 解析数据行
- 拷贝字段值
count(*)
- count(*)不会把所有值都取出来,而是专门做了优化,不取值,因为『*』肯定不为NULL,按行累加
- 不取值:InnoDB返回一个空行,告诉Server层不是NULL,可以计数
效率排序
- count(字段F) < count(主键ID) < count(1) ≈ count(*)
- 尽量使用count(*)
样例
1 | mysql> SHOW CREATE TABLE prop_action_batch_reward\G; |
count(字段F)
无索引
user_ids上无索引,而InnoDB又必须返回user_ids字段,只能遍历聚簇索引
1 | mysql> EXPLAIN SELECT COUNT(user_ids) FROM prop_action_batch_reward; |
有索引
- serial_id上有索引,可以遍历
uniq_serial_id_source_index
- 但由于InnoDB必须返回serial_id字段,因此不会遍历逻辑结果等价的更优选择
idx_create_time
- 如果选择
idx_create_time
,并且返回serial_id字段,这意味着必须回表
- 如果选择
1 | mysql> EXPLAIN SELECT COUNT(serial_id) FROM prop_action_batch_reward; |
count(主键ID)
优化器选择了最优的索引idx_create_time
来遍历,而非聚簇索引
1 | mysql> EXPLAIN SELECT COUNT(id) FROM prop_action_batch_reward; |
count(1)
1 | mysql> EXPLAIN SELECT COUNT(1) FROM prop_action_batch_reward; |
count(*)
1 | mysql> EXPLAIN SELECT COUNT(*) FROM prop_action_batch_reward; |
参考资料
《MySQL实战45讲》
All articles in this blog are licensed under CC BY-NC-SA 4.0 unless stating additionally.