--- title: TiDB 数据库表结构设计练习⼿册 date: 2022-02-17 summary: 在本课练习中，您将连接到 TiDB 数据库，并创建一个非聚簇表和聚簇索引表，并查看每个 Region 的 key 范围。 --- ## 概述 在本课练习中，您将连接到 TiDB 数据库，并创建一个非聚簇表和聚簇索引表，并查看每个 Region 的 key 范围。 ## 实验环境要求 1. 注意，您的实验环境（包括 IP，端口号，用户名，密码和目录）可能和手册不同。 2. 可以连接到实验用虚拟机。 ## 拓扑结构介绍 ![1](media/tidb-table-structure-design-lab-guide/1.png) ## 练习-1: 创建一个非聚簇表并查看每个 Region 的 Key 范围 ### 任务 1.切换到 test 数据库: ``` mysql> use test; Database changed ``` 2.创建一张非聚簇表 `noncluster_order`，注意：`SHARD_ROW_ID_BITS=4 PRE_SPLIT_REGIONS=3`: ``` mysql> CREATE TABLE noncluster_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) ``` 3.创建成功后，查看表结构: ``` mysql> show create table noncluster_order; ----------------------------------------------------------------------------------------------+ | Table | Create Table | +------------------+--------------------------------------------------------------------------+ | noncluster_order | CREATE TABLE `noncluster_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/*!90000 SHARD_ROW_ID_BITS=4 PRE_SPLIT_REGIONS=3 */ | +------------------+-----------------------------------------------------------------+ ``` 4.查看每个 Region 的 `START_KEY` 和 `END_KEY` ，和每个 Region 的 `WRITTEN_BYTES` 数据: ``` mysql> show table noncluster_order regions; ``` ![2](media/tidb-table-structure-design-lab-guide/2.png) 5.插入10 行数据: ``` mysql> insert into noncluster_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()); mysql> insert into noncluster_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()); mysql> insert into noncluster_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()); mysql> insert into noncluster_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()); mysql> insert into noncluster_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, 5 rows affected (0.03 sec) Records: 5 Duplicates: 0 Warnings: 0 mysql> insert into noncluster_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()); mysql> insert into noncluster_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()); mysql> insert into noncluster_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()); mysql> insert into noncluster_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()); mysql> insert into noncluster_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, 5 rows affected (0.03 sec) Records: 5 Duplicates: 0 Warnings: 0 mysql> select count(*) from noncluster_order; +-------------+ | count(*) | +-------------+ | 10 | +-------------+ 1 row in set (0.01 sec) ``` 6.再次查看每个 Region 的 `WRITTEN_BYTES` 数据变化; 变化的 `WRITTEN_BYTES` 代表数据被随机插入到不同的 region 上: ``` mysql> show table noncluster_order regions; ``` ![3](media/tidb-table-structure-design-lab-guide/3.png) ## 练习-2: 创建一个聚簇索引的表并查看每个 Region 的 Key 的范围 ### 任务 1.切换到 test 数据库: ``` mysql> use test; Database changed ``` 2.创建聚簇表 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) ``` 3.查看表 cluster_order 的表结构: ``` mysql> show create table cluster_order; | cluster_order | CREATE TABLE `cluster_order` ( `id` bigint(20) unsigned NOT NULL /*T![auto_rand] AUTO_RANDOM(5) */, `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] CLUSTERED */ ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin | 1 row in set (0.00 sec) ``` 4.看 table Reion 的数量，和 Region 的 `START_KEY` 和 `END_KEY`: ``` mysql> show table cluster_order regions; ``` ![4](media/tidb-table-structure-design-lab-guide/4.png) 5.将 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.41 sec) ``` 6.查看 table Reion 的数量，和 Region 的 `START_KEY` 和 `END_KEY`，记录每个 Region 的 `WRITTEN_BYTES` 数据。 ``` mysql> show table cluster_order regions; ``` ![5](media/tidb-table-structure-design-lab-guide/5.png) 7.插入10 行数据: ``` 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 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 mysql> select count(*) from cluster_order; +-------------+ | count(*) | +-------------+ | 10 | +-------------+ 1 row in set (0.01 sec) ``` 8.再次查看每个 Region 的 `WRITTEN_BYTES` 数据变化: ``` mysql> show table cluster_order regions; ``` ![6](media/tidb-table-structure-design-lab-guide/6.png) 9.思考为什么只有 2 个 Region 的 `WRITTEN_BYTES` 有变化: ``` mysql> select id,id>>58 from cluster_order; +----------------------------------+--------+ | id | id>>58 | +----------------------------------+--------+ | 8070450532247928838 | 28 | | 8070450532247928839 | 28 | | 8070450532247928840 | 28 | | 8070450532247928841 | 28 | | 8070450532247928842 | 28 | | 12682136550675316737 | 44 | | 12682136550675316738 | 44 | | 12682136550675316739 | 44 | | 12682136550675316740 | 44 | | 12682136550675316741 | 44 | +----------------------------------+--------+ 10 rows in set (0.00 sec) ```