Detailed explanation of execution plan in MySQL explain

1. Usage and definition:

explain is the execution plan of sql, just add the explain keyword in front of sql

For example: explain select * from tbl_emp;

Explanation of noun:

id:[Sequence of operation table]

1. The id is the same, the execution order of the table is executed from top to bottom

2. The id is different, the larger the id is executed first

3. The same and different ids are executed together, The larger ones are executed first, and then executed in order from top to bottom

select_type: Query type [distinguish between simple query, subquery, and joint query]

Generally: SIMPLE[the simplest Answered sql query], PRIMARY [If the query contains any complex subquery, the outermost query will be marked],

SUBQUERY [Subquery included in the select or where list]

UNION

.............

type:Access type [best to worst]

system > const > eq_ref > ref > range > index> ALL

　

Generally it is good to reach the range level, it is best to reach the ref

possible: Displays one or more indexes that may be applied to this table; if there is an index on the field involved in the query, the index will be listed, but not necessarily is actually used by the query.

Simply put: MySQL speculates that the index may be used theoretically, but it may not be actually used by the query

key: The index actually used in the query. If it is NULL, it is either not built or Not used, or the index is invalid

Covering index: The fields queried after select are exactly the same as the number and order of the built composite index

If it is not theoretically available, but it appears in the key, then A covering index is used

ref: Displays which column of the index is used

rows: The smaller the better

Extra: Do not use Using filesort [sort within the file], using index (index scan, it will be better if it appears)

2. Function:

　

Example:

Recommended mysql video tutorial, address: https://www.php.cn/course/list/51. html

The above is the detailed content of Detailed explanation of execution plan in MySQL explain. For more information, please follow other related articles on the PHP Chinese website!