SQL Server中的rebuild和reorganize区别，OFFLINE和ONLINE索引重建有什么区别？

最新推荐文章于 2024-08-26 16:21:48 发布

踏雪无痕

最新推荐文章于 2024-08-26 16:21:48 发布

阅读量1.3k

点赞数

分类专栏： SQL Server 2000/2005/2008

原文链接：https://www.bbsmax.com/A/D854rvA3zE/

版权

SQL Server 2000/2005/2008 专栏收录该内容

210 篇文章 4 订阅

订阅专栏

两者的区别

Rebuilding an index drops and re-creates the index. This removes fragmentation, reclaims disk space by compacting the pages based on the specified or existing fill factor setting, and reorders the index rows in contiguous pages. When ALL is specified, all indexes on the table are dropped and rebuilt in a single transaction.

重新生成索引将会删除并重新创建索引。这将根据指定的或现有的填充因子设置压缩页来删除碎片、回收磁盘空间，然后对连续页中的索引行重新排序。如果指定 ALL，将删除表中的所有索引，然后在单个事务中重新生成。

Reorganizing an index uses minimal system resources. It defragments the leaf level of clustered and nonclustered indexes on tables and views by physically reordering the leaf-level pages to match the logical, left to right, order of the leaf nodes. Reorganizing also compacts the index pages. Compaction is based on the existing fill factor value.

重新组织索引使用最少系统资源重新组织索引。通过对叶级页以物理方式重新排序，使之与叶节点的从左到右的逻辑顺序相匹配，进而对表和视图中的聚集索引和非聚集索引的叶级进行碎片整理。重新组织还会压缩索引页。压缩基于现有的填充因子值。

Rebuilding an index can be executed online or offline. Reorganizing an index is always executed online. To achieve availability similar to the reorganize option, you should rebuild indexes online.

rebulid index既可以在online又可以在offline下执行，而reorganize index只能在online下执行的。

本文主要讲解如何使用alter index来rebuild和reorganize索引来清除碎片，rebuild能够完全清除碎片，但是reorganize却不能。

rebulid index既可以在online又可以在offline下执行，而reorganize index只能在online下执行的。

Difference between rebuild index online and offline(PS:2012-9-11)

既然rebuild index既可以是online模式，也可以是offline模式，那么两者有什么区别呢。这个我们可以参考stackoverflow上面的一篇文章：What is the difference between OFFLINE and ONLINE index rebuild in SQL Server? 在这里我还是简要总结一下：

online模式下

rebuild index会复制旧索引来新建索引，此时旧的索引依然可以被读取和修改，但是所以在旧索引上的修改都会同步更新到新索引下。中间会有一些冲突解决机制，具体参考Online Index Operations 里面的Build Phase这一章节。然后在rebuild这个过程完整的时候，会对table上锁一段时间，在这段时间里会用新索引来替换旧索引，当这个过程完成以后再释放table上面的锁。如果索引列包含 LOB对象的话，在SQL Server 2005/2008/R2中rebuild index online会失败。在sql server 2012中，即使索引列包含LOB对象，也可以rebuild index online了，可以参考 Online Index Operations for indexes containing LOB columns.

offline模式下

rebuilde index会对table上锁，所有对这个table的读写操作都会被阻塞，在这期间新索引根据旧索引来创建，其实就是一个复制的过程，但是新索引没有碎片，最后使用新索引替换旧索引。当rebuild整个过程完成以后，table上面的锁才会被释放。

-------------------------------------------------------------------------------------------------------------------------------------------------------------------

在ONLINE模式下新的建立索引，同时可以读取和写入旧索引。旧索引的任何更新也将应用于新索引。反物质列用于跟踪更新和重建之间可能存在的冲突（即删除尚未复制的行）。请参见在线索引操作。当进程完成时，表会被锁定一段时间，新索引将替换旧索引。如果索引包含LOB列，则SQL Server 2005/2008 / R2不支持ONLINE操作。

在OFFLINE模式下，表会被预先锁定以进行任何读取或写入，然后新索引从旧索引构建，同时保持对表的锁定。在重建索引时，不允许对表执行读取或写入操作。只有在操作完成后才会释放对表的锁定并再次允许读取和写入。

转摘：

create index注意n如果是大表建立索引，切记加上ONLINE参数

这几天在做数据库的优化，有个2亿记录的表，发现需要添加一个联合索引，结果就采用普通的create index index_name

on tablename (entp_id,sell_date),结果悲剧了，把所有的DML语句都阻塞了，导致系统不能正常使用，还好是晚上10点，用户不是非常多，1个小时候，索引结束，阻塞解决；

上网查了一下，如果加上 online参数后，就可以在线做索引，而不需要阻塞所有的DML语句，血的教训，拿出来与各位共勉，具体online与不加online区别如下：

1. DML操作对create index 的影响。如果在create的时候，有其他的进程在对这个index 所对应的数据进行DML操作，create会受影响:

SQL> create table test (id number, name varchar2(20));

Table created.

--- 然后重新开一个session:
SQL> insert into test values (1,'lms');

1 row created.

<no commit>

SQL> create index t1 on test(id);
create index t1 on test(id)
*
ERROR at line 1:
ORA-00054: resource busy and acquire with NOWAIT specified

2. 加online这个参数，这个参数加上以后，除了create过程中index 保持online状态，Oracle还会在create index之前等待所有DML操作结束，然后得到DDL锁，开始create.
SQL> create index t1 on test(id) online;
<hold before commit>
<after commit>

SQL> commit;

Commit complete.

Index altered.

---- 如果不commit,上面的操作就会一直hold。

所以以后create索引和rebuild索引的时候最好加上online。