Shulou(Shulou.com)06/03 Report--

In this issue, the editor will bring you about how the distributed unique ID in the SQLServer database is generated. The article is rich in content and analyzes and describes for you from a professional point of view. I hope you can get something after reading this article.

When we need to replicate automatically growing fields of data between multiple databases, it may cause primary key conflicts when data are merged. Imagine that when an Order table in one database replicates a database to an Order table in another library, should OrderID grow automatically?

Shortcomings of database self-growing ID and unordered UUID schemes:

1) it is troublesome to adopt database self-increasing sequence: data migration and merging.

2), UUID random number: use meaningless string, no sort UUID use string storage, query efficiency is relatively low when the amount of data is large. (mainly because index query sales are not the highest)

If you do not want to use non-autonomous growth columns as the primary key (in the distributed system sub-database sub-table), push to use ordered UUID and ordered whole-length Rowid (snowflake algorithm snowflake and MongoDB ObjectId).

Practical exercise of SQL Server distributed unique ID Generation

Unique ID can identify the uniqueness of data. There are many scenarios for generating unique ID in distributed systems, and there are probably three common ways:

2.1. rely on database, using SQL SERVER unordered UUID and ordered UUID.

1), unordered UUID:

SELECT newid ()-generates a 36-bit GUID

SELECT REPLACE (newid (),'-','')-- generate 32-bit GUID

2), ordered UUID:

SQLServer 2005 has solved this problem, using NEWSEQUENTIALID ()

Create table jobs

(

Id UNIQUEIDENTIFIER ROWGUIDCOL PRIMARY KEY NOT NULL

CONSTRAINT [DF_jobs_id] DEFAULT (NEWSEQUENTIALID ())

Account varchar (64) not null

Password varchar (64) not null

)

Go

Insert jobs (account,password) values ('tudou','123')

Insert jobs (account,password) values ('ntudou','123')

Insert jobs (account,password) values ('atudou','123')

Insert jobs (account,password) values ('btudou','123')

Insert jobs (account,password) values ('ctudou','123')

Select * from jobs

Reference:

SQL Server's key solution NEWID (), self-adding ID-Wang Zhanbo-blog Park https://www.cnblogs.com/wangzhanbo/articles/8807125.html

2.2. disordered random UUID and ordered UUID.

1), unordered UUID:

String guid = Guid.NewGuid () .ToString ()

String guid = Guid.NewGuid () .ToString ("N")

Disadvantages: poor index performance.

2), ordered UUID:

C # generates UUID (ordered GUID) Windows system

Https://www.cnblogs.com/lovewl2/p/10334987.html

C # generates ordered GUID codes according to time

Https://www.cnblogs.com/shiningrise/p/5690016.html

Several common generation methods of .NET Core distributed unique ID

Unique ID partition needs to be distinguished according to single application or distributed application. Especially in distributed systems, there are some scenarios that need to use globally unique ID. In this case, 36-bit UUID can be used to prevent ID conflicts, but UUID has some shortcomings, first of all, it is relatively long, and UUID is generally unordered. Sometimes we want to use a simpler ID and want the ID to be generated in an orderly manner.

1. Generate unique ID based on timestamp + random number

Based on the timestamp: DateTime.Now.ToString ("yyyyMMddHHmmssfffffff")-it is easy to have duplicate numbers in this case.

Based on timestamp + random number: DateTime.Now.ToString ("yyyyMMddHHmmssfffffff") + Random random number.

This method is more suitable for business systems with low concurrency for single applications, and the generation method is not the only ID in the strict sense.

2. The design of C# imitating Snowflake snowflake algorithm.

There is a saying that there are no two identical snowflakes in nature. Each snowflake has its own beautiful and unique shape and uniqueness. Snowflake algorithm also indicates that the generated ID is as unique as snowflake. Twitter's snowflake addresses this need.

Snowflake is a twitter open source distributed ID generation algorithm. Its core idea is: a long ID, in which 41bit is used as the millisecond, 10bit as the machine number, and 12bit as the sequence number within milliseconds. In theory, this algorithm can generate up to 1000 * (2 ^ 12), that is, 400W ID per second, which can fully meet the needs of the business.

About the components of the snowflake algorithm:

The snowflake algorithm generates a 64-bit binary data, which is a long type. (up to 19 bits in length after conversion to a string), its basic structure:

First place: for unused

Part 2: 41 bits are millisecond time (41 bits can be used for 69 years)

Part III: 5-bit datacenterId and 5-bit workerId (10-bit length supports deployment of up to 1024 nodes)

Part IV: the last 12 bits are counts in milliseconds (12-bit counting sequence numbers support each node to generate 4096 ID sequence numbers per millisecond)

The ID generated by snowflake is sorted by time increment as a whole, and there is no ID collision (distinguished by datacenter and workerId) in the whole distributed system, and it is more efficient. Snowflake has been tested to produce 260000 ID per second.

C# distributed self-increasing ID algorithm snowflake (Snowflake algorithm)-five-dimensional thinking-blog Garden

Https://www.cnblogs.com/zhaoshujie/p/12010052.html

3. C # imitates the distributed primary key ObjectId design of mongodb

The 12 bytes of _ id (ObjectId) in MongoDB are generated as follows

The first four are timestamps, which provide second-level uniqueness.

The next three digits are the unique identifier of the host, usually the hash value of the machine hostname.

The next two bits are the PID that produces the ObjectId, ensuring that the concurrently generated ObjectId on the same machine is unique.

The first nine bits ensure that the ObjectId generated by different processes on different machines in the same second is unique.

The last three bits are self-increment counters, ensuring that the ObjectId generated by the same process in the same second is unique.

This is how the distributed unique ID in the SQLServer database shared by the editor is generated. If you happen to have similar doubts, please refer to the above analysis to understand. If you want to know more about it, you are welcome to follow the industry information channel.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.