千万级大表 COUNT 优化实测：从 8s 到毫秒级

问题背景：为什么一个 COUNT 能把库打满

在订单、日志、消息明细等千万级 MySQL 表上，最常见也最容易被低估的慢查询就是 COUNT(*)。业务通常需要三类数字：总量、按状态筛选后的数量、后台分页总数。数据量小时它们都是毫秒级；当表超过千万且写入持续增加时，同一个 SQL 可能从几十毫秒涨到数秒，甚至造成缓冲池污染和主从延迟。

先构造一张接近真实业务的订单明细表：

CREATE TABLE order_item (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  order_no VARCHAR(32) NOT NULL,
  user_id BIGINT UNSIGNED NOT NULL,
  status TINYINT NOT NULL DEFAULT 0,
  amount DECIMAL(12,2) NOT NULL DEFAULT 0.00,
  created_at DATETIME NOT NULL,
  updated_at DATETIME NOT NULL,
  PRIMARY KEY (id),
  KEY idx_user_created (user_id, created_at),
  KEY idx_status_created (status, created_at)
) ENGINE=InnoDB;

-- 用递归生成 100 万测试数据，千万级环境可用存储过程分批灌入
INSERT INTO order_item
(order_no, user_id, status, amount, created_at, updated_at)
WITH RECURSIVE seq AS (
  SELECT 1 AS n
  UNION ALL
  SELECT n + 1 FROM seq WHERE n < 1000000
)
SELECT
  CONCAT('O', LPAD(n, 12, '0')),
  100000 + (n % 50000),
  n % 5,
  ROUND(50 + RAND() * 500, 2),
  DATE_ADD('2025-01-01', INTERVAL n MINUTE),
  DATE_ADD('2025-01-01', INTERVAL n MINUTE)
FROM seq;

典型慢 SQL 如下：

-- 后台列表页：无条件总数
SELECT COUNT(*) FROM order_item;

-- 按状态统计
SELECT COUNT(*) FROM order_item WHERE status = 1;

-- 时间范围加状态
SELECT COUNT(*)
FROM order_item
WHERE status = 1
  AND created_at >= '2025-06-01'
  AND created_at < '2025-07-01';

InnoDB 的 COUNT(*) 不像 MyISAM 那样保存全表行数，因为多版本并发控制下，不同事务在同一时刻看到的行数可能不同。执行时需要选择一个索引遍历，判断每行对当前事务是否可见。即使不回表，扫描大量二级索引记录也要消耗 IO 和 CPU。

三种 COUNT 方案对比

方案一：近似统计，适合仪表盘和预估分页

如果页面只展示“约 1,200 万条”，或者搜索结果只需要判断量级，可以使用优化器估算值。它来自表统计信息，不需要逐行扫描，响应通常是毫秒级。

-- MySQL 8.0 查看估算行数
EXPLAIN SELECT * FROM order_item;

-- 也可以直接查询统计信息
SELECT
  TABLE_ROWS AS estimated_rows,
  ROUND(DATA_LENGTH / 1024 / 1024, 2) AS data_mb,
  ROUND(INDEX_LENGTH / 1024 / 1024, 2) AS index_mb,
  STATS_SAMPLE_PAGES
FROM information_schema.TABLES
WHERE TABLE_SCHEMA = DATABASE()
  AND TABLE_NAME = 'order_item';

-- 条件估算：EXPLAIN 的 rows 是选择率估算，不是精确值
EXPLAIN
SELECT *
FROM order_item
WHERE status = 1
  AND created_at >= '2025-06-01'
  AND created_at < '2025-07-01';

近似统计的优点是成本极低，缺点是误差不可控。information_schema.TABLES.TABLE_ROWS 在大量删除、更新、页分裂后可能明显偏离；EXPLAIN rows 受采样页和直方图影响，不能用于结算、对账、库存等强一致场景。

方案二：缓存计数，适合高频精确总数

对“全表总数”“用户订单数”“各状态数量”这类高频读取，可以维护计数表或使用 Redis 计数。创建订单时加一，取消或状态流转时同步调整。为了避免并发丢失更新，建议使用单行原子累加，而不是先查再写。

CREATE TABLE order_count_snapshot (
  count_key VARCHAR(64) NOT NULL,
  cnt BIGINT UNSIGNED NOT NULL DEFAULT 0,
  updated_at DATETIME NOT NULL,
  PRIMARY KEY (count_key)
) ENGINE=InnoDB;

INSERT INTO order_count_snapshot(count_key, cnt, updated_at)
VALUES ('total', 0, NOW())
ON DUPLICATE KEY UPDATE updated_at = NOW();

-- 新订单与总数递增放在同一事务
START TRANSACTION;

INSERT INTO order_item
(order_no, user_id, status, amount, created_at, updated_at)
VALUES ('O202508010001', 200001, 0, 199.00, NOW(), NOW());

UPDATE order_count_snapshot
SET cnt = cnt + 1,
    updated_at = NOW()
WHERE count_key = 'total';

COMMIT;

状态流转时要避免重复加减：

START TRANSACTION;

UPDATE order_item
SET status = 1, updated_at = NOW()
WHERE id = 1001 AND status = 0;

-- 只有实际从 0 改成 1，才调整两个状态计数
UPDATE order_count_snapshot
SET cnt = cnt - 1,
    updated_at = NOW()
WHERE count_key = 'status_0'
  AND ROW_COUNT() >= 0;

UPDATE order_count_snapshot
SET cnt = cnt + 1,
    updated_at = NOW()
WHERE count_key = 'status_1';

COMMIT;

实际项目中更推荐通过 binlog 消费、事务消息或本地事务表异步汇总，并提供定时校准任务。否则历史数据修复、批量导入、失败补偿都可能让缓存计数漂移。

方案三：选择更瘦的二级索引，降低精确 COUNT 成本

精确计数无法绕过可见性判断，但可以选择更小的索引。InnoDB 二级索引叶子节点存储索引列和主键，扫描窄索引通常比扫描聚簇索引读取更少页。

-- 查看执行计划，key 表示实际选择的索引
EXPLAIN FORMAT=JSON
SELECT COUNT(*)
FROM order_item
WHERE status = 1
  AND created_at >= '2025-06-01'
  AND created_at < '2025-07-01';

-- 联合索引符合最左匹配和范围扫描
ALTER TABLE order_item
  ADD INDEX idx_status_created (status, created_at);

-- 对比强制不同索引的扫描行数与耗时
SELECT COUNT(*)
FROM order_item FORCE INDEX (idx_status_created)
WHERE status = 1
  AND created_at >= '2025-06-01'
  AND created_at < '2025-07-01';

下图展示一次 COUNT 请求的决策路径：先判断是否要求精确，再选择统计信息、计数缓存或合适索引。

免责声明

本工具与文章内容仅用于学习测试场景，优化建议仅供参考，请勿直接用于生产环境，由此产生的风险自行承担。