我的生态
🧭
Java 面试指南覆盖面试高频考点，助你从容应对技术面试

    慢查询日志与优化

    2024 年双十一前夜，订单服务的 P99 延迟从 50ms 飙升到 2s。监控告警响成一片，DBA 紧急介入排查。

    排查过程：慢查询日志显示，凌晨有一批 ETL 任务跑在高峰期，大量全表扫描的查询把 MySQL 打爆了。

    开发同学辩解："这个查询白天一直跑得好好的，只是今天加了个新字段才变慢的。"

    DBA 一看 EXPLAIN，发现这个查询没有命中索引，全表扫描了 5000 万行。

    这不是能力问题，是意识问题：没有慢查询日志的意识，没有 EXPLAIN 的习惯，没有索引设计的概念。今天这篇，把 MySQL 慢查询从根上讲透。

    一、慢查询日志配置 🔴

    1.1 开启慢查询日志

    -- 查看慢查询配置
SHOW VARIABLES LIKE 'slow_query%';
SHOW VARIABLES LIKE 'long_query_time';
SHOW VARIABLES LIKE 'log_queries_not_using_indexes';

-- 开启慢查询日志
SET GLOBAL slow_query_log = 'ON';

-- 设置慢查询阈值（秒）
SET GLOBAL long_query_time = 1;  -- 超过 1 秒记录

-- 开启记录未使用索引的查询
SET GLOBAL log_queries_not_using_indexes = 'ON';

-- 指定日志文件路径
SET GLOBAL slow_query_log_file = '/var/log/mysql/slow.log';

    1.2 持久化配置

    # my.cnf 配置文件
[mysqld]
slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow.log
long_query_time = 1
log_queries_not_using_indexes = 1
log_throttle_queries_not_using_indexes = 10  # 每秒最多记录 10 条未使用索引的查询

    1.3 慢查询日志内容

    -- Time: 2024-01-01T02:00:00.123456Z
-- User@Host: app[app] @ localhost []
-- Query_time: 3.524321  Lock_time: 0.000123 Rows_sent: 10  Rows_examined: 50000000
-- SET timestamp=1704064800;
SELECT * FROM orders WHERE status = 0 AND amount > 100 LIMIT 10;

    关键字段：

    • Query_time：查询耗时（秒）
    • Lock_time：锁等待时间
    • Rows_sent：返回行数
    • Rows_examined：扫描行数（核心指标）
    ⚠️

    Rows_examined 是判断查询效率的核心指标。Query_time 长但 Rows_examined 小的，说明服务器资源紧张（锁争用、连接数）；Query_time 长且 Rows_examined 大的，说明查询本身需要优化（缺索引）。

    二、慢查询分析方法 🟡

    2.1 使用 mysqldumpslow 分析日志

    # 统计查询次数最多的 Top 10
mysqldumpslow -s c -t 10 /var/log/mysql/slow.log

# 统计平均耗时最长的 Top 10
mysqldumpslow -s at -t 10 /var/log/mysql/slow.log

# 统计锁等待最长的 Top 10
mysqldumpslow -s l -t 10 /var/log/mysql/slow.log

# 查看包含 'orders' 表的慢查询
mysqldumpslow -g 'orders' /var/log/mysql/slow.log

    2.2 使用 pt-query-digest（推荐）

    # 安装
# yum install percona-toolkit

# 分析慢查询日志，输出到文件
pt-query-digest /var/log/mysql/slow.log > slow_report.txt

# 实时分析当前查询
pt-query-digest --processlist h=localhost

# 只分析某一时间段的查询
pt-query-digest --since='2024-01-01 00:00:00' --until='2024-01-01 06:00:00' slow.log

    2.3 EXPLAIN 分析

    -- 对于慢查询，必须用 EXPLAIN 分析执行计划
EXPLAIN SELECT * FROM orders WHERE status = 0 AND amount > 100;

-- 如果有多个表，用 EXPLAIN FORMAT=JSON 看详细计划
EXPLAIN FORMAT=JSON SELECT ...

    2.4 ❌ 错误示范

    错误 1：只优化 Query_time 最长的查询，不看执行频率。

    -- 一个查询执行一次耗时 5 秒，但一天只执行一次
-- 另一个查询每次 0.5 秒，但一天执行 10 万次
-- 应该优先优化高频查询

    错误 2：只看 Rows_examined，不看业务意义。

    -- Rows_examined = 1000，但这是历史数据归档查询，不影响主业务
