遇到SQL 子查询性能很差?其实可以这样优化

本文涉及的产品
云原生数据库 PolarDB MySQL 版,通用型 2核4GB 50GB
云原生数据库 PolarDB PostgreSQL 版,标准版 2核4GB 50GB
简介: 遇到SQL 子查询性能很差?其实可以这样优化

介绍

SQL 允许您在可能出现表或列名称的几乎任何地方使用子查询。您所要做的就是用括号将查询括起来,例如(SELECT ...),然后您可以在任意表达式中使用它。这使得 SQL 成为一种强大的语言,但是可能难以阅读。但我不想讨论 SQL 的美或丑。在本教程中,我们来看看如何编写出表现良好的子查询。让我们先从简单开始,稍后再来了解更令人惊讶和复杂的话题。

相关和不相关的子查询

在子查询中,您可以使用外部的表列,例如

SELECT a.col1,
       (SELECT b.col2 FROM b WHERE b.x = a.x)
FROM a;

对“a”中的每一行,子查询会不同。这样的子查询通常称为相关子查询。不相关的子查询是指不引用任何外部内容的子查询。

不相关的子查询很简单。如果 PostgreSQL 优化器没有“拉起它”(将其集成到主查询树中),则执行器将在单独的步骤中计算它。您可以在EXPLAIN的输出中看到InitPlan(初始计划)。不相关的子查询几乎从来都不是性能问题。在本文的其余部分,将会主要讨论相关的子查询。

标量和表格子查询

如果在 SQL 语句中的某个位置编写一个子查询,而该位置本来需要写入单个值,则该子查询是标量子查询。标量子查询的一个示例是上一节中的示例。一个不同的例子是

SELECT a.col1
FROM a
WHERE 1 = (SELECT count(*)
           FROM b
           WHERE b.x = a.x);

如果标量子查询不返回任何结果,则结果值为 NULL。如果查询返回多于一行,您会收到运行时错误:

ERROR:  more than one row returned by a subquery used as an expression

表格子查询出现在可以返回多个值的上下文中:

? FROM列表条目:FROM (SELECT ...) AS alias

? 公共表表达式(CTE):WITH q AS (SELECT ...) SELECT ...

? IN或NOT IN表达式:WHERE a.x IN (SELECT ...)

? EXISTS或NOT EXISTS表达式:WHERE NOT EXISTS (SELECT ...)

标量子查询通常有性能问题

我的经验法则是:应当尽量避免相关的标量子查询。原因是 PostgreSQL 只能以嵌套循环方式来执行标量子查询。例如,PostgreSQL 会对表 “a” 中的每一行,执行一次前面提到的子查询。如果“a”是一个小表,这可能很好(请记住,这里的建议只是一个经验法则)。但是,如果表 “a” 很大,即使是快速的子查询,也会使查询执行速度慢得令人难受。

重写 SELECT 列表或 WHERE 子句中的标量子查询

如果相关的标量子查询对性能不利,我们如何避免它们?没有单一的、直接的答案,您可能无法重写查询,以避免在所有情况下都出现此类子查询。但通常的解决方案是,将子查询转换为连接。对于我们的第一个查询,它将如下所示:

SELECT a.col1,
       b.col2
FROM a
   LEFT JOIN b ON b.x = a.x;

查询在语义上是等效的,不同之处在于如果“a”中的行与“b”中的多行匹配,则不会收到运行时错误。我们需要一个外部连接,来说明子查询不返回任何结果的情况。

对于我们的第二个示例,重写后的查询将如下所示:

SELECT a.col1
FROM a
   JOIN b ON b.x = a.x
GROUP BY a.pkey, a.col1
HAVING count(*) = 1;

这里,a.pkey是“a”的主键。根据a.col1分组是不够的,因为表 “a” 中的两个不同行可能具有相同的col1值。

