Z
文章
67
分类
14
评论
9

EXPLAIN深度剖析

| mysql •
字数总计 27770 | 阅读时长 69分钟 | 阅读量 37

一、EXPLAIN介绍

（一）EXPLAIN 是什么

EXPLAIN 是 MySQL 提供的一个强大工具，用于分析 SQL 语句的执行计划。简单来说，它就像是一个 SQL 语句的 “翻译器”，将我们编写的 SQL 语句转换为 MySQL 实际执行的步骤和策略，让我们能够清晰地了解到 MySQL 是如何执行查询的 。通过使用 EXPLAIN，我们可以获取到很多关于查询执行的详细信息，这些信息对于优化 SQL 语句至关重要。

（二）为什么要用 EXPLAIN

在实际的开发中，我们编写的 SQL 语句可能在数据量较小的时候表现良好，但当数据量逐渐增大，或者在高并发的场景下，就可能出现性能问题。使用 EXPLAIN，我们可以在不实际执行 SQL 语句的情况下，了解到查询的执行计划，从而找出潜在的性能瓶颈。比如，我们可以通过 EXPLAIN 判断查询是否使用了合适的索引，如果没有使用索引，或者使用了错误的索引，就可能导致全表扫描，从而使查询效率低下。通过分析 EXPLAIN 的结果，我们可以针对性地优化 SQL 语句，添加或修改索引，调整查询条件等，以提高查询的性能，进而提升整个系统的性能和响应速度，为用户提供更好的体验。

（三）EXPLAIN 的使用方法

EXPLAIN 的使用非常简单，基本语法格式为：EXPLAIN + SQL语句。例如，我们有一个简单的查询语句，从名为users的表中查询所有用户信息：

SELECT * FROM users;

使用 EXPLAIN 时，只需在该语句前加上EXPLAIN关键字即可：

EXPLAIN SELECT * FROM users;

执行上述语句后，MySQL 会返回一个结果集，展示该查询语句的执行计划相关信息，包含多个字段，每个字段都有其特定的含义，这些字段信息对于我们分析和优化查询语句非常关键 。后续我们会详细介绍这些字段的含义以及如何通过它们来优化 SQL 查询。

二、EXPLAIN 输出字段深度解读

（一）id 字段

id 是查询中每个 SELECT 语句的唯一标识符，在 MySQL 的查询执行过程中，id 起着至关重要的作用，它决定了查询的执行顺序。

当查询中只有一个 SELECT 语句时，id 的值通常为 1 。例如：

EXPLAIN SELECT * FROM users;

执行上述语句，得到的结果中 id 字段的值为 1，表示这是一个简单的单查询，执行顺序就是直接从users表中查询所有数据 。

在子查询中，id 的作用就更加明显了。子查询是嵌套在主查询中的查询，id 会为每个子查询分配一个唯一的序号，并且 id 值越大，优先级越高，越先被执行 ，id为 null 最后执行。例如：

EXPLAIN SELECT score.* FROM student_score as score WHERE subject_id = (SELECT id FROM subject WHERE teacher_id = (SELECT id FROM teacher WHERE id = 2));

在这个例子中，最内层的子查询(SELECT id FROM teacher WHERE id = 2)的 id 值最大，它会最先被执行，因为它要先确定教师的 id；接着执行中间层的子查询(SELECT id FROM subject WHERE teacher_id = (SELECT id FROM teacher WHERE id = 2))，根据教师 id 来确定学科 id；最后执行主查询SELECT score.* FROM student_score as score WHERE subject_id = (SELECT id FROM subject WHERE teacher_id = (SELECT id FROM teacher WHERE id = 2))，根据学科 id 来查询学生成绩 。

当查询中包含联合查询（UNION）时，id 的情况会稍微复杂一些。如果 id 相同，可以认为是一组，从上往下顺序执行；在所有组中，id 值越大，优先级越高，越先执行 。例如：

EXPLAIN SELECT subject.* FROM subject LEFT JOIN teacher ON subject.teacher_id = teacher.id
UNION
SELECT subject.* FROM subject RIGHT JOIN teacher ON subject.teacher_id = teacher.id;

