开发指南—DAL语句—EXPLAIN

本文涉及的产品
云数据库 RDS MySQL,集群系列 2核4GB
推荐场景:
搭建个人博客
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
云数据库 RDS MySQL,高可用系列 2核4GB
简介: 该语句用于解释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)
相关实践学习
如何在云端创建MySQL数据库
开始实验后,系统会自动创建一台自建MySQL的 源数据库 ECS 实例和一台 目标数据库 RDS。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心,利用清洁计算为万物互联的新世界提供源源不断的能源动力,目前开服的区域包括中国(华北、华东、华南、香港)、新加坡、美国(美东、美西)、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程,来了解阿里云能够为你的业务带来哪些帮助     相关的阿里云产品:云服务器ECS 云服务器 ECS(Elastic Compute Service)是一种弹性可伸缩的计算服务,助您降低 IT 成本,提升运维效率,使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
相关文章
|
6月前
|
JavaScript 前端开发
js中模糊搜索 模糊匹配如何实现?
js中模糊搜索 模糊匹配如何实现?
Echarts仪盘表axisLine渐变及title颜色设置
Echarts仪盘表axisLine渐变及title颜色设置
237 0
|
4月前
|
SQL 关系型数据库 分布式数据库
PolarDB产品使用问题之遇到慢SQL问题,该如何解决
PolarDB产品使用合集涵盖了从创建与管理、数据管理、性能优化与诊断、安全与合规到生态与集成、运维与支持等全方位的功能和服务,旨在帮助企业轻松构建高可用、高性能且易于管理的数据库环境,满足不同业务场景的需求。用户可以通过阿里云控制台、API、SDK等方式便捷地使用这些功能,实现数据库的高效运维与持续优化。
|
3月前
|
数据采集 机器学习/深度学习 数据可视化
【优秀python web系统毕设】基于python的全国招聘数据分析可视化系统,包括随机森林算法
本文介绍了一个基于Python的全国招聘数据分析可视化系统,该系统利用数据挖掘技术、随机森林算法和数据可视化技术,从招聘网站抓取数据,进行处理、分析和预测,帮助用户洞察招聘市场,为求职者和企业提供决策支持。
101 2
|
存储 搜索推荐 算法
分享一个在线二维码生成器(基于qrcode.js开发)
一种二维码扫描与生成的工具, 它可生成个性化二维码, 支持文本、网址、图片、短信、电话等格式及主题,提供融合码功能
208 1
|
SQL 自然语言处理 关系型数据库
开发指南—DQL语句—SELECT
SELECT用于从一个或多个表中查询数据。
|
SQL 索引
开发指南—DAL语句—SETCHECK TABLE
本文介绍了CHECK TABLE语句的用法。
|
关系型数据库 MySQL 数据库
开发指南—DAL语句—USE
本文将介绍如何通过USE指令在PolarDB-X中切换当前连接的默认数据库。
106 0
|
SQL 关系型数据库 MySQL
开发指南—DAL语句—EXPLAIN
该语句用于解释SQL语句的执行计划,包括SELECT、DELETE、INSERT、REPLACE或UPDATE语句。
|
JSON 前端开发 JavaScript
JavaScript学习 -- fetch的使用
JavaScript学习 -- fetch的使用
119 0