分类： News

发布于

如何为 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

发布于

如何使用计划指南调优 SQL Server 中的临时 SQL？

在 MS SQL Server 中使用计划指南来调优第三方应用程序的 SQL 可以是一种有用的技术，当您需要优化应用程序生成的特定查询或查询集的性能，而无需更改应用程序源代码时。
以下是在 MS SQL Server 中使用计划指南来调优第三方应用程序的 SQL 的步骤，而无需更改源代码：

  • 鉴定导致应用程序性能问题的查询或查询集。您可以使用 SQL Server Profiler 或扩展事件来捕获和分析应用程序生成的 SQL 语句。
  • 创建计划指南，为已鉴定的查询或查询集提供优化的执行计划。这可以涉及修改查询文本或提供查询提示以影响优化器的决策。
  • 测试计划指南，确保它提供了期望的性能改进，并且不会引起任何意外的副作用。
  • 将计划指南部署到生产环境，并监控应用程序的性能，确保计划指南被使用，并且提供了期望的性能改进。

在优化应用程序源代码中的临时 SQL 语句之前，了解 SQL 语句如何与计划指南中指定的语句匹配是至关重要的，包括空格和注释。此外，还需要匹配 SQL 语句的来源。

以下是用于创建计划指南的系统存储过程：

今天的重点将放在使用计划指南来调优临时 SQL (@type = N’SQL’) 上。SQL 有两种类型：独立的 SQL (@module_or_batch = NULL) 和批处理文本中的 SQL (@module_or_batch = N’batch_text’)。例如，如果一个应用程序发送了以下 SQL，并且它独立执行而没有其他代码，那么它属于独立的 SQL。
select top 10 * from employee;
下面的示例展示了一个批处理文本，其中包含了上述列出的 SQL 语句之一，需要通过计划指南进行优化。这个 SQL 语句位于批处理文本的中间。由于相同的 SQL 语句可能来自批处理文本，我们必须通过使用变量 @module_or_batch = N’batch_text’ 来指定具体的批处理文本。因此，必须为同一个 SQL 语句创建两个计划指南，一个用于临时 SQL，一个用于批处理文本。为了准确地确定临时 SQL 的来源，建议使用 SQL Profiler 来捕获需要通过计划指南进行优化的 SQL 语句。

select count(*) from employee;
select top 10 * from  employee;
where emp_id in (select emp_id id
                             from emp_subsidiary
                             where emp_dept<‘h’)
order by emp_name;

Microsoft SQL Server Management Studio提供了一个有用的工具，可以帮助用户创建计划指南，而无需手动执行系统存储过程。然而，了解被优化的SQL语句的类型以及需要输入的相应参数的含义是至关重要的。

尽管对于初学者来说，创建计划指南的步骤可能看起来很复杂，但它们对于在不修改源代码或没有修改权限的情况下改善SQL性能是值得的。然而，最具挑战性和耗时的方面是找到SQL语句的最佳查询提示（@hints = N’OPTION(query_hint [ ,…n ])）。除非您对SQL调优技术有深入的了解并且有足够的时间进行实验，否则您可能需要一个能够从捕获SQL、识别SQL来源类型、自动调优查询提示并便于部署计划指南的产品来简化这个过程。

Tosska DB Ace Enterprise for SQL Server – Tosska Technologies Limited
DBAS Tune SQL PG Standalone – YouTube
DBAS Tune SQL PG Batch – YouTube

发布于

软件部署中常被忽略的性能部署

性能部署是软件部署中一个常常被忽略的地方。尽管确保软件正确部署很重要，但在生产环境中优化性能同样重要。通过在软件部署过程中认识到性能部署的重要性，开发人员可以确保其软件运行良好，并满足用户的需求。关注性能部署可以帮助防止性能问题并提高用户对软件的满意度。

