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

How to view the implementation plan in explain? for this question, this article introduces the corresponding analysis and answer in detail, hoping to help more partners who want to solve this problem to find a more simple and feasible way.

Explain content

View the execution plan of the user full table query:

Mysql > explain select * from user

Id

Id represents the order in which select query statements are executed or the order in which operation tables are executed.

There are two cases of the value of id:

Hongmeng official Strategic Cooperation to build HarmonyOS Technology Community

Id is the same, indicating that the order of execution is from top to bottom

Id is different, the sequence number of id will be incremented, the higher the value of id, the higher the priority, the first to be executed.

Select_type

Select_type represents the query type, and its values are: SIMPLE, PRIMARY, SUBQUERY, DERIVED, UNION, UNION RESULT.

SIMPLE: a simple query, excluding subqueries and UNION.

PRIMARY: if the statement of the query contains complex subqueries, the outermost query is marked PRIMARY.

A subquery in SUBQUERY:select or where.

A derived table query generated by a subquery in the DERIVED:from list.

UNION: Federated query.

UNION RESULT: the query that gets the results from the UNION table.

Table

From which table the data rows are obtained.

Type

Indicates which type of query is used and its range: ALL, index, range, ref, eq_ref, const, system. This is an indicator that we focus on when we optimize.

Sort from the best to the worst:

System > const > eq_ref > ref > range > index > ALL.

Alibaba SQL Optimization Specification:

[recommended] the goal of SQL performance optimization: at least range level is required, ref level is required, and const is the best if possible.

Description:

1) there is at most one matching row (primary key or unique index) in a single const table, and the data can be read in the optimization phase.

2) ref refers to the use of a normal normal index.

3) range searches the range of the index.

System: a query with only one row of records in a table, similar to a system table.

Eq_ref: unique index scan when there are multiple tables. For each index, only one row of records in the table matches.

Index: index physical files are fully scanned, the speed is relatively slow, and the performance is a little better than that of full table scanning.

ALL: full table scan.

Possible_keys

The indexes that may be applied to this table can be one or more, not necessarily the indexes used by the actual query.

Key

The index actually used in the query, if null means that the index is not used.

If the index is overwritten, it is displayed only in key.

Key_len

Represents the maximum possible length of the index field, not the actual length, it is based on the table definition, not retrieved by the table. The shorter the length, the better.

Ref

Indicates which column of the index is used, which may be a constant. Which columns or constants are used to find values on indexed columns.

Rows

Based on the information of the table and index usage, roughly estimate the number of rows that need to be scanned to find the required records. The smaller the better.

Extra

Expand the information.

Alibaba SQL Optimization Specification:

[recommended] use overlay index for query operation to avoid returning to the table.

Explanation: if a book needs to know what the title of Chapter 11 is, will it turn to the corresponding page of Chapter 11? Just browse through the catalog, which plays the role of overwriting the index.

Positive example: the types that can build an index can be divided into three types: primary key index, unique index, and general index, while the overlay index is only an effect of a query. With the result of explain, the column will appear: using index.

The override index is mentioned here, and using index appears in the extra column.

Extra has other important information:

Using filesort: external index sorting is used, that is, MySQL cannot use the index to complete the sorting.

Using temptory: temporary tables are used.

Using where: where filtering is used.

This is the answer to the question about how to view the execution plan in explain. I hope the above content can be of some help to you. If you still have a lot of doubts to be solved, you can follow the industry information channel for more related knowledge.

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.