ITPub博客

首页 > 数据库 > SQL Server > sqlserver索引重建和索引重组的区别和操作方法

sqlserver索引重建和索引重组的区别和操作方法

原创 SQL Server 作者:lusklusklusk 时间:2019-09-10 16:30:40 0 删除 编辑

https://docs.microsoft.com/zh-cn/sql/relational-databases/indexes/reorganize-and-rebuild-indexes?view=sql-server-2017

ALTER INDEX REORGANIZE重新组织索引使用的系统资源最少,并且是联机操作。也就是说,不保留长期阻塞性表锁,且对基础表的查询或更新可以在ALTER INDEX REORGANIZE事务处理期间继续进行。

ALTER INDEX REBUILD重新生成索引会删除并重新创建索引。这可以联机完成,也可以脱机完成,重新生成索引联机执行(ON),则索引操作期间可以用此表中的数据进行查询和修改数据。默认为OFF。


重建表上的所有索引

alter index all on table_name rebuild with (>

重建表上的某个索引

alter index index_name on table_name rebuild with (>


重新组织表上的所有索引

alter index all on table_name reorganize

重新组织表上的某个索引

alter index index_name on table_name reorganize


总结:

1、sqlserve建议使用ALTER INDEX语句来重建或重组索引,已经不推荐使用DBCC INDEXDEFRAG、DBCC DBREINDEX

2、重新组织索引是在线重整Index,不会对Table锁定,重新生成索引会对Table进行锁定,当然重新生成索引期间加上>

3、重新组织索引的100%进度可以通过sys.dm_exec_requests的字段percent_complete来看,重新生成索引无法通过该方法来看



https://docs.microsoft.com/zh-cn/sql/t-sql/database-console-commands/dbcc-indexdefrag-transact-sql?view=sql-server-2017

DBCC INDEXDEFRAG

(

{ database_name | database_id | 0 }

, { table_name | table_id | view_name | view_id }

[ , { index_name | index_id } [ , { partition_number | 0 } ] ]

)

[ WITH NO_INFOMSGS ]

比如DBCC INDEXDEFRAG(DB1, TABLE1, INDEX1) WITH NO_INFOMSGS


database_name | database_id | 0

包含要进行碎片整理的索引的数据库。 如果指定 0,则使用当前数据库。

table_name | table_id | view_name | view_id

包含要进行碎片整理的索引的表或视图。

index_name | index_id

要进行碎片整理的索引的名称或 ID。 如果未指定,该语句将针对指定表或视图的所有索引进行碎片整理。

partition_number | 0

要进行碎片整理的索引的分区号。 如果未指定或指定 0,该语句将对指定索引的所有分区进行碎片整理。

DBCC INDEXDEFRAG 对索引的叶级进行碎片整理,以便页的物理顺序与叶节点从左到右的逻辑顺序相匹配,因此可提高索引扫描性能。

与 DBCC DBREINDEX(或通常的索引生成操作)不同,DBCC INDEXDEFRAG 是联机操作。 它不长期保持锁。 因此,DBCC INDEXDEFRAG 不会阻塞运行查询或更新。 因为碎片整理所需的时间与碎片整理的级别相关,若索引的碎片相对较少,则该索引的碎片整理速度比生成一个新索引要快。 对碎片太多的索引进行整理可能要比重建索引花更多的时间。


https://docs.microsoft.com/zh-cn/sql/t-sql/database-console-commands/dbcc-dbreindex-transact-sql?view=sql-server-2017

DBCC DBREINDEX (table_name[ , index_name [ , fillfactor ] ]) [ WITH NO_INFOMSGS ]

比如DBCC DBREINDEX(TABLE1, '', 0)


table_name

包含要重新生成的指定索引的表的名称。

index_name

要重新生成的索引名。 索引名称必须符合标识符规则。 如果已指定 index_name,则必须指定 table_name 。 如果未指定 index_name 或者该值为“ ”,则重新生成表的所有索引 。

fillfactor

在创建或重新生成索引时,每个索引页上用于存储数据的空间的百分比。 创建索引后,fillfactor 将替换填充因子,从而成为该索引以及重新生成的任何其他非聚集索引(因为重新生成了聚集索引)的新默认值 。

当 fillfactor 为 0 时,DBCC DBREINDEX 将使用上次为索引指定的填充因子值 。 该值存储在 sys.indexes 目录视图中 。

如果已指定 fillfactor,则必须指定 index_name 。 如果未指定 fillfactor,则使用默认填充因子 100 。

DBCC DBREINDEX 重新生成表的一个索引或为表定义的所有索引。 通过允许动态重新生成索引,可以重新生成强制 PRIMARY KEY 或 UNIQUE 约束的索引,而不必删除并重新创建这些约束。 这意味着无需了解表的结构或其约束,即可重新生成索引。 这可能在将数据大容量复制到表中以后发生。

DBCC DBREINDEX 可以在一条语句中重新生成表的所有索引。 这要比对多条 DROP INDEX 和 CREATE INDEX 语句进行编码更容易。 由于这项工作是通过一条语句执行的,因此 DBCC DBREINDEX 自动成为原子性的,而单个 DROP INDEX 和 CREATE INDEX 语句则必须包含在事务中才能成为原子性的。 此外,DBCC DBREINDEX 提供了比单个 DROP INDEX 和 CREATE INDEX 语句更多的优化性能。

与 DBCC INDEXDEFRAG 或具有 REORGANIZE 选项的 ALTER INDEX 不同,DBCC DBREINDEX 是一个脱机操作。 如果重新生成了非聚集索引,则在该操作的持续时间内,相关表持有共享锁。 这可以禁止对表进行修改。 如果重新生成了聚集索引,则持有排他表锁。 这可以禁止任何表访问,因此可以有效地使表脱机。 为了执行联机索引重新生成,或控制索引重新生成操作期间的并行度,可使用具有 ONLINE 选项的 ALTER INDEX REBUILD 语句。

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/30126024/viewspace-2656646/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论
Welcome to Lukes DB HOME。11G OCM, 8年以上DBA工作经验,博客仅记录自己的一个学习过程,不代表完全准确,如有需要,欢迎转载。

注册时间:2015-02-02

  • 博文量
    382
  • 访问量
    533096