开发指南—DAL语句—EXPLAIN-阿里云开发者社区

开发者社区> -技术小能手-> 正文

开发指南—DAL语句—EXPLAIN

简介: 该语句用于解释SQL语句的执行计划,包括SELECT、DELETE、INSERT、REPLACE或UPDATE语句。
+关注继续查看

语法

获取SQL计划信息:


EXPLAIN
{LOGICALVIEW | LOGIC | SIMPLE | DETAIL | EXECUTE | PHYSICAL | OPTIMIZER | SHARDING
 | COST | ANALYZE | BASELINE | JSON_PLAN | ADVISOR} 
 {SELECT statement | DELETE statement | INSERT statement | REPLACE statement| UPDATE statement}

示例

  • explain语句:展示基本的SQL执行计划,该执行计划是算子组成,主要体现SQL在CN上的整个执行过程。
mysql> explain select count(*) from lineitem group by L_ORDERKEY;
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| LOGICAL EXECUTIONPLAN                                                                                                                                                              |
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Project(count(*)="count(*)")                                                                                                                                                       |
|   HashAgg(group="L_ORDERKEY", count(*)="SUM(count(*))")                                                                                                                            |
|     Gather(concurrent=true)                                                                                                                                                        |
|       LogicalView(tables="[000000-000003].lineitem_[00-15]", shardCount=16, sql="SELECT `L_ORDERKEY`, COUNT(*) AS `count(*)` FROM `lineitem` AS `lineitem` GROUP BY `L_ORDERKEY`") |
| HitCache:false                                                                                                                                                                     |                                                                                                                                                               |
| TemplateId: 5819c807                                                                                                                                                               |
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
  • 其中,HitCache标记该查询是否命中PlanCache,取值为false or trueTemplateId表示对该计划的标识,具有全局唯一性。
  • explain logicalview语句:展示LogicalView所表示的下推SQL在DN上的执行计划。
mysql> explain LOGICALVIEW select  mysql> explain select logialview count(*) from lineitem group by L_ORDERKEY;
+----------------------------------------------------------+
| LOGICAL EXECUTIONPLAN                                    |
+----------------------------------------------------------+
| Project(count(*)="count(*)")                             |
|   HashAgg(group="L_ORDERKEY", count(*)="SUM(count(*))")  |
|     Gather(concurrent=true)                              |
|       LogicalView                                        |
|         MysqlAgg(group="L_ORDERKEY", count(*)="COUNT()") |
|           MysqlTableScan(name=[ads, lineitem])           |
| HitCache:true                                            |
| Source:PLAN_CACHE                                        |
| TemplateId: 5819c807
  • explain execute语句:表示下推SQL在mysql的执行情况,这个语句和mysql的explain语句同义。通过该语句可以查看下推SQL在DN上有没有使用索引,有没有做全表扫描。
mysql> explain EXECUTE  select  count(*) from lineitem group by L_ORDERKEY;
+----+-------------+----------+------------+-------+---------------+---------+---------+-----+------+----------+----------------------------------------------+
| id | select_type | table    | partitions | type  | possible_keys | key     | key_len | ref | rows | filtered | Extra                                        |
+----+-------------+----------+------------+-------+---------------+---------+---------+-----+------+----------+----------------------------------------------+
| 1  | SIMPLE      | lineitem | NULL       | index | PRIMARY       | PRIMARY | 8       | NULL | 1    | 100      | Using index; Using temporary; Using filesort |
+----+-------------+----------+------------+-------+---------------+---------+---------+-----+------+----------+----------------------------------------------+
1 row in set (0.24 sec)
  • explain sharding语句:展示当前查询在DN上扫描的物理分片情况。
mysql> explain sharding  select  count(*) from lineitem group by L_ORDERKEY;
+---------------+----------------------------------+-------------+-----------+-----------+
| LOGICAL_TABLE | SHARDING                         | SHARD_COUNT | BROADCAST | CONDITION |
+---------------+----------------------------------+-------------+-----------+-----------+
| lineitem      | [000000-000003].lineitem_[00-15] | 16          | false     |           |
+---------------+----------------------------------+-------------+-----------+-----------+
1 row in set (0.04 sec)
  • explain cost语句:相对于explain语句,除了展示执行计划以外,还会显示各个算子基于统计信息估算的代价,以及这条查询被优化器识别的WORKLOAD。
