关于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 | 查询中如果包含任何子查询，那么最外层的查询则被标记为PRIMARY |
| SUBQUERY | SELECT 列表中的子查询 |
| DEPENDENT SUBQUERY | 依赖外部结果的子查询 |
| UNION | Union操作的第二个或是之后的查询的值为union |
| DEPENDENT UNION | 当UNION作为子查询时，第二或是第二个后的查询的select_type值 |
| UNION RESULT | UNION产生的结果集 |
| 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、>、<这样的查询条件 |
| index | FULL index Scan 全索引扫描，同ALL的区别是，遍历的是索引树 |
| ALL | FULL TABLE Scan 全表扫描，这是效率最差的联接方式 |
6. Extra列
包含MySQL如何执行查询的附加信息
| 值 | 含义 |
|---|---|
| Distinct | 优化distinct操作，在找到第一个匹配的元素后即停止查找 |
| Not exists | 使用not exists来优化查询 |
| Using filesort | 使用额外操作进行排序，通常会出现在order by或group by查询中 |
| Using index | 使用了覆盖索引进行查询 |
| Using temporary | MySQL需要使用临时表来处理查询，常见于排序，子查询，和分组查询 |
| 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博客
779
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
