explain usage and result analysis in MySQL (detailed explanation)

1. Introduction to EXPLAIN

Using the EXPLAIN keyword can simulate the optimizer's execution of SQL query statements, so as to know how MySQL processes your SQL statements. Analyze the performance bottlenecks of your query statements or table structures.
➤ Through EXPLAIN, we can analyze the following results:

• The reading sequence of the table
• The operation type of the data reading operation
• Which indexes can be used
• Which indexes are actually used
• References between tables
• How many rows of each table are queried by the optimizer

➤ Used as follows:

EXPLAIN SQL statement

EXPLAIN SELECT * FROM t1

Information contained in the execution plan

2. The meaning of each field in the execution plan

2.1 id

The sequence number of the select query, including a set of numbers, indicating the order in which select clauses or operation tables are executed in the query

There are 3 cases for the result of id

• #The id is the same, the execution order is from top to bottom
  
  [Summary] The order of loading the table is as shown in the table column above: t1 t3 t2

• id is different. If it is a subquery, the sequence number of the id will increase, and the larger the id value is. The higher the priority, the sooner it will be executed

• 
  
  

• ## As shown in the figure, when the id is 1, the table displays

  , which refers to the table with id 2, that is, the derived table of the t3 table.

• 2.2 select_typeCommon and commonly used values ​​are as follows:

  are used to indicate the type of query, mainly to distinguish between ordinary Complex queries such as queries, union queries, subqueries, etc.

• SIMPLE Simple select query

  , the query
does not contain subqueries or UNION

• If in a PRIMARY query contains any complex

  subparts,
the outermost query is marked as PRIMARY
##SUBQUERY
• in SELECT Or the WHERE list contains the subquery

DERIVED The

subquery contained in the FROM list is marked as DERIVED

(derivative), and MySQL will execute it recursively These subqueries put the

results in the temporary table

UNION If the second SELECT appears after UNION, it is marked as UNION: If UNION is included in In the subquery of the FROM clause, the outer SELECT will be marked as: DERIVED

UNION RESULT SELECT

###### that obtains the result from the UNION table 2.3 table###### refers to the currently executed table######2.4 type######type shows which type is used in the query. The types included in type include as shown in the figure below Several types: #########From best to worst are: ###

system > const > eq_ref > ref > range > index > all

###### Generally speaking, it is necessary to ensure that the query reaches at least the range level, and preferably reaches the ref. ######

• system The table has only one row of records (equal to the system table). This is a special column of const type. It does not usually appear and this can be ignored.
• const means that it is found through the index once, and const is used to compare the primary key or unique index. Because only one row of data is matched, it is very fast. If you place the primary key in the where list, MySQL can convert the query into a constant.
  
  First perform a subquery to obtain a result of the d1 temporary table. The subquery condition is id = 1, which is a constant, so the type is const. The id of 1 is equivalent to querying only one record, so the type is system.
• eq_ref Unique index scan, for each index key, only one record in the table matches it. Commonly seen in primary key or unique index scans
• ref Non-unique index scans return all rows that match a single value. This is essentially an index access. It returns all rows that match a certain value. For single value rows, however, it may find multiple matching rows, so it should be a mix of find and scan.
  
• range Retrieve only rows in a given range, use an index to select rows, the key column shows which index is used, usually in your where statement For queries such as between, , in, etc., this range scan index is better than a full table scan, because it only needs to start at a certain point in the index and end at another point, without scanning the entire index.
  
• index Full Index Scan, the difference between Index and All is that the index type only traverses the index tree. This is usually faster than ALL because index files are usually smaller than data files. (That is to say, although all and Index both read the entire table, index is read from the index, and all is read from the hard disk)
  
  id is the primary key, so there is a primary key index
• all Full Table Scan will traverse the entire table to find matching rows
  

2.5 possible_keys and key

possible_keys Displays one or more indexes that may be applied to this table. If an index exists on the field involved in the query, the index will be listed, but may not be actually used by the query.

key

• The actual index used. If it is NULL, no index is used. (Possible reasons include no index being created or index failure)
  
• If covering index is used in the query (the field to be queried after selecting is exactly the same as the created index field) the same), the index only appears in the key list
  
  

2.6 key_len

represents the number of bytes used in the index, This column can be used to calculate the length of the index used in the query. The shorter the length, the better without losing accuracy. The value displayed by key_len is the maximum possible length of the index field, not the actual length used. That is, key_len is calculated based on the table definition, not retrieved from the table.

2.7 ref

The column showing the index is used, preferably a constant if possible. Which columns or constants are used to find the value on the index column.

2.8 rows

According to table statistics and index selection, roughly estimate the number of rows that need to be read to find the required records, that is, use The less the better

2.9 Extra

Contains important additional information that is not suitable for explicit use in other columns

2.9.1 Using filesort (narrow escape)

Indicates that mysql will use an external index to sort the data instead of reading it in the index order within the table. The sorting operation that cannot be completed using indexes in MySQL is called "file sorting".

2.9.2 Using temporary（十死无生）

使用了用临时表保存中间结果，MySQL在对查询结果排序时使用临时表。常见于排序order by和分组查询group by。

2.9.3 Using index（发财了）

表示相应的select操作中使用了覆盖索引（Covering Index），避免访问了表的数据行，效率不错。如果同时出现using where，表明索引被用来执行索引键值的查找；如果没有同时出现using where，表明索引用来读取数据而非执行查找动作。

2.9.4 Using where

表明使用了where过滤

2.9.5 Using join buffer

表明使用了连接缓存,比如说在查询的时候，多表join的次数非常多，那么将配置文件中的缓冲区的join buffer调大一些。

2.9.6 impossible where

where子句的值总是false，不能用来获取任何元组

SELECT * FROM t_user WHERE id = '1' and id = '2'

2.9.7 select tables optimized away

在没有GROUPBY子句的情况下，基于索引优化MIN/MAX操作或者对于MyISAM存储引擎优化COUNT(*)操作，不必等到执行阶段再进行计算，查询执行计划生成的阶段即完成优化。

2.9.8 distinct

优化distinct操作，在找到第一匹配的元组后即停止找同样值的动作

3. 实例分析

• 执行顺序1：select_type为UNION，说明第四个select是UNION里的第二个select，最先执行【select name,id from t2】
• 执行顺序2：id为3，是整个查询中第三个select的一部分。因查询包含在from中，所以为DERIVED【select id,name from t1 where other_column=’’】
• 执行顺序3：select列表中的子查询select_type为subquery,为整个查询中的第二个select【select id from t3】
• 执行顺序4：id列为1，表示是UNION里的第一个select，select_type列的primary表示该查询为外层查询，table列被标记为<derived3></derived3>,表示查询结果来自一个衍生表，其中derived3中的3代表该查询衍生自第三个select查询，即id为3的select。【select d1.name …】

• 执行顺序5：代表从UNION的临时表中读取行的阶段，table列的表示用第一个和第四个select的结果进行UNION操作。【两个结果union操作】

推荐学习：mysql教程

The above is the detailed content of explain usage and result analysis in MySQL (detailed explanation). For more information, please follow other related articles on the PHP Chinese website!