SQL 优化实战案例：从慢查询到高性能的完整指南

📊 一、常见 SQL 性能问题概览

🚨 生产环境性能问题分布

根据对 1000+ 生产环境慢查询的分析，常见问题比例如下：

索引问题：45%
JOIN优化：25%
全表扫描：15%
锁竞争：10%
其它：5%

📈 性能问题症状识别

快速诊断指标：

• 查询时间 > 1秒：需要优化

• 扫描行数 > 10000行：索引可能失效

• 临时表使用：需要优化查询或索引

• 文件排序：添加合适的索引

⚡ 二、案例一：慢查询优化实战

🎯 问题场景：电商订单查询缓慢

原始慢查询（执行时间：3.2秒）：

SELECT * FROM orders 
WHERE user_id = 1001 
  AND order_date BETWEEN '2023-01-01' AND '2023-12-31'
  AND status IN ('completed', 'shipped')
ORDER BY order_date DESC
LIMIT 100;

🔍 问题分析过程

执行计划分析：

EXPLAIN SELECT * FROM orders 
WHERE user_id = 1001 
  AND order_date BETWEEN '2023-01-01' AND '2023-12-31'
  AND status IN ('completed', 'shipped')
ORDER BY order_date DESC
LIMIT 100;

执行计划输出：

+----+-------------+--------+------------+------+---------------+------+---------+------+--------+----------+-----------------------------+
| id | select_type | table  | partitions | type | possible_keys | key  | key_len | ref  | rows   | filtered | Extra                       |
+----+-------------+--------+------------+------+---------------+------+---------+------+--------+----------+-----------------------------+
|  1 | SIMPLE      | orders | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 500000 |     1.00 | Using where; Using filesort |
+----+-------------+--------+------------+------+---------------+------+---------+------+--------+----------+-----------------------------+

问题诊断：

• type: ALL：全表扫描 50 万行数据

• Using filesort：文件排序，性能杀手

• 缺少合适索引

🛠️ 优化方案

创建复合索引：

-- 根据查询条件创建最优索引
CREATE INDEX idx_user_status_date 
ON orders(user_id, status, order_date);

-- 包含所有过滤字段和排序字段

优化后查询：

-- 使用覆盖索引优化
SELECT order_id, user_id, order_date, status, amount
FROM orders 
WHERE user_id = 1001 
  AND order_date BETWEEN '2023-01-01' AND '2023-12-31'
  AND status IN ('completed', 'shipped')
ORDER BY order_date DESC
LIMIT 100;

📊 性能对比

策略	实现方式	优点	缺点	适用场景
无索引查询	全表扫描 + 文件排序	实现简单，无需索引维护	性能极差，数据量大时不可用	小表查询（数据量 < 1000 行）
复合索引优化	创建 (user_id, status, order_date) 复合索引	查询性能提升 64 倍，减少磁盘 I/O	需要额外存储空间，写入稍慢	中大型表，频繁查询条件固定

优化效果：

• 执行时间：3.2秒 → 0.05秒（64倍提升）

• 扫描行数：500,000行 → 150行（3333倍提升）

• CPU使用：95% → 5%（19倍降低）

🔍 三、案例二：索引深度优化

🎯 问题场景：用户搜索功能性能瓶颈

原始查询（执行时间：2.1秒）：

SELECT * FROM users 
WHERE username LIKE '%john%'
   OR email LIKE '%john%'
   OR phone LIKE '%john%'
ORDER BY created_at DESC
LIMIT 50;

🔍 索引问题分析

现有索引情况：

-- 检查现有索引
SHOW INDEX FROM users;

-- 输出：
-- INDEX (username)  -- 前缀索引
-- INDEX (email)     -- 前缀索引  
-- INDEX (phone)     -- 前缀索引

执行计划分析：

+----+-------------+-------+------------+------+---------------+------+---------+------+--------+----------+-----------------------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows   | filtered | Extra                       |
+----+-------------+-------+------------+------+---------------+------+---------+------+--------+----------+-----------------------------+
|  1 | SIMPLE      | users | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 100000 |    100.00 | Using where; Using filesort |
+----+-------------+-------+------------+------+---------------+------+---------+------+--------+----------+-----------------------------+

问题根源：

• LIKE '%john%'导致索引失效

• OR 条件合并导致全表扫描

• 多个单列索引未有效利用

🛠️ 优化策略

方案一：全文索引优化（MySQL 5.6+）：

-- 创建全文索引
ALTER TABLE users 
ADD FULLTEXT idx_search_fields(username, email, phone);

-- 使用全文搜索
SELECT * FROM users 
WHERE MATCH(username, email, phone) AGAINST('john' IN BOOLEAN MODE)
ORDER BY created_at DESC
LIMIT 50;

方案二：分词索引优化（兼容性更好）：

