| title | summary | aliases | ||
|---|---|---|---|---|
AUTO_RANDOM |
Learn the AUTO_RANDOM attribute. |
|
Since the value of AUTO_RANDOM is random and unique, AUTO_RANDOM is often used in place of AUTO_INCREMENT to avoid write hotspot in a single storage node caused by TiDB assigning consecutive IDs. If the current AUTO_INCREMENT column is a primary key and the type is BIGINT, you can execute the ALTER TABLE t MODIFY COLUMN id BIGINT AUTO_RANDOM(5); statement to switch from AUTO_INCREMENT to AUTO_RANDOM.
For more information about how to handle highly concurrent write-heavy workloads in TiDB, see Highly concurrent write best practices.
The AUTO_RANDOM_BASE parameter in the CREATE TABLE statement is used to set the initial incremental part value of auto_random. This option can be considered as a part of the internal interface. You can ignore this parameter.
AUTO_RANDOM is a column attribute that is used to automatically assign values to a BIGINT column. Values assigned automatically are random and unique.
To create a table with an AUTO_RANDOM column, you can use the following statements. The AUTO_RANDOM column must be included in a primary key, and the AUTO_RANDOM column is the first column in the primary key.
CREATE TABLE t (a BIGINT AUTO_RANDOM, b VARCHAR(255), PRIMARY KEY (a));
CREATE TABLE t (a BIGINT PRIMARY KEY AUTO_RANDOM, b VARCHAR(255));
CREATE TABLE t (a BIGINT AUTO_RANDOM(6), b VARCHAR(255), PRIMARY KEY (a));
CREATE TABLE t (a BIGINT AUTO_RANDOM(5, 54), b VARCHAR(255), PRIMARY KEY (a));
CREATE TABLE t (a BIGINT AUTO_RANDOM(5, 54), b VARCHAR(255), PRIMARY KEY (a, b));You can wrap the keyword AUTO_RANDOM in an executable comment. For more details, refer to TiDB specific comment syntax.
CREATE TABLE t (a bigint /*T![auto_rand] AUTO_RANDOM */, b VARCHAR(255), PRIMARY KEY (a));
CREATE TABLE t (a bigint PRIMARY KEY /*T![auto_rand] AUTO_RANDOM */, b VARCHAR(255));
CREATE TABLE t (a BIGINT /*T![auto_rand] AUTO_RANDOM(6) */, b VARCHAR(255), PRIMARY KEY (a));
CREATE TABLE t (a BIGINT /*T![auto_rand] AUTO_RANDOM(5, 54) */, b VARCHAR(255), PRIMARY KEY (a));When you execute an INSERT statement:
- If you explicitly specify the value of the
AUTO_RANDOMcolumn, it is inserted into the table as is. - If you do not explicitly specify the value of the
AUTO_RANDOMcolumn, TiDB generates a random value and inserts it into the table.
tidb> CREATE TABLE t (a BIGINT PRIMARY KEY AUTO_RANDOM, b VARCHAR(255)) /*T! PRE_SPLIT_REGIONS=2 */ ;
Query OK, 0 rows affected, 1 warning (0.01 sec)
tidb> INSERT INTO t(a, b) VALUES (1, 'string');
Query OK, 1 row affected (0.00 sec)
tidb> SELECT * FROM t;
+---+--------+
| a | b |
+---+--------+
| 1 | string |
+---+--------+
1 row in set (0.01 sec)
tidb> INSERT INTO t(b) VALUES ('string2');
Query OK, 1 row affected (0.00 sec)
tidb> INSERT INTO t(b) VALUES ('string3');
Query OK, 1 row affected (0.00 sec)
tidb> SELECT * FROM t;
+---------------------+---------+
| a | b |
+---------------------+---------+
| 1 | string |
| 1152921504606846978 | string2 |
| 4899916394579099651 | string3 |
+---------------------+---------+
3 rows in set (0.00 sec)
tidb> SHOW CREATE TABLE t;
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| t | CREATE TABLE `t` (
`a` bigint NOT NULL /*T![auto_rand] AUTO_RANDOM(5) */,
`b` varchar(255) DEFAULT NULL,
PRIMARY KEY (`a`) /*T![clustered_index] CLUSTERED */
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin /*T! PRE_SPLIT_REGIONS=2 */ |
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
tidb> SHOW TABLE t REGIONS;
+-----------+-----------------------------+-----------------------------+-----------+-----------------+---------------------+------------+---------------+------------+----------------------+------------------+------------------------+------------------+
| REGION_ID | START_KEY | END_KEY | LEADER_ID | LEADER_STORE_ID | PEERS | SCATTERING | WRITTEN_BYTES | READ_BYTES | APPROXIMATE_SIZE(MB) | APPROXIMATE_KEYS | SCHEDULING_CONSTRAINTS | SCHEDULING_STATE |
+-----------+-----------------------------+-----------------------------+-----------+-----------------+---------------------+------------+---------------+------------+----------------------+------------------+------------------------+------------------+
| 62798 | t_158_ | t_158_r_2305843009213693952 | 62810 | 28 | 62811, 62812, 62810 | 0 | 151 | 0 | 1 | 0 | | |
| 62802 | t_158_r_2305843009213693952 | t_158_r_4611686018427387904 | 62803 | 1 | 62803, 62804, 62805 | 0 | 39 | 0 | 1 | 0 | | |
| 62806 | t_158_r_4611686018427387904 | t_158_r_6917529027641081856 | 62813 | 4 | 62813, 62814, 62815 | 0 | 160 | 0 | 1 | 0 | | |
| 9289 | t_158_r_6917529027641081856 | 78000000 | 48268 | 1 | 48268, 58951, 62791 | 0 | 10628 | 43639 | 2 | 7999 | | |
+-----------+-----------------------------+-----------------------------+-----------+-----------------+---------------------+------------+---------------+------------+----------------------+------------------+------------------------+------------------+
4 rows in set (0.00 sec)The AUTO_RANDOM(S, R) column value automatically assigned by TiDB has a total of 64 bits:
Sis the number of shard bits. The value ranges from1to15. The default value is5.Ris the total length of the automatic allocation range. The value ranges from32to64. The default value is64.
The structure of an AUTO_RANDOM value with a signed bit is as follows:
| Signed bit | Reserved bits | Shard bits | Auto-increment bits |
|---|---|---|---|
| 1 bit | 64-R bits |
S bits |
R-1-S bits |
The structure of an AUTO_RANDOM value without a signed bit is as follows:
| Reserved bits | Shard bits | Auto-increment bits |
|---|---|---|
64-R bits |
S bits |
R-S bits |
- Whether a value has a signed bit depends on whether the corresponding column has the
UNSIGNEDattribute. - The length of the sign bit is determined by the existence of an
UNSIGNEDattribute. If there is anUNSIGNEDattribute, the length is0. Otherwise, the length is1. - The length of the reserved bits is
64-R. The reserved bits are always0. - The content of the shard bits is obtained by calculating the hash value of the starting time of the current transaction. To use a different length of shard bits (such as 10), you can specify
AUTO_RANDOM(10)when creating the table. - The value of the auto-increment bits is stored in the storage engine and allocated sequentially. Each time a new value is allocated, the value is incremented by 1. The auto-increment bits ensure that the values of
AUTO_RANDOMare unique globally. When the auto-increment bits are exhausted, an errorFailed to read auto-increment value from storage engineis reported when the value is allocated again. - Value range: the maximum number of bits for the final generated value = shard bits + auto-increment bits. The range of a signed column is
[-(2^(R-1))+1, (2^(R-1))-1], and the range of an unsigned column is[0, (2^R)-1]. - You can use
AUTO_RANDOMwithPRE_SPLIT_REGIONS. When a table is created successfully,PRE_SPLIT_REGIONSpre-splits data in the table into the number of Regions as specified by2^(PRE_SPLIT_REGIONS).
Note:
Selection of shard bits (
S):
- Since there is a total of 64 available bits, the shard bits length affects the auto-increment bits length. That is, as the shard bits length increases, the length of auto-increment bits decreases, and vice versa. Therefore, you need to balance the randomness of allocated values and available space.
- The best practice is to set the shard bits as
log(2, x), in whichxis the current number of storage engines. For example, if there are 16 TiKV nodes in a TiDB cluster, you can set the shard bits aslog(2, 16), that is4. After all regions are evenly scheduled to each TiKV node, the load of bulk writes can be uniformly distributed to different TiKV nodes to maximize resource utilization.Selection of range (
R):
- Typically, the
Rparameter needs to be set when the numeric type of the application cannot represent a full 64-bit integer.- For example, the range of JSON number is
[-(2^53)+1, (2^53)-1]. TiDB can easily assign an integer beyond this range to a column defined asAUTO_RANDOM(5), causing unexpected behaviors when the application reads the column. In such cases, you can replaceAUTO_RANDOM(5)withAUTO_RANDOM(5, 54)for signed columns, and replaceAUTO_RANDOM(5)withAUTO_RANDOM(5, 53)for unsigned columns, ensuring that TiDB does not assign integers greater than9007199254740991(2^53-1) to the column.
Values allocated implicitly to the AUTO_RANDOM column affect last_insert_id(). To get the ID that TiDB last implicitly allocates, you can use the SELECT last_insert_id () statement.
To view the shard bits number of the table with an AUTO_RANDOM column, you can execute the SHOW CREATE TABLE statement. You can also see the value of the PK_AUTO_RANDOM_BITS=x mode in the TIDB_ROW_ID_SHARDING_INFO column in the information_schema.tables system table. x is the number of shard bits.
After creating a table with an AUTO_RANDOM column, you can use SHOW WARNINGS to view the maximum implicit allocation times:
CREATE TABLE t (a BIGINT AUTO_RANDOM, b VARCHAR(255), PRIMARY KEY (a));
SHOW WARNINGS;The output is as follows:
+-------+------+---------------------------------------------------------+
| Level | Code | Message |
+-------+------+---------------------------------------------------------+
| Note | 1105 | Available implicit allocation times: 288230376151711743 |
+-------+------+---------------------------------------------------------+
1 row in set (0.00 sec)TiDB implicitly allocates values to AUTO_RANDOM columns similarly to AUTO_INCREMENT columns. They are also controlled by the session-level system variables auto_increment_increment and auto_increment_offset. The auto-increment bits (ID) of implicitly allocated values conform to the equation (ID - auto_increment_offset) % auto_increment_increment == 0.
Pay attention to the following restrictions when you use AUTO_RANDOM:
- To insert values explicitly, you need to set the value of the
@@allow_auto_random_explicit_insertsystem variable to1(0by default). It is not recommended that you explicitly specify a value for the column with theAUTO_RANDOMattribute when you insert data. Otherwise, the numeral values that can be automatically allocated for this table might be used up in advance. - Specify this attribute for the primary key column ONLY as the
BIGINTtype. Otherwise, an error occurs. In addition, when the attribute of the primary key isNONCLUSTERED,AUTO_RANDOMis not supported even on the integer primary key. For more details about the primary key of theCLUSTEREDtype, refer to clustered index. - You cannot use
ALTER TABLEto modify theAUTO_RANDOMattribute, including adding or removing this attribute. - You cannot use
ALTER TABLEto change fromAUTO_INCREMENTtoAUTO_RANDOMif the maximum value is close to the maximum value of the column type. - You cannot change the column type of the primary key column that is specified with
AUTO_RANDOMattribute. - You cannot specify
AUTO_RANDOMandAUTO_INCREMENTfor the same column at the same time. - You cannot specify
AUTO_RANDOMandDEFAULT(the default value of a column) for the same column at the same time. - When
AUTO_RANDOMis used on a column, it is difficult to change the column attribute back toAUTO_INCREMENTbecause the auto-generated values might be very large.