What is the introduction of MySQL explain? 03/29 Update SLTechnology News&Howtos

What is the introduction of MySQL explain?

2025-03-29 Update From: SLTechnology News&Howtos shulou NAV: SLTechnology News&Howtos > Database >

Shulou(Shulou.com)05/31 Report--

In this issue, the editor will bring you an introduction about MySQL explain. The article is rich in content and analyzes and narrates it from a professional point of view. I hope you can get something after reading this article.

MySQL explain detailed explanation

Get the execution plan of the select statement through the explain command, and through explain we can know the following information: the reading order of the table, the type of data read operation, which indexes can be used, which indexes are actually used, the references between tables, and how many rows in each table are queried by the optimizer.

There are 10 columns of explain information, namely id,select_type,table,type,possible_keys,key,key_len,ref,row,Extra

Id:select identifier, select query serial number

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

two。 If it is a subquery, the sequence number of the id will be incremented. The higher the id value, the higher the priority, and the more it will be executed first.

3. If the id is the same, it can be considered as a group, which is executed sequentially from top to bottom. In all groups, the higher the id value is, the higher the priority is, and the first execution is.

Select_type: represents the type of each select clause

(1) SIMPLE (simple SELECT, no UNION or subquery, etc.)

(2) PRIMARY (outermost query in a subquery. If the query contains any complex subparts, the outermost select is marked PRIMARY)

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

(4) DEPENDENT UNION (the second or subsequent SELECT statement in UNION, depending on the external query)

(5) UNION RESULT (result of UNION, all select following the start of the second select in the union statement)

(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, depending on the external query)

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

(9) UNCACHEABLE SUBQUERY (the result of a subquery cannot be cached and the first line of the outer link must be reevaluated)

Description of select_type

UNION:

When multiple query results are joined through union, the select_type of the second select is UNION

[yoon] > explain select * from tb1 where id=1 union select * from tb1 where id=2

+-- +

+-- +

+-- +

DEPENDENT UNION and DEPENDENT SUBQUERY:

When union is used as a subquery, the select_type of the second union is DEPENDENT UNION. The select_type of the first subquery is DEPENDENT SUBQUERY.

[yoon] > explain select * from tb1 where id in (select id from tb1 where id=7 union select id from tb1 where id=9)

+-- +

+-- +

+-- +

SUBQUERY:

The select_type of the first select in the subquery is SUBQUERY.

[yoon] > explain select * from tb1 where id= (select id from tb1 where id=7)

+-- +

+-- +

+-- +

DERIVED:

When a subquery is a from clause, its select_type is DERIVED

[yoon] > explain select * from (select id from tb1 where id=7) a

+-- +

+-- +

+-- +

Table: the table referenced by the output row, or it may be an alias for the table

Type: type of access to the table

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

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

2.const: the table has at most one matching row, which will be read at the beginning of the query. Because there is only one row, the column values in this row can be considered constant by the rest of the optimizer. Const tables are fast because they are only read once!

3.eq_ref: for each combination of rows from the previous table, read a row from that table. This is probably the best join type, except for the const type.

4.ref: for each row combination from the previous table, all rows with matching index values will be read from this table.

5.ref_or_null: this join type is like ref, but MySQL is added to specifically search for rows that contain null values.

6.index_merge: this join type indicates that the index merge optimization method is used.

7.unique_subquery: this type replaces the ref of the in subquery in the following form: value IN (SELECT primary_key FROM single_table WHERE some_expr) unique_subquery is an index lookup function that completely replaces the subquery and is more efficient.

8.index_subquery: this join type is similar to unique_subquery. In subqueries can be replaced, but only for non-unique indexes in the following form of subqueries: value IN (SELECT key_column FROM single_table WHERE some_expr)

9.range: only retrieve a given range of rows, using an index to select rows.

10.index: this join type is the same as ALL, except that only the index tree is scanned. This is usually faster than ALL because index files are usually smaller than data files.

11.ALL: perform a complete table scan for each row combination from the previous table.

