GUID vs.Sequential GUID个
A typical pattern it's to use Guid as PK for tables, but, as referred in other discussions (see Advantages and disadvantages of GUID / UUID database keys)
there are some performance issues.
This is a typical Guid sequence
f3818d69-2552-40b7-a403-01a6db4552f7
7ce31615-fafb-42c4-b317-40d21a6a3c60
94732fc7-768e-4cf2-9107-f0953f6795a5
Problems of this kind of data are:<
-
- 价值的广泛分布
- 几乎是随机的
- 索引使用非常、非常、非常糟糕
- 很多树叶在动
- 几乎每个PK都需要至少
在非聚集索引上
- Oracle和上都会出现问题
SQL Server
A possible solution is using Sequential Guid, that are generated as follows:
cc6466f7-1066-11dd-acb6-005056c00008
cc6466f8-1066-11dd-acb6-005056c00008
cc6466f9-1066-11dd-acb6-005056c00008
How to generate them From C# code:
[DllImport("rpcrt4.dll", SetLastError = true)]
static extern int UuidCreateSequential(out Guid guid);
public static Guid SequentialGuid()
{
const int RPC_S_OK = 0;
Guid g;
if (UuidCreateSequential(out g) != RPC_S_OK)
return Guid.NewGuid();
else
return g;
}
Benefits
- 更好地使用索引
- 允许使用聚集键(要
在NLB方案中验证)
- 减少磁盘使用量
- 性能提升20%-25%，
最低成本
Real life measurement:
Scenario:
- 存储为唯一标识符的Guid
- Oracle上存储为CHAR(36)的GUID
- 大量的插入操作，分批进行
- 1到100秒的插入时间取决于
- Some tables > 10 millions rows
Laboratory Test – SQL Server
VS2008 test, 10 concurrent users, no think time, benchmark process with 600 inserts in batch for leaf table
Standard Guid
Avg. Process duration: 10.5 sec
Avg. Request for second: 54.6
Avg. Resp. Time: 0.26
Sequential Guid
Avg. Process duration: 4.6 sec
Avg. Request for second: 87.1
Avg. Resp. Time: 0.12
Results on Oracle (sorry, different tool used for test) 1.327.613 insert on a table with a Guid PK
Standard Guid, 0.02 sec. elapsed time for each insert, 2.861 sec. of CPU time, total of 31.049 sec. elapsed
Sequential Guid, 0.00 sec. elapsed time for each insert, 1.142 sec. of CPU time, total of 3.667 sec. elapsed
The DB file sequential read wait time passed from 6.4 millions wait events for 62.415 seconds to 1.2 million wait events for 11.063 seconds.
It's important to see that all the sequential guid can be guessed, so it's not a good idea to use them if security is a concern, still using standard guid.
To make it short... if you use Guid as PK use sequential guid every time they are not passed back and forward from a UI, they will speed up operation and do not cost anything to implement.