Skip to content
炼金塔

慢查询优化实战

面试开场白

Section titled “面试开场白”

面试官：生产环境出现慢查询，你是如何排查和优化的？

你：先开启慢查询日志，用 mysqldumpslow 或 pt-query-digest 分析 Top N 慢查询，再用 EXPLAIN 分析执行计划，识别全表扫描、filesort 等问题，最后通过索引优化、SQL 改写等手段解决。

面试官：能具体说说 EXPLAIN 的关键字段怎么看吗？type 字段的含义？

这个追问考察你的实战经验。能说清「type 从好到坏排序」和「Extra 字段含义」的候选人，才是真正做过优化的。

链式追问一：慢查询定位与分析

Section titled “链式追问一：慢查询定位与分析”

Q1：如何发现生产环境的慢查询？必考

Section titled “Q1：如何发现生产环境的慢查询？”

步骤一：开启慢查询日志

-- 查看当前配置
SHOW VARIABLES LIKE 'slow_query%';
SHOW VARIABLES LIKE 'long_query_time';


-- 开启慢查询（临时，重启失效）
SET GLOBAL slow_query_log = ON;
SET GLOBAL long_query_time = 1;  -- 超过 1s 的查询记录到慢查询日志
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
min_examined_row_limit = 100  -- 扫描行数 <100 不记录（避免记录小查询）

步骤二：分析慢查询日志

Terminal window
# mysqldumpslow：官方慢查询分析工具
mysqldumpslow -s t -n 10 /var/log/mysql/slow.log
# -s t：按查询时间排序
# -s c：按查询次数排序
# -s l：按锁定时间排序
# -n 10：显示前 10 条


# pt-query-digest（percona 工具，功能更强）
pt-query-digest /var/log/mysql/slow.log | head -100


# 输出示例：
# Rank      Query ID           Response time   Calls R/Call  Item
# ====      =======            =============   ===== =======  ====
#    1 0x1234567890ABCDEF  120.00 85.7%    1000  0.1200 SELECT orders
#    2 0xFEDCBA0987654321   15.00 10.7%     500  0.0300 SELECT users
#
# 解读：查询 1 占总响应时间 85.7%，平均每次 0.12s，需要优化

步骤三：实时监控慢查询

-- 查看当前正在执行的慢查询
SELECT * FROM information_schema.processlist
WHERE TIME > 1  -- 执行时间 > 1s
ORDER BY TIME DESC;


-- 查看锁等待
SELECT
    r.trx_id waiting_trx_id,
    r.trx_mysql_thread_id waiting_thread,
    r.trx_query waiting_query,
    b.trx_id blocking_trx_id,
    b.trx_mysql_thread_id blocking_thread,
    b.trx_query blocking_query
FROM information_schema.innodb_lock_waits w
INNER JOIN information_schema.innodb_trx b ON b.trx_id = w.blocking_trx_id
INNER JOIN information_schema.innodb_trx r ON r.trx_id = w.requesting_trx_id;

Q2：EXPLAIN 的关键字段如何解读？必考

Section titled “Q2：EXPLAIN 的关键字段如何解读？”
EXPLAIN SELECT * FROM orders o
JOIN users u ON o.user_id = u.id
WHERE o.status = 'PENDING' AND u.city = 'Beijing';

EXPLAIN 输出关键字段：

字段含义重点关注值
id查询标识符ID 相同从上往下执行；ID 不同大的先执行
select_type查询类型SIMPLE（简单查询）、SUBQUERY（子查询）、DERIVED（派生表）
table访问的表-
type访问类型const > eq_ref > ref > range > index > ALL
key实际使用的索引NULL 表示未使用索引
key_len索引长度越短越好（但要看具体场景）
rows预计扫描行数越小越好
filtered过滤后行比例100% 最好（索引过滤效率高）
Extra额外信息Using index（好）/ Using filesort（坏）/ Using temporary（坏）

type 类型从好到坏：

system    → 单行系统表（如 SELECT * FROM dual）
const     → 主键/唯一索引精确查询，最多 1 行
            例：SELECT * FROM users WHERE id = 123


eq_ref    → JOIN 时使用主键/唯一索引，每次 JOIN 最多 1 行
            例：JOIN users ON orders.user_id = users.id


ref       → 普通索引等值查询，多行
            例：SELECT * FROM orders WHERE user_id = 123


range     → 索引范围查询（BETWEEN, >, <, IN）
            例：SELECT * FROM orders WHERE id > 100 AND id < 200


