SQL 查询优化基础指南

使我编写高性能查询的最佳实践

摩根豪塞尔在Unsplash上的照片

SQL 查询优化是选择最有效的 SQL 语句执行方式的过程，是任何应用程序的重要组成部分，因为它可以改善用户体验。

即使 SQL 查询中的微小变化也可以显着提高性能，并且随着时间的推移，我采用了一些帮助我提高查询性能的最佳实践，并希望这些对希望编写高性能查询的人有所帮助。

更喜欢 UNION ALL 而不是 UNION
如果重复不是问题，则UNION ALL不会丢弃它们，并且由于UNION ALL不负责删除重复，因此查询将更有效率。

使用 TRUNCATE 而不是 DELETE 删除表中的所有行
TRUNCATE语句通过删除表并重新创建具有相同名称的新表来删除表中的所有行。这比使用DELETE执行得更好，后者执行多个事务以删除所有行。

在加载批量数据之前删除索引
将数千行插入具有索引的表中会减慢数据的摄取速度。

在这种情况下，最好在加载表之前删除索引。加载完成后，重新创建表上的索引。

避免过多的子查询或 CTE 连接
连接许多子查询或 CTE 可能会导致将所有行保存在内存中，并且需要不断地将数据溢出到磁盘以促进连接操作，并且完成查询需要很长时间。

对此的解决方案是将数据或所有子查询/CTE 加载到中间/临时表中，然后使用这些表进行连接，因为这不涉及保存在内存中，而是使用磁盘中可用的预先计算的数据通过计算查询的中间/临时表。

这有助于我将查询执行时间从 7 个多小时缩短到 10 分钟。这简直是​荒谬！！！！

尽可能使用数据压缩
这对于 redshift 和 snowflake 等列式数据仓库以及 SQL Server 等基于行的数据库非常有效。

大多数情况下，从磁盘子系统读取数据比解压缩数据花费的时间更长。如果瓶颈在 I/O 级别，则使用压缩有助于减小磁盘上的表或索引的大小并加快查询执行速度。

在 JOIN 和 WHERE 子句上使用相同的数据类型
当连接或比较具有不同数据类型的两个字段时，SQL 必须在进行比较之前对字段进行即时转换，即使字段已编入索引，这需要资源并且可能会降低性能。如果无法避免不匹配的数据类型，请尽可能尝试将较大的数据类型转换为较小的数据类型。

确保您的查询利用 INDEXING
人们在遇到数据库查询性能问题时遇到的最常见的事情之一是缺乏足够的索引。

您应该索引哪些列通常取决于您过滤的列（即，哪些列通常最终出现在您的WHERE子句中）。如果您发现您总是按一组常见的列进行过滤，您应该考虑为这些列编制索引。

除非需要，否则避免相关子查询
相关子查询依赖于外部查询。由于它逐行执行，因此会降低流程的整体速度。

避免

SELECT c.Name, c.City,
(
SELECT CompanyName
FROM Company
WHERE ID = c.CompanyID
) AS CompanyName
FROM Customer c
更喜欢

SELECT c.Name, c.City, co.CompanyName
FROM
Customer c
LEFT JOIN
Company co
ON c.CompanyID = co.CompanyID
使用等号运算符 (=) 代替 LIKE 子句
‘=’ 和 LIKE 子句用于匹配具有特定值的行。两者的主要区别在于 LIKE 运算符用于匹配通配符（例如 %）以搜索部分字符串，而等于运算符“=”用于查找完全匹配。

如果您必须在两者之间进行选择，请始终使用等号运算符（“=”），因为它使用索引列，可以更快地搜索值，但如果搜索要求是关于搜索特定模式，那么可以使用 LIKE。

避免在 WHERE 子句中连接列
尽可能避免在 WHERE 子句中串联。您应该避免在 WHERE 子句中连接多个列。如果存在串联，则将查询分​​解为多个条件。

避免

SELECT 姓名，姓氏
FROM 类
WHERE 姓名 || 姓氏 = ‘Alexmercer’
更喜欢

选择名称，姓氏
来自类
WHERE name = ‘Alex’
AND surname = ‘mercer’
避免对大表使用 SELECT DISTINCT
SELECT DISTINCT子句允许您通过删除重复条目从查询中获取唯一条目。但是，SELECT DISTINCT的计算量很大。建议尽可能避免它，直到它是必须的。