SQL Server 索引知识汇总

SQL Server 索引知识汇总

摘要

本文对SQLServer中的索引进行一个知识总结。

一、堆表（Heap）

未创建聚集索引的表称为堆表，数据无序追加，无统一排序。

• 优势：写入极快，适合日志、流水表等持续大批量写入场景
• 缺点：无索引时查询必须全表扫描，数据量越大成本越高

-- 创建堆表（无聚集索引）
CREATE TABLE TestData (
    TestId integer, TestName varchar(255), TestDate date,
    TestType integer, TestData1 integer, TestData2 varchar(100),
    TestData3 XML, TestData4 varbinary(max), TestData4_FileType varchar(3)
);

ALTER TABLE TestData REBUILD;   -- 重建堆表
DROP TABLE TestData;            -- 删除表

二、聚集索引（Clustered Index）

B+树结构，索引键与完整行数据存储在叶子节点。一张表最多1个聚集索引，创建后不再为堆表。

• 优势：WHERE条件含索引键时直接定位整行，无需回表；ORDER BY与索引键一致时省去排序开销
• 缺点：DML维护成本高——键值更新会触发页拆分/迁移，INSERT/DELETE也有额外开销

CREATE CLUSTERED INDEX IX_TestData_TestId ON dbo.TestData (TestId);
ALTER INDEX IX_TestData_TestId ON TestData REBUILD WITH (ONLINE = ON);   -- 在线重建
DROP INDEX IX_TestData_TestId ON TestData WITH (ONLINE = ON);           -- 在线删除

三、非聚集索引（Non-Clustered Index）

B+树结构，叶子节点不存完整行，仅存行定位指针（指向聚集索引键或堆表的RID）。

• 优势：可建多个索引适配不同查询；DML维护开销低于聚集索引
• 缺点：增删改仍需维护所有非聚集索引；索引越多写入越慢，需权衡查询与写入性能

CREATE INDEX IX_TestData_TestDate ON dbo.TestData (TestDate);
ALTER INDEX IX_TestData_TestDate ON TestData REBUILD WITH (ONLINE = ON);
DROP INDEX IX_TestData_TestDate ON TestData;

四、列存储索引（Column Store Index）

按列存储的特殊索引，分为聚集列存储与非聚集列存储两种。

• 优势：专为DW/大宽表/海量事实表设计，聚合分析性能提升最高100倍，高压缩算法存储占用最高减少90%
• 缺点：不支持 varchar(max)/nvarchar(max)/XML/text/image/CLR 类型；开启复制/CDC的表无法使用；DML写入开销远高于行式索引

-- 创建聚集列存储索引
CREATE CLUSTERED COLUMNSTORE INDEX CIX_TestData_TestType ON dbo.TestData (TestType)
    WITH (DATA_COMPRESSION = COLUMNSTORE);

DROP INDEX CIX_TestData_TestType;

五、XML 索引

专用于 XML 类型字段，分为主XML索引和二级XML索引（PATH/VALUE/PROPERTY），前置要求：表必须有主键聚集索引。

• 优势：避免每次查询加载解析完整XML文档，适合大XML字段局部读取
• 缺点：磁盘占用极高（XML每个标签生成多条索引记录）；XML更新时同步维护带来写入损耗

CREATE PRIMARY XML INDEX PXML_TestData_TestData3 ON TestData (TestData3);

CREATE XML INDEX XMLPATH_TestData_TestData3 ON TestData (TestData3)
    USING XML INDEX PXML_TestData_TestData3 FOR PATH;

CREATE XML INDEX XMLPROPERTY_TestData_TestData3 ON TestData (TestData3)
    USING XML INDEX PXML_TestData_TestData3 FOR PROPERTY;

CREATE XML INDEX XMLVALUE_TestData_TestData3 ON TestData (TestData3)
    USING XML INDEX PXML_TestData_TestData3 FOR VALUE;

六、全文索引（Full-Text Index）

将文本拆分为分词（Token）构建索引，索引文件独立存放于全文目录，不混存于数据文件。

• 优势：支持大文本/二进制字段检索（char/varchar/nvarchar/text/XML/varbinary(max)/FILESTREAM）；支持精确短语、前缀模糊、变形检索、邻近词、同义词、加权权重等高级检索
• 缺点：全文检索由独立 MSFTESQL 服务执行，会与 SQL Server 争抢内存/IO 资源

CREATE FULLTEXT CATALOG fulltextCatalog AS DEFAULT;

CREATE FULLTEXT INDEX ON dbo.TestData (TestData4 TYPE COLUMN TestData4_FileType)
    KEY INDEX PK_TestData WITH STOPLIST = SYSTEM;

ALTER FULLTEXT CATALOG fulltextCatalog REBUILD;
DROP FULLTEXT INDEX ON dbo.TestData;

七、索引衍生变体

1. 包含列索引（Included Columns）

非聚集索引扩展：将指定字段存入叶子节点，实现"类聚集索引"效果，免去回表。支持 text/ntext/image 外的绝大多数类型。

CREATE NONCLUSTERED INDEX IX_TestData_TestDate_incTestData3 ON TestData (TestDate)
    INCLUDE (TestData3);

2. 函数索引（基于计算列）

SQL Server 不直接支持函数索引，通过持久化计算列模拟实现。

ALTER TABLE TestData ADD TestDatePlus7Days AS DATEADD(DAY, 7, TestDate) PERSISTED;
CREATE NONCLUSTERED INDEX IX_TestData_TestDate_Plus7Days ON TestData (TestDatePlus7Days);

3. 筛选索引（Filtered Index）

带 WHERE 条件的非聚集索引，缩小索引体积、降低维护成本。仅当查询条件与索引 WHERE 完全匹配时优化器才会选用。

CREATE INDEX IX_TestData_TestDate_TestTypeEq1 ON TestData (TestDate) WHERE TestType = 1;

4. 覆盖索引（Covering Index）

设计思路：查询所有字段要么是索引键，要么在 INCLUDE 中，完全消除回表，性能最优。

CREATE INDEX IX_TestData_TestDate_TestType_AllData ON TestData (TestDate, TestType)
    INCLUDE (TestData1, TestData2, TestData3, TestData4);

-- 该查询完全走索引，无需访问原表
SELECT TestData1, TestData2, TestData3, TestData4
FROM TestData
WHERE TestDate > CURRENT_TIMESTAMP - 1 AND TestType = 1;