One of the chat series-Database primary key GUID

Yesterday a row of brothers because work transfer to send to a second-tier city work, although there is high wages and subsidies of temptation but also difficult to release from the Beijing Egg worry Mulberry, about good evening to old gather Point road individual, mainly chat (chui) Chat (chui) before (NIU) Cheng (bi), a few bottles to seize the green stick under the belly, The goods asked me about my current project situation (Fuck the technical control!) ), I simply said the shelves and database, sure enough in the database primary key on the divergence, in his heart the primary key is supposed to be a properly self-increment integer, for my GUID is not tolerated, and pointed out that the GUID is discontinuous, inefficient, blablabla, Finally I said my solution finally got a certain degree of affirmation ...

For the program ape, the database design is the eternal topic, and the primary key as the important element of the database has not received the due attention. This article only makes a simple comparison between the self-increment integer and the GUID, and the other composite, time-series and other forms of the primary key are not considered.

Definition of primary key (from Baidu know)

Primary key: A table often has a combination of columns or columns whose values uniquely identify each row in the table. Such a column or columns is called the table's primary key, which enforces the entity integrity of the table. When you create or change a table, you can create a primary key by defining the PRIMARY key constraint. A table can have only one PRIMARY key constraint, and a column in the PRIMARY key constraint cannot accept null values. Because the PRIMARY KEY constraint ensures unique data, it is often used to define identity columns.

Compare the self-increment primary key and GUID primary key (Welcome to add)

　　Self-increment primary key

　　Advantage:

Database is automatically generated without any management in the business

Saves memory space

High efficiency when writing and retrieving data

Disadvantage:

The importance of data consolidation and migration is easily problematic, depending on the project.

　　GUID PRIMARY key

Advantage:

It is OK to know the target ID at the business level, not the data submitted to the database system.

The benefits are obvious when the tables are divided.

It is very convenient in database merging, migrating and so on.

Disadvantage:

Occupy slightly larger storage space (almost negligible for current hardware-level Rdbs scenarios)

Generate unordered, the data is retrieved when the efficiency of the primary key is significantly lower than the self-increment (resolved by other means)

　　

Since my project is likely to escalate frequently and require high data persistence, choosing a GUID as the data primary key is, of course, a question of retrieval efficiency.

In fact, the real reason for inefficient GUID primary key retrieval is the aggregation index, when creating a data table, the system will automatically create a clustered index on the primary key, according to the implementation principle of the clustered index, the unordered GUID does not play the role of the clustered index, and the GUID does the clustered index may also cause the leaf splitting problem, Seriously eliminate the aggregation index on the GUID!

Perhaps you would consider whether there is an ordered GUID? The answer is YES! The build method for sequential GUIDs is built into C #, but only globally is guaranteed in the case of networking, and GUIDs that are generated when a server restart, system restart, and so on are not kept in a normal order with the GUID before the restart.

For clustered indexes I made some small changes here, first I removed the primary KEY constraint of the GUID primary key, set the non-null + uniqueness constraint, and for the purposes of the archive I had a "data generation Time" column in each of my tables, adding a clustered index that was originally a primary key to the class, which ensured that the clustered index worked.

　　

As a result of the temporary project is too tight, did not do the test data, theoretically feasible, and later have time to fill.