What is the usage of explain in mysql

In mysql, the explain command is mainly used to view the execution plan of the SQL statement. This command can simulate the optimizer to execute the SQL query statement, so as to know how mysql processes the user's SQL statement. The syntax is "explain SQL statement" ;".

The operating environment of this tutorial: windows10 system, mysql8.0.22 version, Dell G3 computer.

What is the usage of explain in mysql

The explain command is mainly used to check the execution plan of the SQL statement, check whether the SQL statement uses indexes, whether a full table scan is performed, etc. It can simulate the optimizer to execute SQL query statements to know how MySQL handles user SQL statements.

In our daily work, we sometimes run slow queries to record some SQL statements that take a long time to execute. Finding these SQL statements does not mean that we are done. Sometimes we often use the explain command. Let’s check the execution plan of one of these SQL statements, check whether the SQL statement uses indexes, and whether a full table scan is performed. This can be checked through the explain command. So we dive deep into MySQL's cost-based optimizer, and we can also get details on many of the access strategies that may be considered by the optimizer, and which strategies are expected to be adopted by the optimizer when running a SQL statement.

-- 实际SQL，查找用户名为Jefabc的员工
select * from emp where name = 'Jefabc';
-- 查看SQL是否使用索引，前面加上explain即可
explain select * from emp where name = 'Jefabc'

The information from expain has 10 columns, namely id, select_type, table, type, possible_keys, key, key_len, ref, rows, Extra

Summary description:

id: Select identifier

select_type: Indicates the type of query.

table: The table that outputs the result set

partitions: the matching partition

type: indicates the connection type of the table

possible_keys: indicates the possibility during query Index used

key: Indicates the actual index used

key_len: The length of the index field

ref: Comparison of columns and indexes

rows: Scan Number of rows out (estimated number of rows)

filtered: Percentage of rows filtered by table conditions

Extra: Description and explanation of the execution

These fields are described below Possible explanations may appear:

1. id

SELECT identifier. This is the query sequence number of SELECT

My understanding is that it identifies the order of SQL execution. SQL is executed from large to small

1. When the id is the same, the execution order is from top to bottom

2. If it is a subquery, the serial number of the id will be incremented. The larger the id value, the higher the priority and the earlier it will be executed.

3. If the id is the same, it can be considered as a group. Execute in order from top to bottom; in all groups, the larger the id value, the higher the priority, and the earlier it is executed

-- 查看在研发部并且名字以Jef开头的员工，经典查询
explain select e.no, e.name from emp e left join dept d on e.dept_no = d.no where e.name like 'Jef%' and d.name = '研发部';

2. select_type

Indicates the type of each select clause in the query

(1) SIMPLE (simple SELECT, does not use UNION or subquery, etc.)

(2) PRIMARY (the outermost query in the subquery, if the query contains any complex subparts , the outermost select is marked as PRIMARY)

(3) UNION(the second or subsequent SELECT statement in UNION)

(4) DEPENDENT UNION(the third SELECT statement in UNION) Two or more SELECT statements, depending on the external query)

(5) UNION RESULT (the result of UNION, the second select in the union statement starts all subsequent selects)

(6 ) SUBQUERY(The first SELECT in the subquery, the result does not depend on the external query)

(7) DEPENDENT SUBQUERY(The first SELECT in the subquery, the result does not depend on the external query)

(8) DERIVED (SELECT of the derived table, subquery of the FROM clause)

(9) UNCACHEABLE SUBQUERY (The results of a subquery cannot be cached, and the first row of the external link must be re-evaluated)

3. table

Displays the name of the table in the database accessed in this step (shows which table the data in this row is about). Sometimes it is not the real table name, it may be the abbreviation, such as the above e, d, may also be the abbreviation of the result of the execution of several steps

4. type

The table access method indicates the way MySQL finds the required rows in the table, also known as "Access type".

Commonly used types are: ALL, index, range, ref, eq_ref, const, system, NULL (from left to right, performance from poor to good)

