SQLServer 复杂查询优化

复杂查询优化概述

复杂查询是指包含多个表连接、子查询、CTE、聚合函数、窗口函数或复杂逻辑的查询。这些查询通常执行时间长、资源消耗大，是 SQL Server 性能优化的重点和难点。

复杂查询优化需要综合考虑查询设计、索引优化、执行计划分析和系统配置等多个方面。优化复杂查询可以显著提高系统性能，降低资源消耗，提升用户体验。

生产场景

在电商网站的订单报表系统中，复杂查询常用于生成销售统计、客户分析和库存报告，优化这些查询可以显著提高报表生成速度，提升业务人员的工作效率。

版本差异

SQL Server 2012-2014：引入了窗口函数，支持更高效的数据分析
SQL Server 2016+：增强了查询存储功能，便于性能监控和优化
SQL Server 2019+：引入了智能查询处理功能，自动优化某些类型的复杂查询

复杂查询的类型

多表连接查询

包含多个表连接的查询，连接类型包括 INNER JOIN、LEFT JOIN、RIGHT JOIN 和 FULL JOIN 等。

示例

sql

SELECT 
    c.CustomerName,
    o.OrderID,
    o.OrderDate,
    p.ProductName,
    od.Quantity,
    od.UnitPrice,
    od.Quantity * od.UnitPrice AS LineTotal
FROM 
    dbo.Customers c
INNER JOIN 
    dbo.Orders o ON c.CustomerID = o.CustomerID
INNER JOIN 
    dbo.OrderDetails od ON o.OrderID = od.OrderID
INNER JOIN 
    dbo.Products p ON od.ProductID = p.ProductID
WHERE 
    o.OrderDate BETWEEN '2023-01-01' AND '2023-12-31'
ORDER BY 
    c.CustomerName, o.OrderDate;

生产场景

在企业资源规划（ERP）系统中，多表连接查询常用于生成财务报表、供应链分析和生产计划，这些查询通常涉及 5 个以上的表连接。

子查询

查询中包含一个或多个子查询，子查询可以出现在 WHERE 子句、FROM 子句或 SELECT 子句中。

示例

sql

-- WHERE 子句中的子查询
SELECT 
    CustomerID, CustomerName
FROM 
    dbo.Customers
WHERE 
    CustomerID IN (SELECT CustomerID FROM dbo.Orders WHERE OrderDate >= '2023-01-01');

-- FROM 子句中的子查询（派生表）
SELECT 
    c.CustomerName,
    o.OrderCount,
    o.TotalAmount
FROM 
    dbo.Customers c
INNER JOIN (
    SELECT 
        CustomerID,
        COUNT(*) AS OrderCount,
        SUM(TotalAmount) AS TotalAmount
    FROM 
        dbo.Orders
    WHERE 
        OrderDate >= '2023-01-01'
    GROUP BY 
        CustomerID
) o ON c.CustomerID = o.CustomerID;

生产场景

在客户关系管理（CRM）系统中，子查询常用于查找特定条件的客户，如最近 30 天有购买行为的客户或购买金额超过阈值的客户。

公共表表达式 (CTE)

使用 WITH 子句定义的临时结果集，用于简化复杂查询和递归查询。

示例

sql

-- 简单 CTE
WITH TopCustomers AS (
    SELECT 
        CustomerID,
        SUM(TotalAmount) AS TotalSales
    FROM 
        dbo.Orders
    WHERE 
        OrderDate >= '2023-01-01'
    GROUP BY 
        CustomerID
    ORDER BY 
        TotalSales DESC
    OFFSET 0 ROWS FETCH NEXT 10 ROWS ONLY
)
SELECT 
    c.CustomerName,
    tc.TotalSales
FROM 
    TopCustomers tc
INNER JOIN 
    dbo.Customers c ON tc.CustomerID = c.CustomerID;

