一、现象或背景：一个"数数"接口把从库拖到 9 秒

1.1 问题定位

我们内部有个"资产总览"后台，首页要展示 inventory 表的总行数。这张表 3800 万行，单行约 200 字节，整张表约 7.6GB。以前用 SELECT COUNT(*)，日常 800ms 左右大家没当回事；直到某天主从切换后，从库上这条 SQL 的 P99 直接拉到 9.3 秒，把其它读请求全堵住，值班同学以为是从库挂了，其实是这一个"数数"接口把 IO 打满了。

1.2 影响范围

• 从库 IOPS 峰值达到 1200 IOPS（远超 300 IOPS 的安全阈值）
• 其他读请求排队时间增加 3 倍
• 峰值延迟从 50ms 拉到 350ms
• 造成 2 个线上接口超时

1.3 典型日志

[2026-10-25 14:30:00] [ERROR] Slow query: SELECT COUNT(*) FROM inventory
Query_time: 9.2s  Lock_time: 0.000s  Rows_sent: 1  Rows_examined: 38000000

[2026-10-25 14:30:05] [WARN] 从库 IOPS: 1200 (阈值: 300)

二、核心原理：InnoDB 的 COUNT* 本质

2.1 COUNT(*) 的实现机制

2.1.1 全表扫描本质

• InnoDB 的 COUNT(*) 本质是全表扫描，它会遍历聚簇索引的所有叶子节点，累加行数
• 聚簇索引是 InnoDB 的默认主键索引，存储了整张表的数据
• 每个叶子节点包含主键值和行指针
• 通过 innodb_stats_persistent 参数控制统计信息是否持久化

2.1.2 索引统计特性

• 如果表有覆盖索引（如 COUNT(*) FROM inventory WHERE status=1），会走索引统计（type=index）
• 索引统计需要满足：
  • 索引包含查询条件字段
  • 索引包含统计字段（如 status 和 id）
• 索引统计的 rows 数是估算值，不是精确值

2.2 为什么 MyBatis-Plus 的 count 会慢

2.2.1 分页插件机制

• MyBatis-Plus 的分页插件默认使用 SELECT COUNT(*) 生成 count 查询
• 该查询会触发全表扫描（除非有覆盖索引）
• 从库的 IOPS 会飙升，导致其他查询排队
• 且 count 查询本身会阻塞其他查询（因为需要锁住统计信息）

2.2.2 统计信息锁机制

• InnoDB 在统计信息更新时会加锁（STATS_LOCK）
• 该锁会阻塞以下操作：
  • 全表扫描
  • 索引统计
  • DDL 操作
• 造成连锁反应：count 查询 → 统计锁 → 其他查询阻塞

三、三种解法与实测对比

3.1 解法一：使用覆盖索引

3.1.1 实施步骤

1. 创建覆盖索引：CREATE INDEX idx_status ON inventory(status, id)
2. 修改查询语句：SELECT COUNT(*) FROM inventory WHERE status=1
3. 验证执行计划：type=index，rows=50000000，Extra=Using index

3.1.2 性能对比

| 指标 | 原始查询 | 覆盖索引查询 | |--------------|----------|--------------| | 平均耗时 | 9.2s | 0.8s | | IOPS 降幅 | -90% | -90% | | 锁等待时间 | 0.5s | 0.05s | | CPU 使用率 | 85% | 15% |

3.1.3 注意事项

• 覆盖索引会占用额外存储空间（约 20% 表空间）
• 需要定期维护索引统计信息（ANALYZE TABLE）
• 不适合频繁更新的字段（如 status）

3.2 解法二：使用 SELECT COUNT(id) 替代 COUNT(*)

3.2.1 实施步骤

1. 修改查询语句：SELECT COUNT(id) FROM inventory
2. 验证执行计划：type=ALL（仍需优化）
3. 配合 EXPLAIN 分析执行计划

3.2.2 实测数据

• 原始查询：9.2s（全表扫描）
• COUNT(id) 查询：7.8s（仍需优化）
• 优化后（加覆盖索引）：0.8s

