作者： Ka Ming Ng

发布于

如何为 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® – 珠海图思科软件有限公司

发布于

Tosska SQL Tuning Expert Pro (TSEG Pro™) for openGauss® – 系统需求

在安装 Tosska SQL Tuning Expert Pro (TSEG Pro™) for openGauss® 之前, 请确保您的系统满足以下最低硬件和软件要求:

CPU 1.8 GHz 处理器
内存 最小2 GB内存，建议4 GB内存
硬盘空间 用于安装的200 MB硬盘空间
操作系统 Microsoft Windows 7
Microsoft Windows 10
Microsoft Windows 11
.NET Framework Microsoft .NET Framework 4.5.2
数据库服务器 openGauss 数据库 5.0或更高版本
发布于

如何使用查询重写插件在 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

发布于

Tosska DB Ace Enterprise (DBAM™) for MySQL® – 系统需求

在安装 Tosska DB Ace Enterprise (DBAM™) for MySQL® 之前, 请确保您的系统满足以下最低硬件和软件要求:

CPU 1.8 GHz 处理器
内存 最小2 GB内存，建议4 GB内存
硬盘空间 用于64位安装的400 MB硬盘空间
操作系统 Microsoft Windows 7 64位
Microsoft Windows 10 64位
.NET Framework Microsoft .NET Framework 4.5.2
数据库服务器 MySQL 数据库 5.6或更高版本