-- 递归 CTE
WITH EmployeeHierarchy AS (
    -- 锚点成员
    SELECT 
        EmployeeID,
        ManagerID,
        FirstName + ' ' + LastName AS EmployeeName,
        0 AS Level
    FROM 
        dbo.Employees
    WHERE 
        ManagerID IS NULL
    UNION ALL
    -- 递归成员
    SELECT 
        e.EmployeeID,
        e.ManagerID,
        e.FirstName + ' ' + e.LastName AS EmployeeName,
        eh.Level + 1 AS Level
    FROM 
        dbo.Employees e
    INNER JOIN 
        EmployeeHierarchy eh ON e.ManagerID = eh.EmployeeID
)
SELECT 
    EmployeeID,
    ManagerID,
    EmployeeName,
    Level
FROM 
    EmployeeHierarchy
ORDER BY 
    Level, EmployeeName;

生产场景

在人力资源管理系统中，递归 CTE 常用于生成组织架构图和管理报告，显示员工之间的层级关系。

版本差异

SQL Server 2005+：支持公共表表达式（CTE）
SQL Server 2008+：支持递归 CTE
SQL Server 2012+：支持 OFFSET FETCH 语法

聚合查询

包含聚合函数（如 SUM、COUNT、AVG、MAX、MIN）和 GROUP BY 子句的查询。

示例

sql

SELECT 
    c.CategoryName,
    p.ProductName,
    YEAR(o.OrderDate) AS OrderYear,
    MONTH(o.OrderDate) AS OrderMonth,
    SUM(od.Quantity) AS TotalQuantity,
    SUM(od.Quantity * od.UnitPrice) AS TotalSales
FROM 
    dbo.Categories c
INNER JOIN 
    dbo.Products p ON c.CategoryID = p.CategoryID
INNER JOIN 
    dbo.OrderDetails od ON p.ProductID = od.ProductID
INNER JOIN 
    dbo.Orders o ON od.OrderID = o.OrderID
WHERE 
    o.OrderDate >= '2022-01-01'
GROUP BY 
    c.CategoryName,
    p.ProductName,
    YEAR(o.OrderDate),
    MONTH(o.OrderDate)
HAVING 
    SUM(od.Quantity * od.UnitPrice) > 1000
ORDER BY 
    c.CategoryName,
    p.ProductName,
    OrderYear,
    OrderMonth;

生产场景

在销售分析系统中，聚合查询常用于生成销售报表，如按产品、类别、区域或时间维度的销售统计。

窗口函数查询

包含窗口函数（如 ROW_NUMBER、RANK、DENSE_RANK、LAG、LEAD）的查询。

示例

sql

SELECT 
    OrderID,
    CustomerID,
    OrderDate,
    TotalAmount,
    ROW_NUMBER() OVER (PARTITION BY CustomerID ORDER BY OrderDate DESC) AS OrderRank,
    LAG(TotalAmount, 1) OVER (PARTITION BY CustomerID ORDER BY OrderDate) AS PreviousOrderAmount,
    LEAD(TotalAmount, 1) OVER (PARTITION BY CustomerID ORDER BY OrderDate) AS NextOrderAmount,
    SUM(TotalAmount) OVER (PARTITION BY CustomerID ORDER BY OrderDate) AS RunningTotal
FROM 
    dbo.Orders
WHERE 
    OrderDate >= '2023-01-01';

生产场景

在金融系统中，窗口函数常用于计算移动平均值、累计总额、排名和前后比较等分析指标。

版本差异

SQL Server 2005+：支持基本窗口函数
SQL Server 2012+：扩展了窗口函数，增加了 LAG、LEAD、FIRST_VALUE、LAST_VALUE 等
SQL Server 2019+：支持窗口函数的批处理模式

复杂查询优化方法

优化查询设计

简化查询

减少不必要的表连接
避免不必要的子查询和 CTE
简化 WHERE 子句条件
减少 SELECT 子句中的列数

示例

sql

-- 复杂查询
SELECT 
    *
FROM 
    dbo.Orders o
INNER JOIN 
    dbo.OrderDetails od ON o.OrderID = od.OrderID
INNER JOIN 
    dbo.Products p ON od.ProductID = p.ProductID
INNER JOIN 
    dbo.Categories c ON p.CategoryID = c.CategoryID
WHERE 
    o.OrderDate >= '2023-01-01'
    AND c.CategoryName = 'Electronics'
    AND p.UnitPrice > 100;

