SQL优化实战：从慢查询到高性能的5个核心技能

共计 2203 个字符，预计需要花费 6 分钟才能阅读完成。

开篇痛点：为什么你的 SQL 这么慢？

当数据量超过百万级别后，很多开发者都会遇到这样的场景：

• 页面加载需要 5 秒以上，EXPLAIN 显示全表扫描（type=ALL）
• 循环执行大量相似查询（典型的 N + 1 问题）
• 复杂联表查询卡死整个数据库连接池

最近我们系统中就有一个报表查询，300 万数据量下执行需要 8 秒。通过后续介绍的优化方法，最终将其降到了 0.7 秒。下面分享 5 个经过实战验证的核心优化技能。

技能一：索引设计与优化

B+ 树原理速览

MySQL 索引底层是 B + 树结构，它的特点：

• 非叶子节点只存键值和指针（类似目录页）
• 叶子节点形成有序链表（范围查询高效）
• 通常 3 层 B + 树可支持千万级数据

最佳实践案例

问题 SQL：

SELECT * FROM orders WHERE user_id = 100 AND status = 'completed';

优化步骤：

1. 确认现有索引：

   SHOW INDEX FROM orders; -- 发现只有主键 id 索引

2. 创建联合索引：

   ALTER TABLE orders ADD INDEX idx_user_status (user_id, status);

3. 验证索引效果：

   EXPLAIN SELECT * FROM orders WHERE user_id = 100 AND status = 'completed';
   -- 输出显示 type=ref,key=idx_user_status

避坑指南：

• 避免在频繁更新的列建索引
• 不要建 (status,user_id) 这样低选择性的组合

技能二：查询语句重构

典型改造案例

原始查询（执行 2.4s）:

SELECT * FROM products 
WHERE category IN (SELECT id FROM categories WHERE name LIKE '% 电子 %')
AND price > 1000;

优化版本（0.3s）:

SELECT p.* FROM products p
JOIN categories c ON p.category = c.id
WHERE c.name LIKE '% 电子 %' AND p.price > 1000;

优化点分析：

1. 用 JOIN 替代 IN 子查询（避免多次执行）
2. 确保 category 和 price 字段都有索引

技能三：执行计划解读

EXPLAIN 关键指标

+----+-------------+-------+------------+------+---------------+-----+---------+-----+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+-----+---------+-----+------+----------+-------------+
| 1  | SIMPLE      | users | NULL       | ref  | idx_name      | ... | 1023    | const | 1    | 100.00   | Using index |
+----+-------------+-------+------------+------+---------------+-----+---------+-----+------+----------+-------------+

重点关注：

• type 列：最好到 range 级别以上
• rows 列：扫描行数越少越好
• Extra 列：出现 ”Using filesort” 就需要警惕

技能四：参数调优

MySQL 核心参数

# my.cnf 关键配置
innodb_buffer_pool_size = 4G  # 建议设为可用内存的 70%
innodb_log_file_size = 256M
query_cache_type = 0          # 高并发环境建议关闭

调整效果验证：

SHOW STATUS LIKE 'Innodb_buffer_pool_read%';
-- 观察命中率是否提升

技能五：分库分表策略

何时需要考虑

• 单表数据超过 500 万条
• 高频访问的热点表

实施示例

-- 按 user_id 哈希分表
CREATE TABLE orders_0 LIKE orders;
CREATE TABLE orders_1 LIKE orders;
-- 插入时路由
INSERT INTO orders_{user_id % 2} VALUES (...);

性能验证报告

优化前后对比数据：

避坑指南

常见错误

1. 盲目添加索引导致写入性能下降
2. 在 WHERE 条件使用函数导致索引失效

   -- 反例：SELECT * FROM users WHERE DATE(create_time) = '2023-01-01';

版本差异注意

• MySQL 5.6+ 支持 ICP（索引条件下推）
• 8.0+ 支持窗口函数，可替代部分复杂子查询

实践思考题

1. 你的系统中最慢的 3 个 SQL 是什么？它们的 EXPLAIN 结果如何？
2. 尝试对一个频繁查询的字段添加索引，观察 QPS 变化
3. 如何监控生产环境的慢查询趋势？

推荐进一步学习：

• 《高性能 MySQL》第 6 章
• MySQL 官方文档的 Optimization 章节
• Percona 博客的案例研究

优化没有银弹，需要结合业务特点持续迭代。建议每周花 1 小时专项优化，长期积累效果显著。