优化 COUNT(*) 查询
COUNT(*) 的误区
很多人以为 COUNT(*) 是 MySQL 中最简单的操作，实际上它可能是最容易被误解的查询之一。
首先纠正一个常见错误认识：MyISAM 引擎的 COUNT(*) 确实极快（直接读行数计数器），但 InnoDB（MySQL 5.5+ 的默认引擎）不是。InnoDB 由于支持 MVCC 和事务隔离，COUNT(*) 需要逐行判断当前事务是否可见，不能使用独立的计数器。
不同 COUNT 变体的对比
-- 四种 COUNT 写法，性能差异显著
COUNT(*) -- 统计行数，不关心是否有 NULL
COUNT(1) -- 统计行数，与 COUNT(*) 等价
COUNT(col) -- 统计 col 列非 NULL 的行数
COUNT(DISTINCT col) -- 统计 col 列的去重非 NULL 行数
注意：COUNT(*) 和 COUNT(1) 在 MySQL 中完全等价。优化器会把 COUNT(1) 转换为 COUNT(*)。
如何优化 COUNT(*)
方案一：使用辅助索引
-- 慢：走主键索引（聚簇索引，数据量大）
SELECT COUNT(*) FROM big_table;
-- 快：走辅助索引（二级索引比聚簇索引小）
-- MySQL 会选最小的辅助索引来计数
InnoDB 在执行 COUNT(*) 时，会自动选择最小的二级索引（最窄的索引树）来遍历，因为：
– 聚簇索引包含所有字段，树很大
– 二级索引只包含索引列和主键，树很小
所以，有一个二级索引甚至比没有索引更快。如果你经常对某张表做 COUNT(*)，可以创建一个最小字段的复合索引用作计数。
方案二：使用近似值
对于不要求精确计数的场景（如后台列表的”共 XX 条”），使用 SHOW TABLE STATUS 的估算值。
-- 获取近似行数，几乎零成本
SHOW TABLE STATUS LIKE 'big_table'\G
-- 输出中的 Rows 是估算值（InnoDB 的采样估算）
或者用 EXPLAIN：
-- EXPLAIN 的 rows 列也包含估算值
EXPLAIN SELECT COUNT(*) FROM big_table;
方案三：使用独立的计数表
创建一张小表专门维护行数：
CREATE TABLE row_count (
table_name VARCHAR(50) PRIMARY KEY,
count BIGINT NOT NULL
);
-- 在业务代码中维护，插入一条 count+1，删除一条 count-1
-- 或使用触发器
优点：SELECT count FROM row_count WHERE table_name = 'orders' 是 O(1) 操作
缺点：需要额外的维护逻辑，在分布式环境下计数可能不准
方案四：使用 Redis 缓存计数
// 插入时
redis.incr("count:orders");
// 删除时
redis.decr("count:orders");
// 查询时
Long count = redis.get("count:orders");
优点：性能极高，O(1)
缺点：Redis 宕机可能丢失计数，需要做数据一致性
方案五：使用信息统计表
在系统设计时，直接设计一张统计表：
CREATE TABLE order_statistics (
stat_date DATE PRIMARY KEY,
total_count INT NOT NULL,
yesterday_count INT NOT NULL
);
-- 每天通过定时任务维护统计
INSERT INTO order_statistics (stat_date, total_count)
VALUES ('2024-12-31', (SELECT COUNT(*) FROM orders))
ON DUPLICATE KEY UPDATE total_count = VALUES(total_count);
带条件的 COUNT 优化
-- 场景：统计某个月的订单数
SELECT COUNT(*) FROM orders WHERE create_time BETWEEN '2024-01-01' AND '2024-01-31';
创建合适的索引是关键：
CREATE INDEX idx_create_time ON orders(create_time);
但即使有索引，如果条件过滤效果差（结果集很大），COUNT(*) 仍然需要扫描大量行。这时上面提到的计数表方案更合适。
海量数据下的终极方案
当表行数达到亿级时：
1. 放弃 COUNT(*) 实时精确计数
2. 改用预聚合 + 定时任务维护计数
3. 前端展示时加上”约”字（如”约 1.2 亿条”）
各方案对比
| 方案 | 精确度 | 性能 | 维护成本 |
|---|---|---|---|
| 辅助索引 COUNT | 精确 | 中 | 低 |
| SHOW TABLE STATUS | 近似 | 高 | 无 |
| 计数表 | 精确 | 极高 | 中 |
| Redis | 近似 | 极高 | 高 |
| 预聚合统计表 | 精确 | 极高 | 中 |
总结
COUNT(*) 优化的核心思路是避免每次查询都扫描大量行。从最轻量的”利用最窄二级索引”，到中等成本的”计数表”，再到高成本的”Redis 计数”，根据业务对精确度和实时性的要求选择合适的方案。对于 TB 级表，实时精确的 COUNT 本身就是不可能的，要接受近似值或异步更新的设计方案。
暂无评论内容