在做性能优化时,经常需要创建索引,维护索引,或重建,或重组;在创建索引时,索引的数据页有时需要填充满,有时需要预留一定比例的空闲空间;在分析查询的执行计划之后,推荐创建覆盖索引(covering index),优化查询语句,使用执行计划通过Index Seek来获取少量数据等,这些都是索引优化不得不知的要点。
一,索引的重组(Reorganize)和重建(Rebuild)
在SQL Server中,索引(Index)是B-Tree(balance tree)结构,每个Page之间都有双向指针链接在一起。Index是在table结构之外,独立存在的存储结构。Index能使查询性能带来飞跃的主要原因是:Index 结构更小,能够更快加载到内存;Index ey物理顺序和逻辑一致,数据的预读取能够提高数据的加载速度,SQL Server 每次读取操作都会将物理物理相邻的多个Page一起加载到内存。
BTree结构决定 Index 的叶子节点,从左到右使依次增大,如图是Index的叶子节点,左边的Index Key最小,右边的Index Key最大:
如果更新数据导致index key变化,例如,将index key 由4变更为9,那么必须将9放置在8之后,10之前,如果8所在的Page有空间容纳9,那么SQL Server只需要将9移动到8之后,原来的4被删除,这会降低原page中数据存储的密度,造成一个碎片(fragment),即:3和5之间存在空闲空间,但是物理顺序和逻辑顺序还是一致的。
如果8和10所在的page不能容纳9,那么 SQL Server 选择最节省,最有效的方式:拆分Page。试想,如果不拆分page,那么,5,6,7,8 这几个数据行都要向前移动,为9腾挪空间。在SQL Server中,数据移动是十分浪费IO,内存和CPU资源的,IO必须在CPU的调控下进行。
拆分Page是指分配一个新的Page,将8所在的Page上的数据的一半(后一半,或前一半)移动在新的Page上,如图,将page中的后一半移动在新的page上,通过指针连接在一起,保持逻辑顺序的一致性,但是物理顺序已经不连续了。
对于Index Key移动之后,其物理顺序和逻辑顺序仍然保持一致,这会导致索引出现碎片,数据存储的密度降低;而拆分page,不仅将page存储数据的密度降低一半,而且数据的物理顺序和逻辑顺序,导致SQL Server的预读取操作效果下降。针对Index的这两种情况,根据Index的碎片率,对Index 进行重组(Reorganize)或重建(Rebuild)。
1,索引Reorganize 和 Rebuild的过程
Rebuild 是重新创建,将索引占用的原有存储空间释放,重新申请空间来创建索引结构,这意味着,SQL Server存储引起需要为索引结构分配新的数据页,在重建索引结构时,索引占用的存储空间是其实际大小的2倍还多。
Reorganize 是重新组织索引结构的叶子节点。在重组索引时,SQL Server存储引擎首先按照原有的填充因子(Fillfactor),压缩索引结构的叶子节点;然后,使用相同的数据页,把索引结构的叶子节点重新组织,使叶子节点符合索引定义的逻辑顺序。重组索引不会分配新的数据页,只占用索引最初占用的存储空间。
ALTER INDEX { index_name | ALL } ON schema.table REBUILD | REORGANIZE
2, 重建索引
在重建索引时,SQL Server 存储引擎使用索引的定义元数据,就是说,按照索引键(index key),索引类型(index type),唯一属性和排序方向重新创建索引。
- 重建索引,将使被disable的索引重新启用;
- 重建聚集索引时,不会重建与之关联的非聚集索引(nonclustered index),除非指定all关键字;all关键字指定基础表中的所有索引。
- 如果指定all关键字,而基础表(underlying table)是堆,那么重建索引的操作对基础表没有任何影响;而与基础表相关的所有非聚集索引都将会重建;
在重新创建索引(Rebuild)时,如果没有指定索引选项(Index Option),Rebuild操作使用默认的索引选项。在SQL Server 2012版本中,共有11个索引选项,其中5个索引选项的元数据存储在sys.indexes 中,分别是 ignore_dup_key、fill_factor、is_padded、allow_row_locks、allow_page_locks,其他6个索引选项使用默认值,其默认值(Default value)都是“否定的”或0,如下列表所示:
SORT_IN_TEMPDB : Default OFF STATISTICS_NORECOMPUTE : Default OFF DROP_EXISTING: Default OFF ONLINE: Default OFF DATA_COMPRESSION : Default NONE MAXDOP: 0
查看 sys.indexes 存储的索引选项:
![](https://i-blog.csdnimg.cn/blog_migrate/8f900a89c6347c561fdf2122f13be562.gif)
3,重组索引
重组索引结构的叶子节点,由于SQL Server默认以联机方式重组索引,这意味着在索引重组事务执行的过程中,SQL Server 存储引擎不会长期阻塞表锁,能够对基础表(underlying table)执行更新操作。如果索引选项ALLOW_PAGE_LOCKS设置为OFF,或索引被禁用,那么不能重组索引。
二,索引的填充属性(FillFactor 和 PAD_INDEX)
在Create Index时,必须考虑属性FillFactor 和 PAD_INDEX的设置,这两个属性只在create index 或 rebuild index时起作用,表示索引页的填充程度,在索引结构中,按照结点在BTree结构中的位置,索引页分为:叶级结点,中间结点。一个数据页(Page)的大小是8KB。
- FillFactor 属性指定索引叶级结点的填充百分比,微软建议设置FillFactor=90;
- Pad_index 属性是boolean 类型,指定是否使用 FillFactor 来填充索引的中间节点;默认值是OFF;
- 在插入数据时,SQL Server 尽可能使用Page全部的空间,而不会考虑填充属性;填充属性只影响索引的创建;
PAD_INDEX = { ON | OFF } FILLFACTOR = fillfactor
在创建索引时,设置FillFactor=90,这意味着,SQL Server 在create index 或 rebuild index时,不是将索引页的全部空间用完,而是使用Page空间的90%,预留10%的空闲空间,预留的Page内部的空间叫做内部碎片(Internal Fragmentation)。预留一定比例的Fragmentation的作用是:减少Page拆分。预留比例需要折衷查询和更新操作:预留空间太多,能够减少Page Split,提高数据更新速度,但预留空间过多导致索引碎片(Index Fragmentation)太多,降低查询性能;预留空间太少,能够最大限度减少Index fragmentation,提高查询查询性能,但是一旦数据更新,会导致Page Split,产生外部碎片,降低查询性能。建议预留10% 的空间,这样既能提高数据更新的速度,也能兼顾数据的更新。
1,索引中间节点的填充属性PAD_INDEX
PAD_INDEX属性表示中间节点填满的程度,100减去该属性值,就是索引页预留的空闲空间的百分比,默认值是OFF;如果设置为ON,那么在索引 create 或 rebuild时,SQL Server 将使用FillFactor百分比来填充中间节点,因此,需要指定FILLFACTOR。
2,索引叶子节点的填充属性 FILLFACTOR
FillFactor选项是一个整数值,有效值是从1到100,该属性表示叶子节点填满的程度,该属性的默认值是0,和FillFactor=100行为相同,表示在创建索引或重建索引时,数据页不会预留空闲空间。
3,推荐在创建索引时,显式指定填充属性
填充属性只在创建索引(create),或重建索引(rebuild)时起作用。如果填充因子在1和100之间,那么创建聚集索引将分配更多的数据页,带来的好处是减少了页拆分操作的次数。
例如,在创建索引(create),或重建索引(rebuild)时,指定FillFactor=80,表示每个叶子节点留下20%的空闲空间,当新的数据插入到基础表(underlying table)时,SQL Server使用该20%的空闲空间来容纳新的数据。
在创建index 或重建 index时,指定 fillfactor 和 pad_index 属性:
![](https://i-blog.csdnimg.cn/blog_migrate/8f900a89c6347c561fdf2122f13be562.gif)
三,索引的查找(Index Seek)和扫描(Index Scan)
索引是B树结构,在执行查询时,Index Seek是指SQL Server从索引结构的根节点(Root Node),逐级向叶节点(Leaf Node)查找;在查找到相应叶子节点后,取出叶子节点的数据。对于聚集索引,叶子节点是整个表的数据,Index Seek能够获取到所有列的数据,而对于非聚集索引,叶子节点存储的是索引列的数据,如果索引有包含列,那么叶子节点中也存储包含列的数据,Index Seek只能获取索引列和包含列的数据;如果查询还需要返回其他列的数据,那么SQL Server必须根据索引叶子节点包含的“行地址”信息到基础表(或聚集索引)中去获取数据,这就是进行书签查找(key lookup)。Index Seek用于从大数据量的表中返回少量记录的查询。
Index Scan是直接遍历索引树的所有叶子节点,对于包含聚集索引的基础表,只能进行Index Seek或Index Scan,因为,聚集索引的叶子包含所有的数据。对于堆表,当需要返回所有列的数据,SQL Server有时会选择执行Table Scan。Table Scan是对全表进行逐行的扫描,即使数据表中只有一行数据匹配,也会将所有数据匹配一遍,微软建议,始终在数据表上创建聚集索引。
参考文档:
Reorganize and Rebuild Indexes
Index Seek和Index Scan的区别以及适用情况
SQL SERVER – Index Seek vs. Index Scan – Diffefence and Usage – A Simple Note