12.NULL: MySQL breaks up statements during optimization and executes without even accessing tables or indexes. For example, selecting a minimum value from an index column can be done through a separate index lookup.

Description of type:

1.system,const

The type of the first line is system, and the second line is const, and these two join types are the fastest.

2.eq_ref

The order_id in the t _ order table is the primary key, and the order_id in the t _ order _ exttable is also the primary key. This table can be thought of as a supplementary information table for the order table, and their relationship is 1 to 1. In the following example, we can see that the join type of table b is eq_ref, which is a very fast join type.

[yoon] > explain select * from t_order a where a.order_id=b.order_id

+-- +

+-- +

| | 1 | SIMPLE | b | ALL | order_id | NULL | NULL | NULL | 1 |

+-- +

3.ref

The following example is slightly modified and conditioned on the above example. At this point the join type of table b becomes ref. Because all matching records with order_id=100 in table a will be obtained from table b. This is a more common type of join.

[yoon] > explain select * from t_order a where a.order_id=b.order_id and a.order_id=100

+-- +

+-- +

| | 1 | SIMPLE | a | const | PRIMARY | PRIMARY | 4 | const | 1 |

| | 1 | SIMPLE | b | ref | order_id | order_id | 4 | const | 1 |

+-- +

4.ref_or_null

The user_id field is a field that can be empty, and an index is created on the field. You can see in the following query that the join type is ref_or_null, which mysql does specifically for fields that contain null. In our table design, we should try to avoid the index field of NULL, as this will take extra processing time of mysql to optimize.

[yoon] > explain select * from t_order where user_id=100 or user_id is null

+-- +

+-- +

+-- +

5.index_merge

It often occurs when using multiple indexes in a table. Mysql merges multiple indexes, such as the following example:

[yoon] > explain select * from t_order where order_id=100 or user_id=10

+- -- +

+- -- +

+- -- +

6.unique_subquery

This join type is used to replace the ref of subqueries such as value IN (SELECT primary_key FROM single_table WHERE some_expr). Notice the ref column, where the second row shows func, indicating that unique_subquery is a function, not a normal ref.

[yoon] > explain select * from t_order where order_id in (select order_id from t_order where user_id=10)

+-- +

+-- +

+-- +

7.index_subquery

The join type is too similar to the one above, except that the subquery looks not for the primary key but for the non-unique index.

[yoon] > explain select * from t_order where user_id in (select user_id from t_order where order_id > 10)

+- -+

+- -+

+- -+

8.range

It is common to retrieve by a specified range.

[yoon] > explain select * from t_order where user_id in (100200300)

+-- +

+-- +

+-- +

9.index

Very common when doing statistics, this join type actually scans the index tree, only faster than ALL.

[yoon] > explain select count (*) from t_order

+-- +

+-- +

+-- +

10.ALL

Complete scan of the whole table, the slowest join type, as far as possible to avoid.

[yoon] > explain select * from t_order

+-- +

+-- +

+-- +

Possible_keys

Indicates which index MySQL can use to find records in the table, and if there is an index on the fields involved in the query, the index will be listed, but not necessarily used by the query (indexes that can be used by the query, if no indexes show null)

Key

The key column shows the key (index) that MySQL actually decides to use, which must be included in the possible_keys

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

Key_len

Indicates the number of bytes used in the index, which can be used to calculate the length of the index used in the query (the value displayed by key_len is the maximum possible length of the index field, not the actual used length, that is, key_len is calculated according to the table definition, not retrieved within the table)

Without losing accuracy, the shorter the length, the better.

Ref

The comparison between the column and the index indicates the join matching condition of the above table, that is, which columns or constants are used to find the value on the index column

Rows

Displays the number of rows that MySQL thinks it checks when executing the query. Multiplying the same set of data between multiple rows can estimate the number of rows to be processed and the addition of different groups

Extra

This column contains the details of the MySQL resolution query

When Distinct:MySQL finds the first matching row, it stops searching for more rows for the current row combination.

Not exists:MySQL can LEFT JOIN optimize the query, and after finding a row that matches the LEFT JOIN standard, no more rows are checked in the table for the previous combination of rows.