测试数据库与生产数据库之间性能部署的缺失环节
尽管进行了广泛的预部署测试，但在软件性能部署过程中仍有可能在特定的开发环境中遇到性能问题。以下问题可能会出现 ：

  1. 无法将生产数据复制到测试数据库中。
  2. 测试数据库与生产数据库之间的硬件和软件配置存在显著差异。
  3. 由于安全限制而无法在生产数据库中测试软件。
  4. 新软件中使用的DML SQL语句可能会损坏生产数据库的数据完整性。

所以用户在发布新应用程序代码后遇到性能问题或应用程序错误并不罕见。

通过预先部署流程确保性能部署
以下说明提供了一种保证软件性能可靠性的新方法。这个想法很简单：由于在生产数据库上运行新的应用程序代码是不可行的，为什么不为生产数据库中的每个SQL语句获取查询计划呢？这样，我们就可以评估每个应用程序代码中的SQL语句在生产数据库上的性能。

假设新应用程序代码中有10个SQL语句需要在测试数据库中识别。在这种情况下，我们需要先清除共享池并在测试数据库中执行新的应用程序，以隔离这10个语句。这个过程将使我们能够捕获和分析这10个SQL语句，并从生产数据库中获取它们的查询计划。下面的表格呈现了查询计划比较所导致的各种潜在结果。

观察结果 可能的原因
生产数据库中有Explain Plan错误 SQL语句需要访问生产数据库中不存在的对象
查询计划有更改 测试和生产数据库之间存在显著的统计差异，包括数据库模式的差异。这些模式差异可能涉及缺少或新的分区以及其他影响数据库结构和组织的变化。由于潜在的重大性能变化，可能需要对SQL进行基准测试。
有未使用的索引 一些在测试数据库中使用的索引在生产数据库中未使用, 由于潜在的重大性能变化，可能需要对SQL进行基准测试。
有新使用的索引 一些在生产数据库中使用的索引在测试数据库中未使用, 由于潜在的重大性能变化，可能需要对SQL进行基准测试。
总成本有变化 10个SQL语句的整体查询计划成本发生变化。如果生产数据库的数据量大于测试数据库，则成本变化将更高。

DBAO SQL Performance Tracker – YouTube
Tosska DB Ace Enterprise for Oracle – Tosska Technologies Limited

发布于

如何使用假设分析来判断数据库环境变化的性能影响 ？

要正确评估数据库环境变化期间一组SQL语句的性能影响，必须深入了解SQL查询性能可能受到的影响。SQL查询可能发生的两种主要性能变化类型。我称之为“渐进性变化”的第一种类型通常是由统计数据的变化引起的，例如相关表或索引页面中数据量的波动。如果统计数据的变化不足以触发新的查询计划，查询计划将保持不变，并且与原始统计数据相比，SQL查询的性能不会有很大的变化。

第二种性能变化类型称为“跳跃性变化”，是由于统计数据或模式发生重大变化而引入新的查询计划。这种类型的变化可能对性能产生重大影响，有时会导致性能灾难。

在更改数据库环境时，必须密切监视SQL查询的性能，并采取适当措施来优化受影响的语句。为了跟踪环境变化前后的关键SQL语句，可以遵循以下一般步骤：

  1. 从SGA或AWR中提取SQL语句以及其查询计划和性能统计信息。
  2. 对数据库应用环境变化，例如创建新索引、收集统计信息、升级数据库或预测在生产数据库中软件部署的性能。
  3. 从更改的数据库环境中获取查询计划。
  4. 比较每个SQL语句的查询计划，以确定是否存在任何差异。
  5. 查找潜在问题，如未使用的索引、高成本SQL更改等。
  6. 对修改后的SQL语句的查询计划进行基准测试，以检测任何性能下降。


如果您只需要跟踪少量SQL语句的性能在环境变化前后的变化，上述步骤可以手动完成。但是，如果您需要监视数百个SQL语句而没有工具，则可能会很具有挑战性。 Tosska DB Ace for Oracle配备了一个强大的工具，可以帮助您跟踪两个数据库之间SQL语句的性能差异。

DBAO SQL Performance Tracker – YouTube
Tosska DB Ace Enterprise for Oracle – Tosska Technologies Limited