For each new table created in TiDB, one Region is segmented by default to store the data of this table. This default behavior is controlled by
split-table in the configuration file. When the data in this Region exceeds the default Region size limit, the Region starts to split into two.
In the above case, because there is only one Region at the beginning, all write requests occur on the TiKV where the Region is located. If there are a large number of writes for the newly created table, hotspots are caused.
To solve the hotspot problem in the above scenario, TiDB introduces the pre-split function, which can pre-split multiple Regions for a certain table according to the specified parameters and scatter them to each TiKV node.
There are two types of Split Region syntax:
SPLIT TABLE table_name [INDEX index_name] BETWEEN (lower_value) AND (upper_value) REGIONS region_num
BETWEEN lower_value AND upper_value REGIONS region_num defines the upper boundary, the lower boundary, and the Region amount. Then the current region will be evenly spilt into the number of regions (as specified in
region_num) between the upper and lower boundaries.
SPLIT TABLE table_name [INDEX index_name] BY (value_list) [, (value_list)] ...
BY value_list… specifies a series of points manually, based on which the current Region is spilt. It is suitable for scenarios with unevenly distributed data.
The key of row data in each table is encoded by
row_id. The format is as follows:
For example, when
table_id is 22 and
row_id is 11:
Row data in the same table have the same
table_id, but each row has its unique
row_id that can be used for Region split.
row_id is an integer, the value of the key to be split can be calculated easily according to the
region_num defined by the user. The calculation starts with the step value (
step = (upper_value - lower_value)/num). Then split will be done evenly per each “step” between
upper_value to generate the number of Regions as specified by
For example, if you want 16 evenly split Regions split from key range
maxInt64 for table t, you can use this statement:
SPLIT TABLE t BETWEEN (-9223372036854775808) AND (9223372036854775807) REGIONS 16;
This statement splits table t into 16 Regions between minInt64 and maxInt64. If the given primary key range is smaller than the specified one, for example, 0~1000000000, you can use 0 and 1000000000 take place of minInt64 and maxInt64 respectively to split Regions.
SPLIT TABLE t BETWEEN (0) AND (1000000000) REGIONS 16;
If the known data is unevenly distributed, and you want a Region to be split respectively in key ranges -inf ~ 10000, 10000 ~ 90000, and 90000 ~ +inf, you can achieve this by setting fixed points, as shown below:
SPLIT TABLE t BY (10000), (90000);
The key of the index data in the table is encoded by
index_id, and the value of the index column. The format is as follows:
For example, when
table_id is 22,
index_id is 5, and
index_value is abc:
index_id of the same index data in one table is the same. To split index Regions, you need to split Regions based on
The way to split index evenly works the same as splitting data evenly. However, calculating the value of step is more complicated, because
index_value might not be an integer.
The values of
lower are encoded into a byte array firstly. After removing the longest common prefix of
upper byte array, the first 8 bytes of
upper are converted into the uint64 format. Then
step = (upper - lower)/num is calculated. After that, the calculated step is encoded into a byte array, which is appended to the longest common prefix of the
upper byte array for index split. Here is an example:
If the column of the
idx index is of the integer type, you can use the following SQL statement to split index data:
SPLIT TABLE t INDEX idx BETWEEN (-9223372036854775808) AND (9223372036854775807) REGIONS 16;
This statement splits the Region of index idx in table t into 16 Regions from
If the column of index idx1 is of varchar type, and you want to split index data by prefix letters.
SPLIT TABLE t INDEX idx1 BETWEEN ("a") AND ("z") REGIONS 26;
This statement splits index idx1 into 26 Regions from a~z. The range of Region 1 is
[minIndexValue, b); the range of Region 2 is
[b, c); … the range of Region 26 is
[z, minIndexValue]. For the
idx index, data with the
a prefix is written into Region 1, while data with the
b prefix is written into Region 2, and so on.
If the column of index idx2 is of time type like timestamp/datetime, and you want to split index Region by time interval:
SPLIT TABLE t INDEX idx2 BETWEEN ("2010-01-01 00:00:00") AND ("2020-01-01 00:00:00") REGIONS 10;
This statemnt spilts the region of index idx2 in table t into 10 Regions from
2010-01-01 00:00:00 to
2020-01-01 00:00:00. The range of Region 1 is
[minIndexValue, 2011-01-01 00:00:00); the range of Region 2 is
[2011-01-01 00:00:00, 2012-01-01 00:00:00) and so on.
Region split methods for other types of index columns are similar.
For data Region split of joint indexes, the only difference is that you can specify multiple columns values.
For example, index
idx3 (a, b) contains 2 columns, with column
a of timestamp type and column
b int. If you just want to do a time range split according to column
a, you can use the SQL statement for splitting time index of a single column. In this case, do not specify the value of column
SPLIT TABLE t INDEX idx3 BETWEEN ("2010-01-01 00:00:00") AND ("2020-01-01 00:00:00") REGIONS 10;
Within the same range of time, if you want to do one more split according to column b column. Just specify the value for column b when splitting.
SPLIT TABLE t INDEX idx3 BETWEEN ("2010-01-01 00:00:00", "a") AND ("2010-01-01 00:00:00", "z") REGIONS 10;
This statement splits 10 Regions in the range of a~z according to the value of column b, with the same time prefix as column a. If the value specified for column a is different, the value of column b might not be used in this case.
Index data can also be split by specified index values.
For example, there is
idx4 (a,b), with column
a of the varchar type and column
b of the timestamp type.
SPLIT TABLE t1 INDEX idx4 ("a", "2000-01-01 00:00:01"), ("b", "2019-04-17 14:26:19"), ("c", "");
This statement specifies 3 values to split 4 Regions. The range of each Region is as follows:
region1 [ minIndexValue , ("a", "2000-01-01 00:00:01")) region2 [("a", "2000-01-01 00:00:01") , ("b", "2019-04-17 14:26:19")) region3 [("b", "2019-04-17 14:26:19") , ("c", "") ) region4 [("c", "") , maxIndexValue )
To have evenly split Regions when a table is created, it is recommended you use
shard_row_id_bits together with
pre_split_regions. When a table is created successfully,
pre_split_regions pre-spilts tables into the number of Regions as specified by
The value of
pre_split_regionsmust be less than or equal to that of
create table t (a int, b int,index idx1(a)) shard_row_id_bits = 4 pre_split_regions=3;
After building the table, this statement splits 4 + 1 Regions for table t.
4 (2^(3-1)) Regions are used to save table row data, and 1 Region is for saving the index data of
The ranges of the 4 table Regions are as follows:
region1: [ -inf , 1<<61 ) region2: [ 1<<61 , 2<<61 ) region3: [ 2<<61 , 3<<61 ) region4: [ 3<<61 , +inf )
The amount of split Region is 2^(pre_split_regions-1) because when using shard_row_id_bits, only positive numbers will be assigned to
_tidb_rowid, so there is no need to do Spilt Region for the negative range.
There are two
SPLIT REGION related session variables:
tidb_wait_split_region_timeout. For details, see TiDB specific system variables and syntax.