在自增字段上建索引时不要设置小于100的填充因子

最新推荐文章于 2022-10-10 10:14:24 发布

原创最新推荐文章于 2022-10-10 10:14:24 发布 · 1k 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#performance #insert #less #table #each

本文探讨了数据库索引中填充因子的作用及优化策略。当新数据均匀分布于表中时，合理的填充因子可以提高性能；反之，若数据仅追加在表尾，则较低的填充因子可能导致索引空间浪费。

由于自增字段一般不会进行update操作而insert时新行只可能插入到记录的最后，所以设置小于100的填充因子只会浪费更多的索引空间而降低性能。

A nonzero fill factor other than 0 or 100 can be good for performance if the new data is evenly distributed throughout the table. However, if all the data is added to the end of the table, the empty space in the index pages will not be filled. For example, if the index key column is an IDENTITY column, the key for new rows is always increasing and the index rows are logically added to the end of the index. If existing rows will be updated with data that lengthens the size of the rows, use a fill factor of less than 100. The extra bytes on each page will help to minimize page splits caused by extra length in the rows.

参看：

http://msdn.microsoft.com/en-us/library/ms177459.aspx