-- 简化后的查询
SELECT 
    o.OrderID,
    o.OrderDate,
    o.CustomerID,
    p.ProductName,
    od.Quantity,
    od.UnitPrice
FROM 
    dbo.Orders o
INNER JOIN 
    dbo.OrderDetails od ON o.OrderID = od.OrderID
INNER JOIN 
    dbo.Products p ON od.ProductID = p.ProductID
WHERE 
    o.OrderDate >= '2023-01-01'
    AND p.CategoryID = (SELECT CategoryID FROM dbo.Categories WHERE CategoryName = 'Electronics')
    AND p.UnitPrice > 100;

生产场景

在实际生产环境中，简化查询可以减少查询的复杂度和资源消耗，特别是对于频繁执行的查询，简化查询设计可以显著提高系统性能。

使用合适的连接类型

优先使用 INNER JOIN，避免使用 OUTER JOIN
对于大表连接，考虑使用 HASH JOIN 或 MERGE JOIN
对于小表连接，使用 NESTED LOOPS JOIN

优化子查询

使用 EXISTS 替代 IN，特别是当子查询结果集较大时
使用 INNER JOIN 替代 FROM 子句中的子查询
避免在 WHERE 子句中使用相关子查询

示例

sql

-- 不推荐：使用 IN 子查询
SELECT 
    CustomerID, CustomerName
FROM 
    dbo.Customers
WHERE 
    CustomerID IN (SELECT CustomerID FROM dbo.Orders WHERE OrderDate >= '2023-01-01');

-- 推荐：使用 EXISTS 子查询
SELECT 
    CustomerID, CustomerName
FROM 
    dbo.Customers c
WHERE 
    EXISTS (SELECT 1 FROM dbo.Orders o WHERE o.CustomerID = c.CustomerID AND o.OrderDate >= '2023-01-01');

-- 推荐：使用 INNER JOIN
SELECT 
    DISTINCT c.CustomerID, c.CustomerName
FROM 
    dbo.Customers c
INNER JOIN 
    dbo.Orders o ON c.CustomerID = o.CustomerID
WHERE 
    o.OrderDate >= '2023-01-01';

生产场景

在订单系统中，查找最近 30 天有购买行为的客户是一个常见查询，使用 EXISTS 或 INNER JOIN 替代 IN 子查询可以显著提高查询性能。

优化索引

创建合适的索引

为连接列创建索引
为 WHERE 子句列创建索引
为 GROUP BY 和 ORDER BY 列创建索引
考虑使用覆盖索引，减少回表查询
对于复杂查询，考虑创建过滤索引

示例

sql

-- 为连接列和 WHERE 子句列创建索引
CREATE NONCLUSTERED INDEX IX_Orders_OrderDate_CustomerID ON dbo.Orders (OrderDate, CustomerID);
CREATE NONCLUSTERED INDEX IX_OrderDetails_ProductID_OrderID ON dbo.OrderDetails (ProductID, OrderID);
CREATE NONCLUSTERED INDEX IX_Products_CategoryID_UnitPrice ON dbo.Products (CategoryID, UnitPrice) INCLUDE (ProductName);

-- 创建过滤索引
CREATE NONCLUSTERED INDEX IX_Orders_OrderDate_Filtered ON dbo.Orders (OrderDate, CustomerID)
WHERE OrderDate >= '2023-01-01';

生产场景

在电商网站中，产品搜索和过滤是核心功能，创建合适的索引可以显著提高搜索查询的性能，提升用户体验。

版本差异

SQL Server 2005+：支持覆盖索引
SQL Server 2008+：支持过滤索引
SQL Server 2012+：支持列存储索引
SQL Server 2016+：支持内存优化表的索引

优化执行计划

分析执行计划

使用 SQL Server Management Studio 查看执行计划
识别执行计划中的瓶颈（如全表扫描、书签查找、昂贵的连接操作）
分析执行计划中的警告信息

常见执行计划问题

全表扫描：考虑添加索引或优化查询条件
书签查找：考虑使用覆盖索引
哈希匹配连接：对于大表连接是正常的，但对于小表连接可以优化
排序操作：考虑添加索引避免排序
并行执行：检查 MAXDOP 设置是否合理

