分类： News

发布于

在 PostgreSQL 中优化含 ‘IN’ 子查询的 SQL 语句有哪些方法 ？

以下是我近期博客中的一条 SQL 语句。有读者询问在子查询的选择列表添加 +0（例如 b.emp_grade+0）对性能的影响。我很欣喜地得知，这个 +0 解决方案大约在 20 年前首次由我提出，用于解决特定子查询问题。不过该方法现已被 COALESCE(b.emp_grade, b.emp_grade) 替代——此举既能规避数据类型检测，又可预防意外错误。

今天，我想进一步探讨利用 IN 子查询优化 SQL 的其他解决方案。

select *
  from grade
 where grd_id in (select b.emp_grade
                    from employee b
                   where b.emp_id > 3000000)

以下截图展示了由 Tosska SQL Tuning Expert Pro 通过’仅重写’选项和 ‘SQL调优智能=2’ 生成的替代SQL方案。我将分享更多SQL重写方法，全面展示各种可能方案，期待以SQL调优技术的精妙艺术激发读者的探索热情。

以下虽不深入探讨具体SQL重写语句细节，但我很乐意分享其余查询重写方案及其对应的执行计划：

Tosska SQL Tuning Expert Pro for PostgreSQL – Tosska Technologies Limited

发布于

如何对 PostgreSQL 中的“IN 子查询”进行 SQL 调优 ?

以下是一个使用 IN 子查询的 SQL 语句示例。该查询从 grade 表中检索所有 grd_id 匹配 employee 表中 emp_id 大于 3,000,000 的记录的 emp_grade 的数据：

select *
  from grade
 where grd_id in (select b.emp_grade
                    from employee b
                   where b.emp_id > 3000000)

查询的执行计划显示，该语句耗时 12.6 秒。

该查询计划显示，优化器采用了 嵌套循环（Nested Loop） 连接方式：先对 grade 表进行全表顺序扫描（Sequential Scan），再对 employee 表进行索引扫描（Index Scan）。然而，这种执行逻辑会导致 每个 grade 表的记录都会触发一次针对 employee 表中 emp_id > 3,000,000 条件的索引扫描。虽然 employee 表总记录约 400 万条，但满足 emp_id > 3,000,000 的记录不足 100 万条。

为解决此问题，我通过在子查询中增加 GROUP BY 子句重写了 SQL：

select *
  from grade
 where grd_id in (select b.emp_grade
                    from employee b
                   where b.emp_id > 3000000
                   group by b.emp_grade)

修改后的查询计划发生以下关键变化：

  • GROUP BY 子句强制子查询优先执行.
  • 通过索引扫描快速筛选出 employee 表中 emp_id > 3,000,000 的记录.
  • 对 emp_grade 列进行分组操作，消除重复值。
  • 将 grade 表与已分组的 emp_grade 子集通过哈希表高效关联。

优化后的 SQL 执行时间降至 0.54 秒，性能显著提升。

优化后的 SQL 语句比原始版本 性能提升超过 23 倍。此类优化也可以通过 Tosska SQL Tuning Expert Pro for PostgreSQL 工具实现。在下方的截图中，虽然展示了其他性能更优的 SQL 改写方案，但受限于篇幅无法在此详述——我们后续可以进一步探讨这一主题。

Tosska SQL Tuning Expert Pro for PostgreSQL – Tosska Technologies Limited

发布于

如何对PostgreSQL中使用CASE表达式的SQL语句进行调优 ？

以下是一个包含CASE表达式语法的简单SQL语句：

SELECT *
FROM   employee
WHERE 
       CASE
         WHEN emp_salary< 1000
         THEN ‘low’
         WHEN emp_salary>100000
         THEN ‘high’
       END = :a

以下是该SQL语句的查询计划（当绑定变量:a等于’low’时，执行时间为1.01秒）。由于CASE表达式无法利用emp_salary索引，查询需要对EMPLOYEE表进行全表扫描。

我们可以通过使用多个OR条件将CASE表达式重构为以下语法：

select *
  from employee
 where (emp_salary < 1000 and
        ‘low’ = :a or
        ((not (emp_salary < 1000)) or
         emp_salary is null) and
        emp_salary > 100000 and
        ‘high’ = :a)

通过将多个OR条件与AND操作（例如’low’ = :a）结合，可以有效禁用对EMPLOYEE表的不必要数据搜索。

