How to use explain in mysql 02/24 Update SLTechnology News&Howtos

How to use explain in mysql

2025-02-24 Update From: SLTechnology News&Howtos shulou NAV: SLTechnology News&Howtos > Database >

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

This article shows you how to use explain in mysql. The content is concise and easy to understand. It will definitely brighten your eyes. I hope you can get something through the detailed introduction of this article.

The point is the second usage, which requires in-depth understanding.

Let's first look at an example:

+-- +

+-- +

1 row in set (0.03 sec)

After adding extended:

+-- +

+-- +

1 row in set, 1 warning (0.00 sec)

It is necessary to explain the meaning of each column in this long table:

IdSELECT identifier. This is SELECT's query serial number select_type.

SELECT type, which can be any of the following:

SIMPLE: simple SELECT (no UNION or subquery)

PRIMARY: outermost SELECT

The second or subsequent SELECT statement in UNION:UNION

The second or subsequent SELECT statement in DEPENDENT UNION:UNION, depending on the external query

Results of UNION RESULT:UNION

SUBQUERY: the first SELECT in the subquery

DEPENDENT SUBQUERY: the first SELECT in the subquery, depending on the external query

DERIVED: export the SELECT of the table (a subquery of the FROM clause)

Table

The table referenced by the output row

Type

Join type. Here are the various join types, sorted from the best type to the worst type:

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

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!

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.

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

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

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

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.

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)

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

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.

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

Possible_keys

Indicates which index MySQL can use to find rows in the table

Key displays the key (index) that MySQL actually decides to use. If no index is selected, the key is NULL. Key_len displays the key length that MySQL decides to use. If the key is NULL, the length is NULL. Ref shows which column or constant is used to select rows from the table with key. Rows displays the number of rows that MySQL thinks it must check when executing the query. Multiplying data between multiple rows can estimate the number of rows to be processed. Filtered shows the percentage estimate of the number of rows filtered by the condition. 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.

I. description of select_type

1.UNION:

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

+-- +

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.

+-- +

4 rows in set (0.03 sec)

3.SUBQUERY:

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

+-- +

+-- +

2 rows in set (0.03 sec)

4.DERIVED:

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

+-- +

| | 1 | PRIMARY | +-- + |

2 rows in set (0.03 sec)

II. Description of type

1.system,const

See the example of 4.DERIVED above. The type of the first row 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, you can see that the join type of table b is eq_ref, which is a very fast join type.

+-- +

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

+-- +

2 rows in set (0.00 sec)

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.

+-- +

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

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

+-- +

2 rows in set (0.00 sec)

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.

+-- +

+-- +

1 row in set (0.00 sec)

5.index_merge

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

+- -- +

+- -- +

1 row in set (0.09 sec)

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.

+-- +

+-- +

2 rows in set (0.00 sec)

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.

+- -+

+- -+

2 rows in set (0.00 sec)

8.range

It is common to retrieve by a specified range.

+-- +

+-- +

1 row in set (0.00 sec)

9.index

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

+-- +

+-- +

1 row in set (0.00 sec)

10.ALL

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

+-- +

+-- +

1 row in set (0.00 sec)

III. Description of the story.

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.

+- -- +

| | 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.

+- -- +

+- -- +

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.

+-- +

+-- +

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.

+- -+

| | 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.

+- -- +

+- -- +

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.

+- -+

+- -+

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.

+-+-

+-+-

1 row in set (0.00 sec)

The above is how to use explain in mysql. Have you learned any knowledge or skills? If you want to learn more skills or enrich your knowledge reserve, 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.