索引维护

Question

什么是索引维护，如何进行？需要多久进行一次维护？有哪些好处？ 这与一个经常被修改的事务表有关；所有的DML操作都会在该表上运行。

- rmdussa

Answer 1

我完全同意Jonathan所说的一切，除了索引维护的频率。

如果你恰好拥有一个设计不良的索引（例如GUID键上的聚集索引），你可能需要每晚甚至白天都要进行维护。

作为一个经验法则：如果你的索引碎片化低于5％，那就没问题。如果你的碎片化在5％到约30％之间，你应该进行索引重组：

ALTER INDEX (your index name) ON (your table name) REORGANIZE

如果您的索引具有超过30％的索引碎片，您需要完全重建它：

ALTER INDEX (your index name) ON (your table name) REBUILD

重建索引可能会造成干扰-尝试在非工作时间进行，例如在晚上。

为了确定索引的碎片情况，您可以使用以下 DMV 查询：

SELECT 
    t.NAME 'Table name',
    i.NAME 'Index name',
    ips.index_type_desc,
    ips.alloc_unit_type_desc,
    ips.index_depth,
    ips.index_level,
    ips.avg_fragmentation_in_percent,
    ips.fragment_count,
    ips.avg_fragment_size_in_pages,
    ips.page_count,
    ips.avg_page_space_used_in_percent,
    ips.record_count,
    ips.ghost_record_count,
    ips.Version_ghost_record_count,
    ips.min_record_size_in_bytes,
    ips.max_record_size_in_bytes,
    ips.avg_record_size_in_bytes,
    ips.forwarded_record_count
FROM 
    sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'DETAILED') ips
INNER JOIN  
    sys.tables t ON ips.OBJECT_ID = t.Object_ID
INNER JOIN  
    sys.indexes i ON ips.index_id = i.index_id AND ips.OBJECT_ID = i.object_id
WHERE
    AVG_FRAGMENTATION_IN_PERCENT > 0.0
ORDER BY
    AVG_FRAGMENTATION_IN_PERCENT, fragment_count

Michelle Ufford拥有一款出色的自动索引碎片整理脚本，强烈推荐！或者您可以考虑设置SQL Server维护计划，每晚运行并清理索引等内容。

- marc_s

---

谢谢，当我第一次创建索引时，是否没有I/O操作？还有一个疑问是关于大小的，索引的大小将随着数据/交易增长，过一段时间后我删除并重新创建相同的索引，那么它的大小和I/O操作会下降吗？ - rmdussa

当然 - 创建索引可能会导致大量的I/O操作！索引的大小取决于表中的行数 - 当您删除许多行时，重建索引可能会略微减小其大小，因为已删除行使用的空间可以被回收 - 但每个表中的行数乘以索引中的字节数（索引字段的总和）将被需要 - 没有什么魔法。 - marc_s

是的，没错 - 这就是你的索引平均碎片化程度。小于5可以接受，5-30建议重新组织，30及以上则需要重建。 - marc_s

这个查询中索引名称为NULL的行，为什么它们的index_type_desc被列为“HEAP”？我无法用给定的alter index命令运行它们。应该忽略它们吗？ - KM.

在执行重建或重组之后，您需要更新统计信息或重新编译存储过程吗？ - KM.

Answer 2

一般来说，DBMS会自动管理索引以确保其正常工作。然而，如果有足够的插入、删除和更新操作，索引可能还有改进的空间。也就是说，如果删除并重新创建索引，则生成的索引将比修改后的索引更小更高效。最终，较小的索引意味着更少的I/O操作，而I/O操作比计算要昂贵得多。

可能有针对SQL Server的特定答案，但是：

- 索引维护是使索引性能更好的过程。 - 进行索引维护的一种方法是删除并重建索引。 - 另一种方法可能是以某种方式更改表格，使得必须重建索引（例如聚集它）。 - 您可能不需要经常这样做，但这主要取决于MS SQL Server的质量以及它处理索引的方式。我希望至少是足够的。如果您有方便的停机时间可用于此操作，那么可能每月进行一次。否则，可以更少或者根本不需要进行。 - 好处是索引使用的空间更少，并且使用索引的性能更好。

- Jonathan Leffler

---

谢谢，当我第一次创建索引时，是否不会有I/O操作？另外一个疑问是关于大小的，索引的大小将随着数据/事务的增长而增加，一段时间后我删除并重新创建相同的索引，它会减小大小和I/O操作吗？ - rmdussa

如果表在创建索引时有1千万条记录，则会发生I/O；如果没有记录，则几乎没有I/O。当您删除一些记录并添加其他记录并保持总记录数量近似恒定时，索引会发生什么取决于情况，但通常它会缓慢增长。某些模式比其他模式更糟。例如，如果您具有单调递增的标识列并始终从低端删除并从高端添加，则可能会破坏索引结构的平衡。但我不是MS SQL Server专家-请注意我的频率想法的分歧。 - Jonathan Leffler