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.