ThinkerQAQ

NOTE

1.1 MySQL explain

1. What It Is Used to see how MySQL executes SQL statements so that SQL can be optimized 2. Purpose - Table read order - Which indexes can be used - Index actually used - How many rows are read from each table 3. Field Description 3.1. id - One select corresponds to one id - When ids are the same, execution proceeds from top to bottom - In a join query, one select + multiple tables produces multiple rows, but the ids are the same

DatabasesCreated Updated 5 min readhistorical

This is a historical learning note and may contain outdated or incomplete understanding.

1. What It Is

Used to see how MySQL executes SQL statements so that SQL can be optimized.

2. Purpose

  • Table read order.
  • Which indexes can be used.
  • Index actually used.
  • How many rows are read from each table.

3. Field Description

3.1. id

  • One select corresponds to one id.
  • When ids are the same, execution proceeds from top to bottom.
    • In a join query, one select + multiple tables produces multiple rows, but the ids are the same. A table that appears earlier is the driving table, and a table that appears later is the driven table.
explain select * from tb_item,tb_item_cat,tb_item_desc where tb_item.cid = tb_item_cat.id and tb_item.id = tb_item_desc.item_id;
+----+-------------+--------------+--------+---------------+---------+---------+--------------------------+------+-------+
| id | select_type | table        | type   | possible_keys | key     | key_len | ref                      | rows | Extra |
+----+-------------+--------------+--------+---------------+---------+---------+--------------------------+------+-------+
| 1  | SIMPLE      | tb_item_desc | ALL    | PRIMARY       | <null>  | <null>  | <null>                   | 1627 |       |
| 1  | SIMPLE      | tb_item      | eq_ref | PRIMARY,cid   | PRIMARY | 8       | tao.tb_item_desc.item_id | 1    |       |
| 1  | SIMPLE      | tb_item_cat  | eq_ref | PRIMARY       | PRIMARY | 8       | tao.tb_item.cid          | 1    |       |
+----+-------------+--------------+--------+---------------+---------+---------+--------------------------+------+-------+
  • For a subquery, the id number increases. The larger the id, the earlier it is executed.
    • Some subqueries are converted into join queries. In this case there is only one corresponding id.
explain select * from tb_item_cat where id=
	(select cid from tb_item where id =
		(select item_id from tb_item_desc where item_id = 536563))
+----+-------------+--------------+-------+---------------+---------+---------+-------+------+-------------+
| id | select_type | table        | type  | possible_keys | key     | key_len | ref   | rows | Extra       |
+----+-------------+--------------+-------+---------------+---------+---------+-------+------+-------------+
| 1  | PRIMARY     | tb_item_cat  | const | PRIMARY       | PRIMARY | 8       | const | 1    | Using where |
| 2  | SUBQUERY    | tb_item      | const | PRIMARY       | PRIMARY | 8       | const | 1    |             |
| 3  | SUBQUERY    | tb_item_desc | const | PRIMARY       | PRIMARY | 8       | const | 1    | Using index |
+----+-------------+--------------+-------+---------------+---------+---------+-------+------+-------------+
  • For a UNION query, id is displayed as NULL.
    • The result sets of multiple queries are combined to create a temporary table, and the records in the temporary table are then deduplicated.

3.2. select_type

  • SIMPLE:
    • Queries that do not contain UNION or subqueries are SIMPLE.
    • Join queries are also SIMPLE.
  • PRIMARY
    • For a large query containing UNION, UNION ALL, or subqueries, it consists of several smaller queries. The leftmost query has a select_type of PRIMARY.
  • UNION
    • For a large query containing UNION or UNION ALL, it consists of several smaller queries. Except for the leftmost one, the other smaller queries have a select_type of UNION.
  • UNION RESULT
    • If MySQL chooses to use a temporary table to complete deduplication for a UNION query, the query against that temporary table has a select_type of UNION RESULT.
  • DEPENDENT UNION
    • In a large query containing UNION or UNION ALL, if each small query depends on the outer query, then except for the leftmost small query, the other small queries have a select_type of DEPENDENT UNION.
  • SUBQUERY
    • If a query containing a subquery cannot be converted into a corresponding semi-join, the subquery is an uncorrelated subquery, and the query optimizer chooses to materialize the subquery, then the query represented by the first SELECT keyword in that subquery has a select_type of SUBQUERY.
  • DEPENDENT SUBQUERY
    • If a query containing a subquery cannot be converted into a corresponding semi-join and the subquery is a correlated subquery, then the query represented by the first SELECT keyword in that subquery has a select_type of DEPENDENT SUBQUERY.
  • DERIVED
    • For a query containing a derived table that is executed using materialization, the subquery corresponding to the derived table has a select_type of DERIVED.
  • MATERIALIZED
    • When the query optimizer executes a statement containing a subquery and chooses to materialize the subquery before joining it with the outer query, the corresponding select_type is MATERIALIZED.

