SHOW TABLE REGIONS
SHOW TABLE REGIONS
语句用于显示 TiDB 中某个表的 Region 信息。
语法
SHOW TABLE [table_name] REGIONS [WhereClauseOptional];
SHOW TABLE [table_name] INDEX [index_name] REGIONS [WhereClauseOptional];
语法说明
- ShowTableRegionStmt
- TableName
ShowTableRegionStmt ::=
"SHOW" "TABLE" TableName PartitionNameList? ("INDEX" IndexName)? "REGIONS" ("WHERE" Expression)?
TableName ::=
(SchemaName ".")? Identifier
执行 SHOW TABLE REGIONS
会返回以下列:
REGION_ID
:Region 的 ID。START_KEY
:Region 的起始 key。END_KEY
:Region 的结束 key。LEADER_ID
:Region 的 Leader ID。LEADER_STORE_ID
:Region Leader 所在的 store(TiKV)ID。PEERS
:所有 Region 副本的 ID。SCATTERING
:Region 是否正在调度。1
表示为 true。WRITTEN_BYTES
:在一个心跳周期内写入该 Region 的数据量估算,单位为字节。READ_BYTES
:在一个心跳周期内从该 Region 读取的数据量估算,单位为字节。APPROXIMATE_SIZE(MB)
:Region 内数据的估算量,单位为 MB。APPROXIMATE_KEYS
:Region 内 key 的估算数量。
SCHEDULING_CONSTRAINTS
:与 Region 所属表或分区相关联的 placement policy 设置。
SCHEDULING_STATE
:拥有 placement policy 的 Region 的调度状态。
示例
创建一个示例表,并插入足够多的数据以填充多个 Region:
CREATE TABLE t1 (
id INT NOT NULL PRIMARY KEY auto_increment,
b INT NOT NULL,
pad1 VARBINARY(1024),
pad2 VARBINARY(1024),
pad3 VARBINARY(1024)
);
INSERT INTO t1 SELECT NULL, FLOOR(RAND()*1000), RANDOM_BYTES(1024), RANDOM_BYTES(1024), RANDOM_BYTES(1024) FROM dual;
INSERT INTO t1 SELECT NULL, FLOOR(RAND()*1000), RANDOM_BYTES(1024), RANDOM_BYTES(1024), RANDOM_BYTES(1024) FROM t1 a JOIN t1 b JOIN t1 c LIMIT 10000;
INSERT INTO t1 SELECT NULL, FLOOR(RAND()*1000), RANDOM_BYTES(1024), RANDOM_BYTES(1024), RANDOM_BYTES(1024) FROM t1 a JOIN t1 b JOIN t1 c LIMIT 10000;
INSERT INTO t1 SELECT NULL, FLOOR(RAND()*1000), RANDOM_BYTES(1024), RANDOM_BYTES(1024), RANDOM_BYTES(1024) FROM t1 a JOIN t1 b JOIN t1 c LIMIT 10000;
INSERT INTO t1 SELECT NULL, FLOOR(RAND()*1000), RANDOM_BYTES(1024), RANDOM_BYTES(1024), RANDOM_BYTES(1024) FROM t1 a JOIN t1 b JOIN t1 c LIMIT 10000;
INSERT INTO t1 SELECT NULL, FLOOR(RAND()*1000), RANDOM_BYTES(1024), RANDOM_BYTES(1024), RANDOM_BYTES(1024) FROM t1 a JOIN t1 b JOIN t1 c LIMIT 10000;
INSERT INTO t1 SELECT NULL, FLOOR(RAND()*1000), RANDOM_BYTES(1024), RANDOM_BYTES(1024), RANDOM_BYTES(1024) FROM t1 a JOIN t1 b JOIN t1 c LIMIT 10000;
INSERT INTO t1 SELECT NULL, FLOOR(RAND()*1000), RANDOM_BYTES(1024), RANDOM_BYTES(1024), RANDOM_BYTES(1024) FROM t1 a JOIN t1 b JOIN t1 c LIMIT 10000;
INSERT INTO t1 SELECT NULL, FLOOR(RAND()*1000), RANDOM_BYTES(1024), RANDOM_BYTES(1024), RANDOM_BYTES(1024) FROM t1 a JOIN t1 b JOIN t1 c LIMIT 10000;
INSERT INTO t1 SELECT NULL, FLOOR(RAND()*1000), RANDOM_BYTES(1024), RANDOM_BYTES(1024), RANDOM_BYTES(1024) FROM t1 a JOIN t1 b JOIN t1 c LIMIT 10000;
INSERT INTO t1 SELECT NULL, FLOOR(RAND()*1000), RANDOM_BYTES(1024), RANDOM_BYTES(1024), RANDOM_BYTES(1024) FROM t1 a JOIN t1 b JOIN t1 c LIMIT 10000;
INSERT INTO t1 SELECT NULL, FLOOR(RAND()*1000), RANDOM_BYTES(1024), RANDOM_BYTES(1024), RANDOM_BYTES(1024) FROM t1 a JOIN t1 b JOIN t1 c LIMIT 10000;
SELECT SLEEP(5);
SHOW TABLE t1 REGIONS;
输出结果应显示该表被拆分为多个 Region。REGION_ID
、START_KEY
和 END_KEY
可能不会完全对应:
...
mysql> SHOW TABLE t1 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 |
+-----------+--------------+--------------+-----------+-----------------+-------+------------+---------------+------------+----------------------+------------------+------------------------+------------------+
| 94 | t_75_ | t_75_r_31717 | 95 | 1 | 95 | 0 | 0 | 0 | 112 | 207465 | | |
| 96 | t_75_r_31717 | t_75_r_63434 | 97 | 1 | 97 | 0 | 0 | 0 | 97 | 0 | | |
| 2 | t_75_r_63434 | | 3 | 1 | 3 | 0 | 269323514 | 66346110 | 245 | 162020 | | |
+-----------+--------------+--------------+-----------+-----------------+-------+------------+---------------+------------+----------------------+------------------+------------------------+------------------+
3 rows in set (0.00 sec)
在上述输出中,START_KEY
为 t_75_r_31717
,END_KEY
为 t_75_r_63434
,表示主键在 31717
到 63434
之间的数据存储在该 Region 中。前缀 t_75_
表示该 Region 属于内部表 ID 为 75
的表(t
)。START_KEY
或 END_KEY
为空时,分别表示负无穷或正无穷。
TiDB 会根据需要自动进行 Region 的重新均衡。若需手动均衡,可使用 SPLIT TABLE REGION
语句:
mysql> SPLIT TABLE t1 BETWEEN (31717) AND (63434) REGIONS 2;
+--------------------+----------------------+
| TOTAL_SPLIT_REGION | SCATTER_FINISH_RATIO |
+--------------------+----------------------+
| 1 | 1 |
+--------------------+----------------------+
1 row in set (42.34 sec)
mysql> SHOW TABLE t1 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 |
+-----------+--------------+--------------+-----------+-----------------+-------+------------+---------------+------------+----------------------+------------------+------------------------+------------------+
| 94 | t_75_ | t_75_r_31717 | 95 | 1 | 95 | 0 | 0 | 0 | 112 | 207465 | | |
| 98 | t_75_r_31717 | t_75_r_47575 | 99 | 1 | 99 | 0 | 1325 | 0 | 53 | 12052 | | |
| 96 | t_75_r_47575 | t_75_r_63434 | 97 | 1 | 97 | 0 | 1526 | 0 | 48 | 0 | | |
| 2 | t_75_r_63434 | | 3 | 1 | 3 | 0 | 0 | 55752049 | 60 | 0 | | |
+-----------+--------------+--------------+-----------+-----------------+-------+------------+---------------+------------+----------------------+------------------+------------------------+------------------+
4 rows in set (0.00 sec)
上述输出显示 Region 96 被拆分,创建了新的 Region 98。表中的其他 Region 未受拆分操作影响。可通过输出统计信息确认:
TOTAL_SPLIT_REGION
表示新拆分出的 Region 数量。本例中为 1。SCATTER_FINISH_RATIO
表示新拆分 Region 的调度完成率。1.0
表示所有 Region 均已调度完成。
更详细的示例:
mysql> 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 |
+-----------+--------------+--------------+-----------+-----------------+---------------+------------+---------------+------------+----------------------+------------------+------------------------+------------------+
| 102 | t_43_r | t_43_r_20000 | 118 | 7 | 105, 118, 119 | 0 | 0 | 0 | 1 | 0 | | |
| 106 | t_43_r_20000 | t_43_r_40000 | 120 | 7 | 107, 108, 120 | 0 | 23 | 0 | 1 | 0 | | |
| 110 | t_43_r_40000 | t_43_r_60000 | 112 | 9 | 112, 113, 121 | 0 | 0 | 0 | 1 | 0 | | |
| 114 | t_43_r_60000 | t_43_r_80000 | 122 | 7 | 115, 122, 123 | 0 | 35 | 0 | 1 | 0 | | |
| 3 | t_43_r_80000 | | 93 | 8 | 5, 73, 93 | 0 | 0 | 0 | 1 | 0 | | |
| 98 | t_43_ | t_43_r | 99 | 1 | 99, 100, 101 | 0 | 0 | 0 | 1 | 0 | | |
+-----------+--------------+--------------+-----------+-----------------+---------------+------------+---------------+------------+----------------------+------------------+------------------------+------------------+
6 rows in set
在上述示例中:
- 表 t 对应 6 个 Region。其中
102
、106
、110
、114
、3
存储行数据,98
存储索引数据。 - Region
102
的START_KEY
和END_KEY
中,t_43
表示表前缀和表 ID,_r
表示表 t 的记录数据前缀,_i
表示索引数据前缀。 - Region
102
的START_KEY
和END_KEY
表示记录数据范围为[-inf, 20000)
。同理,可以计算出 Region (106
、110
、114
、3
) 的数据存储范围。 - Region
98
存储索引数据。表 t 的索引数据起始 key 为t_43_i
,该范围属于 Region98
。
如需查看 store 1 上对应表 t 的 Region,可使用 WHERE
子句:
test> SHOW TABLE t REGIONS WHERE leader_store_id =1;
+-----------+-----------+---------+-----------+-----------------+--------------+------------+---------------+------------+----------------------+------------------+------------------------+------------------+
| 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 |
+-----------+-----------+---------+-----------+-----------------+--------------+------------+---------------+------------+----------------------+------------------+------------------------+------------------+
| 98 | t_43_ | t_43_r | 99 | 1 | 99, 100, 101 | 0 | 0 | 0 | 1 | 0 | | |
+-----------+-----------+---------+-----------+-----------------+--------------+------------+---------------+------------+----------------------+------------------+------------------------+------------------+
使用 SPLIT TABLE REGION
可将索引数据拆分为多个 Region。如下例,将表 t 的索引数据 name
在 [a,z]
范围内拆分为 2 个 Region。
test> SPLIT TABLE t INDEX name BETWEEN ("a") AND ("z") REGIONS 2;
+--------------------+----------------------+
| TOTAL_SPLIT_REGION | SCATTER_FINISH_RATIO |
+--------------------+----------------------+
| 2 | 1.0 |
+--------------------+----------------------+
1 row in set
此时表 t 对应 7 个 Region。其中 5 个(102
、106
、110
、114
、3
)存储表 t 的记录数据,另外 2 个(135
、98
)存储索引数据 name
。
test> 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 |
+-----------+-----------------------------+-----------------------------+-----------+-----------------+---------------+------------+---------------+------------+----------------------+------------------+------------------------+------------------+
| 102 | t_43_r | t_43_r_20000 | 118 | 7 | 105, 118, 119 | 0 | 0 | 0 | 1 | 0 | | |
| 106 | t_43_r_20000 | t_43_r_40000 | 120 | 7 | 108, 120, 126 | 0 | 0 | 0 | 1 | 0 | | |
| 110 | t_43_r_40000 | t_43_r_60000 | 112 | 9 | 112, 113, 121 | 0 | 0 | 0 | 1 | 0 | | |
| 114 | t_43_r_60000 | t_43_r_80000 | 122 | 7 | 115, 122, 123 | 0 | 35 | 0 | 1 | 0 | | |
| 3 | t_43_r_80000 | | 93 | 8 | 73, 93, 128 | 0 | 0 | 0 | 1 | 0 | | |
| 135 | t_43_i_1_ | t_43_i_1_016d80000000000000 | 139 | 2 | 138, 139, 140 | 0 | 35 | 0 | 1 | 0 | | |
| 98 | t_43_i_1_016d80000000000000 | t_43_r | 99 | 1 | 99, 100, 101 | 0 | 0 | 0 | 1 | 0 | | |
+-----------+-----------------------------+-----------------------------+-----------+-----------------+---------------+------------+---------------+------------+----------------------+------------------+------------------------+------------------+
7 rows in set
MySQL 兼容性
该语句是 TiDB 对 MySQL 语法的扩展。