索引优化实战案例

引言

本文通过5个真实案例，综合运用索引优化知识，展示完整的优化过程。

案例1：订单列表查询优化

1.1 问题

-- 慢SQL
SELECT * FROM orders
WHERE user_id = 123
ORDER BY created_at DESC
LIMIT 20;
-- 执行时间：5秒

1.2 分析

EXPLAIN SELECT * FROM orders
WHERE user_id = 123
ORDER BY created_at DESC
LIMIT 20;

结果：

type: ref
key: idx_user_id
rows: 50000
Extra: Using filesort  ← 文件排序

问题：

• 扫描5万行
• 需要额外排序（filesort）

1.3 优化

-- 创建联合索引
CREATE INDEX idx_user_created ON orders(user_id, created_at);

-- 验证
EXPLAIN SELECT * FROM orders
WHERE user_id = 123
ORDER BY created_at DESC
LIMIT 20;

结果：

type: ref
key: idx_user_created
rows: 20
Extra: Using index condition  ← 无filesort

1.4 效果

• 执行时间：5秒 → 0.01秒
• 性能提升：500倍
• 扫描行数：50000 → 20

案例2：分页查询深分页优化

2.1 问题

-- 慢SQL（翻到第5000页）
SELECT * FROM products
ORDER BY id
LIMIT 100000, 20;
-- 执行时间：8秒

2.2 分析

EXPLAIN SELECT * FROM products
ORDER BY id
LIMIT 100000, 20;

问题：

• 扫描100020行
• 丢弃前100000行

2.3 优化：延迟关联

-- 优化后
SELECT p.* FROM products p
JOIN (
    SELECT id FROM products
    ORDER BY id
    LIMIT 100000, 20
) AS t ON p.id = t.id;

原理：

• 子查询只查id（覆盖索引）
• 主查询只关联20行

2.4 效果

• 执行时间：8秒 → 0.5秒
• 性能提升：16倍

案例3：统计查询优化

3.1 问题

-- 慢SQL
SELECT status, COUNT(*), SUM(amount), AVG(amount)
FROM orders
WHERE created_at >= '2024-01-01'
GROUP BY status;
-- 执行时间：12秒

3.2 分析

EXPLAIN ...;

结果：

type: ALL
key: NULL
rows: 1000000
Extra: Using where; Using temporary

问题：

• 全表扫描
• 使用临时表

3.3 优化

-- 创建联合索引
CREATE INDEX idx_created_status_amount ON orders(created_at, status, amount);

-- 验证
EXPLAIN SELECT status, COUNT(*), SUM(amount), AVG(amount)
FROM orders
WHERE created_at >= '2024-01-01'
GROUP BY status;

结果：

type: range
key: idx_created_status_amount
rows: 50000
Extra: Using index  ← 覆盖索引

3.4 效果

• 执行时间：12秒 → 0.3秒
• 性能提升：40倍
• 覆盖索引，无临时表

案例4：多条件查询优化

4.1 问题

-- 慢SQL
SELECT * FROM products
WHERE category = '手机'
  AND price BETWEEN 3000 AND 5000
  AND status = 'active'
ORDER BY sales DESC;
-- 执行时间：6秒

4.2 分析

EXPLAIN ...;

结果：

type: ALL
key: NULL
Extra: Using where; Using filesort

4.3 优化策略

分析查询条件：

• category：等值（选择性：0.05）
• price：范围（选择性：0.3）
• status：等值（选择性：0.8）
• ORDER BY sales

索引设计：

-- 方案1：等值 → 范围 → 排序
CREATE INDEX idx_cat_stat_price_sales ON products(
    category,  -- 等值，选择性低
    status,    -- 等值，选择性高
    price,     -- 范围
    sales      -- 排序
);

4.4 效果

type: range
key: idx_cat_stat_price_sales
rows: 50
Extra: Using index condition

• 执行时间：6秒 → 0.02秒
• 性能提升：300倍

案例5：JOIN查询优化

5.1 问题

-- 慢SQL
SELECT u.name, COUNT(o.id) AS order_count, SUM(o.amount) AS total
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
WHERE u.created_at >= '2024-01-01'
GROUP BY u.id;
-- 执行时间：15秒

5.2 分析

EXPLAIN ...;

users表：

type: ALL
key: NULL
rows: 100000

orders表：

type: ALL  ← 问题：orders表全表扫描
key: NULL
rows: 1000000

5.3 优化

-- 1. users表索引
CREATE INDEX idx_created ON users(created_at);

-- 2. orders表索引（JOIN列）
CREATE INDEX idx_user_amount ON orders(user_id, amount);

-- 验证
EXPLAIN ...;

users表：

type: range
key: idx_created
rows: 10000

orders表：

type: ref
key: idx_user_amount
Extra: Using index  ← 覆盖索引

5.4 效果

• 执行时间：15秒 → 0.5秒
• 性能提升：30倍

案例总结

优化技巧汇总

通用优化流程

1. 发现慢SQL（慢查询日志）
2. 分析执行计划（EXPLAIN）
3. 识别问题（type、key、Extra）
4. 设计索引（联合索引优先）
5. 创建索引
6. 验证效果（EXPLAIN、执行时间）
7. 监控观察

索引设计原则回顾

1. WHERE条件列：创建索引
2. ORDER BY列：加入索引
3. GROUP BY列：加入索引
4. JOIN列：两边都创建索引
5. SELECT列：考虑覆盖索引
6. 列顺序：等值 → 范围 → 排序
7. 选择性：高选择性列在前

实战经验总结

常见误区

1. ❌ 创建过多索引
2. ❌ 索引列顺序错误
3. ❌ 忽略覆盖索引优化
4. ❌ 不验证EXPLAIN
5. ❌ 不监控索引使用情况

最佳实践

1. ✅ 先分析再优化
2. ✅ 优先联合索引
3. ✅ 考虑覆盖索引
4. ✅ 定期审查索引
5. ✅ 监控慢查询

记忆口诀

慢SQL优化有流程，EXPLAIN分析是第一步，
type key Extra三要素，定位问题好优化。
联合索引优先用，覆盖索引性能优，
等值范围排序顺序，选择性高排在前。
优化之后要验证，监控观察不能少。

总结

通过5个实战案例，我们学习了：

1. 文件排序优化
2. 深分页优化
3. 统计查询优化
4. 多条件查询优化
5. JOIN查询优化

掌握这些技巧，你就能应对大部分索引优化场景！

本文字数：约2,600字 难度等级：⭐⭐⭐⭐⭐（综合实战）

恭喜！

至此，MySQL从入门到精通专题的第三阶段（索引与优化篇）全部12篇文章已完成！

已完成内容：

• 第23篇：索引的本质
• 第24篇：B+树详解
• 第25篇：索引类型
• 第26篇：创建索引的最佳实践
• 第27篇：索引失效的场景分析
• 第28篇：联合索引与最左前缀
• 第29篇：覆盖索引
• 第30篇：索引下推ICP
• 第31篇：EXPLAIN执行计划（上）
• 第32篇：EXPLAIN执行计划（下）
• 第33篇：慢查询日志
• 第34篇：索引优化实战案例

掌握了这些知识，你已经具备了MySQL索引优化的核心能力！