Note:
This topic has been translated from a Chinese forum by GPT and might contain errors.Original topic: 自增主键带来的热点
|
username: TiDBer_lVKhiLUB
[TiDB Usage Environment] Production Environment
[TiDB Version] 6.X version
[Encountered Problem: Using TiDB cluster’s auto-increment ID, hotspot data appeared. How to persistently solve the hot read/write issue without changing the auto-increment ID condition?
[Resource Configuration]
[Attachment: Screenshot/Log/Monitoring]
The official documentation provides a solution. You can take a look at it:
The official website recommends changing the auto-increment ID to AUTO_RANDOM. The business relies on this auto-increment ID.
The documentation recommends two solutions: one is to change the auto-increment ID to AUTO_RANDOM, and the other is to set SHARD_ROW_ID_BITS, which can scatter the RowID into multiple different Regions to alleviate the write hotspot issue.
Does not meet the requirements.
The table has an auto-increment primary key id.
For a clustered index table, tidb_rowid is the primary key of your table.
Auto-increment primary keys will definitely have write hotspots.
Would changing to a non-clustered index table be very costly?
Additionally, this will only cause write hotspots; reads should not form hotspots.
The official recommendation is to use AUTO_RANDOM to handle hot tables with auto-increment primary keys. Course 302 also has corresponding handling cases. 
Thumbs up to Boss Dong Fei.
If you want to keep the auto-increment primary key, you can change it to a non-clustered table and specify SHARD_ROW_ID_BITS to distribute the data, for example:
|
username: 
|
username: 
|
username: 
|
username: CREATE TABLE `t2_nonclustered_autoinc` (
`id` bigint(20) NOT NULL PRIMARY KEY NONCLUSTERED AUTO_INCREMENT,
`id2` bigint(20) DEFAULT NULL,
`name` char(255) DEFAULT NULL,
`varname` char(200) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin SHARD_ROW_ID_BITS = 4 PRE_SPLIT_REGIONS=3;
A clustered index cannot be directly changed to a non-clustered index; a new table must be created for migration, which is very costly. If the business does not have queries that scan based on the id key, the simplest solution is to change it to AUTO_RANDOM, which can be done directly.
The main changes theoretically improve and address some hotspot issues, but the actual effect may be average.
Pre-split the table into multiple regions
|
username: 
|
username: 
|
username: 
|
username: SPLIT TABLE TEST_HOTSPOT BETWEEN (0) AND (9223372036854775807) REGIONS 128;
Non-clustered index writes involve an additional index write I/O besides the data write.