一个慢查询引发的深度技术探讨，从8秒到0.1秒的优化之旅
问题背景
在日常开发中，我们经常遇到这样的SQL查询：
SELECT count(id)
FROM cd_rating rating
WHERE rating.rating_type = 4
AND rating.areacode LIKE '22%';
当表数据量达到千万级时，这个查询可能变得异常缓慢（8秒以上）。本文将详细记录从发现问题到彻底解决的完整过程。
一、问题分析与初步诊断
1.1 执行计划分析
通过 EXPLAIN查看执行计划，发现扫描行数高达72万行：
EXPLAIN SELECT count(id) FROM cd_rating
WHERE rating_type = 4 AND areacode LIKE '22%';
-- 执行结果：
-- type: ref
-- possible _keys: idx_search_list, idx_create_modify, ... (多个索引)
-- key: idx_search_list
-- rows: 722926
-- filtered: 50.00
核心问题：优化器选择了错误的索引，导致大量数据扫描。
1.2 索引创建与初步优化
首先创建复合索引：
CREATE INDEX idx_rating_type_areacode
ON cd_rating (rating_type, areacode);
效果：速度从8秒降至7秒，改善有限。
二、覆盖索引的威力
2.1 什么是覆盖索引？
覆盖索引是指索引包含查询所需的所有字段，无需回表查询数据行。
对于我们的查询：
- 需要字段：
rating_type（条件）、areacode（条件）、id（结果） - 普通复合索引：
(rating_type, areacode)→ 不包含id，需要回表 - 覆盖索引：
(rating_type, areacode, id)→ 包含所有字段，无需回表
2.2 创建覆盖索引
CREATE INDEX idx_rating_type_areacode_covering
ON cd_rating (rating_type, areacode, id);
2.3 性能对比
| 索引类型 | 执行时间 | 是否需要回表 | I/O操作 |
|---|---|---|---|
| 无索引 | 8秒+ | 是（全表扫描） | 极高 |
| 复合索引 | 7秒 | 是 | 高 |
| 覆盖索引 | 0.1秒 | 否 | 极低 |
关键发现：覆盖索引避免了"回表"操作，性能提升80倍！
三、优化器选择问题
3.1 为什么优化器没选最佳索引？
创建覆盖索引后，优化器仍选择其他索引，执行计划显示：
key: idx_search_list, idx_create_modify, ... (多个索引尝试)
可能原因：
- 统计信息过时：数据库不知道新索引的效率
- 索引碎片化：新索引可能有碎片
- 其他索引干扰：优化器更熟悉已有索引
3.2 解决方案
-- 1. 更新统计信息
ANALYZE TABLE cd_rating;
-- 2. 重建索引（业务低峰期）
ALTER TABLE cd_rating DROP INDEX idx_rating_type_areacode_covering;
ALTER TABLE cd_rating ADD INDEX idx_rating_type_areacode_covering
(rating_type, areacode, id)
-- 2.1 查询的时候可以强制指定使用某个索引
... FROM cd_rating USE INDEX (idx_rating_type_areacode_covering) ...
-- 2.2 有时更换索引的属性顺序，也会影响优化器的选择
ALTER TABLE cd_rating ADD INDEX idx_rating_type_areacode_covering
(areacode, rating_type);
-- 3. 测试执行计划
EXPLAIN SELECT count(*) FROM cd_rating
WHERE rating_type = 4 AND areacode LIKE '22%';
-- 期望：Extra显示"Using index"，rows显著减少
四、生产环境索引使用
4.1 强制索引的风险
-- 不推荐在生产环境长期使用
SELECT count(id)
FROM cd_rating
USE INDEX (idx_rating_type_areacode_covering)
WHERE rating_type = 4 AND areacode LIKE '22%';
风险：
- 破坏优化器的智能选择
- 索引变更时可能报错
- 数据库升级后可能失效
- 增加维护成本
4.2 正确做法
`-- 1. 创建最佳覆盖索引
CREATE INDEX idx_best_covering
ON cd_rating (areacode, rating_type, id);
-- 2. 更新统计信息
ANALYZE TABLE cd_rating;
-- 3. 让优化器自由选择
SELECT count(*) FROM cd_rating
WHERE rating_type = 4 AND areacode LIKE '22%';`
五、COUNT函数详解
5.1 三种写法的区别
| 函数 | 统计对象 | NULL处理 | 性能 | 适用场景 |
|---|---|---|---|---|
count(*) |
所有行 | 包括NULL | 最优 | 统计总行数 |
count(id) |
指定列 | 不包括NULL | 较差 | 统计非NULL的id |
count(1) |
所有行 | 包括NULL | 最优 | 等同count(*) |
5.2 为什么count(*)性能最好？
- MySQL对
count(*)有特殊优化 - 可选择最小的二级索引统计
- 无需读取列值，只统计行数
5.3 NULL值的影响
-- 创建测试表
CREATE TABLE test_null (
id INT PRIMARY KEY,
value INT -- 允许NULL
);
INSERT INTO test_null VALUES (1, 100), (2, NULL), (3, 200);
SELECT
count(*) as total_rows, -- 结果: 3
count(id) as count_id, -- 结果: 3 (id无NULL)
count(value) as count_value -- 结果: 2 (value有NULL)
FROM test_null;
5.4 场景建议
-- 原查询：count(id)
SELECT count(id) FROM cd_rating WHERE ...;
-- 优化建议：如果id是主键且无NULL，改为count(*)
SELECT count(*) FROM cd_rating WHERE ...;
-- 性能对比：count(*)通常更快，因为优化器可选择最小索引
六、生产环境最佳实践
6.1 索引创建策略
-- 1. 分析查询模式
EXPLAIN SELECT ... WHERE col1 = ? AND col2 LIKE ?;
-- 2. 创建合适索引
-- 等值查询字段在前，前缀匹配字段在后
CREATE INDEX idx_optimal ON table (eq_field, prefix_field);
-- 3. 包含查询字段（覆盖索引）
CREATE INDEX idx_covering ON table (eq_field, prefix_field, select_field);
6.2 维护计划
-- 定期更新统计信息（建议每周）
ANALYZE TABLE cd_rating;
-- 监控慢查询
SET GLOBAL slow_query_log = 1;
SET GLOBAL long_query_time = 1;
-- 检查索引使用情况
SHOW INDEX FROM cd_rating;
6.3 监控指标
-- 查看索引大小
SELECT
table_name,
index_name,
stat_value * @@innodb_page_size as index_size_bytes
FROM mysql.innodb_index_stats
WHERE table_name = 'cd_rating'
AND stat_name = 'size';
-- 分析查询性能
SELECT
DIGEST_TEXT,
COUNT_STAR,
AVG_TIMER_WAIT/1000000000 as avg_time_ms
FROM performance_schema.events_statements_summary_by_digest
WHERE DIGEST_TEXT LIKE '%cd_rating%';
七、进阶优化方案
7.1 分区表
七、进阶优化方案
-- 按rating_type分区，适合数据量极大的情况
ALTER TABLE cd_rating
PARTITION BY LIST(rating_type) (
PARTITION p_0_3 VALUES IN (0,1,2,3),
PARTITION p_4 VALUES IN (4),
PARTITION p_5_9 VALUES IN (5,6,7,8,9)
);
7.2 汇总表/物化视图
-- 创建汇总表（适合查询固定、实时性要求不高的场景）
CREATE TABLE cd_rating_summary (
rating_type INT NOT NULL,
area_prefix VARCHAR(10) NOT NULL,
record_count INT NOT NULL,
last_updated TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (rating_type, area_prefix)
);
-- 定期刷新汇总表
INSERT INTO cd_rating_summary
SELECT rating_type, LEFT(areacode, 2), COUNT(*), NOW()
FROM cd_rating
GROUP BY rating_type, LEFT(areacode, 2)
ON DUPLICATE KEY UPDATE record_count = VALUES(record_count);
-- 查询汇总表（毫秒级）
SELECT record_count FROM cd_rating_summary
WHERE rating_type = 4 AND area_prefix = '22';
7.3 部分索引
-- 只为特定条件创建索引（PostgreSQL支持）
CREATE INDEX idx_partial
ON cd_rating (areacode)
WHERE rating_type = 4;
八、总结与检查清单
8.1 优化路径回顾
- 发现问题：8秒慢查询
- 初步优化：创建复合索引 → 7秒
- 核心突破：创建覆盖索引 → 0.1秒
- 生产部署：更新统计信息，避免强制索引
8.2 优化检查清单
- 分析执行计划，识别扫描行数
- 创建覆盖索引，包含所有查询字段
- 更新统计信息，让优化器正确选择
- 验证Extra字段显示"Using index"
- 生产环境避免使用强制索引
- 根据业务场景选择
count(*)或count(列名) - 定期维护索引和统计信息
8.3 关键结论
- 覆盖索引是千万级数据表优化的关键
count(*)通常比count(id)性能更好- 生产环境应让优化器自由选择，不要强制索引
- 定期维护（ANALYZE TABLE）比强制索引更可靠
- 对于统计查询，考虑汇总表或分区表
九、推荐方案
-- 1. 创建最佳覆盖索引
CREATE INDEX idx_cd_rating_covering
ON cd_rating (areacode, rating_type, id);
-- 2. 更新统计信息
ANALYZE TABLE cd_rating;
-- 3. 优化后的查询（使用count(*)，性能最优）
SELECT count(*)
FROM cd_rating
WHERE rating_type = 4
AND areacode LIKE '22%';
-- 4. 预期性能
-- 扫描行数：几千到几万行（而非几十万）
-- 执行时间：毫秒级
-- I/O操作：仅读取索引页，无需回表
优化心得：数据库性能优化不是简单地"加索引"，而是需要理解执行计划、数据分布和查询模式。覆盖索引是解决COUNT查询性能问题的利器，但生产环境需要谨慎使用强制索引，让优化器在统计数据和索引之间做出智能选择。
本文记录了一次完整的SQL优化实战，从发现问题到彻底解决，希望能帮助遇到类似问题的开发者。如有疑问，欢迎在评论区交流讨论。
评论区