在这个联合查询中，两个 SELECT 语句的 id 可能相同也可能不同，假设它们的 id 分别为 1 和 2，那么 id 为 2 的查询会先执行，然后是 id 为 1 的查询，最后将两个查询的结果合并起来 。通过 id 字段，我们能够清晰地了解查询中各个部分的执行顺序，这对于优化复杂查询非常重要。如果发现某个子查询的执行顺序不合理，导致性能低下，我们就可以通过调整查询结构或者添加合适的索引来优化查询 。

（二）select_type 字段：查询类型

select_type 字段用于标识查询的类型，它就像是一个标签，告诉我们这个查询是简单查询、子查询还是其他复杂类型的查询 。不同的查询类型有着不同的执行特点和性能表现，了解 select_type 字段对于优化 SQL 查询至关重要 。

常见的 select_type 类型有以下几种：

SIMPLE：简单查询，不包含子查询或 UNION 查询。这种类型的查询没有过多的复杂逻辑，执行起来相对简单高效 。例如：

EXPLAIN SELECT * FROM users WHERE age > 20;

这个查询就是一个简单查询，select_type 字段的值为 SIMPLE，它直接从users表中查询年龄大于 20 的用户数据 。

PRIMARY：如果查询中包含任何复杂的子部分，最外层的查询则被标记为主查询（PRIMARY）。例如：

EXPLAIN SELECT score.* FROM student_score as score WHERE subject_id = (SELECT id FROM subject WHERE teacher_id = (SELECT id FROM teacher WHERE id = 2));

在这个例子中，最外层的查询SELECT score.* FROM student_score as score WHERE subject_id = (SELECT id FROM subject WHERE teacher_id = (SELECT id FROM teacher WHERE id = 2))的 select_type 字段为 PRIMARY，它依赖于内层子查询的结果来完成整个查询 。

SUBQUERY：当子查询（非相关子查询）出现在 SELECT 列表中或 WHERE 子句中时（不在from中），该子查询的 select_type 会被标记为 SUBQUERY。非相关子查询指不依赖外层查询结果、可独立执行的子查询。例如：

EXPLAIN SELECT score.* FROM student_score as score WHERE subject_id = (SELECT id FROM subject WHERE teacher_id = (SELECT id FROM teacher WHERE id = 2));

这里的子查询(SELECT id FROM subject WHERE teacher_id = (SELECT id FROM teacher WHERE id = 2))的 select_type 字段为 SUBQUERY 。

DERIVED：在 FROM 列表中包含的子查询被标记为 DERIVED（衍生），MySQL 会递归执行这些子查询，把结果放在临时表中 。例如：

EXPLAIN SELECT * FROM (SELECT id, name FROM users WHERE age > 20) AS derived_table;

在这个例子中，子查询(SELECT id, name FROM users WHERE age > 20)被标记为 DERIVED，MySQL 会先执行这个子查询，将结果存储在临时表derived_table中，然后再从临时表中查询数据 。

UNION：若第二个 SELECT 出现在 UNION 之后，则被标记为 UNION。例如：

EXPLAIN SELECT subject.* FROM subject LEFT JOIN teacher ON subject.teacher_id = teacher.id
UNION
SELECT subject.* FROM subject RIGHT JOIN teacher ON subject.teacher_id = teacher.id;

在这个联合查询中，第二个 SELECT 语句的 select_type 字段为 UNION 。

UNION RESULT：从 UNION 表获取结果的 SELECT。例如：

EXPLAIN SELECT subject.* FROM subject LEFT JOIN teacher ON subject.teacher_id = teacher.id
UNION
SELECT subject.* FROM subject RIGHT JOIN teacher ON subject.teacher_id = teacher.id;

在这个例子中，用于获取 UNION 结果的 SELECT 的 select_type 字段为 UNION RESULT 。

通过了解 select_type 字段，我们可以快速判断查询的复杂程度和类型，从而有针对性地进行优化。对于简单查询，可以直接关注查询条件和索引的使用；对于复杂的子查询或联合查询，需要考虑如何优化子查询的执行顺序，或者是否可以将子查询改写为 JOIN 操作，以提高查询性能 。

（三）table 字段：锁定查询涉及的表

table 字段显示了查询涉及的表名或别名，无论是简单查询还是复杂的多表关联查询，table 字段都能让我们一目了然地知道查询涉及到哪些表 。