index     → 全索引扫描（扫描整个索引树，比 ALL 好，但仍然慢）
            例：SELECT id FROM orders（id 是主键，只扫描索引）


ALL       → 全表扫描（!!!必须优化!!!)
            例：SELECT * FROM orders WHERE status = 'PENDING'（status 无索引）

Extra 字段常见值：

Extra 值含义性能影响
Using index覆盖索引，不需要回表✅ 好
Using where用 WHERE 条件过滤⚠️ 中性
Using index condition索引下推（ICP）✅ 好（减少回表）
Using filesort额外排序（内存或磁盘）❌ 差
Using temporary使用临时表❌ 差
Using join bufferJOIN 使用缓冲区⚠️ 中性（可能缺少索引）

示例分析：

-- 慢查询
EXPLAIN SELECT * FROM orders WHERE status = 'PENDING' ORDER BY create_time DESC LIMIT 10;


+----+-------------+--------+------------+------+---------------+------+---------+------+--------+----------+-----------------------------+
| id | select_type | table  | partitions | type | possible_keys | key  | key_len | ref  | rows   | filtered | Extra                       |
+----+-------------+--------+------------+------+---------------+------+---------+------+--------+----------+-----------------------------+
|  1 | SIMPLE      | orders | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 500000 |    10.00 | Using where; Using filesort |
+----+-------------+--------+------------+------+---------------+------+---------+------+--------+----------+-----------------------------+


问题诊断：
  1. type=ALL：全表扫描（扫描 50 万行）
  2. key=NULL：未使用索引
  3. rows=500000：扫描行数极大
  4. Extra=Using filesort：额外排序


优化方案：
  CREATE INDEX idx_status_time ON orders(status, create_time DESC);

本质一句话：EXPLAIN 的核心是看「type 是否接近 const/ref」、「key 是否使用了索引」、「rows 是否合理」、「Extra 是否有 filesort/temporary」。

链式追问二：索引优化策略

Section titled “链式追问二：索引优化策略”

Q3：索引优化的核心思路？联合索引如何设计？必考

Section titled “Q3：索引优化的核心思路？联合索引如何设计？”

索引优化三原则：

1. 针对高频查询建索引
   - 分析慢查询日志，找出高频查询模式
   - 按 WHERE + ORDER BY + GROUP BY 的字段建联合索引


2. 遵循最左前缀原则
   - 联合索引 (a, b, c)：可以用 a | a,b | a,b,c，不能用 b | c | b,c


3. 覆盖索引优先
   - SELECT 的列都在索引中 → 不需要回表 → 性能提升 50%-80%

联合索引设计实战：

-- 查询模式（高频查询）：
-- WHERE user_id = ? AND status = ? ORDER BY create_time DESC LIMIT 10


-- 设计联合索引
ALTER TABLE orders ADD INDEX idx_uid_status_time(user_id, status, create_time DESC);


索引结构（B+树）：
  (user_id=1, status='PENDING', create_time='2024-01-01') → 行指针
  (user_id=1, status='PENDING', create_time='2024-01-02') → 行指针
  (user_id=1, status='PAID', create_time='2024-01-01') → 行指针
  ...


查询流程：
  1. 按 user_id=1 定位（索引第一列）
  2. 按 status='PENDING' 过滤（索引第二列）
  3. 按 create_time DESC 排序（索引第三列，已有序，无需 filesort）
  4. LIMIT 10 取前 10 条


EXPLAIN 结果：
  type: ref
  key: idx_uid_status_time
  Extra: Using index condition  -- 无 filesort！

最左前缀原则示例：

-- 联合索引：idx_abc(a, b, c)


-- ✅ 能用索引
WHERE a = 1                        -- 用 a
WHERE a = 1 AND b = 2              -- 用 a, b
WHERE a = 1 AND b = 2 AND c = 3    -- 用 a, b, c
WHERE a = 1 AND c = 3              -- 用 a（b 缺失，c 无法用）


-- ❌ 不能用索引
WHERE b = 2                        -- 缺少最左列 a
WHERE c = 3                        -- 缺少最左列 a
WHERE b = 2 AND c = 3              -- 缺少最左列 a

覆盖索引实战：

-- 不好：需要回表查询完整记录
SELECT * FROM orders WHERE user_id = 123;
-- 扫描 user_id 索引 → 找到主键 → 回表查询完整数据


-- 好：只查需要的列，如果联合索引覆盖了这些列，不需要回表
SELECT id, status, amount FROM orders WHERE user_id = 123;
-- 假设有索引 idx_uid_status_amount(user_id, status, amount)
-- 直接从索引获取数据，不需要回表
-- EXPLAIN 中 Extra 显示 Using index（覆盖索引）