ALL: Full Table Scan, MySQL will traverse the entire table to find matching rows

index: Full Index Scan, the difference between index and ALL is that the index type only traverses the index tree

range: only retrieves rows in a given range, use An index to select rows

ref: Indicates the connection matching conditions of the above table, that is, which columns or constants are used to find the value on the index column

eq_ref: Similar to ref, the difference lies in the use The index is a unique index. For each index key value, only one record in the table matches. To put it simply, the primary key or unique key is used as the association condition in multi-table connections.

const, system: When MySQL Use these types of access when optimizing part of a query and converting it to a constant. If the primary key is placed in the where list, MySQL can convert the query into a constant. System is a special case of the const type. When the queried table has only one row, use system

NULL: MySQL is optimizing Decomposing statements in the process does not even require access to tables or indexes during execution. For example, selecting the minimum value from an index column can be completed through a separate index lookup.

五、possible_keys

指出MySQL能使用哪个索引在表中找到记录，查询涉及到的字段上若存在索引，则该索引将被列出，但不一定被查询使用（该查询可以利用的索引，如果没有任何索引显示 null）

该列完全独立于EXPLAIN输出所示的表的次序。这意味着在possible_keys中的某些键实际上不能按生成的表次序使用。

如果该列是NULL，则没有相关的索引。在这种情况下，可以通过检查WHERE子句看是否它引用某些列或适合索引的列来提高你的查询性能。如果是这样，创造一个适当的索引并且再次用EXPLAIN检查查询

六、Key

key列显示MySQL实际决定使用的键（索引），必然包含在possible_keys中

如果没有选择索引，键是NULL。要想强制MySQL使用或忽视possible_keys列中的索引，在查询中使用FORCE INDEX、USE INDEX或者IGNORE INDEX。

七、key_len

表示索引中使用的字节数，可通过该列计算查询中使用的索引的长度（key_len显示的值为索引字段的最大可能长度，并非实际使用长度，即key_len是根据表定义计算而得，不是通过表内检索出的）

不损失精确性的情况下，长度越短越好

八、ref

列与索引的比较，表示上述表的连接匹配条件，即哪些列或常量被用于查找索引列上的值

九、rows

估算出结果集行数，表示MySQL根据表统计信息及索引选用情况，估算的找到所需的记录所需要读取的行数

十、Extra

该列包含MySQL解决查询的详细信息,有以下几种情况：

Using where:不用读取表中所有信息，仅通过索引就可以获取所需数据，这发生在对表的全部的请求列都是同一个索引的部分的时候，表示mysql服务器将在存储引擎检索行后再进行过滤

Using temporary：表示MySQL需要使用临时表来存储结果集，常见于排序和分组查询，常见 group by ; order by

Using filesort：当Query中包含 order by 操作，而且无法利用索引完成的排序操作称为“文件排序”

-- 测试Extra的filesort
explain select * from emp order by name;

Using join buffer：改值强调了在获取连接条件时没有使用索引，并且需要连接缓冲区来存储中间结果。如果出现了这个值，那应该注意，根据查询的具体情况可能需要添加索引来改进能。

Impossible where：这个值强调了where语句会导致没有符合条件的行（通过收集统计信息不可能存在结果）。

Select tables optimized away：这个值意味着仅通过使用索引，优化器可能仅从聚合函数结果中返回一行

No tables used：Query语句中使用from dual 或不含任何from子句

-- explain select now() from dual;

总结：

• EXPLAIN不会告诉你关于触发器、存储过程的信息或用户自定义函数对查询的影响情况

• EXPLAIN不考虑各种Cache

• EXPLAIN不能显示MySQL在执行查询时所作的优化工作

• 部分统计信息是估算的，并非精确值

• EXPALIN只能解释SELECT操作，其他操作要重写为SELECT后查看执行计划。

通过收集统计信息不可能存在结果

推荐学习：mysql视频教程

The above is the detailed content of What is the usage of explain in mysql. For more information, please follow other related articles on the PHP Chinese website!