count(*) 为什么慢？自增主键的空洞与锁

这两道题面试出现率极高，但很多人的回答只到「MyISAM 有计数器，InnoDB 没有」和「自增主键能避免页分裂」。面试官追问下去，基本就卡住了。

先问自己：count(*) 到底在干什么

MySQL 面试里 count(*) 的问题几乎必出。不是因为它难，而是它能区分出"会用 SQL"和"理解 InnoDB"的人。

先看一个基本事实：SELECT COUNT(*) FROM t 在 MyISAM 里是 O(1) 的，在 InnoDB 里却是 O(N) 的。为什么？

MyISAM 的优化：MyISAM 把每张表的行数存在表的元数据里（frm 文件），count(*) 直接读这个数，不需要扫描数据。但 MyISAM 不支持事务，这个计数不带版本号——如果一个事务里先插入后 count，或者两个事务并发操作，计数就乱套了。

InnoDB 为什么不能这么干：InnoDB 需要支持 MVCC，同一个时刻不同事务看到的行数不一样。如果也像 MyISAM 那样存一个全局计数，就没办法在 RR 隔离级别下读到"事务开始瞬间的快照行数"。所以 InnoDB 选择：count(*) 时根据可见性规则逐行判断，走什么索引取决于哪个索引最小（因为 B+ 树索引包含多少个键值对，count 就必须数多少个）。

-- 实践测试：100 万行数据
-- 用二级索引（int 4 字节）vs 主键聚簇索引（含整行数据）
EXPLAIN SELECT COUNT(*) FROM t;
-- 结果：走的是二级索引，而非主键
-- 因为二级索引的 B+ 树叶子节点更小，IO 更少

count(*) / count(1) / count(字段) 到底有什么区别

这是个经典的三连问，理解清楚能省不少面试翻车时间。

写法	行为	是否忽略 NULL
count(*)	统计行数，不关心具体字段	不忽略 NULL（因为根本没看字段）
count(1)	和 count() 等价，8.0 优化器会转为 count()	同上
count(主键)	统计主键列非 NULL 的行数，实际走主键索引	主键不会 NULL，等价
count(非空字段)	统计该字段非 NULL 的行数	不忽略（因为字段非空）
count(可为NULL字段)	统计该字段非 NULL 的行数	忽略 NULL

count(1) 不等于「count 第一列」，括号里的 1 只是一个常量表达式，优化器会把它优化成 count(*)。8.0 里 count(*) 和 count(1) 的执行计划完全一样。

性能差异在 count(字段) 上——如果字段可为 NULL，InnoDB 需要额外判断 null 值，走全表扫描或全索引扫描，比 count(*) 多一层判断。

大表 count 的优化手段

线上 5000 万行的表，SELECT COUNT(*) 跑 30 秒，DBA 说"别这么查"，那怎么办？

方案一：近似值

-- 快速拿估算行数，误差 3-5%
SHOW TABLE STATUS LIKE 'orders';
-- 或者
SELECT TABLE_ROWS FROM information_schema.TABLES WHERE TABLE_NAME = 'orders';

这个值来自 InnoDB 的统计信息采样，不是精确的。业务上如果能接受近似值（比如"共有 5000 万用户"这种展示文案），这是最快的方案，微秒级返回。

方案二：计数表

维护一张单独的计数表，用事务保证一致性：

CREATE TABLE row_count (
    table_name VARCHAR(64) PRIMARY KEY,
    cnt BIGINT NOT NULL
);

-- 插入或删除后更新
BEGIN;
UPDATE row_count SET cnt = cnt + 1 WHERE table_name = 'orders';
COMMIT;

这个方案的问题：每次 DML 操作都要维护计数，写放大明显。而且分布式环境里，计数表本身成了瓶颈。适合单库、写少读多的场景，比如后台统计页面。

方案三：Redis 缓存

更适合的分工是：业务层用 Redis 维护行数，MySQL 只负责写数据。但 Redis 缓存和 MySQL 数据之间有延迟，极端情况（Redis 宕机、缓存丢失）需要从 MySQL 全量重建。

