newid() 与 newsequentialid() 有什么区别/优缺点? [英] newid() vs newsequentialid() What are the differences/pros and cons?

问题描述

在所有主键都是 GUID 的数据库中，使用 newid() 与 newsequentialid() 作为默认值或绑定"有什么区别/含义和/或优缺点.

我所知道的唯一区别是 newid() 创建一个新的随机 GUID，而不是 newsequentialid() 基于表中的最后一个以递增的方式创建一个新的 GUID.

解决方案

当您在 DB 中执行插入行时，它将按相对于表中其他 PK 的顺序插入.使用普通 guid，这可以在表中的任何位置.newsequentialid() 将始终添加到表的末尾.

因此提高了插入的性能.

<块引用>

最引人注目的是NEWID系统函数所需的写入次数.这与 69% 的平均页面密度相结合，证明了由叶级插入的随机分布引起的页面分裂.一页填满后，需要将其拆分为 2 页，每页占 50%，以便完成插入.页拆分不仅导致页密度低，而且使数据页碎片化得很严重(下一个数据页有 99% 的可能性与当前页不相邻).在我们的测试中，页面拆分所需的空闲页面最有可能的位置是在表的末尾，而与插入行的位置无关.因此，为了读取行，扫描需要在广泛分布的拆分页面之间来回跳转，因此出现了令人震惊的碎片.

--Stefan Delmarco

In a database where all of your primary keys are GUIDs, what are the differences/implications and/or pros and cons using newid() versus newsequentialid() as the "default value or binding".

The only difference that I know of is that newid() creates a new random GUID as opposed to newsequentialid() creates a new GUID based on the last one that is in the table in an incremented fashion.

解决方案

When you perform an insert in a row the DB, it will be inserted in order relative to the other PK's in the table. With a normal guid, this could be anywhere in the table. A newsequentialid() will always be added to the end of the table.

So the performance of inserts is improved.

This site explains the differences and benchmarks between the two different methods.

Update - the blog post referenced has been moved. The link now refers to an web.archive.org link. Here is the key takeaway:

Most striking is the number of writes required by the NEWID system function. This, coupled with the average page density of 69%, is evidence of the page splitting caused by the random distribution of inserts at the leaf level. As soon as a page fills up, it needs to be split into 2 pages of 50% each for the insert to complete. Not only has page splitting resulted in poor page density, it has fragmented the data pages quite badly (there is a 99% probability that the next data page is not next to the current one). In our tests the most likely place for a free page required for the page split is at the end of the table irrespective of where the row is being inserted. Therefore to read the rows in order the scan needs to keep jumping back and forth between widely distributed split pages, hence the appalling fragmentation.

--Stefan Delmarco

这篇关于newid() 与 newsequentialid() 有什么区别/优缺点?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！