PolarDB-X 1.0-用户指南-SQL调优指南-SQL调优进阶-执行计划和基本算子

本文涉及的产品
云原生数据库 PolarDB 分布式版,标准版 2核8GB
简介: 本文介绍如何使用EXPLAIN命令查询执行计划,并介绍一些基本的算子(例如LogicalView, Gather,MergeSort等)。更多算子(例如Join、Agg、Sort等)的介绍在后续的章节中单独列出。

背景信息

通常SQL调优的过程离不开以下两个步骤:

  1. 分析问题,例如通过EXPLAIN命令查看执行计划,您也可以通过EXPLAIN ANALYZE查看实际执行情况来分析问题。(参见查询执行器介绍章节)。
  2. 通过Hint控制优化器行为,修改执行计划。

执行计划与EXPLAIN命令

下述案例介绍如何通过EXPLAIN命令获取查询的执行计划。

EXPLAIN语法如下:

说明Hint需放在EXPLAIN之后。

EXPLAIN <SQL Statement>
EXPLAIN <Hint> <SQL Statement>

本文中的示例均基于以下表结构:

CREATE TABLE `sbtest1` (
  `id`  INT(10) UNSIGNED NOT NULL,
  `k`   INT(10) UNSIGNED NOT NULL DEFAULT '0',
  `c`   CHAR(120)        NOT NULL DEFAULT '',
  `pad` CHAR(60)         NOT NULL DEFAULT '',
  KEY `xid` (`id`),
  KEY `k_1` (`k`)
) dbpartition BY HASH (`id`) tbpartition BY HASH (`id`) tbpartitions 4

执行以下EXPLAIN命令,PolarDB-X将返回对应的执行计划。

mysql> explain select a.k, count(*) cnt from sbtest1 a, sbtest1 b where a.id = b.k and a.id > 1000 group by k having cnt > 1300 order by cnt limit 5, 10;
+---------------------------------------------------------------------------------------------------------------------------------------------------+
| LOGICAL PLAN                                                                                                                                      |
+---------------------------------------------------------------------------------------------------------------------------------------------------+
| MemSort(sort="cnt ASC", offset=?2, fetch=?3)                                                                                                      |
|   Filter(condition="cnt > ?1")                                                                                                                    |
|     HashAgg(group="k", cnt="COUNT()")                                                                                                           |
|       BKAJoin(id="id", k="k", c="c", pad="pad", id0="id0", k0="k0", c0="c0", pad0="pad0", condition="id = k", type="inner")                       |
|         MergeSort(sort="k ASC")                                                                                                                   |
|           LogicalView(tables="[0000-0031].sbtest1_[000-127]", shardCount=128, sql="SELECT * FROM `sbtest1` WHERE (`id` > ?) ORDER BY `k`")        |
|         Gather(concurrent=true)                                                                                                                 |
|           LogicalView(tables="[0000-0031].sbtest1_[000-127]", shardCount=128, sql="SELECT * FROM `sbtest1` WHERE ((`k` > ?) AND (`k` IN ('?')))") |
| HitCache:false                                                                                                                                    |
+---------------------------------------------------------------------------------------------------------------------------------------------------+
9 rows in set (0.01 sec)

除执行计划外,EXPLAIN结果中还会有一些额外信息,上面的例子中仅有一项HitCache(是否命中Plan Cache缓存),详细原理参见执行计划管理

算子介绍

PolarDB-X中支持以下算子。

含义 物理算子
下发查询 LogicalViewLogicalModifyViewPhyTableOperation
连接(Join) BKAJoinNLJoinHashJoinSortMergeJoinHashSemiJoinSortMergeSemiJoinMaterializedSemiJoin
排序 MemSortTopN
聚合(Group By) HashAggSortAgg
数据交换 GatherMergeSort
其它 ProjectFilter, LimitUnionWindow

以下介绍部分算子的含义和实现,剩余的部分在后面的章节中介绍。

LogicalView

LogicalView是从存储层MySQL数据源拉取数据的算子,类似于其他数据库中的TableScan或IndexScan,但支持更多的下推。LogicalView中包含下推的SQL语句和数据源信息,更像一个视图。其中下推的SQL可能包含多种算子,如Project、Filter、聚合、排序、Join和子查询等。下述示例为您展示EXPLAINLogicalView的输出信息及其含义:

