[Test Environment for TiDB] Testing
[TiDB Version] v5.4.2
[Encountered Issue: Problem Description and Impact]
When migrating data from MySQL to TiDB, is it necessary to modify the table structure? Should the primary key ID field be deleted? The main concern is that after migrating to TiDB, the data might not be evenly distributed.
It depends on your scenario. If you are worried about uneven distribution, it is likely that you have a high read demand. In that case, you should use a clustered table, which generates keys based on the table name and primary key, so a primary key is required. Without using partitioned tables, regions will automatically split and balance based on the data volume. Generally, for OLTP business, it is recommended to use clustered tables without partitioning.
It means that when migrating MySQL, there is no need to delete the auto-increment primary key of MySQL. TiDB will automatically balance the distribution of data across various nodes.
On the TiDB side, it is necessary to consider whether there will be write hotspots during migration and whether the business will involve write and read hotspots after migration. Because when migrating MySQL’s auto-increment ID to TiDB, if the TiDB side is a cluster type clustered table, the data will be allocated to regions according to the ID primary key. Subsequent inserts will then be sequentially inserted into one region, and when the region reaches 96MB, it will split, causing a write hotspot.
Therefore, you cannot simply migrate the auto-increment ID. Whether to delete it or not, or to migrate only other columns without the auto-increment ID, needs to be considered based on the business read and write operations on the business table. Additionally, try to minimize gRPC and reduce distributed transactions.
For core business tables with large data volumes and frequent access, you need to consider the business characteristics. For regular tables, there’s no need to bother.
|
username: TiDBer_5GvAkLi0
|
username: 
|
username: 
|
username: 
|
username: