SQL Server 索引知识-应用,维护
创建聚集索引
a索引键最好唯一(如果不唯一会隐形建立uniquier列(4字节)确保唯一,也就是这列都会复制到所有非聚集索引中)
b聚集索引列所占空间应尽量小(否则也会使非聚集索引的空间变大)
c聚集索引应固定,不能随便改动(否则会引起分页,碎片,非聚集索引被迫修改等一些列问题)
d聚集索引键一般与主键(primary key)分开(基于方便业务调整,如业务逻辑存储数据与clustered index key一致且不会改变,则主键可为聚集索引)
应用实例：
选择聚集键时，尽量避免因插入引起的分页
单单时间列不是一个好的聚集索引，因为不唯一,但可以组合例如(date+int)
索引时(16byte)要用 NEWSEQUENTIALID() 而不用NEWID(),因为newid()随机,会引起碎片分页等
创建非聚集索引
创建索引选项:
FILLFACTOR ---填充因子,保留页空间比率(避免分页,根据索引项特性设定,如果不可避免分裂,建议设定高比率值)
PAD_INDEX---与FILLFACTOR连接使用,保留中间节点可用空间
GNORE_DUP_KEY---在有UNIQUE(聚集/非聚集)约束时舍弃重复键值而不回滚整个语句
DROP_EXISTING --重建聚集索引时避免聚集索引二次重建(非聚集索引线上环境应使用创建新索引后删除现有索引方式,使索引维护影响最小化)
SORT_IN_TEMPDB---创建(重建)索引使用SORT_IN_TEMPDB选项,在TEMPDB中对键值排序减少源数据库需求空间,如果tempdb和所有操作的数据库在不同磁盘上,则性能会有提高
STATISTICS_NORECOMPUTE---是否自动更新索引上的统计信息
MAXDOP ---控制新建索引能够使用的最大处理器数量(数据过于倾斜会造成CPU资源浪费,创建时瓶颈一般出现在磁盘.千万级数据MAXDOP建议4)
ONLINE---是否在线创建索引(进行索引维护时,为使影响最低,一般设置为ON)
On filegroup ---为索引存储选择文件组.(根据索引类型,维护计划类型指定.)
ASC/DESC ---索引排序规则
索引视图
检查
SELECT OBJECTPROPERTY (OBJECT_ID ('Product_Totals'), 'IsIndexable');---查看视图是否可以建立索引
SELECT OBJECTPROPERTY (OBJECT_ID ('Vdiscount1'), 'IsIndexed');----查看视图是否创建了索引
条件
ANSI_NULLS 和 QUOTED_IDENTIFIER 选项必须设置为 ON
必须使用 SCHEMABINDING
视图不能引用任何其他视图，只能引用基表
视图引用的所有基表必须与视图位于同一数据库中，并且所有者也与视图相同
视图中的表达式引用的所有函数必须是确定的(计算列,可使用IsDeterministic确认)
注意:
索引视图上必须首先创建唯一聚集索引
即使创建了索引视图,查询时优化器也不一定使用索引视图的索引,可能直接有索引定义中表的索引
建议:索引视图会使源表在DML时扩大锁的规模,造成潜在的阻塞甚至死锁问题.在频繁更新的业务中不建议使用.
索引最佳实践
选好聚集索引,最好创建表时就指定(巨大数据量的堆表变为聚集表时成本非常高)
非聚集索引选择,高选择性列(索引密度),频繁访问谓词中选择创建.
复合非聚集索引首列应高选择.
注意复合索引键列排序规则(根据实际业务逻辑)
用好索引包含性列(覆盖查询避免可能的随机IO)
非聚集索引数量考量(平衡查询与DML操作消耗)
过滤索引使用(特殊数据结构下使用.如数据中大量Null值等)
非聚集索引包含性列中显示添加聚集索引(不会消耗额外空间,如果使用非聚集索引时包括聚集谓词,可避免键查找)
用好缺失索引相关视图sys.dm_db_missing_index_XX(需定位到具体查询具体分析,系统规模性调优时酌情设定规则批量创建)
索引维护.
创建索引.
创建前检查运行环境是否适合创建(低负载,是否有长时间占用相应锁资源导致阻塞创建进程)
线上环境应在线创建
替换/调整索引时采用创建,删除步骤
根据特定场景确认是否需重建,重新组织索引(碎片影响区域扫描,预读.重新组织索引对预读无帮助. sys.dm_db_index_physical_stats检索索引碎片)
删除索引.
根据DMV(sys.dm_db_index_usage_stats等)制定相应规则判断索引是否无用.(规则顺序:是否无用索引,是否重复索引,是否合理索引,是否可合并索引)
标记无用索引应先禁用一段时间.(完全确认情况可以立刻删除)
删除前确认是否有长时间占用相应锁资源导致阻塞删除进程.
