SQL 优化实战：从慢查询到执行计划

第一步：找到慢查询

开启慢查询日志，设定阈值（如 1 秒）：

SET GLOBAL slow_query_log = ON;
SET GLOBAL long_query_time = 1;

用 mysqldumpslow 或 pt-query-digest 聚合分析，先优化出现频次高、平均耗时最长的 Top SQL——不要凭感觉乱优化。

第二步：EXPLAIN 看执行计划

EXPLAIN SELECT * FROM posts WHERE category_id = 2 AND status = 'PUBLISHED';

重点看几列：

• type：访问类型，从好到差：system > const > eq_ref > ref > range > index > ALL（全表扫描，必须优化）
• key：实际使用的索引，NULL 表示没走索引
• rows：预估扫描行数，越大越糟
• Extra：出现 Using filesort（额外排序）、Using temporary（临时表，常见于 GROUP BY/DISTINCT）都是危险信号

第三步：建对索引

-- 联合索引遵循最左前缀原则
CREATE INDEX idx_status_category ON posts (status, category_id);
-- 查询条件按索引列顺序排列，范围查询列放最后，后面的列无法再用于索引

索引失效的常见写法：对字段用函数（YEAR(created_at)）、隐式类型转换、LIKE '%x'、OR 连接无索引列。

第四步：改写 SQL

只查需要的列：SELECT * 在大表和覆盖索引场景代价高，明确列出字段。

深分页优化——LIMIT 100000, 20 要扫描并丢弃前 10 万行：

-- 慢
SELECT * FROM posts ORDER BY id LIMIT 100000, 20;

-- 快：先用覆盖索引定位主键，再回表
SELECT * FROM posts
WHERE id >= (SELECT id FROM posts ORDER BY id LIMIT 100000, 1)
ORDER BY id LIMIT 20;
-- 或者记住上一页最大 id：WHERE id > last_id LIMIT 20（游标分页）

其他经验

• 批量写入用 INSERT INTO ... VALUES (...), (...), (...) 一次提交，配合事务
• JOIN 的关联字段必须都有索引，小表驱动大表
• COUNT 大表慢，可维护计数表或用估算值
• 先做结构和索引优化，最后才考虑缓存兜底；缓存会引入一致性问题

优化流程是科学的：测量 → 定位 → 改 → 再测量，而不是"加个索引试试"。