异常教程

mysql explain（手把手讲解）

更新时间:

💡一则或许对你有用的小广告

欢迎加入小哈的星球 ，你将获得：专属的项目实战 / 1v1 提问 / Java 学习路线 / 学习打卡 / 每月赠书 / 社群讨论

截止目前， 星球 内专栏累计输出 90w+ 字，讲解图 3441+ 张，还在持续爆肝中.. 后续还会上新更多项目，目标是将 Java 领域典型的项目都整一波，如秒杀系统, 在线商城, IM 即时通讯，权限管理，Spring Cloud Alibaba 微服务等等，已有 3100+ 小伙伴加入学习 ，欢迎点击围观

在数据库开发中，查询性能优化是一个永恒的话题。无论是初学编程的新手，还是有一定经验的开发者，都可能遇到“为什么我的查询这么慢？”的困惑。此时，EXPLAIN 命令便成为了一把关键的“诊断工具”。它能够直观展示 MySQL 如何执行查询，并为优化提供科学依据。本文将以通俗易懂的方式，结合实际案例，逐步解析 EXPLAIN 的核心概念、使用方法及优化技巧，帮助读者快速掌握这一技能。

一、什么是 EXPLAIN？

EXPLAIN 是 MySQL 提供的一个查询分析工具，其核心作用是揭示 SQL 查询的执行计划。当我们执行 EXPLAIN SELECT ... 时，MySQL 会返回一个包含查询执行细节的表格，例如扫描哪些表、使用哪些索引、数据读取顺序等。

可以将 EXPLAIN 比喻为“数据库的导航仪”：就像导航软件会规划最优路线一样，EXPLAIN 告诉我们数据库如何“规划”查询的路径，从而帮助我们发现潜在的性能瓶颈。

示例：基础用法

EXPLAIN SELECT * FROM users WHERE id = 100;

执行此命令后，MySQL 会返回类似以下的表格：

idselect_typetabletypepossible_keyskeykey_lenrefrowsExtra
1SIMPLEusersconstPRIMARYPRIMARY4const1Using index

二、EXPLAIN 的核心字段解析

理解 EXPLAIN 的输出需要逐一解析关键字段。以下将重点讲解最常用的字段及其含义，并通过比喻帮助读者记忆。

1. id: 查询块标识符

id 表示查询中不同操作的执行顺序。例如，主查询的 id 为 1，子查询的 id 可能更高。

  • 比喻：可以想象 id 是“任务编号”，编号越小的任务优先执行。

2. select_type: 查询类型

该字段说明查询的类型，常见值包括：

  • SIMPLE: 简单查询（无子查询或 UNION）。
  • PRIMARY: 最外层的主查询。
  • SUBQUERY: 子查询。

3. table: 操作的表

直接显示当前操作涉及的表名。

4. type: 访问类型（核心指标）

type 是评估查询性能的最关键字段，它描述了 MySQL 如何扫描表。常见类型从优到劣排序如下：

  • system: 表仅有一行数据（如缓存表），性能最优。
  • const: 主键或唯一索引匹配一行数据，如 WHERE id = 1。
  • eq_ref: 根据主键或唯一索引关联表，常见于主外键关联。
  • ref: 非唯一索引扫描，性能中等（如 WHERE name = 'Tom'）。
  • range: 索引范围扫描，如 WHERE age BETWEEN 20 AND 30。
  • index: 全索引扫描，性能较差。
  • ALL: 全表扫描，最差性能。

比喻：将访问类型比作“快递员的配送方式”：

  • const 是“直接到门”（精准命中），
  • ALL 则是“挨家挨户派送”（效率最低）。

5. possible_keys 和 key: 索引使用情况

  • possible_keys: 可能使用的索引列表。
  • key: 实际使用的索引。

注意：如果 key 为 NULL，表示未使用索引。

6. rows: 预估扫描行数

该字段表示 MySQL 预计需要扫描的行数。数值越小，性能越好。

7. Extra: 额外信息

