首页 > 数据库 > 数据库其它

数据库Mysql-explain执行计划

admin 数据库其它 2021-05-25 09:26:28
后台-系统设置-扩展变量-手机广告位-内容正文底部

关于Mysql的执行计划这块,主要有两个地方可以看:explain 和 optimization trace,这篇博客就讲一讲explain的用法吧

Explain示例

数据

首先涉及的两个表是来自tpcc的表,tpcc使用的是github上的oltpbench。关于如何使用oltpbench,有时间再写个教程。

这两个表的schema信息如下:

customer表

mysql> desc CUSTOMER;
+----------------+---------------+------+-----+-------------------+-------------------+
| Field          | Type          | Null | Key | Default           | Extra             |
+----------------+---------------+------+-----+-------------------+-------------------+
| C_W_ID         | int           | NO   | PRI | NULL              |                   |
| C_D_ID         | int           | NO   | PRI | NULL              |                   |
| C_ID           | int           | NO   | PRI | NULL              |                   |
| C_DISCOUNT     | decimal(4,4)  | NO   |     | NULL              |                   |
| C_CREDIT       | char(2)       | NO   |     | NULL              |                   |
| C_LAST         | varchar(16)   | NO   |     | NULL              |                   |
| C_FIRST        | varchar(16)   | NO   |     | NULL              |                   |
| C_CREDIT_LIM   | decimal(12,2) | NO   |     | NULL              |                   |
| C_BALANCE      | decimal(12,2) | NO   |     | NULL              |                   |
| C_YTD_PAYMENT  | float         | NO   |     | NULL              |                   |
| C_PAYMENT_CNT  | int           | NO   |     | NULL              |                   |
| C_DELIVERY_CNT | int           | NO   |     | NULL              |                   |
| C_STREET_1     | varchar(20)   | NO   |     | NULL              |                   |
| C_STREET_2     | varchar(20)   | NO   |     | NULL              |                   |
| C_CITY         | varchar(20)   | NO   |     | NULL              |                   |
| C_STATE        | char(2)       | NO   |     | NULL              |                   |
| C_ZIP          | char(9)       | NO   |     | NULL              |                   |
| C_PHONE        | char(16)      | NO   |     | NULL              |                   |
| C_SINCE        | timestamp     | NO   |     | CURRENT_TIMESTAMP | DEFAULT_GENERATED |
| C_MIDDLE       | char(2)       | NO   |     | NULL              |                   |
| C_DATA         | varchar(500)  | NO   |     | NULL              |                   |
+----------------+---------------+------+-----+-------------------+-------------------+

oorder表

mysql> desc OORDER;
+--------------+--------------+------+-----+-------------------+-------------------+
| Field        | Type         | Null | Key | Default           | Extra             |
+--------------+--------------+------+-----+-------------------+-------------------+
| O_W_ID       | int          | NO   | PRI | NULL              |                   |
| O_D_ID       | int          | NO   | PRI | NULL              |                   |
| O_ID         | int          | NO   | PRI | NULL              |                   |
| O_C_ID       | int          | NO   |     | NULL              |                   |
| O_CARRIER_ID | int          | YES  |     | NULL              |                   |
| O_OL_CNT     | decimal(2,0) | NO   |     | NULL              |                   |
| O_ALL_LOCAL  | decimal(1,0) | NO   |     | NULL              |                   |
| O_ENTRY_D    | timestamp    | NO   |     | CURRENT_TIMESTAMP | DEFAULT_GENERATED |
+--------------+--------------+------+-----+-------------------+-------------------+

SQL

select sum(O_OL_CNT) from CUSTOMER,OORDER where C_W_ID=O_W_ID and O_ALL_LOCAL > 0;

这个查询啥意思我也不知道,瞎搞的,主要看它执行计划。

mysql8016的explain以后就支持 format=tree和format=json了。8018就支持 EXPLAIN ANALYZE了,但是这个ANALYZE是会跑一遍sql语句的,所以可能会很慢。另外,mysql workbench 还可以生成可视化的执行计划,也无需配置,直接点击Explain按钮默认就是可视化的执行计划了。

我这里是8024版本,所以分别从format的三种形式来看这个explain的含义。

默认Explain

mysql> EXPLAIN select sum(O_OL_CNT) from CUSTOMER,OORDER where C_W_ID=O_W_ID and O_ALL_LOCAL > 0\G
*************************** 1. row ***************************
           id: 1  -- 表示查询的query block,id 号大的先执行,同样的号则从上往下执行
  select_type: SIMPLE -- select 类型
        table: OORDER -- 表名
   partitions: NULL -- 匹配 的partition
         type: ALL -- 表的访问方式,ALL表示扫描全表