> explain select * From sbtest1 where id > 1000;
Gather(concurrent=true)
   LogicalView(tables="[0000-0031].sbtest1_[000-127]", shardCount=128, sql="SELECT * FROM `sbtest1` WHERE (`id` > ?)")

LogicalView的信息由三部分构成:

  • tables:存储层MySQL对应的表名,以英文句号(.)分割,英文句号(.)之前是分库对应的编号,之后是表名及其编号,如[000-127]表示表名编号从000到127的所有表。
  • shardCount:需访问的分表总数,该示例会访问从000到127共计128张分表。
  • sql:下发至存储层MySQL的SQL模版,PolarDB-X在执行时会将表名替换为物理表名,参数化的常量问号(?)替换成实际参数,详情请参见执行计划管理

Gather

Gather将多份数据合并成同份数据。上面的例子中,Gather将各个分表上查询到的数据合并成一份。

在不使用并行查询时,Gather通常出现在LogicalView上方,表示收集合并各个分表的数据。如果并行查询开启,Gather可能被上拉到执行计划中更高的地方,这时候Gather表示将各个Worker的计算结果收集合并。

Gather中的concurrent表示是否并发执行子算子,默认为true,表示并发拉取数据。开启并行查询时,上拉的Gather属性有所变化,显示为parallel=true

MergeSort

MergeSort即归并排序算子,表示将有序的数据流进行归并排序,合并成一个有序的数据流。例如:

> explain select * from sbtest1 where id > 1000 order by id limit 5,10;
MergeSort(sort="id ASC", offset=?1, fetch=?2)
  LogicalView(tables="[0000-0031].sbtest1_[000-127]", shardCount=128, sql="SELECT * FROM `sbtest1` WHERE (`id` > ?) ORDER BY `id` LIMIT (? + ?)")

MergeSort算子包含三部分内容:

  • sort:表示排序字段以及排列顺序,id ASC表示按照ID字段递增排序,DESC表示递减排序。
  • offset:表示获取结果集时的偏移量,例子中被参数化了,实际值为5。
  • fetch:表示最多返回的数据行数。与offset类似,同样是参数化的表示,实际对应的值为10。

Project

Project表示投影操作,即从输入数据中选择部分列输出,或者对某些列进行转换(通过函数或者表达式计算)后输出,当然也可以包含常量。

> explain select '你好, DRDS', 1 / 2, CURTIME();
Project(你好, DRDS="_UTF-16'你好, DRDS'", 1 / 2="1 / 2", CURTIME()="CURTIME()")

Project的计划中包括每列的列名及其对应的列、值、函数或者表达式。

Filter

Filter表示过滤操作,其中包含一些过滤条件。该算子对输入数据进行过滤,若满足条件,则输出,否则丢弃。如下是一个较复杂的例子,包含了以上介绍的大部分算子。

> explain select k, avg(id) avg_id from sbtest1 where id > 1000 group by k having avg_id > 1300;
Filter(condition="avg_id > ?1")
  Project(k="k", avg_id="sum_pushed_sum / sum_pushed_count")
    SortAgg(group="k", sum_pushed_sum="SUM(pushed_sum)", sum_pushed_count="SUM(pushed_count)")
      MergeSort(sort="k ASC")
        LogicalView(tables="[0000-0031].sbtest1_[000-127]", shardCount=128, sql="SELECT `k`, SUM(`id`) AS `pushed_sum`, COUNT(`id`) AS `pushed_count` FROM `sbtest1` WHERE (`id` > ?) GROUP BY `k` ORDER BY `k`")

WHERE id > 1000中的条件没有对应的Filter算子,是因为这个算子最终被下推到了LogicalView中,可以在LogicalView的SQL中看到WHERE (id > ?)

Union All与Union Distinct

顾名思义,Union All对应UNIONALL,Union Distinct对应UNIONDISTINCT。该算子通常有2个或更多个输入,表示将多个输入的数据合并在一起。例如:

> explain select * From sbtest1 where id > 1000 union distinct select * From sbtest1 where id < 200;
UnionDistinct(concurrent=true)
  Gather(concurrent=true)
    LogicalView(tables="[0000-0031].sbtest1_[000-127]", shardCount=128, sql="SELECT * FROM `sbtest1` WHERE (`id` > ?)")
  Gather(concurrent=true)
    LogicalView(tables="[0000-0031].sbtest1_[000-127]", shardCount=128, sql="SELECT * FROM `sbtest1` WHERE (`id` < ?)")

LogicalModifyView

