执行计划输出格式、结果详解

• 对于执行计划的查看，MySQL Server支持explain、desc、describe等几个关键字，以下我们统一使用explain关键字为例，演示如何查看执行计划信息
• 另外，对于执行计划的输出格式，在MySQL 8.0版本中新增了TREE格式，使用explain语句的format选项指定即可（下面简单列出三种输出格式的对比实例，更多信息可参考链接：https://dev.mysql.com/doc/refman/8.0/en/explain.html）

# format=TRADITIONAL 或省略，默认为TRADITIONAL格式
root@localhost : sbtest 07:08:26> explain select s1.id,s2.c,s3.pad from sbtest1 as s1 join sbtest2 as s2 on s1.id=s2.id join sbtest3 as s3 on s1.id=s3.id where s3.id <=10000;
+----+-------------+-------+------------+--------+---------------+---------+---------+--------------+-------+----------+--------------------------+
| id | select_type | table | partitions | type   | possible_keys | key     | key_len | ref          | rows  | filtered | Extra                    |
+----+-------------+-------+------------+--------+---------------+---------+---------+--------------+-------+----------+--------------------------+
|  1 | SIMPLE      | s1    | NULL       | range  | PRIMARY       | PRIMARY | 4       | NULL         | 10250 |   100.00 | Using where; Using index |
|  1 | SIMPLE      | s2    | NULL       | eq_ref | PRIMARY       | PRIMARY | 4       | sbtest.s1.id |     1 |   100.00 | NULL                     |
|  1 | SIMPLE      | s3    | NULL       | eq_ref | PRIMARY       | PRIMARY | 4       | sbtest.s1.id |     1 |   100.00 | NULL                     |
+----+-------------+-------+------------+--------+---------------+---------+---------+--------------+-------+----------+--------------------------+
3 rows in set, 1 warning (0.02 sec)



# format=json
root@localhost : sbtest 07:12:37> explain format=json select s1.id,s2.c,s3.pad from sbtest1 as s1 join sbtest2 as s2 on s1.id=s2.id join sbtest3 as s3 on s1.id=s3.id where s3.id <=10000;
......
| {
  "query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "24392.27"
    },
    "nested_loop": [
      {
        "table": {
          "table_name": "s1",
          "access_type": "range",
          "possible_keys": [
            "PRIMARY"
          ],
          "key": "PRIMARY",
          "used_key_parts": [
            "id"
          ],
          "key_length": "4",
          "rows_examined_per_scan": 10250,
          "rows_produced_per_join": 10250,
          "filtered": "100.00",
          "using_index": true,
          "cost_info": {
            "read_cost": "1035.31",
            "eval_cost": "1025.00",
            "prefix_cost": "2060.31",
            "data_read_per_join": "5M"
          },
          "used_columns": [
            "id"
          ],
          "attached_condition": "(`sbtest`.`s1`.`id` <= 10000)"
        }
      },
      {
        "table": {
          "table_name": "s2",
          "access_type": "eq_ref",
          "possible_keys": [
            "PRIMARY"
          ],
          "key": "PRIMARY",
          "used_key_parts": [
            "id"
          ],
          "key_length": "4",
          "ref": [
            "sbtest.s1.id"
          ],
          "rows_examined_per_scan": 1,
          "rows_produced_per_join": 10250,
          "filtered": "100.00",
          "cost_info": {
            "read_cost": "10137.42",
            "eval_cost": "1025.00",
            "prefix_cost": "13222.73",
            "data_read_per_join": "5M"
          },
          "used_columns": [
            "id",
            "c"
          ]
        }
      },
      {
        "table": {
          "table_name": "s3",
          "access_type": "eq_ref",
          "possible_keys": [
            "PRIMARY"
          ],
          "key": "PRIMARY",
          "used_key_parts": [
            "id"
          ],
          "key_length": "4",
          "ref": [
            "sbtest.s1.id"
          ],
          "rows_examined_per_scan": 1,
          "rows_produced_per_join": 10250,
          "filtered": "100.00",
          "cost_info": {
            "read_cost": "10144.54",
            "eval_cost": "1025.00",
            "prefix_cost": "24392.28",
            "data_read_per_join": "5M"
          },
          "used_columns": [
            "id",
            "pad"
          ]
        }
      }
    ]
  }
} |
......



# format=tree
root@localhost : sbtest 07:12:52> explain format=tree select s1.id,s2.c,s3.pad from sbtest1 as s1 join sbtest2 as s2 on s1.id=s2.id join sbtest3 as s3 on s1.id=s3.id where s3.id <=10000;
......
| -> Nested loop inner join  (cost=24392.28 rows=10250)
    -> Nested loop inner join  (cost=13222.73 rows=10250)
        -> Filter: (s1.id <= 10000)  (cost=2060.31 rows=10250)
            -> Index range scan on s1 using PRIMARY  (cost=2060.31 rows=10250)
        -> Single-row index lookup on s2 using PRIMARY (id=s1.id)  (cost=0.99 rows=1)
    -> Single-row index lookup on s3 using PRIMARY (id=s1.id)  (cost=0.99 rows=1)
 |
......

---

上一篇：explain 语法解析 | 下一篇：执行计划输出列含义简介