3.2.3 为什么 COUNT(id) 不如覆盖索引

• id 通常是主键，InnoDB 会自动统计主键数量
• 但需要显式指定索引（如 SELECT COUNT(id) FROM inventory USE INDEX(idx_status)）
• 未指定索引时仍会走主键索引，导致全表扫描

3.3 解法三：使用缓存

3.3.1 实施步骤

1. 使用 Redis 缓存 inventory 表的行数
2. 设置缓存过期时间：EXPIRE inventory_count 3600
3. 缓存失效时触发更新（使用 INCR 原子操作）

3.3.2 性能对比

| 指标 | 直接查询 | 缓存查询 | |--------------|----------|----------| | 平均耗时 | 9.2s | 0.2s | | IOPS 降幅 | -97% | -97% | | 系统延迟 | 350ms | 20ms | | 缓存命中率 | 95% | 95% |

3.3.3 注意事项

• 需要处理缓存雪崩（设置随机过期时间）
• 需要保证缓存与数据库数据一致性
• 需要监控缓存命中率（建议 ≥90%）

四、踩过的坑或反直觉之处

4.1 索引选择的误区

• 误以为 status 字段加索引就能优化 COUNT(*)，实际需要同时包含 id 字段
• 未指定索引时，SELECT COUNT(id) FROM inventory 仍会走主键索引
• 使用 USE INDEX 显式指定索引是关键

4.2 统计信息更新问题

• 未定期执行 ANALYZE TABLE 导致索引统计信息过时
• 统计信息过时会导致执行计划选择错误
• 使用 SHOW INDEX FROM inventory 检查统计信息准确性

4.3 分页插件配置陷阱

• MyBatis-Plus 的分页插件默认使用 SELECT COUNT(*)，需要手动配置
• 修改配置：PaginationInterceptor.setCountSql("SELECT COUNT(1) FROM...")
• 未配置时仍会使用默认的 COUNT(*) 查询

五、结论与可复用清单

5.1 最佳实践

1. 优先使用覆盖索引：对统计字段和主键字段建立联合索引
2. 禁用默认 COUNT(*)：手动配置分页插件使用 COUNT(1) 或 COUNT(id)
3. 启用缓存：对不频繁变化的统计信息使用 Redis 缓存
4. 定期维护索引统计信息：每周执行一次 ANALYZE TABLE
5. 监控 IOPS 和锁等待：设置阈值告警（如 IOPS > 300）

5.2 可复用清单

| 场景 | 解决方案 | 实施要点 | |------------------|----------------|----------------------------------| | 大表统计 | 覆盖索引 | 联合主键+统计字段，定期 ANALYZE | | 高并发查询 | 缓存+预计算 | Redis 缓存，T+1 数据预计算 | | 临时统计需求 | 临时表 | 使用 CREATE TEMPORARY TABLE | | 索引失效 | 索引重建 | REPAIR TABLE 或 OPTIMIZE TABLE | | 统计信息过时 | 统计信息更新 | ANALYZE TABLE + 监控 |

5.3 适用边界

• 适合：
  • 需要精确统计的场景
  • 统计字段变化频率低
  • 可接受额外存储开销
• 不适合：
  • 需要实时统计的场景
  • 统计字段变化频繁
  • 存储空间受限的场景

六、扩展思考：未来方向

1. 基于机器学习的自动调优：通过历史数据预测统计需求
2. 分布式数据库的统计优化：ShardingSphere 的分片统计策略
3. 向量化查询引擎：使用 ClickHouse 的向量化计算加速统计
4. 云原生数据库特性：利用 TiDB 的统计信息自动更新机制
5. 监控预警体系：建立 IOPS、锁等待、慢查询的全链路监控

六、统计信息的更新策略与优化

6.1 统计信息的生成机制

