MySQL execution plan analysis

1. id column

The id column is a number used to identify the sequence number of the SELECT query, indicating the order of the SELECT clause or the operation table during the execution of the SQL query.

If there are no subqueries or associated queries in SQL, then the id column will all show a 1. Otherwise, inner SELECT statements are generally numbered sequentially.

The id column is divided into three cases:

1. same id A normal query as follows, without subqueries.

2. id is different If there is a subquery, the sequence number of the id will be incremented. The larger the id value, the higher the priority and the first to be executed.

3. The id is the same but different 1), 2) Both situations exist at the same time. If the id is the same, it is considered to be a group and executed from top to bottom. In all groups, the larger the id value, the higher the priority and the first to execute.

2. select_type column

The select_type column indicates the query type of the corresponding row, whether it is a simple query or a complex query, and is mainly used to distinguish complex queries such as ordinary queries, union queries, and subqueries.

The select_type column has the following values:

3.table column

The table column indicates which table the corresponding row is being executed on, and refers to the corresponding table name, or the alias of the table (if an alias is defined in the SQL).

4.partitions column

The partitions involved in the query.

5.type column

The type column refers to the access type, which is how MySQL decides how to find rows in the table. It is a very important indicator in SQL query optimization. It has many values, from worst to best:

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

1) ALL

The well-known full table scan, which means to scan the entire table to find matching rows, is obviously very slow to query in this way. In this case, the performance is the worst, try to avoid this situation when writing SQL.

It is not difficult to understand when you usually write SQL and avoid using select *. In other words, to avoid full table scans, which are the worst performers.

2) index

Full index scan, similar to full table scan ALL, scans the table in index order instead of row scan, that is, only traverses the index tree. Although both index and ALL read the whole table, index is read from the index, and ALL is read from the hard disk. Obviously, index performance is better than ALL, and adding an index reasonably will help improve performance.

3) range

Retrieve only a given range of rows, using an index to select rows. The key column shows which index is used. Generally, queries such as between, <, >, in, etc. appear in the where statement. Range scans on such indexed columns are better than full index scans of the index.

An example is as follows: explain select * from film where film_id between 1 and 10;

4) ref

A nonunique index scan that returns all rows matching a single value. Essentially it is also an indexed access, it returns all rows matching a single value, however it may find multiple matching rows, so it is a hybrid of lookup and scan. This type only occurs when a non-unique index or a non-unique prefix of a unique index is used.

5) eq_ref

Unique index scan. Commonly seen in primary key or unique index scans.

6) const

It can be found once by index, and const is used to compare primary key or unique index. Because only one row of data needs to be matched, all is fast. If you put the primary key in the where list, mysql can convert the query to a const. An example is as follows: show index from film; explain select * from film where film_id = 1;

7) system

The table has only one row of records, which is a special case of const type, which is relatively rare, such as: system table.

6. possible_keys column

Shows which indexes are used in the query.

7. key column

The actual index used, if NULL, no index is used. If a covering index is used in the query, the index appears only on the key column. The possible_keys column indicates which index contributes to a more efficient query, while the key column indicates which index was actually optimized to be more efficient. An example is as follows: show index from film_actor; explain select actor_id,film_id from film_actor;

8. key_len column

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

9. ref column

Indicates to look up the value in the index of the key column record, the column used or the constant const.

10. rows column

Estimate the number of lines to read to find the desired line. This number is the number of loops in the embedded loop association plan. It is not the number of rows that are finally read from the table, but the average number of rows that MySQL must read in order to find those rows that match the query. It can only be used as a relative numbers to measure.

11. filtered column

The number of rows returned as a percentage of the number of rows read. The higher the value, the better.

12. Extra column

Additional information, but important.

Common values are as follows:

1. Using index Indicates that a covering index is used in SQL.

2. Using where Many where conditions involve columns in the index, which can be checked by the storage engine when it reads the index, so not all queries with a where clause will display “Using where”.

3. Using temporary When sorting query results, a temporary table is used, which is common in order by and group by.

4. Using filesort An external index is used to sort the data, instead of reading sorted by the index in the table. That is to say, the sorting operation that MySQL cannot use the index to complete is called “file sorting”.