SPLIT AT 语句用于依据表或索引的特定行,对key-value层的range进行强制拆分。

概要

图片 图片 图片 图片

所需权限

用户必须具有表或索引的INSERT 权限

参数

Parameter Description
table_name
table_name @ index_name
需拆分的表名或索引名
select_stmt 一个 选择查询,会生成一个或多个要拆分表或索引的行

为什么手动进行拆分?

CockroachDB的key-value 层被分成连续的key-space,这些连续的key-space就是ranges。CockroachDB 默认range的大小是小于64MiB。当一个range的大小大于64MiB,系统会自动进行拆分 split。大多数情况下,这种自动拆分是有效的,不需要关心系统何时进行range拆分。

但是,针对如下情形,需要手动拆分表或索引:

例子

拆分一张表

> SHOW EXPERIMENTAL_RANGES FROM TABLE kv;
+-----------+---------+----------+----------+--------------+
| Start Key | End Key | Range ID | Replicas | Lease Holder |
+-----------+---------+----------+----------+--------------+
| NULL      | NULL    |       72 | {1}      |            1 |
+-----------+---------+----------+----------+--------------+
(1 row)
> ALTER TABLE kv SPLIT AT VALUES (10), (20), (30);
+------------+----------------+
|    key     |     pretty     |
+------------+----------------+
| \u0209\x92 | /Table/64/1/10 |
| \u0209\x9c | /Table/64/1/20 |
| \u0209\xa6 | /Table/64/1/30 |
+------------+----------------+
(3 rows)
> SHOW EXPERIMENTAL_RANGES FROM TABLE kv;
+-----------+---------+----------+----------+--------------+
| Start Key | End Key | Range ID | Replicas | Lease Holder |
+-----------+---------+----------+----------+--------------+
| NULL      | /10     |       72 | {1}      |            1 |
| /10       | /20     |       73 | {1}      |            1 |
| /20       | /30     |       74 | {1}      |            1 |
| /30       | NULL    |       75 | {1}      |            1 |
+-----------+---------+----------+----------+--------------+
(4 rows)

使用复合主键对表进行拆分

使用复合主键对表进行拆分(比如,当使用partitions)。

创建一个有复合主键的表。

CREATE TABLE t (k1 INT, k2 INT, v INT, w INT, PRIMARY KEY (k1, k2));

使用如下的sql对复合主键进行拆分:

ALTER TABLE t SPLIT AT VALUES (5,1), (5,2), (5,3);
+------------+-----------------+
|    key     |     pretty      |
+------------+-----------------+
| \xbc898d89 | /Table/52/1/5/1 |
| \xbc898d8a | /Table/52/1/5/2 |
| \xbc898d8b | /Table/52/1/5/3 |
+------------+-----------------+
(3 rows)

使用SHOW EXPERIMENTAL_RANGES查看详细信息。

SHOW EXPERIMENTAL_RANGES FROM TABLE t;
+-----------+---------+----------+----------+--------------+
| Start Key | End Key | Range ID | Replicas | Lease Holder |
+-----------+---------+----------+----------+--------------+
| NULL      | /5/1    |      151 | {2,3,5}  |            5 |
| /5/1      | /5/2    |      152 | {2,3,5}  |            5 |
| /5/2      | /5/3    |      153 | {2,3,5}  |            5 |
| /5/3      | NULL    |      154 | {2,3,5}  |            5 |
+-----------+---------+----------+----------+--------------+
(4 rows)

同时,也可以通过复合主键的前缀列进行range拆分。

> ALTER TABLE t SPLIT AT VALUES (3);
+----------+---------------+
|   key    |    pretty     |
+----------+---------------+
| \xcd898b | /Table/69/1/3 |
+----------+---------------+
(1 row)
SHOW EXPERIMENTAL_RANGES FROM TABLE t;
+-----------+---------+----------+----------+--------------+
| Start Key | End Key | Range ID | Replicas | Lease Holder |
+-----------+---------+----------+----------+--------------+
| NULL      | /3      |      155 | {2,3,5}  |            5 |
| /3        | NULL    |      165 | {2,3,5}  |            5 |
+-----------+---------+----------+----------+--------------+
(2 rows)

拆分索引

> CREATE INDEX secondary ON kv (v);
> SHOW EXPERIMENTAL_RANGES FROM INDEX kv@secondary;
+-----------+---------+----------+----------+--------------+
| Start Key | End Key | Range ID | Replicas | Lease Holder |
+-----------+---------+----------+----------+--------------+
| NULL      | NULL    |       75 | {1}      |            1 |
+-----------+---------+----------+----------+--------------+
(1 row)
> ALTER INDEX kv@secondary SPLIT AT (SELECT v FROM kv LIMIT 3);
+---------------------+-----------------+
|         key         |     pretty      |
+---------------------+-----------------+
| \u020b\x12a\x00\x01 | /Table/64/3/"a" |
| \u020b\x12b\x00\x01 | /Table/64/3/"b" |
| \u020b\x12c\x00\x01 | /Table/64/3/"c" |
+---------------------+-----------------+
(3 rows)
> SHOW EXPERIMENTAL_RANGES FROM INDEX kv@secondary;
+-----------+---------+----------+----------+--------------+
| Start Key | End Key | Range ID | Replicas | Lease Holder |
+-----------+---------+----------+----------+--------------+
| NULL      | /"a"    |       75 | {1}      |            1 |
| /"a"      | /"b"    |       76 | {1}      |            1 |
| /"b"      | /"c"    |       77 | {1}      |            1 |
| /"c"      | NULL    |       78 | {1}      |            1 |
+-----------+---------+----------+----------+--------------+
(4 rows)

See Also