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

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

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

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

  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

使用Query Story优化 SQL的示例

SQL Server 中的查询存储Query Store是一个有价值的工具,用于通过快速识别由查询计划更改引起的性能降级来解决性能问题。以下示例,当在 SSMS 中执行以下 SQL 语句时,需要 15,579 毫秒才能完成。

使用查询存储器中的“消耗最多资源的查询”功能,我们可以看到具Query ID 23713 和相应的Plan ID 37290 的 SQL 在“计划摘要”窗口中显示。


要从 SQL Server 获取 SQL 文本,可以使用Query ID 手动提取并访问相关的系统表,即 sys.query_store_query 和 sys.query_store_query_text。或者,如果您有一个可以帮助提取 SQL 文本的工具,它可能会显示在下面的屏幕上。该工具接受查询 ID 或部分 SQL 文本来定位查询存储中的特定 SQL 语句进行 SQL 调整。

下面的屏幕显示了该产品如何通过向查询注入一系列提示Hints组合并创建相应的计划指南来提高 SQL 性能。手动完成此过程可能很困难,因为有许多可能的提示组合需要评估。如果没有对 SQL 调整和查询计划的基本理解,识别最佳提示组合可能需要进行大量的试错。
该工具是一款完全自动化的 SQL 调整解决方案,利用查询存储器。在处理过程中,该工具向 SQL 查询中注入了 100 种不同的提示Hints,并识别出了 75 种唯一的查询计划。经过基准测试,发现查询存储器Query store 66 (QS 66) 的性能最佳,处理时间节省了 98.45%。优化后的查询包括以下提示:
OPTION(HASH JOIN, TABLE HINT(employee, INDEX(EMPS_GRADE_INX)))

一旦确定了 SQL 语句的最佳提示,我们可以强制Force Plan将其计划用于 SQL 查询,如下面的屏幕所示。这样一来,下次用户程序执行该 SQL 时,其性能将得到改善,无需对其源代码进行任何修改。

下面的屏幕显示执行相同 SQL 语句在 SSMS 中可以显著提高性能的证据。CPU 时间从 54202 毫秒减少到 391 毫秒,性能提高了 138 倍,而经过的时间从 15579 毫秒减少到 294 毫秒,性能提高了 52 倍。

一种专为查询存储器(Query Store)优化SQL语句而设计的新产品
Tosska DB Ace for SQL Server在这个领域标志着一个重要的飞跃,因为它超越了查询存储器的被动恢复能力,并引入了主动的SQL性能增强。这项开创性技术允许用户从查询存储器(Query Store)中提取SQL并通过在查询存储器中创建新的和改进的查询计划来进行优化。使用Tosska DB Ace,用户可以将这些新计划应用于他们的SQL,而无需修改程序源代码或进行广泛的测试。

Tosska DB Ace Enterprise for SQL Server – Tosska Technologies Limited
DBAS Tune SQL QS – YouTube

如何使用查询存储器(Query Store)对SQL进行调优?

SQL Server中的查询存储器功能是一个宝贵的工具,用于通过允许用户快速识别由于查询计划更改而导致的性能降级来解决性能问题。
在给定的示例中,我们可以观察到一个SQL查询(ID 23058)具有两个查询计划。黄点对应于表现相对稳定的查询计划,而蓝点则表示性能计划更加波动。为了增强此SQL性能的稳定性,我们可以使用Query Store中的“强制计划”功能,将黄点的查询计划指定为默认计划。

查询存储器(Query Store)是由SQL Server提供的一个强大功能,使用户能够在Query Store中强制指定一个特定的SQL查询计划。然而,查询存储器也有其限制,因为它不允许用户创建之前未生成的新查询计划。其使用是被动的,仅允许恢复受损的SQL性能,而不提供一种方法让用户改进SQL语句,以便生成更好的查询计划。

如何使用查询存储器(Query Store)来手动调整SQL?
如果想要手动提高存储在查询存储器中的SQL查询性能,这个过程可能会相当复杂。以下是一些一般性的步骤,作为指南:

  1. 从系统表sys.query_store_query和sys.query_store_query_text中提取您要调整的SQL文本。
  2. 通过注入各种提示(Hints)并确定最佳提示应用程序来调整SQL以提高查询性能。
  3. 为SQL文本创建一个计划指南(Plan Guide),保留原始SQL文本格式并纳入步骤2中确定的提示。
  4. 使用新创建的计划指南(Plan Guide)执行SQL,以在查询存储器中生成新的查询计划。
  5. 使用SQL Server Management Studio强制使用SQL的新查询计划。
  6. 最后,丢弃这计划指南(Plan Guide)。

通过遵循这些步骤,用户可以手动调整查询存储器中的SQL查询,并实现改进的性能。然而,需要注意的是,这个过程可能会很复杂和耗时,应该只由有经验的数据库管理员进行,他们对SQL性能优化有深入的了解。

