MySQL 百万级数据统计性能优化

为什么 COUNT(*) 在百万级数据中性能差？

COUNT(*) 在大型表上性能差的主要原因包括： - 需要扫描全表或索引来计算行数 - 对于 InnoDB 引擎，MVCC 机制需要检查每行可见性 - 没有合适的索引时会导致全表扫描 - 锁竞争可能加剧性能问题

优化方案

1. 使用近似值（适合不需要精确统计的场景）

-- 使用 EXPLAIN 获取近似行数
EXPLAIN SELECT * FROM your_table;
-- 或使用 information_schema 获取表统计信息
SELECT TABLE_ROWS FROM information_schema.TABLES 
WHERE TABLE_SCHEMA = 'your_db' AND TABLE_NAME = 'your_table';

2. 使用计数表（适合频繁统计且需要精确值的场景）

-- 创建计数表
CREATE TABLE table_counts (
    table_name VARCHAR(100) PRIMARY KEY,
    row_count BIGINT NOT NULL
);

-- 通过触发器或应用逻辑维护计数

3. 添加条件缩小统计范围

-- 添加 WHERE 条件
SELECT COUNT(*) FROM your_table WHERE create_time > '2023-01-01';

-- 使用覆盖索引
CREATE INDEX idx_covering ON your_table(status, id);
SELECT COUNT(*) FROM your_table WHERE status = 'active'; -- 会使用索引

4. 分区表统计

-- 按日期分区后可以只统计特定分区
SELECT COUNT(*) FROM your_table PARTITION(p202301);

5. 使用汇总表（适合定期统计场景）

-- 定期执行
INSERT INTO stats_daily (stat_date, user_count)
SELECT CURRENT_DATE(), COUNT(*) FROM users
ON DUPLICATE KEY UPDATE user_count = VALUES(user_count);

6. 使用 Redis 或其他缓存系统

// 伪代码示例
function incrementCount() {
    // 业务逻辑...
    redisClient.incr('user_count');
}

高级优化技巧

1. 使用物化视图（MySQL 8.0+）

   CREATE VIEW user_count_view AS SELECT COUNT(*) FROM users;
   

2. 使用 ClickHouse 等分析型数据库 处理超大规模统计

3. 分片计数：将计数任务分散到多个时间段执行

最佳实践建议

1. 评估是否真的需要精确计数 - 很多业务场景可以接受近似值
2. 为统计查询创建专用副本或从库，避免影响主库性能
3. 考虑定期预计算统计结果而非实时计算
4. 对大表避免频繁执行 COUNT(*) 操作

通过以上优化策略，可以显著提升百万级数据表的统计性能，根据您的具体业务场景选择合适的方案组合。