InnoDB and UUID

CakePHP itself has a UUID implementation, so I've been experimenting with the possibility of using the UUID key. Although MySQL is my most commonly used database, but compared with the Auto_increment_int primary key, I am more interested in the UUID primary key, on the one hand because of the database independence of the UUID, on the other hand, when you want to spread the program on multiple servers, the UUID operation is simpler.

However, MySQL does not have native UUID support, and some problems may occur when mated to the InnoDB table type:

First, InnoDB will physically sort the primary key, which is good news for Auto_increment_int, because the primary key position of the next insertion is always the last. But for the UUID, this is bad news, because the UUID is disorganized, each insertion of the primary key position is indeterminate, may be at the beginning or in the middle, when the primary key physical ordering, it is bound to cause a lot of IO operation impact efficiency.

Fortunately, the first part of CakePHP's UUID algorithm's string is timestamp-based, so there is no problem with CakePHP's UUID, and if it is another UUID algorithm, this problem must be carefully considered.

Second, because the other indexes are associated with the primary key, when the primary key is the UUID, it is bound to take up more space than the int, and it is certainly more time-consuming to retrieve the larger space than to retrieve it in a smaller space.

The problem is not much to solve, the more common way is the primary key is still used Auto_increment_int to do, and add a UUID to do a unique index, the table foreign Key association, but also with the UUID, that is to say Auto_increment_ int is just a formal primary key, and the UUID is the de facto primary key, so that on the one hand the INT primary key does not waste much space, and on the other hand, it can continue to use the UUID.

Another problem is the use of UUID in MySQL, generally with char (36) To declare the field, if the column encoding is GBK/UTF8 such a complex encoding, will drag the efficiency of the primary key, this time, our field code to convert to ascii/latin1 such a simple encoding will be better.

Additional information: 5 ways to make hexadecimal identifiers perform better on MySQL

This article from Csdn Blog, reproduced please indicate the source: http://blog.csdn.net/zf213/archive/2009/06/06/4247435.aspx

InnoDB and UUID