-- Rows_examined = 100，但这是核心交易路径上的查询
-- 优化优先级：核心路径 > 非核心路径

    【面试官心理】 这道题我通常会问生产经验。比如："线上发现一个慢查询，EXPLAIN 显示全表扫描，Rows_examined 是 5000 万，你第一步做什么？"能答出"先看业务影响范围再决定优化优先级"的是有实战经验的人。只知道优化 SQL 的是纯技术思维。

    三、索引层面优化 🟡

    3.1 建立合适的索引

    -- 慢查询示例
SELECT * FROM orders
WHERE user_id = 12345
  AND status = 1
  AND created_at >= '2024-01-01'
ORDER BY created_at DESC
LIMIT 10;

-- 分析：
-- 命中索引: user_id + status + created_at（覆盖查询）
-- 执行流程: 索引范围扫描 → 直接返回，无需回表，无需排序

CREATE INDEX idx_user_status_created ON orders(user_id, status, created_at);

    3.2 避免索引失效

    -- 索引 idx(status, created_at)
-- 失效写法
SELECT * FROM orders WHERE created_at > '2024-01-01';  -- 跳过最左列
SELECT * FROM orders WHERE status = 1 AND YEAR(created_at) = 2024;  -- 函数包裹

-- 正确写法
SELECT * FROM orders WHERE status = 1 AND created_at > '2024-01-01';

    3.3 覆盖索引优化

    -- 查询
SELECT id, order_no, amount, status
FROM orders
WHERE user_id = 12345
ORDER BY created_at DESC
LIMIT 10;

-- 建立覆盖索引
CREATE INDEX idx_user_cover ON orders(
    user_id, created_at, id, order_no, amount, status
);
-- 完全覆盖查询，不需要回表，不需要排序

    四、SQL 层面优化 🟡

    4.1 避免 SELECT *

    -- ❌ 慢
SELECT * FROM orders WHERE order_no = 'A001';

-- ✅ 快
SELECT order_no, status, amount FROM orders WHERE order_no = 'A001';

    4.2 分页深度优化

    -- ❌ 慢：深度分页
SELECT * FROM orders ORDER BY id LIMIT 1000000, 10;
-- 扫描 1000010 行，返回后 10 行

-- ✅ 快：游标分页
SELECT * FROM orders
WHERE id > #{last_id}  -- last_id 是上一页最后一条
ORDER BY id
LIMIT 10;
-- 只扫描 10 行

    4.3 批量操作优化

    -- ❌ 慢：逐条插入
INSERT INTO orders VALUES (1, ...);
INSERT INTO orders VALUES (2, ...);
INSERT INTO orders VALUES (3, ...);

-- ✅ 快：批量插入
INSERT INTO orders VALUES
(1, ...),
(2, ...),
(3, ...);
-- 一次网络往返，一条 redo log 刷盘

    4.4 JOIN 优化原则

    -- 小表驱动大表
SELECT * FROM orders o
JOIN users u ON o.user_id = u.id
WHERE u.type = 1;

-- MySQL 优化器自动选择驱动表，但可以强制指定
SELECT * FROM orders o STRAIGHT_JOIN users u ON o.user_id = u.id;
-- STRAIGHT_JOIN 强制左表为驱动表

-- 分解 JOIN 为单表查询
-- 如果 JOIN 的数据量很大，先查小表，在应用层组合

    五、架构层面优化 🟢

    5.1 读写分离

    -- 主库写入，从库读取
-- 读从库，写主库
-- 读写分离后，慢查询对写入的影响减小

    5.2 分库分表

    -- 单表超过 5000 万行，考虑分表
-- 按 user_id 分表
-- 查询命中分片键，只查一个分片
-- 查询未命中分片键，查询所有分表

    5.3 限制查询范围

    -- ❌ 全量查询
SELECT COUNT(*) FROM orders;  -- 5000 万行，全表扫描

-- ✅ 限制范围
SELECT COUNT(*) FROM orders
WHERE created_at >= '2024-01-01'
  AND created_at < '2024-02-01';  -- 索引范围扫描

    【面试官心理】 问慢查询优化的候选人很多，但能说出"架构优化"而不是只盯着 SQL 优化的不多。P7 候选人应该知道：慢查询不只是 SQL 问题，还可能是连接池配置、缓冲池大小、主从延迟等架构问题。

    级别考察重点期望回答
    P5工具使用EXPLAIN 分析、慢查询日志配置、mysqldumpslow 使用
    P6索引/SQL 优化索引设计、SQL 改写、分页优化
    P7架构优化读写分离、分库分表、生产排查流程