The difference and advantages and disadvantages of Mysql primary key UUID and auto-increment primary key

introduction

I used postgresql database for some time. After moving to the cloud, I changed from auto-increment primary key to uuid. I feel that uuid is globally unique and very convenient.

I recently used MySQL and found that all MySQL primary keys are auto-increment primary keys. After careful comparison, I found out why MySQL chooses auto-increment primary keys and what are the differences.

Before MySQL 5.0, if there are multiple master replication environments, auto-increment primary keys cannot be used because they may be duplicated. In versions 5.0 and later, the whole problem is solved by configuring an auto-increment offset.

Under what circumstances do we want to use uuid

1. Avoid duplication and facilitate scale. This is the main reason why we choose UUID when building cloud services.

2. You can know the ID before entering the warehouse

3. Relatively safe. You cannot simply get information from uuid. However, if it is self-incremented, it is easy to expose information. If a customer ID is 123456, it is easy to guess that there is a customer ID of 123456.

What's wrong with UUID

1. UUID has 16 bytes, which takes up more storage space than int (4 bytes) and bigint (8 bytes)

2. Due to size and disorder, performance issues may occur

Mysql uuid principle

The way MySQL's InnoDB storage engine handles storage is through clustered indexes.

A clustered index is when the physical order of the data in the database table rows is the same as the logical (index) order of the key values. A table can only have one clustered index because the physical order of a table can only be one case.

1. Why use uuid as primary key

(1) In fact, under the InnoDB storage engine, the performance of the self-incrementing ID as the primary key has reached the best. Both storage and reading speeds are the fastest, and the storage space occupied is the smallest.

(2) However, we will encounter problems in the actual project. The primary key ID of the historical data table will be repeated with the ID of the data table. When two tables with auto-increment IDs as primary keys are merged, there will definitely be ID conflicts. However, if the respective IDs are also associated with other tables, this will be very difficult to operate.

(3) If UUID is used, the generated ID is not only table-independent but also database-independent. It is very beneficial for future data operations and can be said to be a one-time solution.

2. Advantages and disadvantages of UUID

Disadvantages: 1. Affects the insertion speed and causes low hard disk utilization

2. Comparing the size of UUIDs is much slower than comparing numbers, which affects the query speed.

3. UUID takes up a lot of space. The more indexes you create, the more serious the impact will be.

Advantages: When data is split and merged for storage, global uniqueness can be achieved

3. Best solution

(1). InnoDB engine table is an index-organized table based on B+ tree.

(2) B+ tree: B+ tree is a balanced search tree designed for disks or other direct access auxiliary devices. In the B+ tree, all record nodes are stored in the leaf nodes of the same layer in the order of the key value, and the leaf node pointers are connected.

(3). InnoDB primary index: The leaf node contains complete data records. This type of index is called a clustered index. InnoDB indexes provide a very fast primary key lookup performance. However, its secondary indexes will also contain the primary key columns, so if the primary key is defined to be large, other indexes will also be large. If you want to define many indexes on a table, try to define the primary key as small as possible. InnoDB does not compress indexes

(4) This implementation method of clustered index makes the search by primary key very efficient, but the auxiliary index search requires two index searches: first search the auxiliary index to obtain the primary key, and then use the primary key to search the primary index to obtain the record.

Based on the above, we can get:

(1) If the data writing order of the InnoDB table is consistent with the leaf node order of the B+ tree index, the access efficiency is the highest. For storage and query performance, you should use the self-incrementing id as the primary key.

(2) For the primary index of InnoDB, the data will be sorted according to the primary key. Due to the disorder of UUID, InnoDB will generate huge IO pressure. At this time, it is not suitable to use UUID as the physical primary key. It can be used as a logical primary key. The physical primary key still uses the auto-increment ID. For global uniqueness, uuid should be used as an index to associate other tables or as a foreign key.

4. If you must use UUID as the primary key, here are some suggestions:

If it is a master-slave or MS mode, it is best not to use the MySQL built-in uuid function to generate a unique primary key, because when the uuid generated by the master table is associated with the slave table, it is necessary to go to the database to find out the uuid, which requires one more database interaction, and during this time difference, the master table is likely to generate data, which can easily lead to errors in the associated uuid. If you really want to use uuid, you can generate it in Java and store it directly in the DB. At this time, the uuid of the master and slave will be the same!

Supplement: MySQL's uuid() primary key is repeated

1. The uuid() primary key of mysql is repeated

MySQL uses the navicat client and once executed the following sql

select replace(uuid(), '-', '') as id, u.user_id from t_user u;

It turned out that the generated uuid was repeated.

After investigation, it was found that it was a problem with Navicat. The SQL statement needed to be adjusted as follows:

select replace(convert(uuid() using utf8mb4), '-', ''), u.user_id from t_user u;

The results are as follows:

2. Use other solutions:

Perform md5 again on the uuid:

select md5(uuid()) as id, u.user_id from t_user u;

The above is my personal experience. I hope it can give you a reference. I also hope that you will support 123WORDPRESS.COM. If there are any mistakes or incomplete considerations, please feel free to correct me.