possible_keys: PRIMARY,O_W_ID,IDX_ORDER -- 可能选用的索引
          key: NULL -- 实际使用的索引
      key_len: NULL -- 使用的索引长度
          ref: NULL -- 引用到上一个表的列
         rows: 412448 -- 可能需要扫描的行数 
     filtered: 33.33 -- SQL 语句执行后返回结果的行数占读取行数的百分比,值越大越好
        Extra: Using where -- 表示存储层检索出来记录后,计算层使用where条件过滤
*************************** 2. row ***************************
           id: 1
  select_type: SIMPLE
        table: CUSTOMER
   partitions: NULL
         type: ref -- 这里ref表示使用索引进行扫描
possible_keys: PRIMARY,IDX_CUSTOMER_NAME
          key: PRIMARY -- 表示使用的是主键索引进行扫描,因为逐渐索引有多个键,因此不是eq_ref
      key_len: 4 
          ref: tpcc.OORDER.O_W_ID -- 引用表的列
         rows: 74 -- 扫描的行数,对于join查询,则是每次嵌套查询时所需要的行数,可以认为ORRDER表每扫描一行,CUSTOMER表就要扫描74行,当然可以直接走索引
     filtered: 100.00
        Extra: Using index -- 使用了覆盖索引进行扫描,也就是不需要回表,因为这个查询中就涉及CUSTOMER表的1列

format = tree

tree 类型显示的是物理执行计划,包括很多优化,更加直接

mysql> explain format= tree select sum(O_OL_CNT) from CUSTOMER,OORDER where C_W_ID=O_W_ID and O_ALL_LOCAL > 0\G
*************************** 1. row ***************************
EXPLAIN: -> Aggregate: sum(OORDER.O_OL_CNT)
    -> Nested loop inner join  (cost=1102280.63 rows=10189754) -- 使用的 join方式是loop inner join
        -> Filter: (OORDER.O_ALL_LOCAL > 0)  (cost=41589.80 rows=137469) -- 2.外循环,过滤条件 
            -> Table scan on OORDER  (cost=41589.80 rows=412448) -- 1.先扫描外表
        -> Index lookup on CUSTOMER using PRIMARY (C_W_ID=OORDER.O_W_ID)  (cost=0.30 rows=74) -- 3.内循环,如果外循环过滤出有效记录,则使用 该条件过滤出内循环记录

从tree上可以看到的是物理执行计划,缩进表示子计划,是先于外层执行的。整个可以看成一棵树,树的叶子节点先执行,然后才能执行根节点。

format=json

json格式信息更加丰富

mysql> explain format=json select sum(O_OL_CNT) from CUSTOMER,OORDER where C_W_ID=O_W_ID and O_ALL_LOCAL > 0\G
*************************** 1. row ***************************
EXPLAIN: {
  "query_block": {
    "select_id": 1, -- 对应于默认格式的id
    "cost_info": {
      "query_cost": "1102280.63"
    },
    "nested_loop": [
      {
        "table": {
          "table_name": "OORDER",
          "access_type": "ALL",
          "possible_keys": [
            "PRIMARY",
            "O_W_ID",
            "IDX_ORDER"
          ],
          "rows_examined_per_scan": 412448, #扫描的行数
          "rows_produced_per_join": 137468, #筛选后的行数
          "filtered": "33.33",
          "cost_info": {
            "read_cost": "27842.91",
            "eval_cost": "13746.89",
            "prefix_cost": "41589.80", # 单次查询总成本=read_cost+eval_cost
            "data_read_per_join": "4M" # 读取数据量	
          },
          "used_columns": [
            "O_W_ID",
            "O_OL_CNT",
            "O_ALL_LOCAL"
          ],
          "attached_condition": "(`tpcc`.`OORDER`.`O_ALL_LOCAL` > 0)"
        }
      },
      {
        "table": {
          "table_name": "CUSTOMER",
          "access_type": "ref",
          "possible_keys": [
            "PRIMARY",
            "IDX_CUSTOMER_NAME"
          ],
          "key": "PRIMARY",
          "used_key_parts": [
            "C_W_ID"
          ],
          "key_length": "4",
          "ref": [
            "tpcc.OORDER.O_W_ID"
          ],
          "rows_examined_per_scan": 74,
          "rows_produced_per_join": 10189754,
          "filtered": "100.00",
          "using_index": true,
          "cost_info": {
            "read_cost": "41715.39",
            "eval_cost": "1018975.44", # 这个是累积的cost,因为内表需要扫描多次
            "prefix_cost": "1102280.63",# 需要加上外表的prefix_cost
            "data_read_per_join": "24G"
          },
          "used_columns": [
            "C_W_ID"
          ]
        }
      }
    ]
  }
}

Explain各字段含义

1. ID列

  • ID列中的如果数据为一组数字,表示执行SELECT语句的顺序;如果为NULL,则说明这一行数据是由另外两个SQL语句进行 UNION操作后产生的结果集
  • ID值相同时,说明SQL执行顺序是按照显示的从上至下执行的
  • ID值不同时,ID值越大代表优先级越高,则越先被执行

