📣

TiDB Cloud Serverless 现已更名为
Starter
!此页面由 AI 自动翻译,英文原文请见
此处。

SHOW TABLE REGIONS

SHOW TABLE REGIONS 语句用于显示 TiDB 中某个表的 Region 信息。

语法

SHOW TABLE [table_name] REGIONS [WhereClauseOptional]; SHOW TABLE [table_name] INDEX [index_name] REGIONS [WhereClauseOptional];

语法说明

ShowTableRegionStmt
SHOWTABLETableNamePartitionNameListINDEXIndexNameREGIONSWHEREExpression
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_IDSTART_KEYEND_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_KEYt_75_r_31717END_KEYt_75_r_63434,表示主键在 3171763434 之间的数据存储在该 Region 中。前缀 t_75_ 表示该 Region 属于内部表 ID 为 75 的表(t)。START_KEYEND_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。其中 1021061101143 存储行数据,98 存储索引数据。
  • Region 102START_KEYEND_KEY 中,t_43 表示表前缀和表 ID,_r 表示表 t 的记录数据前缀,_i 表示索引数据前缀。
  • Region 102START_KEYEND_KEY 表示记录数据范围为 [-inf, 20000)。同理,可以计算出 Region (1061101143) 的数据存储范围。
  • Region 98 存储索引数据。表 t 的索引数据起始 key 为 t_43_i,该范围属于 Region 98

如需查看 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 个(1021061101143)存储表 t 的记录数据,另外 2 个(13598)存储索引数据 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 语法的扩展。

另请参阅

文档内容是否有帮助?