通过建立索引,能加快数据的访问速度。
每个表只能有一个聚集索引,可以考虑列有:用来排序大型结果集的列,用在聚合函数中的列,包含完整唯一值的列;而频繁更新的列,非唯一的列,非常多列(多个列的组合),非常宽的列则不适合建聚集索引。
在选择非聚集索引的列时,一般是那些在where,join,order by等子句中频繁引用的那些列,以及搜索返回较小结果集的高选择性列(少于表中所有行的20%)。
此外,还有一些限制:
A、每个表最多可以有249个非聚集索引;
B、索引键列最多16个,总长不超过900字节,但include中可以包含1023个非键列;
CREATE TABLE t(v1 CHAR(300),
v2 CHAR(300),
v3 CHAR(300),
v4 CHAR(300),
v5 CHAR(200))
CREATE INDEX idx_tt ON t(v1,v2,v3,v4)
上面语句在建立索引时会报错:未创建索引 'idx_tt'。此索引有一个键的长度至少为 1200 字节。允许的最大键长度为 900 字节。 Severity 16。
C、在索引键中不能包含大数据类型,但可以把新的max型类型列加入到include中。
除了上面的限制之外,SQL Server中有主键约束、唯一约束、外键约束、默认值约束、Check约束,在建立索引时要特别注意的是:
A、在定义表时如果指定了主键(主键约束),那么系统后自动建一个聚集索引
B、在定义表时如果指定了唯一约束,那么系统会自动建立一个唯一的非聚集索引。
C、主键+聚集索引,主键+非聚集索引,唯一约束+聚集索引,唯一约束+非聚集索引,这几种组合都是可以的。注意:主键约束和唯一约束的区别是唯一约束中的列值可以包含一个NULL。
前者是约束,后者是索引方式,两者没有必然的联系,不是说主键就一定要用聚集索引,其实主键也可以用非聚集索引,因为前者只是强调每个值都要不同且不能为NULL,而后者则是影响了数据的存储方式,通过定义聚集索引,数据按照一个确定的方式存储在磁盘上,数据页通过双向链表串连起来,每个指针都指向一个确定的数据页,这和堆表不同,堆表的数据页不会通过指针前后串联起来,有点像二叉树,从根开始指向下一级,一级一级指下去,页与页之间没有联系。
--1.1建表,定义主键会自动建立聚集索引
CREATE TABLE tt(vid int primary key,
v1 char(100),
v2 varchar(100))
--1.2
create table t2(vid int NOT NULL, --这里必须是not null
v1 char(100),
v2 varchar(100),
v3 int not null)
--1.3通过增加主键约束建立聚集索引,同时指定索引选项
alter table t2
add constraint pk_vid
primary key CLUSTERED (vid) --这里的CLUSTERED换成NONCLUSTERED也是可以的
WITH (fillfactor = 85, --填充因子
PAD_INDEX = ON, --中间索引页是否也要考虑填充因子
MAXDOP = 0, --创建索引时能同时使用多少cpu,0表示没有限制
ONLINE = ON, --创建索引过程中,允许用户访问表
SORT_IN_TEMPDB = ON, --创建索引过程中产生的中间的索引结果,存放在tempdb数据库
ALLOW_PAGE_LOCKS = OFF, --索引上的锁定粒度为行、表
ALLOW_ROW_LOCKS = OFF) --索引上的锁定粒度为页、表
--1.4通过增加唯一约束,自动建立唯一的非聚集索引
alter table t2
add constraint uni_v2
unique (v2)
--1.5建立唯一的非聚集索引
create UNIQUE NONCLUSTERED index uni_v1 on t2(v2)
--1.6建立非聚集索引
create NONCLUSTERED index idx_v1_v2 on t2(v1,v2)
--1.7降序排列索引,同时在include中不能指定排列顺序
create NONCLUSTERED index idx_v1_include on t2(v1 desc) include(v2,v3)
--2.1禁用非聚集索引
alter index idx_v1_include on t2 disable
--2.2被禁用的非聚集索引的定义还保留在系统表中
select * from sys.indexes where object_id = object_id('t2')
--2.3被禁用的非聚集索引数据已经从数据库中删除
select * from sys.partitions where object_id = object_id('t2')
--3.1禁用聚集索引,同时会禁用其他所有的非聚集索引
alter index pk_vid on t2 disable
--3.2查询处理器无法生成计划,因为表或视图 't2' 的索引 'pk_vid' 被禁用。
select * from t2
--4.1重新启用:被禁用的聚集索引
alter index pk_vid on t2
rebuild
--4.2启用被禁用的聚集索引(聚集索引必须先启用)
create nonclustered index idx_v1_include on t2(v1 desc) include(v2,v3) with (DROP_EXISTING = ON)
--4.3启用被禁用的聚集索引的另一种方法(聚集索引必须先启用)
alter index idx_v1_include on t2
rebuild
--4.4启用所有被禁用的索引,包括聚集索引、非聚集索引
alter index all on t2
rebuild
--5.1通过drop_existing改变索引的定义(列,索引选项),也可以先drop,再create
create nonclustered index idx_v1_include on t2(v1 asc) include(v2)
with (DROP_EXISTING = ON,FILLFACTOR = 80)
--5.2删除索引
drop index idx_v1_include on t2
--6.1创建索引时指定文件组
create nonclustered index idx_vvv on t2(v1,v2)
on [wc_fg5]
--6.2索引分区
CREATE TABLE [dbo].[wcTX](
[wcId] [bigint] NOT NULL,
[wcV] [varchar](100) NOT NULL,
[wcDate] [datetime] NOT NULL)
create nonclustered index idx_partiton_wcv on wcTX(wcV)
on wcLeftRangeScheme(wcDate)
--6.3筛选索引:为行的子集建立索引
create nonclustered idx_wc on t2(v1)
where v3 >= 100 and v3 <=180 --筛选谓词:IN,IS,IS NOT,=,<>,>,<,>=,<=
索引压缩
--创建分区索引,同时指定指定页级压缩
create nonclustered index idx_partition_compression on wcT(wcV)
with (DATA_COMPRESSION = PAGE on PARTITIONS (2),
DATA_COMPRESSION = ROW ON PARTITIONS (1,3,4 TO 5))
on wcLeftRangeScheme(wcDate) --在最后再指定分区架构,否则会报错
--通过alter index语句来指定每个分区采用的压缩级别
--不管是在with中指定所有分区还是部分分区,都必须要写PARTITION = ALL,否则会报错
alter index idx_partition_compression on wcT
rebuild PARTITION = ALL
with (DATA_COMPRESSION = ROW ON PARTITIONS (1),
DATA_COMPRESSION = PAGE ON PARTITIONS (2,3,4),
DATA_compression = none on partitions (5))
--对指定分区应用压缩级别
alter index idx_partition_compression on wcT
rebuild partition = 3
WITH (DATA_COMPRESSION = PAGE)
修改索引选项:指定不重新生成或重新组织索引的索引选项。不能为已禁用的索引指定SET。
/*===========================================
ALTER INDEX语句中SET可以设置的选项很有限
<set_index_option>::=
{
ALLOW_ROW_LOCKS = { ON | OFF }
| ALLOW_PAGE_LOCKS = { ON | OFF }
| IGNORE_DUP_KEY = { ON | OFF }
| STATISTICS_NORECOMPUTE = { ON | OFF }
}
=============================================*/
ALTER INDEX idx_vv ON dbo.tt
SET
(
STATISTICS_NORECOMPUTE = ON,
ALLOW_PAGE_LOCKS = ON
) ;
索引元数据
select
t.name ,
t.type_desc,
--是否由sql server内部组件创建的
t.is_ms_shipped,
--对象是否要发布
t.is_published,
--对象架构是否发布
t.is_schema_published,
--FILESTREAM文件组的空间id
t.filestream_data_space_id,
--text,ntext,image数据保存的空间id
t.lob_data_space_id,
--曾经使用的最大列id
t.max_column_id_used,
--禁用时大容量更新获取行锁,启用时会获取大容量更新锁
t.lock_on_bulk_load,
t.uses_ansi_nulls,
--是否使用快照复制或事务复制发布表
t.is_replicated,
--表有复制过滤器
t.has_replication_filter,
--使用合并复制发布表
t.is_merge_published,
--使用同步更新订阅来订阅表
t.is_sync_tran_subscribed,
--表包含的持久化数据,依赖上次ALTER ASSEMBLY期间其定义发生更改的程序集
t.has_unchecked_assembly_data,
--行中最大允许字节数,可以允许直接在行中存储LOB数据
t.text_in_row_limit,
--是否在行外存储varchar(max),nvarchar(max),varbinary(max),xml,UDT类型的数据
--为1时只在行内存储一个16字节的指针指向根目录
--为0时直接存储在行中,如果容纳不下,那么存储在行外
t.large_value_types_out_of_row,
--表是否启用了变更数据捕获
t.is_tracked_by_cdc,
--锁升级
t.lock_escalation_desc,
i.name,
i.type_desc, --索引类型
i.is_unique, --是否唯一索引
i.data_space_id, --索引存储的空间id
i.ignore_dup_key, --是否忽略重复值
i.is_primary_key, --是否主键
i.is_unique_constraint, --是否是唯一约束
i.fill_factor, --填充因子
i.is_padded, --中间层是否也按照填充因子来填充
i.is_disabled, --是否禁用
i.is_hypothetical, --是否假设索引
i.allow_row_locks, --是否允许行级锁
i.allow_page_locks, --是否允许页级锁
i.has_filter, --是否由过滤条件
i.filter_definition, --过滤条件的定义
ic.index_column_id, --索引中列的id,在索引中时唯一的
ic.column_id, --索引中列对应到表中的列id
ic.key_ordinal, --索引中列的次序
ic.is_descending_key, --是否降序排列
ic.is_included_column, --是否是include中的列
c.name --列名称
from sys.tables t
inner join sys.indexes i
on t.object_id = i.object_id
inner join sys.index_columns ic
on ic.object_id = i.object_id
and ic.index_id = i.index_id
inner join sys.columns c
on c.object_id = ic.object_id
and c.column_id = ic.column_id
where t.object_id = object_id('t2')
and i.index_id = 3
索引维护
--1.重建索引,如果重建的是聚集索引,那么整个表的数据都会重建
ALTER INDEX idx_partition_compression ON WCT
REBUILD
/*============================================
rebuild的with中可以包括的选项
<rebuild_index_option > ::=
{
PAD_INDEX = { ON | OFF }
| FILLFACTOR = fillfactor
| SORT_IN_TEMPDB = { ON | OFF }
| IGNORE_DUP_KEY = { ON | OFF }
| STATISTICS_NORECOMPUTE = { ON | OFF }
| ONLINE = { ON | OFF }
| ALLOW_ROW_LOCKS = { ON | OFF }
| ALLOW_PAGE_LOCKS = { ON | OFF }
| MAXDOP = max_degree_of_parallelism
| DATA_COMPRESSION = { NONE | ROW | PAGE }
[ ON PARTITIONS ( { <partition_number_expression> | <range> }
[ , ...n ] ) ]
}
==============================================*/
--1.1ONLINE选项不能用在xml索引,禁用的索引,分区索引,临时表上的索引
ALTER INDEX idx_partition_compression ON WCT
REBUILD
WITH (ONLINE = ON,
ALLOW_PAGE_LOCKS = ON,
ALLOW_ROW_locks = OFF,
MAXDOP = 2,
SORT_IN_TEMPDB = ON)
ALTER INDEX idx_partition_compression ON WCT
REBUILD PARTITION = ALL
WITH (FILLFACTOR = 80,
SORT_IN_TEMPDB = ON,
DATA_COMPRESSION = ROW ON PARTITIONS (1),
DATA_COMPRESSION = PAGE ON PARTITIONS (2,3 TO 5))
/*============================================
PARTITION的with中可以包括的选项
<single_partition_rebuild_index_option> ::=
{
SORT_IN_TEMPDB = { ON | OFF }
| MAXDOP = max_degree_of_parallelism
| DATA_COMPRESSION = { NONE | ROW | PAGE } }
}
==============================================*/
--1.2只重建指定分区
ALTER INDEX idx_partition_compression ON WCT
REBUILD PARTITION = 3
WITH (SORT_in_TEMPDB = ON,
MAXDOP = 2,
data_compression = row)
--1.3重建所有索引
ALTER INDEX ALL ON WCT
REBUILD
--2.1索引碎片整理,总是在ONLINE下运行
ALTER INDEX idx_partition_compression ON WCT
REORGANIZE
--2.2partition中的WITH只有一个LOB_COMPACTION选项
--如果没有LOB数据,那么会忽略此选项
ALTER INDEX idx_partition_compression ON WCT
REORGANIZE PARTITION = 3
WITH (LOB_COMPACTION = ON)
--3.1建立堆表
select * into dbo.wc1
from dbo.wcT
--3.2重新组织堆表
ALTER TABLE DBO.wc1
REBUILD