1. 为什么 count() 会成为 MySQL 的性能瓶颈先说个我实际遇到的场景一张日志表数据量不到 300 万行某天一个统计接口突然从 200ms 飙升到 8 秒。查了半天问题就出在一行SELECT COUNT(*) FROM logs WHERE create_time 2024-01-01上。很多人第一反应是“加索引”但加完索引发现还是慢。这时候就得从 MySQL 的执行机制上找原因了。COUNT()慢的核心原因可以归结为三个层面存储引擎没有维护精确的行数统计、MVCC 多版本并发控制导致无法直接读元数据、大字段回表带来的额外 I/O 开销。1.1 InnoDB 为什么不直接存总数MyISAM 时代引擎层面维护了一个计数器COUNT(*)直接读这个值所以 MyISAM 的 count 查询几乎总是 O(1) 的。但 InnoDB 不这么干原因在于事务隔离。InnoDB 默认的事务隔离级别是 REPEATABLE READ可重复读。在这个级别下每个事务开启时会生成一个一致性快照Read View事务内的所有查询都必须基于这个快照来读取数据。这意味着不同事务在同一时刻看到的行数可能是不同的——事务 A 看到 100 行事务 B 可能看到 101 行如果有一行是 B 自己插的。既然每个事务看到的行数都不一样引擎就不可能存一个“全局精确行数”供所有人直接读取。所以COUNT(*)在 InnoDB 下的执行逻辑是在某个具体事务的快照范围内逐行扫描、判断可见性、计数。这才是性能问题的根源。1.2 索引扫描和回表带来的额外开销我们知道COUNT(*)在 InnoDB 里通常会选择最小的二级索引来扫因为二级索引只存主键值和索引列比聚簇索引存在整行数据要小很多扫描的页数少I/O 成本就低。这是优化器做的一个合理的“偷懒”策略。但问题来了如果表上只有聚簇索引也就是主键索引没有二级索引那COUNT(*)就必须扫描整个聚簇索引。聚簇索引的叶子节点存的是整行数据一条记录假设 1KB300 万行就是 3GB 的扫描量。这还只是逻辑上的量实际物理磁盘 I/O 只会更夸张。更麻烦的是带有 WHERE 条件的COUNT(*)。如果过滤条件用到的列没有索引MySQL 必须做全表扫描。扫描过程中每读一行都要判断记录对当前事务是否可见通过 undo log 回溯版本链可见性判断本身又是开销。1.3 关于 count(1)、count(*) 和 count(列) 的认知误区工作中经常听到一个说法用COUNT(1)比COUNT(*)快。在 MySQL 5.7 及之后的版本里这两个没有实质性能差异。优化器会把COUNT(*)和COUNT(1)都转换成同样的执行计划都选择最优的索引扫描路径。真正的性能差异在COUNT(列)上——如果指定的列是允许 NULL 的MySQL 需要额外判断这一行的该字段是否为空空值不计入这样会多一层判断逻辑。而且如果这个列不在扫描的那个索引里就得回表读主键对应的完整行记录。回表是随机 I/O代价比顺序 I/O 高一个数量级。把这三层原因吃透以后再回头看“为什么 count 这么慢”这个问题就明白它不是单靠某个参数调整就能解决的而要从语义需求、表结构设计、索引策略三个方向一起考虑。2. 什么场景下 count 慢是“正常”的在我们讨论优化方案之前先把场景分清楚。不同场景下 count 慢的含义完全不同优化手段也天差地别。2.1 全表 count vs 条件 count全表SELECT COUNT(*) FROM t慢说明这张表真的很大。在这种场景下慢是符合预期逻辑的——InnoDB 必须扫描全表或最小索引才能给出精确数字。这类查询适合走“计数缓存”路线不是 Java 里的 ConcurrentHashMap 那种缓存而是单独维护一张统计表或者用 Redis 记录。带条件的 count 慢则需要具体分析条件列有索引但选择性不好比如状态字段只有几个枚举值扫描的索引范围依然很大。条件列没有索引直接全表扫描 逐行过滤。条件列有索引但查询条件写法有问题导致索引失效比如对索引列做函数运算、隐式类型转换。2.2 explain 里的 rows 是一个估算值很多人看到EXPLAIN结果里 rows 显示 30 万就以为 MySQL 知道了精确行数。这个值是优化器基于索引基数cardinality估算出来的用的是采样统计默认取 8 个数据页做抽样跟真实的行数相差可能很大。所以当你看到 EXPLAIN 里 rows 是 30 万实际执行跑了 8 秒不要惊讶。这个 rows 代表的是优化器认为需要扫描的行数不是实际扫描的行数。把它当作“量级参考”可以但别当成精确值。2.3 大表 count 慢的真正痛点给一张 5000 万行的表做无条件COUNT(*)如果表上有一个比较小的二级索引比如状态时间扫描索引大约要读 100 万 个叶子节点页耗时在 10~30 秒级别。这种情况下指望靠优化 SQL 本身是不可能救回来的。再叠加一个现实问题COUNT(*) 会拿表级 S 锁吗不会。它只在执行期间保持一致性快照读不会阻塞并发写入。但慢查询本身会占用连接和 I/O 资源在高并发写入的场景下一个 20 秒的慢 count 足以让 InnoDB 的 buffer pool 刷脏压力骤增间接拖慢整个实例。3. 找到“内鬼”的三种经典排查手段所谓“找内鬼”就是把慢查询的真实消耗点定位出来。我常用的方式有三种从拿到一条慢 SQL 到定位根因基本二十分钟内能走完。3.1 用 profiling 定位阶段耗时先打开 profiling把 SQL 执行各阶段的耗时拉出来SET profiling 1; -- 执行你的慢 SQL SELECT COUNT(*) FROM orders WHERE created_at BETWEEN 2024-01-01 AND 2024-03-31; SHOW PROFILE FOR QUERY 1;输出里重点看两个指标Sending data阶段的耗时占比、Statistics阶段耗时。前者代表真正扫描和计算的时间后者代表优化器计算执行计划的时间。如果Statistics占比很高说明优化器在计算索引选择策略时消耗了大量 CPU。这种情况虽然在 count 查询里不常见但遇到过通常是因为统计信息过旧执行ANALYZE TABLE可以解决。如果Sending data占了 90% 以上说明扫描本身是大头接下来用第二个工具看是否走对索引。3.2 用 explain 检查执行计划是否走对索引EXPLAIN SELECT COUNT(*) FROM orders WHERE created_at BETWEEN 2024-01-01 AND 2024-03-31;关键看type和key两列type如果是ALL就是全表扫描。type如果是range或者ref说明走的是索引。key是否为预期使用的那个索引。常见坑在created_at上建了索引但 OR 条件下 MySQL 5.7 可能放弃索引扫描改走全表。遇到这种可以把 OR 改成两个查询 UNION或者用UNION ALL拼结果再 count。3.3 用 performance_schema 做慢查询归档慢查询日志是事后分析performance_schema 可以做在线分析。我比较常用的是把慢 SQL 的 digest 归档到一张表里定期观察SELECT DIGEST_TEXT, COUNT_EXEC, SUM_TIMER_WAIT/1000000000 AS total_s FROM performance_schema.events_statements_summary_by_digest WHERE DIGEST_TEXT LIKE %COUNT(*)% ORDER BY SUM_TIMER_WAIT DESC LIMIT 10;通过这个视图能看出哪些 count 语句是高频慢查询对后续做缓存方案或者走了索引优化提供了客观依据。注意performance_schema 默认是开启的但 events_statements_summary_by_digest 表数据会被自动清空默认每条 digest 在 1小时内没有新执行就会释放。如果要长期监控需要把 history 相关配置调大或者定期快照备份这张表。4. 针对不同场景的优化方案接下来给方案我会给一套可以直接抄的作业。但先说原则方案选型的核心逻辑是先想清楚你要的到底是不是精确值。4.1 场景一首页展示层允许短时不一致这类需求最常见。比如首页要显示“订单总数”“用户总数”实际上业务方不会在意 1 分钟内新增了几条。这种场景我强烈建议直接上 Redis 计数器每次新增记录时对 Redis 中的 key 执行INCR。删除记录时执行DECR。定期用真实 count 做一次全量校准防止因删除逻辑漏执行导致计数漂移。有人担心 Redis 和 MySQL 的一致性问题。我的做法是写一个定时任务比如每天凌晨 2 点对当天的业务数据跑一次精确COUNT(*)把结果写回 Redis。这种兜底策略考虑到 Redis 可能偶发故障丢数据而真正确保任何场景都不丢的方式得用双写这种成本比较高的手段看业务量级觉得值不值得。4.2 场景二需要精确值但数据量已经很大如果业务明确要求精确值比如财务对账、库存校验那不能糊弄。这种情况下要考虑把计数查询从主库拆分出去。我跳过主从方案直接说第二种方案因为你就算配了从库SQL 本身还是要全表扫。更合理的思路是维护一张计数汇总表CREATE TABLE table_count ( table_name VARCHAR(64) PRIMARY KEY, record_count BIGINT NOT NULL DEFAULT 0, updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP ) ENGINEInnoDB;在业务代码的写操作事务中同步更新这张表START TRANSACTION; INSERT INTO orders (id, user_id, amount, created_at) VALUES (1001, 88, 199.00, NOW()); INSERT INTO table_count (table_name, record_count, updated_at) VALUES (orders, 1, NOW()) ON DUPLICATE KEY UPDATE record_count record_count 1; COMMIT;删除操作同理做-1。用事务保证主表的插入和计数表的更新要么同时成功要么同时回滚这是不依赖外部组件、又能拿到精确计数的方式。4.3 场景三条件 count 且必须精确这个最难。带 WHERE 条件的精确计数本质上是时序范围查询。唯一能落地的方式是控制扫描范围。比如统计近 30 天的订单SELECT COUNT(*) FROM orders WHERE created_at DATE_SUB(NOW(), INTERVAL 30 DAY);先确保created_at上有索引然后查看是否走range扫描。如果这张表同时还在频繁写入要注意慢查询导致的长事务的锁竞争。只要走的是范围索引扫描扫描的就是叶子节点里近 30 天的数据段而不是整张表性能会好很多。如果业务上还允许可以再叠加一个时间分区表MySQL 可以直接做分区裁剪partition pruning只扫对应分区。4.4 另一个容易被忽略的点COUNT(DISTINCT)顺手提一个相关的场景。COUNT(DISTINCT col)慢的本质原因和COUNT(*)不一样它是需要去重。如果列上没索引MySQL 需要一个临时表配合排序来完成去重计数在 5.7 里这个临时表默认是磁盘临时表。总结下来就是临时表 排序 磁盘 I/O想快就建索引没有其他太好的办法。5. 一次完整的问题排查复盘从“8秒”到“40毫秒”为了让上面的内容不那么抽象我把文章开头那个案例完整复盘一遍。5.1 现场情况表结构operation_log300 万行InnoDB。慢 SQLSELECT COUNT(*) FROM operation_log WHERE create_time 2024-03-01 00:00:00;耗时8.2 秒。已知条件create_time上已建了普通索引idx_create_time。5.2 逐步排查第一步看执行计划EXPLAIN SELECT COUNT(*) FROM operation_log WHERE create_time 2024-03-01 00:00:00;结果type rangekey idx_create_timerows 1200000。索引走了但预计扫描 120 万行这是从 3 月 1 日到当前的日志量。也就是索引扫描本身的量级就大不存在问题。第二步看 profileSending data耗时 7.9 秒占比 96%。说明时间几乎全部花在扫描索引叶子节点上。此时任何 SQL 写法层面的微调都不会产生质变。第三步确认需求。业务方真的需要 3 月 1 日以来所有行的精确 count 吗运营层面给的答复是“我们想知道这周处理了多少条操作记录。”这个其实是展示型需求不需要精确到每条。5.3 改造方案第一步优化索引结构。把(create_time, id)改成联合索引。虽然create_time单列索引也能用但联合索引可以把二级索引叶子节点中只包含 id 和 create_time扫描页更小I/O 次数更少。ALTER TABLE operation_log ADD INDEX idx_create_time_id (create_time, id);第二步加 Redis 计数器。在写日志的代码路径里加INCR operation_log_count。由于这个日志表的写入本身就在一个事务里计数器的更新放在事务提交后执行保证不会多计数。第三步兜底校准。每天凌晨跑一次SELECT COUNT(*) FROM operation_log; -- 全表 count 也就每秒百万行过滤后很快把结果覆盖写回 Redis。做完这三步同样查询从 8.2 秒降到 40ms 以内走 Redis。如果完全不用 Redis纯靠联合索引把全表扫描变成索引扫描实测大约 620ms也足够撑过 80% 场景。这个复盘的结论很直白在你确认业务语义之前任何优化方案都是盲目的。5.4 排查过程中的三个易错点不要只看有没有索引要看rows的量级。索引走了扫描量还是大就是没救。不要轻信“配置调优”。innodb_buffer_pool_size调大也许能把热数据页缓存在内存里但第一次查询触底 I/O 的那一刻该慢还是慢。不要忽略业务语义。所有 count 慢查询的解决最终答案几乎都是要么缓存要么缩小范围要么接受延迟。精确 大表 实时这个三角不可能同时满足。6. 实用工具和参考语句最后整理几条排查中高频用到的语句方便直接复制使用。查看慢查询是否开启SHOW VARIABLES LIKE slow_query_log%;分析单个 SQL 的执行耗时SET profiling 1; SELECT COUNT(*) FROM t WHERE xxx; SHOW PROFILES; SHOW PROFILE FOR QUERY 1;查看索引基数判断索引选择性的关键SHOW INDEX FROM orders;统计信息更新当索引基数不准时使用ANALYZE TABLE orders;查看当前连接和慢查询数量SHOW STATUS LIKE Slow_queries; SHOW PROCESSLIST;再看一眼当前事务隔离级别SELECT transaction_isolation; -- 或者 5.7 用 SELECT tx_isolation;7. 关于什么时候别用 count这部分算是我自己的经验输出不是教科书。在业务开发中count 往往被当成一种“简单逻辑”去使用但恰恰是它最能暴露系统的设计缺陷。举几个我实际遇过的案例用户列表页显示“全部人数”。数据量到千万级后每次进入页面都触发一次千万行扫描。最终改法是把“人数”列改为“近 30 天活跃人数”并且由离线统计任务生成报表每天刷新一次。用户根本没感知。电商后台的订单统计。老板要求“实时看到所有订单数量”。数据量也就 50 万行其实没到必须上缓存的量级直接在订单创建时间索引上做 range count性能远小于 100ms。这个案例是典型的“你以为需要缓存其实不需要”。库存表系统。每次入库出库都 count 一下当前库存。这个完全可以用“余额”字段维护每次操作加加减减count 是多余的。所以在动手优化 count 之前先问三个问题这个数字的“实时性”要求到底是秒级、分钟级还是小时级这个数字允许短暂回退吗是否可以通过“增量累计”的方式替代“全量扫描”把这三个问题回答完优化方案自己就浮现出来了。8. 再分享一个兜底的技巧如果你在做完所有判断之后确实需要全表精确 count并且表已经大到单次执行超过 30 秒可以考虑把大表按时间做分区。分区后 count 可以做并行处理——分成多段范围分别 count然后汇总。-- 按月份分区 ALTER TABLE orders_partitioned PARTITION BY RANGE (YEAR(created_at) * 100 MONTH(created_at)) ( PARTITION p202401 VALUES LESS THAN (202402), PARTITION p202402 VALUES LESS THAN (202403), PARTITION p202403 VALUES LESS THAN (202404) );然后并行执行SELECT SUM(cnt) FROM ( SELECT COUNT(*) AS cnt FROM orders_partitioned PARTITION (p202401) UNION ALL SELECT COUNT(*) FROM orders_partitioned PARTITION (p202402) UNION ALL SELECT COUNT(*) FROM orders_partitioned PARTITION (p202403) ) t;这个方案适合有人力维护分区的团队多分区并行扫描的收益在千万级以上才明显。数据量只有十万、百万的分区分了等于白分。count 慢这个问题说到底是“精确性和实时性需求”与“存储引擎能力边界”之间的博弈。每次看到开发同学在 count 慢查询上反复消耗精力我都会建议先把需求方的语义问清楚。技术手段能解决的是“怎么查得更快”但很多情况下你会发现真正的优化是“根本不用查”。这个思路比任何 SQL 优化技巧都值钱。