How to view execution plan in Mysql

This article brings you relevant knowledge about mysql. It mainly introduces how to view the execution plan in Mysql. Using the explain keyword can simulate the optimizer to execute SQL query statements, so as to know that MySQL is How to handle your SQL statements and analyze the performance bottlenecks of your query statements or table structures. I hope it will be helpful to everyone.

Recommended learning: mysql video tutorial

Use the explain keyword to simulate the optimizer executing SQL query statements, so as to know that MySQL is How to handle your SQL statements and analyze the performance bottlenecks of your query statements or table structures.

Explain the information contained in the execution plan

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

each Detailed field explanation

id

Select query sequence number, including a set of numbers, indicating the order in which select clauses or operation tables are executed in the query

Three situations:

1. Same id: execution order from top to bottom

2. Different id: if it is a child For queries, the serial number of the id will increase. The larger the id value, the higher the priority and the earlier it will be executed

3. The id is the same but different (both cases are at the same time Exists) : If the id is the same, it can be considered as a group and executed sequentially from top to bottom; among all groups, the larger the id value, the higher the priority, and the earlier it is executed

select_type

The type of query is mainly used to distinguish complex queries such as ordinary queries, joint queries, subqueries, etc.

• 1, SIMPLE: Simple select query, the query does not contain subqueries or unions
• 2. PRIMARY: The query contains any complex subparts, and the outermost query is marked as primary
• 3. SUBQUERY: A subquery is included in the select or where list.
• 4. DERIVED: A subquery is included in the from list. Marked as derived, MySQL or recursively executes these subqueries and puts the results in the zero time table
• 5, UNION: If the second select appears after union, is 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: Select to obtain results from the union table

type

Access type, a very important indicator in SQL query optimization, the result values ​​from best to worst are:

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

Generally speaking, a good sql query reaches at least the range level, and preferably reaches the ref

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

2. const: means it is found through the index once, const is used to compare the primary key or unique index. Because you only need to match one row of data, it's very fast. 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 For each index key, only one record in the table matches it. Commonly seen in primary key or unique index scans.

Note: ALL full table scan of the table with the fewest records, such as the t1 table

4, ref

: non-unique index Scan to return all rows matching a single value. Essentially, it is also an index access, which returns all rows that match a single value. However, it may find multiple rows that meet the criteria, so it should be a mixture of search and scan

5, range: Retrieve only rows in a given range, using an index to select rows. The key column shows which index is used. Generally, queries such as bettween, , in, etc. appear in the where statement. This range scan on index columns is better than a full index scan. It only needs to start at a certain point and end at another point, without scanning the entire index

6, index: Full Index Scan, index and ALL The difference is that the index type only traverses the index tree. This is usually ALL blocks, as index files are usually smaller than data files. (Although Index and ALL both read the entire table, index is read from the index, while ALL is read from the hard disk)

7, ALL: Full Table Scan, traverse the entire table to find matching rows

possible_keys

There is an index on the fields involved in the query, then the index will Listed, but not necessarily used by the query

key

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

If a covering index is used in the query, the index will only appear in the key list

key_len

indicates the number of bytes used in the index and the length of the index used in the query (the maximum possible length), not the actual length used. In theory, the shorter the length, the better. key_len is calculated based on the table definition, not retrieved from the table.

ref

The column showing the index is used. If possible, it is a constant const.

rows

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

Extra

Not suitable for display in other fields, but very important additional information

1. Using filesort:

mysql uses an external index to sort the data, rather than sorting by The index within the table performs sorted reads. That is to say, mysql cannot use the index to complete the sorting operation to become "file sorting"

Since the index is sorted by email first and then by address, if you query directly by Address sorting, the index cannot meet the requirements, and mysql must implement "file sorting" again

2. Using temporary:

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

3. Using index:

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

If Using where appears at the same time, it indicates that the index has been Used to perform search of index key values ​​(refer to the picture above)

If it is not used and "Using where" appears at the same time, it indicates that the index is used to read data rather than perform search actions

Covering Index (Covering Index): Also called index coverage. It is the fields in the select list that can be obtained only from the index. There is no need to read the data file again according to the index. In other words, the query column must be covered by the built index .

Note:

• a. If you need to use a covering index, only take out the required columns from the fields in the select list. Do not use select *
• b. If you use Indexing all fields will cause the index file to be too large, which will reduce crud performance

4. Using where:

Using where filtering

5. Using join buffer:

Using link cache

6. Impossible WHERE:

where clause The value is always false and cannot be used to obtain any ancestor

7. Select tables optimized away:

without group by In the case of clauses, 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 perform calculations. The optimization can be completed during the query execution plan generation phase

8. distinct:

Optimize distinct operation, stop looking for equally worthy actions after finding the first matching ancestor

Comprehensive Case

Execution sequence

1 (id = 4), [select id, name from t2]: select_type is union, specify id The selection of =4 is the second selection in the union.

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

3 (id = 2), [select id from t3]: Because it is a subquery included in select, it is marked as SUBQUERY.

4 (id = 1), [select d1.name, … d2 from … d1]: select_type is PRIMARY, which means that the query is the outermost query, and the table column is marked as "derived3", which means that the query results come from In a derived table (select result of id = 3).

5 (id = NULL), [... union...]: Represents the stage of reading rows from the union's temporary table. "Union 1, 4" in the table column means id=1 and id=4 Perform union operation on the select results.

Recommended learning: mysql video tutorial

The above is the detailed content of How to view execution plan in Mysql. For more information, please follow other related articles on the PHP Chinese website!