3.3. type

  • system
    • When the table has only one record and the storage engine has exact statistics for the table, such as MyISAM or Memory.
  • const
    • When a single table is accessed by equality matching a primary key or unique secondary-index column with a constant, the access method is const.
  • eq_ref
    • In a join query, if the driven table is accessed by equality matching through its primary key or unique secondary-index column, the access method for that driven table is eq_ref.
  • ref
    • When a table is queried by equality matching an ordinary secondary-index column with a constant, the access method may be ref.
  • ref_or_null
    • When an ordinary secondary index is used for an equality-match query and the indexed column can also contain NULL values, the access method may be ref_or_null.
  • index_merge
    • Normally a query on a table uses only one index, but in some scenarios Intersection, Union, and Sort-Union index-merge methods can be used to execute a query.
  • range
    • If an index is used to obtain records in certain ranges, the range access method may be used.
  • index
    • When index covering can be used but all index records need to be scanned, the access method is index.
  • ALL
    • Full table scan.

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

3.3.1. System

Single table. There is only one row in the table. MySQL’s built-in system table.

3.3.2. Const

Single table. There is only one matching row in the table. When the query condition compares a primary-key index or unique index with a constant value, only one record matches.

explain select * from tb_item where id = 536563;
+----+-------------+---------+-------+---------------+---------+---------+-------+------+-------+
| id | select_type | table   | type  | possible_keys | key     | key_len | ref   | rows | Extra |
+----+-------------+---------+-------+---------------+---------+---------+-------+------+-------+
| 1  | SIMPLE      | tb_item | const | PRIMARY       | PRIMARY | 8       | const | 1    |       |
+----+-------------+---------+-------+---------------+---------+---------+-------+------+-------+

3.3.3. eq_ref

Multiple tables. Only one row matches the preceding table (id load order). A primary key or non-NULL unique index of table A equals a constant value or expression from table B. At most one row in A corresponds to B (one-to-one).

explain select * from tb_item_cat, tb_item_param where tb_item_cat.id = tb_item_param.item_cat_id;
+----+-------------+---------------+--------+---------------+---------+---------+-------------------------------+------+-------------+
| id | select_type | table         | type   | possible_keys | key     | key_len | ref                           | rows | Extra       |
+----+-------------+---------------+--------+---------------+---------+---------+-------------------------------+------+-------------+
| 1  | SIMPLE      | tb_item_param | ALL    | item_cat_id   | <null>  | <null>  | <null>                        | 9    | Using where |
| 1  | SIMPLE      | tb_item_cat   | eq_ref | PRIMARY       | PRIMARY | 8       | tao.tb_item_param.item_cat_id | 1    |             |
+----+-------------+---------------+--------+---------------+---------+---------+-------------------------------+------+-------------+

3.3.4. ref

Multiple tables. An ordinary index of table A (not a primary key or unique index) is = or <=> a constant or expression from table B. Only a small number of rows in A correspond to B.

explain select * from tb_item, tb_item_param where tb_item.cid = tb_item_param.item_cat_id;
+----+-------------+---------------+------+---------------+--------+---------+-------------------------------+------+-------------+
| id | select_type | table         | type | possible_keys | key    | key_len | ref                           | rows | Extra       |
+----+-------------+---------------+------+---------------+--------+---------+-------------------------------+------+-------------+
| 1  | SIMPLE      | tb_item_param | ALL  | item_cat_id   | <null> | <null>  | <null>                        | 9    | Using where |
| 1  | SIMPLE      | tb_item       | ref  | cid           | cid    | 8       | tao.tb_item_param.item_cat_id | 387  |             |
+----+-------------+---------------+------+---------------+--------+---------+-------------------------------+------+-------------+

3.3.5. Range

Single table. Only rows in a given range, using an ordinary index. Operators include =, <>, >, >=, <, <=, IS NULL, <=>, BETWEEN, LIKE, and IN().

