Mysql 通过explain分析SQL执行计划

原创

wx62e28ac222a20 2022-08-02 14:05:36 ©著作权

文章标签 数据库 mysql java sql python 文章分类 数据库

©著作权归作者所有：来自51CTO博客作者wx62e28ac222a20的原创作品，请联系作者获取转载授权，否则将追究法律责任

一.MySQL逻辑架构

MySQL逻辑架构分为三层，如下图。

Mysql 通过explain分析SQL执行计划_数据库

客户端
如，连接处理、授权认证、安全等功能
核心服务
MySQL大多数核心服务均在这一层
包括查询解析、分析、优化、缓存、内置函数(如，时间、数学、加密等)
所有的跨存储引擎的功能也在这一层，如，存储过程、触发器、视图等
存储引擎
负责MySQL中的数据存储和读取
中间的服务层通过API与存储引擎通信，这些API屏蔽了不同存储引擎间的差异
重点解释下查询缓存：对于select语句，在解析查询之前，服务器会先检查查询缓存(Query Cache)。如果命中，服务器便不再执行查询解析、优化和执行的过程，而是直接返回缓存中的结果集。

MySQL查询过程如果能搞清楚MySQL是如何优化和执行查询的，对优化查询一定会有帮助。很多查询优化实际上就是遵循一些原则让优化器能够按期望的合理的方式运行。

下图是MySQL执行一个查询的过程。实际上每一步都比想象中的复杂，尤其优化器，更复杂也更难理解。本文只给予简单的介绍。

Mysql 通过explain分析SQL执行计划_sql_02

MySQL查询过程如下：

客户端将查询发送到MySQL服务器
服务器先检查查询缓存，如果命中，立即返回缓存中的结果；否则进入下一阶段
服务器对SQL进行解析、预处理，再由优化器生成对象的执行计划
MySQL根据优化器生成的执行计划，调用存储引擎API来执行查询
服务器将结果返回给客户端，同时缓存查询结果https://www.cndba.cn/hbhe0316/article/22646

执行计划
优化与执行
MySQL会解析查询，并创建内部数据结构(解析树)，并对其进行各种优化，包括重写查询、决定表的读取顺序、选择合适的索引等。

用户可通过关键字提示(hint)优化器，从而影响优化器的决策过程。也可以通过通过优化器解释(explain)优化过程的各个因素，使用户知道数据库是如何进行优化决策的，并提供一个参考基准，便于用户重构查询和数据库表的schema、修改数据库配置等，使查询尽可能高效。

mysql> explain select * from t1 where id = 1;
+----+-------------+-------+------------+------+---------------+------+---------+------+----------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows     | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+------+---------+------+----------+----------+-------------+
|  1 | SIMPLE      | t1    | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 15728923 |    10.00 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+----------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

关于explain,详见如下

在上述查询中,type列对应的为ALL，那么走的是全表扫描，判断为没有创建索引导致。
在t1表创建索引

mysql> create index index_t1 on t1 (id);
Query OK, 0 rows affected (1 min 4.10 sec)
Records: 0  Duplicates: 0  Warnings: 0

再次执行explain，可以看定type已经走了index_t1索引

mysql> explain select * from t1 where id = 1;
+----+-------------+-------+------------+------+---------------+----------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key      | key_len | ref   | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+----------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | t1    | NULL       | ref  | index_t1      | index_t1 | 5       | const |    2 |   100.00 | NULL  |
+----+-------------+-------+------------+------+---------------+----------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.01 sec)

前后select的时间对比，
未创建索引之前的查询值:

mysql> select * from t1 where id = 1;
+------+------+
| id   | name |
+------+------+
|    1 | aaa  |
|    1 | aaa  |
+------+------+
2 rows in set (11.38 sec)

创建索引之后的查询值:

mysql> select * from t1 where id = 1;
+------+------+
| id   | name |
+------+------+
|    1 | aaa  |
|    1 | aaa  |
+------+------+
2 rows in set (0.00 sec)

多表inner join后的explain示意:

mysql> select * from t1 inner join tb2 on t1.id = tb2.id;
+------+------+------+
| id   | name | id   |
+------+------+------+
|    4 | aaa  |    4 |
|    4 | aaa  |    4 |
|    5 | aaa  |    5 |
|    5 | aaa  |    5 |
|    6 | aaa  |    6 |
|    6 | aaa  |    6 |
|    7 | aaa  |    7 |
|    7 | aaa  |    7 |
|    8 | aaa  |    8 |
|    8 | aaa  |    8 |
+------+------+------+
10 rows in set (0.00 sec)

mysql> explain select * from t1 inner join tb2 on t1.id = tb2.id;
+----+-------------+-------+------------+------+---------------+----------+---------+---------------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key      | key_len | ref           | rows | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+----------+---------+---------------+------+----------+-------------+
|  1 | SIMPLE      | tb2   | NULL       | ALL  | NULL          | NULL     | NULL    | NULL          |    5 |   100.00 | Using where |
|  1 | SIMPLE      | t1    | NULL       | ref  | index_t1      | index_t1 | 5       | testdb.tb2.id |    1 |   100.00 | NULL        |
+----+-------------+-------+------------+------+---------------+----------+---------+---------------+------+----------+-------------+
2 rows in set, 1 warning (0.00 sec)