在单表查询中，table 字段非常直观，就是我们查询的表名 。例如：

EXPLAIN SELECT * FROM users;

这里的 table 字段值为users，表示查询只涉及到users这一张表 。

当查询涉及到多表关联时，table 字段会依次列出关联的表 。例如：

EXPLAIN SELECT users.name, orders.order_id FROM users JOIN orders ON users.user_id = orders.user_id;

在这个查询中，table 字段会先显示users表，然后显示orders表，表明查询是从users表和orders表中获取数据，并通过user_id进行关联 。

如果查询中使用了子查询，并且子查询在 FROM 子句中，那么 table 字段会显示为<derivedN>的形式，其中 N 是子查询的 id 。例如：

EXPLAIN SELECT * FROM (SELECT id, name FROM users WHERE age > 20) AS derived_table;

这里的 table 字段为<derived1>，表示查询使用了 id 为 1 的子查询生成的派生表 。

在联合查询（UNION）中，如果是获取 UNION 结果的查询，table 字段会显示为<unionM,N>的形式，其中 M 和 N 是参与 UNION 的 SELECT 语句的 id 。例如：

EXPLAIN SELECT subject.* FROM subject LEFT JOIN teacher ON subject.teacher_id = teacher.id
UNION
SELECT subject.* FROM subject RIGHT JOIN teacher ON subject.teacher_id = teacher.id;

对于获取 UNION 结果的查询，table 字段可能显示为<union1,2>，表示这个查询是从 id 为 1 和 2 的两个 SELECT 语句的结果中获取数据 。

通过 table 字段，我们可以清晰地看到查询涉及的表结构，这对于分析多表关联查询的性能非常有帮助。如果发现某个表的关联顺序不合理，或者某个表的数据量过大导致查询性能下降，我们就可以针对性地调整查询语句，比如调整关联顺序、添加合适的索引等，以提高查询效率 。

（四）type 字段：访问类型

type 字段是 EXPLAIN 结果中非常关键的一个字段，它表示 MySQL 在查询时使用的数据检索方法，也就是访问类型。不同的访问类型有着不同的性能表现，从最优到最差依次为：system > const > eq_ref > ref > range > index > ALL 。一般来说，得保证查询达到range级别，最好达到ref。了解 type 字段对于优化 SQL 查询性能至关重要 。

ALL - 全表扫描：ALL 表示全表扫描，意味着 MySQL 需要遍历整个表的所有数据行来查找符合条件的记录。例如：

EXPLAIN SELECT * FROM users WHERE name = 'John Doe';

如果name字段上没有索引，执行这个查询时，type 字段的值就会是 ALL，MySQL 会扫描users表的每一行数据，去匹配name为John Doe的记录。在数据量较小的时候，全表扫描的影响可能不太明显，但当数据量逐渐增大，比如users表中有数百万条记录时，全表扫描会导致查询速度极慢，严重影响系统性能。因此，在实际应用中，我们要尽量避免出现 ALL 类型的访问，通过添加合适的索引来优化查询 。

index - 索引全扫描：index 表示索引全扫描，它类似于 ALL，也是扫描整个表，也需要避免，但不同的是，它是按照索引的顺序来扫描索引树，而不是直接扫描数据行。例如：

EXPLAIN SELECT idx_column FROM large_table;

如果idx_column上有索引，执行这个查询时，type 字段可能为 index。这种方式适用于查询的字段都包含在索引中，也就是覆盖索引的情况，此时不需要回表查询数据行，直接从索引中就能获取所需数据，所以比 ALL 快一些。另外，当需要对数据按照索引列进行排序时，也可能会使用到索引全扫描 。

因为mysql索引默认都是有序的，所以我们在使用索引字段排序时，要正确利用有序性以及增加索引范围筛选条件，对于符合索引一定要满足最左前缀匹配原则，这样可以减少索引的全扫描。

range - 范围扫描：range 表示范围扫描，它是在指定的索引范围内进行查询，使用一个索引来选择行。常见的范围查询操作符有BETWEEN、>、<、IN等 。例如：

EXPLAIN SELECT * FROM employees WHERE salary BETWEEN 5000 AND 10000;

