MySQL autoincrement ID, UUID and snowflake ID

The concept is introduced

Three primary keys

Self increasing id ：1 2 3 4 5……
uuid ：UUID yes Universally Unique Identifier Abbreviation , It is within a certain range （ From a specific namespace to the world ） Unique machine generated identifier . The meaning of universal unique identifier , It can be based on business practice user id Primary key such as QQ Number Phone number etc.
snow id ： comparison UUID Disorderly id for , The snowflake algorithm is ordered （ With time parameters ）, And they are all composed of numbers . snow id The maximum is 64 position , accord with java in long The length of 64 position . Suitable for large-scale distributed

Clustered index and non clustered index

Self increasing id

The values of the self increasing primary key are sequential , therefore Innodb Store each record at the end of a record . When the maximum fill factor of the page is reached (innodb The default maximum fill factor is page size 15/16, Will be set aside 1/16 Space for future modify )：
① The next record will be written to the new page , Once the data is loaded in this order , The primary key page will be almost filled with records in sequence , Improved the maximum fill rate of the page , There will be no waste of pages
② The newly inserted row must be next to the original maximum data row ,mysql Location and addressing are fast , There is no additional cost to calculate the position of the new row
③ Reduced page fragmentation and fragmentation

advantage ：
1. Self increasing , The trend is self increasing , It can be used as a clustered index , Improve query efficiency
2. Save disk space .500W data ,UUID Occupy 5.4G, Self increasing ID Occupy 2.5G.
3. Inquire about , High write efficiency ： The query is slightly better . When there is a large amount of data higher than uuid Insertion speed

shortcoming ：
1. When importing old data , May be ID repeat , Causes the import to fail .
2. Distributed architecture , Multiple Mysql Instances may lead to ID repeat .
3. Easy to be broken by the outside , Know the actual situation of the business . And for example ： Show the announcement index?id=3 In this way, it is easy to be tampered with as index?id=2. You can adjust to the content of Article 2 .
4 For high concurrency loads ,innodb When inserting by primary key, there will be obvious lock contention , The upper bound of the primary key will become a hot topic of contention , Because all the insertions happen here , Concurrent inserts can lead to gap lock contention .Auto_Increment The lock mechanism will cause the preemption of self increasing lock , There is a certain loss of performance

uuid

Look at the shortcomings above

snow id