索引重建(重组)的常见问题

1. 索引重建和重组有什么用？

当修改表(UPDATE、INSERT、DELETE等)中数据，数据库引擎自动维护索引的数据和结构。但是随着修改次数的累积，可能会现：

• 索引中记录的数据顺序(逻辑顺序)和数据的实际顺序不一致(物理顺序)，这也称之为外部碎片。
• 索引页的数据填充度变小(页密度)，也称之为内部碎片。
  有索引碎片是正常的，但是有大量的碎片，会降低查询性能，可以通过重建和重组索引来减少或消除碎片。

2. 索引重建和重组有什么区别？

• 重建是删除索引并重新创建。通过这种方式移除碎片、回收磁盘空间(根据现有的或指定的填充因子压缩(Compact)页数据)、对相邻页中的索引进行重新排列。重组索引使用的系统资源最少。它在叶级层从左至右，重新排列叶级页使之于索引的逻辑顺序一致。同时也会对页按填充因子进行压缩。由此可知重建对于消除碎片和空间回收上的程度更高。
• 重建索引是单个事务，如果指定了ALL关键字，则所有的索引重建做为一个事务。重组索引(包括指定了ALL)，在内部会分解为多个较小的事务执行。重建事务回滚，需要回滚所有已经发生的修改。重组可以在任意时间点停止并且只回滚当前的某个较小的事务，已经发生的修改不会回滚(这个有点像DBCC SHRINKFILE)。
• 重组只能在ONLINE模式下，重建可以指定为ONLINE或者OFFLINE。

3. 索引重建时的ONLINE和OFFLINE选项是什么意思？

顾名思义，表示重建索引的模式。

• OFFLINE时，会在表上获取Sch-M锁来阻止所有用户的访问，然后将旧索引的数据复制到新索引中,完成重建后才会释放表锁。
• ONLINE时，也是复制旧索引数据到新索引中，同时旧索引是可以读写的。重建过程中旧索引的修改操作同时会被应用到新索中，还有一个中间数据结构实现新旧数据的映射和修改冲突。在重建完成后，会使用Sch-M锁定表非常短的时间，然后使用新索引替代旧索引，并释放Sch-M。详情参考：How Online Index Operations Work
• 本地临时表的索引不能使用ONLINE模式。
• 相对来说，ONLINE要比OFFLINE使用更多的资源，但提供并发支持。

4. 在重组(或重建)大表的索引时，日志文件变得很大，怎么办？

说明一下，小表的索引整理问题没有太多意义。

数据库的所有有损操作都需要记录到日志，这个跟哪种恢复模式没有关系。也就是说从数据库的角度来看，这些日志都是它必须要写的。我们要做的是：引导它少写点日志和提高写日志的性能。下面是一些考虑点：

• 最重要考虑点：我整理索引的目的是什么？消除碎片，回收空间，迁移数据等等？只有重建/重组索引才能达到我的目的吗？

• 我们知道重组始终是ONLINE模式，它提供了并发支持，却会使用更多资源。这些资源中就包括日志。这很好验证，构建两个库，创两个同样的表和同样的索引，分别导入足够多的会产生碎片的数据，截断日志后分别执行重组和重建，你会发现重组产生的日志量要远多于重建。

• 重建索引时的ONLINE和OFFLINE的选择，要结合前一点和实际系统应用情况考虑。我们可以做一些准备工作，比如：重建前先截断日志，对日志文件做一次手动增长来避免自动增长。

• 事务在提交或者回滚后才能被截断，从前面的问题的，我们也知道重建的事务是原子性的，而重组被分成了多个小事务。也就说，在重建过程中，我们不能截断它的日志，而重组时可以截断。同理，不要在显式事务中使用ONLINE,这会导致显式事务提交后，才能截断日志。

• 考虑使用 SORT_IN_TEMPDB选项。这个选项使得索引整理的事务日志写到tempdb，而不是用户数据库。这样就减少了用户数据库事务日志量，当然tempdb的空间要足够。如果tempdb位于独立的磁盘，就可以进一步的减少与用户数据库的存储空间和性能的竞争。

• 如果可能,可以考虑切换到simple和bulk_logged恢复模式，索引的重建和重组可以利用最小化日志减少日志量。最小化日志，它不对每一行数据记录日志，而是对页和区的改变写日志。但是它不支持时间点还原。

• 如果需要预留日志空间，索引大小的2~3倍会比较安全。

5. 在重建大表的索引时，数据文件也增长到很大了，怎么办？

索引重建过程中，旧索引结构和新索引结构是并存的，如果是ONLINE模式下，还有一个中间数据结构存在。如果涉及到数据排序操作，数据排序的临时数据结构也是需要占用空间的。跟日志的问题一样，我们能做的是减弱，不可能杜绝。

• 合理配置MAXDOP选项。在SQL Server 2012/2014/2016 Enterprise上，可以使用多个处理器来执行与索引语句关联的扫描、排序和索引操作。默认是0，由SQL Server引擎决定并行度。并不是越大越好，要根据系统和负载合理设置。
• 对于临时的排序空间，它一次只能被一个索引操作使用，所以如果执行多个索引操作，只需要保证临时排序空间与最大的那个索引一样大即可。例如删除并重建聚集索引时，会同时重建相关的非聚集索引，只需要保证预留的空间与其中最大那个索引一样大即可。
• 当SORT_IN_TEMPDB=ON时，临时排序空间则位于tempdb(重建索引的事务日志也在tempdb)。如=OFF，则排序空间位于当前用户数据库中。
• 对于ONLINE模式重建的中间数据结构的位置，由SORT_IN_TEMPDB决定，跟上一点一样。
• ONLINE操作使用行版本控制，这样读取行时不需要S锁，避免了并发的数据修改事务对索引操作的影响。使用了行版本，对于并发的数据修改操作，在tempdb中存储相关的行版本数据也需要一些空间。

总结

1. 索引整理优化，对tempdb的使用较多，而tempdb本身的配置也是需要优化的。如果可能，将索引和数据分开存储，于性能和管理也有一定帮助。
2. 将平时的一些零散的记录整理汇总而成，如有疏谬，请轻拍。