在这个例子中，salary字段上有索引，执行查询时，type 字段为 range，MySQL 会在salary索引上查找在 5000 到 10000 这个范围内的记录，相比全表扫描，大大减少了扫描的数据量，提高了查询效率 。

ref - 非唯一索引扫描：ref 表示非唯一索引扫描，它使用非唯一索引（普通索引、非唯一的外键索引）来查找数据，返回匹配某个单独值的所有行 。例如：

EXPLAIN SELECT * FROM employees WHERE department_id = 5;

如果department_id是一个普通索引，执行这个查询时，type 字段为 ref，MySQL 会通过department_id索引查找所有department_id为 5 的记录 。在关联查询中，当使用非唯一索引进行连接时，也会出现 ref 类型的访问 。

eq_ref - 唯一索引扫描：eq_ref 表示唯一索引扫描，它用于主键（PRIMARY KEY）或唯一索引（UNIQUE KEY）的等值查询，通常出现在多表 JOIN 操作中 。对于每一行来自主表的数据，子表最多只会返回一条匹配记录 。例如：

EXPLAIN SELECT e.emp_id, d.dept_name FROM employees e JOIN departments d ON e.dept_id = d.dept_id;

如果dept_id在departments表中是主键或唯一索引，执行这个查询时，type 字段为 eq_ref，MySQL 会通过dept_id唯一索引快速匹配到对应的部门记录，查询效率非常高 。

const - 常量查找：const 表示常量查找，适用于唯一索引（PRIMARY KEY 或 UNIQUE）查询，并且查询条件是常量 。MySQL 仅需查询一次，就能获取数据 。例如：

EXPLAIN SELECT * FROM employees WHERE emp_id = 100;

如果emp_id是主键，执行这个查询时，type 字段为 const，MySQL 在编译阶段就确定了查询结果，直接通过主键找到对应的记录，查询速度非常快 。

system - 系统表的特殊情况：system 是 const 的特例，当表只有一行记录（等于系统表）时出现 。例如，在一些系统表中，数据量非常小且固定，可能会出现 system 类型的访问 。在实际业务中，system 类型很少见，一般可以忽略 。

通过了解 type 字段的不同取值及其含义，我们可以判断查询的性能状况。在优化查询时，我们的目标是尽量将 type 优化到 ref 或更好级别，避免出现 ALL 和 index 类型，通过合理创建和使用索引，提高查询效率 。

（五）possible_keys 和 key 字段

在 MySQL 的查询优化中，possible_keys 和 key 字段是与索引密切相关的两个重要字段，能够帮助我们了解索引在查询中的潜在应用和实际使用情况 。

possible_keys 字段显示了 MySQL 在查询时可能使用的索引列表 。这些索引是根据查询条件和表结构来确定的，它们就像是一份候选名单，列出了所有有可能被用来优化查询的索引 。例如：

EXPLAIN SELECT * FROM users WHERE age > 20 AND gender = 'Male';

如果users表在age和gender字段上分别有索引，那么在执行这个查询时，possible_keys 字段可能会显示这两个索引，表明这两个索引都有可能被用于优化查询 。然而，possible_keys 中列出的索引并不一定会被实际使用，它只是提供了一种可能性 。

key 字段则显示了 MySQL 实际选择使用的索引 。告诉我们在众多可能的索引中，MySQL 真正使用了哪个索引来执行查询 。如果 key 字段的值为 NULL，则表示没有使用索引，查询可能会进行全表扫描 。如果想强制mysql使用或忽视possible_keys列中的索引，可以在查询中使用 force

index、ignore index。例如：

EXPLAIN SELECT * FROM users WHERE name = 'John Doe';

如果name字段上没有索引，执行这个查询时，key 字段为 NULL，MySQL 会进行全表扫描来查找符合条件的记录 。而如果name字段上有索引，并且 MySQL 选择使用了这个索引，那么 key 字段就会显示该索引的名称 。

通过对比 possible_keys 和 key 字段，我们可以判断索引的使用情况是否合理 。如果 possible_keys 中列出了多个索引，但 key 字段显示只使用了其中一个，我们可以进一步分析为什么其他索引没有被使用，是否可以通过调整查询条件或者创建更合适的索引来提高查询性能 。例如，如果查询条件中同时包含了age和gender字段，但 MySQL 只使用了age字段上的索引，我们可以考虑创建一个包含age和gender字段的联合索引，以提高查询效率 。

