Skip to content

COUNT性能优化

一、 问题:SELECT COUNT(*) 为何会慢?

  • 现象:在 InnoDB 表中,随着数据量增大,执行 SELECT COUNT(*) FROM table; 会越来越慢。
  • 原因:不同存储引擎实现不同。
    • MyISAM:将表的总行数精确存储在磁盘上,执行 COUNT(*) 时直接返回,速度极快(前提是无 WHERE 条件)。但 MyISAM 不支持事务。
    • InnoDB不存储精确行数。执行 COUNT(*) 时,需要遍历整个表(或某个索引)来逐行计数

二、 InnoDB 为何不存储精确行数?

  • 核心原因MVCC (多版本并发控制)
    • InnoDB 支持事务和不同的隔离级别(默认为可重复读)。
    • 在同一时刻,不同的事务看到的可见行数可能不同。例如,一个事务启动后,即使其他事务插入了新行并提交,该事务也可能看不到这些新行,其 COUNT(*) 结果应保持不变。
    • 由于没有一个“绝对正确”的行数对所有事务都适用,InnoDB 无法简单地存储一个全局计数器。它必须根据当前事务的可见性规则来实际计算行数。

三、 InnoDB 对 COUNT(*) 的优化

  • 选择最小索引树:InnoDB 知道 COUNT(*) 只是要统计行数,不需要读取实际数据。它会选择表上最小的那棵索引树(通常是某个二级索引,因为它只包含索引列和主键值,比包含整行数据的主键索引小得多)进行遍历计数,以减少扫描的数据量。

四、 SHOW TABLE STATUSTABLE_ROWS 能用吗?

  • 不能TABLE_ROWS 是通过采样估算得出的值,与 EXPLAIN 中的 rows 类似,非常不准确(官方文档称误差可达 40%-50%),不能用于精确计数。

五、 应对频繁精确计数的方案

既然直接 COUNT(*) 慢且不可避免,对于需要频繁获取精确总数的场景(如页面展示),只能自行维护计数

  1. 使用缓存系统 (如 Redis)

    • 方法:表插入时 Redis 计数 +1,删除时 -1。
    • 优点:读写速度快。
    • 缺点
      • 数据丢失风险:Redis 异常重启可能丢失最近的计数更新。需要重启后通过全表 COUNT(*) 恢复,但异常期间不准。
      • 逻辑不一致 (核心问题):即使 Redis 正常,由于数据库写操作和 Redis 更新是两个独立操作,无法保证原子性。并发读写时,可能出现:
        • 读到新数据,但计数未更新。
        • 计数已更新,但读不到新数据。
        • 这是因为缺乏分布式事务保证。
  2. 在数据库中创建计数表

    • 方法:创建一个专门的表存储目标表的行数。
    • 优点
      • 无数据丢失风险:利用 InnoDB 的事务持久性。
      • 保证逻辑一致性 (关键):将对业务表的操作(INSERT/DELETE)和对计数表的更新放在同一个数据库事务中完成。利用事务的原子性和隔离性,外部查询要么看到数据和计数都更新后的状态(事务已提交),要么都看到更新前的状态(事务未提交),保证了数据一致性。

六、 不同 COUNT() 用法的性能比较 (InnoDB)

  • 语义COUNT() 统计参数非 NULL的行数。
    • COUNT(*)COUNT(1)COUNT(主键id):都用于统计结果集的总行数。
    • COUNT(字段):统计结果集中该字段值不为 NULL的行数。
  • 性能原则
    • Server 层要什么,InnoDB 给什么。
    • InnoDB 只给必要的值。
    • 优化器目前主要优化了 COUNT(*) 为“取行数”。
  • 性能分析
    • COUNT(*)优化过,不取值,效率高。
    • COUNT(1):InnoDB 不取值,Server 层放入 '1' (非 NULL),效率近似 COUNT(*)
    • COUNT(主键id):InnoDB 需要遍历并取出主键 id 值给 Server 层判断非 NULL,涉及数据拷贝,慢于 COUNT(1)
    • COUNT(字段)
      • 若字段 NOT NULL:InnoDB 取出字段值给 Server 层判断非 NULL,慢于 COUNT(1)
      • 若字段允许 NULL:InnoDB 取出字段值,Server 层实际判断是否为 NULL,最慢
  • 性能排序COUNT(允许NULL字段) < COUNT(NOT NULL字段) < COUNT(主键id) < COUNT(1) ≈ COUNT(*)
  • 推荐尽量使用 COUNT(*)

七、 总结

  • InnoDB 的 COUNT(*) 因 MVCC 需遍历计数,性能随数据量下降。
  • 避免使用 SHOW TABLE STATUS 获取精确行数。
  • 对于频繁精确计数需求,推荐在数据库内部使用计数表,并利用事务保证一致性
  • COUNT() 的各种写法中,COUNT(*) 是被明确优化的,性能最好,应优先使用。