SQL 优化实战
约 1244 字大约 4 分钟
布欧-Lewyon
2026-05-15
首页 › MySQL › 索引与性能（在新窗口打开） › SQL 优化实战
前面三节（索引基础、索引策略、EXPLAIN）已打下理论基础，本节通过具体案例展示 SQL 优化的常见模式和最佳实践。
ORDER BY 优化
文件排序 vs 索引排序
-- ❌ 文件排序（salary 无索引）
EXPLAIN SELECT * FROM employee ORDER BY salary DESC;
-- Extra: Using filesort
-- ✅ 索引排序（给 salary 建索引，order by 可走索引）
CREATE INDEX idx_salary ON employee(salary);
EXPLAIN SELECT * FROM employee ORDER BY salary DESC;
-- Extra: (无)文件排序的代价：MySQL 需要将数据先读到内存（或在磁盘上临时文件）中排序，当结果集很大时消耗大量 CPU 和内存。
多列排序需要符合最左前缀
-- 复合索引 (dept_id, salary)
-- ✅ 索引排序（ORDER BY 列与索引一致）
SELECT * FROM employee ORDER BY dept_id, salary;
-- ❌ 文件排序（跳过了中间列）
SELECT * FROM employee ORDER BY dept_id, age;
-- ❌ 文件排序（方向不一致）
SELECT * FROM employee ORDER BY dept_id ASC, salary DESC;GROUP BY 优化
GROUP BY 本质上也是排序，索引优化同样适用：
-- ❌ Using temporary + filesort
EXPLAIN SELECT dept_id, COUNT(*) FROM employee GROUP BY dept_id;
-- Extra: Using temporary; Using filesort
-- ✅ 建索引后消除临时表和文件排序
CREATE INDEX idx_dept ON employee(dept_id);
EXPLAIN SELECT dept_id, COUNT(*) FROM employee GROUP BY dept_id;
-- Extra: Using index只聚合不计较顺序
如果 GROUP BY 的结果不需要排序：
-- 加上 ORDER BY NULL 避免额外排序
SELECT dept_id, COUNT(*) FROM employee
GROUP BY dept_id
ORDER BY NULL;8.0 优化器已能识别，但在某些场景下仍有作用。
分页优化
大偏移量问题
-- ❌ 传统分页，OFFSET 越大越慢
SELECT * FROM order_
ORDER BY id
LIMIT 100000, 20;
-- MySQL 会扫描 100020 行，丢弃前 100000 行方案一：延迟关联
SELECT o.*
FROM order_ o
INNER JOIN (
SELECT id
FROM order_
ORDER BY id
LIMIT 100000, 20
) tmp ON o.id = tmp.id;
-- 子查询只走索引（id 主键），快得多方案二：游标分页（推荐）
-- 第一次查询：返回最后一条的 id
SELECT * FROM order_ ORDER BY id LIMIT 20;
-- 客户端记录最后一条的 id = 20
-- 下一页：WHERE id > 20 代替 OFFSET
SELECT * FROM order_ WHERE id > 20 ORDER BY id LIMIT 20;
-- 最后一页 id = 40
-- 再下一页
SELECT * FROM order_ WHERE id > 40 ORDER BY id LIMIT 20;游标分页优缺点：
| 优点 | 缺点 |
|---|---|
| 固定性能（无论第几页） | 无法跳转到任意页码 |
| 不走全表扫描 | 需要唯一有序的游标列 |
| 适合无限滚动/Timeline | 不适合传统页码导航 |
避免 SELECT *
-- ❌ 返回所有列，无法利用覆盖索引
SELECT * FROM employee WHERE dept_id = 1;
-- ✅ 只取需要的列
SELECT id, name, salary FROM employee WHERE dept_id = 1;
-- 如果 (dept_id, name, salary) 是复合索引，Extra 为 Using indexEXISTS vs IN
-- 子查询结果集小 → IN 快
SELECT * FROM employee WHERE dept_id IN (1, 2, 3);
-- 子查询结果集大 → EXISTS 快（半连接短路）
SELECT * FROM employee e
WHERE EXISTS (SELECT 1 FROM large_table l WHERE l.id = e.dept_id);MySQL 8.0 的优化器在简单场景下会自动改写，但了解差异有助于写出更可预测的 SQL。
其他常用优化
避免在索引列上做运算
-- ❌ 函数包裹索引列
SELECT * FROM employee WHERE YEAR(join_date) = 2026;
-- ✅ 改为范围查询
SELECT * FROM employee
WHERE join_date >= '2026-01-01' AND join_date < '2027-01-01';使用 UNION ALL 代替 OR（某些场景）
-- OR：可能不走索引
SELECT * FROM employee WHERE dept_id = 1 OR salary = 22000;
-- UNION ALL：各自走索引
SELECT * FROM employee WHERE dept_id = 1
UNION ALL
SELECT * FROM employee WHERE salary = 22000;MySQL 8.0 的 Index Merge 优化已能处理某些 OR 场景，但 EXPLAIN 验证最保险。
批量操作
-- ❌ 逐条插入（1000 条需要 1000 次网络往返）
INSERT INTO user (name) VALUES ('a');
INSERT INTO user (name) VALUES ('b');
-- ...
-- ✅ 单次批量插入
INSERT INTO user (name) VALUES ('a'), ('b'), ... (1000 行);
-- ✅ 事务批量，减少提交开销
START TRANSACTION;
INSERT INTO user (name) VALUES (1);
INSERT INTO user (name) VALUES (2);
-- ...
COMMIT;OPTIMIZE TABLE 与 ANALYZE TABLE
-- 重建表和索引，回收碎片空间（大表较慢）
OPTIMIZE TABLE employee;
-- 更新索引统计信息，帮优化器做更好的决策
ANALYZE TABLE employee;-- 查看表碎片情况
SELECT
TABLE_NAME,
DATA_LENGTH,
INDEX_LENGTH,
DATA_FREE,
(DATA_FREE / (DATA_LENGTH + INDEX_LENGTH)) AS fragmentation_ratio
FROM information_schema.TABLES
WHERE TABLE_SCHEMA = 'demo' AND TABLE_NAME = 'employee';一般当碎片率超过 30~40% 时可考虑
OPTIMIZE。
真实优化案例流程
-- Step 1: 发现慢查询（来自慢日志）
-- Query_time: 5.2s
SELECT * FROM order_
WHERE status = 'PENDING'
ORDER BY create_time
LIMIT 20;
-- Step 2: EXPLAIN
-- type: ALL, rows: 500000, Extra: Using where; Using filesort
-- Step 3: 加索引
CREATE INDEX idx_status_create ON order_(status, create_time);
-- Step 4: 重新 EXPLAIN
-- type: ref, rows: 5000, Extra: Using index condition
-- 扫描行数从 50 万降到 5000
-- Step 5: 验证：查询时间从 5.2s 降到 0.03s小结
ORDER BY和GROUP BY尽量利用索引排序，避免Using filesort和Using temporary。- 大偏移量分页用延迟关联或游标分页替代传统
OFFSET。 - 避免
SELECT *、函数包裹索引列、OR无索引列等反模式。 OPTIMIZE TABLE回收碎片；ANALYZE TABLE更新统计信息。- 优化流程：慢日志 → EXPLAIN → 加索引/改写 → 验证。
上一节：慢查询日志 下一节：事务基础与 ACID