SQL Server 性能优化实战（第二期）：复合索引与列顺序的奥秘

SQL Server 性能优化实战（第二期）：复合索引与列顺序的奥秘

上一期我们讲解了索引的基本原理。但实际开发中，查询条件往往不止一个字段。这时，复合索引就成了必备武器。然而，很多人在创建复合索引时，只是凭感觉排列列顺序——你可能不知道，顺序错了，索引效果可能天差地别。这一期，我们深入复合索引的核心逻辑。

一、什么是复合索引？

复合索引（Composite Index）是指在一个索引中包含多个列的索引。

-- 一个包含三列的复合索引
CREATE NONCLUSTERED INDEX IX_Orders_CustomerDate 
ON Orders(CustomerID, OrderDate, Status);

复合索引的 B-Tree 结构

复合索引的排序规则是：先按第一列排序，第一列相同时按第二列排序，以此类推。

想象一个电话本：

• 先按姓氏排序
• 姓氏相同时按名字排序

这就是一个 (LastName, FirstName) 的复合索引。

二、最左前缀原则——复合索引的灵魂

最左前缀原则（Leftmost Prefix Principle）：复合索引只从最左边的列开始匹配，跳过第一列就无法使用索引。

2.1 索引 (A, B, C) 能支持哪些查询？

-- ✅ 可以使用索引（完整匹配）
WHERE A = 1 AND B = 2 AND C = 3

-- ✅ 可以使用索引（A 和 B）
WHERE A = 1 AND B = 2

-- ✅ 可以使用索引（仅 A）
WHERE A = 1

-- ✅ 可以使用索引（A 和 C，但只用 A 做筛选，C 作为过滤）
WHERE A = 1 AND C = 3
-- 实际效果：A 走索引 Seek，C 在结果集上过滤

-- ❌ 无法使用索引查找
WHERE B = 2 AND C = 3
-- 因为没有指定 A，索引无法定位起始位置

-- ❌ 无法使用索引查找
WHERE C = 3

2.2 实战验证

-- 创建测试表
DROP TABLE IF EXISTS TestIndex;
CREATE TABLE TestIndex (
    ID INT IDENTITY(1,1),
    ColA INT,
    ColB INT,
    ColC INT,
    SomeData VARCHAR(100)
);

-- 插入10万条测试数据
WITH Numbers AS (
    SELECT TOP 100000 ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS n
    FROM sys.all_columns a CROSS JOIN sys.all_columns b
)
INSERT INTO TestIndex (ColA, ColB, ColC, SomeData)
SELECT 
    n % 1000,
    n % 1000,
    n % 1000,
    'Data_' + CAST(n AS VARCHAR)
FROM Numbers;

-- 创建复合索引
CREATE NONCLUSTERED INDEX IX_Test_ABC ON TestIndex(ColA, ColB, ColC);
GO

-- 开启执行计划显示
SET STATISTICS TIME ON;
SET STATISTICS IO ON;

测试1：使用第一列

SELECT * FROM TestIndex WHERE ColA = 500;

执行计划：Index Seek ✅ 使用索引

测试2：使用第一、二列

SELECT * FROM TestIndex WHERE ColA = 500 AND ColB = 300;

执行计划：Index Seek ✅ 使用索引（更精确）

测试3：跳过第一列

SELECT * FROM TestIndex WHERE ColB = 300 AND ColC = 200;

执行计划：Index Scan ❌ 无法使用 Seek

三、列顺序的选择策略

3.1 等值查询的列顺序

对于全是 = 条件的查询，原则是：高选择性（区分度高）的列放在前面。

-- 假设数据分布：
-- Status: 只有 3 种值（'Pending', 'Approved', 'Rejected'）→ 选择性低
-- CustomerID: 10万个不同值 → 选择性高

-- ❌ 低效的列顺序
CREATE INDEX IX_Status_Customer ON Orders(Status, CustomerID);

-- ✅ 高效的列顺序
CREATE INDEX IX_Customer_Status ON Orders(CustomerID, Status);

原因：高选择性的列放在前面，能快速将数据范围缩小到很少的行。

