SQL – 珠海图思科软件有限公司

25/06/2025

如何优化 PostgreSQL 中带 “NOT IN 子查询” 的 SQL 语句？

以下是一个使用 NOT IN 子查询的 SQL 语句示例。该查询从 Grade 表中检索记录，条件是当 emp_id 大于 3,000 时，grd_id 不与 employee 表中的任何 grd_id 匹配：

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

当前执行计划耗时异常漫长，需要 50 分 10 秒才能完成。

该查询计划显示，系统先在Employee表上对Emp_id > 3000进行顺序扫描，生成物化临时存储，随后用Grade表的Grd_id进行过滤。这种查询计划效率极低——因为当Grade表的5000行记录需要逐行处理时，需反复扫描Employee表约300万条记录。

为解决此问题，我将SQL语句重写为NOT (EXISTS …)形式：

select *
from grade g1
where not (exists (select ‘x’
                      from employee b
                     where g1.grd_id = b.emp_grade and
                           b.emp_id > 3000))

优化后的查询计划显示：现在通过嵌套循环连接（Nest Loop），以Grade表为驱动，配合Employee表上的索引扫描（emps_grade_inx）进行操作。

重写后的SQL语句执行时间降至0.07秒，性能得到显著提升。

重写后的SQL比原始版本提速超过43,000倍。这类优化同样可通过Tosska SQL Tuning Expert Pro for PostgreSQL工具实现。下图展示了几种性能更优的SQL替代方案，但因篇幅所限不便在本文详述，也许我们可以稍后再讨论这个话题。

Tosska SQL Tuning Expert Pro for PostgreSQL – Tosska Technologies Limited

05/06/2025

在 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

27/05/202527/05/2025

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

06/03/2025

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

22/01/2025

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

14/01/2025

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

04/09/2024

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

22/12/2023

如何使用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

发布于09/08/2023
如何使用计划指南调优 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

发布于04/07/2023
如何使用80/20法则来调优数据库应用程序 II ？

之前的文章“如何使用80/20法则来调优数据库应用程序 I”演示了如何应用80/20法则来评估数据库中SQL工作负载的整体性能。在本例中，展示了从Oracle SGA检索到的一组90个SQL语句的图表，每个语句按照其资源使用情况以降序排列列出，最具资源密集型的SQL在左侧。分析显示，大约14.44％的SQL语句占用了80％的总经过时间，而21.11％的SQL语句占用了80％的总CPU时间，表明SQL工作负载分布符合80/20法则。因此，调整SQL可能并不必要，因为这不太可能带来显著的性能改进。

然而，为了更加成本有效地进一步优化数据库性能，建议对高工作量SQL语句的前20％进行深入调查。这将揭示资源利用在前几个SQL语句中急剧下降，使它们成为优化的最关键候选项。

让我们将目标的总资源消耗比例从80％降低到60％，并检查负责利用这些资源的SQL语句。结果很有趣，显示出3个SQL语句占用了60％的经过时间，6个SQL语句占用了60％的CPU时间，而仅有一个SQL语句占用了60％的磁盘读取。通过专注于这些SQL语句，可以提高数据库工作负载高达60％。例如，如果数据库遇到IO瓶颈，专注于一个SQL语句可以节省高达60％的磁盘读取。

您可以利用Excel来进行上述80/20法则分析的模拟，提供SQL工作负载分布的全面概述。这种方法有助于快速评估数据库SQL性能的整体状况，以及优化高负载SQL语句的成本和效益。更进一步的SQL资源频谱分析已集成到我们的Tosska DB Ace for Oracle软件中。

Tosska DB Ace Enterprise for Oracle – Tosska Technologies Limited

DBAO Inspect SQL – YouTube

文章分页

页 1 页 2 下一页