C# ABP项目中生成基于各种数据库的顺序GUID，有效减少索引碎片，提高查询效率
/// <summary> /// Used to generate Ids. /// </summary> public interface IGuidGenerator { /// <summary> /// Creates a GUID. /// </summary> Guid Create(); }
using System; using System.Security.Cryptography; /// <summary> /// Implements <see cref="IGuidGenerator"/> by creating sequential Guids. /// This code is taken from https://github.com/jhtodd/SequentialGuid/blob/master/SequentialGuid/Classes/SequentialGuid.cs /// </summary> public class SequentialGuid : IGuidGenerator { /// <summary> /// Gets the singleton <see cref="SequentialGuid"/> instance. /// </summary> public static SequentialGuid Instance { get; } = new SequentialGuid(); private static readonly RandomNumberGenerator Rng = RandomNumberGenerator.Create(); public SequentialGuidDatabaseType DatabaseType { get; set; } /// <summary> /// Prevents a default instance of the <see cref="SequentialGuid"/> class from being created. /// Use <see cref="Instance"/>. /// </summary> private SequentialGuid() { DatabaseType = SequentialGuidDatabaseType.SqlServer; } public Guid Create() { return Create(DatabaseType); } public Guid Create(SequentialGuidDatabaseType databaseType) { switch (databaseType) { case SequentialGuidDatabaseType.SqlServer: return Create(SequentialGuidType.SequentialAtEnd); case SequentialGuidDatabaseType.Oracle: return Create(SequentialGuidType.SequentialAsBinary); case SequentialGuidDatabaseType.MySql: return Create(SequentialGuidType.SequentialAsString); case SequentialGuidDatabaseType.PostgreSql: return Create(SequentialGuidType.SequentialAsString); default: throw new InvalidOperationException(); } } public Guid Create(SequentialGuidType guidType) { // We start with 16 bytes of cryptographically strong random data. var randomBytes = new byte[10]; Rng.Locking(r => r.GetBytes(randomBytes)); // An alternate method: use a normally-created GUID to get our initial // random data: // byte[] randomBytes = Guid.NewGuid().ToByteArray(); // This is faster than using RNGCryptoServiceProvider, but I don't // recommend it because the .NET Framework makes no guarantee of the // randomness of GUID data, and future versions (or different // implementations like Mono) might use a different method. // Now we have the random basis for our GUID. Next, we need to // create the six-byte block which will be our timestamp. // We start with the number of milliseconds that have elapsed since // DateTime.MinValue. This will form the timestamp. There's no use // being more specific than milliseconds, since DateTime.Now has // limited resolution. // Using millisecond resolution for our 48-bit timestamp gives us // about 5900 years before the timestamp overflows and cycles. // Hopefully this should be sufficient for most purposes. :) long timestamp = DateTime.UtcNow.Ticks / 10000L; // Then get the bytes byte[] timestampBytes = BitConverter.GetBytes(timestamp); // Since we're converting from an Int64, we have to reverse on // little-endian systems. if (BitConverter.IsLittleEndian) { Array.Reverse(timestampBytes); } byte[] guidBytes = new byte[16]; switch (guidType) { case SequentialGuidType.SequentialAsString: case SequentialGuidType.SequentialAsBinary: // For string and byte-array version, we copy the timestamp first, followed // by the random data. Buffer.BlockCopy(timestampBytes, 2, guidBytes, 0, 6); Buffer.BlockCopy(randomBytes, 0, guidBytes, 6, 10); // If formatting as a string, we have to compensate for the fact // that .NET regards the Data1 and Data2 block as an Int32 and an Int16, // respectively. That means that it switches the order on little-endian // systems. So again, we have to reverse. if (guidType == SequentialGuidType.SequentialAsString && BitConverter.IsLittleEndian) { Array.Reverse(guidBytes, 0, 4); Array.Reverse(guidBytes, 4, 2); } break; case SequentialGuidType.SequentialAtEnd: // For sequential-at-the-end versions, we copy the random data first, // followed by the timestamp. Buffer.BlockCopy(randomBytes, 0, guidBytes, 0, 10); Buffer.BlockCopy(timestampBytes, 2, guidBytes, 10, 6); break; } return new Guid(guidBytes); } /// <summary> /// Database type to generate GUIDs. /// </summary> public enum SequentialGuidDatabaseType { SqlServer, Oracle, MySql, PostgreSql, } /// <summary> /// Describes the type of a sequential GUID value. /// </summary> public enum SequentialGuidType { /// <summary> /// The GUID should be sequential when formatted using the /// <see cref="Guid.ToString()" /> method. /// </summary> SequentialAsString, /// <summary> /// The GUID should be sequential when formatted using the /// <see cref="Guid.ToByteArray" /> method. /// </summary> SequentialAsBinary, /// <summary> /// The sequential portion of the GUID should be located at the end /// of the Data4 block. /// </summary> SequentialAtEnd } }
using System; /// <summary> /// Extension methods to make locking easier. /// </summary> public static class LockExtensions { /// <summary> /// Executes given <paramref name="action"/> by locking given <paramref name="source"/> object. /// </summary> /// <param name="source">Source object (to be locked)</param> /// <param name="action">Action (to be executed)</param> public static void Locking(this object source, Action action) { lock (source) { action(); } } /// <summary> /// Executes given <paramref name="action"/> by locking given <paramref name="source"/> object. /// </summary> /// <typeparam name="T">Type of the object (to be locked)</typeparam> /// <param name="source">Source object (to be locked)</param> /// <param name="action">Action (to be executed)</param> public static void Locking<T>(this T source, Action<T> action) where T : class { lock (source) { action(source); } } /// <summary> /// Executes given <paramref name="func"/> and returns it's value by locking given <paramref name="source"/> object. /// </summary> /// <typeparam name="TResult">Return type</typeparam> /// <param name="source">Source object (to be locked)</param> /// <param name="func">Function (to be executed)</param> /// <returns>Return value of the <paramref name="func"/></returns> public static TResult Locking<TResult>(this object source, Func<TResult> func) { lock (source) { return func(); } } /// <summary> /// Executes given <paramref name="func"/> and returns it's value by locking given <paramref name="source"/> object. /// </summary> /// <typeparam name="T">Type of the object (to be locked)</typeparam> /// <typeparam name="TResult">Return type</typeparam> /// <param name="source">Source object (to be locked)</param> /// <param name="func">Function (to be executed)</param> /// <returns>Return value of the <paramnref name="func"/></returns> public static TResult Locking<T, TResult>(this T source, Func<T, TResult> func) where T : class { lock (source) { return func(source); } } }
使用方法：
for (int i = 0; i < 100; i++) {
Console.WriteLine(SequentialGuid.Instance.Create(SequentialGuid.SequentialGuidDatabaseType.SqlServer).ToString() + " " + i + " " + DateTime.Now.ToFileTime()); Thread.Sleep(1); }
注意：此处生成的基于sqlserver的guid在控制台里面查看并不是顺序的，但是在数据库中是可以进行顺序排序的，因为sqlserver对guid的排序是有特定规则的，具体可以参考下面的文章
https://www.cnblogs.com/tdfblog/p/SequentialGuid.html
- .Net中GUID的排序规则是从左到右依次进行排序，与数字排序规则一致；
- Sql Server数据库提供对GUID类型的支持，在数据库中称为
UniqueIdentifier类型，但是排序规则比较复杂：- 先按每1-8从左到右进行排序；
- 接着按第9-10位从右到左进行排序；
- 最后按后11-16位从右到左进行排序；
- Oracle数据库未提供对GUID类型的支持，使用的是raw bytes类型保存数据raw(16)，具体类型为，排序规则与GUID在.Net中规则一致；
- MySql数据未提供对GUID类型的支持，使用的是字符串的类型保存数据，使用是的char(36)类型，由于使用的是字符串类型，排序规则与GUID在.Net中的规则一致。