像上面这样重写查询的优点是,PostgreSQL 可以选择最佳连接策略,并且不限于嵌套循环。如果表 “a” 只有几行,这可能没有区别,因为无论如何,嵌套循环连接可能是最有效的连接策略。但是,在这种情况下,查询也不会因重写而表现变差。如果 “a” 很大,则使用哈希或合并连接的速度会快得多。

表格子查询和性能

虽然相关的标量子查询通常很糟糕,但表格子查询的情况也没那么简单。让我们分别考虑不同的情况。

FROM 中的 CTE 和子查询

这些情况几乎相同,因为您始终可以将 CTE 重写为FROM中的子查询,除非它是递归的、MATERIALIZED的或数据修改的 CTE。CTE 不会是相关的,因此它们永远不会有问题。但是,FROM子句条目可以在横向连接中关联:

SELECT a.col1,sub.col2
FROM a
CROSS JOIN LATERAL
(SELECT b.col2
FROM b
WHERE b.x = a.x
ORDER BY b.sort
       LIMIT 1) AS sub;

同样,PostgreSQL 将在嵌套循环中执行这样的子查询,这对于大型表 “a” 可能会表现不佳。因此,重写查询以避免相关的子查询,通常是一个好主意:

SELECT DISTINCT ON (a.pkey)
       a.col1, b.col2
FROM a
   JOIN b ON b.x = a.x
ORDER BY a.pkey, b.sort;

如果 “a” 有很多行,则重写后的查询性能会更好,但如果 “a” 很小而 “b” 很大可能会更差,除非在(x, sort)上面有索引。

EXISTS 和 NOT EXISTS 中的子查询

这是一个特例。到目前为止,我一直建议避免相关子查询。但是使用EXISTS和NOT EXISTS,PostgreSQL 优化器能够将子句分别转换为半连接和反连接。这使得 PostgreSQL 可以使用所有连接策略,而不仅仅是嵌套循环。

因此,PostgreSQL 可以高效地处理EXISTS和NOT EXISTS的相关子查询。

IN 和 NOT IN 的棘手情况

您可能会期望这两种情况的行为相似,但事实并非如此。在一个查询中,使用IN的子查询始终可以使用EXISTS重写。例如,下面的语句:

SELECT a.col1
FROM a
WHERE a.foo IN (SELECT b.col2
                 FROM b
                 WHERE a.x = b.x);

等价于

SELECT a.col1
FROM a
WHERE EXISTS (SELECT NULL
              FROM b
              WHERE a.x = b.x
                AND a.foo = b.col2);

PostgreSQL 优化器可以做到这一点,并会像处理EXISTS中的子查询一样,高效地处理IN中的子查询。

然而,NOT IN的情况却大不相同。您可以像上面一样,将NOT IN重写为NOT EXISTS,但这不是 PostgreSQL 可以自动完成的转换,因为重写的语句在语义上不同:如果子查询返回至少一个 NULL 值,则NOT IN永远不会为 TRUE。而NOT EXISTS子句没有表现出这种令人惊讶的行为。

现在人们通常不关心NOT IN的这个特点(事实上,很少有人知道它)。无论如何,大多数人都更喜欢NOT EXISTS的这种行为。但是您必须自己重写 SQL 语句,并且不能指望 PostgreSQL 会自动执行此操作。因此,我的建议是,您永远不要使用带有子查询的NOT IN子句,而始终应改为使用NOT EXISTS 。

使用相关子查询强制嵌套循环连接

到目前为止,我已经告诉您如何重写 SQL 语句,以避免强制优化器使用嵌套循环。然而,有时你会需要完全相反的结果:你希望优化器使用嵌套循环连接,因为你恰好知道这是最好的连接策略。然后,您可以有意识地将常规连接重写为横向交叉连接,以强制嵌套循环。例如,下面的查询

SELECT a.col1, b.col2
FROM a
   JOIN b ON a.x = b.x;

在语义上等价于

SELECT a.col1, sub.col2
FROM a
   CROSS JOIN LATERAL
      (SELECT b.col2
       FROM b
       WHERE a.x = b.x) AS sub;

