©2020-2024 ioDraw All rights reserved
<> preface
I only knew before UUID Not suitable for primary key , But I don't know the specific reason , I learned about it recently , Make a record here
<> Indexes
Let's first understand mysql Data structure of index ,mysql stay innodb Is used under the storage engine of B+ Tree as the data structure of the index , And a table will eventually create a primary key index
, Even if the primary key is not set, there will be generation rules （ Find out if there is a non empty unique index first , If not, you can use the default rowid Field creation index ）
mysql The smallest unit in which data is stored is called page（ page ）, Size is 16KB, That is, in the index structure, a page Stored a lot of data , As you can see, the first block is stored 1,28,66 Three data
We know that the index adopts B+ After the structure of the tree , Data must be ordered
, If at this time page1 It's full ,page2 Half written , The next time the data is in order , Then the data will be appended directly to page2 End of .
If the data is out of order , Then the data may be inserted into page1 Somewhere in , But at this time page1 It's full , that page1 After inserting data in, the data must be moved back , The data that cannot be saved can only be put into others page, This happens
page Division and merger of , If at this time page2 It's also full , Then it will affect others in turn page, It will greatly affect the insertion and deletion of data , This is why the primary key index is not suitable for use UUID Reason for
Reasons why primary keys are not suitable for unordered fields ：
1, Unordered fields as indexes will cause page Division and merger of
2,UUID Waiting for data to occupy too many bytes , And self increasing int Type only needs to be occupied 4 Bytes , All indexes will eventually store the primary key in the leaf node id, It will take up more disk space and reduce io performance
Technology