-- 添加搜索专用字段
ALTER TABLE users ADD COLUMN search_tags VARCHAR(500);
UPDATE users SET search_tags = CONCAT(username, ' ', email, ' ', phone);

-- 创建分词索引
CREATE INDEX idx_search_tags ON users(search_tags(100));

-- 优化查询
SELECT * FROM users 
WHERE search_tags LIKE 'john%' 
   OR search_tags LIKE '% john%'
ORDER BY created_at DESC
LIMIT 50;

📊 优化方案对比

策略	实现方式	优点	缺点	适用场景
单列索引	为每个字段创建独立索引	简单直观，维护方便	OR 条件无法有效利用，LIKE '%xxx' 模糊查询失效	精确查询，字段独立使用
全文索引	使用 MATCH ... AGAINST 语法	专业文本搜索，支持相关性排序	MySQL 5.6+ 才支持，中文分词需额外插件（如 ngram、mmseg）	大量文本搜索，CMS、论坛、博客系统
分词索引	创建搜索专用字段 + 前缀索引	兼容性好，LIKE 'xxx%' 模糊查询可用	需要维护额外字段，存在一定数据冗余	中小规模搜索，对兼容性要求高的业务场景

优化效果：

• 全文索引方案：2.1秒 → 0.08秒（26倍提升）
• 分词索引方案：2.1秒 → 0.15秒（14倍提升）

🔗 四、案例三：大表 JOIN 优化

🎯 问题场景：电商报表查询超时

原始查询（执行时间：12.5秒）：

SELECT 
    u.username,
    o.order_id,
    o.amount,
    p.product_name,
    c.category_name
FROM users u
JOIN orders o ON u.user_id = o.user_id
JOIN order_items oi ON o.order_id = oi.order_id
JOIN products p ON oi.product_id = p.product_id
JOIN categories c ON p.category_id = c.category_id
WHERE u.register_date >= '2023-01-01'
  AND o.order_date BETWEEN '2023-01-01' AND '2023-12-31'
  AND o.amount > 1000
ORDER BY o.order_date DESC
LIMIT 100;

🔍 JOIN 问题分析

表数据量分析：

-- 各表数据量
SELECT 
    'users' as table_name, COUNT(*) as count FROM users
UNION ALL
SELECT 'orders', COUNT(*) FROM orders
UNION ALL  
SELECT 'order_items', COUNT(*) FROM order_items
UNION ALL
SELECT 'products', COUNT(*) FROM products
UNION ALL
SELECT 'categories', COUNT(*) FROM categories;

输出：

+------------+---------+
| table_name | count   |
+------------+---------+
| users      | 100,000 |
| orders     | 1,000,000|
| order_items| 5,000,000|
| products   | 50,000  |
| categories | 500     |
+------------+---------+

执行计划分析：

+----+-------------+-------+------------+--------+---------------+---------+---------+----------------------+---------+----------+----------------------------------------------------+
| id | select_type | table | partitions | type   | possible_keys | key     | key_len | ref                  | rows    | filtered | Extra                                              |
+----+-------------+-------+------------+--------+---------------+---------+---------+----------------------+---------+----------+----------------------------------------------------+
|  1 | SIMPLE      | u     | NULL       | range  | PRIMARY       | PRIMARY | 4       | NULL                 | 50000   |   100.00 | Using index condition; Using temporary; Using filesort |
|  1 | SIMPLE      | o     | NULL       | ref    | idx_user_id   | idx_user_id | 5    | db.u.user_id         | 10      |    33.33 | Using where                                        |
|  1 | SIMPLE      | oi    | NULL       | ref    | idx_order_id  | idx_order_id | 4    | db.o.order_id       | 5       |   100.00 | NULL                                               |
|  1 | SIMPLE      | p     | NULL       | eq_ref | PRIMARY       | PRIMARY | 4       | db.oi.product_id     | 1       |   100.00 | NULL                                               |
|  1 | SIMPLE      | c     | NULL       | eq_ref | PRIMARY       | PRIMARY | 4       | db.p.category_id    | 1       |   100.00 | NULL                                               |
+----+-------------+-------+------------+--------+---------------+---------+---------+----------------------+---------+----------+----------------------------------------------------+

问题诊断：

• 驱动表选择不当：从 50,000 行 users 表开始

• 中间结果集过大：多次 JOIN 产生大量中间数据

• 排序性能差：对大量数据排序

🛠️ 优化方案

方案一：优化 JOIN 顺序和索引：

-- 1. 添加缺失索引
CREATE INDEX idx_orders_user_date_amount 
ON orders(user_id, order_date, amount);

CREATE INDEX idx_users_register_date 
ON users(register_date, user_id);

