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

This article will explain in detail how to view the implementation plan in mysql. The content of the article is of high quality, so the editor will share it for you as a reference. I hope you will have a certain understanding of the relevant knowledge after reading this article.

Information contained in the explain execution plan

The most important fields are: id, type, key, rows, Extra

Detailed explanation of each field 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:

1. Id is the same: execution order is from top to bottom

2. Id is different: if it is a subquery, the sequence number of id will be incremented. The higher the id value, the higher the priority, the first to be executed.

3. Id is the same but different (both cases exist at the same time): if id is the same, it can be considered as a group, which can be executed sequentially from top to bottom. In all groups, the higher the id value, the higher the priority, and the higher the execution priority is.

Select_type

The type of query is mainly used to distinguish between ordinary queries, federated queries, subqueries and other complex queries.

1. SIMPLE: a simple select query that does not include subqueries or union

2. PRIMARY: the query contains any complex subsections, and the outermost query is marked as primary

3. SUBQUERY: subqueries are included in the select or where list

4. DERIVED: subqueries included in the from list are marked derived (derivative), mysql or recursively execute these subqueries, putting the results in the 00:00 table

5. UNION: 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.

6. UNION RESULT: the select that gets the result from the union table

Type

Access type is a very important indicator in sql query optimization. The result values from good to bad are as follows:

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

Generally speaking, a good sql query is at least range level, preferably ref

1. System: the table has only one row of records (equal to the system table). This is a special case of const type, which usually does not appear and can be ignored.

2. Const: indicates that it can be found once through the index, and const is used to compare primary key or unique indexes. Because you only need to match a row of data, all very quickly. If the primary key is placed in the where list, mysql can convert the query into a const

3. Eq_ref: unique index scan, for each index key, only one record in the table matches it. Common in primary key or unique index scans.

Note: the tables scanned by ALL have the least records, such as T1 table.

4. Ref: a non-unique index scan that returns all rows that match a single value. In essence, it is also an index access, which returns all rows that match a single value, but he may find multiple qualified rows, so it should be a mixture of lookup and scanning.

5. Range: only the rows in a given range are retrieved, and an index is used to select rows. The key column shows that that index is used. Generally speaking, queries such as bettween, in, and so on appear in the where statement. The range scan on this index column is better than a full index scan. You just need to start at one point and end at another without scanning all the indexes.

6. Index:Full Index Scan,index differs from ALL in that index only traverses the index tree. This is usually an ALL block, because the index file is usually smaller than the data file. (both Index and ALL read the whole table, but index reads from the index, while ALL reads from the hard disk.)

7. ALL:Full Table Scan, traversing the table to find matching rows

Possible_keys

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

The index actually used, if NULL, is not used.

If an override index is used in the query, the index appears only in the key list

Key_len

Represents the number of bytes used in the index, the length of the index used in the query (the maximum possible length), not the actual length, in theory, the shorter the better. The key_len is calculated from the table definition, not retrieved from within the table

Ref

The column that shows the index is used and, if possible, a constant const.

Rows

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

Extra

Additional information that is not suitable for display in other fields, but is very important

1 、 Using filesort:

Mysql sorts the data using an external index instead of sorting and reading by the index within the table. In other words, mysql cannot use the sorting operation completed by the index to become "file sorting".

Since the index is sorted first by email and then by address, if the query is sorted directly by address, the index cannot meet the requirements, and "file sorting" must be implemented again within mysql.

2 、 Using temporary:

Use temporary tables to save intermediate results, that is, mysql uses temporary tables when sorting query results, which is common in order by and group by

3 、 Using index:

Indicates that the overlay index (Covering Index) is used in the corresponding select operation, which avoids accessing the data rows of the table and is efficient.

If Using where appears at the same time, the index is used to perform the lookup of index key values (see figure above)

If Using where does not appear at the same time, the index is used to read data rather than perform lookup actions

Override index (Covering Index): also known as index overwrite. These are the fields in the select list that can be obtained only from the index, without having to read the data file again according to the index, in other words, the query column will be overwritten by the index created.

Note:

A. If you need to use an override index, the fields in the select list will only take out the required columns. Do not use select *.

B. If all fields are indexed, the index file will be too large, which will reduce crud performance.

4 、 Using where:

Where filtering is used

5 、 Using join buffer:

Link caching is used

6 、 Impossible WHERE:

The value of the where clause is always false and cannot be used to get any meta-ancestors.

7 、 select tables 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 is calculated, and the optimization can be completed at the stage of query execution plan generation.

8 、 distinct:

Optimize the distinct operation and stop looking for the same worthwhile action after finding the first matching meta-ancestor.

Integrated Case

Execution sequence

1 (id=4), [select id, name from T2]: select_type is union, indicating that the select of id=4 is the second select in union.

2 (id = 3), [select id, name from T1 where address = '11']: because it is a subquery contained in the from statement, it is marked as DERIVED (derivative). Where address =' 11' can be retrieved through the composite index idx_name_email_address, so type is index.

3 (id = 2), [select id from T3]: marked as SUBQUERY because it is a subquery contained in select.

4 (id = 1), [select d1.name, … D2 from... D1]: select_type for PRIMARY indicates that the query is the outermost query, and the table column marked "derived3" indicates that the query result comes from a derived table (select result with id = 3).

5 (id = NULL), [… Union... ]: represents the stage of reading rows from the temporary table of union, and the "union 1,4" of the table column indicates that the union operation is performed with the select results of id=1 and id=4.

On how to view the implementation plan in mysql to share here, I hope the above content can be of some help to you, can learn more knowledge. If you think the article is good, you can share it for more people to see.

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.