An overview of the

  • Compare the advantages and disadvantages of using UUDI and auto-increment primary keys

UUID

  • Disadvantages:
    • IO: UUID Because the primary key is generated randomly, the insertion location is not in memory cache, which will generate a large number of random I/OS
    • Page splitting: Since inserts are out of order and data stored on Innodb pages is ordered, it frequently causes leaf splitting, which moves a large amount of data
    • Large disk footprint: New pages may not be inserted for a long time after they are generated
  • Advantages:
    • Suitable for distribution

Since the primary key

  • advantages
    • IO: auto-increment primary key is suitable for insertion, there is a high probability that disk IO will not be generated, the page is already in memory
    • Page splitting: greatly reduces the generation of page splitting. Because it is increasing, when a page reaches the maximum fill factor (15/16), new pages will be generated automatically, and no data movement will occur
    • Disk: very compact, each page is almost full before the next page is written
  • disadvantages
    • This is harder to implement in distributed situations
    • So the solution is, multiple machines, varying the number of increments, 2 machines, each incrementing by +2

compromise

  • Snowflakes algorithm
  • Snowflake algorithm can keep auto-increment by timestamp in distributed case, and can set different serial number for different machine, similar to auto-increment primary key
  • Disadvantages:
    • Time synchronization is tricky