MySQL系列（七）——count()探究

计算一个表的行数SQL语句select count(*) from t ，随着系统中记录数越来越多，这条语句执行得也会越来越慢。此篇旨在说明count(*)语句到底是怎样实现的，以及MySQL为什么会这么实现。如果应用中有这种频繁变更并需要统计表行数的需求，业务设计上可以怎么做。

我的新书《LangChain编程从入门到实践》 已经开售！推荐正在学习AI应用开发的朋友购买阅读！

count(*)的实现方式

1. MyISAM引擎把一个表的总行数存在了磁盘上，因此执行count(*)的时候会直接返回这个数，效率很高；
2. InnoDB引擎执行count(*)的时候，需要把数据一行一行地从引擎里面读出来，然后累积计数。
3. 注意：如果加了where 条件的话，MyISAM表也是不能返回得这么快的。

假设表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，每一行记录都要判断自己是否对这个会话可见，因此对于count(*)请求来说，InnoDB只好把数据一行一行地读出依次判断，可见的行才能够用于计算“基于这个查询”的表的总行数。

优化

1. InnoDB是索引组织表
   • 聚簇索引树：叶子节点是数据
   • 二级索引树：叶子节点是主键值
2. 二级索引树占用的空间比聚簇索引树小很多
3. 在保证逻辑正确的前提下，尽量减少扫描的数据量，是数据库系统设计的通用法则之一
   • 针对无过滤条件的count操作，无论遍历哪一颗索引树，效果都是一样的
   • 优化器会为count(*)选择最优的索引树

show table status

show table status同样通过采样来估算（非常不精确），误差能到40%~50%

mysql> show table status like 't'\G;
*************************** 1. row ***************************
           Name: t
         Engine: InnoDB
        Version: 10
     Row_format: Dynamic
           Rows: 100256
 Avg_row_length: 36
    Data_length: 3686400
Max_data_length: 0
   Index_length: 3178496
      Data_free: 0
 Auto_increment: NULL
    Create_time: 2020-02-11 10:00:27
    Update_time: NULL
     Check_time: NULL
      Collation: utf8_unicode_ci
       Checksum: NULL
 Create_options:
        Comment:
1 row in set (0.04 sec)

维护计数

用缓存系统保存计数

1. 用Redis来保存表的总行数（无过滤条件）
2. 这个表每插入一行，Redis计数+1，每删除一行，Redis计数-1
3. 缺点：

• 丢失更新
  • Redis可能会丢失更新
  • 解决方案：Redis异常重启后，到数据库执行一次count(*)，异常重启并不常见，这时全表扫描的成本是可以接受的
• 逻辑不精确 – 致命
  • 在并发系统里面，无法精确控制不同线程的执行时刻的，即使Redis正常工作，这个计数值还是逻辑上不精确的
  • Redis和MySQL是两个不同的存储系统，_不支持分布式事务_，因此无法拿到精确的一致性视图

在数据库保存计数

1. 把计数值放到数据库单独的一张计数表C中
2. 利用InnoDB的crash-safe的特性，解决了崩溃丢失的问题
3. 利用InnoDB的支持事务的特性，解决了一致性视图的问题
4. 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的性能

分析性能差别的几个原则：

• server层要什么就给什么；

• InnoDB只给必要的值；

• 现在的优化器只优化了count(*)的语义为“取行数”，其他“显而易见”的优化并没有做。

count(字段F)

1. 如果字段F定义为不允许为NULL，一行行地从记录里读出这个字段，判断通过后按行累加，通过表结构判断该字段是不可能为NULL
2. 如果字段F定义为允许NULL，一行行地从记录里读出这个字段，判断通过后按行累加
   • 通过表结构判断该字段是有可能为NULL
   • 判断该字段值是否实际为NULL
3. 如果字段F上没有索引，只能遍历整张表（聚簇索引）
4. _由于InnoDB必须返回字段F，因此优化器能做出的优化决策将减少_，例如不能选择最优的索引来遍历

count(主键ID)

1. InnoDB会遍历整张表，把每一行的id值取出来，返回给Server层
2. Server层拿到id后，判断为不可能为NULL，然后按行累加
3. 优化器可能会选择最优的索引来遍历

count(1)

1. InnoDB引擎会遍历整张表，但不取值
2. Server层对于返回的每一行，放一个数字1进去，判断是不可能为NULL，按行累加
3. count(1)比count(主键ID)快，因为count(主键ID)会涉及到两部分操作
   • 解析数据行
   • 拷贝字段值

count(*)

1. count()不会把所有值都取出来，而是专门做了优化，不取值，因为『\』肯定不为NULL，按行累加
2. 不取值：InnoDB返回一个空行，告诉Server层不是NULL，可以计数

结论

1. count(字段F) < count(主键ID) < count(1) ≈ count(*)
2. 尽量使用count(*)