性能对比：
  回表查询：10ms
  覆盖索引：2ms（提升 80%）

Q4：索引失效的常见场景有哪些？必考

Section titled “Q4：索引失效的常见场景有哪些？”

索引失效场景汇总：

场景示例原因解决方案
对索引列做函数/运算WHERE DATE(create_time) = '2024-01-01'索引存的是原始值，函数处理后无法匹配改写为范围查询
LIKE 前缀通配符WHERE name LIKE '%张'前缀不确定，无法用索引用后缀通配符 '张%'
隐式类型转换WHERE phone = 13800138000（phone 是 VARCHAR）隐式转换相当于 CAST(phone AS SIGNED)用字符串 '13800138000'
OR 的一侧无索引WHERE id = 1 OR name = 'test'（name 无索引）OR 要求两边都能用索引，否则全表扫描给 name 建索引
!= / NOT INWHERE status != 'CANCELLED'不等于查询无法用索引定位改写为 IN
IS NULLWHERE name IS NULLNULL 值不存索引（部分引擎）默认值代替 NULL

失效场景详解：

-- 场景 1：对索引列做函数/运算
-- ❌ 索引失效
WHERE DATE(create_time) = '2024-01-01'


-- ✅ 改写为范围查询
WHERE create_time >= '2024-01-01' AND create_time < '2024-01-02'


-- ❌ 索引失效
WHERE amount + 10 > 100


-- ✅ 改写
WHERE amount > 90


-- 场景 2：LIKE 前缀通配符
-- ❌ 索引失效
WHERE name LIKE '%张'    -- 前缀不确定
WHERE name LIKE '%张%'   -- 前缀不确定


-- ✅ 索引生效
WHERE name LIKE '张%'    -- 前缀确定，可以用索引


-- 场景 3：隐式类型转换
-- phone 是 VARCHAR(11)
-- ❌ 索引失效（隐式转换：CAST(phone AS SIGNED)）
WHERE phone = 13800138000


-- ✅ 索引生效
WHERE phone = '13800138000'


-- 场景 4：OR 的一侧无索引
-- name 列无索引
-- ❌ 全表扫描
WHERE id = 1 OR name = 'test'


-- ✅ 改写为 UNION
SELECT * FROM users WHERE id = 1
UNION
SELECT * FROM users WHERE name = 'test';


-- 场景 5：!= / NOT IN
-- ❌ 通常全表扫描
WHERE status != 'CANCELLED'


-- ✅ 改写为 IN
WHERE status IN ('PENDING', 'PAID', 'SHIPPED')

验证索引是否生效：

-- 用 EXPLAIN 检查
EXPLAIN SELECT * FROM orders WHERE DATE(create_time) = '2024-01-01';


-- type=ALL, key=NULL → 索引失效
-- type=ref/range, key=idx_xxx → 索引生效

链式追问三：深分页与复杂查询优化

Section titled “链式追问三：深分页与复杂查询优化”

Q5：大表分页优化？深分页如何处理？必考

Section titled “Q5：大表分页优化？深分页如何处理？”

深分页问题：

-- 问题：OFFSET 10000000 需要扫描并丢弃 1000万行，极慢！
SELECT * FROM orders ORDER BY id LIMIT 10000000, 10;


-- EXPLAIN 会看到：type=ALL 或 index，rows 极大
-- 执行流程：
--   1. 扫描前 10000010 行
--   2. 丢弃前 10000000 行
--   3. 返回后 10 行
-- 时间：10-30 秒

优化方案对比：

方案原理优点缺点适用场景
游标分页记住上一页最后的 ID性能最好；无 OFFSET不能跳页列表滚动加载
延迟关联先查 ID，再关联查完整数据减少回表次数仍然有 OFFSET允许跳页
业务限制禁止深分页从根本上解决问题用户体验差大多数场景
ES 辅助用 Elasticsearch 分页性能好；功能强大引入新组件；数据同步搜索场景

方案一：游标分页（推荐）

-- 记住上一页最后一条记录的 ID
-- 下一页：id > last_id（利用主键索引）
SELECT * FROM orders WHERE id > 10000000 ORDER BY id LIMIT 10;


-- EXPLAIN：type=range, key=PRIMARY, Extra=Using index condition
-- 性能：扫描 10 行，<10ms


-- 前端实现：
-- 第一次请求：/api/orders?limit=10
-- 返回：{ data: [...], lastId: 10000010 }
-- 下一页请求：/api/orders?lastId=10000010&limit=10

