SQL Server 索引知识汇总(最新整理)_MsSqlserver

摘要

本文对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;

到此这篇关于sql server 索引知识汇总的文章就介绍到这了,更多相关sql server 索引内容请搜索代码网以前的文章或继续浏览下面的相关文章希望大家以后多多支持代码网！