示例

sql

-- 查看执行计划
SET SHOWPLAN_TEXT ON;
GO

SELECT 
    c.CustomerName,
    o.OrderID,
    o.OrderDate,
    p.ProductName,
    od.Quantity,
    od.UnitPrice
FROM 
    dbo.Customers c
INNER JOIN 
    dbo.Orders o ON c.CustomerID = o.CustomerID
INNER JOIN 
    dbo.OrderDetails od ON o.OrderID = od.OrderID
INNER JOIN 
    dbo.Products p ON od.ProductID = p.ProductID
WHERE 
    o.OrderDate BETWEEN '2023-01-01' AND '2023-12-31'
    AND p.CategoryID = 1
ORDER BY 
    c.CustomerName, o.OrderDate;
GO

SET SHOWPLAN_TEXT OFF;
GO

生产场景

在性能监控中，分析执行计划是定位查询瓶颈的重要方法，DBA 可以通过分析执行计划识别出需要优化的查询和索引。

优化系统配置

配置最大并行度 (MAXDOP)

根据 CPU 核心数和工作负载调整 MAXDOP 设置
对于 OLTP 工作负载，考虑将 MAXDOP 设置为 1 或较低的值
对于 OLAP 工作负载，可以将 MAXDOP 设置为较高的值

配置查询超时

根据业务需求设置合适的查询超时时间
避免设置过长的查询超时时间

优化内存配置

为 SQL Server 分配足够的内存
配置合适的最小和最大服务器内存

示例

sql

-- 设置最大并行度
EXEC sp_configure 'show advanced options', 1;
RECONFIGURE;
EXEC sp_configure 'max degree of parallelism', 8;
RECONFIGURE;

-- 设置并行查询阈值
EXEC sp_configure 'cost threshold for parallelism', 50;
RECONFIGURE;

生产场景

在数据仓库环境中，合理配置 MAXDOP 和并行查询阈值可以提高复杂查询的执行效率，充分利用系统资源。

版本差异

SQL Server 2012+：支持自动并行度调整
SQL Server 2016+：支持 Query Store 用于监控和分析查询性能
SQL Server 2019+：支持智能查询处理，自动优化执行计划

复杂查询优化最佳实践

优先优化最频繁执行的查询

识别并优化系统中最频繁执行的复杂查询
这些查询对系统性能的影响最大
使用 DMVs 或查询存储识别频繁执行的查询

示例

sql

-- 使用 DMVs 识别频繁执行的查询
SELECT 
    TOP 10
    execution_count,
    total_worker_time / execution_count AS avg_cpu_time,
    total_elapsed_time / execution_count AS avg_elapsed_time,
    total_logical_reads / execution_count AS avg_logical_reads,
    total_physical_reads / execution_count AS avg_physical_reads,
    SUBSTRING(text, statement_start_offset/2 + 1,
        (CASE WHEN statement_end_offset = -1 
            THEN LEN(CONVERT(nvarchar(max), text)) * 2 
            ELSE statement_end_offset 
        END - statement_start_offset)/2) AS query_text
FROM 
    sys.dm_exec_query_stats s
CROSS APPLY 
    sys.dm_exec_sql_text(s.sql_handle)
ORDER BY 
    avg_cpu_time DESC;

生产场景

在企业级应用中，通过监控和优化最频繁执行的前 10% 查询，可以解决 90% 的性能问题，这是一种高效的性能优化策略。

测试优化效果

在测试环境中测试优化效果
比较优化前后的查询性能（执行时间、CPU 使用率、I/O 开销）
确保优化不会引入新的问题

生产场景

在生产环境中优化查询前，必须在测试环境中进行充分测试，确保优化不会破坏现有功能或引入新的性能问题。

考虑使用临时表或表变量

对于复杂查询，可以考虑使用临时表或表变量存储中间结果
减少重复计算，提高查询性能
注意临时表和表变量的适用场景

示例

sql

-- 使用临时表存储中间结果
CREATE TABLE #TopProducts (
    ProductID INT PRIMARY KEY,
    ProductName NVARCHAR(50),
    TotalSales DECIMAL(10,2)
);