方案二：延迟关联

-- 先用覆盖索引找 ID（不回表）
-- 再用 ID 查完整数据（只 10 行，精确回表）
SELECT o.* FROM orders o
JOIN (
    SELECT id FROM orders ORDER BY id LIMIT 10000000, 10
) t ON o.id = t.id;


-- 执行流程：
--   1. 子查询：SELECT id FROM orders ORDER BY id LIMIT 10000000, 10
--      → 走覆盖索引（只扫描索引树，不回表）
--      → 返回 10 个 ID
--   2. 外查询：SELECT * FROM orders WHERE id IN (...)
--      → 精确回表 10 次


-- 性能：比直接 OFFSET 快 5-10 倍

方案三：业务限制

-- 禁止访问 100 页以后的数据
SELECT * FROM orders ORDER BY id LIMIT 10 OFFSET 990;  -- 第 100 页


-- 大多数用户不会翻 100 页，可以接受
-- 如果必须深分页，提供搜索功能（用 ES）

方案四：ES 辅助分页

// Elasticsearch 分页（from + size 或 search_after）
SearchRequest request = new SearchRequest("orders");
request.source()
    .query(QueryBuilders.matchAllQuery())
    .from(10000000)  // ES 深分页也会慢，但比 MySQL 快
    .size(10);


// 推荐：search_after（类似游标分页）
request.source()
    .query(QueryBuilders.matchAllQuery())
    .searchAfter(new Object[]{lastSortValue})  // 上一页最后的排序值
    .size(10);

Q6：如何优化 ORDER BY 产生的 filesort？高频

Section titled “Q6：如何优化 ORDER BY 产生的 filesort？”

filesort 问题：

-- 慢查询原因：Extra: Using filesort
SELECT * FROM orders WHERE user_id = 123 ORDER BY create_time DESC LIMIT 10;


-- 执行流程：
--   1. 用 user_id 索引找到所有 user_id=123 的行（假设 1000 行）
--   2. 对这 1000 行按 create_time 排序（内存或磁盘排序）
--   3. 取前 10 行返回


-- 问题：排序消耗内存和 CPU，如果内存不够，用磁盘排序（极慢）

优化方案：建联合索引，让索引的顺序与 ORDER BY 一致：

-- 建联合索引（user_id 过滤 + create_time 排序）
CREATE INDEX idx_uid_time ON orders(user_id, create_time DESC);


-- 查询走索引顺序，无需 filesort
-- EXPLAIN: Extra: Using index condition（无 filesort！）


-- 执行流程：
--   1. 用索引 (user_id=123, create_time DESC) 直接定位
--   2. 索引已按 create_time DESC 排序，直接取前 10 行
--   3. 无需额外排序


-- 性能对比：
--   filesort：100ms
--   索引排序：10ms（提升 90%）

复杂 ORDER BY 优化：

-- 场景：多列排序
SELECT * FROM orders
WHERE user_id = 123
ORDER BY status ASC, create_time DESC
LIMIT 10;


-- 索引设计
CREATE INDEX idx_uid_status_time ON orders(user_id, status ASC, create_time DESC);


-- 注意：MySQL 8.0+ 支持「降序索引」，可以精确匹配 ORDER BY status ASC, create_time DESC
-- MySQL 5.7 及以下：索引只能全部 ASC 或全部 DESC，需要权衡

Q7：GROUP BY 优化？临时表问题如何解决？高频

Section titled “Q7：GROUP BY 优化？临时表问题如何解决？”

GROUP BY 产生临时表：

-- 慢查询原因：Extra: Using temporary
SELECT status, COUNT(*) FROM orders GROUP BY status;


-- 执行流程：
--   1. 扫描全表
--   2. 创建临时表，按 status 分组聚合
--   3. 返回结果


-- 问题：临时表消耗内存，如果内存不够，用磁盘临时表（极慢）

优化方案：用索引避免临时表：

-- 建索引（status 列）
CREATE INDEX idx_status ON orders(status);


-- 查询走索引，无需临时表
-- EXPLAIN: Extra: Using index（覆盖索引 + 索引分组）


-- 执行流程：
--   1. 扫描 status 索引（已按 status 分组）
--   2. 直接统计每个 status 的数量（无需临时表）
--   3. 返回结果


-- 性能对比：
--   临时表：500ms
--   索引分组：50ms（提升 90%）

复杂 GROUP BY 优化：

-- 场景：多列分组
SELECT user_id, status, COUNT(*)
FROM orders
GROUP BY user_id, status;