explain select * from tb_item where id in (562379,536563);
+----+-------------+---------+-------+---------------+---------+---------+--------+------+-------------+
| id | select_type | table   | type  | possible_keys | key     | key_len | ref    | rows | Extra       |
+----+-------------+---------+-------+---------------+---------+---------+--------+------+-------------+
| 1  | SIMPLE      | tb_item | range | PRIMARY       | PRIMARY | 8       | <null> | 2    | Using where |
+----+-------------+---------+-------+---------------+---------+---------+--------+------+-------------+

3.3.6. Index

The query has no condition, but uses an index.

explain select cid from tb_item;
+----+-------------+---------+-------+---------------+-----+---------+--------+------+-------------+
| id | select_type | table   | type  | possible_keys | key | key_len | ref    | rows | Extra       |
+----+-------------+---------+-------+---------------+-----+---------+--------+------+-------------+
| 1  | SIMPLE      | tb_item | index | <null>        | cid | 8       | <null> | 3102 | Using index |
+----+-------------+---------+-------+---------------+-----+---------+--------+------+-------------+

3.3.7. All

The query has no condition and does not use an index.

explain select * from tb_item;
+----+-------------+---------+------+---------------+--------+---------+--------+------+-------+
| id | select_type | table   | type | possible_keys | key    | key_len | ref    | rows | Extra |
+----+-------------+---------+------+---------------+--------+---------+--------+------+-------+
| 1  | SIMPLE      | tb_item | ALL  | <null>        | <null> | <null>  | <null> | 3102 |       |
+----+-------------+---------+------+---------------+--------+---------+--------+------+-------+

3.4. table

No matter how complex the SQL statement is, it ultimately needs single-table access for each table. table is the name of the table being accessed.

3.5. possible_keys

Indexes that may be used, but are not necessarily actually used.

3.6. key

The index actually used.

3.7. key_len

The maximum possible number of bytes used from the index, not the actual length.

3.8. ref

When a query is executed using an equality-match condition on an indexed column, that is, when the access method is one of const, eq_ref, ref, ref_or_null, unique_subquery, or index_subquery, the ref column shows what is equality-matched with the indexed column, for example a constant or another column.

3.9. rows

  • If the query optimizer decides to execute a query on a table using a full table scan, the rows column in the execution plan represents the estimated number of rows that need to be scanned.
  • If an index is used to execute the query, the rows column represents the estimated number of index records to scan.

3.10. Extra

3.10.1. Using filesort

The index cannot be used for sorting.

explain select * from tb_item order by barcode
+----+-------------+---------+------+---------------+--------+---------+--------+------+----------------+
| id | select_type | table   | type | possible_keys | key    | key_len | ref    | rows | Extra          |
+----+-------------+---------+------+---------------+--------+---------+--------+------+----------------+
| 1  | SIMPLE      | tb_item | ALL  | <null>        | <null> | <null>  | <null> | 3102 | Using filesort |
+----+-------------+---------+------+---------------+--------+---------+--------+------+----------------+

3.10.2. Using temporary

A temporary table is used to store intermediate results. It may appear with ORDER BY and GROUP BY.

explain select title from tb_item group by status;
+----+-------------+---------+------+---------------+--------+---------+--------+------+---------------------------------+
| id | select_type | table   | type | possible_keys | key    | key_len | ref    | rows | Extra                           |
+----+-------------+---------+------+---------------+--------+---------+--------+------+---------------------------------+
| 1  | SIMPLE      | tb_item | ALL  | <null>        | <null> | <null>  | <null> | 3102 | Using temporary; Using filesort |
+----+-------------+---------+------+---------------+--------+---------+--------+------+---------------------------------+

3.10.3. Using index

A covering index is used (the queried columns are covered by the created index).

explain select id from tb_item;
+----+-------------+---------+-------+---------------+---------+---------+--------+------+-------------+
| id | select_type | table   | type  | possible_keys | key     | key_len | ref    | rows | Extra       |
+----+-------------+---------+-------+---------------+---------+---------+--------+------+-------------+
| 1  | SIMPLE      | tb_item | index | <null>        | updated | 5       | <null> | 3102 | Using index |
+----+-------------+---------+-------+---------------+---------+---------+--------+------+-------------+

If Using where appears at the same time, it means:

3.10.4. Using where

A WHERE clause is used.

explain select id,cid,title from tb_item where title = 'test'; 
+----+-------------+---------+------+---------------+--------+---------+--------+------+-------------+
| id | select_type | table   | type | possible_keys | key    | key_len | ref    | rows | Extra       |
+----+-------------+---------+------+---------------+--------+---------+--------+------+-------------+
| 1  | SIMPLE      | tb_item | ALL  | <null>        | <null> | <null>  | <null> | 3102 | Using where |
+----+-------------+---------+------+---------------+--------+---------+--------+------+-------------+

