1.执行计划解释
什么是执行计划呢？SQL是一种傻瓜式语言，每一个条件就是一个需求，访问的顺序不同就形成了不同的执行计划。MySQL必须做出选择，一次只能有一种访问路径，一个访问路径就是一个执行计划。
通常一条SQL有多个执行计划，那我们如何选择？MySQL数据库与Oracle一样，使用的是基于开销(cost)的优化器策略，那种执行开销更低，就意味着性能更好，速度更快，MySQL就选择哪一种。
2.执行计划用什么命令去查看SQL执行
#1.Explain，可以查看SELECT,DELETE,INSERT,REPLACE,UPDATE语句的执行计划
mysql> help explain;
Name: 'EXPLAIN'
Description:
Syntax:
{EXPLAIN | DESCRIBE | DESC}
tbl_name [col_name | wild]
{EXPLAIN | DESCRIBE | DESC}
[explain_type]
{explainable_stmt | FOR CONNECTION connection_id}
{EXPLAIN | DESCRIBE | DESC} ANALYZE [FORMAT = TREE] select_statement
explain_type: {
FORMAT = format_name
}
format_name: {
TRADITIONAL
| JSON
| TREE
}
explainable_stmt: {
SELECT statement
| TABLE statement
| DELETE statement
| INSERT statement
| REPLACE statement
| UPDATE statement
}
The DESCRIBE and EXPLAIN statements are synonyms. In practice, the
DESCRIBE keyword is more often used to obtain information about table
structure, whereas EXPLAIN is used to obtain a query execution plan
(that is, an explanation of how MySQL would execute a query).
URL: https://dev.mysql.com/doc/refman/8.0/en/explain.html
mysql> explain select user,host,plugin from mysql.user;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
| 1 | SIMPLE | user | NULL | ALL | NULL | NULL | NULL | NULL | 6 | 100.00 | NULL |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
1 row in set, 1 warning (0.01 sec)
需要注意的是：
1.在早期的MySQL版本中，使用EXTENDED查看扩展信息，目前默认已经启用了扩展信息的输出，因此该参数显得多余了，在MySQL 8.0中已经移除该参数。
2.在早期版本中，分区信息是使用EXPLAIN PARTITIONS输出的，目前已经默认开启了分区信息的输出，该参数也已经不再需要，在MySQL 8.0中已经移除该参数。
3.不能在同一个EXPLAIN中同时使用EXTENDED和PARTITIONS关键字，这2个关键字都不能与FORMAT关键字一起使用。
FORMAT参数用于选择输出格式，一共有2种输出格式：
-- TRADITIONAL ：以表格显示输出，默认模式
-- JSON ：以json格式输出
mysql> explain format=json select user from mysql.user;
{
"query_block": {
"select_id": 1,
"cost_info": {
"query_cost": "0.85"
},
"table": {
"table_name": "user",
"access_type": "index",
"key": "PRIMARY",
"used_key_parts": [
"Host",
"User"
],
"key_length": "351",
"rows_examined_per_scan": 6,
"rows_produced_per_join": 6,
"filtered": "100.00",
"using_index": true,
"cost_info": {
"read_cost": "0.25",
"eval_cost": "0.60",
"prefix_cost": "0.85",
"data_read_per_join": "3K"
},
"used_columns": [
"User"
]
}
}
}
-- 以表格格式输出执行计划，默认方式 EXPLAIN sql_stmt -- 以json格式输出执行计划 EXPLAIN FORMAT=JSON sql_stmt
#2.Desc
mysql> help desc;
Name: 'DESC'
Description:
Syntax:
{EXPLAIN | DESCRIBE | DESC}
tbl_name [col_name | wild]
{EXPLAIN | DESCRIBE | DESC}
[explain_type]
{explainable_stmt | FOR CONNECTION connection_id}
{EXPLAIN | DESCRIBE | DESC} ANALYZE [FORMAT = TREE] select_statement
explain_type: {
FORMAT = format_name
}
format_name: {
TRADITIONAL
| JSON
| TREE
}
explainable_stmt: {
SELECT statement
| TABLE statement
| DELETE statement
| INSERT statement
| REPLACE statement
| UPDATE statement
}
The DESCRIBE and EXPLAIN statements are synonyms. In practice, the
DESCRIBE keyword is more often used to obtain information about table
structure, whereas EXPLAIN is used to obtain a query execution plan
(that is, an explanation of how MySQL would execute a query).
URL: https://dev.mysql.com/doc/refman/8.0/en/explain.html
mysql> desc select user,host,plugin from mysql.user;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
| 1 | SIMPLE | user | NULL | ALL | NULL | NULL | NULL | NULL | 6 | 100.00 | NULL |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)
3.执行计划基础信息
首先以TRADITIONAL格式查看执行计划：
mysql> explain select user,host,plugin from mysql.user;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
| 1 | SIMPLE | user | NULL | ALL | NULL | NULL | NULL | NULL | 6 | 100.00 | NULL |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)
再以JSON格式查看执行计划：
mysql> explain format=json select user,host,plugin from mysql.user;
可以看到以上两种格式输出的信息基本相同，但是也存在不一样的地方，个人觉得最大的区别在于：josn格式的执行计划把cost给展示出来了，MySQL优化器是基于cost选择执行计划的，查看cost对于调优很重要。但是，在实际的使用过程中，我们往往会以表格的形式查看执行计划，因为表格形式的执行计划较为简练，便于我们查看。
4.Explain 查出执行计划化各字段解释
mysql> explain select user,host,plugin from mysql.user;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
| 1 | SIMPLE | user | NULL | ALL | NULL | NULL | NULL | NULL | 6 | 100.00 | NULL |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)
- select值 含义
---------------------- -------------------------------------
SIMPLE 简单的select查询，未使用UNION和子查询
PRIMARY 查询中包含任何复杂的子部分，则被标记为PRIMARY，PRIMARY为最外层查询，最后执行
UNION 第2个SELECT在UNION之后，则被标记为UNION
DEPENDENT UNION 含有UNION查询的第二个或最后一个表，依赖外部的查询
UNION RESULT UNION结果
SUBQUERY 在SELECT或WHERE中包含的子查询
DEPENDENT SELECT 子查询中的第一个SELECT，依赖外部的查询
DERIVED 衍生表，衍生表是FROM子句中子查询的内部名称
MATERIALIZED 物化子查询
UNCACHEABLE SUBQUERY 子查询，其结果无法缓存，必须针对外部查询的每一行进行评估
UNCACHEABLE UNION 在UNION里的第二个或最后一个表属于不可缓存的子查询
table: 此次查询访问的表
---<unionM,N>：该行是id为M和N的行的并集
---<derivedN>：该行是id为N的行的派生表
---<subqueryN>：该行是物化子查询的结果
partitions：该参数用于记录使用的分区信息，NULL表示该表不是分区表
type：连接类型，见后面"执行计划连接类型type". 索引查询的类型（ALL、index、range、ref、eq_ref、const(system)、NULL）
possible_keys：在该查询中，MySQL可能使用的索引，如果此列是NULL，则没有相关的索引，在这种情况下，需要检查WHERE字句，以确定是否适合创建索引
key：MySQL实际使用的索引。在大多数情况下，key中的值都在possible_key里面，但也会出现possible_key不存在该值，但key里面存在的情
key_len：该列指MySQL决定使用的索引长度。该值体现了在使用复合索引的时候，使用了复合索引的前面哪几个列(需要根据字段长度计算)，如果key列为NULL，则该列也为NULL。由于key存储的格式原因，可以为NULL的列的key长度比NOT NULL的列长度大1。
ref：列显示哪些列或者常量与key中的索引进行比较，以从表中选择行
rows：MySQL查询需要遍历的行数，对于innodb表，可能并不总是准确的。这里需要特别注意，Oracle数据库的执行计划里面也有rows列，不过代表结果的行数，含义不一样
filtered:被条件过滤的行数百分比。最大值为100，表示没有行过滤，值从100减小表示过滤增加。rows表示检查的行数，rows * filtered/100表示过滤后的行数，也就是与下表进行连接的行
Extra：执行计划的额外信息，见后面"执行计划额外信息Extra"
5.执行计划连接类型type类型讲解
explain的type列表示表的连接类型，从最佳到最差类型如下：比较常见：（ALL、index、range、ref、eq_ref、const(system)、NULL）
System --> const --> eq_ref --> ref --> fulltext --> ref_or_unll --> index_merge --> unique_subquery --> index_subquery --> range --> index --> ALL
- system：该表只有一行，这是const连接的特殊情况，平时不会出现，可以不用重点注意
- const：该表最多只有一个匹配行，该行在查询开始时读取。因为只有一行，所以优化器的其余部分可以将这一行中的值做为常量，因为它值读取一次。const在基于主键或者唯一性索引比较时使用。
- eq_ref：与驱动表的连接查询，后表(被驱动表)仅读取一行数据，当被驱动表存在主键索引或者unique+not null时使用，eq_ref用于使用"="运算的索引列
- ref：与驱动表的连接查询，后表(被驱动表)读取一行或多行数据。ref用于仅使用key的最左前缀，或者说key不是PAIMARY KAY或UNIQUE索引。换句话说，如果连接无法根据KEY选择单个行，则使用ref，否则使用eq_ref。ref可以用于使用"="或者"<=>"运算符进行的比较
- fulltext：使用FULLTEXT进行连接
- ref_or_null：这种方式类似于ref，但是MySQL会额外搜索包含NULL值的行
- index_merge：索引合并优化，把多个索引合并为一个使用
- unique_subquery：该类型替换eq_ref形式下的IN子查询，子查询中最多返回一个值，提高查询效率
- index_subquery：该类型类似于unique_subquery，它用来替代子查询，子查询中返回值的个数不确
- range：使用索引去检索一个范围的行数据，key列是使用到的索引，ref列为NULL。range可以在=，<>，>,>=,<,<=,IS NULL,<=>,BETWEEN,LIKE或IN()index：index与ALL相似，只是索引树被扫描，对应2种情况：
- a.索引本省存储相关的列数据，如果索引中的数据可满足查询需求，则仅扫描索引树，在这种情况下，Extra列显示为Using index。
- b.使用对索引的读取执行全表扫描，以按顺序查找数据行，在这种情况下，Extra没有出现Using index。
- ALL：全表扫描，如果驱动表不是以const方式获取数据的，则可以会导致非常糟糕的查询性能。通常可以添加索引来避免权标扫描
6. analyze 命令讲解
#1.analyze 这个语句是会真正执行，而不是预估执行。所以在生产繁忙中慎用，会占用资料
mysql> explain analyze select user,host,plugin from mysql.user;
+--------------------------------------------------------------------------------------+
| EXPLAIN |
+--------------------------------------------------------------------------------------+
| -> Table scan on user (cost=0.85 rows=6) (actual time=0.165..0.189 rows=6 loops=1)
|
+--------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> desc format=tree select user,host,plugin from mysql.user;
+--------------------------------------------+
| EXPLAIN |
+--------------------------------------------+
| -> Table scan on user (cost=0.85 rows=6)
|
+--------------------------------------------+
1 row in set (0.00 sec)
以上两种况可以做为生产与测试对比，可以看出语句真正执行与测试执行的结果对比。