-- 2. 使用 STRAIGHT_JOIN 强制优化器使用最佳顺序
SELECT STRAIGHT_JOIN
    u.username,
    o.order_id,
    o.amount,
    p.product_name,
    c.category_name
FROM orders o  -- 从 orders 开始，过滤条件更强
JOIN users u ON o.user_id = u.user_id
JOIN order_items oi ON o.order_id = oi.order_id
JOIN products p ON oi.product_id = p.product_id
JOIN categories c ON p.category_id = c.category_id
WHERE u.register_date >= '2023-01-01'
  AND o.order_date BETWEEN '2023-01-01' AND '2023-12-31'
  AND o.amount > 1000
ORDER BY o.order_date DESC
LIMIT 100;

方案二：分阶段查询 + 临时表：

-- 第一阶段：过滤核心数据
CREATE TEMPORARY TABLE temp_orders AS
SELECT o.order_id, o.user_id, o.amount, o.order_date
FROM orders o
WHERE o.order_date BETWEEN '2023-01-01' AND '2023-12-31'
  AND o.amount > 1000
ORDER BY o.order_date DESC
LIMIT 1000;  -- 扩大限制，确保最终有100条

-- 第二阶段：关联其他表
SELECT 
    u.username,
    t.order_id,
    t.amount,
    p.product_name,
    c.category_name
FROM temp_orders t
JOIN users u ON t.user_id = u.user_id
JOIN order_items oi ON t.order_id = oi.order_id
JOIN products p ON oi.product_id = p.product_id
JOIN categories c ON p.category_id = c.category_id
WHERE u.register_date >= '2023-01-01'
ORDER BY t.order_date DESC
LIMIT 100;

📊 优化策略对比

策略	实现方式	优点	缺点	适用场景
原始 JOIN	多表直接关联，依赖优化器	代码简洁，逻辑清晰	性能不可控，容易触发全表扫描	小数据量，开发测试环境
优化 JOIN 顺序	使用 STRAIGHT_JOIN 强制执行顺序	性能可控，避免优化器误判	需要手动分析，硬编码不够灵活	表关系复杂，优化器选择不佳时
分阶段查询	借助临时表 + 分步查询	内存消耗低，性能更稳定	代码复杂度高，需要多次查询	超大数据量，复杂聚合计算

优化效果：

优化JOIN顺序：12.5秒 → 1.2秒（10倍提升）

分阶段查询：12.5秒 → 0.8秒（15倍提升）

💡 五、总结与优化方法论

🎯 SQL 优化核心原则

优化优先级：

索引优化：

• 添加缺失索引
• 优化索引结构
• 覆盖索引

查询重写

• 减少JOIN
• 优化WHERE
• 分页优化

架构调整

• 读写分离
• 分库分表
• 缓存策略

📝 优化检查清单

索引优化检查项：

• ✅ WHERE 条件字段是否有索引？

• ✅ ORDER BY/GROUP BY 字段是否有索引？

• ✅ 复合索引字段顺序是否最优？

• ✅ 是否使用覆盖索引？

• ✅ 索引选择性是否足够高？

查询优化检查项：

• ✅ 是否避免 SELECT *？

• ✅ 是否使用 LIMIT 限制返回行数？

• ✅ JOIN 条件是否有索引？

• ✅ 是否避免在 WHERE 中使用函数？

• ✅ 是否合理使用 UNION 替代 OR？

🛠️ 整体优化方案对比

优化维度	基础方案	进阶方案	高级方案	选择建议
索引策略	单列索引	复合索引	覆盖索引	根据查询频率和字段选择
查询优化	减少 SELECT *	使用 LIMIT	分页优化	结合业务需求逐步优化
JOIN 优化	添加关联索引	调整 JOIN 顺序	分阶段查询	根据数据量和复杂度选择
架构优化	查询优化	读写分离	分库分表	按业务增长阶段实施

🔧 性能监控工具对比

分析工具	使用方式	输出内容	适用阶段	学习成本
EXPLAIN	EXPLAIN SELECT …	执行计划详情	日常开发	低
EXPLAIN ANALYZE	EXPLAIN ANALYZE SELECT …	实际执行统计	性能调优	中
慢查询日志	配置 slow_query_log	慢 SQL 记录	问题排查	中
Performance Schema	查询系统表	详细性能指标	深度优化	高

🚀 进阶优化策略

读写分离架构：

应用层 -> （写主库 - 读从库1 - 读从库2 - 读从库3） -> 主库

分库分表策略：

垂直分表：将大字段分离到扩展表

水平分表：按时间或ID范围拆分

分库：按业务模块分离

📚 持续学习路径

推荐学习资源：

• MySQL 官方文档：EXPLAIN 输出详解

• Percona Toolkit：性能分析工具

• sys Schema：MySQL 性能监控视图

• 数据库设计范式：从源头避免性能问题