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

How to interpret the MySQL implementation plan, many novices are not very clear about this, in order to help you solve this problem, the following editor will explain for you in detail, people with this need can come to learn, I hope you can gain something.

EXPLAIN SELECT... Variant: 1. EXPLAIN EXTENDED SELECT. Decompile the execution plan into a SELECT statement, and run SHOW WARNINGS to get the query statement optimized by the MySQL optimizer. EXPLAIN PARTITIONS SELECT... EXPLAIN for partitioned tables

Information contained in the execution plan

Id

Contains a set of numbers that represent the order in which the select clause or action table is executed in the query

The id is the same and the execution order is from top to bottom

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.

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, the higher the priority and the first execution.

Select_type

Represents the type of each select clause in the query (simple OR complex)

A.SIMPLE: subqueries or UNION are not included in the query

b. If the query contains any complex subsections, the outermost query is marked as: PRIMARY

c. A subquery is included in the SELECT or WHERE list, which is marked: SUBQUERY

d. The subquery included in the FROM list is marked as: DERIVED (derived)

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

f. The SELECT that gets the result from the UNION table is marked as: UNION RESULT

Type

Indicates how MySQL finds the required rows in the table, also known as "access type". The common types are as follows:

From left to right, from the worst to the best

A.ALL:Full Table Scan, MySQL will traverse the entire table to find matching rows

B.index:Full Index Scan,index differs from ALL in that index only traverses the index tree.

C.range: index range scan. The scan of the index starts at a certain point and returns rows that match the range, which is common in queries such as between, etc.

Performance differences of different forms of index access for range access types

D.ref: a non-unique index scan that returns all rows that match a single value. It is common to use a non-unique prefix of a non-unique index, that is, a unique index.

E.eq_ref: unique index scan, with only one record in the table matching for each index key. Common in primary key or unique index scans

F.const, system: use these types of access when MySQL optimizes some part of the query and converts it to a constant. If the primary key is placed in the where list, MySQL can convert the query to a constant

System is a special case of the const type, using system when the query table has only one row

G.NULL:MySQL breaks up statements during optimization and executes without even accessing tables or indexes

Possible_keys

Indicates which index MySQL can use to find rows in the table. If there is an index on the field involved in the query, the index will be listed, but not necessarily used by the query.

Key

Displays the index actually used by MySQL in the query. If no index is used, it is displayed as NULL

TIPS: if an override index is used in a query, it appears only in the key list

Key_len

Represents 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 length used, that is, key_len is calculated according to the table definition, not retrieved from within the table.

Ref

Indicates the join matching condition of the above table, that is, which columns or constants are used to find values on index columns

In this example, key_len knows that the idx_col1_col2 of the T1 table is fully utilized, and the col1,col2 of the col1 matching T2 table matches a constant, namely 'ac'.

Rows

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

Extra

Contains additional information that is not suitable for display in other columns but is very important

A.Using index

This value indicates that the overlay index (Covering Index) is used in the corresponding select operation.

TIPS: override index (Covering Index)

MySQL can use the index to return the fields in the select list without having to read the data file again according to the index

An index that contains all the data that meets the needs of a query is called an overlay index (Covering Index).

Note:

If you want to use an override index, be sure to note that only the columns you need are taken out of the select list, not select *, because if you index all the fields together, the index file will be too large and the query performance will be degraded.

B.Using where

Indicates that the MySQL server performs "post-filtering" (Post-filter) after the storage engine is recorded

If the query fails to use the index, the function of Using where is to remind us that MySQL will use the where clause to filter the result set

C.Using temporary

Indicates that MySQL needs to use temporary tables to store result sets, which are common in sorting and grouping queries

D.Using filesort

The sort operation that cannot be done with indexes in MySQL is called "file sorting".

Limitations of MySQL implementation Plan

EXPLAIN won't tell you about triggers, stored procedures, or the impact of user-defined functions on queries.

EXPLAIN does not consider all kinds of Cache

EXPLAIN cannot show the optimization work done by MySQL when executing the query

? Some of the statistics are estimated, not exact values

EXPALIN can only explain SELECT operations. Other operations should be rewritten as SELECT to view the execution plan.

Is it helpful for you to read the above content? If you want to know more about the relevant knowledge or read more related articles, please follow the industry information channel, thank you for your support.

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.