如何优化复杂的 SQL 查询性能

 

性能优化复杂 SQL 查询涉及理解其执行流程、优化查询结构以及改进数据库交互。在本文中，我将讨论一种有助于优化复杂 SQL 查询性能的方法。

为了理解这种方法，我们将使用一些示例。在这些示例中，我们使用了示例数据库 AdventureWorks2022 中的两个表（sales.Customer 和 Sales.SalesOrderHeader）。

我们已复制这两个表，用于本文，如下所示：

select * into sales.Customer_demo from sales.Customer
select * into Sales.SalesOrderHeader_demo from Sales.SalesOrderHeader

1. 理解查询结构

在第一步中，我们需要将 SQL 查询分解为多个部分，并识别执行繁重操作的部分，如连接、子查询和聚合（如 group by 等）。

• 将 SQL 查询分解为更小的部分（SELECT、JOIN、WHERE 子句）。
• 识别繁重操作，如子查询、连接和聚合。

示例

select * from sales.Customer_demo cust 
inner join Sales.SalesOrderHeader_demo soh on cust.CustomerID=soh.CustomerID 
WHERE soh.orderdate >= '2013-01-01'

2. 分析执行计划

• 在此步骤中，我们需要确定查询的哪些部分消耗的资源最多（扫描、排序、连接）。为此，我们可以使用 EXPLAIN、EXPLAIN ANALYZE（PostgreSQL）或SQL Server 执行计划等工具。

使用 SQL Server 执行计划的示例:

如果获取上述 SQL 查询的执行计划，执行计划会显示正在进行全表扫描，而不是索引查找，如下所示：

我们还可以使用 SET SHOWPLAN_TEXT ON 来获取执行计划，如下所示：

3. 索引优化

• 在 JOIN、WHERE 和 ORDER BY 子句中使用的列上创建或修改索引。
• 对多个条件使用复合索引。
• 使用覆盖索引将 SELECT 查询的所有列包含在索引中。
• 避免对小型表使用索引，因为这会不必要地增加开销。
• 识别并删除不再引用的索引，因为这些索引会不必要地增加开销并降低查询性能。
• 应定期监控索引的碎片，并相应地重建/重组索引。
• 对于发生非常频繁的 DML 更改的表，应避免使用聚集索引，因为维护聚集索引的成本很高。它会对性能产生非常严重的影响。

示例:

CREATE INDEX idx_SalesOrderHeader_orderdate ON Sales.SalesOrderHeader_demo(CustomerID,orderdate) include (Totaldue);
CREATE INDEX idx_CustomerID ON sales.Customer_demo(CustomerID,AccountNumber);

4. 避免 SELECT *

• 仅指定您需要的列。检索不必要的列会增加数据传输时间并降低性能。

示例：而不是

SELECT * FROM sales.Customer_demo cust JOIN Sales.SalesOrderHeader_demo soh ON cust.CustomerID = soh.CustomerID WHERE soh.orderdate >= '2013-01-01';

应使用以下查询。

SELECT cust.AccountNumber,soh.TotalDue FROM sales.Customer_demo cust JOIN Sales.SalesOrderHeader_demo soh ON cust.CustomerID = soh.CustomerID WHERE soh.orderdate >= '2013-01-01';

5. 优化连接和子查询

• 尽可能优先使用JOIN 而非子查询/关联子查询，因为关联子查询可能会为每一行重新执行。使用 JOIN 而非子查询通常可以减少查询执行时间。
• 如果您不需要不匹配的数据，请使用内连接而不是外连接。

示例：而不是使用子查询

SELECT cust.AccountNumber, (SELECT COUNT(*) FROM Sales.SalesOrderHeader_demo soh WHERE soh.CustomerID = cust.CustomerID) as total_orders
FROM sales.Customer_demo cust order by total_orders desc ;

使用 JOIN

SELECT cust.AccountNumber, COUNT(soh.CustomerID) as total_orders
FROM sales.Customer_demo cust
LEFT JOIN Sales.SalesOrderHeader_demo soh ON cust.CustomerID = soh.CustomerID
GROUP BY cust.AccountNumber<br />order by total_orders desc ;

6. 尽早限制数据处理

• 在执行 JOIN 操作或排序之前，使用 WHERE 子句过滤数据。
• 如果适用，在 MySQL 中使用 LIMIT 来减少处理的行数。在 SQL Server 中，您可以使用 Row_number 函数或 OFSET/FETCH.. Next 来减少行数，如下所示。

示例:

SELECT cust.AccountNumber,soh.TotalDue FROM sales.Customer_demo cust 
JOIN Sales.SalesOrderHeader_demo soh ON cust.CustomerID = soh.CustomerID 
WHERE soh.orderdate >= '2013-01-01' order by  cust.AccountNumber
OFFSET  5 ROWS 
FETCH NEXT 5 ROWS ONLY

 

7. 重构复杂逻辑

• 将复杂查询分解为临时表或通用表表达式 (CTE)。这简化了查询，并允许在不同阶段进行优化。

示例:

WITH recent_orders AS ( SELECT soh.CustomerID, soh.totaldue FROM Sales.SalesOrderHeader_demo soh WHERE soh.orderdate >= '2013-01-01' )
SELECT cust.AccountNumber, ro.totaldue FROM sales.Customer_demo cust JOIN recent_orders ro ON cust.customerid = ro.customerid;

8. 避免在索引列上使用函数

• 避免在 WHERE 子句的索引列上应用函数，因为这会抵消索引的优势。

示例：而不是

select * from Sales.SalesOrderHeader_demo soh where YEAR(soh.orderdate) = '2013'

用途

select * from Sales.SalesOrderHeader_demo soh where  soh.orderdate >= '2013-01-01' AND soh.orderdate < '2014-01-01';

9. 监控和调整

• 使用 SQL Server Profiler、pg_stat_statements（PostgreSQL）或AWR Reports（Oracle）等工具持续监控数据库。
• 专注于减少资源密集型查询的 I/O、CPU 和内存使用。

10. 使用“set nocount on”

除非需要知道查询/存储过程执行影响的行数，否则应在代码块的开头使用“set nocount on”，在代码块的结尾使用“set nocount off”，因为计算受影响的行数会增加额外的性能成本。

结论

优化复杂 SQL 查询涉及分析其执行计划、优化索引以及重构逻辑以获得更好的性能。遵循此方法，您可以显著缩短查询执行时间并减少资源消耗。

 

此文章 如何优化复杂 SQL 查询的性能 最先出现在 Technology with Vivek Johari。