問題描述
我正在為我公司正在啟動的新數(shù)據(jù)庫制定數(shù)據(jù)庫標準.我們試圖定義的一件事是與 UniqueIdentifiers 相關(guān)的主鍵和聚集索引規(guī)則.
I am working on database standards for a new database my company is starting. One of the things we are trying to define is Primary Key and Clustered Index rules in relation to UniqueIdentifiers.
(注意:我不想討論使用 UniqueIdentifier 作為主鍵或聚集索引的利弊.網(wǎng)絡(luò)上有大量關(guān)于此的信息.這不是 那個討論.)
(NOTE: I do not want a discussion on the pros and cons of using a UniqueIdentifier as a primary key or clustered index. There is a ton of info on the web about that. This is not that discussion.)
所以這是讓我擔心的場景:
So here is the scenario that has me worried:
假設(shè)我有一個表，它有一個 UniqueIdentifier 作為聚集索引和主鍵.讓我們稱之為可樂.我將 ColA 的默認值設(shè)置為 NewSequentialId().
Say I have a table with a UniqueIdentifier as the clustered index and primary key. Lets call it ColA. I set the default value for ColA to be NewSequentialId().
使用 NewSequentialId() 我插入三個連續(xù)的行:
Using that NewSequentialId() I insert three sequential rows:
{72586AA4-D2C3-440D-A9FE-CC7988DDF065}
{72586AA4-D2C3-440D-A9FE-CC7988DDF066}
{72586AA4-D2C3-440D-A9FE-CC7988DDF067}
{72586AA4-D2C3-440D-A9FE-CC7988DDF065}
{72586AA4-D2C3-440D-A9FE-CC7988DDF066}
{72586AA4-D2C3-440D-A9FE-CC7988DDF067}
然后我重新啟動我的服務(wù)器.NewSequentialId 的文檔 說重新啟動 Windows 后，GUID 可以啟動再次來自較低的范圍，但仍然是全球唯一的."
Then I reboot my server. The docs for NewSequentialId say that "After restarting Windows, the GUID can start again from a lower range, but is still globally unique."
所以下一個起點可以低于上一個范圍.
So the next starting point can be lower than the previous range.
所以在重新啟動后，我又插入了 3 個值:
So after the restart, I insert 3 more values:
{35729A0C-F016-4645-ABA9-B098D2003E64}
{35729A0C-F016-4645-ABA9-B098D2003E65}
{35729A0C-F016-4645-ABA9-B098D2003E66}
{35729A0C-F016-4645-ABA9-B098D2003E64}
{35729A0C-F016-4645-ABA9-B098D2003E65}
{35729A0C-F016-4645-ABA9-B098D2003E66}
(我不確定該 guid 在數(shù)據(jù)庫中的確切表示方式，但讓我們假設(shè)，因為這個 guid 以 3 開頭，而前一個以 7 開頭，所以 3 個比 7 個小".)
(I am not sure exactly how the guid is represented in the database, but lets assume since this one starts with 3 and the previous ones started with 7 that the 3 ones are "smaller" than the 7 ones.)
當您在聚集索引中間執(zhí)行插入操作時，必須重新映射索引.(至少我的 DBA 是這么告訴我的.)而且每次我重新啟動時，我都會冒著讓我的新 UniqueIdentifier 范圍正好位于其他先前范圍中間的風險.
When you do an insert that is in the middle of a clustered index, a remapping of the index has to happen. (At least so my DBA has told me.) And every time I reboot I run the risk of having my new UniqueIdentifier range be right in the middle of other previous ranges.
所以我的問題是:由于下一組 UniqueIdentifiers 將小于上一組，所以每次插入都會導(dǎo)致我的聚集索引混洗嗎?
So my question is: Since the next set of UniqueIdentifiers will be smaller than the last set, will every insert cause my clustered index to shuffle?
如果沒有，為什么?SQL Server 知道我在使用 NewSequentialId 嗎?有什么辦法彌補嗎?
And if not, why? Does SQL Server know that I am using NewSequentialId? Does it some how compensate for that?
如果沒有，那么它怎么知道我接下來要插入什么?也許接下來的一百萬次插入將從 3 開始.或者他們可能會從 7 開始.它怎么知道?
If not, then how does it know what I will insert next? Maybe the next million inserts will start with 3. Or maybe they will start with 7. How does it know?
或者它不知道并保持一切井然有序.如果是這種情況，那么一次重新啟動可能會嚴重影響性能.(這讓我覺得我需要自己的不受重啟影響的自定義 NewSequentialId.)對嗎?還是有什么我不知道的魔法?
Or does it not know and just keeps everything in order. If that is the case then one reboot could massively affect performance. (Which makes me think I need my own custom NewSequentialId that is not affected by reboots.) Is that correct? Or is there some magic I am not aware of?
在我的標準中強烈不鼓勵將 GUID 作為聚集索引.正如我上面所說，有很多原因表明這是一個壞主意.我想知道這是否是另一個原因.
GUID as a clustered index is strongly discouraged in my standard. As I said above, there are many reasons that this is a bad idea. I am trying to find out if this is another reason why.
推薦答案
通常，您將使用適當?shù)?FILL FACTOR 創(chuàng)建索引，以便在所有頁面中為這種情況留出空白空間.也就是說，一旦空白空間被填滿，聚集索引就會重新排序.
Normally you will create your indexes with an appropriate FILL FACTOR to leave empty space in all your pages for just such a scenario. That being said, the clustered index does get reordered once the empty space is filled.
我知道您不想討論使用 GUID 作為聚集鍵，但這是不推薦做法的原因之一.
I know you don't want to discuss using GUID as a clustered key, but this is one of the reasons that it's not a recommended practice.
將會發(fā)生的情況是，您將有越來越多的頁面拆分，這將在您不斷插入行時導(dǎo)致非常高的碎片水平，并且您將需要以更高的頻率重建索引以保持性能線.
What will happen is that you will have an increasing volume of page splits, which will lead to a very high level of fragmentation as you keep inserting rows, and you will need to rebuild your index at a higher frequency to keep performance in line.
有關(guān)該主題的完整處理，沒有比
For a full treatment on the topic, there's no better source than
金
Tripp 的
博客
附帶說明，當您考慮創(chuàng)建自己的 NewSequentialID 創(chuàng)建函數(shù)時，您可能遇到了設(shè)計問題，應(yīng)該重新考慮您的計劃.
As a side note, when you are considering creating your own NewSequentialID creation function, you probably have a design issue and should reconsider your plan.
這篇關(guān)于UniqueIdentifier 聚集索引上的 NewSequentialId的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網(wǎng)！