- Introduction
- Concepts
- Architecture
- Key Features
- Horizontal Scalability
- MySQL Compatible Syntax
- Replicate from and to MySQL
- Distributed Transactions with Strong Consistency
- Cloud Native Architecture
- Minimize ETL with HTAP
- Fault Tolerance & Recovery with Raft
- Automatic Rebalancing
- Deployment and Orchestration with Ansible, Kubernetes, Docker
- JSON Support
- Spark Integration
- Read Historical Data Without Restoring from Backup
- Fast Import and Restore of Data
- Hybrid of Column and Row Storage
- SQL Plan Management
- Open Source
- Online Schema Changes
- How-to
- Get Started
- Deploy
- Hardware Recommendations
- From Binary Tarball
- Orchestrated Deployment
- Geographic Redundancy
- Data Migration with Ansible
- Configure
- Secure
- Transport Layer Security (TLS)
- Generate Self-signed Certificates
- Monitor
- Migrate
- Maintain
- Common Ansible Operations
- Backup and Restore
- Use BR (recommended)
- Identify Abnormal Queries
- Scale
- Upgrade
- Troubleshoot
- Reference
- SQL
- MySQL Compatibility
- SQL Language Structure
- Attributes
- Data Types
- Functions and Operators
- Function and Operator Reference
- Type Conversion in Expression Evaluation
- Operators
- Control Flow Functions
- String Functions
- Numeric Functions and Operators
- Date and Time Functions
- Bit Functions and Operators
- Cast Functions and Operators
- Encryption and Compression Functions
- Information Functions
- JSON Functions
- Aggregate (GROUP BY) Functions
- Window Functions
- Miscellaneous Functions
- Precision Math
- List of Expressions for Pushdown
- SQL Statements
ADD COLUMN
ADD INDEX
ADMIN
ADMIN CANCEL DDL
ADMIN CHECKSUM TABLE
ADMIN CHECK [TABLE|INDEX]
ADMIN SHOW DDL [JOBS|QUERIES]
ALTER DATABASE
ALTER INSTANCE
ALTER TABLE
ALTER USER
ANALYZE TABLE
BEGIN
CHANGE COLUMN
COMMIT
CREATE DATABASE
CREATE INDEX
CREATE ROLE
CREATE TABLE LIKE
CREATE TABLE
CREATE USER
CREATE VIEW
DEALLOCATE
DELETE
DESC
DESCRIBE
DO
DROP COLUMN
DROP DATABASE
DROP INDEX
DROP ROLE
DROP TABLE
DROP USER
DROP VIEW
EXECUTE
EXPLAIN ANALYZE
EXPLAIN
FLUSH PRIVILEGES
FLUSH STATUS
FLUSH TABLES
GRANT <privileges>
GRANT <role>
INSERT
KILL [TIDB]
LOAD DATA
LOAD STATS
MODIFY COLUMN
PREPARE
RECOVER TABLE
RENAME INDEX
RENAME TABLE
REPLACE
REVOKE <privileges>
REVOKE <role>
ROLLBACK
SELECT
SET DEFAULT ROLE
SET [NAMES|CHARACTER SET]
SET PASSWORD
SET ROLE
SET TRANSACTION
SET [GLOBAL|SESSION] <variable>
SHOW ANALYZE STATUS
SHOW CHARACTER SET
SHOW COLLATION
SHOW [FULL] COLUMNS FROM
SHOW CREATE TABLE
SHOW CREATE USER
SHOW DATABASES
SHOW ENGINES
SHOW ERRORS
SHOW [FULL] FIELDS FROM
SHOW GRANTS
SHOW INDEXES [FROM|IN]
SHOW INDEX [FROM|IN]
SHOW KEYS [FROM|IN]
SHOW PRIVILEGES
SHOW [FULL] PROCESSSLIST
SHOW SCHEMAS
SHOW STATUS
SHOW [FULL] TABLES
SHOW TABLE REGIONS
SHOW TABLE STATUS
SHOW [GLOBAL|SESSION] VARIABLES
SHOW WARNINGS
SPLIT REGION
START TRANSACTION
TRACE
TRUNCATE
UPDATE
USE
- Constraints
- Generated Columns
- Partitioning
- Character Set
- SQL Mode
- Views
- Configuration
- Security
- Transactions
- System Databases
- Errors Codes
- Supported Client Drivers
- Garbage Collection (GC)
- Performance
- Overview
- Understanding the Query Execution Plan
- The Blocklist of Optimization Rules and Expression Pushdown
- Introduction to Statistics
- TopN and Limit Push Down
- Optimizer Hints
- Follower Read
- Check the TiDB Cluster Status Using SQL Statements
- Execution Plan Binding
- Statement Summary Table
- Tune TiKV
- Operating System Tuning
- Column Pruning
- Key Monitoring Metrics
- Alert Rules
- Best Practices
- TiSpark
- TiKV
- TiFlash
- TiDB Binlog
- Tools
- Overview
- Use Cases
- Download
- TiDB Operator
- Table Filter
- Backup & Restore (BR)
- Mydumper
- Syncer
- Loader
- Data Migration
- TiDB Lightning
- sync-diff-inspector
- PD Control
- PD Recover
- TiKV Control
- TiDB Control
- TiDB in Kubernetes
- FAQs
- Support
- Contribute
- Releases
- All Releases
- v3.1
- v3.0
- v2.1
- v2.0
- v1.0
- Glossary
You are viewing the documentation of an older version of the TiDB database (TiDB v3.1).
Split Region
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.
Usage of Split Region
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 following example shows the result of the SPLIT
statement:
+--------------------+----------------------+
| TOTAL_SPLIT_REGION | SCATTER_FINISH_RATIO |
+--------------------+----------------------+
| 4 | 1.0 |
+--------------------+----------------------+
TOTAL_SPLIT_REGION
: the number of newly split Regions.SCATTER_FINISH_RATIO
: the completion rate of scattering for newly split Regions.1.0
means that all Regions are scattered.0.5
means that only half of the Regions are scattered and the rest are being scattered.
The following two session variables might affect the behavior of the SPLIT
statement:
tidb_wait_split_region_finish
: It might take a while to scatter the Regions. This duration depends on PD scheduling and TiKV loads. This variable is used to control when executing theSPLIT REGION
statement whether to return the results to the client until all Regions are scattered. If its value is set to1
(by default), TiDB returns the results only after the scattering is completed. If its value is set to0
, TiDB returns the results regardless of the scattering status.tidb_wait_split_region_timeout
: This variable is to set the execution timeout of theSPLIT REGION
statement, in seconds. The default value is 300s. If thesplit
operation is not completed within the duration, TiDB returns a timeout error.
Split Table Region
The key of row data in each table is encoded by table_id
and row_id
. The format is as follows:
t[table_id]_r[row_id]
For example, when table_id
is 22 and row_id
is 11:
t22_r11
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.
Even Split
Because row_id
is an integer, the value of the key to be split can be calculated according to the specified lower_value
, upper_value
, and region_num
. TiDB first calculates the step value (step = (upper_value - lower_value)/num
). Then split will be done evenly per each "step" between lower_value
and upper_value
to generate the number of Regions as specified by num
.
For example, if you want 16 evenly split Regions split from key rangeminInt64
~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;
Uneven split
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);
Split index Region
The key of the index data in the table is encoded by table_id
, index_id
, and the value of the index column. The format is as follows:
t[table_id]_i[index_id][index_value]
For example, when table_id
is 22, index_id
is 5, and index_value
is abc:
t22_i5abc
The table_id
and index_id
of the same index data in one table is the same. To split index Regions, you need to split Regions based on index_value
.
Even Spilt
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 upper
and lower
are encoded into a byte array firstly. After removing the longest common prefix of lower
and upper
byte array, the first 8 bytes of lower
and 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 lower
and 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 minInt64
to maxInt64
.
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 25;
This statement splits index idx1 into 25 Regions from a~z. The range of Region 1 is [minIndexValue, b)
; the range of Region 2 is [b, c)
; … the range of Region 25 is [y, minIndexValue]
. For the idx
index, data with the a
prefix is written into Region 1, and data with the b
prefix is written into Region 2, and so on.
In the split method above, both data with the y
and z
prefixes are written into Region 25, because the upper bound is not z
, but {
(the character next to z
in ASCII). Therefore, a more accurate split method is as follows:
SPLIT TABLE t INDEX idx1 BETWEEN ("a") AND ("{") REGIONS 26;
This statement splits index idx1 of the table t
into 26 Regions from a~{
. The range of Region 1 is [minIndexValue, b)
; the range of Region 2 is [b, c)
; … the range of Region 25 is [y, z)
, and the range of Region 26 is [z, maxIndexValue)
.
If the column of index idx2
is of time type like timestamp/datetime, and you want to split index Region by year:
SPLIT TABLE t INDEX idx2 BETWEEN ("2010-01-01 00:00:00") AND ("2020-01-01 00:00:00") REGIONS 10;
This statement splits 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.
If you want to split the index Region by day, see the following example:
SPLIT TABLE t INDEX idx2 BETWEEN ("2020-06-01 00:00:00") AND ("2020-07-01 00:00:00") REGIONS 30;
This statement splits the data of June 2020 of index idx2
in table t
into 30 Regions, each Region representing 1 day.
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 b
in lower_value
and upper_velue
.
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.
Uneven Split
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 BY ("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 )
pre_split_regions
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 2^(PRE_SPLIT_REGIONS)
.
The value of PRE_SPLIT_REGIONS
must be less than or equal to that of SHARD_ROW_ID_BITS
.
The tidb_scatter_region
global variable affects the behavior of PRE_SPLIT_REGIONS
. This variable controls whether to wait for Regions to be pre-split and scattered before returning results after the table creation. If there are intensive writes after creating the table, you need to set the value of this variable to 1
, then TiDB will not return the results to the client until all the Regions are split and scattered. Otherwise, TiDB writes the data before the scattering is completed, which will have a significant impact on write performance.
Example
create table t (a int, b int,index idx1(a)) shard_row_id_bits = 4 pre_split_regions=2;
After building the table, this statement splits 4 + 1
Regions for table t. 4 (2^2)
Regions are used to save table row data, and 1 Region is for saving the index data of idx1
.
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 )
MySQL compatibility
This statement is a TiDB extension to MySQL syntax.
See also
There are two SPLIT REGION
related session variables: tidb_scatter_region
, tidb_wait_split_region_finish
and tidb_wait_split_region_timeout
. For details, see TiDB specific system variables and syntax.