Range checked for each record (index map: #): MySQL did not find a good index to use, but found that some indexes might be available if the column values from the previous table were known.

Using filesort:MySQL needs an extra pass to figure out how to retrieve rows in sort order.

Using index: retrieve the column information in the table by reading the actual rows from using only the information in the index tree without further search.

Using temporary: to solve the query, MySQL needs to create a temporary table to hold the results.

The Using where:WHERE clause is used to restrict which row matches the next table or is sent to the customer.

Using sort_union (...), Using union (...), Using intersect (...): these functions show how to merge index scans for index_merge join types.

Using index for group-by: similar to the Using index way of accessing tables, Using index for group-by indicates that MySQL has found an index that can be used to query all columns of a GROUP BY or DISTINCT query without additional searching the hard disk to access the actual table.

Description of extra

1.Distinct

When MySQL finds the first matching row, it stops searching for more rows for the current row combination. No suitable examples have been found for this item. Ask for advice.

2.Not exists

Because the order_id in the b table is the primary key, it cannot be NULL, so when mysql scans the t _ order table with the order_id of table an and looks for the row of the b table, if it finds a matching row in the b table, it will no longer scan b, because the order_id field in the b table cannot be NULL. This avoids multiple scans of the b table.

Mysql > explain select count (1) from t_order a left join t_order_ext b on a.order_id=b.order_id where b.order_id is null

+- -- +

+- -- +

| | 1 | SIMPLE | b | ref | order_id | order_id | 4 | test.a.order_id | 1 | Using where; Using index; Not exists |

+- -- +

2 rows in set (0.01sec)

3.Range checked for each record

In this case, mysql does not find a good index available, which is much faster than no index at all.

Mysql > explain select * from t_order t, t_order_ext s where s.order_id > = t.order_id and s.order_id5

+- -- +

+- -- +

+- -- +

2 rows in set (0.00 sec)

4.Using filesort

It is a common situation when there is a sort clause. At this point, mysql browses all eligible records according to the join type, saves the sort keyword and row pointer, and then sorts the keywords and retrieves the rows in order.

Mysql > explain select * from t_order order by express_type

+-- +

+-- +

+-- +

1 row in set (0.00 sec)

5.Using index

This is a case of high performance. Occurs when the data needed for the query can be retrieved directly from the index tree. There are many such examples in the above examples, so there are no more examples.

6.Using temporary

In general, this situation needs to be optimized. Mysql needs to create a temporary table to process such queries.

Mysql > explain select * from t_order a left join t_order_ext b on a.order_id=b.order_id group by b.order_id

+- -+

+- -+

| | 1 | SIMPLE | b | ref | order_id | order_id | 4 | test.a.order_id | 1 |

+- -+

2 rows in set (0.00 sec)

7.Using where

When there is a where clause, extra will have an explanation.

8.Using sort_union (...) / Using union (...) / Using intersect (...)

In the following example, user_id is a retrieval scope, where mysql uses the sort_union function to merge the indexes. When user_id is a fixed value, see the above type example of 5.index_merge, in which case the union function is used for index merging.

Mysql > explain select * from t_order where order_id=100 or user_id > 10

+- -- +

+- -- +

+- -- +

1 row in set (0.00 sec)

For the example of Using intersect, see the following example, where user_id and express_type have index cross-merging.

Mysql > explain select * from t_order where express_type=1 and user_id=100

+- -+

+- -+

+- -+

1 row in set (0.00 sec)

9.Using index for group-by

Indicates that all the data needed for the grouping can be found in the index without the need to query the actual table.

Mysql > explain select user_id from t_order group by user_id

+-+-

+-+-

+-+-

The above is the introduction of MySQL explain shared by the editor. If you happen to have similar doubts, you might as well refer to the above analysis to understand. If you want to know more about it, you are welcome to follow the industry information channel.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.

*The comments in the above article only represent the author's personal views and do not represent the views and positions of this website. If you have more insights, please feel free to contribute and share.