「SQL」SQL 查询性能调优

一、背景说明

复杂 SQL 查询的性能调优是提升数据库系统效率的关键环节。本文结合实际示例，总结了复杂 SQL 查询性能优化的主要方法及实践经验。

二、优化流程与方法

1. 理解查询结构

  • 将 SQL 查询分解为更小的部分（如 SELECT、JOIN、WHERE 子句）。

  • 识别执行负载较重的操作，如子查询、连

  • 接和聚合（GROUP BY 等）。

示例：

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（PostgreSQL 可用 EXPLAIN ANALYZE）或 SQL Server 执行计划等工具，定位资源消耗最集中的环节（如全表扫描、排序、连接）。

  • 可通过 SET SHOWPLAN_TEXT ON 获取执行计划，便于进一步分析。

3. 索引优化

  • 在 JOIN、WHERE 和 ORDER BY 子句涉及的列上创建或优化索引。

  • 对多条件查询使用复合索引，提升检索效率。

  • 使用覆盖索引，将查询涉及的所有列包含在索引中。

  • 避免对小表建立索引，防止不必要的性能开销。

  • 定期监控索引碎片化情况，及时重建或重组索引。

  • 避免在 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 替代子查询，避免相关子查询带来的多次执行开销。

  • 若不需要不匹配数据，建议使用内连接。

子查询示例：

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 order by total_orders desc ;

6. 尽早限制数据处理

  • 在操作或排序前通过 WHERE 子句过滤数据，减少后续处理量。

  • 使用 LIMIT（MySQL）或 OFFSET/FETCH（SQL Server）控制结果集大小。

示例：

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”

  • 除非必须返回受影响的行数，建议在 T-SQL 代码块前加 SET NOCOUNT ON，结束时关闭，以减少额外性能开销。

三、结论与建议

调优复杂 SQL 查询需结合执行计划分析、索引优化与逻辑重构等多种手段，逐步提升查询性能、缩短执行时间、降低资源消耗。建议团队持续关注查询性能，定期进行优化和监控，保障数据库系统高效稳定运行。

评论