MySQL explain根据查询计划去优化SQL语句

当我们执行 MySQL 数据库中一条SELECT语句时，MySQL 根据查询语句的结构和条件，生成一个查询计划，然后根据该计划来执行查询操作。通过执行 explain 命令，我们可以获取这个查询计划，通过这个查询计划，我们可以比较容易地找出 SQL 查询语句存在的性能瓶颈，从而进一步优化 SQL 语句，提高查询效率。

下面是根据查询计划优化SQL语句的完整攻略：

一、使用 explain 命令

我们可以使用如下的语法来执行 explain 命令：

EXPLAIN SELECT ...

这里的 SELECT ... 表示具体的 SQL 查询语句，我们可以在其中填写完整的 SELECT 查询语句。执行上述命令之后，MySQL 会返回查询计划的详细信息，包括查询中使用的索引、扫描行数、连接类型等信息。

二、分析查询计划

查看查询计划的输出结果，我们可以发现其中包含如下重要信息：

每个 SELECT 查询语句会分配一个唯一的 id 值，表示执行该查询语句的顺序。

select_type

该字段表示 SELECT 查询语句的类型，主要可以分为以下 8 种：

SIMPLE：简单的 SELECT 查询，不包含子查询或者 UNION 操作
PRIMARY：外层查询
SUBQUERY：子查询
DERIVED：派生表，from 子句中的子查询
UNION：UNION 操作
UNION RESULT：UNION 的结果
DEPENDENT UNION：依赖 UNION 的子查询
DEPENDENT SUBQUERY：依赖外层表的子查询
table

该字段表示查询语句中使用到的表名。

type

该字段表示在对表执行操作时，MySQL 底层使用的查询类型。例如，在该查询中是否使用了索引，是否执行了全表扫描等。

其中常见的几种类型如下：

ALL：全表扫描
index：使用了索引扫描
range：使用了索引，且索引按照范围查找
ref：使用了非唯一索引进行查询
eq_ref：使用了唯一性索引进行查询
const/system：这些类型都非常快，分别表示查询一个只有一行返回的值或者使用了 mysql 系统表中的一行数据
possible_keys

该字段表示当前查询中可能使用到的索引。

该字段表示MySQL底层实际使用的索引。如果该字段为 NULL，则表示在该查询中没有使用到任何的索引。

rows

该字段表示对表执行操作的行数。

Extra

该字段包含了关于查询计划的其他信息

三、通过查询计划优化 SQL 语句

通过查询计划，我们可以分析出查询语句的性能瓶颈，进而进行 SQL 查询的优化。下面，我们举两个简单的示例说明：

示例一

首先，我们有一张students表，表中包含三个字段：id、name和age，其中id为主键。现在，我们要查询出所有姓名为“张三”且年龄为18岁的学生的信息，同时需要按照id降序排列。我们可以使用如下的 SQL 查询语句：

SELECT * FROM students WHERE name = '张三' AND age = 18 ORDER BY id DESC;

执行 explain 命令后，可以得到查询计划如下：

+----+-------------+----------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table    | type | possible_keys | key  | rows    | Extra|  
+----+-------------+----------+------+---------------+------+---------+------+-------------+
| 1  | SIMPLE      | students | ref  | idx_name_age  | idx_name_age | 1      | Using where; Using index; Backward partial sort|
+----+-------------+----------+------+---------------+------+---------+------+-------------+

查询计划中的 possible_keys 列显示出来的是 idx_name_age，也就是说这个查询的最优索引是 idx_name_age （根据表中"名字"和"年龄"字段创建了复合索引）。查询计划中的 Extra 列显示了 Using where、Using index 和 Backward partial sort。在这个查询中，Using where 意味着 MySQL 会根据 WHERE 子句来筛选数据；Using index 表示 MySQL 使用了覆盖索引（covering index）即这个查询中的语句是全索引的，did、name和age都只需要在索引中查找，而不是扫描整个表；Backward partial sort 表示 MySQL 使用倒序排序（即根据id字段降序排序）。

通过对查询计划的分析，我们可以看出这是一个优化较为完善的查询，已经通过覆盖索引来扫描数据并使用了适当的排序方式。

示例二

接下来，我们有一张user表，表中包含两个字段：id（主键）和 phone。我们需要查询出所有id以偶数开头且phone字段不为空的用户的信息。我们可以使用如下的 SQL 查询语句：

SELECT * FROM user WHERE id % 2 = 0 AND phone IS NOT NULL;

执行 explain 命令后，可以得到查询计划如下：

+----+-------------+-------+-------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type  | possible_keys | key  | rows    | Extra|
+----+-------------+-------+-------+---------------+------+---------+------+------+-------------+
| 1  | SIMPLE      | user  | range | id_phone_key  | id_phone_key | 1250000 | Using where|
+----+-------------+-------+-------+---------------+------+---------+------+------+-------------+

查询计划中的 possible_keys 列显示出来的是 id_phone_key，也就是说这个查询的最优索引是 id_phone_key （根据表中"id"和"phone"字段创建的联合索引）。查询计划中的 type 列显示出来的是 range，表明MySQL使用了范围扫描。通过对查询计划的分析，我们可以看出这个查询使用到了适当的索引，但是随着数据量的增加，查询时间可能会变慢，需要进行性能优化。可以考虑使用分区技术，将记录按照某个规则划分到不同的分区中，从而减少单个分区中的记录数，提高查询性能。

综上所述，通过分析查询计划，我们可以总结出SQL查询时需要注意的细节和优化方向，从而优化SQL查询，并提高查询性能。

本站文章如无特殊说明，均为本站原创，如若转载，请注明出处：MySQL explain根据查询计划去优化SQL语句 - Python技术站