mysql 中如何使用explain

56次阅读

没有评论

共计 14675 个字符，预计需要花费 37 分钟才能阅读完成。

自动写代码机器人，免费开通

本篇文章为大家展示了 mysql 中如何使用 explain，内容简明扼要并且容易理解，绝对能使你眼前一亮，通过这篇文章的详细介绍希望你能有所收获。

重点是第二种用法，需要深入的了解。

先看一个例子：

+—-+————-+———+——+—————+——+———+——+——–+——-+

+—-+————-+———+——+—————+——+———+——+——–+——-+

1 row in set (0.03 sec)

加上 extended 后之后：

+—-+————-+———+——+—————+——+———+——+——–+———-+——-+

+—-+————-+———+——+—————+——+———+——+——–+———-+——-+

1 row in set, 1 warning (0.00 sec)

有必要解释一下这个长长的表格里每一列的含义：

id
SELECT 识别符。这是 SELECT 的查询序列号 select_type

SELECT 类型, 可以为以下任何一种:

SIMPLE: 简单 SELECT(不使用 UNION 或子查询)

PRIMARY: 最外面的 SELECT

UNION:UNION 中的第二个或后面的 SELECT 语句

DEPENDENT UNION:UNION 中的第二个或后面的 SELECT 语句, 取决于外面的查询

UNION RESULT:UNION 的结果

SUBQUERY: 子查询中的第一个 SELECT

DEPENDENT SUBQUERY: 子查询中的第一个 SELECT, 取决于外面的查询

DERIVED: 导出表的 SELECT(FROM 子句的子查询)

table

输出的行所引用的表

type

联接类型。下面给出各种联接类型, 按照从最佳类型到最坏类型进行排序:

system: 表仅有一行 (= 系统表)。这是 const 联接类型的一个特例。

const: 表最多有一个匹配行, 它将在查询开始时被读取。因为仅有一行, 在这行的列值可被优化器剩余部分认为是常数。const 表很快, 因为它们只读取一次!

eq_ref: 对于每个来自于前面的表的行组合, 从该表中读取一行。这可能是最好的联接类型, 除了 const 类型。

ref: 对于每个来自于前面的表的行组合, 所有有匹配索引值的行将从这张表中读取。

ref_or_null: 该联接类型如同 ref, 但是添加了 MySQL 可以专门搜索包含 NULL 值的行。

index_merge: 该联接类型表示使用了索引合并优化方法。

unique_subquery: 该类型替换了下面形式的 IN 子查询的 ref: value IN (SELECT primary_key FROM single_table WHERE some_expr) unique_subquery 是一个索引查找函数, 可以完全替换子查询, 效率更高。

index_subquery: 该联接类型类似于 unique_subquery。可以替换 IN 子查询, 但只适合下列形式的子查询中的非唯一索引: value IN (SELECT key_column FROM single_table WHERE some_expr)

range: 只检索给定范围的行, 使用一个索引来选择行。

index: 该联接类型与 ALL 相同, 除了只有索引树被扫描。这通常比 ALL 快, 因为索引文件通常比数据文件小。

ALL: 对于每个来自于先前的表的行组合, 进行完整的表扫描。

possible_keys

指出 MySQL 能使用哪个索引在该表中找到行

key
显示 MySQL 实际决定使用的键 (索引)。如果没有选择索引, 键是 NULL。key_len
显示 MySQL 决定使用的键长度。如果键是 NULL, 则长度为 NULL。ref
显示使用哪个列或常数与 key 一起从表中选择行。rows
显示 MySQL 认为它执行查询时必须检查的行数。多行之间的数据相乘可以估算要处理的行数。filtered
显示了通过条件过滤出的行数的百分比估计值。Extra

该列包含 MySQL 解决查询的详细信息

Distinct:MySQL 发现第 1 个匹配行后, 停止为当前的行组合搜索更多的行。

Not exists:MySQL 能够对查询进行 LEFT JOIN 优化, 发现 1 个匹配 LEFT JOIN 标准的行后, 不再为前面的的行组合在该表内检查更多的行。