包含重要提示，如：

  • Using where: 表示查询需要在检索后进行过滤。
  • Using temporary: 表示使用了临时表（常见于排序或分组操作）。
  • Using filesort: 表示 MySQL 需要额外排序（非索引顺序）。
  • Using index: 表示仅使用索引树（覆盖索引，性能优异）。

三、实战案例：通过 EXPLAIN 优化查询

以下通过一个具体案例，演示如何利用 EXPLAIN 分析并优化查询。

案例背景

假设有一个 orders 表，结构如下：

CREATE TABLE orders (  
    id INT PRIMARY KEY AUTO_INCREMENT,  
    user_id INT,  
    product_id INT,  
    amount DECIMAL(10,2),  
    created_at DATETIME  
);

需要查询用户 user_id = 100 的订单总金额：

SELECT SUM(amount) FROM orders WHERE user_id = 100;

分析原查询

执行 EXPLAIN：

EXPLAIN SELECT SUM(amount) FROM orders WHERE user_id = 100;

输出结果：

idselect_typetabletypepossible_keyskeykey_lenrefrowsExtra
1SIMPLEordersALLNULLNULLNULLNULL1000Using where

问题分析：

  • type 为 ALL，表示全表扫描。
  • possible_keys 为 NULL，说明没有可用索引。
  • 预估扫描 1000 行数据，效率较低。

优化方案

为 user_id 字段添加索引：

ALTER TABLE orders ADD INDEX idx_user_id (user_id);

再次执行 EXPLAIN：

idselect_typetabletypepossible_keyskeykey_lenrefrowsExtra
1SIMPLEordersrefidx_user_ididx_user_id4const5Using where

优化效果：

  • type 改为 ref，使用了索引 idx_user_id。
  • rows 从 1000 降至 5，性能显著提升。

四、EXPLAIN 的进阶技巧

1. 复杂查询的分析

对于多表关联查询，EXPLAIN 会展示每个表的访问方式及关联顺序。例如：

EXPLAIN SELECT * FROM users JOIN orders ON users.id = orders.user_id WHERE users.city = 'Beijing';

输出结果可能包含两行（users 和 orders 表），需关注两表的 type 和 rows 值，优先优化访问类型较差的表。

2. 覆盖索引优化

当查询字段完全被索引覆盖时，可极大减少 I/O 开销。例如：

-- 原查询  
SELECT product_id FROM orders WHERE user_id = 100;  

-- 添加联合索引  
ALTER TABLE orders ADD INDEX idx_user_product (user_id, product_id);  

-- 执行 EXPLAIN 后，Extra 中出现 "Using index"

3. 避免隐式类型转换

若字段类型不匹配，可能导致索引失效。例如：

-- 错误写法（user_id 是 INT，但传入字符串）  
SELECT * FROM orders WHERE user_id = '100';  

-- 正确写法  
SELECT * FROM orders WHERE user_id = 100;

五、常见误区与解决方案

误区 1：盲目添加索引

索引虽能加速查询，但会降低写入性能（如 INSERT、UPDATE）。需根据业务场景权衡。

误区 2：忽略复合索引的顺序

复合索引（如 (a, b)）遵循“最左前缀原则”。例如：

  • WHERE a = 1 可用索引，
  • WHERE b = 2 无法使用索引。

误区 3：依赖 EXPLAIN 的“预估行数”

rows 是 MySQL 的估算值，实际执行可能因数据分布而不同。需结合慢查询日志综合分析。

六、总结

通过本文的学习，读者应能掌握以下核心要点：

  1. EXPLAIN 的基本用法及关键字段含义，如 type、key、rows 等。
  2. 如何通过案例分析定位性能问题，并利用索引优化查询。
  3. 避免常见误区，如过度依赖索引或忽略复合索引顺序。

掌握 EXPLAIN 是提升数据库开发技能的重要一步。建议读者在实际项目中多加实践，逐步培养“通过执行计划思考查询性能”的习惯。记住，优化并非一蹴而就，而是需要持续学习与迭代的过程。

希望本文能帮助开发者更高效地使用 MySQL，写出高性能的 SQL 查询！

最新发布

查看更多文章