# 实验 1：聚簇索引与非聚簇索引

## 概述

在本实验中，你将为主键表创建聚簇索引和非聚簇索引，并进行相关设置以避免热点问题。你将为主键表创建聚簇索引，拆分表区域以避免热点问题。我们还将练习如何通过监控检测热点，并通过修改表结构来避免热点问题。

### 实验时长

+ 本实验大约需 60 分钟完成。

> [!TIP]
> 实验室环境部署完成后，点击底部栏上的 **前进** 进入 **模块 1**。

## 模块 1：聚簇索引的最佳实践

在本模块中，你将为主键表创建聚簇索引，并配置相关设置以防止数据插入热点。

### 任务

+ 根据给定的 DDL 创建名为 `orders1` 的表。根据应用程序设计，该表将有高并发的插入操作。
+ 识别 `orders1` 表可能出现的任何潜在性能问题。
+ 重新创建 `orders1` 表，确保其主键包含聚簇索引。
+ 向 `orders1` 表执行多次数据插入，并分析生成的数据分布。

### 步骤 1：打开终端，SSH 登录到 TiUP 控制机

+ 点击这里 - [终端 1](ssh://monitor1-ssh#1) 打开一个新的终端，连接到为你的实践实验分配的虚拟机。

+ 登录后显示的横幅如下所示。

    ```output
     ,     #_
    ~\_  ####_        Amazon Linux 2
    ~~  \_#####\
    ~~     \###|      
    ~~       \#/ ___
    ~~       V~' '->
    ~~~         /    A newer version of Amazon Linux is available!
     ~~._.   _/
        _/ _/       Amazon Linux 2023, GA and supported until 2028-03-15.
      _/m/'           https://aws.amazon.com/linux/amazon-linux-2023/
    ```

### 步骤 2. 连接到数据库 `test`

   ```bash
   mysql -h ${HOST_DB1_PRIVATE_IP} --port 4000 -u root
   ```

   ```sql
   USE test;
   ```
   ```output
   Database changed
   ```

### 步骤 3. 查看 `orders1` 表的定义，指出潜在的性能问题

+ 表定义如下，点击 `Explain with AI` 按钮获取更多信息。

   ```sql
   CREATE TABLE orders1 (
    id INT UNSIGNED AUTO_INCREMENT NOT NULL,
    code VARCHAR(30) NOT NULL,
    order_no VARCHAR(200) NOT NULL DEFAULT '', 
    status INT NOT NULL,
    cancel_flag INT 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) 
   );
   ```

+ `orders1` 表的主键使用了 `AUTO_INCREMENT`。建议将 `AUTO_INCREMENT` 替换为 `AUTO_RANDOM`，以防止因顺序数据插入可能产生的热点问题。

+ 虑到预期的大量数据插入，建议在初始创建表时通过拆分 TiKV region 来预先对表进行分区。

### 步骤 4. 按照最佳实践重新创建 `orders1` 表

+ 创建 `orders1` 表，由于我们需要使用 `AUTO_RANDOM` 而非 `AUTO_INCREMENT`，`id` 列需要从 `INT` 类型更改为 `BIGINT`。

   ```sql
   DROP TABLE orders1;
   CREATE TABLE orders1 
   ( id BIGINT unsigned AUTO_RANDOM NOT NULL,
     code VARCHAR(30) NOT NULL,
     order_no VARCHAR(200) NOT NULL DEFAULT '', 
     status INT NOT NULL,
     cancel_flag INT 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
   );
   SHOW CREATE TABLE orders1\G
   ```
   ```output
   *************************** 1. row ***************************
   Table: orders1
   Create Table: CREATE TABLE `orders1` (
     `id` BIGINT unsigned NOT NULL /*T![auto_rand] AUTO_RANDOM(5) */,
     `code` VARCHAR(30) NOT NULL,
     `order_no` VARCHAR(200) NOT NULL DEFAULT '',
     `status` INT NOT NULL,
     `cancel_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.01 sec)
   ```

+ 检查 `orders1` 表的区域分布。

   ```sql
   SHOW TABLE orders1 REGIONS\G
   ```
   ```output
   *************************** 1. row ***************************
                REGION_ID: 34
                START_KEY: t_94_
                  END_KEY: t_281474976710649_
                LEADER_ID: 139
          LEADER_STORE_ID: 2
                    PEERS: 35, 139, 186
               SCATTERING: 0
            WRITTEN_BYTES: 273
               READ_BYTES: 0
     APPROXIMATE_SIZE(MB): 1
         APPROXIMATE_KEYS: 0
   SCHEDULING_CONSTRAINTS: 
         SCHEDULING_STATE: 
   1 row in set (0.01 sec)
   ```

+ 将 `orders1` 表拆分为多个 TiKV region。

   ```sql
   SPLIT TABLE orders1 BETWEEN(0) AND (9223372036854775807) REGIONS 16;
   SHOW TABLE orders1 REGIONS\G
   ```
   ```output
   +--------------------+----------------------+
   | TOTAL_SPLIT_REGION | SCATTER_FINISH_RATIO |
   +--------------------+----------------------+
   |                 15 |                    1 |
   +--------------------+----------------------+
   1 row in set (0.48 sec)
   *************************** 1. row ***************************
                REGION_ID: 244
                START_KEY: t_94_
                  END_KEY: t_94_r_576460752303423487
                LEADER_ID: 246
          LEADER_STORE_ID: 2
                    PEERS: 245, 246, 247
               SCATTERING: 0
            WRITTEN_BYTES: 39
               READ_BYTES: 0
     APPROXIMATE_SIZE(MB): 1
         APPROXIMATE_KEYS: 0
   SCHEDULING_CONSTRAINTS: 
         SCHEDULING_STATE: 
   *************************** 2. row ***************************
                REGION_ID: 248
                START_KEY: t_94_r_576460752303423487
                  END_KEY: t_94_r_1152921504606846974
                LEADER_ID: 251
          LEADER_STORE_ID: 5
                    PEERS: 249, 250, 251
               SCATTERING: 0
            WRITTEN_BYTES: 39
               READ_BYTES: 0
     APPROXIMATE_SIZE(MB): 1
         APPROXIMATE_KEYS: 0
   SCHEDULING_CONSTRAINTS: 
         SCHEDULING_STATE: 
   ... ...
   *************************** 16. row ***************************
                REGION_ID: 34
                START_KEY: t_94_r_8646911284551352305
                  END_KEY: t_281474976710649_
                LEADER_ID: 139
          LEADER_STORE_ID: 2
                    PEERS: 35, 139, 186
               SCATTERING: 0
            WRITTEN_BYTES: 0
               READ_BYTES: 0
     APPROXIMATE_SIZE(MB): 1
         APPROXIMATE_KEYS: 0
   SCHEDULING_CONSTRAINTS: 
         SCHEDULING_STATE: 
   16 rows in set (0.08 sec)
   ```

### 步骤 5. 向 `orders1` 表插入五次数据

   ```sql
   BEGIN;
   INSERT INTO orders1 (code, status) VALUES (SUBSTRING(MD5(RAND()),1,20), FLOOR(RAND()*10));
   INSERT INTO orders1 (code, status) VALUES (SUBSTRING(MD5(RAND()),1,20), FLOOR(RAND()*10));
   INSERT INTO orders1 (code, status) VALUES (SUBSTRING(MD5(RAND()),1,20), FLOOR(RAND()*10));
   INSERT INTO orders1 (code, status) VALUES (SUBSTRING(MD5(RAND()),1,20), FLOOR(RAND()*10));
   INSERT INTO orders1 (code, status) VALUES (SUBSTRING(MD5(RAND()),1,20), FLOOR(RAND()*10));
   COMMIT;
   ```
   ```output
   Query OK, 0 rows affected (0.00 sec)
   ```

   ```sql
   SELECT id, code, status FROM orders1;
   ```
   ```output
   +---------------------+----------------------+--------+
   | id                  | code                 | status |
   +---------------------+----------------------+--------+
   | 1152921504606846977 | b1f55f9268fb31cec7f5 |      0 |
   | 1152921504606846978 | c3fd54abaab48504539a |      3 |
   | 1152921504606846979 | 92c1b99e02e4e646b0b5 |      0 |
   | 1152921504606846980 | 67af6b9ceb8e758c5794 |      9 |
   | 1152921504606846981 | ed834b9ef841a67d8edb |      8 |
   +---------------------+----------------------+--------+
   5 rows in set (0.01 sec)
   ```

+ 尽管在 `orders1` 表的主键中指定了 AUTO_RANDOM 属性，但生成的主键值仍然是连续的。
 
### Step 6. `TRUNCATE` `orders1` 表中的数据并插入新数据

+ 请注意，`TRUNCATE` 操作后，`orders1` 表中只会剩下 1 个 region，因此需要手动拆分。
  
   ```sql
   TRUNCATE TABLE orders1;
   SPLIT TABLE orders1 BETWEEN(0) AND (9223372036854775807) REGIONS 16;
   ```
   ```output
   +--------------------+----------------------+
   | TOTAL_SPLIT_REGION | SCATTER_FINISH_RATIO |
   +--------------------+----------------------+
   |                 15 |                    1 |
   +--------------------+----------------------+
   1 row in set (0.06 sec)
   ```

   ```sql
   INSERT INTO orders1(code, status) VALUES(SUBSTRING(MD5(RAND()),1,20), FLOOR(RAND()*10)), \
   (SUBSTRING(MD5(RAND()),1,20), FLOOR(RAND()*10)), (SUBSTRING(MD5(RAND()),1,20), FLOOR(RAND()*10)), \
   (SUBSTRING(MD5(RAND()),1,20), FLOOR(RAND()*10)), (SUBSTRING(MD5(RAND()),1,20), FLOOR(RAND()*10));
   SELECT id, code, status FROM orders1;
   ```
   ```output
   +---------------------+----------------------+--------+
   | id                  | code                 | status |
   +---------------------+----------------------+--------+
   | 5188146770730811393 | 4341550b1717c9e42c33 |      6 |
   | 5188146770730811394 | 8d5687b2a3fa0cbcc039 |      3 |
   | 5188146770730811395 | 87424ab86c4189f14488 |      3 |
   | 5188146770730811396 | 3952300c307266d9da4e |      2 |
   | 5188146770730811397 | 901e4f0fb4e0bcfd98bc |      9 |
   +---------------------+----------------------+--------+
   5 rows in set (0.01 sec)
   ```

+ 结论：只要插入语句在同一事务内，无论是否指定了 AUTO_RANDOM，主键值都会是连续的，仍然存在热点的可能性。 
  
### 步骤 7. 在不同事务中插入数据

   ```sql
   TRUNCATE TABLE orders1;
   SPLIT TABLE orders1 BETWEEN(0) AND (9223372036854775807) REGIONS 16;
   ```
   ```output
   +--------------------+----------------------+
   | TOTAL_SPLIT_REGION | SCATTER_FINISH_RATIO |
   +--------------------+----------------------+
   |                 15 |                    1 |
   +--------------------+----------------------+
   1 row in set (0.06 sec)
   ```

   ```sql
   SET autocommit=ON;
   INSERT INTO orders1(code, status) VALUES(SUBSTRING(MD5(RAND()),1,20), FLOOR(RAND()*10));
   INSERT INTO orders1(code, status) VALUES(SUBSTRING(MD5(RAND()),1,20), FLOOR(RAND()*10));
   INSERT INTO orders1(code, status) VALUES(SUBSTRING(MD5(RAND()),1,20), FLOOR(RAND()*10));
   INSERT INTO orders1(code, status) VALUES(SUBSTRING(MD5(RAND()),1,20), FLOOR(RAND()*10));
   INSERT INTO orders1(code, status) VALUES(SUBSTRING(MD5(RAND()),1,20), FLOOR(RAND()*10));
   SELECT id, code, status FROM orders1;
   ```
   ```output
   +----------------------+----------------------+--------+
   | id                   | code                 | status |
   +----------------------+----------------------+--------+
   |  6341068275337658369 | a96328e33dd6b7ad2f15 |      8 |
   | 13835058055282163715 | 586ab8cf9fb194b5e4ca |      2 |
   | 14411518807585587205 | 21105132b2384ccab63b |      7 |
   |  8646911284551352324 | a80b9c0196c4e9256fd3 |      6 |
   |  7493989779944505346 | 3f43c82a9308a1467b85 |      2 |
   +----------------------+----------------------+--------+
   5 rows in set (0.02 sec)
   ```

+ 当不同事务执行插入操作时，我们发现主键列的值是随机的。

### 步骤 8. 清理 `orders1` 表

   ```sql
   DROP TABLE orders1;
   ```
   ```output
   Query OK, 0 rows affected (0.52 sec)
   ```

## 模块 2：非聚簇索引的最佳实践

+ 在本模块中，你将为主键表创建非聚簇索引，并进行相关设置以避免热点。

### 步骤 1. 查看 `orders2` 表的定义，指出潜在的性能问题

   ```sql
   CREATE TABLE orders2 
   ( id INT(20) NOT NULL ,
     code VARCHAR(30) NOT NULL,
     order_no VARCHAR(200) NOT NULL DEFAULT '', 
     status INT(4) NOT NULL,
     cancel_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
   );
   ```

+ 此表为主键创建了非聚簇索引，这会导致写入热点问题。为避免此问题，可以使用 `SHARD_ROW_ID_BITS` 和 `PRE_SPLIT_REGIONS`。
   
### 步骤 2. 重新创建 `orders2` 表

   ```sql
   DROP TABLE orders2;
   CREATE TABLE orders2
   ( id INT(20) NOT NULL ,
     code VARCHAR(30) NOT NULL,
     order_no VARCHAR(200) NOT NULL DEFAULT '', 
     status INT(4) NOT NULL,
     cancel_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
   ) SHARD_ROW_ID_BITS=4 PRE_SPLIT_REGIONS=3;
   SHOW CREATE TABLE orders2\G
   ```
   ```output
   *************************** 1. row ***************************
         Table: orders2
   Create Table: CREATE TABLE `orders2` (
      `id` INT(20) NOT NULL,
      `code` VARCHAR(30) NOT NULL,
      `order_no` VARCHAR(200) NOT NULL DEFAULT '',
      `status` INT(4) NOT NULL,
      `cancel_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
   ) 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)
   ```

### 步骤 3. 确认 `orders2` 表具有非聚簇索引

   ```sql
   SELECT TIDB_PK_TYPE FROM information_schema.tables WHERE table_schema = 'test' AND table_name = 'orders2';
   ```
   ```output
   +--------------+
   | TIDB_PK_TYPE |
   +--------------+
   | NONCLUSTERED |
   +--------------+
   1 row in set (0.02 sec)
   ```

### 步骤 4. 检查 `orders2` 表的 region 分布

+ 由于 `PRE_SPLIT_REGIONS` 为 3，`orders2` 表在创建时就有 8 个空区域可用于插入数据。

  
   ```sql
   SHOW TABLE orders2 REGIONS\G
   ```
   ```output
   *************************** 1. row ***************************
                REGION_ID: 748
                START_KEY: t_106_
                  END_KEY: t_106_r_1152921504606846976
                LEADER_ID: 750
          LEADER_STORE_ID: 2
                    PEERS: 749, 750, 751
               SCATTERING: 0
            WRITTEN_BYTES: 39
               READ_BYTES: 0
     APPROXIMATE_SIZE(MB): 1
         APPROXIMATE_KEYS: 0
   SCHEDULING_CONSTRAINTS: 
         SCHEDULING_STATE: 
   *************************** 2. row ***************************
                REGION_ID: 752
                START_KEY: t_106_r_1152921504606846976
                  END_KEY: t_106_r_2305843009213693952
                LEADER_ID: 754
          LEADER_STORE_ID: 2
                    PEERS: 753, 754, 755
               SCATTERING: 0
            WRITTEN_BYTES: 0
               READ_BYTES: 0
     APPROXIMATE_SIZE(MB): 1
         APPROXIMATE_KEYS: 0
   SCHEDULING_CONSTRAINTS: 
         SCHEDULING_STATE:
            
   ... ...

   *************************** 8. row ***************************
                REGION_ID: 34
                START_KEY: t_106_r_8070450532247928832
                  END_KEY: t_281474976710649_
                LEADER_ID: 139
          LEADER_STORE_ID: 2
                    PEERS: 35, 139, 186
               SCATTERING: 0
            WRITTEN_BYTES: 0
               READ_BYTES: 0
     APPROXIMATE_SIZE(MB): 1
         APPROXIMATE_KEYS: 0
   SCHEDULING_CONSTRAINTS: 
         SCHEDULING_STATE: 
   8 rows in set (0.05 sec)
   ```

### 步骤 5. 向 `orders2` 表插入数据

   ```sql
   BEGIN;
   INSERT INTO orders2(id, code, status) VALUES(1, SUBSTRING(MD5(RAND()),1,20), FLOOR(RAND()*10));
   INSERT INTO orders2(id, code, status) VALUES(1, SUBSTRING(MD5(RAND()),1,20), FLOOR(RAND()*10));
   INSERT INTO orders2(id, code, status) VALUES(2, SUBSTRING(MD5(RAND()),1,20), FLOOR(RAND()*10));
   INSERT INTO orders2(id, code, status) VALUES(2, SUBSTRING(MD5(RAND()),1,20), FLOOR(RAND()*10));
   INSERT INTO orders2(id, code, status) VALUES(9, SUBSTRING(MD5(RAND()),1,20), FLOOR(RAND()*10));
   COMMIT;
   SELECT id, code, status FROM orders2;
   SELECT _tidb_rowid, id, code, status FROM orders2;
   ```
   ```output
   +----+----------------------+--------+
   | id | code                 | status |
   +----+----------------------+--------+
   |  1 | 9cb0ad99d83a62915d6b |      4 |
   |  1 | 329dadd330d53bbe8aeb |      3 |
   |  2 | 999ca3ad7c2c61f61777 |      5 |
   |  2 | 74760a7e6f1f3a16ef64 |      7 |
   |  9 | 1e264cb3f696c5d9b94c |      2 |
   +----+----------------------+--------+
   5 rows in set (0.01 sec)

   +---------------------+----+----------------------+--------+
   | _tidb_rowid         | id | code                 | status |
   +---------------------+----+----------------------+--------+
   | 5764607523034234881 |  1 | 9cb0ad99d83a62915d6b |      4 |
   | 5764607523034234882 |  1 | 329dadd330d53bbe8aeb |      3 |
   | 5764607523034234883 |  2 | 999ca3ad7c2c61f61777 |      5 |
   | 5764607523034234884 |  2 | 74760a7e6f1f3a16ef64 |      7 |
   | 5764607523034234885 |  9 | 1e264cb3f696c5d9b94c |      2 |
   +---------------------+----+----------------------+--------+
   5 rows in set (0.00 sec)
   ```

+ 你会发现，尽管使用了 `SHARD_ROW_ID_BITS` ，生成的隐藏列 `_tidb_rowid` 仍然是连续的。

### 步骤 6. `TRUNCATE` `orders2` 表中的数据

+ 请注意，`TRUNCATE` 操作后，`orders2` 表仍有 8 个默认 region。这是因为 `orders2` 指定了 `PRE_SPLIT_REGIONS=3`，无需再次手动拆分。 
  
   ```sql
   TRUNCATE TABLE orders2;
   INSERT INTO orders2(id, code, status) VALUES(1, SUBSTRING(MD5(RAND()),1,20), FLOOR(RAND()*10)), \
   (1, SUBSTRING(MD5(RAND()),1,20), FLOOR(RAND()*10)), (2, SUBSTRING(MD5(RAND()),1,20), FLOOR(RAND()*10)), \
   (2, SUBSTRING(MD5(RAND()),1,20), FLOOR(RAND()*10)), (9, SUBSTRING(MD5(RAND()),1,20), FLOOR(RAND()*10));
   SELECT id, code, status FROM orders2;
   SELECT _tidb_rowid, id, code, status FROM orders2;
   ```
   ```output
   +----+----------------------+--------+
   | id | code                 | status |
   +----+----------------------+--------+
   |  1 | 71f193f2ed99f16bed50 |      4 |
   |  1 | 83664e98ca7e5991ac06 |      0 |
   |  2 | 79639074670bc1b9dfaf |      0 |
   |  2 | 446775a4c9850a10ce95 |      8 |
   |  9 | 1a2e539cc7aac470f468 |      4 |
   +----+----------------------+--------+
   5 rows in set (0.00 sec)

   +---------------------+----+----------------------+--------+
   | _tidb_rowid         | id | code                 | status |
   +---------------------+----+----------------------+--------+
   | 1152921504606846977 |  1 | 71f193f2ed99f16bed50 |      4 |
   | 1152921504606846978 |  1 | 83664e98ca7e5991ac06 |      0 |
   | 1152921504606846979 |  2 | 79639074670bc1b9dfaf |      0 |
   | 1152921504606846980 |  2 | 446775a4c9850a10ce95 |      8 |
   | 1152921504606846981 |  9 | 1a2e539cc7aac470f468 |      4 |
   +---------------------+----+----------------------+--------+
   5 rows in set (0.01 sec)
   ```

+ 结论是，只要插入语句在同一事务内，无论是否指定了表的 `SHARD_ROW_ID_BITS` 属性，生成的隐藏列 `_tidb_rowid` 仍会是连续的，仍有可能出现热点。下面我们将在不同事务中执行插入操作。


### 步骤 7. 在不同事务中插入数据

   ```sql
   TRUNCATE TABLE orders2;
   SET autocommit=ON;
   INSERT INTO orders2(id, code, status) VALUES(1, SUBSTRING(MD5(RAND()),1,20), FLOOR(RAND()*10));
   INSERT INTO orders2(id, code, status) VALUES(1, SUBSTRING(MD5(RAND()),1,20), FLOOR(RAND()*10));
   INSERT INTO orders2(id, code, status) VALUES(2, SUBSTRING(MD5(RAND()),1,20), FLOOR(RAND()*10));
   INSERT INTO orders2(id, code, status) VALUES(2, SUBSTRING(MD5(RAND()),1,20), FLOOR(RAND()*10));
   INSERT INTO orders2(id, code, status) VALUES(9, SUBSTRING(MD5(RAND()),1,20), FLOOR(RAND()*10));
   SELECT id, code, status FROM orders2;
   SELECT _tidb_rowid, id, code, status FROM orders2;
   ```
   ```output
   +----+----------------------+--------+
   | id | code                 | status |
   +----+----------------------+--------+
   |  9 | dbb9004292d00a8a0059 |      8 |
   |  1 | 219cf3b5989d4bf0973c |      7 |
   |  2 | e2d02bf318cca2040c71 |      4 |
   |  1 | 3c32ed407a200ed13f5c |      1 |
   |  2 | 74301f4a88de6434abd3 |      7 |
   +----+----------------------+--------+
   5 rows in set (0.00 sec)

   +---------------------+----+----------------------+--------+
   | _tidb_rowid         | id | code                 | status |
   +---------------------+----+----------------------+--------+
   | 8070450532247928837 |  9 | dbb9004292d00a8a0059 |      8 |
   | 2305843009213693953 |  1 | 219cf3b5989d4bf0973c |      7 |
   | 1729382256910270467 |  2 | 74301f4a88de6434abd3 |      7 |
   | 7493989779944505346 |  1 | 3c32ed407a200ed13f5c |      1 |
   | 4611686018427387908 |  2 | e2d02bf318cca2040c71 |      4 |
   +---------------------+----+----------------------+--------+
   5 rows in set (0.00 sec)
   ```

+ 当不同事务执行插入操作时，我们发现自动生成的隐藏列 `_tidb_rowid` 不连续。

### 步骤 8. 清理 `orders2` 表

   ```sql
   DROP TABLE orders2;
   ```
   ```output
   Query OK, 0 rows affected (0.52 sec)
   ```

## 模块 3：拆分表

+ 在本模块中，我们将为主键表创建聚簇索引。向表中插入数据后，我们将手动拆分表的区域，以防止读写操作出现热点。

### 步骤 1. 为主键表 `orders` 创建新的聚簇索引

   ```sql
   USE test;
   CREATE TABLE orders
   ( 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,
     cancel_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
   );
   SHOW CREATE TABLE orders\G
   ```

   ```output
   *************************** 1. row ***************************
         Table: orders
   Create Table: CREATE TABLE `orders` (
   `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,
   `cancel_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)
   ```

### 步骤 2. 向 `orders` 表插入一些数据

   ```sql
   INSERT INTO orders(code, order_no, status, cancel_flag, create_user, create_time) \
   VALUES(CONCAT('CODE_', LPAD(ROUND(RAND() * 1000), 3, 0)), SUBSTRING(MD5(RAND()),1,20), \
   FLOOR(RAND()*10), FLOOR(RAND()*10), 'Jones', now());
   INSERT INTO orders(code, order_no, status, cancel_flag, create_user, create_time) \ 
   VALUES(CONCAT('CODE_', LPAD(ROUND(RAND() * 1000), 3, 0)), SUBSTRING(MD5(RAND()),1,20), \ 
   FLOOR(RAND()*10), FLOOR(RAND()*10), 'Garcia', now());
   INSERT INTO orders(code, order_no, status, cancel_flag, create_user, create_time) \
   VALUES(CONCAT('CODE_', LPAD(ROUND(RAND() * 1000), 3, 0)), SUBSTRING(MD5(RAND()),1,20), \
   FLOOR(RAND()*10), FLOOR(RAND()*10), 'Davis', now());
   INSERT INTO orders(code, order_no, status, cancel_flag, create_user, create_time) \
   VALUES(CONCAT('CODE_', LPAD(ROUND(RAND() * 1000), 3, 0)), SUBSTRING(MD5(RAND()),1,20), \
   FLOOR(RAND()*10), FLOOR(RAND()*10), 'Adams', now());
   INSERT INTO orders(code, order_no, status, cancel_flag, create_user, create_time) \
   VALUES(CONCAT('CODE_', LPAD(ROUND(RAND() * 1000), 3, 0)), SUBSTRING(MD5(RAND()),1,20), \
   FLOOR(RAND()*10), FLOOR(RAND()*10), 'Edwards', now());
   INSERT INTO orders(code, order_no, status, cancel_flag, create_user, create_time) \
   VALUES(CONCAT('CODE_', LPAD(ROUND(RAND() * 1000), 3, 0)), SUBSTRING(MD5(RAND()),1,20), \
   FLOOR(RAND()*10), FLOOR(RAND()*10), 'Anderson', now());
   INSERT INTO orders(code, order_no, status, cancel_flag, create_user, create_time) \
   VALUES(CONCAT('CODE_', LPAD(ROUND(RAND() * 1000), 3, 0)), SUBSTRING(MD5(RAND()),1,20), \
   FLOOR(RAND()*10), FLOOR(RAND()*10), 'Morgan', now());
   INSERT INTO orders(code, order_no, status, cancel_flag, create_user, create_time) \
   VALUES(CONCAT('CODE_', LPAD(ROUND(RAND() * 1000), 3, 0)), SUBSTRING(MD5(RAND()),1,20), \
    FLOOR(RAND()*10), FLOOR(RAND()*10), 'Clark', now());
   INSERT INTO orders(code, order_no, status, cancel_flag, create_user, create_time) \
   VALUES(CONCAT('CODE_', LPAD(ROUND(RAND() * 1000), 3, 0)), SUBSTRING(MD5(RAND()),1,20), \
   FLOOR(RAND()*10), FLOOR(RAND()*10), 'Smith', now());
   INSERT INTO orders(code, order_no, status, cancel_flag, create_user, create_time) \
   VALUES(CONCAT('CODE_', LPAD(ROUND(RAND() * 1000), 3, 0)), SUBSTRING(MD5(RAND()),1,20), F\
   LOOR(RAND()*10), FLOOR(RAND()*10), 'Tom', now());
   SELECT COUNT(*) FROM orders;
   SELECT code, create_user FROM orders;
   ```
   ```output
   +----------+
   | COUNT(*) |
   +----------+
   |       10 |
   +----------+
   1 row in set (0.01 sec)

   +----------+-------------+
   | code     | create_user |
   +----------+-------------+
   | CODE_525 | Edwards     |
   | CODE_502 | Davis       |
   | CODE_224 | Morgan      |
   | CODE_370 | Jones       |
   | CODE_525 | Smith       |
   | CODE_832 | Tom         |
   | CODE_189 | Anderson    |
   | CODE_483 | Clark       |
   | CODE_963 | Garcia      |
   | CODE_257 | Adams       |
   +----------+-------------+
   10 rows in set (0.00 sec)
   ```

### 步骤 3. 拆分 `orders` 表以防止读写热点

   ```sql
   SHOW TABLE orders REGIONS\G
   SPLIT TABLE orders BETWEEN (0) and (9223372036854775807) regions 4;
   SHOW TABLE orders REGIONS\G
   ```
   ```output
   *************************** 1. row ***************************
                REGION_ID: 28
                START_KEY: t_110_
                  END_KEY: t_281474976710649_
                LEADER_ID: 187
          LEADER_STORE_ID: 2
                    PEERS: 29, 187, 261
               SCATTERING: 0
            WRITTEN_BYTES: 0
               READ_BYTES: 0
     APPROXIMATE_SIZE(MB): 1
         APPROXIMATE_KEYS: 0
   SCHEDULING_CONSTRAINTS: 
         SCHEDULING_STATE: 
   1 row in set (0.01 sec)

   +--------------------+----------------------+
   | TOTAL_SPLIT_REGION | SCATTER_FINISH_RATIO |
   +--------------------+----------------------+
   |                  3 |                    1 |
   +--------------------+----------------------+
   1 row in set (0.28 sec)

   *************************** 1. row ***************************
                REGION_ID: 426
                START_KEY: t_110_
                  END_KEY: t_110_r_2305843009213693951
                LEADER_ID: 427
          LEADER_STORE_ID: 1
                    PEERS: 427, 428, 429
               SCATTERING: 0
            WRITTEN_BYTES: 39
               READ_BYTES: 0
     APPROXIMATE_SIZE(MB): 1
         APPROXIMATE_KEYS: 0
   SCHEDULING_CONSTRAINTS: 
         SCHEDULING_STATE: 

   ... ...

   *************************** 4. row ***************************
                REGION_ID: 28
                START_KEY: t_110_r_6917529027641081853
                  END_KEY: t_281474976710649_
                LEADER_ID: 187
          LEADER_STORE_ID: 2
                    PEERS: 29, 187, 261
               SCATTERING: 0
            WRITTEN_BYTES: 1624
               READ_BYTES: 2290
     APPROXIMATE_SIZE(MB): 1
         APPROXIMATE_KEYS: 0
   SCHEDULING_CONSTRAINTS: 
         SCHEDULING_STATE: 
   4 rows in set (0.01 sec)
   ```

### 步骤 4. 清理 `orders` 表

   ```sql
   DROP TABLE orders;
   ```

   ```sql
   exit
   ```
   ```output
   Bye
   ```

## 模块 4: 使用流量可视化页面 (Key Visualizer)

+ 在本练习中，你将使用 sysbench 工具模拟并发数据插入，并采用 `AUTO_RANDOM` 选项尝试拆分热点。

### 步骤 1. 配置 [TiDB PD Dashboard](http://${LAB:HOST_PD1_PUBLIC_IP}:2379/dashboard) `key visualizer` 

+ 在 [TiDB PD Dashboard](http://${LAB:HOST_PD1_PUBLIC_IP}:2379/dashboard) 的 `key visualizer` 中，将刷新间隔设置为 `30s`。
+ 你可以在页面左侧的资源选项卡中找到 [TiDB PD Dashboard](http://${LAB:HOST_PD1_PUBLIC_IP}:2379/dashboard) 的 URL。默认用户名是 `root` ，无密码。
   
### 步骤 2. 阅读脚本 `oltp_common_1.lua`

+ 请注意  `CREATE TABLE` 语句。思考使用的表创建方法可能会出现什么问题。

   ```bash
   cat oltp_common_1.lua 
   ```
   ```output
   cat oltp_common_1.lua 
   ...
   query = string.format([[
   CREATE TABLE sbtest%d(
     id INT(11) NOT NULL AUTO_INCREMENT,
     k INT(11) NOT NULL DEFAULT '0',
     c CHAR(120) NOT NULL DEFAULT '',
     pad CHAR(60) DEFAULT '' NOT NULL,
     PRIMARY KEY (id)
   ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin]],
         table_num)
   
   con:query(query)
   ...
   ```

+  创建的表为主键创建了聚簇索引。它在主键列 `id` 上使用了 `AUTO_INCREMENT`。这可能会在并发插入时导致热点问题。

### 步骤 3. 查看 sysbench 工具的配置文件

   ```bash
   cat config_lab01
   ```

   ```output
   mysql-host=10.90.1.178 
   mysql-port=4000 
   mysql-user=root 
   mysql-db=sbtest 
   mysql-password=
   time=600 
   threads=32 
   report-interval=10 
   db-driver=mysql
   ```

+ 以下是更详细的解释：
   +  time: 测试的运行时间。
   +  thread: 并发会话数。
   +  report-interval: 打印报告的间隔（单位：秒）。
   +  db-driver: MySQL 兼容语法。

### 步骤 4. 创建一个新的数据库用于 sysbench 测试

   ```bash
   mysql -h ${HOST_DB1_PRIVATE_IP} --port 4000 -u root
   ```
   ```sql
   CREATE DATABASE sbtest;
   EXIT;
   ```

### 步骤 5. 运行 sysbench 模拟并发数据插入场景

+ 此步骤可能需要长达 5 分钟，在此过程中，我们可以通过 [TiDB PD Dashboard](http://${LAB:HOST_PD1_PUBLIC_IP}:2379/dashboard) 中的流量可视化图表监控是否生成热点。 

   ```bash
   sysbench './oltp_common_1.lua' --config-file=config_lab01 --tables=1 --table-size=5000000 prepare
   ```
   ```output
    sysbench './oltp_common_1.lua' --config-file=config_lab01 --tables=1 --table-size=5000000 prepare
   sysbench 1.0.17 (using bundled LuaJIT 2.1.0-beta2)

   Initializing worker threads...

   Creating table 'sbtest1'...
   Inserting 20000000 records into 'sbtest1'
   ```

+ table-size : 将插入表中的行数。
+ sysbench 测试完成后，在 [TiDB PD Dashboard](http://${LAB:HOST_PD1_PUBLIC_IP}:2379/dashboard) 的流量可视化图表中观察 1 小时内的写入流量。以下图表是插入 20,000,000 条数据后生成的。你的图表可能不同。思考为什么会出现这种情况以及是否可以优化。

   ![30%](https://lab-static.pingcap.com/quick-demo/TableAndIdnexDesignSysBench1_aa_001_20231121.png)
   + 因为该表是主键的聚簇索引，并使用 `AUTO_INCREMENT` 定义主键，在持续插入数据时，我们发现同时写入压力集中在某个区域，这意味着存在热点问题。

### 步骤 6. 清理 `oltp_common_1.lua` 的数据

   ```bash
   sysbench './oltp_common_1.lua' --config-file=config_lab01 cleanup
   ```
   ```output
    sysbench './oltp_common_1.lua' --config-file=config_lab01 cleanup
   sysbench 1.0.17 (using bundled LuaJIT 2.1.0-beta2)

   Dropping table 'sbtest1'...
   ```

### 步骤 7. 阅读脚本 `oltp_common_2.lua`

+ 请注意 `CREATE TABLE` 语句。

   ```bash
   cat oltp_common_2.lua 
   ```
   ```output
   ...
   query = string.format([[
   CREATE TABLE sbtest%d(
   id bigINT(11) NOT NULL AUTO_RANDOM(5),
   k INT(11) NOT NULL DEFAULT '0',
   c CHAR(120) NOT NULL DEFAULT '',
   pad CHAR(60) DEFAULT '' NOT NULL,
   PRIMARY KEY (id)
   ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin]],
         table_num)

   con:query(query)
   ...
   ```

### 步骤 8. 运行 sysbench 模拟并发数据插入场景

+ 同样，此步骤可能需要 5 分钟。在此过程中，我们可以使用 [TiDB PD Dashboard](http://${LAB:HOST_PD1_PUBLIC_IP}:2379/dashboard) 中的流量可视化图表监控是否生成热点。

   ```bash
   sysbench './oltp_common_2.lua' --config-file=config_lab01 --tables=1 --table-size=5000000 prepare
   ```
   ```output
   sysbench './oltp_common_2.lua' --config-file=config_lab01 --tables=1 --table-size=5000000 prepare
   sysbench 1.0.17 (using bundled LuaJIT 2.1.0-beta2)

   Initializing worker threads...

   Creating table 'sbtest1'...
   Inserting 5000000 records into 'sbtest1'
   ```

+ sysbench 测试完成后，在 [TiDB PD Dashboard](http://${LAB:HOST_PD1_PUBLIC_IP}:2379/dashboard) 的流量可视化图表中观察 1 小时内的流入流量。以下图表是插入 20,000,000 条数据后生成的。你的图表可能不同。思考原因并与之前的测试进行比较，判断热点问题是否已解决。

   ![30%](https://lab-static.pingcap.com/quick-demo/TableAndIdnexDesignSysBench2_aa_001_20231121.png)

### 步骤 9. 清理 `oltp_common_2.lua` 的数据

   ```bash
   sysbench './oltp_common_2.lua' --config-file=config_lab01 cleanup
   ```
   ```output
    sysbench './oltp_common_2.lua' --config-file=config_lab01 cleanup
   sysbench 1.0.17 (using bundled LuaJIT 2.1.0-beta2)

   Dropping table 'sbtest1'...
   ```

## 实验结束