java - Questions about the primary key of the table
过去多啦不再A梦2017-05-17 09:58:34
0
4
658
Most primary keys should only be integers (auto-incrementing) or system-generated unique codes (such as UUID); I would like to ask what are the pros and cons of each of these two, and hope to talk about actual experience.
Auto-incrementing ID saves storage space, and the primary key index does not have the problem of insertion and reordering. The disadvantage is that the amount of data is limited, and a maximum of 2^63 records can be stored. uuid is generally a string, which consumes more storage space than integer, and requires index reordering during insertion. In principle, there is no upper limit on the quantity.
Integer type (mysql’s index is saved in the form of a file, so the integer type must be smaller than UUID), and because it is an integer type, the index efficiency must be higher than UUID, but because it is automatically incremented, mysql will When inserting data, the table must be locked, which causes a large overhead on the MySQL server under a large amount of concurrency. And UUID is better than integer auto-increment in handling concurrency
When the field is the primary key, the integer type saves space than the string type (you should remember that int only requires 4 bytes, and char has one byte per character) When the field is not the primary key, in addition to saving space, the integer type saves more space than the string type The type is much faster, and it grows geometrically according to the character length
For those who pursue perfection, data such as IP addresses are also stored in the database using integers (IP address strings and integers have a fixed algorithm).
For example; if the value of a field is 1234567890 and there is no primary key When doing sql query where id >= '1234567890' int type only needs to be compared once char type needs to be compared 10 times, each character Everyone needs to participate in the comparison. So the longer the characters, the slower the speed
If the amount of data in the database is large enough, you can easily check the speed of processing char types by executing similar SQL where id like '123%' limit 5; where id like '1234%' limit 5; where id like ' 12345%' limit 5; where id like '123456%' limit 5; The execution speed of the above SQL is slower in turn. Because the longer the characters, the more times they are compared.
No matter the length of int type, it only compares once
![[Web front-end] Node.js quick start](https://img.php.cn/upload/course/000/000/067/662b5d34ba7c0227.png)
Auto-incrementing ID saves storage space, and the primary key index does not have the problem of insertion and reordering. The disadvantage is that the amount of data is limited, and a maximum of 2^63 records can be stored.
uuid is generally a string, which consumes more storage space than integer, and requires index reordering during insertion. In principle, there is no upper limit on the quantity.
Integer type (mysql’s index is saved in the form of a file, so the integer type must be smaller than UUID), and because it is an integer type, the index efficiency must be higher than UUID, but because it is automatically incremented, mysql will When inserting data, the table must be locked, which causes a large overhead on the MySQL server under a large amount of concurrency. And UUID is better than integer auto-increment in handling concurrency
uuid supports sub-library
When the field is the primary key, the integer type saves space than the string type (you should remember that int only requires 4 bytes, and char has one byte per character)
When the field is not the primary key, in addition to saving space, the integer type saves more space than the string type The type is much faster, and it grows geometrically according to the character length
For those who pursue perfection, data such as IP addresses are also stored in the database using integers (IP address strings and integers have a fixed algorithm).
For example; if the value of a field is 1234567890 and there is no primary key
When doing sql query where id >= '1234567890'
int type only needs to be compared once
char type needs to be compared 10 times, each character Everyone needs to participate in the comparison. So the longer the characters, the slower the speed
If the amount of data in the database is large enough, you can easily check the speed of processing char types by executing similar SQL
where id like '123%' limit 5;
where id like '1234%' limit 5;
where id like ' 12345%' limit 5;
where id like '123456%' limit 5;
The execution speed of the above SQL is slower in turn. Because the longer the characters, the more times they are compared.
No matter the length of int type, it only compares once