当 key 字段为 NULL，而 possible_keys 中列出了索引时，说明索引未被使用，这可能是由于查询条件的写法不当、索引的选择性不高或者数据分布不均匀等原因导致的 。此时，我们需要进一步分析原因，采取相应的优化措施，比如调整查询语句、重新设计索引或者对数据进行分析和优化 。

（六）key_len 字段：索引使用字节数

key_len 字段在 MySQL 的查询分析中扮演着重要的角色，它表示索引中使用的字节数，通过这个字段，我们可以了解到 MySQL 在使用索引时的一些细节，就像是一把尺子，能够衡量索引使用的 “长度” 。

key_len 的计算与索引字段的类型、字符集以及是否允许 NULL 值等因素有关 。

key_len 表示MySQL 在查询中实际使用的索引字段的字节长度，用于评估复合索引是否被完整利用。

  • 值越小：说明索引使用越 “简短”（可能仅使用部分索引字段）。

  • 值等于索引总长度：说明索引被完整使用（最佳状态）。

（七）ref 字段

ref 表示在索引查找中，哪些 “列或常量” 被用来匹配索引列的值，用于说明索引查找的 “匹配条件来源”。判断索引的匹配方式（是用常量、字段还是函数结果匹配），间接反映索引的利用效率。

const 表示用 “常量” 匹配索引列（如 WHERE id = 1），且该索引是主键或唯一索引（确保只匹配一行）。是最理想的情况，说明索引精确匹配，效率极高。

func 表示匹配条件是 “函数计算结果”（如 WHERE age = YEAR(NOW())），此时索引无法直接利用函数结果进行匹配，需计算后再查找。效率较低，尽量避免在索引列上使用函数（会导致索引失效）。

字段名 如 user.class_id ，表示用 “其他表的字段” 匹配当前表的索引列（通常出现在多表连接中）。示例：EXPLAIN SELECT * FROM user u JOIN class c ON u.class_id = c.id;，若 u.class_id 有索引，则 ref 可能显示 test.c.id（test 是库名）。

（八）rows 字段

rows 表示 MySQL 估计执行该查询需要扫描的行数（是 “估计值”，非精确值）。评估查询的 “扫描范围”，值越小说明查询效率越高（扫描的行数越少）。

rows的结果基于表的统计信息（如索引基数、数据分布）计算，而非实际扫描行数，可能与真实情况有偏差（可通过 ANALYZE TABLE 表名 更新统计信息）。

若索引有效，rows 应远小于表的总记录数；若 rows 接近表的总记录数，说明索引未被有效利用（可能是全表扫描）。

在连接查询中，rows 表示对 “当前表” 的估计扫描行数，需结合连接顺序判断整体效率。

（九）filtered 字段

filtered 表示 “符合过滤条件的行” 占 “扫描行” 的百分比（范围 0-100）。反映过滤条件的 “有效性”，filtered 越高，说明过滤条件筛选掉的无效数据越多，查询效率越高。在连接查询中，filtered 表示当前表经过过滤后，与下一张表连接的行占比。

（十）extra 字段

extra 包含查询执行的 “额外信息”，是分析查询性能的 “关键线索”，能直接反映索引利用、排序、临时表等核心操作。可以判断查询是否存在低效操作（如文件排序、临时表），或是否使用了高效优化（如覆盖索引）。

Using index 表示查询使用了 “覆盖索引”（所需字段均在索引中），无需回表查询数据行，效率极高。

Using where 表示查询使用了 WHERE 条件过滤，但未使用索引（或索引未覆盖过滤条件），需在扫描后过滤数据。若同时出现 Using index + Using where，表示用索引过滤（如 SELECT id FROM user WHERE id > 10，索引 idx_id 覆盖且过滤），是正常情况。

Using filesort 表示查询需要 “额外排序”（无法利用索引的有序性），排序操作可能在内存或磁盘中进行（量大时磁盘排序效率极低）。可以尝试调整 ORDER BY 字段与索引顺序一致（如索引 idx_age(age)，则 ORDER BY age 可避免排序）。

