一句话总结
慢查询优化步骤：1）开启慢查询日志定位慢 SQL → 2）EXPLAIN 分析执行计划（type/rows/Extra）→ 3）加索引优化（覆盖索引+最左前缀）→ 4）SQL 改写（避免 SELECT *、深分页用延迟关联）→ 5）表结构优化（字段类型、范式/反范式）。核心口诀：先看 type（const > ref > range > ALL），再看 Extra（Using filesort/Using temporary 需处理）。
初级理解
开启慢查询日志
# 查看慢查询配置
SHOW VARIABLES LIKE 'slow_query%';
SHOW VARIABLES LIKE 'long_query_time';
# 开启慢查询日志
SET GLOBAL slow_query_log = ON;
SET GLOBAL long_query_time = 1; # 超过 1 秒记录
SET GLOBAL slow_query_log_file = '/var/log/mysql/slow.log';
# 记录未使用索引的查询
SET GLOBAL log_queries_not_using_indexes = ON;
# 永久配置（my.cnf）
[mysqld]
slow_query_log = 1
long_query_time = 1
slow_query_log_file = /var/log/mysql/slow.log
log_queries_not_using_indexes = 1
mysqldumpslow 分析慢日志
# 常用分析命令
mysqldumpslow -s t -t 10 slow.log # 按时间排序，显示前 10 条
mysqldumpslow -s c -t 10 slow.log # 按出现次数排序
mysqldumpslow -s r -t 10 slow.log # 按扫描行数排序
mysqldumpslow -g 'LEFT JOIN' slow.log # 过滤包含 LEFT JOIN 的 SQL
# 输出示例
# Count: 100 Time=2.50s (250s) Lock=0.00s Rows=50000.0 (5000000)
# SELECT * FROM orders WHERE status='N' ORDER BY create_time DESC;
# 解读：执行100次，平均2.5秒，平均扫描5万行
一句话总结：先开慢查询日志找到问题 SQL，再用 EXPLAIN 分析为什么慢。
中级深入
EXPLAIN 详解
EXPLAIN SELECT * FROM orders WHERE user_id = 100 AND status = 'paid';
# 输出解读（按重要性排序）：
# id: 查询序号（复杂查询可能有多个）
# select_type: SIMPLE（简单）/ PRIMARY（主查询）/ SUBQUERY（子查询）
# type: 访问类型（最重要！）
# system > const > eq_ref > ref > range > index > ALL
# const: 主键等值，1行（最优）
# eq_ref: 联表时唯一索引匹配，1行
# ref: 非唯一索引等值匹配
# range: 索引范围扫描（>,<,BETWEEN,IN）
# index: 全索引扫描（比ALL好一点）
# ALL: 全表扫描（最差，必须优化）
# possible_keys: 可能使用的索引
# key: 实际使用的索引
# key_len: 使用的索引长度
# ref: 与索引比较的列
# rows: 预估扫描行数
# filtered: 过滤后占比
# Extra: 额外信息（重点看！）
# Using index: 覆盖索引 ✅
# Using index condition: ICP 生效 ✅
# Using where: Server层过滤
# Using filesort: 文件排序 ❌（需优化）
# Using temporary: 临时表 ❌（需优化）
常见 SQL 优化技巧
# 1. SELECT * 改 SELECT 具体列
# ❌ SELECT * FROM users WHERE id = 1;
# ✅ SELECT id, name, email FROM users WHERE id = 1;
# 好处：减少网络传输 + 可能走覆盖索引
# 2. 分页优化（深分页）
# ❌ SELECT * FROM orders LIMIT 100000, 20; # 扫描10万+20行
# ✅ 延迟关联
SELECT o.* FROM orders o
INNER JOIN (
SELECT id FROM orders ORDER BY id LIMIT 100000, 20
) tmp ON o.id = tmp.id;
# ✅ 游标分页
SELECT * FROM orders WHERE id > 100000 ORDER BY id LIMIT 20;
# 3. IN 代替 OR
# ❌ SELECT * FROM users WHERE id = 1 OR id = 2 OR id = 3;
# ✅ SELECT * FROM users WHERE id IN (1, 2, 3);
# 4. UNION ALL 代替 UNION（不去重）
# ❌ SELECT name FROM users UNION SELECT name FROM admins;
# ✅ SELECT name FROM users UNION ALL SELECT name FROM admins;
# 5. 避免在 WHERE 子句中对字段进行函数操作
# ❌ SELECT * FROM users WHERE YEAR(create_time) = 2025;
# ✅ SELECT * FROM users WHERE create_time >= '2025-01-01' AND create_time < '2026-01-01';
# 6. JOIN 代替子查询
# ❌ SELECT * FROM orders WHERE user_id IN (SELECT id FROM users WHERE status=1);
# ✅ SELECT o.* FROM orders o INNER JOIN users u ON o.user_id=u.id WHERE u.status=1;
中级要点：EXPLAIN 先看 type（至少 range 以上），再看 Extra（消除 Using filesort/temporary），最后优化 rows。
高级拓展
JOIN 优化 — 小表驱动大表
# NLJ（Nested Loop Join）：驱动表每行去被驱动表匹配
# 原则：小表做驱动表，大表走索引
# 查看 JOIN 顺序
EXPLAIN SELECT * FROM users u
JOIN orders o ON u.id = o.user_id
WHERE u.status = 1;
# id 相同的行，从上到下执行
# 第一行是驱动表，第二行是被驱动表
# 被驱动表的关联字段必须有索引！
# BKA（Batched Key Access）：批量读取，减少 IO
# MySQL 8.0 默认开启
SET optimizer_switch='batched_key_access=on';
profiling 分析查询耗时
# 开启 profiling
SET profiling = 1;
# 执行查询
SELECT * FROM orders WHERE user_id = 100;
# 查看所有查询的耗时
SHOW PROFILES;
# 查看具体查询的详细耗时
SHOW PROFILE FOR QUERY 1;
# 输出示例：
# Status Duration
# starting 0.000042
# checking permissions 0.000005
# Opening tables 0.000012
# init 0.000015
# System lock 0.000006
# optimizing 0.000008
# statistics 0.000012
# preparing 0.000010
# executing 0.000005
# Sending data 2.500000 ← 瓶颈在这里！
# end 0.000005
# query end 0.000003
optimizer_trace — 查看优化器决策
# 开启优化器跟踪
SET optimizer_trace='enabled=on';
# 执行查询
SELECT * FROM orders WHERE user_id = 100 AND status = 'paid';
# 查看优化器决策过程
SELECT * FROM information_schema.optimizer_trace\G
# 关键信息：
# "rows_estimation": [...] # 索引选择评估
# "considered_execution_plans": [...] # 执行计划对比
# "attached_conditions_summary": [...] # 条件附加
SET optimizer_trace='enabled=off';
实战场景
场景：优化一个 5 秒慢查询
# 原始 SQL（5.2秒）
SELECT * FROM orders WHERE status = 'paid'
AND create_time >= '2025-01-01'
ORDER BY create_time DESC LIMIT 20;
# Step 1: EXPLAIN 分析
EXPLAIN SELECT * FROM orders WHERE status = 'paid'
AND create_time >= '2025-01-01'
ORDER BY create_time DESC LIMIT 20;
# type: ALL, rows: 5000000, Extra: Using where; Using filesort
# Step 2: 问题诊断
# - type=ALL 全表扫描，500万行
# - Using filesort 文件排序，额外开销
# Step 3: 创建联合索引
ALTER TABLE orders ADD INDEX idx_status_time(status, create_time);
# Step 4: 验证效果
EXPLAIN SELECT * FROM orders WHERE status = 'paid'
AND create_time >= '2025-01-01'
ORDER BY create_time DESC LIMIT 20;
# type: range, rows: 50000, Extra: Using index condition
# 查询时间：5.2s → 0.03s
# Step 5: 如果仍慢，考虑覆盖索引
# 只查需要的列
SELECT id, user_id, amount, create_time FROM orders
WHERE status = 'paid' AND create_time >= '2025-01-01'
ORDER BY create_time DESC LIMIT 20;
面试模拟
面试官：线上一个查询突然变慢，你怎么排查？
你：1）先看慢查询日志，确认是哪个 SQL 变慢；2）EXPLAIN 看执行计划，对比之前的执行计划是否变化（索引是否失效、是否走了不同的索引）；3）检查数据量是否暴增、统计信息是否过期（ANALYZE TABLE 更新）；4）检查是否有锁等待（SHOW PROCESSLIST 看锁状态）；5）检查服务器资源（CPU/IO/内存）。
面试官：深分页为什么慢？怎么优化？
你：LIMIT 100000,20 需要扫描前 100020 行再丢弃前 100000 行，数据量越大越慢。优化方案：1）延迟关联——子查询只取 ID 走覆盖索引，外层回表只查 20 行；2）游标分页——记录上一页最后一条的 ID，用 WHERE id > last_id LIMIT 20。
面试官：EXPLAIN 中 type 有哪些？从好到差排序。
你：system > const > eq_ref > ref > range > index > ALL。const 是主键等值查询最优；eq_ref 是联表唯一索引；ref 是非唯一索引；range 是索引范围；index 是全索引扫描；ALL 是全表扫描最差。至少要优化到 range 级别。