3.2 范围查询的列顺序

当查询包含范围条件（>、<、BETWEEN、LIKE）时，范围查询的列应该放在最后。

-- 查询：客户ID = 100 且 订单日期在 2024年1月内
WHERE CustomerID = 100 AND OrderDate BETWEEN '2024-01-01' AND '2024-01-31'

-- ✅ 正确的列顺序
CREATE INDEX IX_Customer_Date ON Orders(CustomerID, OrderDate);
-- CustomerID（等值）在前，OrderDate（范围）在后

-- ❌ 错误的列顺序
CREATE INDEX IX_Date_Customer ON Orders(OrderDate, CustomerID);
-- 一旦 OrderDate 使用范围查询，CustomerID 就无法利用索引过滤了

3.3 列顺序决策矩阵

四、覆盖索引——避免回表的艺术

4.1 什么是回表（Key Lookup）？

当非聚集索引不包含查询所需的所有列时，SQL Server 需要：

1. 在非聚集索引中找到匹配行
2. 拿着指针（聚集键或 RID）回到聚集索引或堆表中查找剩余列

这就是 Key Lookup（书签查找），代价很高！

-- 索引只有 CustomerID
CREATE INDEX IX_CustomerID ON Orders(CustomerID);

-- 查询需要 Amount 列，索引中没有
SELECT CustomerID, OrderDate, Amount 
FROM Orders 
WHERE CustomerID = 100;

执行计划：Index Seek + Key Lookup

4.2 覆盖索引

在索引的叶子节点中包含查询需要的所有列，避免回表。

-- 覆盖索引：包含查询所需的所有列
CREATE INDEX IX_CustomerID_Cover 
ON Orders(CustomerID) 
INCLUDE (OrderDate, Amount);
-- 注意：INCLUDE 中的列不参与索引排序，只存储在叶子节点

现在同样的查询：

SELECT CustomerID, OrderDate, Amount 
FROM Orders 
WHERE CustomerID = 100;

执行计划：Index Seek（无 Key Lookup）✅

4.3 INCLUDE vs 普通索引列

最佳实践：

• WHERE / JOIN / ORDER BY 中的列 → 放在普通索引列
• SELECT 中需要但不用于过滤的列 → 放在 INCLUDE

五、复合索引的设计实战

场景：订单查询页面

用户可以在页面上组合筛选：

• 客户ID（精确，必填）
• 订单状态（精确，可选）
• 下单日期范围（可选）
• 支付金额（可选）

页面查询示例：

SELECT OrderID, CustomerID, OrderDate, Status, Amount
FROM Orders
WHERE CustomerID = @CustomerID
  AND Status = @Status                    -- 可选
  AND OrderDate BETWEEN @StartDate AND @EndDate  -- 可选

方案分析

候选索引1：(CustomerID, Status, OrderDate)

• CustomerID 等值 → ✅
• Status 等值 → ✅（在 CustomerID 过滤后的结果上）
• OrderDate 范围 → ✅（放在最后）

候选索引2：(CustomerID, OrderDate) INCLUDE (Status)

• 如果 Status 查询频率很低，可以省略
• INCLUDE 添加 Status 用于 SELECT

推荐：

CREATE INDEX IX_Orders_Query 
ON Orders(CustomerID, Status, OrderDate) 
INCLUDE (Amount);

六、复合索引的潜在陷阱

❌ 陷阱1：索引列顺序错误导致无法使用

-- 索引 (A, B)
-- 查询 WHERE B = 1 → 无法使用索引

❌ 陷阱2：索引包含太多列

-- 索引 (A, B, C, D, E, F, G)
-- 更新成本高，空间占用大
-- 每多一列，索引树更宽更深

建议：复合索引最多 5-6 列，超过考虑拆分或使用列存储索引。

❌ 陷阱3：重复索引

CREATE INDEX IX_1 ON Orders(CustomerID);
CREATE INDEX IX_2 ON Orders(CustomerID, OrderDate);
CREATE INDEX IX_3 ON Orders(CustomerID, OrderDate, Status);