Using temporary 表示查询需要创建 “临时表” 存储中间结果（如 GROUP BY 非索引字段、DISTINCT 操作等），临时表会消耗内存 / 磁盘资源，效率低。可以为 GROUP BY 或 DISTINCT 的字段创建索引，避免临时表。

Using index condition 表示使用了 “索引条件下推（Index Condition Pushdown）” 优化：存储引擎层会先通过索引过滤部分数据，再将剩余数据返回给服务器层继续过滤，减少回表次数。是良性优化，说明索引利用更高效。

索引条件下推示例：

表中有联合索引：(name,age)，但是第一个检索条件name使用了like操作，导致查出的age是无序的，无法直接使用索引的排序，索引下推会先把name符合的索引记录全部查出来，查出来后不回表(如果没有索引下推，这里直接会回表)，先再次过滤age大于20的记录，然后再回表。

SELECT * FROM user WHERE name LIKE '张%' AND age > 20;

三、索引最佳实践

# 示例表
CREATE TABLE `employees` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(24) NOT NULL DEFAULT '' COMMENT '姓名',
  `age` int(11) NOT NULL DEFAULT '0' COMMENT '年龄',
  `position` varchar(20) NOT NULL DEFAULT '' COMMENT '职位',
  `hire_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '入职时间',
  PRIMARY KEY (`id`),
  KEY `idx_name_age_position` (`name`,`age`,`position`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8 COMMENT='员工记录表';

 INSERT INTO employees(name,age,position,hire_time) VALUES('LiLei',22,'manager',NOW());
 INSERT INTO employees(name,age,position,hire_time) VALUES('HanMeimei',23,'dev',NOW());
 INSERT INTO employees(name,age,position,hire_time) VALUES('Lucy',23,'dev',NOW());

1.全值匹配

核心：查询条件完全匹配索引中的所有列时，索引会被高效利用。

  • 示例 1：EXPLAIN SELECT * FROM employees WHERE name = 'LiLei';（单列索引匹配）

  • 示例 2：EXPLAIN SELECT * FROM employees WHERE name = 'LiLei' AND age = 22;（复合索引前两列匹配）

  • 示例 3：EXPLAIN SELECT * FROM employees WHERE name = 'LiLei' AND age = 22 AND position = 'manager';（复合索引全列匹配）

2.最左前缀法则

核心：多列索引（复合索引）需从最左列开始匹配，且不能跳过中间列，否则索引失效。

  • 有效示例：EXPLAIN SELECT * FROM employees WHERE name = 'Bill' AND age = 31;（从最左列name开始，不跳列）

  • 失效示例 1：EXPLAIN SELECT * FROM employees WHERE age = 30 AND position = 'dev';（跳过最左列name）

  • 失效示例 2：EXPLAIN SELECT * FROM employees WHERE position = 'manager';（跳过最左列name和中间列age）

3.索引列上的操作会导致索引失效

核心：在索引列上执行计算、函数操作或类型转换（自动 / 手动），会使索引失效，触发全表扫描。

  • 函数操作示例：

    • 有效：EXPLAIN SELECT * FROM employees WHERE name = 'LiLei';（直接使用索引列）

    • 失效：EXPLAIN SELECT * FROM employees WHERE LEFT(name, 3) = 'LiLei';（对索引列name使用函数）

  • 日期函数示例：

    • 失效：EXPLAIN SELECT * FROM employees WHERE DATE(hire_time) = '2018-09-30';（对索引列hire_time使用函数）

    • 优化：EXPLAIN SELECT * FROM employees WHERE hire_time >= '2018-09-30 00:00:00' AND hire_time <= '2018-09-30 23:59:59';（用范围查询替代函数）

4.范围条件右边的列无法使用索引

核心：复合索引中，若某列使用范围条件（如>、<、>=、<=、BETWEEN），则其右侧的列无法利用索引。

  • 有效示例：EXPLAIN SELECT * FROM employees WHERE name = 'LiLei' AND age = 22 AND position = 'manager';（无范围条件，全列有效）

  • 失效示例：EXPLAIN SELECT * FROM employees WHERE name = 'LiLei' AND age > 22 AND position = 'manager';（age用范围条件，右侧position无法使用索引，但是会使用索引下推优化）

5.优先使用覆盖索引，避免SELECT *

核心：查询字段若均在索引中（覆盖索引），无需回表查询完整数据，效率更高；SELECT *会导致回表，增加 I/O。

  • 高效示例：EXPLAIN SELECT name, age FROM employees WHERE name = 'LiLei' AND age = 23 AND position = 'manager';（查询字段均在索引中）

  • 低效示例：EXPLAIN SELECT * FROM employees WHERE name = 'LiLei' AND age = 23 AND position = 'manager';（SELECT *需回表）

6.不等于、NOT IN、NOT EXISTS可能导致全表扫描

核心：!=、<>、NOT IN、NOT EXISTS通常无法使用索引；<、>、<=、>=等范围操作是否使用索引，由 MySQL 优化器根据数据量、检索比例等评估。

  • 示例：EXPLAIN SELECT * FROM employees WHERE name != 'LiLei';（可能全表扫描）

7.IS NULL/IS NOT NULL一般无法使用索引

核心：对索引列使用IS NULL或IS NOT NULL时，索引通常失效。

  • 示例：EXPLAIN SELECT * FROM employees WHERE name IS NULL;（索引可能失效）

8.LIKE以通配符开头（%xxx）导致索引失效

核心：LIKE '%xxx'或LIKE '%xxx%'会使索引失效；LIKE 'xxx%'（前缀匹配）可使用索引。

  • 失效示例：EXPLAIN SELECT * FROM employees WHERE name LIKE '%Lei';（以%开头）

  • 有效示例：EXPLAIN SELECT * FROM employees WHERE name LIKE 'Lei%';（前缀匹配）

解决LIKE '%xxx%'索引失效的方法：

  • 方法 1：使用覆盖索引（查询字段均为索引列）：EXPLAIN SELECT name, age, position FROM employees WHERE name LIKE '%Lei%';

  • 方法 2：数据量较大时，借助搜索引擎（如 Elasticsearch）实现模糊查询。

9.字符串不加单引号导致索引失效

核心：字符串类型的索引列，若查询条件不加单引号，会触发自动类型转换，导致索引失效。

  • 有效示例：EXPLAIN SELECT * FROM employees WHERE name = '1000';（字符串加单引号）

  • 失效示例：EXPLAIN SELECT * FROM employees WHERE name = 1000;（不加单引号，触发类型转换）

10.慎用OR或IN

核心：OR或IN是否使用索引，由 MySQL 优化器根据检索比例、表大小等评估，可能导致全表扫描。

  • 示例：EXPLAIN SELECT * FROM employees WHERE name = 'LiLei' OR name = 'HanMeimei';（优化器可能不使用索引）

11.范围查询优化

核心：当范围查询覆盖数据量过大时，优化器可能选择全表扫描；可拆分范围为多个小范围提升索引利用率。

  • 低效示例：EXPLAIN SELECT * FROM employees WHERE age >= 1 AND age <= 2000;（范围过大，可能不走索引）

  • 优化示例：

    • EXPLAIN SELECT * FROM employees WHERE age >= 1 AND age <= 1000;

    • EXPLAIN SELECT * FROM employees WHERE age >= 1001 AND age <= 2000;（拆分后可能走索引）

四、总结

通过EXPLAIN，我们能够深入了解 SQL 查询的执行计划，洞察 MySQL 在执行查询时的每一个决策和步骤 。从查询顺序的确定到访问类型的选择，从索引的潜在应用到实际使用情况，EXPLAIN 为我们提供了丰富而详细的信息，这些信息是我们优化 SQL 性能的关键依据 。

在实际的 Java 开发中，SQL 查询的性能直接关系到系统的响应速度和用户体验。一个高效的 SQL 查询能够使系统快速响应用户的请求，提升用户满意度；而一个低效的查询则可能导致系统卡顿，甚至引发用户的流失 。因此，我们要养成使用 EXPLAIN 分析 SQL 查询的好习惯，在开发过程中，对每一条重要的 SQL 语句都进行仔细的分析和优化，确保其能够高效地执行 。

文章作者： Z
本文链接：
版权声明： 本站所有文章除特别声明外，均采用 CC BY-NC-SA 4.0 许可协议。转载请注明来自 微博客！
mysql
喜欢就支持一下吧