常见的慢 SQL 优化案例

以下是一些常见的慢 SQL 优化示例，涵盖索引优化、查询结构调整、分页优化等场景。每个示例包含优化前的低效 SQL 和优化后的高效 SQL，并附带简要说明。

一、索引优化类

1. 函数导致的索引失效

优化前（对索引列使用函数）：

-- 假设order_time有索引
SELECT * FROM orders WHERE YEAR(order_time) = 2023;

 

优化后（避免函数，直接比较范围）：

SELECT * FROM orders WHERE order_time >= '2023-01-01' AND order_time < '2024-01-01';

 

2. LIKE 非前缀匹配

优化前（%在开头导致索引失效）：

-- 假设product_name有索引
SELECT * FROM products WHERE product_name LIKE '%phone';

 

优化后（改为前缀匹配）：

SELECT * FROM products WHERE product_name LIKE 'phone%';

 

3. OR 条件索引失效

优化前（OR 连接无索引列）：

-- 假设user_id有索引，email无索引
SELECT * FROM users WHERE user_id = 100 OR email = 'test@example.com';

 

优化后（用 UNION 拆分查询）：

(SELECT * FROM users WHERE user_id = 100)
UNION
(SELECT * FROM users WHERE email = 'test@example.com');

 

4. 复合索引未遵循最左前缀

优化前（复合索引为(a, b, c)，但未使用最左列a）：

SELECT * FROM table WHERE b = 1 AND c = 2;

 

优化后（包含最左列）：

SELECT * FROM table WHERE a = 0 AND b = 1 AND c = 2;

 

二、查询结构优化类

1. 子查询优化为 JOIN

优化前（子查询嵌套）：

SELECT * FROM products WHERE category_id IN (SELECT id FROM categories WHERE parent_id = 1);

 

优化后（转换为 JOIN）：

SELECT p.* FROM products p 
JOIN categories c ON p.category_id = c.id 
WHERE c.parent_id = 1;

 

2. 多表 JOIN 顺序优化

优化前（大表在前，小表在后）：

SELECT * FROM orders o  -- orders表数据量大
JOIN users u ON o.user_id = u.id  -- users表数据量小
WHERE u.status = 'active';

 

优化后（小表驱动大表）：

SELECT * FROM users u 
JOIN orders o ON u.id = o.user_id 
WHERE u.status = 'active';

 

三、分页查询优化

1. 大偏移量分页

优化前（传统分页，偏移量越大越慢）：

SELECT * FROM logs ORDER BY id DESC LIMIT 100000, 10;

 

优化后（书签分页，利用覆盖索引）：

-- 先通过索引快速定位到起始ID
SELECT * FROM logs WHERE id > 100000 ORDER BY id DESC LIMIT 10;

 

2. 带过滤条件的分页

优化前（每次都全表扫描）：

SELECT * FROM products WHERE category = 'electronics' LIMIT 5000, 10;

 

优化后（先索引过滤，再关联主表）：

SELECT p.* FROM (
    SELECT id FROM products WHERE category = 'electronics' LIMIT 5000, 10
) AS t 
JOIN products p ON t.id = p.id;

 

四、聚合查询优化

1. 无索引的 GROUP BY

优化前（对无索引列分组）：

SELECT category_id, COUNT(*) FROM products GROUP BY category_id;

 

优化后（添加索引）：

CREATE INDEX idx_category ON products(category_id);

 

2. 聚合函数与索引覆盖

优化前（需回表查询）：

SELECT user_id, SUM(amount) FROM orders GROUP BY user_id;

 

优化后（创建覆盖索引）：

CREATE INDEX idx_user_amount ON orders(user_id, amount);

 

五、锁定优化

1. 悲观锁导致的长事务

优化前（长时间持有写锁）：

BEGIN;
SELECT * FROM stock WHERE product_id = 100 FOR UPDATE;
-- 执行其他业务逻辑（耗时操作）
COMMIT;

 

优化后（缩短锁持有时间）：

BEGIN;
SELECT * FROM stock WHERE product_id = 100 LIMIT 1 FOR UPDATE;
-- 仅执行必要的更新操作
UPDATE stock SET quantity = quantity - 1 WHERE product_id = 100;
COMMIT;

六、其他优化

1. 避免 SELECT *

优化前（查询所有字段）：

SELECT * FROM users WHERE status = 'active';

 

优化后（只取需要的字段）：

SELECT id, name, email FROM users WHERE status = 'active';

 

2. 批量操作替代循环

优化前（多次单行插入）：

INSERT INTO logs (message) VALUES ('log1');
INSERT INTO logs (message) VALUES ('log2');
INSERT INTO logs (message) VALUES ('log3');

 

优化后（批量插入）：

INSERT INTO logs (message) VALUES ('log1'), ('log2'), ('log3');

 

总结

优化慢 SQL 时，建议：

1. 先用 EXPLAIN 分析执行计划，确定索引是否生效
2. 优先优化索引，遵循最左前缀原则
3. 避免复杂子查询，多用 JOIN 和覆盖索引
4. 对大表分页，使用书签分页或覆盖索引
5. 定期分析表统计信息（ANALYZE TABLE）和重建索引

通过这些优化技巧，大多数慢 SQL 都能获得显著性能提升。