本文目录导读：
索引优化是数据库性能调优的核心,目标是用最小的索引代价，换取最极致的查询速度，以下是从实战角度总结的6大索引优化技巧：
最左前缀法则
这是联合索引的核心规则，假设有联合索引 (a, b, c)：
- 有效查询：
WHERE a=1 AND b=2（用了a和b）、WHERE a=1（用了a）。 - 无效查询：
WHERE b=2（未使用a，索引失效）、WHERE a=1 ORDER BY c（只用了a，c用于排序而非查找）。 - 优化建议：将最常查询、区分度最高的列放在联合索引的最左边。
覆盖索引
-
定义：查询的所有字段（包括SELECT、WHERE、ORDER BY）都包含在同一个索引中。
-
优势：避免回表，直接从索引树获取数据，大幅减少磁盘I/O。
-
示例：
-- 假设有索引 (name, age) -- 这个查询是覆盖索引（只要name和age） SELECT name, age FROM users WHERE name = '张三'; -- 这个查询需要回表（多了phone字段不在索引中） SELECT name, age, phone FROM users WHERE name = '张三';
区分度的核心地位
- 原则：索引列的基数（Cardinality）要足够大。
- 判断：计算区分度 =
COUNT(DISTINCT 列名) / COUNT(*)。大于10%一般可接受，低于1%（如性别、状态码）强烈不建议建索引。 - 反面案例：性别列只有“男/女”，使用索引扫描会比全表扫描更慢（因为还要回表）。
避免索引失效
这是日常最容易踩的坑,常见场景：
| 场景 | 错误写法 | 优化写法 |
|---|---|---|
| 模糊查询 | WHERE name LIKE '%张三%' |
WHERE name LIKE '张三%'（利用前缀匹配） |
| 函数操作 | WHERE YEAR(create_time) = 2024 |
WHERE create_time >= '2024-01-01' AND create_time < '2025-01-01' |
| 隐式类型转换 | WHERE phone = 123456（phone是varchar） |
WHERE phone = '123456' |
| 不等条件 | WHERE status != 1 |
考虑使用反向查询或业务分离 |
| OR 条件 | WHERE a=1 OR b=2（a有索引，b没有则全表扫描） |
改为 UNION ALL 或给b也加索引 |
前缀索引与冗余索引
- 前缀索引：对长字符串（如
VARCHAR(255)）索引时，只索引前N个字符。-- 只索引email的前10个字符 CREATE INDEX idx_email_prefix ON users(email(10));
注意：需要测试确定前缀长度（以不严重降低区分度为界）。
- 冗余索引：删除重复或相似索引。
INDEX (a)+INDEX (a,b)→ 保留INDEX (a,b)即可。INDEX (a)+INDEX (a,b)+INDEX (a,b,c)→ 保留最宽的那个。
排序与分组优化
- 排序：
ORDER BY的字段顺序要严格匹配索引顺序。- 索引
(a, b)能高效支持ORDER BY a, b，但不支持ORDER BY b, a。
- 索引
- 分组：
GROUP BY本质是先排序后分组，索引同样能加速。-- 索引 (dept_id, status) 可以优化这个查询 SELECT dept_id, COUNT(*) FROM employees GROUP BY dept_id, status;
- 够用就好：不要给所有列都加索引（索引会增加写入、删除、更新的开销，且占用磁盘空间）。
- 小表不用：表记录数 < 1000 时，全表扫描可能比索引扫描更快（因为索引涉及随机I/O）。
- 看 Explain：任何索引优化后，都通过
EXPLAIN检查key和rows（扫描行数明显减少才算优化到位）。 - 写优先：如果表写入频繁（如日志表），索引数量控制在3-5个以内。
永远不要在线上直接改索引，先用慢查询日志定位问题SQL，然后在测试环境模拟数据压测，确认优化效果后再上线。