IX_1 和 IX_2 在功能上被 IX_3 覆盖，可以删除前两个。

七、快速诊断：你的复合索引合理吗？

7.1 查找可能冗余的复合索引

SELECT 
    OBJECT_NAME(i1.object_id) AS TableName,
    i1.name AS Index1,
    i2.name AS Index2,
    LEFT(i1.index_columns, 200) AS Index1_Columns,
    LEFT(i2.index_columns, 200) AS Index2_Columns
FROM (
    SELECT i.object_id, i.index_id, i.name,
        STUFF((SELECT ',' + c.name
               FROM sys.index_columns ic
               JOIN sys.columns c ON ic.object_id = c.object_id AND ic.column_id = c.column_id
               WHERE ic.object_id = i.object_id AND ic.index_id = i.index_id
                 AND ic.is_included_column = 0
               ORDER BY ic.key_ordinal
               FOR XML PATH('')), 1, 1, '') AS index_columns
    FROM sys.indexes i
    WHERE i.type > 0
) i1
JOIN (
    SELECT i.object_id, i.index_id, i.name,
        STUFF((SELECT ',' + c.name
               FROM sys.index_columns ic
               JOIN sys.columns c ON ic.object_id = c.object_id AND ic.column_id = c.column_id
               WHERE ic.object_id = i.object_id AND ic.index_id = i.index_id
                 AND ic.is_included_column = 0
               ORDER BY ic.key_ordinal
               FOR XML PATH('')), 1, 1, '') AS index_columns
    FROM sys.indexes i
    WHERE i.type > 0
) i2 ON i1.object_id = i2.object_id AND i1.index_id < i2.index_id
WHERE i2.index_columns LIKE i1.index_columns + '%'
ORDER BY TableName, Index1;

7.2 查找缺失的复合索引建议

SELECT 
    mid.equality_columns,
    mid.inequality_columns,
    mid.included_columns,
    migs.user_seeks,
    migs.avg_total_user_cost,
    migs.avg_user_impact,
    migs.user_seeks * migs.avg_total_user_cost * migs.avg_user_impact / 100.0 AS ImpactScore
FROM sys.dm_db_missing_index_details mid
JOIN sys.dm_db_missing_index_groups mig ON mid.index_handle = mig.index_handle
JOIN sys.dm_db_missing_index_group_stats migs ON mig.group_handle = migs.group_handle
WHERE mid.database_id = DB_ID()
ORDER BY ImpactScore DESC;

注意 equality_columns（等值条件列）和 inequality_columns（范围条件列）是分开显示的，这就是 SQL Server 推荐的列顺序。

八、核心总结

一句话记住本期内容：

复合索引的列顺序决定生死——等值在前、范围在后、高选择性在前、低选择性在后，INCLUDE 帮你远离回表之痛。

动手练习

-- 现有查询：
SELECT OrderID, CustomerID, OrderDate, Status, Amount
FROM Orders
WHERE CustomerID = @CustID
  AND OrderDate >= @StartDate
  AND OrderDate < @EndDate
ORDER BY OrderDate DESC;

-- 问题：
-- 1. 应该创建什么索引？
-- 2. 列顺序如何安排？
-- 3. 哪些列放在 INCLUDE？

-- 参考答案
-- CustomerID 等值 → 第一列
-- OrderDate 范围 + ORDER BY → 第二列（注意范围查询必须在等值之后）
-- 其他 SELECT 列放入 INCLUDE

CREATE INDEX IX_Orders_CustDate 
ON Orders(CustomerID, OrderDate) 
INCLUDE (Status, Amount);

下一期预告

执行计划深度解析——让 SQL Server 告诉你慢在哪里

• 如何读懂执行计划中的每一个图标
• 常见的性能杀手算子（Sort、Spool、Hash Join 等）
• 预估行 vs 实际行：为什么统计信息如此重要
• 实战：从一个慢查询到执行计划到索引优化

📌 本文代码已在 SQL Server 2019+ 验证。如有疑问或想深入讨论某个案例，欢迎留言。

本系列持续更新中，点击关注不错过下一期。