mysql执行计划查看工具explain

在优化sql语句时，我们经常会用到explain语句，这里对explain语句做一个详细的总结说明。

The EXPLAIN statement provides information about how MySQL executes statements. EXPLAIN works with SELECT, DELETE, INSERT, REPLACE, and UPDATE statements.

通过一个实例来说明explain的用法，其实使用很简单，主要是通过实例来介绍explain各个输出项的使用。（实例中使用的数据是MySQL官方提供的测试数据）

#查看员工中是管理者的信息，工号，姓名，性别，部门ID。mysql>  a.emp_no, concat(a.first_name, , a.last_name) as fullname, a.gender,b.dept_no  from employees as a, dept_manager as b  where a.emp_no =+--------+-----------------------+--------+---------+| emp_no | fullname              | gender | dept_no |+--------+-----------------------+--------+---------+|  | Margareta Markovitch  | M      | d001    ||  | Vishwani Minakawa     | M      | d001    ||  | Ebru Alpin            | M      | d002    ||  | Isamu Legleitner      | F      | d002    ||  | Shirish Ossenbruggen  | F      | d003    ||  | Karsten Sigstam       | F      | d003    ||  | Krassimir Wegerle     | F      | d004    ||  | Rosine Cools          | F      | d004    ||  | Shem Kieras           | M      | d004    ||  | Oscar Ghazalie        | M      | d004    ||  | DeForest Hagimont     | M      | d005    ||  | Leon DasSarma         | F      | d005    ||  | Peternela Onuegbe     | F      | d006    ||  | Rutger Hofmeyr        | F      | d006    ||  | Sanjoy Quadeer        | F      | d006    ||  | Dung Pesch            | M      | d006    ||  | Przemyslawa Kaelbling | M      | d007    ||  | Hauke Zhang           | M      | d007    ||  | Arie Staelin          | M      | d008    ||  | Hilary Kambil         | F      | d008    ||  | Tonny Butterworth     | F      | d009    ||  | Marjo Giarratana      | F      | d009    ||  | Xiaobin Spinelli      | F      | d009    ||  | Yuchang Weedman       | M      | d009    |+--------+-----------------------+--------+---------+ rows  set (

id：select选择标识符。这是查询中选择的序列号。如果行引用其他行的联合结果，则该值为空。在这种情况下，table列会显示一个类似于
select_type:选择的类型，可以是下表中显示的任何类型。在表格中，json name表示的是在explain以json格式输出时对应的名字。

`select_type` Value	JSON Name	Meaning
`SIMPLE`	None	Simple `SELECT` (not using `UNION` or subqueries) 【简单类型的查询，不使用索引或者子查询】
`PRIMARY`	None	Outermost `SELECT 【最外面的查询】`
`UNION`	None	Second or later `SELECT` statement in a `UNION 【联合查询中的第二个或者更晚的查询】`
`DEPENDENT UNION`	`dependent` (`true`)	Second or later `SELECT` statement in a `UNION`, dependent on outer query`【联合查询中的第二个或者更晚的查询，取决于外部查询】`
`UNION RESULT`	`union_result`	Result of a `UNION`. 【联合查询的结果】
`SUBQUERY`	None	First `SELECT` in subquery 【子查询中的第一个查询】
`DEPENDENT SUBQUERY`	`dependent` (`true`)	First `SELECT` in subquery, dependent on outer query【子查询中的第一个查询，取决于外部查询】
`DERIVED`	None	Derived table 【派生表】
`MATERIALIZED`	`materialized_from_subquery`	Materialized subquery 【物化查询】
`UNCACHEABLE SUBQUERY`	`cacheable` (`false`)	A subquery for which the result cannot be cached and must be re-evaluated for each row of the outer query 【子查询的结果无法缓存，必须为外部的每一行查询重新计算】
`UNCACHEABLE UNION`	`cacheable` (`false`)	The second or later select in a `UNION` that belongs to an uncacheable subquery (see `UNCACHEABLE SUBQUERY`) 【联合查询中，第二个或者之后的子查询是不可缓存的】

table：表示输出引用表的名称，可以是以下值之一。json格式化时表示为：table_name!
- 　　
- 　　＜subqueryN>: 指的是ID为N的行的物化子查询的结果。
partitions(json name: partitions): 查询来自哪个分区表，如果没有使用分区表，那么这个值是空的。

