知序
返回
保存草稿
发布
H1
H2
H3
1
2
3
Excalidraw 图
AI 写作
AI 优化
目录
输入内容后显示目录
Markdown
# 26 - MySQL 慢查询排查与优化 > 导读:接口 P99 从 50ms 涨到 3 秒,最常见的元凶就是一条没走索引的 SQL。本文把"抓慢 SQL → 读执行计划 → 加对索引 → 改写 SQL"这条链路讲清,并列出 5 种最容易发生的索引失效场景。 ## 一、先建立全局认知 ### Q:接口变慢时,怎么判断问题出在数据库? A:看三个信号是否同时出现: - DBA 或监控反馈有大量慢查询,最慢的 SQL 执行了数秒; - 慢查询的时间点和接口延迟升高**时间吻合**; - 数据库侧 `Threads_running`、CPU 使用率同步上涨。 只要慢查询的开始时间与接口 P99 的抬升重合,基本可以确定是数据库的问题;如果数据库指标平稳,则应转向应用侧的 GC、线程池或下游调用排查。 ### Q:慢查询的排查流程是哪五步? A:**定位慢 SQL → EXPLAIN 分析 → 索引优化 → SQL 改写 → 其他手段**。 顺序不能颠倒:先拿到具体的 SQL 文本,再看执行计划,再决定是加索引还是改写法。跳过 EXPLAIN 直接加索引,是线上最常见的时间浪费。  [编辑 Excalidraw 源图](/images/java-interview-copy/online-troubleshoot/26-mysql-slow-query-mysql-slow-query-optimization-flow-diagram.excalidraw) ## 二、理解核心机制 ### Q:怎么把慢 SQL 抓出来? A:三条路径,按环境选择: ```sql -- 1. 开启慢查询日志 SET GLOBAL slow_query_log = ON; SET GLOBAL long_query_time = 1; -- 超过 1 秒记录 SET GLOBAL log_queries_not_using_indexes = ON; -- 记录未使用索引的查询 -- 2. 查看慢查询日志 -- 日志位置:SHOW VARIABLES LIKE 'slow_query_log_file'; -- 3. 用 mysqldumpslow 分析(按执行时间排序,取前 10) -- mysqldumpslow -s t -t 10 /var/log/mysql/slow.log -- 4. 或者查 performance_schema SELECT digest_text, count_star, avg_timer_wait/1000000000 AS avg_ms FROM performance_schema.events_statements_summary_by_digest ORDER BY avg_timer_wait DESC LIMIT 10; ``` > 取舍:开启 `log_queries_not_using_indexes` 会把所有未走索引的查询都记下来,在小表上会造成大量噪音(小表全表扫描本来就更快)。生产上排查期临时开启、排查完关闭比较稳妥。而 `performance_schema` 是按 SQL 指纹聚合的,适合找"执行次数多 × 平均耗时长"的 SQL——比单看最慢一条更有价值。 ### Q:`EXPLAIN` 的结果怎么看? A:只需要重点盯四个字段: ``` EXPLAIN 关键字段解读: | 字段 | 关注点 | |--------------|------------------------------------------| | type | 访问类型,从好到差:const > eq_ref > ref > range > index > ALL | | key | 实际使用的索引,NULL 表示全表扫描 | | rows | 预估扫描行数,越小越好 | | Extra | Using filesort(需要额外排序) | | | Using temporary(使用临时表) | | | Using index(覆盖索引,最优) | | | Using where(需要回表过滤) | 最差情况:type=ALL, key=NULL → 全表扫描 需要优化:Extra 出现 Using filesort 或 Using temporary ``` 读的顺序是:**先看 `type` 是不是 ALL,再看 `key` 是不是 NULL,最后看 `Extra` 有没有 filesort / temporary**。这三项就能覆盖绝大多数问题。注意 `rows` 只是优化器的**预估值**,不是实际扫描行数,遇到统计信息不准时会偏差很大。 ## 三、放进真实场景 ### Q:什么情况下,加一个索引就能解决问题? A:缺少索引导致的 `type=ALL`,这是最容易修的一类: ```sql -- 问题:user_id 没有索引,全表扫描 EXPLAIN SELECT * FROM orders WHERE user_id = 10086; -- type=ALL, rows=5000000 -- 解决:加索引 ALTER TABLE orders ADD INDEX idx_user_id (user_id); -- type=ref, rows=150 ``` 加索引后 `type` 从 ALL 变成 ref,扫描行数从 500 万降到 150——这是**数量级**的改善,也是索引优化中最典型的收益。 ### Q:为什么明明有索引,`EXPLAIN` 却显示没走? A:五种常见原因,都会让索引"失效": ```sql -- 1. 对索引列使用函数 → 索引失效 SELECT * FROM orders WHERE YEAR(create_time) = 2024; -- 改写: SELECT * FROM orders WHERE create_time >= '2024-01-01' AND create_time < '2025-01-01'; -- 2. 隐式类型转换 → 索引失效 -- phone 是 VARCHAR 类型,传入数字 SELECT * FROM users WHERE phone = 13800138000; -- 改写: SELECT * FROM users WHERE phone = '13800138000'; -- 3. LIKE 左模糊 → 索引失效 SELECT * FROM users WHERE name LIKE '%张'; -- 改写(如果业务允许): SELECT * FROM users WHERE name LIKE '张%'; -- 4. OR 条件中有非索引列 → 索引失效 SELECT * FROM orders WHERE user_id = 1 OR remark = 'test'; -- 改写:拆成 UNION SELECT * FROM orders WHERE user_id = 1 UNION SELECT * FROM orders WHERE remark = 'test'; -- 5. 不满足最左前缀原则 -- 联合索引 (a, b, c),查询条件只有 b 和 c → 索引失效 SELECT * FROM t WHERE b = 1 AND c = 2; -- 需要包含 a: SELECT * FROM t WHERE a = 1 AND b = 1 AND c = 2; ``` 第 2 条最隐蔽:`phone` 是 VARCHAR,写成 `= 13800138000`(数字)时,MySQL 会把**列**转成数字比较,等价于对列做函数运算,索引直接失效。这类问题在代码里几乎看不出来,只能靠 `EXPLAIN` 发现。 ### Q:联合索引的字段顺序应该怎么定? A:遵循"**等值条件在前,排序字段在后**": ```sql -- 原始查询 SELECT * FROM orders WHERE user_id = 10086 AND status = 1 ORDER BY create_time DESC LIMIT 20; -- 最优联合索引:(user_id, status, create_time) -- 原因:WHERE 等值条件在前,ORDER BY 字段在后,避免 filesort ALTER TABLE orders ADD INDEX idx_user_status_time (user_id, status, create_time); ``` 这样建索引的价值在于:前两个字段负责**快速定位**,第三个字段的顺序与 `ORDER BY` 一致,数据库可以直接按索引顺序读取,**省掉 `Using filesort` 这一步**。如果索引建成 `(create_time, user_id, status)`,虽然也能用上索引,但排序仍要额外做,收益大打折扣。 ### Q:深分页为什么慢,怎么优化? A:因为 `OFFSET` 不是"跳过",而是"扫过再丢弃": ```sql -- 问题:OFFSET 很大时,MySQL 要扫描 OFFSET + LIMIT 行再丢弃前 OFFSET 行 SELECT * FROM orders ORDER BY id LIMIT 1000000, 20; -- 扫描 100 万行,只返回 20 行 -- 优化一:游标分页(推荐) SELECT * FROM orders WHERE id > #{lastId} ORDER BY id LIMIT 20; -- 优化二:延迟关联(子查询只查主键) SELECT o.* FROM orders o INNER JOIN (SELECT id FROM orders ORDER BY id LIMIT 1000000, 20) t ON o.id = t.id; ``` 两种方案的差别在于**扫描发生在哪张表上**:游标分页让 `WHERE id > lastId` 直接走索引定位,成本与页码无关;延迟关联则让子查询在索引上只扫主键(不需要回表取全部字段),再按 20 个 id 回表。前者需要前端改造(传游标而不是页码),后者对调用方透明,但页码越深收益越小。 ### Q:还有哪些常见的 SQL 改写手段? A: ```sql -- SELECT * 改为具体字段 -- 问题:SELECT * 返回所有字段,无法使用覆盖索引 SELECT * FROM orders WHERE user_id = 10086; -- 优化:只查需要的字段,可能命中覆盖索引 SELECT id, order_no, amount, status FROM orders WHERE user_id = 10086; ``` ```sql -- 大 IN 查询优化 -- 问题:IN 列表太长 SELECT * FROM orders WHERE user_id IN (1, 2, 3, ..., 10000); -- 优化:分批查询 -- Java 代码中将 list 按 500 个一批拆分 Lists.partition(userIds, 500).forEach(batch -> { orderMapper.selectByUserIds(batch); }); ``` `SELECT *` 的危害不只是多传几个字段——它会让"覆盖索引"(`Using index`)彻底失效,因为查询需要的列不在索引里,必须回表。 ### Q:除了索引和 SQL 改写,还有哪些手段? A: ``` 1. 读写分离:慢查询走从库,不影响主库写入 2. 分库分表:单表超过 2000 万行考虑分表 3. 缓存:热点查询结果缓存到 Redis 4. 异步化:非实时查询走离线数据仓库(如 ClickHouse) 5. 连接池优化:合理设置最大连接数,避免连接耗尽 ``` 这些手段的**优先级低于索引优化**:绝大多数慢查询是"索引问题"而不是"数据量问题"。在索引没调好之前分库分表,等于把问题带到了更多机器上。 ## 四、总结与自测 ### Q:面试里怎么答这道题? A: 1. **排查流程**:开启慢查询日志 → 定位慢 SQL → EXPLAIN 分析 → 索引优化 → SQL 改写; 2. **EXPLAIN 重点**:看 `type`(避免 ALL)、`key`(是否用到索引)、`Extra`(避免 filesort/temporary); 3. **常见索引失效场景**:函数计算、隐式转换、左模糊、OR 非索引列、不满足最左前缀; 4. **深分页优化**:游标分页或延迟关联。 ### Q:加了索引但 `EXPLAIN` 显示没用到,可能是什么原因? A:① 优化器认为全表扫描更快(数据量小或选择性差,如 `status` 只有 0/1 两个值);② 索引列有函数或隐式转换;③ 不满足最左前缀;④ 统计信息不准确,可以 `ANALYZE TABLE` 更新统计信息。 其中第 ① 条最容易被误解:"加了索引就一定走索引"是错的——**索引是否被选中是优化器基于成本的决策**。当某列只有两个取值时,走索引反而要额外回表,优化器多半会直接全表扫描。 ### Q:线上加索引会锁表吗? A:MySQL 5.6+ 支持 Online DDL,大部分 `ALTER TABLE` 操作不会锁表(只在开始和结束时短暂加元数据锁)。但大表加索引仍然耗时较长,建议用 `pt-online-schema-change` 或 `gh-ost` 工具在线加索引,对业务无感知。 --- ## 自测 1. `EXPLAIN` 里 `type=ALL`、`key=NULL`、`rows=5000000`,你的第一步动作是什么? 2. `phone` 字段是 VARCHAR 且有索引,`WHERE phone = 13800138000` 为什么可能不走索引? 3. `SELECT * FROM orders ORDER BY id LIMIT 1000000, 20` 为什么慢,有哪两种改法?
预览
发布文章
添加摘要让读者更快了解文章内容
取消
确认发布
AI 写作助手
输入一个主题,AI 将为你生成一篇 Markdown 博客文章
AI 正在写作中...
取消
开始生成
AI 优化助手
描述你的修改需求,AI 将在原文基础上进行补充或微调
AI 正在优化中...
取消
开始优化
画布
从已有图里选
✕
图名
保存后生成 /images/excalidraw/<图名>.svg(只能用字母、数字、短横线、下划线、点)
取消
保存并插入