一、什么是复合索引复合索引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 走索引 SeekC 在结果集上过滤 -- ❌ 无法使用索引查找 WHERE B 2 AND C 3 -- 因为没有指定 A索引无法定位起始位置 -- ❌ 无法使用索引查找 WHERE C 32.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 列顺序决策矩阵查询模式推荐列顺序示例全等值高选择性 → 低选择性(CustomerID, Status)等值 范围等值列在前范围列在后(CustomerID, OrderDate)多范围无法完美考虑覆盖索引或拆查询—四、覆盖索引——避免回表的艺术4.1 什么是回表Key Lookup当非聚集索引不包含查询所需的所有列时SQL Server 需要在非聚集索引中找到匹配行拿着指针聚集键或 RID回到聚集索引或堆表中查找剩余列这就是 Key Lookup书签查找代价很高-- 索引只有 CustomerID CREATE INDEX IX_CustomerID ON Orders(CustomerID); -- 查询需要 Amount 列索引中没有 SELECT CustomerID, OrderDate, Amount FROM Orders WHERE CustomerID 100;执行计划Index Seek Key Lookup4.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 普通索引列特性普通索引列INCLUDE 列参与排序✅ 影响索引顺序❌ 不参与排序可用于 WHERE✅❌可用于范围查询✅❌索引大小较大较小不排序最佳实践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 避免回表大幅提升性能设计原则每张表的索引数控制在 5-10 个复合索引最多 5-6 列冗余检查定期清理前缀重叠的冗余索引一句话记住本期内容复合索引的列顺序决定生死——等值在前、范围在后、高选择性在前、低选择性在后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