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

Editor to share with you the example analysis of the implementation plan index in mysql, I believe that most people do not know much about it, so share this article for your reference, I hope you can learn a lot after reading this article, let's go to know it!

First execute the information contained in the plan:

Id, select_type, table, partitions, type, possible_keys, key, key_len, ref, rows, filtered, Extra

Id

The sequence number of a select query that contains a set of numbers indicating the order in which the select clause or action table is executed in the query

There are three situations:

A) id is the same, indicating that the table (table field) is executed from top to top

B) id is different. If it is a subquery, the larger the id, the higher the priority of table execution.

C) there are both similarities and differences in id. The same id can be considered as the same group, which is executed sequentially from top to bottom. The larger the id in all groups, the higher the execution priority.

Select_type

There are mainly six kinds.

SIMPLE simple query, excluding self-query and union

Markup for the outermost query in a PRIMAY query that contains any complex subsections

SUBQUERY contains self-queries in the list of select or where clauses

The subqueries included by DERIVED in the from list are marked as derived (derived) mysql recursive queries and placed in temporary tables

UNION marked as union if the second select appears after union

If union is included in a subquery of the from clause, the outer select is marked derived

Merging of UNION RESULT union results

Table

Corresponding table

Type

Access Typ

Efficiency system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL

Mainly used: system > const > eq_ref > ref > range > index > ALL

The system table has only one row of records (system table)

Const index constant

Eq_ref unique index scan

Ref non-unique index scan

Range range index scan (index columns are limited by ranges such as between in)

Index full index scan

ALL full table scan

Possible_keys

One or more indexes that exist on the fields involved in the query (may be used, but not necessarily)

Keys

The index actually used

Key_len

The number of bytes used by the index, which can be used to calculate the length of the index in the query. Under the same query result (without loss of precision), the smaller the length, the better.

The displayed value is the maximum possible length of the index, not the actual length, that is, the non-table retrieval value calculated by key_len through the table definition

Ref

Show columns (or constants) used in the index example db.table.col, const

Rows

According to the statistical information of the table and the selection of the index, we roughly estimate the number of rows that need to be read to find the required records.

Extra

Additional information

1 、 Using filesort

Use an external index sort (file sort) instead of reading by index sort

For example, for a table that uses a composite index, only one of the columns of the composite index is used for query sorting, which may result in file sorting. Situations to be avoided as much as possible

2 、 Using temporary

Temporary tables are used to save intermediate results, which are common in order by and group by

Consumption of resources, situations to be avoided

Use override indexes in order by and group by statements

3 、 Using index

Override index used to avoid accessing the data rows of the table

At the same time, using where appears, indicating that the index is used to query the key value of the index (for example, the columns in the query results are overwritten by index columns).

If using where does not appear, the index is directly used to read the data and does not perform the lookup action.

* override index (Covering index)

Select's data columns are only obtained from the index, there is no need to find data rows

4 、 Using where

5 、 Using join buffer

Connection cache is used

6 、 Impossible where

Where clause result false

7 、 select table optimized away

In the case of no group by clause, optimizing the MIN/MAX operation based on the index or optimizing the COUNT (*) operation for the MyISAM storage engine does not have to wait until the execution phase to calculate, and the query execution and call generation phase can complete the optimization.

8 、 distinct

Optimize the distinct operation to stop looking for the same value after finding the first matching tuple

The above is all the contents of the article "sample Analysis of execution Plan Index in mysql". Thank you for reading! I believe we all have a certain understanding, hope to share the content to help you, if you want to learn more knowledge, 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.