Does uuid use auto-increment as the primary key?

I have been searching for it online for a long time. Some people say that uuid is better, that is, it is easier to merge data into sub-databases and sub-tables, and some people say that auto-increment is better, when there is more data in the table, the performance is much better than the uuid. What is better? Are there any great gods in the real production environment to give a complete... I have been searching for it online for a long time. Some people say that uuid is better, that is, it is easier to merge data into sub-databases and sub-tables, and some people say that auto-increment is better, when there is more data in the table, the performance is much better than the uuid. What is better? Is there a complete answer to the question given by the great gods in the real production environment?

Reply content:

I have been searching for it online for a long time. Some people say that uuid is better, that is, it is easier to merge data into sub-databases and sub-tables, and some people say that auto-increment is better, when there is more data in the table, the performance is much better than the uuid. What is better? Is there a complete answer to the question given by the great gods in the real production environment?

FirstUUIDPerformance is not comparableAuto-increment IDDepending onUUIDGeneration algorithm. For exampleMongoDBTheObjectIdIs a relatively goodUUIDPolicy, which is composedTimestamp + machine code + process code + auto IncrementThe machine code and process code can be generated at one time.ObjectIdRatio onlyAuto-increment IDA timestamp is added. In addition, consideringAuto-increment IDMake a unique primary key index, andUUIDYou can only make an index, not make a unique index (using its features, you can leave it alone), and its performance can be said to be no betterAuto-increment IDPoor.

For usageUUIDOrAuto-increment IDIt mainly depends on whether the project is large enough and whether the data volume is large enough. ConvenienceAuto-increment IDIt is easy to use and does not require additional support.UUIDRelatively troublesome, involvingUUIDAlgorithm selection, program embedding, and so on. In terms of the effect of coping with a large system,UUIDRatioAuto-increment IDIt looks much better. You can choose one based on your actual situation.

In the Oracle environment (no use experience in other databases ):
In the long run, the occupied space of the auto-increment Number is larger than that of the UUID (raw (16) occupies 32 bytes). However, when the data volume of a table is smaller than tens of billions of bytes, the occupied space of the number is still less;
UUID is not easy to use. When the UUID is stored as raw (16), the HEXTORAW conversion is required for the query condition;
When the number of rows is the same, the UUID-based index occupies a larger space than the number. When the data volume goes up, the number of disk reads will certainly increase;
In a concurrent or rac cluster environment, UUID performance is higher than number because of hot blocks (reverse indexing can solve this problem );
UUID is safer to use for front-end display. You can guess the Number.

1. If this id is exposed to the user (part of the URL), the auto-increment ID is more friendly.

2. The two performance gaps are not big.

3. UUID is much easier to develop than auto-increment ID.

   • UUID can resolve the distributed ID conflict and the database auto-increment ID is not supported.

   • UUID can be obtained before it is submitted to the database. No more select statements are required.

   • Not all databases support auto-incremental IDs.

4. UUID can be compressed to 12-16 characters in length with base64 instead of the standard 36-bit length.

5. The UUID scheme is preferred. If a more friendly URL is needed, sequence should also be used instead of auto-incremental ID.

Most of the above answers compare the uuid generation efficiency from the developer's perspective. In the case of database access efficiency, if InnoDB or TokuDB is used, the answer is the auto-incrementing primary key. Let's look at this answer from Oracle ACE:
Why is it recommended that the auto-incrementing column be used as the primary key in the InnoDB table?

Take as needed, analyze specific issues, simply put, generally 32-byte UUID can be universal

Auto-increment has advantages in indexing (the comparison of Integer type is faster than that of string type), but its advantages are limited. The main difference between the two lies in that the id is generated on the client/server side. Compared with mysql, java is a client. Uuid can ensure that the client generates a unique and unique id, which is very important in distributed systems.

If you are not sure, I suggest you use uuid.

This depends on the selection of your underlying storage. Oracle is not familiar with it. It uses rowid at the underlying layer, so it does not comment. Other features similar to MongoDB have their own characteristics, so it is appropriate to use UUID.

However, if your underlying storage uses MySQL and the storage engine happens to be Innodb, I highly recommend that you use the only auto-increment number as your primary key;

First, we will not discuss the number of commands and CPU computing cycles corresponding to the UUID and long data during CPU computing. The most important thing is that the bottom-layer storage of Innodb is B + Tree, A clustered index is required. All data query operations are performed based on the clustered index. (If a primary key is designed for the table, this primary key is selected by default for the clustered index)

If the data writing sequence of the InnoDB table can be consistent with the leaf node sequence of the B + tree index, the access efficiency is the highest at this time.

Of course, if your system does not pursue performance limits

Why don't I customize my primary key policy?

Mysql uses auto-increment, otherwise innodb also needs to maintain its own primary key index.

If it is a single machine, we recommend that you use the auto-incremental id of mysql.

In a distributed environment, if you want to maintain a globally unique id, neither auto-increment nor uuid will work.
In this case, you need to customize an id generator mechanism.

Note]
I have encountered GUID duplication. Therefore, using GUID directly as the unique primary key is potentially problematic!

(GUID is an implementation of the UUID Standard)

This feeling depends on specific scenarios. If it is just a database server, and the business logic is not complex, you can use the auto-incremental ID Method for simple development; if there are only a few primary database servers, you can also use auto-incremental IDs, but some processing is required.

For distributed databases, that is, when multiple primary database servers use auto-incrementing IDs, the development becomes more complex and the auto-incrementing IDs become more and more difficult to control. In this case, why not use UUID, after all, the performance of both is not very poor.