AUTO_RANDOMNew in v3.1.0

User scenario

Since the value ofAUTO_RANDOMis random and unique,AUTO_RANDOMis often used in place ofAUTO_INCREMENTto avoid write hotspot in a single storage node caused by TiDB assigning consecutive IDs. If the currentAUTO_INCREMENTcolumn is a primary key and the type isBIGINT, you can execute theALTER TABLE t MODIFY COLUMN id BIGINT AUTO_RANDOM(5);statement to switch fromAUTO_INCREMENTtoAUTO_RANDOM.

For more information about how to handle highly concurrent write-heavy workloads in TiDB, seeHighly concurrent write best practices.

TheAUTO_RANDOM_BASEparameter in the创建TABLEstatement is used to set the initial incremental part value ofauto_random. This option can be considered as a part of the internal interface. You can ignore this parameter.

Basic concepts

AUTO_RANDOMis a column attribute that is used to automatically assign values to aBIGINTcolumn. Values assigned automatically arerandomandunique.

To create a table with anAUTO_RANDOMcolumn, you can use the following statements. TheAUTO_RANDOMcolumn must be included in a primary key, and theAUTO_RANDOMcolumn is the first column in the primary key.


              
               创建
               TABLEt (aBIGINTAUTO_RANDOM, bVARCHAR(255),PRIMARYKEY (a));创建
               TABLEt (aBIGINT
               PRIMARYKEY AUTO_RANDOM, bVARCHAR(255));创建
               TABLEt (aBIGINTAUTO_RANDOM(6), bVARCHAR(255),PRIMARYKEY (a));创建
               TABLEt (aBIGINTAUTO_RANDOM(5,54), bVARCHAR(255),PRIMARYKEY (a));创建
               TABLEt (aBIGINTAUTO_RANDOM(5,54), bVARCHAR(255),PRIMARYKEY (a, b));

You can wrap the keywordAUTO_RANDOMin an executable comment. For more details, refer toTiDB specific comment syntax.


              
               创建
               TABLEt (abigint
               /*T![auto_rand] AUTO_RANDOM */, bVARCHAR(255),PRIMARYKEY (a));创建
               TABLEt (abigint
               PRIMARYKEY/*T![auto_rand] AUTO_RANDOM */, bVARCHAR(255));创建
               TABLEt (aBIGINT
               /*T![auto_rand] AUTO_RANDOM(6) */, bVARCHAR(255),PRIMARYKEY (a));创建
               TABLEt (aBIGINT
               /*T![auto_rand] AUTO_RANDOM(5, 54) */, bVARCHAR(255),PRIMARYKEY (a));

当你执行一个INSERTstatement:

如果你明确specify the value of theAUTO_RANDOMcolumn, it is inserted into the table as is.
If you do not explicitly specify the value of theAUTO_RANDOMcolumn, TiDB generates a random value and inserts it into the table.


              
               tidb>
               创建
               TABLEt (aBIGINT
               PRIMARYKEY AUTO_RANDOM, bVARCHAR(255)); Query OK,0
               rowsaffected,1warning (0.01sec) tidb>
               INSERT
               INTOt(a, b)VALUES(1,'string'); Query OK,1
               rowaffected (0.00sec) tidb>
               SELECT
               *
               FROMt;+
               ---+--------+
               |a|b|
               +
               ---+--------+
               |
               1
               |string|
               +
               ---+--------+
               1
               row
               in
               set(0.01sec) tidb>
               INSERT
               INTOt(b)VALUES('string2'); Query OK,1
               rowaffected (0.00sec) tidb>
               INSERT
               INTOt(b)VALUES('string3'); Query OK,1
               rowaffected (0.00sec) tidb>
               SELECT
               *
               FROMt;+
               ---------------------+---------+
               |a|b|
               +
               ---------------------+---------+
               |
               1
               |string|
               |
               1152921504606846978
               |string2|
               |
               4899916394579099651
               |string3|
               +
               ---------------------+---------+
               3
               rows
               in
               set(0.00sec)

TheAUTO_RANDOM(S, R)column value automatically assigned by TiDB has a total of 64 bits:

S是数量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 anAUTO_RANDOMvalue is as follows:

Total number of bits	Sign bit	Reserved bits	Shard bits	Auto-increment bits
64 bits	0/1 bit	(64-R) bits	S bits	(R-1-S) bits

The length of the sign bit is determined by the existence of anUNSIGNEDattribute. If there is anUNSIGNEDattribute, the length is0. Otherwise, the length is1.
The length of the reserved bits is64-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 specifyAUTO_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 ofAUTO_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.

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 aslog(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, theRparameter 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 outside this range to a column ofAUTO_RANDOM(5), causing unexpected behaviors when the application reads the column. In this case, you can replaceAUTO_RANDOM(5)withAUTO_RANDOM(5, 54)and TiDB does not assign an integer greater than9007199254740991(2^53-1) to the column.

Values allocated implicitly to theAUTO_RANDOMcolumn affectlast_insert_id(). To get the ID that TiDB last implicitly allocates, you can use theSELECT last_insert_id ()statement.

To view the shard bits number of the table with anAUTO_RANDOMcolumn, you can execute theSHOW CREATE TABLEstatement. You can also see the value of thePK_AUTO_RANDOM_BITS=xmode in theTIDB_ROW_ID_SHARDING_INFOcolumn in theinformation_schema.tablessystem table.x是数量of shard bits.

After creating a table with anAUTO_RANDOMcolumn, you can useSHOW WARNINGSto view the maximum implicit allocation times:


              
               创建
               TABLEt (aBIGINTAUTO_RANDOM, bVARCHAR(255),PRIMARYKEY (a));SHOWWARNINGS;

The output is as follows:


              
               +
               -------+------+---------------------------------------------------------+
               |水平|Code|Message|
               +
               -------+------+---------------------------------------------------------+
               |Note|
               1105
               |Available implicit allocation times:288230376151711743
               |
               +
               -------+------+---------------------------------------------------------+
               1
               row
               in
               set(0.00sec)

Restrictions

Pay attention to the following restrictions when you useAUTO_RANDOM:

To insert values explicitly, you need to set the value of the@@allow_auto_random_explicit_insertsystem variable to1(0by default). It isnotrecommended 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 columnONLYas theBIGINTtype. 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 toclustered index.
You cannot useALTER TABLEto modify theAUTO_RANDOMattribute, including adding or removing this attribute.
You cannot useALTER 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 withAUTO_RANDOMattribute.
You cannot specifyAUTO_RANDOMandAUTO_INCREMENTfor the same column at the same time.
You cannot specifyAUTO_RANDOMandDEFAULT(the default value of a column) for the same column at the same time.
WhenAUTO_RANDOMis used on a column, it is difficult to change the column attribute back toAUTO_INCREMENTbecause the auto-generated values might be very large.

Download PDF Request docs changes Ask questions on Discord

Playground

New

One-stop & interactive experience of TiDB's capabilities WITHOUT registration.