一种专为查询存储器(Query Store)优化SQL语句而设计的新产品
Tosska DB Ace for SQL Server在这个领域标志着一个重要的飞跃,因为它超越了查询存储器的被动恢复能力,并引入了主动的SQL性能增强。这项开创性技术允许用户从查询存储器(Query Store)中提取SQL并通过在查询存储器中创建新的和改进的查询计划来进行优化。使用Tosska DB Ace,用户可以将这些新计划应用于他们的SQL,而无需修改程序源代码或进行广泛的测试。

Tosska DB Ace Enterprise for SQL Server – Tosska Technologies Limited
DBAS Tune SQL QS – YouTube

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

如何对具有 DB Link 的 Oracle SQL 进行调整 II ?

以下是一个 SQL 示例,查询从远程数据库 @richdb 检索员工、部门和等级表。

SELECT   *
FROM     emp_subsidiary@richdb a,
         department@richdb,
         grade@richdb
WHERE    emp_grade < 1200
         AND emp_dept = dpt_id
         AND emp_grade = grd_id
ORDER BY emp_id

下面是此 SQL 的查询计划,它花费了 15.92 秒才能完成。查询计划的第一步是“SELECT STATEMENT REMOTE”,这意味着整个查询将在远程数据库 @richdb 上执行,并将结果发送回本地数据库。查询计划有点复杂,不容易判断是否最优。但如果查询在本地数据库 @local 上部分执行,我们可以尝试一件事情。

为了请求 Oracle 在本地数据库中执行某些连接操作,SQL 查询必须包含至少一个在本地数据库中执行的表。这才允许在 SQL 查询中使用提示 /*+ DRIVING_SITE ( [ @ queryblock ] tablespec ) */。如果没有表在本地数据库中显式执行,则没有办法请求 Oracle 尝试在本地数据库中执行连接操作。
我们可以在 SQL 中添加一个虚拟条件“EXISTS (SELECT ‘X’ FROM DUAL)”和提示 /*+ DRIVING_SITE(DUAL) */,以强制 Oracle 在本地数据库中执行一些连接操作。

SELECT   /*+ DRIVING_SITE(DUAL) */ *
FROM     emp_subsidiary@richdb a,
         department@richdb,
         grade@richdb
WHERE    emp_grade < 1200
         AND emp_dept = dpt_id
         AND emp_grade = grd_id
         AND EXISTS ( SELECT ‘x’
                      FROM   dual)
ORDER BY emp_id

以下是修改后的 SQL 的查询计划,它花费了 4.08 秒,比原始 SQL 语句快约 4 倍,其中仅有一个连接操作在远程数据库中执行。

在 SQL 查询中添加 ORDERED 提示可以进一步优化查询。这将会将在上一个查询计划中标记的复合语句分解为单个表数据的远程提取,如下面的查询计划所示。

SELECT   /*+ DRIVING_SITE(DUAL) ORDERED */ *
FROM     emp_subsidiary@richdb a,
         department@richdb,
         grade@richdb
WHERE    emp_grade < 1200
         AND emp_dept = dpt_id
         AND emp_grade = grd_id
         AND EXISTS ( SELECT ‘x’
                      FROM   dual)
ORDER BY emp_id

如果您熟悉 Oracle Exadata,您可能会注意到远程数据库 @richdb 中 REMOTE 表的数据检索过程,类似于 Exadata 存储服务器的工作方式。
需要记住的是,将此技术应用于具有 DB Link 的 SQL 查询只在某些环境下有益。例如,当网络速度良好、数据流量不大且本地数据库的工作负载较低时,这种技术是理想的。

Tosska DB Ace for Oracle 可以自动执行此类重写,从而生成一个比原始 SQL 查询快近 10 倍的 SQL 查询。

Tosska DB Ace Enterprise for Oracle – Tosska Technologies Limited

DBAO Tune DB Link SQL – YouTube

如何对具有 DB Link 的 Oracle SQL 进行调整 I ?

这里是一个示例SQL查询,用于计算本地数据库中以字母“D”开头的每个部门在远程数据库@richdb上员工的平均工资。


SELECT   Avg(emp_salary),
         emp_dept
FROM     employee@richdb
WHERE    emp_dept IN (SELECT dpt_id
                      FROM   department
                      WHERE  dpt_name LIKE ‘D%’)
GROUP BY emp_dept

以下是此SQL的查询计划,它需要9.16秒才能完成。查询计划显示从本地DEPARTMENT到远程EMPLOYEE数据库的嵌套循环。由于EMPLOYEE表的大小比DEPARTMENT表大得多,因此在这种情况下,嵌套循环连接路径不是最优的。

为了要求Oracle考虑在远程数据库@richdb中执行连接操作,我添加了一个提示/*+ DRIVING_SITE(employee) */,告诉Oracle使用EMPLOYEE表的数据库@richdb作为分布式查询的驱动站点。


SELECT   /*+ DRIVING_SITE(employee) */ Avg(emp_salary),
         emp_dept