以下是重构后SQL的查询计划（执行速度提升至0.005秒），性能比原始语法快约200倍。新的查询计划显示使用了emp_salary索引的位图索引扫描（Bitmap Index Scan）。

这类SQL重写可以通过Tosska SQL Tuning Expert for PostgreSQL自动完成。实际上，还存在其他性能更优的改写方法，但由于篇幅限制无法在此详述，后续可能会在我的博客中进一步探讨。

Tosska SQL Tuning Expert Pro for PostgreSQL – Tosska Technologies Limited

发布于

如何为 openGauss 调优带有 CASE 表达式的 SQL 语句 ?

openGauss 是一个开源的关系型数据库管理系统（RDBMS），起源于 PostgreSQL。它专为高性能、高可用性和企业级功能而设计。openGauss 最初由华为开发，后来开源给社区。

以下是开发人员可能会遇到的一个常见问题：编写动态 SQL 语句时，在 CASE 表达式中硬编码了 a = ‘low’，而不是使用绑定变量 = :var，如下所示：

SELECT *
FROM   employee
WHERE 
       CASE
         WHEN emp_salary< 1000
         THEN ‘low’
         WHEN emp_salary>100000
         THEN ‘high’
         ELSE ‘Normal’
       END = ‘low’

以下是该 SQL 语句的查询计划，其执行时间为 2.20 秒。由于 CASE 表达式无法利用 emp_salary 索引，查询对 EMPLOYEE 表执行了顺序扫描（Seq Scan）。

我们可以使用以下带有多个 OR 条件的语法来重写 CASE 表达式。

select *
  from employee
 where (emp_salary < 1000 and
        ‘low’ = ‘low’ or
        (not (emp_salary < 1000)) and
        emp_salary > 100000 and
        ‘high’ = ‘low’ or
        (not (emp_salary < 1000 or
              emp_salary > 100000)) and
        ‘Normal’ = ‘low’);

如果 emp_salary 字段可为空（nullable），SQL 查询应按照以下方式编写：

select *
  from employee
 where (emp_salary < 1000 and
        ‘low’ = ‘low’ or
        ((not (emp_salary < 1000)) or
         emp_salary is null) and
        emp_salary > 100000 and
        ‘high’ = ‘low’ or
        ((not (emp_salary < 1000 or
               emp_salary > 100000)) or
         emp_salary is null) and
        ‘Normal’ = ‘low’)

以下是重写后的 SQL 查询计划，其执行时间为 0.002 秒，比原始语法快了 1100 倍。新的查询计划使用了针对 emp_salary 索引的位图索引扫描（Bitmap Index Scan）。
这种重写可以通过 Tosska SQL Tuning Expert Pro 工具为 openGauss 自动实现。还有一些其他重写方法可以提供更好的性能，但由于篇幅限制，本文不适合详细讨论。我可能会在未来的博客文章中进一步探讨这些方法。

Tosska SQL Tuning Expert Pro (TSEG Pro™) for OpenGauss® – Tosska Technologies Limited

发布于

如何为 openGauss 调优带有“EXISTS 子查询”的 SQL ？

openGauss 是一种开源的关系型数据库管理系统（RDBMS），它起源于 PostgreSQL。openGauss 旨在提供高性能、高可用性和企业级功能。最初由华为开发，后来被开源给社区。

在大多数数据库的 SQL 优化器中，一个常见问题是对 EXISTS 子查询 的低效处理。以下是一个带有 EXISTS 子查询的 SQL 语句示例。该查询从 emp_subsidiary 表中检索与 employee 表中的 salary、grade_id 和 department_id 匹配的记录。

select *
  from emp_subsidiary a
 where exists (select ‘x’
                 from employee b
                where a.emp_salary = b.emp_salary and
                      b.emp_grade = a.emp_grade and
                      a.emp_dept = b.emp_dept)

以下是查询计划；完成该查询需要 10.35 秒。

查询计划显示了一个 Hash Inner Join，其中包含对 emp_subsidiary 表的顺序扫描（sequential scan）和对 employee 表的顺序扫描。该查询计划的性能看起来是合理的，如果不引入新的索引，性能将无法进一步提升。

有人可能会建议将 SQL 重写为使用 IN 语句，以查看查询是否可以改进，如下所示：

select *
  from emp_subsidiary a
 where (a.emp_salary, a.emp_grade, a.emp_dept) in (select b.emp_salary,
                                                          b.emp_grade,
                                                          b.emp_dept
                                                     from employee b)

