1、查询计划
SQL语句只告诉机器干什么，没有告诉具体怎么干。DBMS内嵌了查询优化器，对用户透明。
但是有时候我们写的SQL语言查询很慢，就需要通过查询计划看看机器具体是怎么执行这个SQL的，确定查询慢的瓶颈问题，然后修改SQL进行优化。
2、MySQL查询计划
（1）MySQL使用explain + sql语法查看执行计划
MariaDB [(none)]> use test;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
MariaDB [test]> EXPLAIN SELECT * FROM test.users;
+------+-------------+-------+------+---------------+------+---------+------+------+-------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+------+-------------+-------+------+---------------+------+---------+------+------+-------+
| 1 | SIMPLE | users | ALL | NULL | NULL | NULL | NULL | 3 | |
+------+-------------+-------+------+---------------+------+---------+------+------+-------+
1 row in set (0.00 sec)
MariaDB [test]>
（2）该查询类型select_type是简单查询SIMPLE,此外还有其他查询类型
- SIMPLE ：简单查询
- PRIMARY：最外层查询
- SUBQUERY：映射为子查询
- DERIVED：子查询
- UNION：联合
- UNION RESULT：使用联合的结果
（3）table字段表示MySQL在表中找到所需行的方式，又称访问类型。
- ALL：全数据表扫描
- INDEX：全索引表扫描
- RANGE：对索引列进行范围查找，常见于between、<、>等的查询
- INDEX_MERGE：合并索引，使用多个单列索引搜索
- REF：根据索引查找一个或多个值
- EQ_REF：扫描时使用primary key 或 unique类型
- CONST：const表很快,只读取一次
- SYSTEM：system是const类型的特例，表仅有一行。
（4）查询性能
ALL < index < range < index_merge < ref_or_null < ref < eq_ref < system/const
2、Doris的查询计划
分布式查询计划是由多个 Fragment 组成的，每个 Fragment 负责查询计划的一部分，各个 Fragment 直接会通过 ExchangeNode 算子进行数据的传输。
（1）简单查询
MySQL [tpa]> explain graph SELECT * FROM t1;
+-----------------------------------------------------------------+
| Explain String |
+-----------------------------------------------------------------+
| |
| ┌───────────────┐ |
| │[1: ResultSink]│ |
| │[Fragment: 1] │ |
| │RESULT SINK │ |
| └───────────────┘ |
| │ |
| │ |
| ┌─────────────┐ |
| │[1: EXCHANGE]│ |
| │[Fragment: 1]│ |
| └─────────────┘ |
| │ |
| │ |
| ┌───────────────────┐ |
| │[1: DataStreamSink]│ |
| │[Fragment: 0] │ |
| │STREAM DATA SINK │ |
| │ EXCHANGE ID: 01 │ |
| │ UNPARTITIONED │ |
| └───────────────────┘ |
| │ |
| │ |
| ┌─────────────────┐ |
| │[0: OlapScanNode]│ |
| │[Fragment: 0] │ |
| │TABLE: t1 │ |
| └─────────────────┘ |
+-----------------------------------------------------------------+
28 rows in set (0.00 sec)
MySQL [tpa]> explain SELECT * FROM t1;
+-------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Explain String |
+-------------------------------------------------------------------------------------------------------------------------------------------------------------+
| PLAN FRAGMENT 0 |
| OUTPUT EXPRS:`default_cluster:tpa.t1`.`siteid` | `default_cluster:tpa.t1`.`citycode` | `default_cluster:tpa.t1`.`username` | `default_cluster:tpa.t1`.`pv` |
| PARTITION: UNPARTITIONED |
| |
| RESULT SINK |
| |
| 1:EXCHANGE |
| |
| PLAN FRAGMENT 1 |
| OUTPUT EXPRS: |
| PARTITION: RANDOM |
| |
| STREAM DATA SINK |
| EXCHANGE ID: 01 |
| UNPARTITIONED |
| |
| 0:OlapScanNode |
| TABLE: t1 |
| PREAGGREGATION: OFF. Reason: No AggregateInfo |
| partitions=1/1 |
| rollup: t1 |
| tabletRatio=10/10 |
| tabletList=439810,439813,439816,439819,439822,439825,439828,439831,439834,439837 |
| cardinality=5 |
| avgRowSize=30.0 |
| numNodes=3 |
+-------------------------------------------------------------------------------------------------------------------------------------------------------------+
26 rows in set (0.00 sec)
MySQL [tpa]>
（2）聚合
MySQL [tpa]> explain graph SELECT SUM(t1.pv) FROM t1;
+-----------------------------------------------------------------------------------------------------------+
| Explain String |
+-----------------------------------------------------------------------------------------------------------+
| |
| ┌───────────────┐ |
| │[3: ResultSink]│ |
| │[Fragment: 1] │ |
| │RESULT SINK │ |
| └───────────────┘ |
| │ |
| │ |
| ┌───────────────────────────────┐ |
| │[3: AGGREGATE (merge finalize)]│ |
| │[Fragment: 1] │ |
| └───────────────────────────────┘ |
| │ |
| │ |
| ┌─────────────┐ |
| │[2: EXCHANGE]│ |
| │[Fragment: 1]│ |
| └─────────────┘ |
| │ |
| │ |
| ┌───────────────────┐ |
| │[2: DataStreamSink]│ |
| │[Fragment: 0] │ |
| │STREAM DATA SINK │ |
| │ EXCHANGE ID: 02 │ |
| │ UNPARTITIONED │ |
| └───────────────────┘ |
| │ |
| │ |
| ┌─────────────────────────────────┐ |
| │[1: AGGREGATE (update serialize)]│ |
| │[Fragment: 0] │ |
| └─────────────────────────────────┘ |
| │ |
| │ |
| ┌─────────────────┐ |
| │[0: OlapScanNode]│ |
| │[Fragment: 0] │ |
| │TABLE: t1 │ |
| └─────────────────┘ |
+-----------------------------------------------------------------------------------------------------------+
40 rows in set (0.01 sec)
MySQL [tpa]> explain SELECT SUM(t1.pv) FROM t1;
+---------------------------------------------------------------------------------------+
| Explain String |
+---------------------------------------------------------------------------------------+
| PLAN FRAGMENT 0 |
| OUTPUT EXPRS: sum(`t1`.`pv`) |
| PARTITION: UNPARTITIONED |
| |
| RESULT SINK |
| |
| 3:AGGREGATE (merge finalize) |
| | output: sum( sum(`t1`.`pv`)) |
| | group by: |
| | cardinality=-1 |
| | |
| 2:EXCHANGE |
| |
| PLAN FRAGMENT 1 |
| OUTPUT EXPRS: |
| PARTITION: RANDOM |
| |
| STREAM DATA SINK |
| EXCHANGE ID: 02 |
| UNPARTITIONED |
| |
| 1:AGGREGATE (update serialize) |
| | output: sum(`t1`.`pv`) |
| | group by: |
| | cardinality=1 |
| | |
| 0:OlapScanNode |
| TABLE: t1 |
| PREAGGREGATION: ON |
| partitions=1/1 |
| rollup: t1 |
| tabletRatio=10/10 |
| tabletList=439810,439813,439816,439819,439822,439825,439828,439831,439834,439837 |
| cardinality=4 |
| avgRowSize=8.0 |
| numNodes=3 |
+---------------------------------------------------------------------------------------+
36 rows in set (0.01 sec)
MySQL [tpa]>
（3）关联查询
MySQL [tpa]> SELECT SUM(t1.pv) FROM t1 JOIN t2 WHERE t1.siteid = t2.siteid;
+----------------+
| sum(`t1`.`pv`) |
+----------------+
| 12 |
+----------------+
1 row in set (0.29 sec)
MySQL [tpa]> SELECT SUM(pv) FROM t2 WHERE siteid IN (SELECT siteid FROM t1 WHERE siteid > 2);
+-----------+
| sum(`pv`) |
+-----------+
| 8 |
+-----------+
1 row in set (0.36 sec)
MySQL [tpa]> explain SELECT SUM(t1.pv) FROM t1 JOIN t2 WHERE t1.siteid = t2.siteid;
+---------------------------------------------------------------------------------------+
| Explain String |
+---------------------------------------------------------------------------------------+
| PLAN FRAGMENT 0 |
| OUTPUT EXPRS:<slot 3> sum(`t1`.`pv`) |
| PARTITION: UNPARTITIONED |
| |
| RESULT SINK |
| |
| 6:AGGREGATE (merge finalize) |
| | output: sum(<slot 3> sum(`t1`.`pv`)) |
| | group by: |
| | cardinality=-1 |
| | |
| 5:EXCHANGE |
| |
| PLAN FRAGMENT 1 |
| OUTPUT EXPRS: |
| PARTITION: RANDOM |
| |
| STREAM DATA SINK |
| EXCHANGE ID: 05 |
| UNPARTITIONED |
| |
| 3:AGGREGATE (update serialize) |
| | output: sum(`t1`.`pv`) |
| | group by: |
| | cardinality=1 |
| | |
| 2:HASH JOIN |
| | join op: INNER JOIN (BUCKET_SHUFFLE) |
| | runtime filter: true |
| | hash predicates: |
| | colocate: false, reason: table not in the same group |
| | equal join conjunct: `t1`.`siteid` = `t2`.`siteid` |
| | cardinality=4 |
| | |
| |----4:EXCHANGE |
| | |
| 0:OlapScanNode |
| TABLE: t1 |
| PREAGGREGATION: ON |
| partitions=1/1 |
| rollup: t1 |
| tabletRatio=10/10 |
| tabletList=439810,439813,439816,439819,439822,439825,439828,439831,439834,439837 |
| cardinality=4 |
| avgRowSize=12.0 |
| numNodes=3 |
| |
| PLAN FRAGMENT 2 |
| OUTPUT EXPRS: |
| PARTITION: RANDOM |
| |
| STREAM DATA SINK |
| EXCHANGE ID: 04 |
| BUCKET_SHFFULE_HASH_PARTITIONED: `t2`.`siteid` |
| |
| 1:OlapScanNode |
| TABLE: t2 |
| PREAGGREGATION: OFF. Reason: null |
| partitions=1/3 |
| rollup: t2 |
| tabletRatio=10/10 |
| tabletList=440143,440146,440149,440152,440155,440158,440161,440164,440167,440170 |
| cardinality=0 |
| avgRowSize=4.0 |
| numNodes=1 |
+---------------------------------------------------------------------------------------+
65 rows in set (0.73 sec)
MySQL [tpa]> explain graph SELECT SUM(t1.pv) FROM t1 JOIN t2 WHERE t1.siteid = t2.siteid;
+-------------------------------------------------------------------------------------------------------------------------------+
| Explain String |
+-------------------------------------------------------------------------------------------------------------------------------+
| |
| ┌───────────────┐ |
| │[6: ResultSink]│ |
| │[Fragment: 2] │ |
| │RESULT SINK │ |
| └───────────────┘ |
| │ |
| │ |
| ┌───────────────────────────────┐ |
| │[6: AGGREGATE (merge finalize)]│ |
| │[Fragment: 2] │ |
| └───────────────────────────────┘ |
| │ |
| │ |
| ┌─────────────┐ |
| │[5: EXCHANGE]│ |
| │[Fragment: 2]│ |
| └─────────────┘ |
| │ |
| │ |
| ┌───────────────────┐ |
| │[5: DataStreamSink]│ |
| │[Fragment: 0] │ |
| │STREAM DATA SINK │ |
| │ EXCHANGE ID: 05 │ |
| │ UNPARTITIONED │ |
| └───────────────────┘ |
| │ |
| │ |
| ┌─────────────────────────────────┐ |
| │[3: AGGREGATE (update serialize)]│ |
| │[Fragment: 0] │ |
| └─────────────────────────────────┘ |
| │ |
| │ |
| ┌────────────────────────────────────┐ |
| │[2: HASH JOIN] │ |
| │[Fragment: 0] │ |
| │join op: INNER JOIN (BUCKET_SHUFFLE)│ |
| └────────────────────────────────────┘ |
| ┌─────────────────┴─────────┐ |
| │ │ |
| ┌─────────────────┐ ┌─────────────┐ |
| │[0: OlapScanNode]│ │[4: EXCHANGE]│ |
| │[Fragment: 0] │ │[Fragment: 0]│ |
| │TABLE: t1 │ └─────────────┘ |
| └─────────────────┘ │ |
| │ |
| ┌─────────────────────────────────┐ |
| │[4: DataStreamSink] │ |
| │[Fragment: 1] │ |
| │STREAM DATA SINK │ |
| │ EXCHANGE ID: 04 │ |
| │ BUCKET_SHFFULE_HASH_PARTITIONED│ |
| └─────────────────────────────────┘ |
| │ |
| │ |
| ┌─────────────────┐ |
| │[1: OlapScanNode]│ |
| │[Fragment: 1] │ |
| │TABLE: t2 │ |
| └─────────────────┘ |
+-------------------------------------------------------------------------------------------------------------------------------+
62 rows in set (0.21 sec)
（4）查询 Profile
Profile 包含查询语句各个节点的具体执行情况，可以帮助我们分析查询瓶颈。
先开启is_report_success
MySQL [tpa]> show query profile "/"\G
Empty set (0.01 sec)
MySQL [tpa]> SET is_report_success=true;
Query OK, 0 rows affected (0.00 sec)
MySQL [tpa]> show query profile "/"\G
Empty set (0.00 sec)
MySQL [tpa]> SELECT SUM(t1.pv) FROM t1 JOIN t2 WHERE t1.siteid = t2.siteid;
+----------------+
| sum(`t1`.`pv`) |
+----------------+
| 12 |
+----------------+
1 row in set (2.45 sec)
MySQL [tpa]> show query profile "/"\G
*************************** 1. row ***************************
QueryId: 15400462342f4d04-b294791b760d77e7
User: default_cluster:test
DefaultDb: default_cluster:tpa
SQL: SELECT SUM(t1.pv) FROM t1 JOIN t2 WHERE t1.siteid = t2.siteid
QueryType: Query
StartTime: 2021-08-26 09:54:04
EndTime: 2021-08-26 09:54:04
TotalTime: 118ms
QueryState: EOF
1 row in set (0.00 sec)
MySQL [tpa]> show query profile "/15400462342f4d04-b294791b760d77e7"\G
*************************** 1. row ***************************
Fragments:
┌────────────────────────┐
│[-1: DataBufferSender] │
│Fragment: 0 │
│MaxActiveTime: 106.143ms│
└────────────────────────┘
│
│
┌─────────────────────┐
│[6: AGGREGATION_NODE]│
│Fragment: 0 │
└─────────────────────┘
│
│
┌──────────────────┐
│[5: EXCHANGE_NODE]│
│Fragment: 0 │
└──────────────────┘
│
│
┌────────────────────────┐
│[5: DataStreamSender] │
│Fragment: 1 │
│MaxActiveTime: 101.428ms│
└────────────────────────┘
│
│
┌─────────────────────┐
│[3: AGGREGATION_NODE]│
│Fragment: 1 │
└─────────────────────┘
│
│
┌───────────────────┐
│[2: HASH_JOIN_NODE]│
│Fragment: 1 │
└───────────────────┘
┌────────────┴──────────┐
│ │
┌───────────────────┐ ┌──────────────────┐
│[0: OLAP_SCAN_NODE]│ │[4: EXCHANGE_NODE]│
│Fragment: 1 │ │Fragment: 1 │
└───────────────────┘ └──────────────────┘
│ │
│ │
┌─────────────┐ ┌───────────────────────┐
│[OlapScanner]│ │[4: DataStreamSender] │
│Fragment: 1 │ │Fragment: 2 │
└─────────────┘ │MaxActiveTime: 79.987ms│
│ └───────────────────────┘
│ │
┌─────────────────┐ │
│[SegmentIterator]│ ┌───────────────────┐
│Fragment: 1 │ │[1: OLAP_SCAN_NODE]│
└─────────────────┘ │Fragment: 2 │
└───────────────────┘
│
│
┌─────────────┐
│[OlapScanner]│
│Fragment: 2 │
└─────────────┘
│
│
┌─────────────────┐
│[SegmentIterator]│
│Fragment: 2 │
└─────────────────┘
1 row in set (0.00 sec)
MySQL [tpa]>
3001
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
