UUID as the primary key

The only database that I am still familiar with is mysql. Probably mysql users, more than 9% will use autoincrement ID as the primary key. This is understandable, because the auto-increment ID of MySQL is very efficient and convenient to use. So what do the remaining 1% users use as the primary key? It may be your own keygenerator, or the uuid we will talk about below.

It is said that in the Oracle circle, if the user uses auto-incrementing ID as the primary key is to be despised, the most natural choice of the primary key is UUID. I am not familiar with Oracle, and do not promise whether these conclusions are correct.

What is UUID first? In short, UUID refers to the number generated on a machine, which ensures that all machines in the same time and space are unique. In the uuid algorithm, information such as the nic mac address, IP address, host name, and process ID may be used to ensure its independence.

If your MySQL version is not old, type select UUID (); the output is UUID, as shown below:

Mysql> select UUID ();
+ -------------------------------------- +
| UUID () |
+ -------------------------------------- +
| 54b4c01f-dce0-102a-a4e0-462c07a00c5e |
+ -------------------------------------- +

Oracle> select sys_guid () from dual;

Now you should have a more intuitive understanding of UUID. Let's take a look at the advantages and disadvantages of UUID.

Advantages:

It can ensure independence, and the program can be migrated between different databases without affecting the effect.
Ensure that the generated IDs are not only table independent, but also database independent. This is especially important when you want to split the database.
During data migration in Oracle, the table sequence is not continuous.

Disadvantages:

Compared to the int type, it takes more space to store a uuid.
After UUID is used, the URL is lengthy and unfriendly.

The following is my opinion on the shortcomings of the uuid mentioned above. I am not very concerned about this shortcoming, but the most valuable one is the hard disk now. It is okay to skip this shortcoming, however, you must note that the efficiency of data indexing will decrease as the volume increases. As for the use of UUID, the URL seems unfriendly. I think this is the inertial Thinking Caused by your int complex. In fact, compared with the int type, UUID is the most natural primary key choice, note: I use the natural adjective here. I want to understand what you mean. In addition, in many cases, the URL itself does not need to be friendly. For example, for an e-commerce website, according to the INT-friendly URL, her order URL is probably in the following format:/order. PHP/ID/123. What I want to note is that this is very friendly, but some are very friendly, friendly, or even insecure. For example, I placed an order in the morning, the URL is/order. PHP/ID/1000. The URL for the next order in the evening is/order. PHP/ID/2000, then I can estimate that the number of orders for this website on a day is about 1000, and I can even estimate the sales volume of this website, these data are often important commercial secrets. There is no such concern when using UUID.

Efficiency?

If none of the above-mentioned UUID's so-called shortcomings are true, then whether to use UUID as the primary key is the only problem is efficiency. It is said that there are dedicated UUID types in PostgreSQL and other databases. In such a database, using UUID as the primary key has no efficiency problems. Unfortunately, such fields are not found in MySQL, if you want to save the uuid in MySQL as the primary key, it is generally simulated using char (36). Because it is not a native UUID type, how can the efficiency of the primary key be tested? In addition, the efficiency of UUID primary keys is also closely related to the uuid algorithm implementation.

In addition, for engines such as InnoDB that aggregate primary key types, data will be sorted by primary key. Due to the unordered UUID, InnoDB will generate a huge Io pressure, UUID is not suitable for the physical primary key. It can be used as the logical primary key. The physical primary key still uses the auto-increment ID.

I don't know how efficient it is to use UUID (char (36) for storage in MySQL, or binary (16) for primary key storage, sorry -_-!!!

Obtain UUID in Java

Import java. util. UUID;

Public class idgenerate {

 

Public static string getuuidstring (){
String id = UUID. randomuuid (). tostring ();
Id = ID. Replace ("-","");
Return ID;
}

Public static void main (string [] ARGs ){
// Todo auto-generated method stub
System. Out. println (idgenerate. getuuidstring ());
}

}

UUID as the primary key