优化 SQL 查询的秘诀 - 了解 SQL 执行顺序🚀

在本文中，我们将学习数据库引擎如何执行 SQL 查询，以及如何利用这些知识来优化查询，以获得更高的性能和准确性。我们还将学习一些编写高效且可重复查询的常用技巧和最佳实践。😎

什么是 SQL 执行顺序？🤔

SQL 执行顺序是数据库引擎处理 SQL 查询各个组件的实际顺序。它与我们编写查询的顺序不同。通过遵循特定的执行顺序，数据库引擎可以最大限度地减少磁盘 I/O，有效地使用索引，并避免不必要的操作。这可以加快查询执行速度并降低资源消耗。

让我们举一个 SQL 查询的例子，看看它是如何执行的：

SELECT
customers.name,
COUNT(order_id) as Total_orders,
SUM(order_amount) as total_spent
FROM customers
JOIN orders ON customers.id = orders.customer_id
WHERE order_date >= '2023-01-01'
GROUP BY customers.name
HAVING total_spent >= 1000
ORDER BY customers.name
LIMIT 100;

该查询的执行顺序如下：

1. FROM 子句：第一步是识别查询中涉及的表。在本例中，它们是客户表和订单表。

2. JOIN 子句：下一步是根据连接条件执行连接操作。在本例中，该条件为customers.id = orders.customer_id，通过匹配客户 ID 来连接两个表。

3. WHERE 子句：第三步是将过滤条件应用于已连接的表。在本例中，条件是 order_date >= '2023-01-01'，表示仅选择 2023 年 1 月 1 日或之后的订单。现在，编写 SARGABLE 查询以有效利用索引至关重要。SARGABLE 的全称是 Searched ARGUment ABLE，指的是可以使用索引来加快执行速度的查询。我们将在本文的后面深入探讨 SARGABLE 查询。

4. GROUP BY 子句：第四步是按指定列对行进行分组。在本例中，该子句为customers.name，它根据客户名称创建组。

5. HAVING 子句：第五步是按条件筛选组。在本例中，条件是 total_spent >= 1000，即仅选择总消费金额为 1000 或以上的组。

6. SELECT 子句：第六步是从每个组中选择列和聚合函数。在本例中，它们是customers.name、COUNT(order_id) 作为Total_orders，以及SUM(order_amount) 作为total_spent。

7. ORDER BY 子句：第七步是按指定的列对行进行排序。在本例中，该子句为customers.name，它按客户名称的字母顺序对行进行排序。

8. LIMIT 子句：最后一步是从排序的结果集中跳过一些行。在本例中，它将结果限制为最多 100 行。

图片来源

为什么SARGABLE查询很重要？🙌

SARGABLE 代表可搜索参数 (Searched ARGUment ABLE)，指的是可以使用索引来加快执行速度的查询。索引是一种数据结构，它按排序顺序存储表中列的子集，以便快速查找和比较。

如果查询使用的运算符和函数能够利用索引，则该查询是可查询的 (SARGABLE)。例如，在索引列上使用等号 (=)、不等号 (<>、!=)、范围 (BETWEEN) 或成员 (IN) 运算符可以使查询可查询。

如果查询使用的运算符或函数阻止使用索引或需要全表扫描，则该查询不属于 SARGABLE。例如，在索引列上使用否定 (NOT)、通配符 (LIKE) 或算术运算符（+、-、*、/）可能会导致查询不属于 SARGABLE。

要编写 SARGABLE 查询，我们应该遵循一些一般准则：

• 避免在 WHERE 子句中的索引列上使用函数，例如 UPPER()、LOWER()、SUBSTRING() 等。
• 避免在 WHERE 子句中对索引列使用算术运算，例如列 + 1 > 10、列 * 2 < 20 等。
• 避免在 WHERE 子句中的索引列上使用否定运算符，例如 NOT IN、NOT LIKE、NOT EXISTS 等。
• 避免在 WHERE 子句中对索引列使用以通配符 (%) 开头的通配符运算符，例如 LIKE '%abc'、LIKE '%xyz%' 等。
• 对列和文字使用适当的数据类型，以避免影响索引使用的隐式转换。

以下是 SARGABLE 和非 SARGABLE 查询的一些示例：

Bad: SELECT ... WHERE Year(myDate) = 2022
Fixed: SELECT ... WHERE myDate >= '01-01-2022' AND myDate < '01-01-2023'

Bad: Select ... WHERE SUBSTRING(DealerName,4) = 'Ford'
Fixed: Select ... WHERE DealerName Like 'Ford%'

Bad: Select ... WHERE DateDiff(mm, OrderDate, GetDate ()) >= 30
Fixed: Select ... WHERE OrderDate < DateAdd(mm, -30, GetDate())

如何调整数据库级别的性能？

提高 SQL 执行顺序的性能涉及优化数据库引擎处理和执行 SQL 查询所遵循的步骤。以下是一些提高 SQL 执行顺序性能的方法：

• 使用合适的索引: 分析查询模式并识别搜索、连接和过滤操作中经常使用的列。在这些列上创建索引，以加快数据检索速度并减少全表扫描的需要。

• 优化连接操作：确保连接条件高效并利用合适的索引。尽可能使用内连接 (INNER JOIN) 而非外连接 (OUTER JOIN)，因为内连接通常能带来更佳的性能。考虑连接多个表的顺序，以最小化中间结果集的大小。

• 限制结果集大小：使用 LIMIT 子句限制查询返回的行数。这可以减少处理的数据量并缩短查询响应时间。

• 避免不必要的排序和分组：仅在需要时添加不必要的排序和分组操作。这可以通过仔细分析查询并删除不必要的 ORDER BY 和 GROUP BY 子句来实现。

• 使用 WHERE 子句尽早过滤：使用 WHERE 子句在查询执行顺序中尽早应用过滤条件。这可以减少后续步骤处理的行数，从而提高性能。

• 使用合适的数据类型: 为列选择正确的数据类型，以确保高效的数据存储和检索。使用合适的数据类型有助于减少内存消耗并提高查询执行速度。

• 避免不必要的计算和函数：尽量减少查询中的计算和函数的使用，尤其是在索引列上。这些操作可能会妨碍索引的使用并影响性能。必要时，请考虑预先计算值或使用派生列。

• 查询优化工具：利用特定于数据库的查询优化工具或提示来指导数据库引擎生成高效的执行计划。这些工具可以提供洞察、建议和统计数据，从而提高性能。

结论

在本文中，我们了解了 SQL 执行顺序对查询性能和数据库效率的重要性。我们可以通过索引、连接、过滤、SARGABLE 查询和最佳实践来增强它。这将提高我们的 SQL 速度，并使我们的数据库系统性能更佳。

参考及实用资源

• 理解 SQL 执行顺序 — ByteByteGo
• Sargable 是什么？
• 什么是查询计划？
• 13 个最佳 SQL 查询优化工具

如果您正在使用Medium，请支持我并关注我，获取更多有趣的文章。Medium简介

订阅我的新闻通讯：

如果您准备订阅，只需点击以下链接：

订阅我的新闻通讯

点击链接，您将跳转至新闻通讯页面，轻松订阅，并定期接收直接发送到您邮箱的更新。不要错过软件开发领域的最新趋势和专家分析。

如果本指南对您和您的团队有帮助，请与其他人分享！