SQL Server：索引重建和索引重组有什么区别？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/873263/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
SQL Server: What is the difference between Index Rebuilding and Index Reorganizing?
提问by Anoop
What is the difference between Index Rebuilding and Index Reorganizing?
索引重建和索引重组有什么区别？
回答by Charlie Martin
Think about how the index is implemented. It's generally some kind of tree, like a B+ Tree or B- Tree. The index itself is created by looking at the keys in the data, and building the tree so the table can be searched efficiently.
想想索引是如何实现的。它通常是某种树，例如 B+ 树或 B- 树。索引本身是通过查看数据中的键并构建树来创建的，以便可以有效地搜索表。
When you reorganizethe index, you go through the existing index, cleaning up blocks for deleted records etc. This couldbe done (and is in some databases) when you make a deletion, but that imposes some performance penalty. instead, you do it separately in order to do it more or less batch mode.
当您重新组织索引时，您会遍历现有索引，清理已删除记录的块等。这可以在您进行删除时完成（并且在某些数据库中），但这会带来一些性能损失。相反，您可以分开进行，以便或多或少地以批处理模式进行。
When you rebuildthe index, you delete the existing tree and read all the records, building a new tree directly from the data. That gives you a new, and hopefully optimized, tree that maybe better than the results of reorganizing the table; it also lets you regenerate the tree if it somehow has been corrupted.
当你重建索引，则删除现有的树和读取所有的记录，直接从数据建立一个新的树。这为您提供了一个新的，并且有望优化的树，它可能比重组表的结果更好；如果它以某种方式损坏，它还可以让您重新生成树。
回答by Quassnoi
REBUILDlocks the table for the whole operation period (which may be hours and days if the table is large).
REBUILD在整个操作期间锁定表（如果表很大，可能是几小时和几天）。
REORGANIZEdoesn't lock the table.
REORGANIZE不锁表。
Well. actually, it places some temporary locks on the pages it works with right now, but they are removed as soon as the operation is complete (which is fractions of second for any given lock).
好。实际上，它在它现在使用的页面上放置了一些临时锁，但是一旦操作完成它们就会被删除（对于任何给定的锁来说都是几分之一秒）。
As @Andomarnoted, there is an option to REBUILDan index online, which creates the new index, and when the operation is complete, just replaces the old index with the new one.
如前所述@Andomar，有一个REBUILD在线索引选项，它创建新索引，当操作完成时，只需用新索引替换旧索引。
This of course means you should have enough space to keep both the old and the new copy of the index.
这当然意味着您应该有足够的空间来保存索引的旧副本和新副本。
REBUILDis also a DMLoperation which changes the system tables, affects statistics, enables disabled indexes etc.
REBUILD也是DML更改系统表、影响统计信息、启用禁用索引等的操作。
REORGANIZEis a pure cleanup operation which leaves all system state as is.
REORGANIZE是一个纯粹的清理操作，它使所有系统状态保持原样。
回答by Mojib M
Rebuild it droping the current indexes and recreating new ones.
重建它删除当前索引并重新创建新索引。
Reorganizing is like putting the house in order with what u already have.
重组就像把你已经拥有的房子整理好。
it is a good practice to use 30% fragmentation to determine rebuild vs reorganize.
使用 30% 的碎片来确定重建与重组是一个很好的做法。
<30% reorganize vs. >30% rebuild
<30% 重组 vs. >30% 重建
回答by Danny Battison
There are a number of differences. Basically, rebuilding is a total rebuild of an index - it will build a new index, then drop the existing one, whereas reorganising it will simply, well... it will reorganiseit.
有许多不同之处。基本上，重建是对索引的完全重建——它会建立一个新索引，然后删除现有的索引，而重新组织它会很简单，嗯……它会重新组织它。
Thisblog entry I came across a while back will explain it much better than I can. :)
不久前我遇到的这篇博客条目将比我能更好地解释它。:)
回答by Irina C
"Reorganize index" is a process of cleaning, organizing, and defragmenting of "leaf level" of the B-tree (really, data pages).
“重新组织索引”是对 B 树（实际上是数据页）的“叶级”进行清理、组织和碎片整理的过程。
Rebuilding of the index is changing the whole B-tree, recreating the index.
重建索引正在改变整个 B 树，重新创建索引。
It's recommended that index should be reorganized when index fragmentation is from 10% to 40%; if index fragmentation is great than 40%, it's better to rebuild it.
当索引碎片在10%到40%之间时，建议重新组织索引；如果索引碎片大于 40%，最好重建它。
Rebuilding of an index takes more resources, produce locks and slowing performance (if you choose to keep table online). So, you need to find right time for that process.
重建索引需要更多资源，产生锁并降低性能（如果您选择保持表在线）。因此，您需要为该过程找到合适的时间。
回答by Mark Sowul
In addition to the differences above (basically rebuild will create the index anew, and then "swap it in" for the existing one, rather than trying to fix the existing one), an important consideration is that a rebuild - even an Enterprise ONLINE rebuild - will interfere with snapshot isolation transactions.
除了上述差异（基本上重建会重新创建索引，然后将其“换入”现有索引，而不是尝试修复现有索引），一个重要的考虑因素是重建 - 即使是 Enterprise ONLINE 重建- 会干扰快照隔离事务。
TX1 starts snapshot transaction
TX1 reads from T
TX1 启动快照事务
TX1 从 T 读取
TX2 rebuilds index on T
TX2 rebuild complete
TX2 重建索引 T
TX2 重建完成
TX1 read from T again:
TX1 再次从 T 读取：
Error 3961, Snapshot isolation transaction failed in database because the object accessed by the statement has been modified by a DDL statement in another concurrent transaction since the start of this transaction. It is disallowed because the metadata is not versioned. A concurrent update to metadata can lead to inconsistency if mixed with snapshot isolation.
错误 3961，数据库中的快照隔离事务失败，因为自该事务开始以来，该语句访问的对象已被另一个并发事务中的 DDL 语句修改。这是不允许的，因为元数据没有版本化。如果与快照隔离混合使用，对元数据的并发更新可能会导致不一致。
回答by girish
Rebuild index - rebuilds one or more indexes for a table in the specified database.
重建索引 - 为指定数据库中的表重建一个或多个索引。
Reorganise index - Defragments clustered and secondary indexes of the specified table
重组索引 - 对指定表的聚集索引和二级索引进行碎片整理
