What are the differences between TiDB and MySQL when designing Schema primary keys?

This topic has been translated from a Chinese forum by GPT and might contain errors.

Original topic: TiDB和MySQL在设计Schema主键的时候,有什么区别

| username: redgame

What are the differences between TiDB and MySQL when designing Schema primary keys?

| username: xingzhenxiang | Original post link

It is not recommended to use auto-increment primary keys due to hotspot data.

| username: xfworld | Original post link

Refer to this article, it is more comprehensive:

专栏 - 【TiDB 最佳实践系列】开发 Java 应用使用 TiDB 的最佳实践 | TiDB 社区

| username: 南征北战 | Original post link

MySQL recommends using auto-increment primary keys, while distributed systems should use Snowflake algorithm for primary keys.

| username: linnana | Original post link

TiDB auto-increment columns generally use bigint, which consumes much more compared to standalone MySQL.

| username: zhanggame1 | Original post link

The differences between TiDB and MySQL in terms of auto-increment are quite noticeable. To ensure basic continuity, you need to add the AUTO_ID_CACHE 1 parameter when creating a table. In newer versions, the performance of the AUTO_ID_CACHE 1 parameter is acceptable.

Each table can only have one AUTO_INCREMENT, and the auto-increment ID is pre-allocated to all TiDB servers in ID segments (default is 30,000 per segment, configurable). For example, with 2 TiDB servers, when a table is first created, TiDB1 caches [1,30000] and TiDB2 caches [30001,60000]. Thus, if two sessions connect to the two TiDB servers and each writes a record, one will have id=1 and the other id=30001. The same table on the same TiDB server can ensure monotonic auto-increment, meaning that records written through connections already established on TiDB1 will have continuous IDs.

AUTO_INCREMENT | PingCAP Documentation Center

To prevent write hotspots, you can generally use AUTO_RANDOM to handle auto-increment primary key hotspot tables. This is suitable for replacing auto-increment primary keys and solving the write hotspot issue caused by auto-increment primary keys.