面试官：谈谈你对 MySQL 执行计划的理解？

MySQL 性能分析神器一文读懂EXPLAIN执行计划开篇引子线上系统突然告警用户反馈操作卡顿DBA一看监控CPU 使用率飙升慢查询日志里全是“罪魁祸首”别慌这背后往往藏着一个被忽视的 SQL 语句。掌握EXPLAIN这把“瑞士军刀”你就能化身数据库侦探精准定位性能瓶颈。本文将带你从零开始彻底搞懂 MySQL 的执行计划让你的 SQL 优化之路畅通无阻你将收获什么读完这篇文章你将能够深入理解EXPLAIN命令输出的每一个字段及其深层含义。熟练解读典型的执行计划快速判断 SQL 的好坏。学以致用通过实际案例掌握常见的 SQL 性能问题诊断与优化方法。从容应对技术面试中关于 MySQL 执行计划的高频问题。1.EXPLAIN简介SQL 的“体检报告”EXPLAIN是 MySQL 提供的一个非常实用的命令它能模拟优化器执行 SQL 语句并返回一个详细的报告。这份报告就像 SQL 的“体检报告”告诉我们这条 SQL 语句是如何被执行的它扫描了多少行数据是否使用了索引以及查询的关联方式等关键信息。通过解读这份报告我们就能知道 SQL 是否高效瓶颈在哪里从而进行针对性的优化。如何使用在你的SELECT语句前面加上EXPLAIN关键字即可。例如EXPLAIN SELECT u.name, o.total FROM users u JOIN orders o ON u.id o.user_id WHERE u.status active;执行后你会得到一张包含多个字段的表格接下来我们逐一解读。2. 核心字段解读逐个击破EXPLAIN的输出结果包含了多个字段每个字段都提供了关于查询执行的不同侧面的信息。我们重点关注以下几个核心字段id: 查询序列号id用来标识查询中执行的 SELECT 语句的顺序。它有以下几种情况相同id执行顺序从上到下当 SQL 中有多个简单的查询如UNION之外的多表 JOIN时它们的id相同。优化器会按照从上到下的顺序执行。不同idid值越大越先执行在包含子查询的 SQL 中最里层的子查询id值最大最先被执行。外层查询的id值依次递减。select_type: 查询类型这个字段告诉我们当前这一行的查询是属于哪种类型的查询。SIMPLE: 最简单的 SELECT 查询不包含 UNION 或子查询。PRIMARY: 包含复杂查询时最外层的 SELECT 会被标记为 PRIMARY。SUBQUERY: 在 SELECT 或 WHERE 列表中包含的子查询。DERIVED: 在 FROM 列表中包含的子查询MySQL 会将结果物化即创建一个临时表。UNION: 在 UNION 语句中的第二个或后面的 SELECT 语句。UNION RESULT: UNION 查询的结果。table: 表名这一列显示了当前这一行正在访问的表名。在多表 JOIN 的情况下这里会显示出每次关联操作涉及的具体表。partitions(分区)如果查询的表是分区表这里会显示命中的分区。对于非分区表该列为空。type: 关联类型最重要的性能指标 ⭐⭐⭐type列是判断查询性能好坏的最关键字段之一。它表示 MySQL 在表中找到所需行的方式或者说访问类型。从最优到最差的排序如下system: 表只有一行等于系统表这是const类型的特例。const: 通过主键或唯一索引UNIQUE KEY进行等值查询最多只返回一行数据。因为只匹配一行所以速度非常快。-- 假设 id 是主键 EXPLAIN SELECT * FROM users WHERE id 1; -- type: consteq_ref: 在多表 JOIN 中对于前一个表的每一行在当前表中通过主键或唯一索引UNIQUE KEY最多只匹配一行。这是高效的 JOIN 方式。-- 假设 order.user_id 是一个唯一索引 EXPLAIN SELECT o.*, u.name FROM orders o JOIN users u ON o.user_id u.id; -- type for u: eq_refref: 非唯一索引扫描返回匹配某个单个值的所有行。也是常见的高效 JOIN 方式。EXPLAIN SELECT * FROM users WHERE user_name John; -- type: refrange: 使用索引获取范围值例如BETWEEN,IN,,等操作。EXPLAIN SELECT * FROM users WHERE age BETWEEN 20 AND 30; -- type: rangeindex: 索引全扫描遍历整个索引来查找匹配的行。虽然只扫描索引但如果索引很大性能也可能不佳。ALL:全表扫描这是最糟糕的情况MySQL 会扫描整张表来找到匹配的行。优化时必须坚决避免ALL类型。possible_keys: 可能用到的索引这一列显示了 MySQL 在查询时理论上可以使用哪些索引来提高效率。如果该列为NULL则表示没有相关的索引。这种情况需要考虑为相关列创建索引。key: 实际使用的索引这一列显示了 MySQL 在执行查询时实际选用的索引。如果为NULL则表示没有使用任何索引。key_len: 索引长度key_len表示 MySQL 在索引中实际使用的字节数。通过这个值我们可以判断索引的使用情况。例如一个复合索引(col1, col2, col3)如果key_len只等于col1的长度说明只用到了索引的第一列col2和col3的联合部分并未被利用违反了最左前缀原则。ref: 索引参照列ref列显示了在使用索引进行查找时与索引列进行等值匹配的值或列。它可以帮助我们理解 JOIN 的关联关系。rows: 扫描的行数重要参考rows是一个非常关键的性能指标它表示 MySQL 为了找到所需的行大约需要扫描多少行数据。这个数字越小越好。需要注意的是rows是一个估算值并非精确数字但它能很好地反映扫描数据量的趋势。filtered: 表示返回结果的行数占读取行数的百分比filtered的计算方式是rows乘以filtered的结果大致等于最终返回给客户端的行数。例如rows1000,filtered50意味着 MySQL 读取了 1000 行但经过 WHERE 条件过滤后只有大约 500 行数据被返回。Extra: 额外信息性能优化的线索Extra列包含了 MySQL 解决查询的详细信息是优化的重要依据。常见的值有Using index:覆盖索引。表示查询的列完全被索引覆盖不需要回表查询数据行这是非常高效的。-- 假设 (name, age) 有复合索引 EXPLAIN SELECT name, age FROM users WHERE name John; -- Extra: Using indexUsing where: 表示 MySQL 服务器将在存储引擎检索行后再进行 WHERE 条件的过滤。如果type是ALL并且有Using where说明进行了全表扫描后又做了过滤性能很差。Using temporary: 表示 MySQL 需要创建一个临时表来处理查询常见于GROUP BY或ORDER BY操作。应尽量避免。Using filesort: MySQL 无法利用索引完成ORDER BY操作需要额外的排序步骤。这也是一个需要优化的信号。3. 实战案例从诊断到优化光说不练假把式我们来看几个真实的案例看看EXPLAIN如何帮助我们发现问题并解决问题。案例一全表扫描的陷阱问题场景查询所有姓“张”的用户信息发现查询非常慢。原始 SQL-- 假设表中有100万条用户数据 SELECT * FROM users WHERE last_name Zhang;执行计划EXPLAIN SELECT * FROM users WHERE last_name Zhang; -- type: ALL -- rows: 1000000 -- Extra: Using where分析type为ALLrows高达100万这是一个典型的全表扫描效率极低。优化方案为last_name列添加索引。CREATE INDEX idx_lastname ON users(last_name);优化后执行计划EXPLAIN SELECT * FROM users WHERE last_name Zhang; -- type: ref -- key: idx_lastname -- rows: 100 (假设姓张的用户有100个)效果type从ALL变为ref扫描行数从100万骤降到100性能得到巨大提升。案例二覆盖索引的妙用问题场景业务只需要查询用户的姓名和年龄但 SQL 却选择了SELECT *。原始 SQLSELECT * FROM users WHERE status active;执行计划EXPLAIN SELECT * FROM users WHERE status active; -- type: ref -- key: idx_status -- rows: 10000 -- Extra: NULL (或 Using where)分析看起来type和key都没问题但SELECT *意味着即使使用了idx_status索引找到了数据仍然需要回表去读取users表的其他所有列如 email, address 等造成了不必要的 I/O。优化方案只查询需要的列。SELECT name, age FROM users WHERE status active;优化后执行计划EXPLAIN SELECT name, age FROM users WHERE status active; -- type: ref -- key: idx_status -- rows: 10000 -- Extra: Using index效果Extra变成了Using index这意味着查询所需的所有数据name,age,status都在索引idx_status中无需回表效率更高。