EXPLAIN Statement

Reference:

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

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

1、Obtaining Table Structure Information

DESCRIBE|EXPLAIN order_tm_order_item;

key---PRI主键约束；UNI唯一约束；MUL可以重复 

The DESCRIBE and EXPLAIN statements are synonyms. In practice, the DESCRIBE keyword is more often used to obtain information about table structure, whereas EXPLAIN is used to obtain a query execution plan (that is, an explanation of how MySQL would execute a query).  

2、Obtaining Execution Plan Information

{EXPLAIN | DESCRIBE | DESC}
    [explain_type]
    {explainable_stmt | FOR CONNECTION connection_id}

{EXPLAIN | DESCRIBE | DESC} ANALYZE select_statement    

explain_type: {
    FORMAT = format_name
}

format_name: {
    TRADITIONAL
  | JSON
  | TREE
}

explainable_stmt: {
    SELECT statement
  | DELETE statement
  | INSERT statement
  | REPLACE statement
  | UPDATE statement
}

 例1

EXPLAIN SELECT *  FROM order_tm_order_info WHERE order_no = '201912101752480001';

例2

EXPLAIN FORMAT=JSON SELECT `name`,`code` FROM product_spu WHERE id = 100;

字段解释：

 

Possible_keys:  The possible_keys column indicates the indexes from which MySQL can choose to find the rows in this table.If this column is   NULL (or undefined in JSON-formatted output), there are no relevant indexes. In this case, you may be able to improve the performance of your query by examining the WHERE clause to check whether it refers to some column or columns that would be suitable for indexing.  tips：To see what indexes a table has, use SHOW INDEX FROM tbl_name.

Key:  The key column indicates the key (index) that MySQL actually decided to use. If MySQL decides to use one of the possible_keys indexes to look up rows, that index is listed as the key value.

Rows:  The rows column indicates the number of rows MySQL believes it must examine to execute the query.