Explain命令在优化查询中怎么应用

开发技术作者：iii 2024-05-08 14:00:02

今天小编给大家分享一下Explain命令在优化查询中怎么应用的相关知识点，内容详细，逻辑清晰，相信大部分人都还太了解这方面的知识，所以分享这篇文章给大家参考...

今天小编给大家分享一下Explain命令在优化查询中怎么应用的相关知识点，内容详细，逻辑清晰，相信大部分人都还太了解这方面的知识，所以分享这篇文章给大家参考一下，希望大家阅读完这篇文章后有所收获，下面我们一起来了解一下吧。

首先，我们来看一个简单的示例：

EXPLAIN SELECT * FROM `users` WHERE `id` = 1;

这条 SQL 查询语句查询了 users 表中 id 等于 1 的行。下面是这条 SQL 语句的 EXPLAIN 结果：

+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------------+
| id | select_type | table | partitions | type  | possible_keys | key     | key_len | ref   | rows | filtered | Extra       |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------------+
| 1  | SIMPLE      | users | NULL       | const | PRIMARY       | PRIMARY | 4       | const | 1    | 100.00   | Using index |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------------+

接下来，我们将逐一分析每一个字段的含义。

id

id 是一个唯一标识符，用于区分每个 SELECT 语句。在一个复杂的查询中，可能会包含多个 SELECT 语句，每个 SELECT 语句都会有一个不同的 id。在 EXPLAIN 的输出结果中，如果 id 相同，那么这些查询将被认为是相互关联的。

select_type

select_type 表示查询类型。下面是 select_type 可能出现的取值及其含义：

SIMPLE：简单的 SELECT 查询，不包含子查询或 UNION 查询；
PRIMARY：最外层的 SELECT 查询；
SUBQUERY：子查询中的第一个 SELECT 查询，该查询在最终结果中返回一个值，用于作为主查询的条件；
DEPENDENT SUBQUERY：依赖于外部查询的子查询，子查询中的 SELECT 查询会被重复执行；
DERIVED：派生表，查询中包含子查询作为 FROM 子句的一部分；
UNION：UNION 中的第二个及后面的 SELECT 查询；
DEPENDENT UNION：依赖于外部查询的 UNION 查询；
UNION RESULT：UNION 的结果集；
DEPENDENT UNION RESULT：依赖于外部查询的 UNION 结果集。

table

table 表示查询涉及的表

partitions

partitions 表示查询涉及的分区。

type

type 表示 MySQL 执行查询时采用的访问类型。下面是 type 可能出现的取值及其含义：

system：仅包含一行的表，系统表（例如 MySQL 中的 mysql.user 表）；
const：仅查询一行，基于主键或唯一索引的等值查询（例如 id = 1）；
eq_ref：使用唯一索引或主键从单个表中查询一行数据；
ref：使用非唯一索引从单个表中查询多行数据；
fulltext：全文搜索；
ref_or_null：类似于 ref，但是还包含 NULL 值；
index_merge：使用多个索引合并结果，比如使用 OR 来连接多个索引；
unique_subquery：使用 IN 或 EXISTS 进行子查询；
index_subquery：使用 IN 或 EXISTS 进行子查询，但是子查询使用了索引；
range：查询范围内的行，使用一个索引进行查找；
index：全表扫描，但是只遍历索引树；
ALL：全表扫描。

在优化查询时，我们通常希望避免出现 ALL、index 或 fulltext 这样的访问类型，而是希望查询能够使用更加高效的索引访问方式，例如 eq_ref、ref 或 range。

possible_keys

possible_keys 表示 MySQL 可能使用的索引列表。

key

key 表示 MySQL 实际使用的索引。

key_len

key_len 表示索引使用的字节数。

ref

ref 表示查询使用的索引列或常量。

rows

rows 表示 MySQL 估计需要扫描的行数。

filtered

filtered 表示结果集的行占全部匹配行的比例。如果 filtered 很小，说明查询的结果集很小。

Extra

Extra 字段包含了执行查询的额外信息，通常包括以下信息：

Using where：表示 MySQL 会在存储引擎层面使用 WHERE 子句来过滤结果集；
Using index：表示 MySQL 使用了覆盖索引来查询数据，不需要访问表；
Using temporary：表示 MySQL 在查询过程中需要使用临时表；
Using filesort：表示 MySQL 需要对结果集进行排序；
Using join buffer：表示 MySQL 需要使用连接缓存区；
Impossible where：表示 WHERE 子句总是返回 false；
Select tables optimized away：表示 MySQL 可以在查询过程中删除未引用的表；
No tables used：表示查询不需要访问任何表。

以上就是 EXPLAIN 命令详情介绍

怎么优化查询？

通过 EXPLAIN 命令输出的结果，我们可以判断查询的瓶颈在哪里，然后进行优化。通常，我们可以从以下几个方面入手：

选择合适的索引

在 EXPLAIN 输出中，可以看到 possible_keys 和 key 字段，它们分别表示可能使用的索引和实际使用的索引。如果 key 字段是 NULL，那么说明查询没有使用任何索引，这是需要优化的重点。为了提高查询效率，我们应该尽可能地使用索引，而不是全表扫描。

在选择索引时，我们需要根据查询条件的类型和频率来选择合适的索引。通常来说，可以选择与 WHERE 子句中使用的条件完全匹配的索引。如果查询中有多个条件，那么可以选择多个条件的交集（AND）或并集（OR）的索引。另外，也可以使用联合索引来覆盖多个查询条件。

在选择索引时，我们还需要注意一些性能问题。例如，我们应该选择基于数据密度较高的列的索引，避免使用字符串类型的索引，避免使用过多的联合索引等。

减少数据访问

在 EXPLAIN 输出中，可以看到 type 字段，它表示 MySQL 执行查询时采用的访问类型。如果 type 字段是 ALL 或 index，那么说明查询需要进行全表扫描，这是需要优化的重点。为了提高查询效率，我们需要尽可能地避免全表扫描。

一种减少数据访问的方法是使用覆盖索引。覆盖索引是指查询只需要从索引中读取数据，而不需要回到数据表中查找其他数据。使用覆盖索引可以避免 MySQL 进行全表扫描，从而大大提高查询效率。

为了使用覆盖索引，我们需要选择合适的索引，并将查询所需的所有列都包含在索引中。如果索引中的列不能满足查询的需求，那么 MySQL 就需要回到数据表中查找其他数据，从而导致性能下降。

减少排序和分组

在 EXPLAIN 输出中，可以看到 Extra 字段，它表示 MySQL 需要进行的额外操作。如果 Extra 字段中出现了 Using filesort 或 Using temporary，那么说明查询需要进行排序或分组，这是需要优化的重点。为了提高查询效率，我们需要尽可能地减少排序和分组操作。

一种减少排序和分组的方法是使用索引。通过选择合适的索引，我们可以避免 MySQL 进行排序和分组操作，从而提高查询效率。另外，我们也可以使用 ORDER BY 和 GROUP BY 子句来明确排序和分组的顺序，避免 MySQL 进行额外的操作。