前言

想的再多，不如行动起来，大家好，我是啊Q，让我们徜徉在知识的海洋里吧。

一起“开启掘金成长之旅！这是我参与「掘金日新计划 · 2 月更文挑战」的第18天， 点击查看活动详情 。

前面我们分析了索引的创建，索引的类型以及索引如何优化等问题。下面我们来通过查询的执行计划来了解一下数据库是如何执行一个查询的，以及该查询有没有用到索引和扫描的数据的行数等问题。通过执行计划我们又该如何优化我们的查询语句等问题。

查询计划的作用

查询计划是数据库执行 SQL 查询时生成的一组步骤，它描述了数据库执行查询时所采用的具体策略。查询计划的作用包括：

优化查询：查询计划可以帮助数据库优化查询语句的执行效率。通过查询计划，可以分析 SQL 语句的执行情况，确定最优执行路径，从而优化查询效率。
定位性能问题：查询计划可以帮助开发人员和 DBA 定位性能问题。通过分析查询计划，可以发现性能问题所在，包括索引使用不当、语句写法不当、表关联方式不合适等问题。
评估查询复杂度：查询计划可以评估查询语句的复杂度。通过查看查询计划中的操作步骤、扫描行数、访问方式等信息，可以了解查询语句的复杂度，从而优化查询语句，提高查询效率。
优化索引：查询计划可以帮助优化数据库索引。通过查询计划，可以了解查询语句所使用的索引及其效果，从而优化索引设计和使用。
优化表结构：查询计划可以帮助优化数据库表结构。通过查询计划，可以了解表之间的关系、数据分布情况等信息，从而优化表的设计和使用。

EXPLAIN

在MySQL中，使用EXPLAIN命令可以查看查询语句的执行计划，它会返回一张表格，表格中每一行代表查询执行的一个阶段，其中包含了多个字段信息，下面是每个字段的含义：

id: 表示查询的执行顺序，如果是简单查询，该字段只有一个值1；如果是复杂查询，该字段的值表示查询的执行顺序，值越大优先级越高，越先被执行。
select_type: 表示查询的类型，如SIMPLE、PRIMARY、UNION、SUBQUERY等，具体含义如下：

- SIMPLE: 简单查询，不包含子查询或者UNION查询
- PRIMARY: 包含多个表的复杂查询，最外层的查询称为PRIMARY查询
- UNION: UNION查询
- DEPENDENT UNION: UNION查询中，子查询依赖于外层查询的结果集
- UNION RESULT: UNION查询的结果集
- SUBQUERY: 子查询，可以出现在SELECT、WHERE、FROM等子句中
- DEPENDENT SUBQUERY: 子查询依赖于外层查询的结果集
- DERIVED: 表示从临时表中获取结果的子查询，一般出现在FROM子句中的子查询中
- MATERIALIZED: 和DERIVED类似，表示从临时表中获取结果的子查询，但会被显式地放入临时表中

table: 表示查询的表
partitions: 表示查询的分区
type: 表示查询时使用的索引类型，常见的值包括：

- ALL: 全表扫描
- index: 全索引扫描
- range: 使用索引范围查询
- ref: 使用非唯一索引查询，返回匹配某个单独值的所有行
- eq_ref: 使用唯一索引查询，返回匹配某个单独值的一行
- const、system: 使用主键或唯一索引查询，返回匹配某个单独值的一行

possible_keys: 表示可能使用的索引
key: 表示实际使用的索引
key_len: 表示使用的索引的长度，单位是字节，如果使用的是复合索引，则该值为多个索引长度之和
ref: 表示索引的匹配条件，如果使用的是常量或直接匹配的索引，则该值为常量或匹配的值；如果使用的是复合索引，则该值会显示每个索引列的匹配条件

rows: 这是指在执行查询时需要扫描的行数。该值越小越好，因为扫描的行数越少，查询的速度就越快。如果使用的是覆盖索引，则该值将为 NULL。
filtered：表示根据条件过滤的行数占总行数的比例，100%，表示查询条件很精确，过滤了所有行。
Extra: 这是一个附加信息字段，包含有关查询执行的其他信息。这个字段中的值可能包括：Using where（表示需要在返回结果前进行过滤）、Using temporary（表示需要使用临时表）和Using filesort（表示需要进行排序）等。

示例：

假设我们有一个包含上亿条记录的图书馆书籍表，其中包含书籍的标题、作者、出版社、ISBN号、价格等信息。我们想要查询标题以“3体”开头的所有书籍。

给出没有索引的查询sql

我们可以使用如下的查询语句来查询标题以“3体”开头的所有书籍：

SELECT * FROM books WHERE title LIKE '3体%';

我们使用EXPLAIN命令来查看一下该查询的查询计划：

EXPLAIN SELECT * FROM books WHERE title LIKE '3体%';

得到的查询计划结果如下：

+----+-------------+-------+------+---------------+------+---------+------+----------+-------------+ 
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows     | Extra       |
+----+-------------+-------+------+---------------+------+---------+------+----------+-------------+ 
|  1 | SIMPLE      | books | ALL  | NULL          | NULL | NULL    | NULL | 10000000 | Using where | 
+----+-------------+-------+------+---------------+------+---------+------+----------+-------------+

可以看出，MySQL没有使用任何索引，而是进行了全表扫描，扫描了10000000条记录。

通过查询计划和查询时间等相关问题，给出优化sql

可以看到，上述查询需要进行全表扫描，导致查询效率低下。为了提高查询效率，我们可以在title列上创建一个前缀索引，这样就可以通过索引定位到匹配的记录，而不必扫描整个表。

ALTER TABLE books ADD INDEX(title(20));

然后再次执行查询语句：

SELECT * FROM books WHERE title LIKE '3体%';

给出优化后的sql的查询计划并给出分析

我们再次使用EXPLAIN命令来获取查询计划：

EXPLAIN SELECT * FROM books WHERE title LIKE '3体%';

得到的查询计划结果如下：

+----+-------------+-------+-------+---------------+---------+---------+------+------+----------+-------------+ 
| id | select_type | table | type  | possible_keys | key     | key_len | ref  | rows | filtered | Extra       | 
+----+-------------+-------+-------+---------------+---------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | books | range | title         | title   | 62      | NULL | 3126 |    11.11 | Using where | 
+----+-------------+-------+-------+---------------+---------+---------+------+------+----------+-------------+

可以看出，MySQL使用了title索引来定位匹配的记录，只扫描了3126条记录，而不是全表扫描，大大提高了查询效率。

如何利用查询计划优化查询

前言

查询计划的作用

EXPLAIN