重写后的 SQL 并未生成新的查询计划，且未观察到性能提升。为了解决这个问题，让我进一步将原始 SQL 语句重写为带有 INTERSECT 语句的子查询。

显然，重写后的 SQL 在子查询中引入了一个额外的操作，即 employee 表与 emp_subsidiary 表进行交集运算。这意味着只有 employee 表和 emp_subsidiary 表之间基于 emp_salary、emp_grade 和 emp_dept 的交集记录会被返回。因此，子查询的结果集大幅减少，从而显著降低了高成本的 Hash Aggregate 操作。

select *
  from emp_subsidiary a
 where (a.emp_salary, a.emp_grade, a.emp_dept) in (select b.emp_salary,
                                                          b.emp_grade,
                                                          b.emp_dept
                                                     from employee b
                                                   intersect
                                                   select a.emp_salary,
                                                          a.emp_grade,
                                                          a.emp_dept
                                                     from emp_subsidiary a)

重写后的 SQL 需要 4 秒 完成，其查询计划如下。与原始查询计划相比，新计划略显复杂。然而，Hashed Aggregate 操作的成本显著低于原始 SQL，实际执行时间也反映了这一改进。

这种语法重写方法只有在 INTERSECT 操作引入的开销被其显著减少的子查询结果集所抵消时才有用。

重写后的 SQL 比原始 SQL 快 2 倍以上。这种优化也可以通过使用 Tosska SQL Tuning Expert 在 openGauss 中实现。

Tosska SQL Tuning Expert Pro (TSEG Pro™) for openGauss® – 珠海图思科软件有限公司

发布于

如何为 openGauss 调优带有“IN 子查询”的 SQL ？

openGauss 是一种开源的关系型数据库管理系统（RDBMS），它起源于 PostgreSQL。openGauss 旨在提供高性能、高可用性和企业级功能。最初由华为开发，后来被开源给社区。openGauss 的 SQL 优化器基于 PostgreSQL，但经过了显著的增强和修改，以提升性能、可扩展性并支持企业级工作负载。虽然官方文档中没有明确说明 openGauss 是从哪个 PostgreSQL 版本继承的初始代码库，但普遍认为 openGauss 起源于 PostgreSQL 9.2.4。因此，当前版本的 openGauss 中可能仍然存在一些来自旧版 PostgreSQL 的遗留 SQL 优化器问题。

在不成熟的 SQL 优化器中，一个常见问题是对 IN 子查询的低效处理。以下是一个带有 IN 子查询的 SQL 语句示例。该查询从 employee 表中检索与 emp_subsidiary 表中 salary 匹配的记录，条件是两者的 emp_id 相同。

select *
  from employee a
 where a.emp_salary in (select b.emp_salary
                          from emp_subsidiary b
                         where a.emp_id = b.emp_id)

以下是查询计划；完成该查询需要 7.2 秒。

查询计划显示了对 employee 表的顺序扫描（sequence scan）和对 emp_subsidiary 表的索引扫描（index scan）。然而，这种查询不适合 employee 与 emp_subsidiary 比例为 30:1 的场景。如果 openGauss 拥有更强大的 SQL 优化器，它应该包含一个内部的 SQL 语法重写机制，将 IN 语句转换为 JOIN 或 EXISTS 语句，从而允许探索更高效的查询计划。>br>

下面，我将使用 EXISTS 语句重写 SQL，如下所示：

select *
  from employee a
 where exists (select ‘x’
                 from emp_subsidiary b
                where a.emp_salary = b.emp_salary and
                      a.emp_id = b.emp_id)


重写后的 SQL 仅需 0.34 秒 即可完成，并且在查询计划中使用了 Merge Semi Join——这是一种无法通过原始 IN 语法生成的计划。我们可以看到，重写后的 SQL 成本显著低于原始 SQL 语句。这表明，在 openGauss 对原始查询进行 SQL 优化时，Merge Semi Join 计划并未在其探索的计划空间内。

重写后的 SQL 比原始 SQL 快 20 倍以上。这种优化也可以通过使用 Tosska SQL Tuning Expert 在 openGauss 中实现。

Tosska SQL Tuning Expert Pro (TSEG Pro™) for openGauss® – 珠海图思科软件有限公司

发布于

如何使用查询重写插件在 MySQL 数据库中调整 SQL II？