INSERT INTO #TopProducts
SELECT 
    p.ProductID,
    p.ProductName,
    SUM(od.Quantity * od.UnitPrice) AS TotalSales
FROM 
    dbo.Products p
INNER JOIN 
    dbo.OrderDetails od ON p.ProductID = od.ProductID
INNER JOIN 
    dbo.Orders o ON od.OrderID = o.OrderID
WHERE 
    o.OrderDate >= '2023-01-01'
GROUP BY 
    p.ProductID,
    p.ProductName
HAVING 
    SUM(od.Quantity * od.UnitPrice) > 10000
ORDER BY 
    TotalSales DESC
OFFSET 0 ROWS FETCH NEXT 10 ROWS ONLY;

-- 使用临时表进行后续查询
SELECT 
    tp.ProductName,
    tp.TotalSales,
    c.CategoryName,
    p.UnitPrice,
    p.UnitsInStock
FROM 
    #TopProducts tp
INNER JOIN 
    dbo.Products p ON tp.ProductID = p.ProductID
INNER JOIN 
    dbo.Categories c ON p.CategoryID = c.CategoryID;

-- 清理临时表
DROP TABLE #TopProducts;

生产场景

在生成月度销售报表时，使用临时表存储中间结果可以减少重复计算，提高报表生成速度，特别是当报表包含多个复杂查询时。

考虑使用索引视图

对于频繁执行的复杂查询，可以考虑使用索引视图
索引视图预计算并存储查询结果，提高查询性能
适用于查询模式稳定、数据更新不频繁的场景

示例

sql

-- 创建索引视图
CREATE VIEW dbo.vw_DailySales
WITH SCHEMABINDING
AS
SELECT 
    CONVERT(DATE, o.OrderDate) AS OrderDate,
    p.ProductID,
    p.ProductName,
    COUNT_BIG(*) AS OrderCount,
    SUM(od.Quantity) AS TotalQuantity,
    SUM(od.Quantity * od.UnitPrice) AS TotalSales
FROM 
    dbo.Orders o
INNER JOIN 
    dbo.OrderDetails od ON o.OrderID = od.OrderID
INNER JOIN 
    dbo.Products p ON od.ProductID = p.ProductID
GROUP BY 
    CONVERT(DATE, o.OrderDate),
    p.ProductID,
    p.ProductName;

-- 创建唯一聚集索引
CREATE UNIQUE CLUSTERED INDEX IX_vw_DailySales_OrderDate_ProductID ON dbo.vw_DailySales (OrderDate, ProductID);

-- 查询索引视图
SELECT 
    OrderDate,
    ProductName,
    TotalQuantity,
    TotalSales
FROM 
    dbo.vw_DailySales WITH (NOEXPAND)
WHERE 
    OrderDate BETWEEN '2023-01-01' AND '2023-01-31';

生产场景

在数据仓库环境中，索引视图常用于加速频繁执行的聚合查询，如销售日报、月报和季报的生成。

版本差异

SQL Server 2000+：支持索引视图
SQL Server 2016+：支持内存优化表的索引视图

复杂查询优化生产案例

原始查询

sql

SELECT 
    c.CustomerName,
    o.OrderID,
    o.OrderDate,
    SUM(od.Quantity * od.UnitPrice) AS OrderTotal,
    (SELECT SUM(Quantity * UnitPrice) FROM dbo.OrderDetails WHERE OrderID IN (SELECT OrderID FROM dbo.Orders WHERE CustomerID = o.CustomerID AND OrderDate < o.OrderDate)) AS RunningTotal
FROM 
    dbo.Customers c
INNER JOIN 
    dbo.Orders o ON c.CustomerID = o.CustomerID
INNER JOIN 
    dbo.OrderDetails od ON o.OrderID = od.OrderID
WHERE 
    o.OrderDate BETWEEN '2023-01-01' AND '2023-12-31'
GROUP BY 
    c.CustomerName,
    o.OrderID,
    o.OrderDate
ORDER BY 
    c.CustomerName,
    o.OrderDate;

问题分析

该查询使用了相关子查询计算累计总额，执行效率低
相关子查询会对每一行结果重复执行，导致大量的重复计算
执行时间长，资源消耗大