mysql> explain COST  select  count(*) from lineitem group by L_ORDERKEY;
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| LOGICAL EXECUTIONPLAN                                                                                                                                                                                                                                                                                            |
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Project(count(*)="count(*)"): rowcount = 2508.0, cumulative cost = value = 2.4867663E7, cpu = 112574.0, memory = 88984.0, io = 201.0, net = 4.75, id = 182                                                                                                                                                       |
|   HashAgg(group="L_ORDERKEY", count(*)="SUM(count(*))"): rowcount = 2508.0, cumulative cost = value = 2.4867662E7, cpu = 112573.0, memory = 88984.0, io = 201.0, net = 4.75, id = 180                                                                                                                            |
|     Gather(concurrent=true): rowcount = 2508.0, cumulative cost = value = 2.4860069E7, cpu = 105039.0, memory = 29796.0, io = 201.0, net = 4.75, id = 178                                                                                                                                                        |
|       LogicalView(tables="[000000-000003].lineitem_[00-15]", shardCount=16, sql="SELECT `L_ORDERKEY`, COUNT(*) AS `count(*)` FROM `lineitem` AS `lineitem` GROUP BY `L_ORDERKEY`"): rowcount = 2508.0, cumulative cost = value = 2.4860068E7, cpu = 105038.0, memory = 29796.0, io = 201.0, net = 4.75, id = 109 |
| HitCache:true                                                                                                                                                                                                                                                                                                    |
| Source:PLAN_CACHE                                                                                                                                                                                                                                                                                                |
| WorkloadType: TP                                                                                                                                                                                                                                                                                                 |
| TemplateId: 5819c807
  • explain analyze语句:相对于explain cost语句,除了显示各个算子基于统计信息估算的代价以外,该语句可以收集真实运行过程中算子输出的rowCount等信息。
mysql> explain ANALYZE  select  count(*) from lineitem group by L_ORDERKEY;
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| LOGICAL EXECUTIONPLAN                                                                                                                                                                                                                                                                                                                                                                                    |
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Project(count(*)="count(*)"): rowcount = 2508.0, cumulative cost = value = 2.4867663E7, cpu = 112574.0, memory = 88984.0, io = 201.0, net = 4.75, actual time = 0.001 + 0.000, actual rowcount = 2506, actual memory = 0, instances = 1, id = 182                                                                                                                                                        |
|   HashAgg(group="L_ORDERKEY", count(*)="SUM(count(*))"): rowcount = 2508.0, cumulative cost = value = 2.4867662E7, cpu = 112573.0, memory = 88984.0, io = 201.0, net = 4.75, actual time = 0.000 + 0.000, actual rowcount = 2506, actual memory = 0, instances = 1, id = 180                                                                                                                             |
|     Gather(concurrent=true): rowcount = 2508.0, cumulative cost = value = 2.4860069E7, cpu = 105039.0, memory = 29796.0, io = 201.0, net = 4.75, actual time = 0.000 + 0.000, actual rowcount = 0, actual memory = 0, instances = 0, id = 178                                                                                                                                                            |
|       LogicalView(tables="[000000-000003].lineitem_[00-15]", shardCount=16, sql="SELECT `L_ORDERKEY`, COUNT(*) AS `count(*)` FROM `lineitem` AS `lineitem` GROUP BY `L_ORDERKEY`"): rowcount = 2508.0, cumulative cost = value = 2.4860068E7, cpu = 105038.0, memory = 29796.0, io = 201.0, net = 4.75, actual time = 0.030 + 0.025, actual rowcount = 10000, actual memory = 0, instances = 0, id = 109 |
| HitCache:true                                                                                                                                                                                                                                                                                                                                                                                            |
| Source:PLAN_CACHE                                                                                                                                                                                                                                                                                                                                                                                        |
| TemplateId: 5819c807                                                                                                                                                                                                                                                                                                                                                                                     |
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
7 rows in set (1.08 sec)
  • explain physical语句:展示查询在运行过程中执行模式、各个执行片段(Fragment)的依赖关系和并行度。该查询被识别为单机单线程计划模式(TP_LOCAL),执行计划被分为三个片段Fragment-0、Fragment-1和Fragment-2,先做预聚合再做最终的聚合计算,每个片段的执行度可以不同。
mysql> explain physical   select  count(*) from lineitem group by L_ORDERKEY;
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| PLAN                                                                                                                                                                           |
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| ExecutorMode: TP_LOCAL                                                                                                                                                         |
| Fragment 0 dependency: [] parallelism: 4                                                                                                                                       |
| Gather(concurrent=true)                                                                                                                                                        |
|   LogicalView(tables="[000000-000003].lineitem_[00-15]", shardCount=16, sql="SELECT `L_ORDERKEY`, COUNT(*) AS `count(*)` FROM `lineitem` AS `lineitem` GROUP BY `L_ORDERKEY`") |
| Fragment 1 dependency: [] parallelism: 8                                                                                                                                       |
| LocalBuffer                                                                                                                                                                    |
|   RemoteSource(sourceFragmentIds=[0], type=RecordType(INTEGER L_ORDERKEY, BIGINT count(*)))                                                                                    |
| Fragment 2 dependency: [0, 1] parallelism: 8                                                                                                                                   |
| Project(count(*)="count(*)")                                                                                                                                                   |
|   HashAgg(group="L_ORDERKEY", count(*)="SUM(count(*))")                                                                                                                        |
|     RemoteSource(sourceFragmentIds=[1], type=RecordType(INTEGER L_ORDERKEY, BIGINT count(*)))                                                                                  |
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
11 rows in set (0.10 sec)
  • explain advisor语句:主要是会基于统计信息,分析当前查询的执行计划,给用户推荐可以加速查询的全局二级索引。