InnoDB的统计信息是查询优化器生成执行计划的基础，其准确性直接影响查询性能。统计信息包含字段分布、索引选择性、行数估算等关键数据，这些数据通过ANALYZE TABLE命令更新。MySQL 8.0之后默认启用innodb_stats_persistent参数，该参数决定了统计信息是否持久化存储。当设置为ON时，统计信息会以独立文件形式存储，避免每次查询时重新计算，但可能导致统计信息滞后。而设置为OFF时，统计信息仅在查询时动态计算，虽然实时性高但会增加I/O开销。

统计信息的更新频率受innodb_stats_sample_pages参数控制，该参数定义了统计信息采样页数。默认值为20，但针对大表可能需要调高以获取更精确的分布信息。例如，对10亿行的表，若设置为200，可覆盖更多数据分布，但会显著增加更新时间。需要注意的是，ANALYZE TABLE会锁表，因此在业务低峰期执行更合适。

6.2 统计信息的维护策略

统计信息的维护需结合业务特征制定策略。对于数据更新频繁的表，建议设置innodb_stats_persistent_sample_pages为200，并每周执行一次ANALYZE TABLE。而对于数据变化缓慢的统计表，可将innodb_stats_persistent设为OFF，通过SHOW INDEX定期检查统计信息准确性。例如，当SHOW INDEX显示Cardinality字段与实际数据量存在30%偏差时，需手动触发更新。

在MySQL 8.0中新增的ANALYZE TABLE并行化功能可显著提升维护效率。通过innodb_stats_persistent_sample_pages和innodb_stats_sample_pages参数的配合，可在不影响业务的前提下完成统计信息更新。此外，information_schema.COLUMN_STATISTICS视图可实时监控统计信息状态，当#Rows字段与实际数据量偏差超过10%时，需立即执行更新。

6.3 统计信息的优化实践

在实际场景中，统计信息的优化需结合索引设计与查询模式。对于使用覆盖索引的场景，应定期分析索引选择性。例如，当status=1字段的基数（Cardinality）低于100万时，说明该字段的选择性较低，可能需要增加联合索引字段。通过EXPLAIN查看Extra列的Using index提示，确认统计信息是否有效指导查询优化器选择索引。

针对大表的统计信息更新，可采用分片更新策略。例如，将ANALYZE TABLE拆分为多个小范围更新，使用WHERE子句限制数据范围。如ANALYZE TABLE inventory WHERE status=1仅更新状态为1的数据统计信息，避免全表锁表。同时，可结合pt-online-schema-change工具在后台执行统计信息更新，减少对业务的影响。

在统计信息更新过程中，需监控SHOW ENGINE INNODB STATUS中的BUFFER POOL状态。当Buffer pool hit rate低于90%时，可能需要调整innodb_buffer_pool_size参数，确保统计信息更新时有足够的内存缓存数据页。此外，通过SHOW STATUS LIKE 'Innodb_row_lock_time%'监控锁等待时间，若发现统计信息更新导致锁等待超过1秒，需立即调整维护策略。

6.4 统计信息的异常处理

当统计信息更新失败时，可通过SHOW ENGINE INNODB STATUS查看错误日志。常见的异常包括磁盘空间不足、权限问题或数据页损坏。对于磁盘空间不足的情况，需检查innodb_data_file_path配置，适当调整innodb_data_file_path的大小。若因权限问题导致更新失败，需确保MySQL服务账户对数据目录有读写权限。

在统计信息更新后，可通过SHOW INDEX FROM inventory验证统计信息是否生效。例如，当Cardinality字段从50000变为100000时，说明统计信息已正确更新。若更新后执行计划未变化，需检查optimizer_switch参数中的use_index_extensions是否启用，该参数控制是否使用索引统计信息进行优化。

对于统计信息更新导致的查询性能波动，可通过SET GLOBAL optimizer_stats_on_metadata=1临时禁用统计信息元数据使用，但该操作可能影响查询优化准确性。最终需通过EXPLAIN对比更新前后的执行计划，确认统计信息优化效果。若发现统计信息更新后性能下降，需回滚到之前的统计信息版本，并分析更新参数设置是否合理。