优化后的查询

sql

-- 使用窗口函数替代相关子查询
SELECT 
    c.CustomerName,
    o.OrderID,
    o.OrderDate,
    OrderTotal,
    SUM(OrderTotal) OVER (PARTITION BY c.CustomerID ORDER BY o.OrderDate) - OrderTotal AS RunningTotal
FROM 
    dbo.Customers c
INNER JOIN (
    SELECT 
        OrderID,
        CustomerID,
        OrderDate,
        SUM(Quantity * UnitPrice) AS OrderTotal
    FROM 
        dbo.Orders o
    INNER JOIN 
        dbo.OrderDetails od ON o.OrderID = od.OrderID
    WHERE 
        OrderDate BETWEEN '2023-01-01' AND '2023-12-31'
    GROUP BY 
        OrderID,
        CustomerID,
        OrderDate
) o ON c.CustomerID = o.CustomerID
ORDER BY 
    c.CustomerName,
    o.OrderDate;

优化效果

减少了相关子查询的使用，提高了查询性能
使用窗口函数计算累计总额，更高效
减少了重复计算，降低了 CPU 使用率
执行时间从 5 秒减少到 0.5 秒
资源消耗降低了 90%

生产场景

在财务系统中，计算客户的累计消费总额是一个常见需求，使用窗口函数替代相关子查询可以显著提高查询性能，提升系统响应速度。

常见问题与解决方案

如何识别复杂查询的瓶颈？

分析执行计划：使用 SQL Server Management Studio 或 SET SHOWPLAN_TEXT ON 查看执行计划，识别昂贵的操作符，如全表扫描、书签查找、排序等。
监控查询性能：使用 SQL Server Profiler 或 Extended Events 监控查询的 CPU 使用率、I/O 开销和执行时间。
使用 DMVs：查询 sys.dm_exec_query_stats、sys.dm_exec_requests 和 sys.dm_os_wait_stats 等 DMVs，了解查询的资源消耗和等待类型。
检查索引使用情况：使用 sys.dm_db_index_usage_stats 查看索引的使用情况，识别缺失索引或无用索引。
分析查询存储：如果使用 SQL Server 2016 或更高版本，可以使用查询存储查看查询的性能历史和执行计划变化。

如何优化多表连接查询？

确保连接列上有合适的索引：为所有连接列创建索引，减少连接操作的开销。
优化连接顺序：将过滤最严格的表放在连接顺序的前面，减少后续连接的数据量。
减少连接表的数量：只连接必要的表，避免不必要的表连接。
使用合适的连接类型：根据表的大小和数据分布选择合适的连接类型（NESTED LOOPS、HASH MATCH、MERGE JOIN）。
考虑使用临时表存储中间结果：对于特别复杂的连接查询，可以使用临时表存储部分连接结果，减少单次查询的复杂度。

如何优化子查询？

使用 EXISTS 替代 IN：当子查询结果集较大时，EXISTS 比 IN 更高效，因为 EXISTS 只检查是否存在匹配行，而 IN 需要比较所有行。
使用 INNER JOIN 替代 FROM 子句中的子查询：将子查询转换为 INNER JOIN 可以提高查询性能，特别是当子查询返回大量行时。
避免相关子查询：相关子查询会对外部查询的每一行重复执行，效率很低，尽量将相关子查询转换为非相关子查询或使用其他方式实现。
考虑使用 CTE 替代复杂子查询：CTE 可以使复杂查询更易读，同时在某些情况下可以提高性能。
限制子查询的结果集：在子查询中使用 WHERE 子句限制结果集大小，减少子查询的执行时间。

如何优化聚合查询？

确保 GROUP BY 列上有合适的索引：为 GROUP BY 列创建索引可以加速分组操作。
考虑使用列存储索引：对于大型表的聚合查询，列存储索引可以显著提高性能。
减少 GROUP BY 子句中的列数：只按必要的列分组，减少分组操作的复杂度。
考虑使用分区表：将表按时间或其他维度分区，可以减少聚合查询需要扫描的数据量。
使用索引视图：对于频繁执行的聚合查询，可以创建索引视图预计算聚合结果。