如上文介绍,LogicalView表示从底层数据源获取数据的算子,与之对应的,LogicalModifyView表示对底层数据源的修改算子,其中也会记录一个SQL语句,该SQL可能是INSERTUPDATE或者DELETE

> explain update sbtest1 set c='Hello, DRDS' where id > 1000;
LogicalModifyView(tables="[0000-0031].sbtest1_[000-127]", shardCount=128, sql="UPDATE `sbtest1` SET `c` = ? WHERE (`id` > ?)"

> explain delete from sbtest1 where id > 1000;
LogicalModifyView(tables="[0000-0031].sbtest1_[000-127]", shardCount=128, sql="DELETE FROM `sbtest1` WHERE (`id` > ?)")

LogicalModifyView查询计划的内容与LogicalView类似,包括下发的物理分表,分表数以及SQL模版。同样,由于开启了执行计划缓存,对SQL做了参数化处理,SQL模版中的常量会用?替换。

PhyTableOperation

PhyTableOperation表示对某个物理分表直接执行一个操作。

说明 通常情况下,该算子仅用于INSERT语句。但当路由分发分到一个分片时,该算子也会出现在SELECT语句中。

> explain insert into sbtest1 values(1, 1, '1', '1'),(2, 2, '2', '2');
PhyTableOperation(tables="SYSBENCH_CORONADB_1526954857179TGMMSYSBENCH_CORONADB_VGOC_0000_RDS.[sbtest1_001]", sql="INSERT INTO ? (`id`, `k`, `c`, `pad`) VALUES(?, ?, ?, ?)", params="`sbtest1_001`,1,1,1,1")
PhyTableOperation(tables="SYSBENCH_CORONADB_1526954857179TGMMSYSBENCH_CORONADB_VGOC_0000_RDS.[sbtest1_002]", sql="INSERT INTO ? (`id`, `k`, `c`, `pad`) VALUES(?, ?, ?, ?)", params="`sbtest1_002`,2,2,2,2")

示例中,INSERT插入两行数据,每行数据对应一个PhyTableOperation算子。PhyTableOperation算子的内容包括三部分:

  • tables:物理表名,仅有唯一一个物理表名。
  • sql:SQL模版,该SQL模版中表名和常量均被参数化,用?替换,对应的参数在随后的params中给出。
  • params:SQL模版对应的参数,包括表名和常量。
相关实践学习
快速体验PolarDB开源数据库
本实验环境已内置PostgreSQL数据库以及PolarDB开源数据库:PolarDB PostgreSQL版和PolarDB分布式版,支持一键拉起使用,方便各位开发者学习使用。
相关文章
|
27天前
|
存储 SQL 关系型数据库
【MySQL调优】如何进行MySQL调优?从参数、数据建模、索引、SQL语句等方向,三万字详细解读MySQL的性能优化方案(2024版)
MySQL调优主要分为三个步骤:监控报警、排查慢SQL、MySQL调优。 排查慢SQL:开启慢查询日志 、找出最慢的几条SQL、分析查询计划 。 MySQL调优: 基础优化:缓存优化、硬件优化、参数优化、定期清理垃圾、使用合适的存储引擎、读写分离、分库分表; 表设计优化:数据类型优化、冷热数据分表等。 索引优化:考虑索引失效的11个场景、遵循索引设计原则、连接查询优化、排序优化、深分页查询优化、覆盖索引、索引下推、用普通索引等。 SQL优化。
214 15
【MySQL调优】如何进行MySQL调优?从参数、数据建模、索引、SQL语句等方向,三万字详细解读MySQL的性能优化方案(2024版)
|
29天前
|
SQL 分布式计算 数据库
SQL调优总结
数据库表的规范化和反规范化设计,设计合适的字段数据类型……
38 8
|
3月前
|
SQL 关系型数据库 分布式数据库
PolarDB产品使用问题之相同的SQL语句在不同时间执行EXPLAIN计划显示出不同的索引类型,是什么原因
PolarDB产品使用合集涵盖了从创建与管理、数据管理、性能优化与诊断、安全与合规到生态与集成、运维与支持等全方位的功能和服务,旨在帮助企业轻松构建高可用、高性能且易于管理的数据库环境,满足不同业务场景的需求。用户可以通过阿里云控制台、API、SDK等方式便捷地使用这些功能,实现数据库的高效运维与持续优化。
PolarDB产品使用问题之相同的SQL语句在不同时间执行EXPLAIN计划显示出不同的索引类型,是什么原因
|
2月前
|
Java XML Maven
跨越时代的飞跃:Struts 2 升级秘籍——从旧版本无缝迁移到最新版,焕发应用新生!
【8月更文挑战第31天】随着软件技术的发展,Struts 2 框架也在不断更新。本文通过具体案例指导开发者如何从旧版平滑升级到 Struts 2.6.x。首先更新 `pom.xml` 中的依赖版本,并执行 `mvn clean install`。接着检查 `struts.xml` 配置,确保符合新版本要求,调整包扫描器等设置。审查 Action 类及其注解,检查配置文件中的弃用项及插件。更新自定义拦截器实现,并验证日志配置。最后,通过一系列测试确保升级后的系统正常运行。通过这些步骤,可以顺利完成 Struts 2 的版本升级,提升应用的安全性和性能。
106 0
|
2月前
|
Java 开发者 前端开发
Struts 2、Spring MVC、Play Framework 上演巅峰之战,Web 开发的未来何去何从?
【8月更文挑战第31天】在Web应用开发中,Struts 2框架因强大功能和灵活配置备受青睐,但开发者常遇配置错误、类型转换失败、标签属性设置不当及异常处理等问题。本文通过实例解析常见难题与解决方案,如配置文件中遗漏`result`元素致页面跳转失败、日期格式不匹配需自定义转换器、`&lt;s:checkbox&gt;`标签缺少`label`属性致显示不全及Action中未捕获异常影响用户体验等,助您有效应对挑战。
73 0
|
2月前
|
SQL 监控 关系型数据库
SQL性能监控与调优工具的神奇之处:如何用最佳实践选择最适合你的那一个,让你的数据库飞起来?
【8月更文挑战第31天】在现代软件开发中,数据库性能监控与调优对应用稳定性至关重要。本文对比了数据库内置工具、第三方工具及云服务工具等几种常用SQL性能监控与调优工具,并通过示例代码展示了如何利用MySQL的EXPLAIN功能分析查询性能。选择最适合的工具需综合考虑功能需求、数据库类型及成本预算等因素。遵循了解工具功能、试用工具及定期维护工具等最佳实践,可帮助开发者更高效地管理和优化数据库性能,迎接未来软件开发中的挑战与机遇。
40 0
|
2月前
|
SQL 关系型数据库 MySQL
SQL性能调优的神奇之处:如何用优化技巧让你的数据库查询飞起来,实现秒级响应?
【8月更文挑战第31天】在现代软件开发中,数据库性能至关重要。本文通过一个实战案例,展示了从慢查询到秒级响应的全过程。通过对查询的详细分析与优化,包括创建索引、改进查询语句及数据类型选择等措施,最终显著提升了性能。文章还提供了示例代码及最佳实践建议,帮助读者掌握SQL性能调优的核心技巧。
65 0
|
3月前
|
SQL 关系型数据库 分布式数据库
PolarDB产品使用问题之遇到慢SQL问题,该如何解决
PolarDB产品使用合集涵盖了从创建与管理、数据管理、性能优化与诊断、安全与合规到生态与集成、运维与支持等全方位的功能和服务,旨在帮助企业轻松构建高可用、高性能且易于管理的数据库环境,满足不同业务场景的需求。用户可以通过阿里云控制台、API、SDK等方式便捷地使用这些功能,实现数据库的高效运维与持续优化。
|
3月前
|
SQL 关系型数据库 分布式数据库
PolarDB产品使用问题之sql运行报错是神么原因
PolarDB产品使用合集涵盖了从创建与管理、数据管理、性能优化与诊断、安全与合规到生态与集成、运维与支持等全方位的功能和服务,旨在帮助企业轻松构建高可用、高性能且易于管理的数据库环境,满足不同业务场景的需求。用户可以通过阿里云控制台、API、SDK等方式便捷地使用这些功能,实现数据库的高效运维与持续优化。
|
3月前
|
SQL 关系型数据库 Serverless
PolarDB产品使用问题之如何控制队列中排队的SQL的等待时间
PolarDB产品使用合集涵盖了从创建与管理、数据管理、性能优化与诊断、安全与合规到生态与集成、运维与支持等全方位的功能和服务,旨在帮助企业轻松构建高可用、高性能且易于管理的数据库环境,满足不同业务场景的需求。用户可以通过阿里云控制台、API、SDK等方式便捷地使用这些功能,实现数据库的高效运维与持续优化。

相关产品

  • 云原生分布式数据库 PolarDB-X