type(json name:access_type): 联合查询的类型，可以是如下几个选项：

如果索引是查询的覆盖索引，并且可以用于满足表中所需的所有数据，则只扫描索引树。在这种情况下，Extra列表示使用索引。仅索引扫描通常比全部扫描快，因为索引的大小通常小于表数据。
A full table scan is performed using reads from the index to look up data rows in index order. Uses indexdoes not appear in the Extra column.
system：该表只有一行，这是一个const连接类型的特例。

const：表最多有一个匹配行，该行在查询开始时读取。因为只有一行，所以优化器的其余部分可以将此行列中的值视为常量。const表非常快，因为它们只读一次。const被使用在当你使用主键或者唯一索引和一个常量比较时。

mysql> explain select * from dept_manager where emp_no = 111877;+----+-------------+--------------+------------+------+---------------+---------+---------+-------+------+----------+-------+| id | select_type | table        | partitions | type | possible_keys | key     | key_len | ref   | rows | filtered | Extra |+----+-------------+--------------+------------+------+---------------+---------+---------+-------+------+----------+-------+|  1 | SIMPLE      | dept_manager | NULL       | ref  | PRIMARY       | PRIMARY | 4       | const |    1 |   100.00 | NULL  |+----+-------------+--------------+------------+------+---------------+---------+---------+-------+------+----------+-------+1 row in set, 1 warning (0.00 sec)mysql>

eq_ref:对于前一个表中的每一行记录，从该表中读取一行。除了系统类型和常量类型之外，这是最好的连接类型。当一个索引的所有部分都被联接使用并且该索引是主键或唯一的非空索引时，就使用它． eq_rf可用于使用=运算符比较的索引列。比较值可以是常量，也可以是使用在此表之前读取的表中的列的表达式。

One row is read from this table  each combination of rows from the previous tables. . It is used when all parts of an index are used by the  indexed columns that are compared using the = operator.

mysql> explain select a.emp_no, concat(a.first_name," ", a.last_name) as fullname, a.gender,b.dept_no  from employees as a, dept_manager as b  where a.emp_no = b.emp_no;+----+-------------+-------+------------+--------+---------------+---------+---------+--------------------+------+----------+-------------+| id | select_type | table | partitions | type   | possible_keys | key     | key_len | ref                | rows | filtered | Extra       |+----+-------------+-------+------------+--------+---------------+---------+---------+--------------------+------+----------+-------------+|  1 | SIMPLE      | b     | NULL       | index  | PRIMARY       | dept_no | 12      | NULL               |   24 |   100.00 | Using index ||  1 | SIMPLE      | a     | NULL       | eq_ref | PRIMARY       | PRIMARY | 4       | employees.b.emp_no |    1 |   100.00 | NULL        |+----+-------------+-------+------------+--------+---------------+---------+---------+--------------------+------+----------+-------------+2 rows in set, 1 warning (0.00 sec)#在之前的实例中，表a中我们使用了等号表达式，并且比较值是从上一个表中读取的列值。

ref：对于上一个表中的每一行组合，将从此表中读取具有匹配索引值的所有行。如果联接仅使用索引的最左边前缀，或者索引不是主键或唯一索引（换句话说，如果联接无法基于索引值选择单行），则使用ref。如果所使用的索引只匹配很少的行，那么这是一种很好的连接类型。ref能使用于索引列使用“=”或者“

fulltext：表示使用的是全文索引。

mysql> explain select * from tb3 where match(body) against("please" in natural language mode);+----+-------------+-------+------------+----------+---------------+----------+---------+-------+------+----------+-------------------------------+| id | select_type | table | partitions | type     | possible_keys | key      | key_len | ref   | rows | filtered | Extra                         |+----+-------------+-------+------------+----------+---------------+----------+---------+-------+------+----------+-------------------------------+|  1 | SIMPLE      | tb3   | NULL       | fulltext | ft_index      | ft_index | 0       | const |    1 |   100.00 | Using where; Ft_hints: sorted |+----+-------------+-------+------------+----------+---------------+----------+---------+-------+------+----------+-------------------------------+1 row in set, 1 warning (0.01 sec)＃有关全文索引的知识可以查看博客：https://www.cnblogs.com/wxzhe/p/10233973.html

