请问下各位老师 建一个表，不插入数据， 这个时候只会分配一个region嘛？region 是会预先分配还是说，需要region的时候，才分配region？
单表、分区表、pre_split_regions 建完表 show table regions 看下就知道了
你描述的场景都可以满足的，这个和建表选择的模型有很大的关系。
-
高性能的模型会自动分配和生成region，但是有些场景不适用。聚簇索引的模式
-
另外一种就是非族簇索引模式，需要预分配或者手动打散
以上请参考，相关的文档都可以通过搜索获取到
测试了一把，咋感觉PRE_SPLIT_REGIONS 这个参数没起作用呢。
MySQL [test]> create table test (id int primary key );
Query OK, 0 rows affected (0.10 sec)
MySQL [test]> show table test regions;
+-----------+-----------+---------+-----------+-----------------+---------------------------------+------------+---------------+------------+----------------------+------------------+
| REGION_ID | START_KEY | END_KEY | LEADER_ID | LEADER_STORE_ID | PEERS | SCATTERING | WRITTEN_BYTES | READ_BYTES | APPROXIMATE_SIZE(MB) | APPROXIMATE_KEYS |
+-----------+-----------+---------+-----------+-----------------+---------------------------------+------------+---------------+------------+----------------------+------------------+
| 111528001 | t_2858_ | | 111528003 | 110997197 | 111528003, 111528505, 111530519 | 0 | 50 | 0 | 1 | 0 |
+-----------+-----------+---------+-----------+-----------------+---------------------------------+------------+---------------+------------+----------------------+------------------+
1 row in set (0.00 sec)
MySQL [test]> insert into test VALUES (1),(2),(3),(4);
Query OK, 4 rows affected (0.00 sec)
Records: 4 Duplicates: 0 Warnings: 0
MySQL [test]> show table test regions;
+-----------+-----------+---------+-----------+-----------------+---------------------------------+------------+---------------+------------+----------------------+------------------+
| REGION_ID | START_KEY | END_KEY | LEADER_ID | LEADER_STORE_ID | PEERS | SCATTERING | WRITTEN_BYTES | READ_BYTES | APPROXIMATE_SIZE(MB) | APPROXIMATE_KEYS |
+-----------+-----------+---------+-----------+-----------------+---------------------------------+------------+---------------+------------+----------------------+------------------+
| 111528001 | t_2858_ | | 111528003 | 110997197 | 111528003, 111528505, 111530519 | 0 | 50 | 0 | 1 | 0 |
+-----------+-----------+---------+-----------+-----------------+---------------------------------+------------+---------------+------------+----------------------+------------------+
1 row in set (0.00 sec)
MySQL [test]> create table test2 (id int primary key ) PRE_SPLIT_REGIONS = 2;
Query OK, 0 rows affected (0.09 sec)
MySQL [test]> show table test2 regions;
+-----------+-----------+---------+-----------+-----------------+---------------------------------+------------+---------------+------------+----------------------+------------------+
| REGION_ID | START_KEY | END_KEY | LEADER_ID | LEADER_STORE_ID | PEERS | SCATTERING | WRITTEN_BYTES | READ_BYTES | APPROXIMATE_SIZE(MB) | APPROXIMATE_KEYS |
+-----------+-----------+---------+-----------+-----------------+---------------------------------+------------+---------------+------------+----------------------+------------------+
| 111528001 | t_2860_ | | 111528003 | 110997197 | 111528003, 111528505, 111530519 | 0 | 0 | 0 | 1 | 0 |
+-----------+-----------+---------+-----------+-----------------+---------------------------------+------------+---------------+------------+----------------------+------------------+
1 row in set (0.00 sec)
MySQL [test]> insert into test2 VALUES (1),(2),(3),(4);
Query OK, 4 rows affected (0.01 sec)
Records: 4 Duplicates: 0 Warnings: 0
MySQL [test]> show table test2 regions;
+-----------+-----------+---------+-----------+-----------------+---------------------------------+------------+---------------+------------+----------------------+------------------+
| REGION_ID | START_KEY | END_KEY | LEADER_ID | LEADER_STORE_ID | PEERS | SCATTERING | WRITTEN_BYTES | READ_BYTES | APPROXIMATE_SIZE(MB) | APPROXIMATE_KEYS |
+-----------+-----------+---------+-----------+-----------------+---------------------------------+------------+---------------+------------+----------------------+------------------+
| 111528001 | t_2860_ | | 111528003 | 110997197 | 111528003, 111528505, 111530519 | 0 | 0 | 0 | 1 | 0 |
+-----------+-----------+---------+-----------+-----------------+---------------------------------+------------+---------------+------------+----------------------+------------------+
1 row in set (0.00 sec)
MySQL [test]> create table test3 (id int primary key ) PRE_SPLIT_REGIONS = 4;
Query OK, 0 rows affected (0.08 sec)
MySQL [test]> show table test3 regions;
+-----------+-----------+---------+-----------+-----------------+---------------------------------+------------+---------------+------------+----------------------+------------------+
| REGION_ID | START_KEY | END_KEY | LEADER_ID | LEADER_STORE_ID | PEERS | SCATTERING | WRITTEN_BYTES | READ_BYTES | APPROXIMATE_SIZE(MB) | APPROXIMATE_KEYS |
+-----------+-----------+---------+-----------+-----------------+---------------------------------+------------+---------------+------------+----------------------+------------------+
| 111528001 | t_2862_ | | 111528003 | 110997197 | 111528003, 111528505, 111530519 | 0 | 547 | 0 | 1 | 0 |
+-----------+-----------+---------+-----------+-----------------+---------------------------------+------------+---------------+------------+----------------------+------------------+
1 row in set (0.00 sec)
MySQL [test]> select version();
+--------------------+
| version() |
+--------------------+
| 5.7.25-TiDB-v6.1.0 |
+--------------------+
1 row in set (0.00 sec)
MySQL [test]> select tidb_version();
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| tidb_version() |
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Release Version: v6.1.0
Edition: Community
Git Commit Hash: 1a89decdb192cbdce6a7b0020d71128bc964d30f
Git Branch: heads/refs/tags/v6.1.0
UTC Build Time: 2022-06-05 05:15:11
GoVersion: go1.18.2
Race Enabled: false
TiKV Min Version: v3.0.0-60965b006877ca7234adaced7890d7b029ed1306
Check Table Before Drop: false |
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
MySQL [test]>
pre_split_regions
创建表时使用SHARD_ROW_ID_BITS，如果希望建表时就均匀切分 Region，可以考虑配合 PRE_SPLIT_REGIONS 一起使用，用来在建表成功后就开始预均匀切分 2^(PRE_SPLIT_REGIONS) 个 Region。
参考：
https://docs.pingcap.com/zh/tidb/dev/shard-row-id-bits#shard_row_id_bits
排查分析Empty regions 较大原因
1 个赞
和shard_row_id_bits 一起用
1 个赞
SHARD_ROW_ID_BITS = 4 PRE_SPLIT_REGIONS = 4
两个参数一起用。
MySQL [test]> CREATE TABLE t (c int) SHARD_ROW_ID_BITS = 4 PRE_SPLIT_REGIONS = 4;
Query OK, 0 rows affected (0.09 sec)
MySQL [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 |
+-----------+------------------------------+------------------------------+-----------+-----------------+---------------------------------+------------+---------------+------------+----------------------+------------------+
| 111570150 | t_2873_ | t_2873_r_576460752303423488 | 111570151 | 110997197 | 111570151, 111570152, 111570153 | 0 | 0 | 0 | 1 | 0 |
| 111570154 | t_2873_r_576460752303423488 | t_2873_r_1152921504606846976 | 111570155 | 110997197 | 111570155, 111570156, 111570157 | 0 | 0 | 0 | 1 | 0 |
| 111570158 | t_2873_r_1152921504606846976 | t_2873_r_1729382256910270464 | 111570159 | 110997197 | 111570159, 111570160, 111570161 | 0 | 0 | 0 | 1 | 0 |
| 111570162 | t_2873_r_1729382256910270464 | t_2873_r_2305843009213693952 | 111570163 | 110997197 | 111570163, 111570164, 111570165 | 0 | 39 | 0 | 1 | 0 |
| 111570166 | t_2873_r_2305843009213693952 | t_2873_r_2882303761517117440 | 111570167 | 110997197 | 111570167, 111570168, 111570169 | 0 | 39 | 0 | 1 | 0 |
| 111570170 | t_2873_r_2882303761517117440 | t_2873_r_3458764513820540928 | 111570171 | 110997197 | 111570171, 111570172, 111570173 | 0 | 39 | 0 | 1 | 0 |
| 111570174 | t_2873_r_3458764513820540928 | t_2873_r_4035225266123964416 | 111570175 | 110997197 | 111570175, 111570176, 111570177 | 0 | 39 | 0 | 1 | 0 |
| 111570178 | t_2873_r_4035225266123964416 | t_2873_r_4611686018427387904 | 111570179 | 110997197 | 111570179, 111570180, 111570181 | 0 | 39 | 0 | 1 | 0 |
| 111570182 | t_2873_r_4611686018427387904 | t_2873_r_5188146770730811392 | 111570183 | 110997197 | 111570183, 111570184, 111570185 | 0 | 39 | 0 | 1 | 0 |
| 111570186 | t_2873_r_5188146770730811392 | t_2873_r_5764607523034234880 | 111570187 | 110997197 | 111570187, 111570188, 111570189 | 0 | 39 | 0 | 1 | 0 |
| 111570190 | t_2873_r_5764607523034234880 | t_2873_r_6341068275337658368 | 111570191 | 110997197 | 111570191, 111570192, 111570193 | 0 | 27 | 0 | 1 | 0 |
| 111570194 | t_2873_r_6341068275337658368 | t_2873_r_6917529027641081856 | 111570195 | 110997197 | 111570195, 111570196, 111570197 | 0 | 27 | 0 | 1 | 0 |
| 111570198 | t_2873_r_6917529027641081856 | t_2873_r_7493989779944505344 | 111570199 | 110997197 | 111570199, 111570200, 111570201 | 0 | 39 | 0 | 1 | 0 |
| 111570202 | t_2873_r_7493989779944505344 | t_2873_r_8070450532247928832 | 111570203 | 110997197 | 111570203, 111570204, 111570205 | 0 | 39 | 0 | 1 | 0 |
| 111570206 | t_2873_r_8070450532247928832 | t_2873_r_8646911284551352320 | 111570207 | 110997197 | 111570207, 111570208, 111570209 | 0 | 39 | 0 | 1 | 0 |
| 111528001 | t_2873_r_8646911284551352320 | | 111528003 | 110997197 | 111528003, 111528505, 111530519 | 0 | 0 | 0 | 1 | 0 |
+-----------+------------------------------+------------------------------+-----------+-----------------+---------------------------------+------------+---------------+------------+----------------------+------------------+
16 rows in set (0.02 sec)
shard_row_id_bits = 4 pre_split_regions = 4 两个参数要一起用
show table regions 可以看一下结果
PRE_SPLIT_REGIONS、 shard_row_id_bits 一起使用，但是如果预分配的region 没有使用，且你开启了region合并功能，空的region会被自动合并
参考 排查分析Empty regions 较大原因
感谢回复
PCTP 官方课件：
-- 创建个非聚簇表， 并添加 SHARD_ROW_ID_BITS 来打散数据，并在建表后将表预切分为 4 个 Region
CREATE TABLE t (c int PRIMARY KEY NONCLUSTERED) SHARD_ROW_ID_BITS = 4 pre_split_regions = 2;
-- 修改表的打散随机位到 5
ALTER TABLE t SHARD_ROW_ID_BITS = 5;
-- 查看表region分布
show table t regions;
此话题已在最后回复的 1 分钟后被自动关闭。不再允许新回复。