-- 索引设计
CREATE INDEX idx_uid_status ON orders(user_id, status);


-- 索引顺序与 GROUP BY 顺序一致 → 避免临时表

GROUP BY + ORDER BY 优化：

-- 场景：分组后排序
SELECT user_id, COUNT(*) as cnt
FROM orders
GROUP BY user_id
ORDER BY cnt DESC
LIMIT 10;


-- 问题：COUNT(*) 是聚合结果，无法用索引排序
-- 解决方案：接受 filesort（临时表 + 排序）


-- 优化方向：减少分组的数据量
-- 方法：先过滤再分组
SELECT user_id, COUNT(*) as cnt
FROM orders
WHERE create_time >= '2024-01-01'  -- 先过滤
GROUP BY user_id
ORDER BY cnt DESC
LIMIT 10;


-- 建索引：(create_time, user_id)

链式追问四：实战案例分析

Section titled “链式追问四：实战案例分析”

Q8：实际案例：电商订单表慢查询优化全过程实战

Section titled “Q8：实际案例：电商订单表慢查询优化全过程”

问题背景：

表结构：
  orders 表，5000 万行，50GB
  字段：id, user_id, order_no, status, amount, create_time, ...


慢查询告警：
  查询用户订单列表，P99 延迟 5s，超时告警


查询 SQL：
  SELECT * FROM orders
  WHERE user_id = 123
  ORDER BY create_time DESC
  LIMIT 20;

步骤一：EXPLAIN 分析：

EXPLAIN SELECT * FROM orders
WHERE user_id = 123
ORDER BY create_time DESC
LIMIT 20;


+----+-------------+--------+------------+------+---------------+------+---------+------+--------+----------+-----------------------------+
| id | select_type | table  | partitions | type | possible_keys | key  | key_len | ref  | rows   | filtered | Extra                       |
+----+-------------+--------+------------+------+---------------+------+---------+------+--------+----------+-----------------------------+
|  1 | SIMPLE      | orders | NULL       | ref  | idx_user_id   | idx_user_id | 8   | const | 50000  |   100.00 | Using filesort              |
+----+-------------+--------+------------+------+---------------+------+---------+------+--------+----------+-----------------------------+


问题诊断：
  1. type=ref：用了 idx_user_id 索引
  2. rows=50000：扫描 5 万行（该用户有 5 万订单）
  3. Extra=Using filesort：对 5 万行排序

步骤二：优化方案：

-- 方案：建联合索引 (user_id, create_time DESC)
CREATE INDEX idx_uid_time ON orders(user_id, create_time DESC);


-- 再次 EXPLAIN
EXPLAIN SELECT * FROM orders
WHERE user_id = 123
ORDER BY create_time DESC
LIMIT 20;


+----+-------------+--------+------------+------+---------------+--------------+---------+-------+------+----------+-------+
| id | select_type | table  | partitions | type | possible_keys | key          | key_len | ref   | rows | filtered | Extra |
+----+-------------+--------+------------+------+---------------+--------------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | orders | NULL       | ref  | idx_uid_time  | idx_uid_time | 8       | const |   20 |   100.00 | NULL  |
+----+-------------+--------+------------+------+---------------+--------------+---------+-------+------+----------+-------+


改进：
  1. rows=20：只扫描 20 行（从 5 万降到 20）
  2. Extra=NULL：无 filesort

步骤三：性能验证：

优化前：
  扫描行数：50000
  执行时间：5000ms（P99）


优化后：
  扫描行数：20
  执行时间：20ms（P99）


性能提升：250 倍

步骤四：索引覆盖进一步优化：

-- 如果前端只需要部分字段
SELECT id, order_no, status, amount, create_time
FROM orders
WHERE user_id = 123
ORDER BY create_time DESC
LIMIT 20;


-- 建覆盖索引
CREATE INDEX idx_uid_time_cover ON orders(user_id, create_time DESC, order_no, status, amount);


-- EXPLAIN: Extra: Using index（覆盖索引，无需回表）


-- 性能：
--   回表查询：20ms
--   覆盖索引：5ms（提升 75%）

总结：

优化流程：
  1. 定位慢查询（慢查询日志 + pt-query-digest）
  2. EXPLAIN 分析执行计划（type, key, rows, Extra）
  3. 诊断问题（全表扫描、filesort、回表过多）
  4. 建合适的索引（联合索引、覆盖索引）
  5. 验证优化效果（EXPLAIN + 压测）
  6. 上线监控（慢查询日志 + Prometheus）

高频面试题总结

Section titled “高频面试题总结”