Summary of usage of Explain in MySQL (detailed)

This article brings you a summary of the usage of Explain in MySQL (detailed). It has certain reference value. Friends in need can For reference, I hope it will be helpful to you.

Execution plan (query Execution plan)

Syntax

explain select * from table

The columns in explain

expain has 10 columns of information ,
are id, select_type, table, type, partitions, possible_keys, key, key_len, ref, rows, Extra. The following is a possible explanation of the occurrence of these fields:

1. ID

Identification of 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 increase. The larger the ID value, the higher the priority and the first it will be executed.

3. If the IDs are the same, they can be considered as a group and executed sequentially from top to bottom; Among all groups, the larger the ID value, the higher the priority, and the earlier it is executed

2. select_type

Indicates the type of each select clause in the query

1. SIMPLE: Simple SELECT, UNION or subquery is not practical.

2. PRIMARY: Outermost SELECT.

3. UNION: In the second layer, UNION is used after SELECT.

4. DEPENDENT UNION: The second SELECT in the UNION statement depends on the external subquery.

5. UNION RESULT: The result of UNION.

6. SUBQUERY: The first SELECT in the subquery.

7. DEPENDENT SUBQUERY: The first SELECT in the subquery depends on the outer query.

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

9. MATERIALIZED: materialized subquery

10. UNCACHEABLE SUBQUERY: Unable to cache the results Subquery must be recalculated for each row of the outer query

11. UNCACHEABLE UNION: UNION belongs to the second or subsequent selection of a non-cacheable subquery

3. table

The name of the table referenced by the output row. This can also be one of the following values:

• M,N,...>: This row refers to the union of id value M and id value N.

• N>: This row refers to the value N used for the derived table result id with this row. For example, a derived table can come from a subquery

• N> in the FROM clause: the row refers to the id The result of the materialized subquery for the row with value N

4. type

represents the way MySQL finds the required row in the table , also known as "access type".

Commonly used types are: NULL, system, const, eq_ref, ref, range, index, ALL (from left to right, performance from worst to best)
The following list describes from the best type to The worst type of connection type

NULL
MySQL decomposes the statement during the optimization process, and does not even need to access the table or index during execution. For example, selecting the minimum value from an index column can be done by Individual index lookup is completed.

system
This table has only one row (for example: system table). This is a special case of the const join type

const
The table has at most one matching row, which is read at the beginning of the query. Because there is only one row, the rest of the optimizer can treat the values ​​of the columns in this row as constants. const tables are very fast because they are read only once.

SELECT * FROM tbl_name WHERE primary_key=1;
SELECT * FROM tbl_name WHERE primary_key_part1=1 AND primary_key_part2=2;

eq_ref
For each combination of rows in the previous table, read one row from the table. Besides system and const types, this is the best connection type. It is used when the join uses all parts of the index and the index is a PRIMARY KEY index or a UNIQUE NOT NULL index.

SELECT * FROM ref_table,other_table
  WHERE ref_table.key_column=other_table.column;

SELECT * FROM ref_table,other_table
    WHERE ref_table.key_column_part1=other_table.column
    AND ref_table.key_column_part2=1;

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

fulltext
Use FULLTEXT index to perform the join.

ref_or_null

SELECT * FROM ref_table WHERE key_column IS NULL;

index_merge
The index merge access method retrieves multiple rows with range scans and merges their results into one. This access method only combines index scans from a single table and does not scan multiple tables. A merge can produce a union, cross, or cross-union of its underlying scan

SELECT * FROM tbl_name WHERE key1 = 10 OR key2 = 20;

SELECT * FROM tbl_name
WHERE (key1 = 10 OR key2 = 20) AND non_key = 30;

SELECT * FROM t1, t2
WHERE (t1.key1 IN (1,2) OR t1.key2 LIKE 'value%')
AND t2.key1 = t1.some_col;

SELECT * FROM t1, t2
WHERE t1.key1 = 1
AND (t2.key1 = t1.some_col OR t2.key2 = t1.some_col2);

unique_subquery
This type replaces some IN subquery with eq_ref of the form:

value IN (SELECT primary_key FROM single_table WHERE some_expr)

index_subquery
This connection type is similar to unique_subquery. It replaces the IN subquery, but it works with non-unique indexes in a subquery of the form:

value IN (SELECT key_column FROM single_table WHERE some_expr)

range
Retrieve only rows within a given range, using index selection OK. The key column in the output row indicates which index to use. Contain key_len to contain the key part that has been used the longest. The ref column NULL is suitable for this type.
range When a key column uses any value compared to constant, you can use =, , >, >=, , BETWEEN, LIKE , or IN() operator:

index
The index join type is the same as ALL, except that the index tree is scanned. There are two situations:

1. The index tree is only scanned if the index is a covering index for the query and can be used to satisfy all the data required in the table. In this case, the Extra column says Using index. Index-only scans are generally faster than ALL indexes, which are typically smaller in size than the table data.

2. Use reads in the index to perform a full table scan to find data rows in index order. Uses index does not appear in the Extra column. MySQL can use this connection type when the query uses only columns belonging to a single index.

ALL
Perform a full table scan for each row combination in the previous table. Usually bad if the table is the first one not marked const, and usually very bad in all other cases. Typically, you can avoid ALL this by adding an index that enables row retrieval from the table based on a constant value or a column value from an earlier table. The column indicates the index that MySQL can choose to find rows in this table. It indicates which index MySQL can use to find the record in the table. If an index exists on the field involved in the query, the index will be listed, but it will not necessarily be used by the query.

This column is completely independent of the order of the tables shown in the EXPLAIN output. This means that some keys in possible_keys cannot actually be used in the generated table order. If the column is NULL, there is no related index. In this case, you can improve the performance of your query by checking the WHERE clause to see if it refers to certain columns or columns that are suitable for indexing. If so, create an appropriate index and check the query again with EXPLAIN

6. Key

Thekey column displays the key (index) that MySQL actually decided to use

If no index is selected, the key is NULL. To force MySQL to use or ignore the index on the possible_keys column, use FORCE INDEX, USE INDEX, or IGNORE INDEX in the query.

indicates 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 value displayed by key_len is the index field The maximum possible length is not the actual length used, that is, key_len is calculated based on the table definition, not retrieved from the table)

Without loss of accuracy, the shorter the length, the better

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

9 , rows

indicates that MySQL estimates the number of rows that need to be read to find the required records based on table statistics and index selection

10. Extra

The Extra column EXPLAIN output contains additional information for MySQL to solve the query. The following list describes the possible values ​​in this column. Each item also indicates to the JSON-formatted output which property displays the Extra value. For some of them, there is a specific property. Other text displayed as message attribute

11. partitions (extension)

Record the partitions that will match the query. This column is only displayed when using the PARTITIONS keyword. The non-partitioned table displays null

This article ends here. For more knowledge about MySQL, you can pay attention to the MySQL Tutorial column on the php Chinese website! ! !

The above is the detailed content of Summary of usage of Explain in MySQL (detailed). For more information, please follow other related articles on the PHP Chinese website!