MySQL 中的查询重写插件是一个组件，允许您在执行之前修改传入的 SQL 查询。它提供了根据特定需求转换、路由、过滤或扩展查询的功能。

如果您已安装了该插件，可以使用以下 SQL 语句来定义您的 SQL 替换规则和错误消息处理.

INSERT INTO query_rewrite.rewrite_rules (message, pattern, replacement)
VALUES(Unique_ID, Original_SQL, Rewrite_SQL);

MySQL 中的 query_rewrite.rewrite_rules 表存储了 Query Rewriter 插件用于重写 SQL 查询的规则。该表包含两列：

Pattern – 此列代表触发 SQL 查询重写的模式或条件。它定义了要匹配的特定查询或查询模式。

Replacement – 此列指定应该应用于匹配的查询或查询模式的替换或转换。

当执行 SQL 查询时，Query Rewriter 插件会检查 query_rewrite.rewrite_rules 表以查找匹配的模式。如果模式与执行的查询匹配，插件将使用相应的替换重写查询。这使您能够根据特定的模式或条件修改查询结构、优化查询或添加自定义逻辑。
以下是一个包含硬编码字面量的 SQL 语句示例，执行该 SQL 需要 2.1 秒，并附有以下查询计划。

SELECT   *
FROM     employee
WHERE    emp_id in (SELECT emp_id id
                    FROM   emp_subsidiary
                    where  emp_dept < ‘D’)
ORDER BY emp_id LIMIT 1;

以下屏幕显示了通过 Tosska DB Ace SQL 调优工具识别出的性能显著提升的替代 SQL 语句。由于注入 JOIN ORDER 提示，此优化后的 SQL 查询比原始 SQL 快了300多倍.

现在，我们需要将这个优化后的 SQL 与原始 SQL 一起部署到数据库中。然而，一个挑战出现在条件“emp_dept < 'D'”上，当使用一个带有不同硬编码字面量的 SQL 语句，比如“emp_dept < 'E'”时，它与 SQL 文本不匹配。因此，我们必须使用数值占位符Placeholder来替换硬编码字面量，将其替换为占位符“？”，如下所示。

INSERT INTO query_rewrite.rewrite_rules (message, pattern, replacement)
VALUES(Unique_ID, Original_SQL, Rewrite_SQL);

Original_SQL

SELECT   *
FROM     employee
WHERE    emp_id in (SELECT emp_id id
                    FROM   emp_subsidiary
                    where  emp_dept < ?)
ORDER BY emp_id LIMIT 1

Rewrite_SQL

select   /*+ QB_NAME(QB1) JOIN_ORDER(`employee`@QB1, `emp_subsidiary`@QB2) */ *
from     employee
where    emp_id in (select /*+ QB_NAME(QB2) */ emp_id id
                    from   emp_subsidiary
                    where  emp_dept < ?)
order by emp_id limit 1

Tosska DB Ace for MySQL通过自动化发现优化SQL替代方案和部署具有数值占位符替换的SQL，简化了这一过程。

查询重写插件功能强大且易于使用。最具挑战性的方面是为性能较差的SQL语句找到替代SQL。Tosska DB Ace Enterprise for MySQL可以帮助您自动化这一过程，从识别性能较差的SQL语句到重写SQL语法并部署替代规则。

Tosska DB Ace Enterprise for MySQL – Tosska Technologies Limited

DBAM Tune Rewriter demo – YouTube

发布于

如何使用MySQL数据库中的Query Rewriter插件来调优SQL？

MySQL中的Query Rewriter插件是一个组件，允许您在执行SQL之前修改传入的SQL查询。它提供了根据特定需求转换、路由、过滤或扩展查询的能力。该插件在SQL层操作，并可用于优化查询性能、强制执行安全策略、实施数据分区策略或向查询添加附加业务逻辑。通过Query Rewriter插件，您可以自定义和塑造SQL查询，以满足特定需求，在MySQL服务器内灵活控制查询执行。

Query Rewriter查询转换功能使您能够将原始查询重写或转换为等效或更高效的形式。这对于优化性能、简化复杂查询或强制使用特定查询计划非常有用。

在使用此功能之前，您必须安装Query Rewriter插件。Query Rewriter的概念很简单，它是一组预定义的SQL语句，用于替换从应用程序程序触发的特定SQL语句模式。

如果您已安装了该插件，以下SQL语句可用于定义您的SQL替换规则和错误消息处理。

