MySQL explain

Explain

Explain用来查看执行计划。可用于SELECT, DELETE, INSERT, REPLACE, and UPDATE statements

例子

mysql> explain SELECT COUNT(*) FROM vehicle_prod WHERE fin LIKE '%00000' \G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: vehicle_prod
   partitions: NULL
         type: index
possible_keys: NULL
          key: idx_vehicle_prod_fin
      key_len: 203
          ref: NULL
         rows: 3182812
     filtered: 11.11
        Extra: Using where; Using index
1 row in set, 1 warning (0.00 sec)

其中：

id

The SELECT identifier

select_type

The SELECT type

• SIMPLE:Simple SELECT (not using UNION or subqueries)
• PRIMARY:Outermost SELECT
• UNION:Second or later SELECT statement in a UNION
• SUBQUERY:First SELECT in subquery
• ...

table

The table for the output row

partitions

The matching partitions

type

The join type

possible_keys

The possible indexes to choose

key

The index actually chosen

key_len

The length of the chosen key

ref

The columns compared to the index

rows

Estimate of rows to be examined

filtered

Percentage of rows filtered by table condition

Extra

Additional information

It is possible for key to name an index that is not present in the possible_keys value. This can happen if none of the possible_keys indexes are suitable for looking up rows, but all the columns selected by the query are columns of some other index. That is, the named index covers the selected columns, so although it is not used to determine which rows to retrieve, an index scan is more efficient than a data row scan.

Extra显示的索引扫描方式:

• using where：A WHERE clause is used to restrict which rows to match against the next table or send to the client. Unless you specifically intend to fetch or examine all rows from the table, you may have something wrong in your query if the Extra value is not Using where and the table join type is ALL or index.
• using index condition：Tables are read by accessing index tuples and testing them first to determine whether to read full table rows. In this way, index information is used to defer (“push down”) reading full table rows unless it is necessary.
• using index：The column information is retrieved from the table using only information in the index tree without having to do an additional seek to read the actual row. This strategy can be used when the query uses only columns that are part of a single index. For InnoDB tables that have a user-defined clustered index, that index can be used even when Using index is absent from the Extra column. This is the case if type is index and key is PRIMARY.
• using index & using where：查询使用了索引，但是需要的数据都在索引列中能找到，不需要回表查询数据。

1. https://dev.mysql.com/doc/refman/8.0/en/explain-output.html