Generate a primary key ID for a data table from Redis, and a primary key ID for redis

Generate a primary key ID for a data table from Redis, and a primary key ID for redis

For the global ID (primary key) of MySql, we generally use the auto-incrementing integer column, the GUID generated by the program, and a separate table as the ID generator. These solutions have their own advantages and disadvantages, the final efficiency cannot be said to be very satisfactory (especially in the case of massive data). In fact, the INCR of Redis can easily generate auto-increment numbers. Because it is a cache operation, the generation efficiency is also good.

The number of primary keys inserted into the database is also increasing continuously. In combination with indexes, the reading efficiency is also high.

The following code retrieves a new auto-increment value from Redis:

Public sealed class Utils {private static readonly object sequence_locker = new object (); /// <summary> /// obtain an auto-incrementing sequence ID from Redis /// </summary> /// <param name = "key"> key name </param> /// <param name = "getting"> obtain the sequence ID substitution generation method (if it does not exist in the cache) </param> public static int NewSequenceFromRedis (string key, Func <int> alternative) {if (string. isNullOrEmpty (key) throw new ArgumentNullException ("key"); lock (sequence_locker ){ RedisHelper redis = new RedisHelper (1); // in db1 long value = redis. stringIncrement (key, 1); if (value> Int32.MaxValue | value <Int32.MinValue) throw new OverflowException ("The sequence overflow. "); if (value <= 1 & alternative! = Null) {value = alternative (); redis. StringSet (key, value. ToString (); // update} return (int) value ;}}}

My project uses the Repository mode, so the method for getting the new primary key is written to the Repository parent class (defined in the IRepository Interface), so that each Repository can reload the attribute TableName, of course, you can use NewIdentity as a public method, as long as TableName is passed in.

Public abstract class RepositoryBase: IRepository {protected IDbConnection _ db; public RepositoryBase (IDbConnection connection) {_ db = connection;} protected virtual string TableName {get;} public virtual int NewIdentity () {if (string. isNullOrEmpty (this. tableName) throw new NoNullAllowedException ("TableName is null. "); var redisKey = $" Sequence _ {TableName }. id "; // eg. sequence_lottery.Id, Sequence_player.Id var id = Utils. newSequenceFromRedis (redisKey, () =>{ // if the primary key ID is not obtained from Redis (for example, the Redis key is deleted), replace return _ db with the maximum table ID + 1. executeScalar <int> ("select max (id) AS MaxId FROM" + TableName) + 1 ;}); return id ;}}

The following is the test code and StopWatch is used to test the efficiency of each execution:

Using (var ctx = DI. resolve <IRepositoryContext> () {System. diagnostics. stopwatch sw = System. diagnostics. stopwatch. startNew (); var userId = ctx. resolve <IUserRepository> (). newIdentity (); sw. stop (); Console. writeLine ("userId = {0}, elapsed: {1} ms", userId, sw. elapsedMilliseconds); sw. restart (); var gameId = ctx. resolve <IGameRepository> (). newIdentity (); sw. stop (); Console. writeLine ("gameId = {0}, elapsed: {1} ms ", gameId, sw. elapsedMilliseconds); sw. restart (); var roomId = ctx. resolve <IGameRepository> (). newRoomIdentity (); sw. stop (); Console. writeLine ("roomId = {0}, elapsed: {1} ms", roomId, sw. elapsedMilliseconds); sw. restart (); var betItemId = ctx. resolve <IGameRepository> (). newBetItemIdentity (); sw. stop (); Console. writeLine ("betItemId = {0}, elapsed: {1} ms", betItemId, sw. elapsedMilliseconds); sw. resta Rt (); var lotteryId = ctx. resolve <ILotteryRepository> (). newIdentity (); sw. stop (); Console. writeLine ("lotteryId = {0}, elapsed: {1} ms", lotteryId, sw. elapsedMilliseconds); // omitted code ...}

The running result is as follows. Except for the first time to get the primary key, the overhead is 98 milliseconds (it is estimated that the StopWatch Initialization is performed), and the latter is almost 0 milliseconds (Redis was originally fast, so you do not need to consider the time consumption of database connection opening/closing)

 

View the key values in Redis:

 

Of course, the Code still needs to be improved. For example, if Redis crashes, the ID Primary Key can read MAX (ID) + 1 to replace the primary key generation. However, after Redis recovers, how can we synchronize the auto-increment?