2. SELECT_TYPE列

值含义SIMPLE不包含子查询或是UNION操作的查询PRIMARY查询中如果包含任何子查询,那么最外层的查询则被标记为PRIMARYSUBQUERYSELECT 列表中的子查询DEPENDENT SUBQUERY依赖外部结果的子查询UNIONUnion操作的第二个或是之后的查询的值为unionDEPENDENT UNION当UNION作为子查询时,第二或是第二个后的查询的select_type值UNION RESULTUNION产生的结果集DERIVED出现在FROM子句中的子查询

3. TABLE列

包含以下几种结果:

  • 输出去数据行所在表的名称,如果表取了别名,则显示的是别名
  • <union M,N>: 由ID为M,N查询union产生的结果集
  • <derived N> /<subquery N> :由ID为N的子查询产生的结果

4. PARTITIONS列:

查询匹配的记录来自哪一个分区

  • 对于分区表,显示查询的分区ID
  • 对于非分区表,显示为NULL

5. TYPE列

按性能从高至低排列如下:

值含义system这是const联接类型的一个特例,当查询的表只有一行时使用const表中有且只有一个匹配的行时使用,如对主键或是唯一索引的查询,这是效率最高的联接方式eq_ref唯一索引或主键索引查询,对应每个索引键,表中只有一条记录与之匹配ref非唯一索引查找,返回匹配某个单独值的所有行ref_or_null类似于ref类型的查询,但是附加了对NULL值列的查询index_merge该联接类型表示使用了索引合并优化方法range索引范围扫描,常见于between、>、<这样的查询条件indexFULL index Scan 全索引扫描,同ALL的区别是,遍历的是索引树ALLFULL TABLE Scan 全表扫描,这是效率最差的联接方式

6. Extra列

包含MySQL如何执行查询的附加信息

值含义Distinct优化distinct操作,在找到第一个匹配的元素后即停止查找Not exists使用not exists来优化查询Using filesort使用额外操作进行排序,通常会出现在order by或group by查询中Using index使用了覆盖索引进行查询Using temporaryMySQL需要使用临时表来处理查询,常见于排序,子查询,和分组查询Using where需要在MySQL服务器层使用WHERE条件来过滤数据select tables optimized away直接通过索引来获得数据,不用访问表,这种情况通常效率是最高的

7. POSSIBLE_KEYS列

  • 指出MySQL能使用哪些索引来优化查询
  • 查询列所涉及到的列上的索引都会被列出,但不一定会被使用

8. KEY列

  • 查询优化器优化查询实际所使用的索引
  • 如果表中没有可用的索引,则显示为NULL
  • 如果查询使用了覆盖索引,则该索引仅出现在Key列中

9. KEY_LEN列

显示MySQL索引所使用的字节数,在联合索引中如果有3列,假如3列字段总长度为100个字节,Key_len显示的可能会小于100字节,比如30字节,这就说明在查询过程中没有使用到联合索引的所有列,只是利用到了前面的一列或2列

  • 表示索引字段的最大可能长度
  • Key_len的长度由字段定义计算而来,并非数据的实际长度

10. Ref列

  • 表示当前表在利用Key列记录中的索引进行查询时所用到的列或常量

11. rows列

  • 表示MySQL通过索引的统计信息,估算出来的所需读取的行数(关联查询时,显示的是每次嵌套查询时所需要的行数)
  • Rows值的大小是个统计抽样结果,并不十分准确

12. Filtered列

  • 表示返回结果的行数占需读取行数的百分比
  • Filtered列的值越大越好(值越大,表明实际读取的行数与所需要返回的行数越接近)
  • Filtered列的值依赖统计信息,所以同样也不是十分准确,只是一个参考值

参考

(4) MySQL中EXPLAIN执行计划分析 - 听风。 - 博客园 (cnblogs.com)

MySQL :: MySQL 8.0 Reference Manual :: 13.8.2 EXPLAIN Statement

(1条消息) EXPLAIN FORMAT=json和EXPLAIN ANALYZE查询计划解读_Hello World-CSDN博客

文章来源:https://blog.csdn.net/Fei20140908/article/details/117158905

后台-系统设置-扩展变量-手机广告位-内容正文底部
版权声明

本文仅代表作者观点,不代表本站立场。
本文系作者授权发表,未经许可,不得转载。
本文地址:https://www.jcdi.cn/shujukuqita/30801.html

留言与评论(共有 0 条评论)
   
验证码:
后台-系统设置-扩展变量-手机广告位-评论底部广告位

教程弟

https://www.jcdi.cn/

统计代码 | 京ICP1234567-2号

Powered By 教程弟 教程弟

使用手机软件扫描微信二维码