mysql 8.0 使用 explain 查看执行计划

拼搏现实的明天。 2023-03-03 14:27 47阅读 0赞

os: centos 7.4.1708  
db: mysql 8.0.20

# help explain #

mysql> help explain;
    Name: 'EXPLAIN'
    Description:
    Syntax:
    {EXPLAIN | DESCRIBE | DESC}
        tbl_name [col_name | wild]
    
    {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
      | TABLE statement
      | DELETE statement
      | INSERT statement
      | REPLACE statement
      | UPDATE statement
    }
    
    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).
    
    URL: https://dev.mysql.com/doc/refman/8.0/en/explain.html

# format = TRADITIONAL #

+----+-------------+-------+-------------+-------+-------------------+-------------------+---------+------------+---------+----------+-------------+
    | id | select_type | table | partitions  | type  | possible_keys     | key               | key_len | ref        | rows    | filtered | Extra       |
    +----+-------------+-------+-------------+-------+-------------------+-------------------+---------+------------+---------+----------+-------------+
    |  1 | SIMPLE      | t0    | p0,p1,p2,p3 | index | idx_part_range_id | idx_part_range_id | 4       | NULL       | 3971640 |   100.00 | Using index |
    |  1 | SIMPLE      | t1    | p0,p1,p2,p3 | ref   | idx_part_range_id | idx_part_range_id | 4       | test.t0.id |       1 |   100.00 | Using index |
    +----+-------------+-------+-------------+-------+-------------------+-------------------+---------+------------+---------+----------+-------------+
    2 rows in set, 1 warning (0.00 sec)

# format = JSON #

+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
    | EXPLAIN                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                          |
    +-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
    | {
      "query_block": {
        "select_id": 1,
        "cost_info": {
          "query_cost": "4794804.00"
        },
        "nested_loop": [
          {
            "table": {
              "table_name": "t0",
              "partitions": [
                "p0",
                "p1",
                "p2",
                "p3"
              ],
              "access_type": "index",
              "possible_keys": [
                "idx_part_range_id"
              ],
              "key": "idx_part_range_id",
              "used_key_parts": [
                "id"
              ],
              "key_length": "4",
              "rows_examined_per_scan": 3971640,
              "rows_produced_per_join": 3971640,
              "filtered": "100.00",
              "using_index": true,
              "cost_info": {
                "read_cost": "28836.00",
                "eval_cost": "397164.00",
                "prefix_cost": "426000.00",
                "data_read_per_join": "1G"
              },
              "used_columns": [
                "id"
              ]
            }
          },
          {
            "table": {
              "table_name": "t1",
              "partitions": [
                "p0",
                "p1",
                "p2",
                "p3"
              ],
              "access_type": "ref",
              "possible_keys": [
                "idx_part_range_id"
              ],
              "key": "idx_part_range_id",
              "used_key_parts": [
                "id"
              ],
              "key_length": "4",
              "ref": [
                "test.t0.id"
              ],
              "rows_examined_per_scan": 1,
              "rows_produced_per_join": 3971640,
              "filtered": "100.00",
              "using_index": true,
              "cost_info": {
                "read_cost": "3971640.00",
                "eval_cost": "397164.00",
                "prefix_cost": "4794804.00",
                "data_read_per_join": "1G"
              },
              "used_columns": [
                "id"
              ]
            }
          }
        ]
      }
    } |
    +-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
    1 row in set, 1 warning (0.00 sec)

# format = TREE #

mysql> explain format=tree select count(1) from part_range t0,part_range t1 where t0.id=t1.id;
    +---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
    | EXPLAIN                                                                                                                                                                                                                                                       |
    +---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
    | -> Aggregate: count(1)
        -> Nested loop inner join  (cost=4794804.00 rows=3971640)
            -> Index scan on t0 using idx_part_range_id  (cost=426000.00 rows=3971640)
            -> Index lookup on t1 using idx_part_range_id (id=t0.id)  (cost=1.00 rows=1)
     |
    +---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
    1 row in set (0.00 sec)

format = TREE 的格式更加接近 oracle，postgresql