MySQL explain

来自WHY42
Riguz留言 | 贡献2021年5月3日 (一) 11:17的版本 →‎例子

Explain

Explain用来查看执行计划。可用于, , , , and 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 value. This can happen if none of the 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.


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