FROM     employee@richdb
WHERE    emp_dept IN (SELECT dpt_id
                      FROM   department
                      WHERE  dpt_name LIKE ‘D%’)
GROUP BY emp_dept

以下查询显示驱动站点已更改为@richdb,并且远程从“本地”数据库检索DEPARTMENT数据。现在速度已经提高到5.94秒。但是查询计划有点复杂,有一个视图,由EMPLOYEE和DEPARTMENT的两个“索引快速完整扫描”的索引哈希连接构成。

我进一步更改了SQL,并在子查询的选择列表中添加了一个虚拟操作Coalesce(dpt_id,dpt_id),以阻止DEMPARTMENT表的索引快速全扫描。


SELECT   /*+ DRIVING_SITE(employee) */ Avg(emp_salary),
         emp_dept
FROM     employee@richdb
WHERE    emp_dept IN (SELECT Coalesce(dpt_id,dpt_id)
                      FROM   department
                      WHERE  dpt_name LIKE ‘D%’)
GROUP BY emp_dept

这个更改给SQL带来了一个新的查询计划,如下所示,性能显著提高至0.71秒。您可以从这个例子中了解虚拟操作Coalesce(dpt_id,dpt_id)如何影响Oracle SQL优化器的决策。

这种重写可以通过Tosska DB Ace for Oracle自动完成,它表明这个重写比原始SQL快了近13倍。

Tosska DB Ace Enterprise for Oracle – Tosska Technologies Limited

如何使用80/20法则来优化数据库应用 I ?

2002年,微软报告称,他们软件中大多数的错误和崩溃都是由他们检测到的少数bug引起的,具体来说大约是20%。这种现象,称为帕累托原理或八二法则,也适用于数据库SQL行为的分析。假定只有20%的SQL语句应该占据整个数据库系统80%的资源消耗。任何应用系统中,少于20%的SQL语句消耗了80%以上的数据库资源,都被认为是异常的,并应进行审查和优化。

使用SQL资源谱分析技术,用户可以设置资源阈值百分比(通常在80%左右)来确定负责大部分资源消耗的前M%的SQL语句。如果分析显示只有少数SQL语句(例如只有4%)负责超过总资源消耗的80%,则表明优化这些特定的SQL语句有可能显著提高整个数据库的性能。

以下的例子显示了从Oracle SGA获取的90个SQL语句集合,每个语句在图表中按其资源使用量列出,从左侧的最高消耗SQL开始以降序排列。分析显示,约14.44%的SQL语句占据了总经过时间的80%,而21.11%的SQL语句占据了总CPU时间的80%。这表明SQL工作负载的分布与80/20法则接近对齐。因此,可能没有急迫的需要进行SQL调优,因为这不太可能导致显着的性能提升。

另一个例子突显了SQL工作负载分布中对80/20法则的显著偏差。分析显示,大约有4个SQL语句占据了总经过时间的80%,而另外4个SQL语句则占据了总CPU时间的80%,还有2个SQL语句贡献了总磁盘读取量的80%。这表明,通过仅优化这4个SQL语句,可以在数据库的整体性能方面实现显著的改进。

您可以利用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

如何为SQL Server调优子查询中带有OR条件的SQL语句?

下面示例显示带有EXISTS子查询的SQL语句。如果在DEPARTMENT表的子查询中满足OR条件,SQL会对EMPLOYEE表中的记录进行计数。

select countn(*) from employee a where
exists (select ‘x’ from department b
   where a.emp_id=b.dpt_manager or a.emp_salary=b.dpt_avg_salary
    )

下面是Tosska专有的树结构执行计划,它需要4分29秒才能完成。

该执行计划显示了从EMPLOYEE到全表扫描DEPARTMENT表的嵌套循环,这是整个执行计划的主要问题。因为 SQL Server 无法通过其他连接操作来解决这个OR条件“a.emp_id=b.dpt_manager or a.emp_salary=b.dpt_avg_salary”。

下面我将子查询中的OR条件改写为UNION ALL子查询,子查询中UNION ALL的第一部分表示“a.emp_id=b.dpt_manager”条件,第二部分表示“a.emp_salary=b.dpt_avg_salary”条件,但排除已经满足第一个条件的数据。

select count(*)
from  employee a
where exists ( select ‘x’
        from   department b
        where a.emp_id = b.dpt_manager
        union all
        select  ‘x’
        from   department b
        where ( not ( a.emp_id = b.dpt_manager )
           or b.dpt_manager is null )
           and a.emp_salary = b.dpt_avg_salary )

下面是改写后SQL的执行计划,看起来有点复杂,但现在性能很好,只需要0.447秒。有两个散列匹配连接用于替换原来从EMPLOYEE表到全表扫描DEPARTMENT表的嵌套循环。

虽然最终改写的步骤有点复杂,但这种改写可以由Tosska SQL Tuning Expert for SQL Server自动完成,这表明该改写SQL比原始SQL快了600多倍。

Tosska SQL Tuning Expert (TSES™) for SQL Server® – Tosska Technologies Limited