MySQL COUNT 优化实战 | 清晨的一缕阳光

核心概念

COUNT 是最常用的聚合函数，但在大数据量场景下性能问题突出。

COUNT 类型对比

类型	含义	NULL 处理	性能
*COUNT()**	统计所有行	包括 NULL	最优
COUNT(1)	统计所有行	包括 NULL	与 COUNT(*) 相同
COUNT(列)	统计非 NULL 行	排除 NULL	稍慢
COUNT(DISTINCT 列)	统计去重后的行数	排除 NULL	最慢

MySQL 官方说明

MySQL 官方文档明确说明：
COUNT(*) 是专门优化过的，不需要担心性能问题。

InnoDB 对 COUNT(*) 的优化：
- 不需要读取具体列值
- 直接使用可见性判断
- 选择最小的索引扫描

COUNT 底层原理

InnoDB COUNT 实现

InnoDB 不存储表的总行数（与 MyISAM 不同）：
原因：MVCC + 事务隔离，不同事务看到的行数不同

COUNT(*) 执行过程：
1. 选择一个最小的索引（如果有二级索引）
2. 遍历索引树
3. 根据 MVCC 可见性判断是否计数
4. 返回总数

MyISAM vs InnoDB

-- MyISAM：存储总行数，COUNT(*) 极快
SELECT COUNT(*) FROM users;  -- 0.001s
-- 直接从元数据读取

-- InnoDB：需要扫描，COUNT(*) 较慢
SELECT COUNT(*) FROM users;  -- 10s
-- 需要遍历索引树

为什么 InnoDB 不存储总行数？

1. MVCC：不同事务看到的行数不同
2. 并发：存储总行数需要加锁，影响并发
3. 事务：未提交的事务影响行数统计

COUNT 优化方案

方案 1：使用覆盖索引

-- 创建覆盖索引
CREATE INDEX idx_create_time ON orders(create_time);

-- COUNT 使用覆盖索引
SELECT COUNT(*) FROM orders WHERE create_time > '2024-01-01';
-- Extra: Using index（只扫描索引树，不回表）

-- 对比：无索引
SELECT COUNT(*) FROM orders WHERE status = 1;
-- 全表扫描

索引选择原则：

选择区分度高的索引
选择最小的索引（列少）
复合索引考虑最左前缀

方案 2：近似计数

-- 精确 COUNT（慢）
SELECT COUNT(*) FROM orders;  -- 10s

-- 近似计数（快）
SELECT TABLE_ROWS FROM information_schema.TABLES 
WHERE TABLE_SCHEMA = 'database' AND TABLE_NAME = 'orders';
-- 0.01s

-- 误差：约 1-5%，可接受场景使用

注意： TABLE_ROWS 是近似值，不定期更新。

-- 更新统计信息
ANALYZE TABLE orders;

-- 查看更新后的统计
SELECT TABLE_ROWS FROM information_schema.TABLES 
WHERE TABLE_NAME = 'orders';

方案 3：缓存计数

-- 创建计数表
CREATE TABLE table_counts (
    table_name VARCHAR(50) PRIMARY KEY,
    row_count BIGINT,
    update_time DATETIME
);

-- 初始化
INSERT INTO table_counts VALUES ('orders', 1000000, NOW());

-- 触发器维护（增量更新）
DELIMITER $$
CREATE TRIGGER trg_order_after_insert
AFTER INSERT ON orders
FOR EACH ROW
BEGIN
    UPDATE table_counts 
    SET row_count = row_count + 1, update_time = NOW()
    WHERE table_name = 'orders';
END$$

CREATE TRIGGER trg_order_after_delete
AFTER DELETE ON orders
FOR EACH ROW
BEGIN
    UPDATE table_counts 
    SET row_count = row_count - 1, update_time = NOW()
    WHERE table_name = 'orders';
END$$
DELIMITER ;

-- 查询缓存的计数
SELECT row_count FROM table_counts WHERE table_name = 'orders';
-- 0.001s

适用场景：

需要频繁查询总数
可以接受秒级延迟
高并发场景

方案 4：Redis 缓存

// Redis 缓存计数
@Service
public class OrderCountService {
    
    @Resource
    private RedisTemplate<String, Long> redisTemplate;
    
    @Resource
    private OrderMapper orderMapper;
    
    // 获取计数
    public Long getOrderCount() {
        String key = "count:orders";
        Long count = redisTemplate.opsForValue().get(key);
        
        if (count == null) {
            // 缓存未命中，查询数据库
            count = orderMapper.count();
            // 写入缓存，5 分钟过期
            redisTemplate.opsForValue().set(key, count, 5, TimeUnit.MINUTES);
        }
        
        return count;
    }
    