3.10.5. Using join buffer

A join buffer is used for the join query.

3.10.6. Impossible where

The WHERE condition is always false.

explain select * from tb_item where id=1 and id=2;
+----+-------------+--------+--------+---------------+--------+---------+--------+--------+------------------+
| id | select_type | table  | type   | possible_keys | key    | key_len | ref    | rows   | Extra            |
+----+-------------+--------+--------+---------------+--------+---------+--------+--------+------------------+
| 1  | SIMPLE      | <null> | <null> | <null>        | <null> | <null>  | <null> | <null> | Impossible WHERE |
+----+-------------+--------+--------+---------------+--------+---------+--------+--------+------------------+

4. Examples

  1. Single table Keep trying different plans.
explain select id,file_name from tb_export where user_id = 55 and result >= 'success' order by create_time desc limit 1;
+----+-------------+-----------+-------+-------------------------------------+-------------------------------------+---------+--------+------+-------------+
| id | select_type | table     | type  | possible_keys                       | key                                 | key_len | ref    | rows | Extra       |
+----+-------------+-----------+-------+-------------------------------------+-------------------------------------+---------+--------+------+-------------+
| 1  | SIMPLE      | tb_export | index | tb_export_user_id_create_time_index | tb_export_user_id_create_time_index | 14      | <null> | 1    | Using where |
+----+-------------+-----------+-------+-------------------------------------+-------------------------------------+---------+--------+------+-------------+
  1. Two tables Add the index to the right-hand table, because a left join keeps all rows from the left table.
explain select *
from tb_item_param_item
left join tb_order_item
on tb_order_item.item_id=tb_item_param_item.item_id;
***************************[ 1. row ]***************************
id            | 1
select_type   | SIMPLE
table         | tb_item_param_item
type          | ALL
possible_keys | <null>
key           | <null>
key_len       | <null>
ref           | <null>
rows          | 6
Extra         | 
***************************[ 2. row ]***************************
id            | 1
select_type   | SIMPLE
table         | tb_order_item
type          | ref
possible_keys | tb_order_item_item_id_index
key           | tb_order_item_item_id_index
key_len       | 8
ref           | tao.tb_item_param_item.item_id
rows          | 4
Extra         | Using where
  1. Three tables Create indexes on the right-hand tables.
explain select *
from tb_item_param_item
left join tb_order_item
on tb_order_item.item_id=tb_item_param_item.item_id
left join tb_item_desc on tb_item_desc.item_id = tb_item_param_item.item_id;
***************************[ 1. row ]***************************
id            | 1
select_type   | SIMPLE
table         | tb_item_param_item
type          | ALL
possible_keys | <null>
key           | <null>
key_len       | <null>
ref           | <null>
rows          | 6
Extra         | 
***************************[ 2. row ]***************************
id            | 1
select_type   | SIMPLE
table         | tb_order_item
type          | ref
possible_keys | tb_order_item_item_id_index
key           | tb_order_item_item_id_index
key_len       | 8
ref           | tao.tb_item_param_item.item_id
rows          | 50
Extra         | Using where
***************************[ 3. row ]***************************
id            | 1
select_type   | SIMPLE
table         | tb_item_desc
type          | ref
possible_keys | tb_item_desc_item_id_index
key           | tb_item_desc_item_id_index
key_len       | 9
ref           | tao.tb_item_param_item.item_id
rows          | 1
Extra         | Using where
  • Join optimization: Reduce the number of NestedLoop iterations as much as possible; use a small result set to drive a large result set.

5. Other SQL Optimizations

5.1. Small Table Drives Large Table

  • If table A has less data than table B, use exists.
select * from A where exists(select * from B where B.id=A.id);
  • If table A has more data than table B, use in.
select * from A where id in (select id from B );

5.2. order by

  • There are two forms of sorting for order by:
  • FileSort
  • Index
    • The fields in order by use the leftmost prefix of the index.
    • The fields in where + order by use the leftmost prefix of the index.
  • Optimization:
    • When using order by select, select only the required fields.

5.3. group by

group by first sorts and then groups, so it follows the leftmost-prefix principle of indexes.

6. References

Discussion

Sign in with GitHub to comment. Discussions are stored as GitHub Issues.View on GitHub