ref_or_null: 这个连接类型类似于ref，但是除了mysql对包含空值的行进行额外的搜索之外。这种连接类型优化通常用于解析子查询。
```
SELECT * FROM ref_table  WHERE key_column=expr OR key_column IS NULL;
```
index_merge: 这种类型表示优化器使用索引合并。在这种情况下，索引栏包含的是使用索引的列表，而key_len表示使用索引中的最长索引部分。

unique_subquery:此类型在以下某些形式的子查询中替换eq_ref.

value IN (SELECT primary_key FROM single_table WHERE some_expr)#唯一子查询只是一个索引查找函数，它完全替换子查询以提高效率。

index_subquery:这个类型与unique_subquery相似。它替换子查询，但是只有在子查询为非唯一索引的情况下。
```
value IN (SELECT key_column FROM single_table WHERE some_expr)
```
range：只检索给定范围的行，使用索引选择行。key栏在输出行中表示哪种索引被使用，key_len表示的使用索引的最长部分，ref栏是空的。range类型被使用当索引比较使用运算符=,<>,>,>=,<,<=,is null,
index：与all相似，只是扫描了索引树。

　　　　　　MySQL can use this join type when the query uses only columns that are part of a single index

- all：这表示全表扫描。　　
possible_keys(json name: possible_keys): possible_keys列表示MySQL可以选择的索引。注意的是，这个列的值完全独立于explain的输出。这意味着在possible_keys中的显示的索引可能并没有用到。如果这个字段为空（或者json格式中未定义）说明没有相应的索引。在这种情况下，你可以检查where条件语句是否使用了合适的索引来提高性能。如果未空，也就是查询没有使用索引，要么where子句没有使用索引，或者就是表中没有索引。

key(json name: key): 确定实际使用的索引，如果MySQL使用的是possible_keys中索引的一个，那么这个值会被列出。

It  possible that  will name an  that   present  the possible_keys value. This can happen  none  the possible_keys indexes are suitable  looking up rows, but  the columns selected  the query are columns   other . That , the named  covers the selected columns, so although it   used  determine which rows  retrieve, an  scan  more efficient than a data row scan.

key_len(json name: key_length): MySQL使用索引的长度。

ref（json name:ref）：

The ref  shows which columns  constants are compared  the  named  the     rows  the  the value  func, the value used  the result   .  see which ,  SHOW WARNINGS following EXPLAIN  see the extended EXPLAIN output. The  might actually be an operator such  an arithmetic operator.

rows： MySQL扫描的行数，对innodb存储引擎，这个值是不精确的。
filtered（json name:filtered）:过滤率，最大100，表示没有对行进行筛选。
extra：这一行包含了一些额外的信息表示MySQL是怎么样解析查询的。extra的值可以参考官方文档：

explain使用json格式化输出，如下：

mysql> explain format=json select * from employees where emp_no in (select emp_no from dept_manager)\G*************************** 1. row ***************************EXPLAIN: {  "query_block": {    "select_id": 1,    "cost_info": {      "query_cost": "34.69"    },    "nested_loop": [      {        "table": {          "table_name": "dept_manager",          "access_type": "index",          "possible_keys": [            "PRIMARY"          ],          "key": "PRIMARY",          "used_key_parts": [            "emp_no",            "dept_no"          ],          "key_length": "16",          "rows_examined_per_scan": 24,          "rows_produced_per_join": 24,          "filtered": "100.00",          "using_index": true,          "loosescan": true,          "cost_info": {            "read_cost": "1.09",            "eval_cost": "4.80",            "prefix_cost": "5.89",            "data_read_per_join": "576"          },          "used_columns": [            "emp_no"          ]        }      },      {        "table": {          "table_name": "employees",          "access_type": "eq_ref",          "possible_keys": [            "PRIMARY"          ],          "key": "PRIMARY",          "used_key_parts": [            "emp_no"          ],          "key_length": "4",          "ref": [            "employees.dept_manager.emp_no"          ],          "rows_examined_per_scan": 1,          "rows_produced_per_join": 1,          "filtered": "100.00",          "cost_info": {            "read_cost": "24.00",            "eval_cost": "0.20",            "prefix_cost": "34.69",            "data_read_per_join": "104"          },          "used_columns": [            "emp_no",            "birth_date",            "first_name",            "last_name",            "gender",            "hire_date"          ]        }      }    ]  }}1 row in set, 1 warning (0.00 sec)mysql>

更多相关文章

随机推荐