    // 增量更新
    @Transactional
    public void createOrder(Order order) {
        orderMapper.insert(order);
        // 异步更新 Redis
        redisTemplate.opsForValue().increment("count:orders");
    }
    
    // 定期校准（防止缓存漂移）
    @Scheduled(cron = "0 0 * * * ?")  // 每小时
    public void calibrate() {
        Long dbCount = orderMapper.count();
        redisTemplate.opsForValue().set("count:orders", dbCount);
    }
}

适用场景：

超高并发
可以接受短暂不一致
需要毫秒级响应

方案 5：分库分表计数

-- 分表场景：orders_0, orders_1, ..., orders_9

-- 方案 1：汇总所有分表
SELECT SUM(cnt) FROM (
    SELECT COUNT(*) AS cnt FROM orders_0
    UNION ALL
    SELECT COUNT(*) AS cnt FROM orders_1
    UNION ALL
    ...
    SELECT COUNT(*) AS cnt FROM orders_9
) tmp;

-- 方案 2：维护汇总表
CREATE TABLE order_count_summary (
    date DATE PRIMARY KEY,
    total_count BIGINT
);

-- 每天统计一次
INSERT INTO order_count_summary
SELECT DATE(create_time), COUNT(*) 
FROM orders 
GROUP BY DATE(create_time)
ON DUPLICATE KEY UPDATE total_count = VALUES(total_count);

-- 查询总数
SELECT SUM(total_count) FROM order_count_summary;

方案 6：使用 COUNT(1) vs COUNT(*)

-- MySQL 5.7+：COUNT(*) 和 COUNT(1) 性能相同
SELECT COUNT(*) FROM orders;  -- 优化器转为 COUNT(1)
SELECT COUNT(1) FROM orders;  -- 直接执行

-- 执行计划相同
EXPLAIN SELECT COUNT(*) FROM orders;
EXPLAIN SELECT COUNT(1) FROM orders;

-- 结论：使用 COUNT(*)，语义更清晰

方案 7：优化 COUNT(DISTINCT)

-- COUNT(DISTINCT) 性能最差
SELECT COUNT(DISTINCT user_id) FROM orders;
-- 需要去重，使用临时表

-- 优化 1：使用近似去重（MySQL 8.0.12+）
-- 没有内置的近似去重函数，需要自定义

-- 优化 2：使用 HyperLogLog（Redis）
-- Redis: PFADD orders:user {user_id}
-- Redis: PFCOUNT orders:user

-- 优化 3：位图（适合整数 ID）
-- 使用位图记录已出现的 ID

方案 8：分区表 COUNT 优化

-- 创建分区表
CREATE TABLE orders (
    id BIGINT,
    create_time DATETIME,
    ...
) PARTITION BY RANGE (YEAR(create_time)) (
    PARTITION p0 VALUES LESS THAN (2020),
    PARTITION p1 VALUES LESS THAN (2021),
    PARTITION p2 VALUES LESS THAN (2022),
    PARTITION p3 VALUES LESS THAN (2023),
    PARTITION p4 VALUES LESS THAN MAXVALUE
);

-- 查询特定分区
SELECT COUNT(*) FROM orders PARTITION (p3) 
WHERE create_time >= '2023-01-01';
-- 只扫描 2023 年的分区

-- 查询所有分区
SELECT COUNT(*) FROM orders;
-- 扫描所有分区

不同场景 COUNT 优化

场景 1：带条件 COUNT

-- 优化前：全表扫描
SELECT COUNT(*) FROM orders WHERE status = 1;

-- 优化 1：添加索引
CREATE INDEX idx_status ON orders(status);
SELECT COUNT(*) FROM orders WHERE status = 1;
-- Extra: Using index

-- 优化 2：覆盖索引
CREATE INDEX idx_status_id ON orders(status, id);
SELECT COUNT(*) FROM orders WHERE status = 1;
-- 只扫描索引，不回表

场景 2：范围 COUNT

-- 优化前
SELECT COUNT(*) FROM orders WHERE create_time > '2024-01-01';

-- 优化：索引 + 覆盖
CREATE INDEX idx_create_time ON orders(create_time);
SELECT COUNT(*) FROM orders WHERE create_time > '2024-01-01';
-- Using index

场景 3：多条件 COUNT

-- 优化前
SELECT COUNT(*) FROM orders WHERE status = 1 AND user_id = 100;

-- 优化：复合索引
CREATE INDEX idx_status_user ON orders(status, user_id);
SELECT COUNT(*) FROM orders WHERE status = 1 AND user_id = 100;
-- Using index

场景 4：GROUP BY COUNT

-- 优化前
SELECT user_id, COUNT(*) AS cnt FROM orders GROUP BY user_id;
-- Using temporary