range checked for each record (index map: #):MySQL 没有发现好的可以使用的索引, 但发现如果来自前面的表的列值已知, 可能部分索引可以使用。

Using filesort:MySQL 需要额外的一次传递, 以找出如何按排序顺序检索行。

Using index: 从只使用索引树中的信息而不需要进一步搜索读取实际的行来检索表中的列信息。

Using temporary: 为了解决查询,MySQL 需要创建一个临时表来容纳结果。

Using where:WHERE 子句用于限制哪一个行匹配下一个表或发送到客户。

Using sort_union(…), Using union(…), Using intersect(…): 这些函数说明如何为 index_merge 联接类型合并索引扫描。

Using index for group-by: 类似于访问表的 Using index 方式,Using index for group-by 表示 MySQL 发现了一个索引, 可以用来查询 GROUP BY 或 DISTINCT 查询的所有列, 而不要额外搜索硬盘访问实际的表。

一.select_type 的说明

1.UNION:

当通过 union 来连接多个查询结果时，第二个之后的 select 其 select_type 为 UNION。

+—-+————–+————+——-+—————+———+———+——-+——+——-+

| NULL | UNION RESULT |
3 rows in set (0.34 sec)

2.DEPENDENT UNION 与 DEPENDENT SUBQUERY:

当 union 作为子查询时，其中第二个 union 的 select_type 就是 DEPENDENT UNION。
第一个子查询的 select_type 则是 DEPENDENT SUBQUERY。

+—-+——————–+————+——-+—————+———+———+——-+——–+————-+

| NULL | UNION RESULT |
+—-+——————–+————+——-+—————+———+———+——-+——–+————-+

4 rows in set (0.03 sec)

3.SUBQUERY:

子查询中的第一个 select 其 select_type 为 SUBQUERY。

+—-+————-+———+——-+—————+———+———+——-+——+————-+

+—-+————-+———+——-+—————+———+———+——-+——+————-+

2 rows in set (0.03 sec)

4.DERIVED:

当子查询是 from 子句时，其 select_type 为 DERIVED。

+—-+————-+————+——–+—————+———+———+——+——+————-+

| 1 | PRIMARY |
+—-+————-+————+——–+—————+———+———+——+——+————-+

2 rows in set (0.03 sec)

二.type 的说明

1.system，const

见上面 4.DERIVED 的例子。其中第一行的 type 就是为 system，第二行是 const，这两种联接类型是最快的。

2.eq_ref

在 t_order 表中的 order_id 是主键，t_order_ext 表中的 order_id 也是主键，该表可以认为是订单表的补充信息表，他们的关系是 1 对 1，在下面的例子中可以看到 b 表的连接类型是 eq_ref，这是极快的联接类型。

+—-+————-+——-+——–+—————+———+———+—————–+——+————-+

| 1 | SIMPLE | b | ALL | order_id | NULL | NULL | NULL | 1 | |

+—-+————-+——-+——–+—————+———+———+—————–+——+————-+

2 rows in set (0.00 sec)

3.ref

下面的例子在上面的例子上略作了修改，加上了条件。此时 b 表的联接类型变成了 ref。因为所有与 a 表中 order_id=100 的匹配记录都将会从 b 表获取。这是比较常见的联接类型。

+—-+————-+——-+——-+—————+———-+———+——-+——+——-+

| 1 | SIMPLE | a | const | PRIMARY | PRIMARY | 4 | const | 1 | |

| 1 | SIMPLE | b | ref | order_id | order_id | 4 | const | 1 | |

+—-+————-+——-+——-+—————+———-+———+——-+——+——-+

2 rows in set (0.00 sec)

4.ref_or_null

user_id 字段是一个可以为空的字段，并对该字段创建了一个索引。在下面的查询中可以看到联接类型为 ref_or_null，这是 mysql 为含有 null 的字段专门做的处理。在我们的表设计中应当尽量避免索引字段为 NULL，因为这会额外的耗费 mysql 的处理时间来做优化。

+—-+————-+———+————-+—————+———+———+——-+——-+————-+

+—-+————-+———+————-+—————+———+———+——-+——-+————-+

1 row in set (0.00 sec)

5.index_merge

经常出现在使用一张表中的多个索引时。mysql 会将多个索引合并在一起，如下例:

+—-+————-+———+————-+—————–+—————–+———+——+——+——————————————-+

+—-+————-+———+————-+—————–+—————–+———+——+——+——————————————-+

1 row in set (0.09 sec)

6.unique_subquery

该联接类型用于替换 value IN (SELECT primary_key FROM single_table WHERE some_expr) 这样的子查询的 ref。注意 ref 列，其中第二行显示的是 func，表明 unique_subquery 是一个函数，而不是一个普通的 ref。

+—-+——————–+———+—————–+—————–+———+———+——+——–+————-+

+—-+——————–+———+—————–+—————–+———+———+——+——–+————-+

2 rows in set (0.00 sec)

7.index_subquery

该联接类型与上面的太像了，唯一的差别就是子查询查的不是主键而是非唯一索引。

+—-+——————–+———+—————-+—————–+———+———+——+——–+————————–+

+—-+——————–+———+—————-+—————–+———+———+——+——–+————————–+

2 rows in set (0.00 sec)

8.range

按指定的范围进行检索，很常见。

+—-+————-+———+——-+—————+———+———+——+——+————-+

+—-+————-+———+——-+—————+———+———+——+——+————-+

1 row in set (0.00 sec)

9.index

在进行统计时非常常见，此联接类型实际上会扫描索引树，仅比 ALL 快些。

+—-+————-+———+——-+—————+———+———+——+——–+————-+

+—-+————-+———+——-+—————+———+———+——+——–+————-+

1 row in set (0.00 sec)

10.ALL

完整的扫描全表，最慢的联接类型，尽可能的避免。

+—-+————-+———+——+—————+——+———+——+——–+——-+

+—-+————-+———+——+—————+——+———+——+——–+——-+

1 row in set (0.00 sec)

三.extra 的说明

1.Distinct

MySQL 发现第 1 个匹配行后, 停止为当前的行组合搜索更多的行。对于此项没有找到合适的例子，求指点。

2.Not exists

因为 b 表中的 order_id 是主键，不可能为 NULL，所以 mysql 在用 a 表的 order_id 扫描 t_order 表，并查找 b 表的行时，如果在 b 表发现一个匹配的行就不再继续扫描 b 了，因为 b 表中的 order_id 字段不可能为 NULL。这样避免了对 b 表的多次扫描。

+—-+————-+——-+——-+—————+————–+———+—————–+——–+————————————–+

| 1 | SIMPLE | b | ref | order_id | order_id | 4 | test.a.order_id | 1 | Using where; Using index; Not exists |

+—-+————-+——-+——-+—————+————–+———+—————–+——–+————————————–+

2 rows in set (0.01 sec)

3.Range checked for each record

这种情况是 mysql 没有发现好的索引可用，速度比没有索引要快得多。

+—-+————-+——-+——-+———————-+————–+———+——+——+————————————————+

+—-+————-+——-+——-+———————-+————–+———+——+——+————————————————+

2 rows in set (0.00 sec)

4.Using filesort

在有排序子句的情况下很常见的一种情况。此时 mysql 会根据联接类型浏览所有符合条件的记录，并保存排序关键字和行指针，然后排序关键字并按顺序检索行。

+—-+————-+———+——+—————+——+———+——+——–+—————-+

+—-+————-+———+——+—————+——+———+——+——–+—————-+

1 row in set (0.00 sec)

5.Using index

这是性能很高的一种情况。当查询所需的数据可以直接从索引树中检索到时，就会出现。上面的例子中有很多这样的例子，不再多举例了。

6.Using temporary

发生这种情况一般都是需要进行优化的。mysql 需要创建一张临时表用来处理此类查询。

+—-+————-+——-+——+—————+———-+———+—————–+——–+———————————+

| 1 | SIMPLE | b | ref | order_id | order_id | 4 | test.a.order_id | 1 | |

+—-+————-+——-+——+—————+———-+———+—————–+——–+———————————+

2 rows in set (0.00 sec)

7.Using where

当有 where 子句时，extra 都会有说明。

8.Using sort_union(…)/Using union(…)/Using intersect(…)

下面的例子中 user_id 是一个检索范围，此时 mysql 会使用 sort_union 函数来进行索引的合并。而当 user_id 是一个固定值时，请参看上面 type 说明 5.index_merge 的例子，此时会使用 union 函数进行索引合并。

+—-+————-+———+————-+—————–+—————–+———+——+——+————————————————+

+—-+————-+———+————-+—————–+—————–+———+——+——+————————————————+

1 row in set (0.00 sec)

对于 Using intersect 的例子可以参看下例，user_id 与 express_type 发生了索引交叉合并。

+—-+————-+———+————-+———————-+———————-+———+——+——+—————————————————-+

+—-+————-+———+————-+———————-+———————-+———+——+——+—————————————————-+

1 row in set (0.00 sec)

9.Using index for group-by

表明可以在索引中找到分组所需的所有数据，不需要查询实际的表。

+—-+————-+———+——-+—————+———+———+——+——+————————–+

+—-+————-+———+——-+—————+———+———+——+——+————————–+

1 row in set (0.00 sec)

上述内容就是 mysql 中如何使用 explain，你们学到知识或技能了吗？如果还想学到更多技能或者丰富自己的知识储备，欢迎关注丸趣 TV 行业资讯频道。

向 AI 问一下细节

正文完

发表至：数据库

2023-12-04

转载说明：除特殊说明外本站除技术相关以外文章皆由网络搜集发布，转载请注明出处。

SQLite支持正则表达式吗如何使用

IMP的indexfile参数怎么用

怎么在MySQL中高效导入多个.sql文件

phpmyadmin如何导入SQL

如何快速完成mysql数据迁移