知序
返回
保存草稿
发布
H1
H2
H3
1
2
3
Excalidraw 图
AI 写作
AI 优化
目录
输入内容后显示目录
Markdown
## EXPLAIN 执行计划 `EXPLAIN SELECT * FROM orders WHERE status = 1 AND create_time > '2024-01-01';` ### 关键字段解读 - `type`:访问类型(重要!从好到差) - system > const > eq_ref > ref > range > index > ALL - const:主键或唯一索引等值查询,最多一行(最优) - eq_ref:JOIN 时,被驱动表用主键或唯一索引 - ref:非唯一索引等值查询 - range:索引范围查询(BETWEEN、>、<、IN) - index:全索引扫描(比 ALL 好,因为索引比数据小) - ALL:全表扫描(最差,需要优化) - `key`:实际用到的索引(NULL 表示没用索引) - `key_len`:索引使用的字节数(可以判断联合索引用了几列) - `rows`:预估扫描行数(越小越好) - `Extra`:额外信息(重要!) - Using index:覆盖索引,不需要回表(好) - Using where:在 Server 层过滤 - Using filesort:需要额外排序(需要优化) - Using temporary:使用临时表(需要优化) - Using index condition:索引下推(ICP)  ### 优化决策流程图  *图:EXPLAIN 各信号对应的 SQL 优化动作* ``` 拿到慢 SQL │ ↓ EXPLAIN 分析 │ ├── type=ALL(全表扫描) │ └──→ 加索引,让查询走索引 │ ├── type=index(全索引扫描) │ └──→ 缩小扫描范围(加 WHERE 条件或改用更优的索引) │ ├── Extra=Using filesort │ └──→ 优化 ORDER BY,让排序走索引 │ 检查 ORDER BY 的列是否在索引中且顺序正确 │ ├── Extra=Using temporary │ └──→ 优化 GROUP BY / DISTINCT │ 检查是否有合适索引避免临时表 │ ├── Extra=Using where(且 rows 很大) │ └──→ 索引过滤效果不够 │ 加联合索引让更多条件在索引层过滤 │ ├── key_len 小于预期 │ └──→ 联合索引只用了一部分 │ 检查最左前缀是否满足 │ └── Extra=Using index(覆盖索引) └──→ 当前最优,不需要优化 ✓ ``` ### EXPLAIN type 字段对比示例 ## 真实 EXPLAIN 输出示例与逐字段解读 ### 示例表结构 |字段名| id | user_id | status |amount|ccreate_time| |---| --- | --- | --- | --- |---| |类型| BIGINT| INT | TINYINT |DECIMAL(10,2)|DATETIME| |约束| PRIMARY KEY AUTO_INCREMENT, | NOT NULL | NOT NULL,DEFAULT 0 |NOT NULL|NOT NULL| ```sql CREATE TABLE orders ( id BIGINT PRIMARY KEY AUTO_INCREMENT, user_id INT NOT NULL, status TINYINT NOT NULL DEFAULT 0, amount DECIMAL(10,2) NOT NULL, create_time DATETIME NOT NULL, INDEX idx_user_id (user_id), INDEX idx_status_create (status, create_time) ) ENGINE=InnoDB; -- 假设表中有 100 万条数据 ``` ### 示例 1:全表扫描(最差情况) ```sql EXPLAIN SELECT * FROM orders WHERE amount > 500; ``` ``` +----+-------------+--------+------+---------------+------+---------+------+---------+----------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+--------+------+---------------+------+---------+------+---------+----------+-------------+ | 1 | SIMPLE | orders | ALL | NULL | NULL | NULL | NULL | 1000000 | 33.33 | Using where | +----+-------------+--------+------+---------------+------+---------+------+---------+----------+-------------+ 逐字段解读: type=ALL:全表扫描!最差情况,需要优化 key=NULL:没有使用索引(amount 没有索引) rows=1000000:预估需要扫描 100 万行(整张表) filtered=33.33:过滤后约剩 33.33% 的行 Extra=Using where:在 Server 层用 WHERE 条件过滤 问题:扫描 100 万行,性能极差 优化:给 amount 加索引 ALTER TABLE orders ADD INDEX idx_amount (amount); ``` ### 示例 2:主键等值查询(最优情况) ```sql EXPLAIN SELECT * FROM orders WHERE id = 12345; ``` ``` +----+-------------+--------+-------+---------------+---------+---------+-------+------+----------+-------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+--------+-------+---------------+---------+---------+-------+------+----------+-------+ | 1 | SIMPLE | orders | const | PRIMARY | PRIMARY | 8 | const | 1 | 100.00 | NULL | +----+-------------+--------+-------+---------------+---------+---------+-------+------+----------+-------+ 逐字段解读: type=const:主键等值查询,最多返回 1 行,最优! key=PRIMARY:使用了主键索引 key_len=8:BIGINT 类型占 8 字节 rows=1:只需扫描 1 行 Extra=NULL:没有额外操作,最干净的情况 ``` ### 示例 3:联合索引范围查询(常见情况) ```sql EXPLAIN SELECT * FROM orders WHERE status = 1 AND create_time > '2024-01-01 00:00:00'; ``` ``` +----+-------------+--------+-------+--------------------+--------------------+---------+------+-------+----------+-----------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+--------+-------+--------------------+--------------------+---------+------+-------+----------+-----------------------+ | 1 | SIMPLE | orders | range | idx_status_create | idx_status_create | 6 | NULL | 50000 | 100.00 | Using index condition | +----+-------------+--------+-------+--------------------+--------------------+---------+------+-------+----------+-----------------------+ 逐字段解读: type=range:索引范围查询,比 ALL 好很多 key=idx_status_create:使用了联合索引 key_len=6: status 是 TINYINT(1字节)+ create_time 是 DATETIME(5字节)= 6字节 说明两列都用到了索引! 如果 key_len=1(只有 1 字节),说明只用到了 status,create_time 没用到 rows=50000:预估扫描 5 万行 Extra=Using index condition:使用了索引下推(ICP) ``` ### 示例 4:覆盖索引(最优的二级索引查询) ```sql EXPLAIN SELECT id, status, create_time FROM orders WHERE status = 1 AND create_time > '2024-01-01 00:00:00'; ``` ``` Extra=Using where; Using index 关键变化: "Using index" 表示覆盖索引!查询的字段(id, status, create_time) 都在索引 idx_status_create 中(索引叶子节点包含主键 id),不需要回表! 对比示例 3(SELECT *): 示例 3:需要回表获取 user_id、amount 等字段 示例 4:不需要回表,性能更好 ``` ## 慢查询优化实战 ### 案例:订单列表查询优化 ```sql -- 原始慢查询(执行时间:3.2 秒) SELECT o.*, u.name AS user_name FROM orders o JOIN users u ON o.user_id = u.id WHERE o.status = 1 AND o.create_time >= '2024-01-01 00:00:00' AND o.create_time < '2024-02-01 00:00:00' ORDER BY o.create_time DESC LIMIT 20; ``` ``` 第 1 步:用 EXPLAIN 分析 EXPLAIN 输出(简化): orders: type=ALL, rows=1000000, Extra=Using filesort ← 两个大问题! users: type=eq_ref, rows=1 ← 这个没问题 问题 1:orders 全表扫描(type=ALL) 原因:没有合适的索引覆盖 status + create_time 的查询 问题 2:Using filesort 原因:ORDER BY create_time 没有利用索引,需要额外排序 第 2 步:添加索引 ALTER TABLE orders ADD INDEX idx_status_create (status, create_time); 第 3 步:再次 EXPLAIN orders: type=range, key=idx_status_create, rows=8000, Extra=Using index condition 改善:扫描行数 1,000,000 → 8,000(减少 99.2%),Using filesort 消失 第 4 步:验证执行时间 优化前:3.2 秒 优化后:0.05 秒(提升 64 倍) ``` ## 常见 SQL 优化技巧 ### 1. 分页优化(深分页问题) ```sql -- 差:LIMIT 偏移量大时,需要扫描大量数据再丢弃 SELECT * FROM orders ORDER BY id LIMIT 10; -- 0.001 秒 SELECT * FROM orders ORDER BY id LIMIT 100000, 10; -- 0.8 秒(扫描 10 万行再丢弃) SELECT * FROM orders ORDER BY id LIMIT 900000, 10; -- 3.5 秒(扫描 90 万行再丢弃) -- 好方案 1:游标分页(记录上次最大 id) -- 第一页 SELECT * FROM orders ORDER BY id LIMIT 10; -- 假设最后一条 id=10 -- 第二页(用上次的最大 id 作为起点) SELECT * FROM orders WHERE id > 10 ORDER BY id LIMIT 10; -- 执行时间:0.001 秒(无论翻到第几页都一样快) -- 好方案 2:延迟关联(先查 id,再关联查完整数据) SELECT o.* FROM orders o JOIN ( SELECT id FROM orders ORDER BY id LIMIT 900000, 10 ) t ON o.id = t.id; -- 子查询只查 id(覆盖索引,不回表),速度快 -- 再用 id 关联查完整数据(10 次主键查询) -- 执行时间:0.3 秒(比原来的 3.5 秒快 10 倍以上) ```  [编辑 Excalidraw 源图](/images/excalidraw/06-sql-optimization-deep-pagination-diagram.excalidraw) ### 2. 避免 SELECT * ```sql -- 差:传输多余数据,无法使用覆盖索引 SELECT * FROM users WHERE id = 1; -- 好:只查需要的字段 SELECT id, name, email FROM users WHERE id = 1; ``` ### 3. 避免在 WHERE 中对索引列做运算 ```sql -- 差:索引失效 WHERE YEAR(create_time) = 2024 WHERE id + 1 = 10 -- 好: WHERE create_time >= '2024-01-01' AND create_time < '2025-01-01' WHERE id = 9 ``` ### 4. 批量操作代替循环 ```sql -- 差:循环单条插入(N 次网络往返) -- 好:批量插入(1 次网络往返) INSERT INTO t VALUES (1, ...), (2, ...), (3, ...); ``` ## 多条件查询的索引选择 ```sql -- 场景:查询某用户的已完成订单,按时间倒序 SELECT * FROM orders WHERE user_id = 1001 AND status = 3 AND create_time >= '2024-01-01' ORDER BY create_time DESC LIMIT 10; -- 候选索引方案分析: -- 方案 A:INDEX (user_id) -- user_id=1001 可能有 1000 条记录,然后在 Server 层过滤 status 和 create_time -- 扫描行数:1000,需要 filesort -- 方案 B:INDEX (user_id, status) -- user_id=1001 AND status=3 可能有 200 条记录,然后过滤 create_time -- 扫描行数:200,需要 filesort -- 方案 C:INDEX (user_id, status, create_time) ← 最优 -- 三列都用到索引,扫描行数最少(假设 50 条) -- create_time 在索引中有序,不需要 filesort! ALTER TABLE orders ADD INDEX idx_user_status_create (user_id, status, create_time); -- 验证: EXPLAIN SELECT * FROM orders WHERE user_id = 1001 AND status = 3 AND create_time >= '2024-01-01' ORDER BY create_time DESC LIMIT 10; -- 期望:type=range, key=idx_user_status_create, Extra=Using index condition(没有 filesort!) ```  [编辑 Excalidraw 源图](/images/excalidraw/06-sql-optimization-multi-condition-index-diagram.excalidraw) ## key_len 字段详解 ``` key_len 表示索引使用的字节数,可以判断联合索引用了几列。 各类型字节数: TINYINT:1 字节 INT:4 字节 BIGINT:8 字节 DATETIME:5 字节 VARCHAR(n):n*3+2 字节(utf8mb4,+2 是变长字段的长度标识) 允许 NULL 的列额外 +1 字节 示例:联合索引 (status TINYINT, create_time DATETIME) key_len=1:只用了 status key_len=6(1+5):两列都用到了 ```  [编辑 Excalidraw 源图](/images/excalidraw/06-sql-optimization-key-len-diagram.excalidraw) ## filtered 字段详解 ``` filtered 表示经过存储引擎初步扫描后, 预计还有多少比例的记录能通过 Server 层剩余条件过滤。 ``` ``` 举例: rows = 1000 filtered = 10% 表示优化器估计扫描 1000 行后, 最终只有大约 100 行能真正进入下一步处理。 ``` ``` 它的意义不是“越大越好”或“越小越好”, 而是帮助你判断: 1. 索引过滤是否足够充分 2. 额外 WHERE 条件是不是还在大量做无效过滤 3. JOIN 时驱动表和被驱动表的代价是否合理 ``` ## MySQL 为什么会选错索引,如何处理 ``` 优化器选错索引的常见原因: 1. 统计信息过旧 2. 数据分布倾斜 3. 单列索引很多,但缺少真正合适的联合索引 4. 成本模型估算和真实执行情况有偏差 ``` ```sql ANALYZE TABLE orders; ``` ``` 常见处理手段: 1. 先 ANALYZE TABLE 更新统计信息 2. 补更合理的联合索引 3. 必要时用 FORCE INDEX 做短期兜底 4. 最后再结合慢日志和执行计划复核 ``` ## EXPLAIN 里 key 有值但还是很慢怎么办 ``` key 有值只说明“用了某个索引”, 不代表这个索引就足够高效。 仍然可能慢的原因: 1. 扫描行数太多 2. 回表太多 3. 排序没用上索引 4. JOIN 顺序不合理 5. 走了索引,但选择了低选择性的索引 ``` ``` 所以排查时不能只看 key, 还要一起看: rows、filtered、Extra、实际执行耗时。 ``` ## Using filesort 能优化吗 ``` 可以,但前提是先判断排序是否真的成为瓶颈。 Using filesort 不是“用文件排序”的字面意思, 而是 MySQL 需要额外做排序,没能直接利用索引有序性。 ``` ``` 优化思路: 1. 让 ORDER BY 尽量和索引顺序一致 2. 联合索引把等值过滤列放前面,排序列放后面 3. 减少 SELECT *,降低排序时搬运的数据量 4. 深分页场景结合 LIMIT 优化和覆盖索引 ``` ## 面试常见问题 **Q:EXPLAIN 中 type 字段各个值的含义?哪些需要优化?** A:从好到差:system > const > eq_ref > ref > range > index > ALL。type 为 ALL(全表扫描)或 index(全索引扫描)时需要优化,通过添加合适的索引来提升到 ref 或 range 级别。 **Q:如何优化深分页问题?** A:两种方案:① 游标分页:记录上次查询的最大 id,下次从这个 id 开始查,无论翻到第几页都是 O(1) 的查询,但需要前端配合(不能跳页);② 延迟关联:先用覆盖索引查出 id 列表,再关联查完整数据,减少回表次数,可以跳页但仍需扫描索引。 **Q:key_len 字段怎么看?** A:key_len 表示索引使用的字节数,可以判断联合索引用了几列。例如联合索引 (status TINYINT, create_time DATETIME),key_len=1 说明只用了 status;key_len=6(1+5)说明两列都用到了。 **Q:一条慢 SQL 如何排查优化?** A:① 用 EXPLAIN 查看执行计划,重点看 type、key、rows、Extra;② 如果 type=ALL,说明没走索引,需要添加索引;③ 如果 Extra 有 Using filesort,说明排序没走索引,考虑在 ORDER BY 列上建索引;④ 如果 rows 很大,考虑优化查询条件或索引;⑤ 添加索引后再次 EXPLAIN 验证效果,最后对比实际执行时间。 --- 上一篇:[05 - 锁机制](./05-locks.md) | 下一篇:[07 - 主从复制与高可用](./07-replication-high-availability.md)
预览
发布文章
添加摘要让读者更快了解文章内容
取消
确认发布
AI 写作助手
输入一个主题,AI 将为你生成一篇 Markdown 博客文章
AI 正在写作中...
取消
开始生成
AI 优化助手
描述你的修改需求,AI 将在原文基础上进行补充或微调
AI 正在优化中...
取消
开始优化
画布
从已有图里选
✕
图名
保存后生成 /images/excalidraw/<图名>.svg(只能用字母、数字、短横线、下划线、点)
取消
保存并插入