如何测量 SQL 填充因子值
通常，当我在表上创建索引时，我通常会根据对表的使用方式（多次读取或多次写入）的有根据的猜测来猜测填充因子应该是什么。
是否有更科学的方法来确定更准确的填充因子值？
Usually when I'm creating indexes on tables, I generally guess what the Fill Factor should be based on an educated guess of how the table will be used (many reads or many writes).
Is there a more scientific way to determine a more accurate Fill Factor value?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
我倾向于认为，如果您追求性能改进，那么您的时间最好花在其他地方，调整您的模式，优化您的查询并确保良好的索引覆盖率。 填充因子是当您知道系统中的其他一切都处于最佳状态时才需要担心的事情之一。 我不知道有谁能这么说。
I would tend to be of the opinion that if you're after performance improvements, your time is much better spent elsewhere, tweaking your schema, optimising your queries and ensuring good index coverage. Fill factor is one of those things that you only need to worry about when you know that everything else in your system is optimal. I don't know anyone that can say that.
您可以尝试运行大量实际操作并查看 IO 队列以了解不同的操作。
有很多变量控制它，例如每行的大小以及写入与读取的数量。
基本上：高填充因子 = 读取速度更快，低填充因子 = 写入速度更快。
然而，事情并没有那么简单，因为几乎所有写入都将针对需要首先查找的行的子集。
例如：将填充因子设置为 10%，每次单行更新将花费 10 倍的时间才能找到正在更改的行，即使页面拆分的可能性很小。
通常，您会看到填充因子为 70%（非常高的写入）到 95%（非常高的读取）。
这是一种艺术形式。
我发现填充因子的一个好方法是将其视为地址簿中的页面 - 地址包装得越紧密，更改它们就越困难，但地址簿也越薄。 我想我在我的博客上解释得更好。
You could try running a big list of realistic operations and looking at IO queues for the different actions.
There are a lot of variables that govern it, such as the size of each row and the number of writes vs reads.
Basically: high fill factor = quicker read, low = quicker write.
However it's not quite that simple, as almost all writes will be to a subset of rows that need to be looked up first.
For instance: set a fill factor to 10% and each single-row update will take 10 times as long to find the row it's changing, even though a page split would then be very unlikely.
Generally you see fill factors 70% (very high write) to 95% (very high read).
It's a bit of an art form.
I find that a good way of thinking of fill factors is as pages in an address book - the more tightly you pack the addresses the harder it is to change them, but the slimmer the book. I think I explained it better on my blog.