INSERT INTO query_rewrite.rewrite_rules (message, pattern, replacement)
VALUES(Unique_ID, Original_SQL, Rewrite_SQL);

在MySQL中，query_rewrite.rewrite_rules表存储了Query Rewriter插件用于重写SQL查询的规则。该表具有两列：
Pattern – 此列表示触发SQL查询重写的模式或条件。它定义了要匹配的特定查询或查询模式。

Replacement – 此列指定应应用于匹配的查询或查询模式的替换或转换。

当执行SQL查询时，Query Rewriter插件会检查query_rewrite.rewrite_rules表以查找匹配的模式。如果某个模式与执行的查询匹配，插件将使用相应的替换来重写查询。这使您能够根据特定的模式或条件修改查询结构、优化查询或添加自定义逻辑。
我利用message列来定义SQL替换规则的临时唯一标识，这样可以使用以下SQL提取实际的规则ID。

SELECT id into :SID FROM query_rewrite.rewrite_rules where message= Unique_ID;

当您对query_rewrite.rewrite_rules表中的查询重写规则进行更改时，这些更改不会立即生效。相反，MySQL会将规则缓存在内存中以提高性能。然而，如果您希望确保更新后的规则立即生效，可以调用query_rewrite.flush_rewrite_rules()函数。

CALL query_rewrite.flush_rewrite_rules();

如果发生加载错误，插件还会将Rewriter_reload_error状态变量设置为ON，并将错误消息存储在Message列中。

SELECT message FROM query_rewrite.rewrite_rules where id=:SID;

实际上，Query Rewriter插件功能强大且易于使用。最具挑战性的方面是为性能不佳的SQL语句找到替代的SQL语句。Tosska DB Ace Enterprise for MySQL可以帮助您自动化这个过程，从识别性能不佳的SQL语句到重写SQL语法和部署替代规则。

Tosska DB Ace Enterprise for MySQL – Tosska Technologies Limited

DBAM Tune Rewriter demo – YouTube

发布于

如何使用计划指南（Plan Guide）为SQL Server中的数据库对象进行SQL优化 ？

利用SQL Server中的计划指南（Plan Guides）可以优化存储过程和触发器等数据库对象中特定查询的性能，从而提高查询效率，而无需修改应用程序的源代码。

以下是在不更改源代码的情况下，使用计划指南来优化SQL Server中第三方应用程序的SQL的步骤：

  1. 鉴定导致数据库对象性能问题的SQL语句。
  2. 创建一个计划指南，通过引入查询提示（query hints）来为指定的查询提供优化的执行计划，以影响优化器的决策过程。
  3. 测试计划指南，确保其提供了期望的性能改进，并且不会引起任何意外的副作用。
  4. 将计划指南部署到生产环境，并监控应用程序的性能，以确保计划指南正在使用，并提供了期望的性能改进。

在优化执行中不修改源代码的应用程序中的数据库对象的SQL语句之前，关键是要了解SQL语句与计划指南中指定的语句的匹配情况，包括空格和注释。此外，还要确保匹配执行SQL语句的数据库对象。
以下是一个示例，演示如何优化名为getRD2的数据库对象中的SQL语句。用户选择并突出显示了SQL语句。

创建计划指南可能一开始看起来很复杂，但它是一种宝贵的方法，可以提高SQL性能，而无需修改源代码或缺乏必要的权限。最耗时的部分是使用sp_create_plan_guide系统存储过程中的@hints = N’OPTION(query_hint [ ,…n ])’参数来找到SQL语句的最佳查询提示。如果您对SQL调优技术了解不深或没有足够的时间进行试验，可以使用一种简化该过程的解决方案。它可以捕获SQL语句，识别SQL源类型，自动优化查询提示，并便于计划指南的轻松部署。
以下产品自动识别了一个计划指南，如附带的截图所示，可以将SQL性能提升75.81%。

在确定了最佳的计划指南之后，我们可以将其与存储过程一起部署到SQL Server数据库中。这个部署将会改善名为getRD2的存储过程的性能，而无需对存储过程的源代码进行任何修改。

一旦您获得了适合SQL语句的合适提示解决方案，您还可以选择使用系统存储过程sp_create_plan_guide手动创建计划指南。

如需详细信息，请访问我们的网站并观看我们的演示视频。
Tosska DB Ace Enterprise for SQL Server – Tosska Technologies Limited
DBAS Tune SQL PG Object – YouTube