MySQL 8.0 的并行扫描

这是 MySQL 8.0.17 引入的优化：SELECT COUNT(*) 在满足条件时，可以用多个线程并行扫描索引的 B+ 树叶子节点。innodb_parallel_read_threads 参数控制并行度，默认 4。实测 1000 万行表，并行度 4 时耗时从 2.3 秒降到 0.6 秒。

-- 查看当前并行设置
SHOW VARIABLES LIKE 'innodb_parallel_read_threads';
-- 默认 4，可根据 CPU 核数调整

自增主键：空洞从哪来

自增主键选型是面试另一道高频题。很多人只会说"自增主键能减少页分裂"——但面试官更想知道的是自增主键的坑。

空洞的三种来源

1. 事务回滚

BEGIN;
INSERT INTO t(name) VALUES('a');  -- 分配 id=1
ROLLBACK;
-- id=1 不会回填，下一个插入分配 id=2

InnoDB 为什么不一回滚就把 id 重用？因为如果别的会话已经看到了 id=1（比如在 RR 隔离级别下插入了一条带 id=1 的记录然后提交了），回滚重用会导致主键冲突。InnoDB 保证自增 id 不重用的前提就是不回退。

2. 唯一键冲突

INSERT INTO t(id, name) VALUES(10, 'a');
INSERT INTO t(name) VALUES('b');  -- 分配 id=11
INSERT INTO t(name) VALUES('c');  -- 分配 id=12

-- 假设唯一键冲突
INSERT INTO t(name) VALUES('b');  -- 分配 id=13，但唯一键冲突，插入失败
-- 下一次插入分配 id=14，id=13 成为空洞

3. 批量插入预分配

INSERT ... SELECT、LOAD DATA 等批量插入操作，InnoDB 会一次性预分配一批自增值。即便实际插入的数据少，多分配的自增值也不会回吐。

-- 假设当前 auto_increment=100
INSERT INTO t(name) SELECT name FROM big_table;  -- 预分配了 1000 个自增值
-- 实际只插入了 500 行，id 从 100 跳到 599，600-1099 是空洞

自增锁模式演进

这是面试必问的细节。自增锁的粒度在 MySQL 版本间经历了显著变化：

版本	innodb_autoinc_lock_mode	行为
5.1 之前	0（传统模式）	INSERT 都加表级 AUTO-INC 锁，语句结束释放，并发写入差
5.1-7	1（连续模式，默认）	普通 INSERT 用轻量级 mutex（预分配后释放），批量 INSERT 仍用表锁
8.0	2（交错模式，默认）	所有 INSERT 都用轻量级 mutex，不锁表，但 binlog 格式必须为 ROW

8.0 的 key change：自增值不再是内存里的 volatile 值，而是持久化到 redo log。每分配一个自增值就写一次 redo，重启后从 redo 恢复，不会再出现老版本重启后自增值回退的问题。

-- 查看当前自增锁模式
SHOW VARIABLES LIKE 'innodb_autoinc_lock_mode';
-- 8.0 默认 2，5.7 默认 1

总结

• count(*) 慢的根本原因是 InnoDB 需要按可见性逐行判断，而不是 InnoDB 比 MyISAM 差——这是事务的代价。
• 大表 count 不要硬查，用近似值、计数表或 Redis 缓存代替。
• 自增主键的空洞是正常现象，不影响业务就别管。非要连续可以用序列号段单独维护，但代价远大于收益。
• 自增锁从 5.7 到 8.0 的演进方向是减少锁粒度，不走回头路——8.0 默认交错模式的前提是 binlog_format=ROW，如果还在用 STATEMENT 格式，老老实实开锁模式 1。

参考

• MySQL 官方文档：COUNT 优化
• MySQL 官方文档：AUTO_INCREMENT 处理
• MySQL 8.0：并行查询与 InnoDB 并行扫描