如何优化窗口函数查询？

确保 OVER 子句中的 ORDER BY 列上有合适的索引：为 ORDER BY 列创建索引可以加速窗口函数的排序操作。
减少窗口函数的使用：避免在同一查询中使用过多的窗口函数，特别是在大型表上。
考虑使用临时表或索引视图：对于复杂的窗口函数查询，可以使用临时表或索引视图存储中间结果。
选择合适的窗口函数：根据需求选择合适的窗口函数，如使用 ROW_NUMBER 替代 RANK 或 DENSE_RANK（如果不需要排名功能）。
限制窗口函数的分区大小：将窗口函数的 PARTITION BY 子句限制在较小的范围内，减少窗口函数的计算量。

如何使用查询存储优化复杂查询？

启用查询存储：在数据库级别启用查询存储功能。
监控查询性能：使用查询存储查看查询的执行计划历史、性能变化和资源消耗。
识别回归查询：使用查询存储识别性能退化的查询，找出执行计划变化的原因。
强制使用高效执行计划：对于性能退化的查询，可以使用查询存储强制使用之前的高效执行计划。
分析查询模式：使用查询存储分析查询的执行频率和资源消耗，确定优化优先级。

版本差异：查询存储功能仅适用于 SQL Server 2016 及更高版本。

如何优化递归 CTE？

限制递归深度：在递归 CTE 中使用 OPTION (MAXRECURSION n) 选项限制递归深度，避免无限递归。
优化锚点查询：确保锚点查询返回的行数尽可能少，减少递归的起始数据量。
优化递归条件：确保递归条件高效，避免不必要的递归。
考虑使用临时表：对于特别复杂的递归查询，可以考虑使用临时表和循环替代递归 CTE。
监控递归性能：使用 SQL Server Profiler 或 Extended Events 监控递归 CTE 的性能，识别瓶颈。

如何处理参数嗅探问题？

使用 OPTION (RECOMPILE)：在查询末尾添加 OPTION (RECOMPILE) 选项，强制 SQL Server 为每次执行生成新的执行计划。
使用 OPTION (OPTIMIZE FOR)：使用 OPTION (OPTIMIZE FOR (@parameter = value)) 选项，指定参数的优化值。
使用本地变量：将存储过程的参数赋值给本地变量，然后在查询中使用本地变量，避免参数嗅探。
重新编译存储过程：使用 sp_recompile 存储过程重新编译存储过程，生成新的执行计划。
使用查询提示：根据实际情况使用适当的查询提示，如 OPTION (HASH JOIN) 或 OPTION (LOOP JOIN)。

如何优化跨数据库或跨服务器的复杂查询？

减少跨数据库或跨服务器的数据传输：只查询必要的列和行，减少数据传输量。
使用链接服务器的优化设置：配置链接服务器的查询超时和连接超时设置，优化查询性能。
考虑使用分布式分区视图：对于跨服务器的大型表查询，可以考虑使用分布式分区视图。
复制数据到本地：对于频繁执行的跨数据库或跨服务器查询，可以考虑将数据复制到本地数据库，减少网络延迟。
使用 Azure Synapse Analytics 或其他数据仓库：对于大规模的数据集成和分析，可以考虑使用专门的数据仓库解决方案。

如何优化内存使用和避免内存压力？

配置合适的服务器内存：为 SQL Server 配置合适的最小和最大服务器内存，避免内存争用。
优化查询的内存消耗：减少查询返回的行数和列数，避免大结果集。
使用内存优化表：对于频繁访问的小表，可以考虑使用内存优化表，减少 I/O 开销。
监控内存使用情况：使用 sys.dm_os_process_memory 和 sys.dm_os_memory_clerks 等 DMVs 监控内存使用情况，识别内存消耗大的查询。
考虑使用 Columnstore 索引：对于大型表的分析查询，Columnstore 索引可以显著减少内存使用，提高查询性能。

总结

复杂查询优化是 SQL Server 性能优化的重点和难点，需要综合考虑查询设计、索引优化、执行计划分析和系统配置等多个方面。通过合理的优化策略和最佳实践，可以显著提高复杂查询的性能，降低系统资源消耗，提升用户体验。