-- 优化：索引
CREATE INDEX idx_user_id ON orders(user_id);
SELECT user_id, COUNT(*) AS cnt FROM orders GROUP BY user_id;
-- 仍然需要临时表，但扫描更快

-- 优化 2：覆盖索引
CREATE INDEX idx_user_id ON orders(user_id);
-- 只扫描 user_id 索引列

场景 5：COUNT + LIMIT

-- 分页查询总数
SELECT COUNT(*) FROM orders WHERE status = 1;
-- 慢查询

-- 优化：只查询是否有数据
SELECT 1 FROM orders WHERE status = 1 LIMIT 1;
-- 找到 1 条就返回，极快

-- 应用：判断是否有数据用 EXISTS
SELECT EXISTS(
    SELECT 1 FROM orders WHERE status = 1 LIMIT 1
) AS has_data;

场景 6：大数据量 COUNT

-- 1000 万数据，COUNT(*) 需要 10s+

-- 方案 1：近似计数
SELECT TABLE_ROWS FROM information_schema.TABLES 
WHERE TABLE_NAME = 'orders';
-- 0.01s

-- 方案 2：缓存
SELECT row_count FROM table_counts WHERE table_name = 'orders';
-- 0.001s

-- 方案 3：Redis
GET count:orders
-- < 1ms

-- 方案 4：业务妥协
-- 显示 "1000 万+" 而不是精确数字

COUNT 性能对比

不同 COUNT 方式性能对比

-- 测试数据：orders 表 1000 万行

-- 1. COUNT(*)
SELECT COUNT(*) FROM orders;
-- 耗时：10s

-- 2. COUNT(1)
SELECT COUNT(1) FROM orders;
-- 耗时：10s（与 COUNT(*) 相同）

-- 3. COUNT(id)
SELECT COUNT(id) FROM orders;
-- 耗时：12s（需要检查 NULL）

-- 4. COUNT(非 NULL 列)
SELECT COUNT(create_time) FROM orders;
-- 耗时：12s

-- 5. COUNT(DISTINCT user_id)
SELECT COUNT(DISTINCT user_id) FROM orders;
-- 耗时：30s（需要去重）

-- 6. 近似计数
SELECT TABLE_ROWS FROM information_schema.TABLES WHERE TABLE_NAME = 'orders';
-- 耗时：0.01s

-- 7. Redis 缓存
GET count:orders
-- 耗时：< 1ms

索引对 COUNT 性能影响

-- 无索引
SELECT COUNT(*) FROM orders WHERE status = 1;
-- 耗时：15s（全表扫描）

-- 有索引
CREATE INDEX idx_status ON orders(status);
SELECT COUNT(*) FROM orders WHERE status = 1;
-- 耗时：2s（索引扫描）

-- 覆盖索引
CREATE INDEX idx_status_id ON orders(status, id);
SELECT COUNT(*) FROM orders WHERE status = 1;
-- 耗时：1s（只扫描索引）

最佳实践总结

COUNT 使用原则

优先使用 COUNT(*)
- MySQL 专门优化
- 语义清晰
- 性能最优
避免 COUNT(DISTINCT)
- 性能差
- 考虑使用 Redis HyperLogLog
大数据量使用近似计数
- TABLE_ROWS
- 缓存计数
- Redis 缓存
添加合适的索引
- WHERE 条件字段建索引
- 使用覆盖索引

场景选择指南

1. 精确计数（小数据量）：
   → COUNT(*) + 索引

2. 精确计数（大数据量）：
   → 缓存计数 + 定期校准

3. 近似计数：
   → TABLE_ROWS
   → Redis 缓存

4. 判断是否有数据：
   → EXISTS(SELECT 1 ... LIMIT 1)

5. 去重计数：
   → Redis HyperLogLog
   → 位图

6. 分表场景：
   → 汇总表
   → 各分表 COUNT 求和

监控与调优

-- 查看 COUNT 相关慢查询
SELECT query, exec_time 
FROM mysql.slow_log 
WHERE query LIKE '%COUNT%';

-- 分析 COUNT 执行计划
EXPLAIN SELECT COUNT(*) FROM orders WHERE status = 1;

-- 查看索引使用情况
SHOW INDEX FROM orders;

常见误区

误区 1：COUNT(1) 比 COUNT(*) 快
→ MySQL 5.7+ 性能相同

误区 2：COUNT(列) 比 COUNT(*) 快
→ 实际更慢（需要检查 NULL）

误区 3：InnoDB 的 COUNT(*) 很快
→ 实际很慢（需要扫描）

误区 4：COUNT 必须精确
→ 很多场景可以接受近似值

参考资料

MySQL 官方文档 - COUNT 函数
MySQL 官方文档 - InnoDB 限制
《高性能 MySQL》第 4 章：查询性能优化