SQL Server 索引知识汇总摘要本文对SQLServer中的索引进行一个知识总结。一、堆表Heap未创建聚集索引的表称为堆表数据无序追加无统一排序。优势写入极快适合日志、流水表等持续大批量写入场景缺点无索引时查询必须全表扫描数据量越大成本越高-- 创建堆表无聚集索引CREATETABLETestData(TestIdinteger,TestNamevarchar(255),TestDatedate,TestTypeinteger,TestData1integer,TestData2varchar(100),TestData3 XML,TestData4varbinary(max),TestData4_FileTypevarchar(3));ALTERTABLETestData REBUILD;-- 重建堆表DROPTABLETestData;-- 删除表二、聚集索引Clustered IndexB树结构索引键与完整行数据存储在叶子节点。一张表最多1个聚集索引创建后不再为堆表。优势WHERE条件含索引键时直接定位整行无需回表ORDER BY与索引键一致时省去排序开销缺点DML维护成本高——键值更新会触发页拆分/迁移INSERT/DELETE也有额外开销CREATECLUSTEREDINDEXIX_TestData_TestIdONdbo.TestData(TestId);ALTERINDEXIX_TestData_TestIdONTestData REBUILDWITH(ONLINEON);-- 在线重建DROPINDEXIX_TestData_TestIdONTestDataWITH(ONLINEON);-- 在线删除三、非聚集索引Non-Clustered IndexB树结构叶子节点不存完整行仅存行定位指针指向聚集索引键或堆表的RID。优势可建多个索引适配不同查询DML维护开销低于聚集索引缺点增删改仍需维护所有非聚集索引索引越多写入越慢需权衡查询与写入性能CREATEINDEXIX_TestData_TestDateONdbo.TestData(TestDate);ALTERINDEXIX_TestData_TestDateONTestData REBUILDWITH(ONLINEON);DROPINDEXIX_TestData_TestDateONTestData;四、列存储索引Column Store Index按列存储的特殊索引分为聚集列存储与非聚集列存储两种。优势专为DW/大宽表/海量事实表设计聚合分析性能提升最高100倍高压缩算法存储占用最高减少90%缺点不支持 varchar(max)/nvarchar(max)/XML/text/image/CLR 类型开启复制/CDC的表无法使用DML写入开销远高于行式索引-- 创建聚集列存储索引CREATECLUSTEREDCOLUMNSTOREINDEXCIX_TestData_TestTypeONdbo.TestData(TestType)WITH(DATA_COMPRESSIONCOLUMNSTORE);DROPINDEXCIX_TestData_TestType;五、XML 索引专用于 XML 类型字段分为主XML索引和二级XML索引PATH/VALUE/PROPERTY前置要求表必须有主键聚集索引。优势避免每次查询加载解析完整XML文档适合大XML字段局部读取缺点磁盘占用极高XML每个标签生成多条索引记录XML更新时同步维护带来写入损耗CREATEPRIMARYXMLINDEXPXML_TestData_TestData3ONTestData(TestData3);CREATEXMLINDEXXMLPATH_TestData_TestData3ONTestData(TestData3)USINGXMLINDEXPXML_TestData_TestData3FORPATH;CREATEXMLINDEXXMLPROPERTY_TestData_TestData3ONTestData(TestData3)USINGXMLINDEXPXML_TestData_TestData3FORPROPERTY;CREATEXMLINDEXXMLVALUE_TestData_TestData3ONTestData(TestData3)USINGXMLINDEXPXML_TestData_TestData3FORVALUE;六、全文索引Full-Text Index将文本拆分为分词Token构建索引索引文件独立存放于全文目录不混存于数据文件。优势支持大文本/二进制字段检索char/varchar/nvarchar/text/XML/varbinary(max)/FILESTREAM支持精确短语、前缀模糊、变形检索、邻近词、同义词、加权权重等高级检索缺点全文检索由独立 MSFTESQL 服务执行会与 SQL Server 争抢内存/IO 资源CREATEFULLTEXT CATALOG fulltextCatalogASDEFAULT;CREATEFULLTEXTINDEXONdbo.TestData(TestData4TYPECOLUMNTestData4_FileType)KEYINDEXPK_TestDataWITHSTOPLISTSYSTEM;ALTERFULLTEXT CATALOG fulltextCatalog REBUILD;DROPFULLTEXTINDEXONdbo.TestData;七、索引衍生变体1. 包含列索引Included Columns非聚集索引扩展将指定字段存入叶子节点实现类聚集索引效果免去回表。支持 text/ntext/image 外的绝大多数类型。CREATENONCLUSTEREDINDEXIX_TestData_TestDate_incTestData3ONTestData(TestDate)INCLUDE(TestData3);2. 函数索引基于计算列SQL Server 不直接支持函数索引通过持久化计算列模拟实现。ALTERTABLETestDataADDTestDatePlus7DaysASDATEADD(DAY,7,TestDate)PERSISTED;CREATENONCLUSTEREDINDEXIX_TestData_TestDate_Plus7DaysONTestData(TestDatePlus7Days);3. 筛选索引Filtered Index带 WHERE 条件的非聚集索引缩小索引体积、降低维护成本。仅当查询条件与索引 WHERE 完全匹配时优化器才会选用。CREATEINDEXIX_TestData_TestDate_TestTypeEq1ONTestData(TestDate)WHERETestType1;4. 覆盖索引Covering Index设计思路查询所有字段要么是索引键要么在 INCLUDE 中完全消除回表性能最优。CREATEINDEXIX_TestData_TestDate_TestType_AllDataONTestData(TestDate,TestType)INCLUDE(TestData1,TestData2,TestData3,TestData4);-- 该查询完全走索引无需访问原表SELECTTestData1,TestData2,TestData3,TestData4FROMTestDataWHERETestDateCURRENT_TIMESTAMP-1ANDTestType1;