mysql> explain advisor   select  count(*) from lineitem group by L_ORDERKEY \G;
*************************** 1. row ***************************
IMPROVE_VALUE: 4.4%
  IMPROVE_CPU: 340.8%
  IMPROVE_MEM: 0.0%
   IMPROVE_IO: 1910.0%
  IMPROVE_NET: 0.0%
 BEFORE_VALUE: 2.48676627E7
   BEFORE_CPU: 112573.7
   BEFORE_MEM: 88983.8
    BEFORE_IO: 201
   BEFORE_NET: 4.7
  AFTER_VALUE: 2.38256249E7
    AFTER_CPU: 25536
    AFTER_MEM: 88983.8
     AFTER_IO: 10
    AFTER_NET: 4.7
 ADVISE_INDEX: ALTER TABLE `ads`.`lineitem` ADD GLOBAL INDEX `__advise_index_gsi_lineitem_L_ORDERKEY`(`L_ORDERKEY`) DBPARTITION BY HASH(`L_ORDERKEY`) TBPARTITION BY HASH(`L_ORDERKEY`) TBPARTITIONS 4;
     NEW_PLAN:
Project(count(*)="count(*)")
  HashAgg(group="L_ORDERKEY", count(*)="SUM(count(*))")
    Gather(concurrent=true)
      IndexScan(tables="[000000-000003].lineitem__what_if_gsi_L_ORDERKEY_[00-15]", shardCount=16, sql="SELECT `L_ORDERKEY`, COUNT(*) AS `count(*)` FROM `lineitem__what_if_gsi_L_ORDERKEY` AS `lineitem__what_if_gsi_L_ORDERKEY` GROUP BY `L_ORDERKEY`")
         INFO: GLOBAL_INDEX
1 row in set (0.13 sec)

版权声明:本文内容由阿里云实名注册用户自发贡献,版权归原作者所有,阿里云开发者社区不拥有其著作权,亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容,填写侵权投诉表单进行举报,一经查实,本社区将立刻删除涉嫌侵权内容。

相关文章
Bitmaps
Bitmaps 称为位图,它不是一种数据类型。网上很多视频教程把Bitmaps称为数据类型,应该是不正确的。
4 0
有人问你什么是CAP?你就把这篇文章发给他
CAP定理是分布式存储系统的基石,分布式系统(distributed system)指的是建立在网络上的软件系统,它是多个计算机节点通过协调工作的方式,共同完成任务的系统。分布式系统解决了单个计算机无法完成的计算和存储任务。但是分布式系统的设计十分复杂,设计者必定面临诸多挑战,比如节点故障、网络分区、异地网络等等问题。
7 0
Redis事务详述
Redis类似大多数成熟的数据库系统一样,提供了事务机制。Redis的事务机制非常简单,它没有严格的事务模型,无法像关系型数据库一样保证操作的原子性。
4 0
ECS使用感受
阿里云服务器初体验
4 0
分页问题|学习笔记
快速学习 分页问题
4 0
跳跃列表
Skip List(跳跃列表)这种随机的数据结构,可以看做是一个二叉树的变种,它在性能上与红黑树、AVL树很相近;但是Skip List(跳跃列表)的实现相比前两者要简单很多,目前Redis的zset实现采用了Skip List(跳跃列表)(其它还有LevelDB等也使用了跳跃列表)。
5 0
布隆(Bloom Filter)过滤器
布隆过滤器(Bloom Filter)是1970年由布隆提出的。它实际上是一个很长的二进制向量和一系列随机映射函数。布隆过滤器可以用于检索一个元素是否在一个集合中。它的优点是空间效率和查询时间都比一般的算法要好的多,缺点是有一定的误识别率和删除困难。
4 0
HyperLogLog
HyperLogLog是用来做基数统计的算法,它提供不精确的去重计数方案(这个不精确并不是非常不精确),标准误差是0.81%,对于UV这种统计来说这样的误差范围是被允许的。HyperLogLog的优点在于,输入元素的数量或者体积非常大时,基数计算的存储空间是固定的。在Redis中,每个HyperLogLog键只需要花费12KB内存,就可以计算接近2^64个不同的基数。
4 0
如何实现Windows宿主系统和虚拟机ubuntu系统文件互相访问
如何实现Windows宿主系统和虚拟机ubuntu系统文件互相访问
4 0
通过90行代码学会HTML5 WebSQL的4种基本操作
通过90行代码学会HTML5 WebSQL的4种基本操作
6 0
1280
文章
0
问答
来源圈子
更多
文章排行榜
最热
最新
相关电子书
更多
文娱运维技术
立即下载
《SaaS模式云原生数据仓库应用场景实践》
立即下载
《看见新力量:二》电子书
立即下载