在优化复杂查询时，应该：

优先优化最频繁执行的查询，这些查询对系统性能的影响最大
分析执行计划，识别瓶颈，确定优化方向
创建合适的索引，优化查询设计
考虑使用临时表、表变量或索引视图存储中间结果
测试优化效果，确保不会引入新的问题
监控查询性能，持续优化

通过本文介绍的复杂查询优化方法、最佳实践和实际案例，DBA 和开发人员可以有效优化 SQL Server 复杂查询，提高数据库性能和可用性，为业务提供高效、可靠的数据支持。

SQLServer 复杂查询优化 ​

复杂查询优化概述 ​

生产场景 ​

版本差异 ​

复杂查询的类型 ​

多表连接查询 ​

示例 ​

生产场景 ​

子查询 ​

示例 ​

生产场景 ​

公共表表达式 (CTE) ​

示例 ​

生产场景 ​

版本差异 ​

聚合查询 ​

示例 ​

生产场景 ​

窗口函数查询 ​

示例 ​

生产场景 ​

版本差异 ​

复杂查询优化方法 ​

优化查询设计 ​

简化查询 ​

示例 ​

生产场景 ​

使用合适的连接类型 ​

优化子查询 ​

示例 ​

生产场景 ​

优化索引 ​

创建合适的索引 ​

示例 ​

生产场景 ​

版本差异 ​

优化执行计划 ​

分析执行计划 ​

常见执行计划问题 ​

示例 ​

生产场景 ​

优化系统配置 ​

配置最大并行度 (MAXDOP) ​

配置查询超时 ​

优化内存配置 ​

示例 ​

生产场景 ​

版本差异 ​

复杂查询优化最佳实践 ​

优先优化最频繁执行的查询 ​

示例 ​

生产场景 ​

测试优化效果 ​

生产场景 ​

考虑使用临时表或表变量 ​

示例 ​

生产场景 ​

考虑使用索引视图 ​

示例 ​

生产场景 ​

版本差异 ​

复杂查询优化生产案例 ​

原始查询 ​

问题分析 ​

优化后的查询 ​

优化效果 ​

生产场景 ​

常见问题与解决方案 ​

如何识别复杂查询的瓶颈？ ​

如何优化多表连接查询？ ​

如何优化子查询？ ​

如何优化聚合查询？ ​

如何优化窗口函数查询？ ​

如何使用查询存储优化复杂查询？ ​

如何优化递归 CTE？ ​

如何处理参数嗅探问题？ ​

如何优化跨数据库或跨服务器的复杂查询？ ​

如何优化内存使用和避免内存压力？ ​

总结 ​

SQLServer 复杂查询优化

复杂查询优化概述

生产场景

版本差异

复杂查询的类型

多表连接查询

示例

生产场景

子查询

示例

生产场景

公共表表达式 (CTE)

示例

生产场景

版本差异

聚合查询

示例

生产场景

窗口函数查询

示例

生产场景

版本差异

复杂查询优化方法

优化查询设计

简化查询

示例

生产场景

使用合适的连接类型

优化子查询

示例

生产场景

优化索引

创建合适的索引

示例

生产场景

版本差异

优化执行计划

分析执行计划

常见执行计划问题

示例

生产场景

优化系统配置

配置最大并行度 (MAXDOP)

配置查询超时

优化内存配置

示例

生产场景

版本差异

复杂查询优化最佳实践

优先优化最频繁执行的查询

示例

生产场景

测试优化效果

生产场景

考虑使用临时表或表变量

示例

生产场景

考虑使用索引视图

示例

生产场景

版本差异

复杂查询优化生产案例

原始查询

问题分析

优化后的查询

优化效果

生产场景

常见问题与解决方案

如何识别复杂查询的瓶颈？

如何优化多表连接查询？

如何优化子查询？

如何优化聚合查询？

如何优化窗口函数查询？

如何使用查询存储优化复杂查询？

如何优化递归 CTE？

如何处理参数嗅探问题？

如何优化跨数据库或跨服务器的复杂查询？

如何优化内存使用和避免内存压力？

总结