结论

如果您希望获得良好的子查询性能,通常最好遵循以下准则:

? 尽可能地使用不相关的子查询,只要它们不会让语句难以理解

? 在所有地方避免使用相关子查询,除非在EXISTS、NOT EXISTS和IN子句中

? 总是将NOT IN重写为NOT EXISTS

也不要把这些规则当作铁律。有时,相关子查询实际上可以表现得更好,有时您可以使用相关子查询,来强制优化器使用嵌套循环,只要您确定这是正确的策略。


相关实践学习
使用PolarDB和ECS搭建门户网站
本场景主要介绍基于PolarDB和ECS实现搭建门户网站。
阿里云数据库产品家族及特性
阿里云智能数据库产品团队一直致力于不断健全产品体系,提升产品性能,打磨产品功能,从而帮助客户实现更加极致的弹性能力、具备更强的扩展能力、并利用云设施进一步降低企业成本。以云原生+分布式为核心技术抓手,打造以自研的在线事务型(OLTP)数据库Polar DB和在线分析型(OLAP)数据库Analytic DB为代表的新一代企业级云原生数据库产品体系, 结合NoSQL数据库、数据库生态工具、云原生智能化数据库管控平台,为阿里巴巴经济体以及各个行业的企业客户和开发者提供从公共云到混合云再到私有云的完整解决方案,提供基于云基础设施进行数据从处理、到存储、再到计算与分析的一体化解决方案。本节课带你了解阿里云数据库产品家族及特性。
目录
相关文章
|
10天前
|
SQL 资源调度 分布式计算
如何让SQL跑快一点?(优化指南)
这篇文章主要探讨了如何在阿里云MaxCompute(原ODPS)平台上对SQL任务进行优化,特别是针对大数据处理和分析场景下的性能优化。
|
19天前
|
SQL 监控 数据库
慢SQL对数据库写入性能的影响及优化技巧
在数据库管理系统中,慢SQL(即执行缓慢的SQL语句)不仅会影响查询性能,还可能对数据库的写入性能产生显著的不利影响
|
22天前
|
SQL Oracle 关系型数据库
Oracle SQL:了解执行计划和性能调优
Oracle SQL:了解执行计划和性能调优
34 1
|
8天前
|
SQL 数据库 开发者
8种SQL编写陷阱:性能杀手还是团队乐趣?
【10月更文挑战第17天】记住,一个好的开发者不仅要知道如何编写代码,还要知道如何编写高效的代码。
11 0
|
19天前
|
SQL 存储 数据库
慢SQL对数据库写入性能的影响及优化技巧
在数据库管理系统中,慢SQL(即执行缓慢的SQL语句)不仅会影响查询性能,还可能对数据库的写入性能产生显著的不利影响
|
22天前
|
SQL 数据处理 数据库
警惕!这八个 SQL 习惯正在拖垮数据库性能
【10月更文挑战第3天】在日常的数据库开发与维护工作中,编写高效、清晰的SQL语句是每位数据工程师的必修课。然而,不当的SQL编写习惯不仅能降低查询效率,还可能给同事的工作带来不必要的困扰。今天,我们就来揭秘八种常见的“专坑同事”SQL写法,助你避免成为那个无意间拖慢整个团队步伐的人。
31 0
|
22天前
|
SQL 数据处理 数据库
SQL语句优化与查询结果优化:提升数据库性能的实战技巧
在数据库管理和应用中,SQL语句的编写和查询结果的优化是提升数据库性能的关键环节
|
4月前
|
SQL 监控 数据库
|
2月前
|
关系型数据库 MySQL 网络安全
5-10Can't connect to MySQL server on 'sh-cynosl-grp-fcs50xoa.sql.tencentcdb.com' (110)")
5-10Can't connect to MySQL server on 'sh-cynosl-grp-fcs50xoa.sql.tencentcdb.com' (110)")