实验目的
本实验中我们将建立聚簇表和非聚簇表，并写入数据，查看表的Region分布及Region的Key范围。
实验过程
一、创建一个非聚簇表并查看每个Region的Key范围
1.连接tidb数据库
[root@node1 ~]# mysql -u root -P 4000 -h 192.168.59.27
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 9
Server version: 5.7.25-TiDB-v5.2.1 TiDB Server (Apache License 2.0) Community Edition, MySQL 5.7 compatible
Copyright (c) 2000, 2015, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> use test; 2.建立非聚簇表，并且预建立8个Region
mysql> create table nocluster_order
-> (id bigint(20) unsigned auto_increment not null,
-> code varchar(30) not null,
-> order_no varchar(200) not null default '',
-> status int(4) not null,
-> cancle_flag int(4) default null,
-> create_user varchar(50) default null,
-> update_user varchar(50) default null,
-> create_time datetime default null,
-> update_time datetime default null,
-> primary key (id) nonclustered)
-> engine=innodb shard_row_id_bits=4 pre_split_regions=3;
Query OK, 0 rows affected (0.12 sec)
mysql> show create table nocluster_order;
+-----------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- -----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-----------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- -----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| nocluster_order | CREATE TABLE `nocluster_order` (
`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`code` varchar(30) NOT NULL,
`order_no` varchar(200) NOT NULL DEFAULT '',
`status` int(4) NOT NULL,
`cancle_flag` int(4) DEFAULT NULL,
`create_user` varchar(50) DEFAULT NULL,
`update_user` varchar(50) DEFAULT NULL,
`create_time` datetime DEFAULT NULL,
`update_time` datetime DEFAULT NULL,
PRIMARY KEY (`id`) /*T![clustered_index] NONCLUSTERED */
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin /*T! SHARD_ROW_ID_BITS=4 PRE_SPLIT_REGIONS=3 */ |
+-----------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- -----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec) 查看Region分布情况：
mysql> show table nocluster_order regions;
+-----------+-----------------------------+-----------------------------+-----------+-----------------+------------------------+------------+---------------+------------+----------------------+------------------+
| REGION_ID | START_KEY | END_KEY | LEADER_ID | LEADER_STORE_ID | PEERS | SCATTERING | WRITTEN_BYTES | READ_BYTES | APPROXIMATE_SIZE(MB) | APPROXIMATE_KEYS |
+-----------+-----------------------------+-----------------------------+-----------+-----------------+------------------------+------------+---------------+------------+----------------------+------------------+
| 493589 | t_360_i_1_ | t_360_r_1152921504606846976 | 493590 | 3 | 493590, 493591, 493592 | 0 | 0 | 0 | 1 | 0 |
| 493593 | t_360_r_1152921504606846976 | t_360_r_2305843009213693952 | 493594 | 3 | 493594, 493595, 493596 | 0 | 27 | 0 | 1 | 0 |
| 493597 | t_360_r_2305843009213693952 | t_360_r_3458764513820540928 | 493600 | 1 | 493598, 493599, 493600 | 0 | 0 | 0 | 1 | 0 |
| 493601 | t_360_r_3458764513820540928 | t_360_r_4611686018427387904 | 493602 | 3 | 493602, 493603, 493604 | 0 | 27 | 0 | 1 | 0 |
| 493605 | t_360_r_4611686018427387904 | t_360_r_5764607523034234880 | 493608 | 1 | 493606, 493607, 493608 | 0 | 1083 | 0 | 1 | 0 |
| 493609 | t_360_r_5764607523034234880 | t_360_r_6917529027641081856 | 493610 | 3 | 493610, 493611, 493612 | 0 | 1323 | 0 | 1 | 0 |
| 493613 | t_360_r_6917529027641081856 | t_360_r_8070450532247928832 | 493614 | 3 | 493614, 493615, 493616 | 0 | 675 | 0 | 1 | 0 |
| 493481 | t_360_r_8070450532247928832 | | 493482 | 3 | 493482, 493483, 493484 | 0 | 2459 | 0 | 1 | 0 |
+-----------+-----------------------------+-----------------------------+-----------+-----------------+------------------------+------------+---------------+------------+----------------------+------------------+
8 rows in set (0.03 sec)mysql> insert into nocluster_order(code,order_no,status,create_user,create_time)values(concat('CODE_',LPAD(round((rand()*1000)),5,0)),uuid(),round((rand()*100)),concat('USERID_',LPAD(round((rand()*100)),4,0)),now());
Query OK, 1 row affected (0.12 sec)
mysql> insert into nocluster_order(code,order_no,status,create_user,create_time)values(concat('CODE_',LPAD(round((rand()*1000)),5,0)),uuid(),round((rand()*100)),concat('USERID_',LPAD(round((rand()*100)),4,0)),now());
Query OK, 1 row affected (0.01 sec)
mysql> insert into nocluster_order(code,order_no,status,create_user,create_time)values(concat('CODE_',LPAD(round((rand()*1000)),5,0)),uuid(),round((rand()*100)),concat('USERID_',LPAD(round((rand()*100)),4,0)),now());
Query OK, 1 row affected (0.01 sec)
mysql> insert into nocluster_order(code,order_no,status,create_user,create_time)values(concat('CODE_',LPAD(round((rand()*1000)),5,0)),uuid(),round((rand()*100)),concat('USERID_',LPAD(round((rand()*100)),4,0)),now());
Query OK, 1 row affected (0.01 sec)
mysql> insert into nocluster_order(code,order_no,status,create_user,create_time)values(concat('CODE_',LPAD(round((rand()*1000)),5,0)),uuid(),round((rand()*100)),concat('USERID_',LPAD(round((rand()*100)),4,0)),now());
Query OK, 1 row affected (0.01 sec)
mysql> insert into nocluster_order(code,order_no,status,create_user,create_time)values(concat('CODE_',LPAD(round((rand()*1000)),5,0)),uuid(),round((rand()*100)),concat('USERID_',LPAD(round((rand()*100)),4,0)),now());
Query OK, 1 row affected (0.00 sec)
mysql> insert into nocluster_order(code,order_no,status,create_user,create_time)values(concat('CODE_',LPAD(round((rand()*1000)),5,0)),uuid(),round((rand()*100)),concat('USERID_',LPAD(round((rand()*100)),4,0)),now());
Query OK, 1 row affected (0.00 sec)
mysql> insert into nocluster_order(code,order_no,status,create_user,create_time)values(concat('CODE_',LPAD(round((rand()*1000)),5,0)),uuid(),round((rand()*100)),concat('USERID_',LPAD(round((rand()*100)),4,0)),now());
Query OK, 1 row affected (0.01 sec)
mysql> insert into nocluster_order(code,order_no,status,create_user,create_time)values(concat('CODE_',LPAD(round((rand()*1000)),5,0)),uuid(),round((rand()*100)),concat('USERID_',LPAD(round((rand()*100)),4,0)),now());
Query OK, 1 row affected (0.01 sec)
mysql> insert into nocluster_order(code,order_no,status,create_user,create_time)values(concat('CODE_',LPAD(round((rand()*1000)),5,0)),uuid(),round((rand()*100)),concat('USERID_',LPAD(round((rand()*100)),4,0)),now());
Query OK, 1 row affected (0.01 sec) 再次查看Region分布及WRITTEN_BYTES的变化：
mysql> show table nocluster_order regions;
+-----------+-----------------------------+-----------------------------+-----------+-----------------+------------------------+------------+---------------+------------+----------------------+------------------+
| REGION_ID | START_KEY | END_KEY | LEADER_ID | LEADER_STORE_ID | PEERS | SCATTERING | WRITTEN_BYTES | READ_BYTES | APPROXIMATE_SIZE(MB) | APPROXIMATE_KEYS |
+-----------+-----------------------------+-----------------------------+-----------+-----------------+------------------------+------------+---------------+------------+----------------------+------------------+
| 493589 | t_360_i_1_ | t_360_r_1152921504606846976 | 493590 | 3 | 493590, 493591, 493592 | 0 | 1559 | 0 | 1 | 0 |
| 493593 | t_360_r_1152921504606846976 | t_360_r_2305843009213693952 | 493594 | 3 | 493594, 493595, 493596 | 0 | 27 | 0 | 1 | 0 |
| 493597 | t_360_r_2305843009213693952 | t_360_r_3458764513820540928 | 493600 | 1 | 493598, 493599, 493600 | 0 | 1323 | 0 | 1 | 0 |
| 493601 | t_360_r_3458764513820540928 | t_360_r_4611686018427387904 | 493602 | 3 | 493602, 493603, 493604 | 0 | 27 | 0 | 1 | 0 |
| 493605 | t_360_r_4611686018427387904 | t_360_r_5764607523034234880 | 493608 | 1 | 493606, 493607, 493608 | 0 | 0 | 0 | 1 | 0 |
| 493609 | t_360_r_5764607523034234880 | t_360_r_6917529027641081856 | 493610 | 3 | 493610, 493611, 493612 | 0 | 0 | 0 | 1 | 0 |
| 493613 | t_360_r_6917529027641081856 | t_360_r_8070450532247928832 | 493614 | 3 | 493614, 493615, 493616 | 0 | 0 | 0 | 1 | 0 |
| 493481 | t_360_r_8070450532247928832 | | 493482 | 3 | 493482, 493483, 493484 | 0 | 1323 | 0 | 1 | 0 |
+-----------+-----------------------------+-----------------------------+-----------+-----------------+------------------------+------------+---------------+------------+----------------------+------------------+
8 rows in set (0.02 sec)二、建立一个聚簇索引表并查看每个Region的Key范围
1.创建聚簇表cluster_order，并查看表结构
mysql> create table cluster_order(
-> id bigint(20) unsigned auto_random not null,
-> code varchar(30) not null,
-> order_no varchar(200) not null default '',
-> status int(4) not null,
-> cancle_flag int(4) default null,
-> create_user varchar(50) default null,
-> update_user varchar(50) default null,
-> create_time datetime default null,
-> update_time datetime default null,
-> primary key (id) clustered
-> ) engine=innodb;
Query OK, 0 rows affected, 1 warning (0.11 sec)
mysql> show table cluster_order regions;
+-----------+-----------+---------+-----------+-----------------+------------------------+------------+---------------+------------+----------------------+------------------+
| REGION_ID | START_KEY | END_KEY | LEADER_ID | LEADER_STORE_ID | PEERS | SCATTERING | WRITTEN_BYTES | READ_BYTES | APPROXIMATE_SIZE(MB) | APPROXIMATE_KEYS |
+-----------+-----------+---------+-----------+-----------------+------------------------+------------+---------------+------------+----------------------+------------------+
| 493669 | t_365_ | | 493670 | 3 | 493670, 493671, 493672 | 0 | 0 | 0 | 1 | 0 |
+-----------+-----------+---------+-----------+-----------------+------------------------+------------+---------------+------------+----------------------+------------------+
1 row in set (0.00 sec) 2.因cluster_order只有一个region，可以通过split手动切分
mysql> split table cluster_order between (0) and (9223372036854775807) regions 16;
+--------------------+----------------------+
| TOTAL_SPLIT_REGION | SCATTER_FINISH_RATIO |
+--------------------+----------------------+
| 15 | 1 |
+--------------------+----------------------+
1 row in set (0.05 sec)
mysql> show table cluster_order regions;
+-----------+-----------------------------+-----------------------------+-----------+-----------------+------------------------+------------+---------------+------------+----------------------+------------------+
| REGION_ID | START_KEY | END_KEY | LEADER_ID | LEADER_STORE_ID | PEERS | SCATTERING | WRITTEN_BYTES | READ_BYTES | APPROXIMATE_SIZE(MB) | APPROXIMATE_KEYS |
+-----------+-----------------------------+-----------------------------+-----------+-----------------+------------------------+------------+---------------+------------+----------------------+------------------+
| 493705 | t_365_ | t_365_r_576460752303423487 | 493706 | 3 | 493706, 493707, 493708 | 0 | 1323 | 0 | 1 | 0 |
| 493709 | t_365_r_576460752303423487 | t_365_r_1152921504606846974 | 493712 | 1 | 493710, 493711, 493712 | 0 | 1323 | 0 | 1 | 0 |
| 493713 | t_365_r_1152921504606846974 | t_365_r_1729382256910270461 | 493715 | 2 | 493714, 493715, 493716 | 0 | 675 | 0 | 1 | 0 |
| 493717 | t_365_r_1729382256910270461 | t_365_r_2305843009213693948 | 493718 | 3 | 493718, 493719, 493720 | 0 | 1323 | 0 | 1 | 0 |
| 493721 | t_365_r_2305843009213693948 | t_365_r_2882303761517117435 | 493723 | 2 | 493722, 493723, 493724 | 0 | 0 | 0 | 1 | 0 |
| 493725 | t_365_r_2882303761517117435 | t_365_r_3458764513820540922 | 493728 | 1 | 493726, 493727, 493728 | 0 | 27 | 0 | 1 | 0 |
| 493729 | t_365_r_3458764513820540922 | t_365_r_4035225266123964409 | 493730 | 3 | 493730, 493731, 493732 | 0 | 0 | 0 | 1 | 0 |
| 493733 | t_365_r_4035225266123964409 | t_365_r_4611686018427387896 | 493734 | 3 | 493734, 493735, 493736 | 0 | 27 | 0 | 1 | 0 |
| 493737 | t_365_r_4611686018427387896 | t_365_r_5188146770730811383 | 493738 | 3 | 493738, 493739, 493740 | 0 | 0 | 0 | 1 | 0 |
| 493741 | t_365_r_5188146770730811383 | t_365_r_5764607523034234870 | 493742 | 3 | 493742, 493743, 493744 | 0 | 0 | 0 | 1 | 0 |
| 493745 | t_365_r_5764607523034234870 | t_365_r_6341068275337658357 | 493746 | 3 | 493746, 493747, 493748 | 0 | 27 | 0 | 1 | 0 |
| 493749 | t_365_r_6341068275337658357 | t_365_r_6917529027641081844 | 493750 | 3 | 493750, 493751, 493752 | 0 | 0 | 0 | 1 | 0 |
| 493753 | t_365_r_6917529027641081844 | t_365_r_7493989779944505331 | 493755 | 2 | 493754, 493755, 493756 | 0 | 675 | 0 | 1 | 0 |
| 493757 | t_365_r_7493989779944505331 | t_365_r_8070450532247928818 | 493758 | 3 | 493758, 493759, 493760 | 0 | 0 | 0 | 1 | 0 |
| 493761 | t_365_r_8070450532247928818 | t_365_r_8646911284551352305 | 493764 | 1 | 493762, 493763, 493764 | 0 | 0 | 0 | 1 | 0 |
| 493669 | t_365_r_8646911284551352305 | | 493670 | 3 | 493670, 493671, 493672 | 0 | 0 | 0 | 1 | 0 |
+-----------+-----------------------------+-----------------------------+-----------+-----------------+------------------------+------------+---------------+------------+----------------------+------------------+
16 rows in set (0.04 sec) 3.通过如下SQL一次写入5行数据，写三次，总共写入15行数据
insert into cluster_order (code,order_no,`status`,create_user,create_time)
values (concat('CODE_',LPAD(round((rand()*1000)),5,0)),uuid(),round((rand()*100)),concat('USERID_',LPAD(round((rand()*100)),4,0)),now()),
(concat('CODE_',LPAD(round((rand()*1000)),5,0)),uuid(),round((rand()*100)),concat('USERID_',LPAD(round((rand()*100)),4,0)),now()),
(concat('CODE_',LPAD(round((rand()*1000)),5,0)),uuid(),round((rand()*100)),concat('USERID_',LPAD(round((rand()*100)),4,0)),now()),
(concat('CODE_',LPAD(round((rand()*1000)),5,0)),uuid(),round((rand()*100)),concat('USERID_',LPAD(round((rand()*100)),4,0)),now()),
(concat('CODE_',LPAD(round((rand()*1000)),5,0)),uuid(),round((rand()*100)),concat('USERID_',LPAD(round((rand()*100)),4,0)),now());mysql> insert into cluster_order (code,order_no,`status`,create_user,create_time)
-> values (concat('CODE_',LPAD(round((rand()*1000)),5,0)),uuid(),round((rand()*100)),concat('USERID_',LPAD(round((rand()*100)),4,0)),now()),
-> (concat('CODE_',LPAD(round((rand()*1000)),5,0)),uuid(),round((rand()*100)),concat('USERID_',LPAD(round((rand()*100)),4,0)),now()),
-> (concat('CODE_',LPAD(round((rand()*1000)),5,0)),uuid(),round((rand()*100)),concat('USERID_',LPAD(round((rand()*100)),4,0)),now()),
-> (concat('CODE_',LPAD(round((rand()*1000)),5,0)),uuid(),round((rand()*100)),concat('USERID_',LPAD(round((rand()*100)),4,0)),now()),
-> (concat('CODE_',LPAD(round((rand()*1000)),5,0)),uuid(),round((rand()*100)),concat('USERID_',LPAD(round((rand()*100)),4,0)),now());
Query OK, 5 rows affected (0.03 sec)
Records: 5 Duplicates: 0 Warnings: 0
mysql> insert into cluster_order (code,order_no,`status`,create_user,create_time)
-> values (concat('CODE_',LPAD(round((rand()*1000)),5,0)),uuid(),round((rand()*100)),concat('USERID_',LPAD(round((rand()*100)),4,0)),now()),
-> (concat('CODE_',LPAD(round((rand()*1000)),5,0)),uuid(),round((rand()*100)),concat('USERID_',LPAD(round((rand()*100)),4,0)),now()),
-> (concat('CODE_',LPAD(round((rand()*1000)),5,0)),uuid(),round((rand()*100)),concat('USERID_',LPAD(round((rand()*100)),4,0)),now()),
-> (concat('CODE_',LPAD(round((rand()*1000)),5,0)),uuid(),round((rand()*100)),concat('USERID_',LPAD(round((rand()*100)),4,0)),now()),
-> (concat('CODE_',LPAD(round((rand()*1000)),5,0)),uuid(),round((rand()*100)),concat('USERID_',LPAD(round((rand()*100)),4,0)),now());
Query OK, 5 rows affected (0.00 sec)
Records: 5 Duplicates: 0 Warnings: 0
mysql> insert into cluster_order (code,order_no,`status`,create_user,create_time)
-> values (concat('CODE_',LPAD(round((rand()*1000)),5,0)),uuid(),round((rand()*100)),concat('USERID_',LPAD(round((rand()*100)),4,0)),now()),
-> (concat('CODE_',LPAD(round((rand()*1000)),5,0)),uuid(),round((rand()*100)),concat('USERID_',LPAD(round((rand()*100)),4,0)),now()),
-> (concat('CODE_',LPAD(round((rand()*1000)),5,0)),uuid(),round((rand()*100)),concat('USERID_',LPAD(round((rand()*100)),4,0)),now()),
-> (concat('CODE_',LPAD(round((rand()*1000)),5,0)),uuid(),round((rand()*100)),concat('USERID_',LPAD(round((rand()*100)),4,0)),now()),
-> (concat('CODE_',LPAD(round((rand()*1000)),5,0)),uuid(),round((rand()*100)),concat('USERID_',LPAD(round((rand()*100)),4,0)),now());
Query OK, 5 rows affected (0.01 sec)
Records: 5 Duplicates: 0 Warnings: 0 4.查看写入数据后cluster_order的Region变化
mysql> show table cluster_order regions;
+-----------+-----------------------------+-----------------------------+-----------+-----------------+------------------------+------------+---------------+------------+----------------------+------------------+
| REGION_ID | START_KEY | END_KEY | LEADER_ID | LEADER_STORE_ID | PEERS | SCATTERING | WRITTEN_BYTES | READ_BYTES | APPROXIMATE_SIZE(MB) | APPROXIMATE_KEYS |
+-----------+-----------------------------+-----------------------------+-----------+-----------------+------------------------+------------+---------------+------------+----------------------+------------------+
| 493705 | t_365_ | t_365_r_576460752303423487 | 493706 | 3 | 493706, 493707, 493708 | 0 | 1323 | 0 | 1 | 0 |
| 493709 | t_365_r_576460752303423487 | t_365_r_1152921504606846974 | 493712 | 1 | 493710, 493711, 493712 | 0 | 0 | 0 | 1 | 0 |
| 493713 | t_365_r_1152921504606846974 | t_365_r_1729382256910270461 | 493715 | 2 | 493714, 493715, 493716 | 0 | 0 | 0 | 1 | 0 |
| 493717 | t_365_r_1729382256910270461 | t_365_r_2305843009213693948 | 493718 | 3 | 493718, 493719, 493720 | 0 | 0 | 0 | 1 | 0 |
| 493721 | t_365_r_2305843009213693948 | t_365_r_2882303761517117435 | 493723 | 2 | 493722, 493723, 493724 | 0 | 1323 | 0 | 1 | 0 |
| 493725 | t_365_r_2882303761517117435 | t_365_r_3458764513820540922 | 493728 | 1 | 493726, 493727, 493728 | 0 | 1778 | 0 | 1 | 0 |
| 493729 | t_365_r_3458764513820540922 | t_365_r_4035225266123964409 | 493730 | 3 | 493730, 493731, 493732 | 0 | 2045 | 0 | 1 | 0 |
| 493733 | t_365_r_4035225266123964409 | t_365_r_4611686018427387896 | 493734 | 3 | 493734, 493735, 493736 | 0 | 27 | 0 | 1 | 0 |
| 493737 | t_365_r_4611686018427387896 | t_365_r_5188146770730811383 | 493738 | 3 | 493738, 493739, 493740 | 0 | 675 | 0 | 1 | 0 |
| 493741 | t_365_r_5188146770730811383 | t_365_r_5764607523034234870 | 493742 | 3 | 493742, 493743, 493744 | 0 | 0 | 0 | 1 | 0 |
| 493745 | t_365_r_5764607523034234870 | t_365_r_6341068275337658357 | 493746 | 3 | 493746, 493747, 493748 | 0 | 27 | 0 | 1 | 0 |
| 493749 | t_365_r_6341068275337658357 | t_365_r_6917529027641081844 | 493750 | 3 | 493750, 493751, 493752 | 0 | 675 | 0 | 1 | 0 |
| 493753 | t_365_r_6917529027641081844 | t_365_r_7493989779944505331 | 493755 | 2 | 493754, 493755, 493756 | 0 | 0 | 0 | 1 | 0 |
| 493757 | t_365_r_7493989779944505331 | t_365_r_8070450532247928818 | 493758 | 3 | 493758, 493759, 493760 | 0 | 675 | 0 | 1 | 0 |
| 493761 | t_365_r_8070450532247928818 | t_365_r_8646911284551352305 | 493764 | 1 | 493762, 493763, 493764 | 0 | 1083 | 0 | 1 | 0 |
| 493669 | t_365_r_8646911284551352305 | | 493670 | 3 | 493670, 493671, 493672 | 0 | 2758 | 0 | 1 | 0 |
+-----------+-----------------------------+-----------------------------+-----------+-----------------+------------------------+------------+---------------+------------+----------------------+------------------+
16 rows in set (0.04 sec)
mysql> select id from cluster_order;
+----------------------+
| id |
+----------------------+
| 2882303761517117491 |
| 2882303761517117501 |
| 2882303761517117511 |
| 2882303761517117521 |
| 2882303761517117531 |
| 13835058055282163813 |
| 13835058055282163823 |
| 13835058055282163833 |
| 13835058055282163843 |
| 13835058055282163853 |
| 3458764513820540929 |
| 3458764513820540939 |
| 3458764513820540949 |
| 3458764513820540959 |
| 3458764513820540969 |
+----------------------+
15 rows in set (0.01 sec) 可以看出通过auto_random，一次写入的id值连续，但多次写入的id不连续，被分配到三个不同region中存储。
三、遇到的问题及解决方案
1.建立非聚簇表时预分配的多个Region，会自动合并成一个Region，现象如下：
mysql> create table nocluster_order (id bigint(20) unsigned auto_increment not null,code varchar(30) not null,order_no varchar(200) not null default '',status int(4) not n ull,cancle_flag int(4) default null,create_user varchar(50) default null,update_user varchar(50) default null,create_time datetime default null,update_time datetime defaul t null,primary key (id) nonclustered) engine=innodb shard_row_id_bits=4 pre_split_regions=3;
Query OK, 0 rows affected (0.53 sec)
mysql> show table nocluster_order regions;
+-----------+-----------------------------+-----------------------------+-----------+-----------------+------------------------+------------+---------------+------------+- ---------------------+------------------+
| REGION_ID | START_KEY | END_KEY | LEADER_ID | LEADER_STORE_ID | PEERS | SCATTERING | WRITTEN_BYTES | READ_BYTES | APPROXIMATE_SIZE(MB) | APPROXIMATE_KEYS |
+-----------+-----------------------------+-----------------------------+-----------+-----------------+------------------------+------------+---------------+------------+- ---------------------+------------------+
| 489545 | t_330_i_1_ | t_330_r_1152921504606846976 | 489547 | 2 | 489546, 489547, 489548 | 0 | 0 | 0 | 1 | 1850 |
| 489549 | t_330_r_1152921504606846976 | t_330_r_2305843009213693952 | 489551 | 2 | 489550, 489551, 489552 | 0 | 0 | 0 | 1 | 3700 |
| 489553 | t_330_r_2305843009213693952 | t_330_r_3458764513820540928 | 489555 | 2 | 489554, 489555, 489556 | 0 | 1299 | 0 | 1 | 3700 |
| 489557 | t_330_r_3458764513820540928 | t_330_r_4611686018427387904 | 489559 | 2 | 489558, 489559, 489560 | 0 | 855 | 0 | 1 | 3700 |
| 489561 | t_330_r_4611686018427387904 | t_330_r_5764607523034234880 | 489563 | 2 | 489562, 489563, 489564 | 0 | 27 | 0 | 1 | 3700 |
| 489565 | t_330_r_5764607523034234880 | t_330_r_6917529027641081856 | 489567 | 2 | 489566, 489567, 489568 | 0 | 0 | 0 | 1 | 3700 |
| 489569 | t_330_r_6917529027641081856 | t_330_r_8070450532247928832 | 489571 | 2 | 489570, 489571, 489572 | 0 | 27 | 0 | 1 | 3700 |
| 489413 | t_330_r_8070450532247928832 | | 489415 | 2 | 489414, 489415, 489416 | 0 | 1497 | 0 | 1 | 3700 |
+-----------+-----------------------------+-----------------------------+-----------+-----------------+------------------------+------------+---------------+------------+- ---------------------+------------------+
8 rows in set (0.04 sec)
mysql> insert into nocluster_order(code,order_no,status,create_user,create_time)values(concat('CODE_',LPAD(round((rand()*1000)),5,0)),uuid(),round((rand()*100)),concat('US ERID_',LPAD(round((rand()*100)),4,0)),now());
Query OK, 1 row affected (0.02 sec)
mysql> insert into nocluster_order(code,order_no,status,create_user,create_time)values(concat('CODE_',LPAD(round((rand()*1000)),5,0)),uuid(),round((rand()*100)),concat('US ERID_',LPAD(round((rand()*100)),4,0)),now());
Query OK, 1 row affected (0.00 sec)
mysql> insert into nocluster_order(code,order_no,status,create_user,create_time)values(concat('CODE_',LPAD(round((rand()*1000)),5,0)),uuid(),round((rand()*100)),concat('US ERID_',LPAD(round((rand()*100)),4,0)),now());
Query OK, 1 row affected (0.01 sec)
mysql> insert into nocluster_order(code,order_no,status,create_user,create_time)values(concat('CODE_',LPAD(round((rand()*1000)),5,0)),uuid(),round((rand()*100)),concat('US ERID_',LPAD(round((rand()*100)),4,0)),now());
Query OK, 1 row affected (0.00 sec)
mysql> insert into nocluster_order(code,order_no,status,create_user,create_time)values(concat('CODE_',LPAD(round((rand()*1000)),5,0)),uuid(),round((rand()*100)),concat('US ERID_',LPAD(round((rand()*100)),4,0)),now());
Query OK, 1 row affected (0.00 sec)
mysql> insert into nocluster_order(code,order_no,status,create_user,create_time)values(concat('CODE_',LPAD(round((rand()*1000)),5,0)),uuid(),round((rand()*100)),concat('US ERID_',LPAD(round((rand()*100)),4,0)),now());
Query OK, 1 row affected (0.04 sec)
mysql> insert into nocluster_order(code,order_no,status,create_user,create_time)values(concat('CODE_',LPAD(round((rand()*1000)),5,0)),uuid(),round((rand()*100)),concat('US ERID_',LPAD(round((rand()*100)),4,0)),now());
Query OK, 1 row affected (0.01 sec)
mysql> insert into nocluster_order(code,order_no,status,create_user,create_time)values(concat('CODE_',LPAD(round((rand()*1000)),5,0)),uuid(),round((rand()*100)),concat('US ERID_',LPAD(round((rand()*100)),4,0)),now());
Query OK, 1 row affected (0.01 sec)
mysql> insert into nocluster_order(code,order_no,status,create_user,create_time)values(concat('CODE_',LPAD(round((rand()*1000)),5,0)),uuid(),round((rand()*100)),concat('US ERID_',LPAD(round((rand()*100)),4,0)),now());
Query OK, 1 row affected (0.01 sec)
mysql> insert into nocluster_order(code,order_no,status,create_user,create_time)values(concat('CODE_',LPAD(round((rand()*1000)),5,0)),uuid(),round((rand()*100)),concat('US ERID_',LPAD(round((rand()*100)),4,0)),now());
Query OK, 1 row affected (0.01 sec)
mysql> select count(*) from nocluster_order;
+----------+
| count(*) |
+----------+
| 10 |
+----------+
1 row in set (0.00 sec)
mysql> show table nocluster_order regions;
+-----------+-----------+---------+-----------+-----------------+------------------------+------------+---------------+------------+----------------------+---------------- --+
| REGION_ID | START_KEY | END_KEY | LEADER_ID | LEADER_STORE_ID | PEERS | SCATTERING | WRITTEN_BYTES | READ_BYTES | APPROXIMATE_SIZE(MB) | APPROXIMATE_KEY S |
+-----------+-----------+---------+-----------+-----------------+------------------------+------------+---------------+------------+----------------------+---------------- --+
| 489557 | t_330_ | | 489559 | 2 | 489558, 489559, 489560 | 0 | 19969 | 0 | 1 | 1670 2 |
+-----------+-----------+---------+-----------+-----------------+------------------------+------------+---------------+------------+----------------------+---------------- --+
1 row in set (0.00 sec) 2.分析原因，应该时Region合并，查询官方文档 " PD 调度策略最佳实践 | PingCAP Docs"，Region的大小和Key值分别由max-merge-region-size、max-merge-region-keys这两个参数控制，自动调度间隔时间由split-merge-interval参数控制，查询数据库目前这个三个系统参数值
mysql> show config where name like '%max-merge-region-keys%';
+------+--------------------+--------------------------------+--------+
| Type | Instance | Name | Value |
+------+--------------------+--------------------------------+--------+
| pd | 192.168.59.29:2379 | schedule.max-merge-region-keys | 200000 |
| pd | 192.168.59.28:2379 | schedule.max-merge-region-keys | 200000 |
| pd | 192.168.59.27:2379 | schedule.max-merge-region-keys | 200000 |
+------+--------------------+--------------------------------+--------+
3 rows in set (0.05 sec)
mysql> show config where name like '%split-merge%';
+------+--------------------+-------------------------------+--------+
| Type | Instance | Name | Value |
+------+--------------------+-------------------------------+--------+
| pd | 192.168.59.29:2379 | schedule.split-merge-interval | 1m0s |
| pd | 192.168.59.28:2379 | schedule.split-merge-interval | 1m0s |
| pd | 192.168.59.27:2379 | schedule.split-merge-interval | 1m0s |
+------+--------------------+-------------------------------+--------+
3 rows in set (0.93 sec)
mysql> show config where name like '%max-merge-region-size%';
+------+--------------------+--------------------------------+-------+
| Type | Instance | Name | Value |
+------+--------------------+--------------------------------+-------+
| pd | 192.168.59.29:2379 | schedule.max-merge-region-size | 20 |
| pd | 192.168.59.28:2379 | schedule.max-merge-region-size | 20 |
| pd | 192.168.59.27:2379 | schedule.max-merge-region-size | 20 |
+------+--------------------+--------------------------------+-------+ 从以上参数可以看出系统1分钟自动合并一次空Region，修改split-merge-interval参数到默认值1小时
[root@node1 ~]# tiup ctl:v5.2.0 pd -u http://192.168.59.27:2379 config set split-merge-interval '1h';
Starting component `ctl`: /root/.tiup/components/ctl/v5.2.0/ctl pd -u http://192.168.59.27:2379 config set split-merge-interval 1h
Success!
[root@node1 ~]# mysql -u root -P 4000 -h 192.168.59.27
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 5
Server version: 5.7.25-TiDB-v5.2.1 TiDB Server (Apache License 2.0) Community Edition, MySQL 5.7 compatible
Copyright (c) 2000, 2015, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> show config where name like '%split-merge%';
+------+--------------------+-------------------------------+--------+
| Type | Instance | Name | Value |
+------+--------------------+-------------------------------+--------+
| pd | 192.168.59.29:2379 | schedule.split-merge-interval | 1h0m0s |
| pd | 192.168.59.28:2379 | schedule.split-merge-interval | 1h0m0s |
| pd | 192.168.59.27:2379 | schedule.split-merge-interval | 1h0m0s |
+------+--------------------+-------------------------------+--------+
3 rows in set (0.93 sec) 改为1小时后，实验正常进行。
最后修改时间：2022-04-11 09:30:28
「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容，转载时必须标注文章的来源（墨天轮），文章链接，文章作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。
