# Lab 1: Clustered and Non-Clustered Indexes

### Overview
+ In these exercises, you will create both clustered and non-clustered indexes for primary key tables and perform related settings to avoid hotspots. You will create a clustered index for primary key table, split the table regions to avoid hotspot issues. We will practice how to detect hotspots through monitoring and avoid hotspot issues by modifying the table structure.

### Notes
+ Click [pe-class-key-oregon.pem](lab-controller/class_303_get_ec2_key_for_lab.php?region=oregon) to download the private key file, which is required to log into the Control Machine for this lab.
+ Commands and SQL statements presented on this page are clickable for easy copy and paste.
+ Your environment may be different from the one shown on the lab guide, including but not limited to IP, port, username, password and directory. Example output is displayed in a gray background in this guide.
+ The `$` in your terminal window is an OS prompt. The `tidb>` or `mysql>` in your terminal window is SQL command prompt.

### Assumptions
+ The private key file is downloaded and placed under `~/.ssh/` folder locally. Relevant permission has been provided by [chmod 400 ~/.ssh/pe-class-key-oregon.pem](lab-controller/class_303_get_ec2_key_for_lab.php?region=oregon)
+ For Windows users, please refer to this [guide](https://github.com/pingcap-inc/tidb-course-labs/blob/master/README_303_class_setup_guide_for_student_lab_control_en.md) for the private key preparation.

## Exercise 1 
### Subject: Best practice for clustered index

### Overview
+ In this exercise, you will create both clustered indexes for primary key tables and perform related settings to avoid hotspots.

### Duration
+ This exercise should take you approximately 10 minutes to complete.

### Tasks
+ One of your colleagues has created a table `orders1` in the TiDB database. This table will have a high concurrency of insertions in the application. The definition is shown below. Point out the performance issues that may occur with this table, and rebuild the `orders1` table, to make sure that the `orders1` table has a clustered index for primary key table.
   ```output
   USE test;
   CREATE TABLE orders1 
   ( id INT(20) unsigned AUTO_INCREMENT 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) 
   );
   ```
+ Insert data into the `orders1` table five times and observe the data distribution.

### Solution

0. Login as user ec2-user on monitor1_address. If you do not possess the credentials to connect, kindly review the <kbd>Assumptions</kbd> section under the <kbd>OVERVIEW</kbd> tab. The returned message may be slightly different from what is displayed here. If prompted, answer `yes`.
   ```command
   $ ssh -i ~/.ssh/pe-class-key-oregon.pem ec2-user@monitor1_address
   ```
   ```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/
   ```

1. Connect to TiDB.
   ```command
   $ mysql -h ${HOST_DB1_PRIVATE_IP} --port 4000 -u root
   ```

   ```command
   use test;
   ```
   ```output
   Database changed
   ```

2. Observe the original `orders1` table, point out the major issues.
   ```output
   USE test;
   CREATE TABLE orders1 
   ( id INT(20) unsigned AUTO_INCREMENT 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) 
   );
   ```
   + Major issues include: (1) `orders1` uses of AUTO_INCREMENT for the primary key. It is recommended to change AUTO_INCREMENT to AUTO_RANDOM to avoid hotspot issues caused by inserted data. (2) Due to the large amount of data insertion, it is recommended to split the table in advance during table creation.

3. Rebuild the `orders1` table, since we need to use AUTO_RANDOM instead of AUTO_INCREMENT, the `id` column needs to be changed from `int` type to `bigint` type. 
   ```command
   USE test;
   CREATE TABLE orders1 
   ( 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 orders1\G
   ```
   ```output
   tidb> USE test;
   Database changed
   tidb> CREATE TABLE orders1 
       ->     ( 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
       ->     );
   Query OK, 0 rows affected, 1 warning (0.53 sec)

   tidb>SHOW CREATE TABLE orders1\G
   *************************** 1. row ***************************
   Table: orders1
   Create Table: CREATE TABLE `orders1` (
     `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.01 sec)
   ```

4. Check the Region distribution of the `orders1` table and manually split it.
   ```command
   SHOW TABLE orders1 REGIONS\G
   SPLIT TABLE orders1 BETWEEN(0) AND (9223372036854775807) REGIONS 16;
   SHOW TABLE orders1 REGIONS\G
   ```
   ```output
   tidb>SHOW TABLE orders1 REGIONS\G
   *************************** 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)

   tidb>SPLIT TABLE orders1 BETWEEN(0) AND (9223372036854775807) REGIONS 16;
   +--------------------+----------------------+
   | TOTAL_SPLIT_REGION | SCATTER_FINISH_RATIO |
   +--------------------+----------------------+
   |                 15 |                    1 |
   +--------------------+----------------------+
   1 row in set (0.48 sec)

   tidb>SHOW TABLE orders1 REGIONS\G
   *************************** 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. Insert data into the created `orders1` table five times and observe the distribution of the data. 
   ```command
   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;
   SELECT id, code, status FROM orders1;
   ```
   ```output
   tidb>BEGIN;
   Query OK, 0 rows affected (0.00 sec)

   tidb>INSERT INTO orders1(code, status) VALUES(SUBSTRING(MD5(RAND()),1,20), FLOOR(RAND()*10));
   Query OK, 1 row affected (0.01 sec)

   tidb>INSERT INTO orders1(code, status) VALUES(SUBSTRING(MD5(RAND()),1,20), FLOOR(RAND()*10));
   Query OK, 1 row affected (0.01 sec)

   tidb>INSERT INTO orders1(code, status) VALUES(SUBSTRING(MD5(RAND()),1,20), FLOOR(RAND()*10));
   Query OK, 1 row affected (0.00 sec)

   tidb>INSERT INTO orders1(code, status) VALUES(SUBSTRING(MD5(RAND()),1,20), FLOOR(RAND()*10));
   Query OK, 1 row affected (0.00 sec)

   tidb>INSERT INTO orders1(code, status) VALUES(SUBSTRING(MD5(RAND()),1,20), FLOOR(RAND()*10));
   Query OK, 1 row affected (0.00 sec)

   tidb>COMMIT;
   Query OK, 0 rows affected (0.00 sec)

   tidb>SELECT id, code, status FROM orders1;
   +---------------------+----------------------+--------+
   | 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)
   ```
   + Although the AUTO_RANDOM attribute has been specified for the primary key in the `orders1` table, the generated primary key values are still continuous.

6. `TRUNCATE` the data in the `orders1` table and insert new data again. After `TRUNCATE`, there will only be 1 region left in the `orders1` table, so it needs to be splitted manually.
   ```command
   TRUNCATE TABLE orders1;
   SPLIT TABLE orders1 BETWEEN(0) AND (9223372036854775807) REGIONS 16;
   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
   tidb>TRUNCATE TABLE orders1;
   Query OK, 0 rows affected (0.54 sec)

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

   tidb>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));
   Query OK, 5 rows affected (0.02 sec)
   Records: 5  Duplicates: 0  Warnings: 0

   tidb>SELECT id, code, status FROM orders1;
   +---------------------+----------------------+--------+
   | 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)
   ```
   + The conclusion is that as long as the insertion statements are within the same transaction, regardless of whether AUTO_RANDOM is specified or not, the primary key values will be continuous and there is still a possibility of hotspots. 
  
7. Perform the insertion operation in different transactions.
   ```command
   TRUNCATE TABLE orders1;
   SPLIT TABLE orders1 BETWEEN(0) AND (9223372036854775807) REGIONS 16;
   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
   tidb> TRUNCATE TABLE orders1;
   Query OK, 0 rows affected (0.53 sec)

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

   tidb> SET autocommit=ON;
   Query OK, 0 rows affected (0.00 sec)

   tidb> INSERT INTO orders1(code, status) VALUES(SUBSTRING(MD5(RAND()),1,20), FLOOR(RAND()*10));
   Query OK, 1 row affected (0.02 sec)

   tidb> INSERT INTO orders1(code, status) VALUES(SUBSTRING(MD5(RAND()),1,20), FLOOR(RAND()*10));
   Query OK, 1 row affected (0.00 sec)

   tidb> INSERT INTO orders1(code, status) VALUES(SUBSTRING(MD5(RAND()),1,20), FLOOR(RAND()*10));
   Query OK, 1 row affected (0.01 sec)

   tidb> INSERT INTO orders1(code, status) VALUES(SUBSTRING(MD5(RAND()),1,20), FLOOR(RAND()*10));
   Query OK, 1 row affected (0.00 sec)

   tidb> INSERT INTO orders1(code, status) VALUES(SUBSTRING(MD5(RAND()),1,20), FLOOR(RAND()*10));
   Query OK, 1 row affected (0.01 sec)

   tidb> SELECT id, code, status FROM orders1;
   +----------------------+----------------------+--------+
   | 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)
   ```
   + When different transactions perform insertion, we found that the values of the primary key column are random.

8. Clean up table `orders1`. 
   ```command
   DROP TABLE orders1;
   ```
   ```output
   tidb>DROP TABLE orders1;
   Query OK, 0 rows affected (0.52 sec)
   ```

## Exercise 2
### Subject: Best practice for non-clustered index

### Overview
+ In this exercise, you will create non-clustered indexes for primary key tables and perform related settings to avoid hotspots.

### Duration
+ This exercise should take you approximately 10 minutes to complete.

### Tasks
+ One of your colleagues has created a table `orders2` in the TiDB database. This table will have a high concurrency of insertions in the application. The definition is shown below. Point out the performance issues that may occur with this table, and rebuild the `orders2` table, to make sure that the `orders2` table has a non-clustered index for primary key table.
   ```output
   USE test;
   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
   );
   ```
+ Insert data into the `orders2` table five times and observe the data distribution.
+ Clean up the `orders2` table.

### Solution
1. Investigate the original `orders2` table. This table has a non-clustered index for primary key, it will cause a write hotspot problem. To avoid this, SHARD_ROW_ID_BITS and PRE_SPLIT_REGIONS can be used.
   ```output
   USE test;
   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
   );
   ```
   
2. Rebuild the `orders2` table，
   ```command
   USE test;
   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
   tidb> USE test;
   Database changed
   tidb>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;
   Query OK, 0 rows affected (0.54 sec)

   tidb>SHOW CREATE TABLE orders2\G
   *************************** 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. Confirm `orders2` table has a non-clustered index for primary key. 
   ```command
   SELECT TIDB_PK_TYPE FROM information_schema.tables WHERE table_schema = 'test' AND table_name = 'orders2';
   ```
   ```output
   tidb>SELECT TIDB_PK_TYPE FROM information_schema.tables WHERE table_schema = 'test' AND table_name = 'orders2';
   +--------------+
   | TIDB_PK_TYPE |
   +--------------+
   | NONCLUSTERED |
   +--------------+
   1 row in set (0.02 sec)
   ```

4. Check the distribution of regions of the `orders2` table. Since `PRE_SPLIT_REGIONS` is 3, the `orders2` table has 8 empty regions available for insertion at the time of creation.
   ```command
   SHOW TABLE orders2 REGIONS\G
   ```
   ```output
   tidb>SHOW TABLE orders2 REGIONS\G
   *************************** 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. Insert data into the newly created `orders2` table five times and observe the distribution of the data.  
   ```command
   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
   tidb>BEGIN;
   Query OK, 0 rows affected (0.00 sec)

   tidb>INSERT INTO orders2(id, code, status) VALUES(1, SUBSTRING(MD5(RAND()),1,20), FLOOR(RAND()*10));
   Query OK, 1 row affected (0.01 sec)

   tidb>INSERT INTO orders2(id, code, status) VALUES(1, SUBSTRING(MD5(RAND()),1,20), FLOOR(RAND()*10));
   Query OK, 1 row affected (0.00 sec)

   tidb>INSERT INTO orders2(id, code, status) VALUES(2, SUBSTRING(MD5(RAND()),1,20), FLOOR(RAND()*10));
   Query OK, 1 row affected (0.01 sec)

   tidb>INSERT INTO orders2(id, code, status) VALUES(2, SUBSTRING(MD5(RAND()),1,20), FLOOR(RAND()*10));
   Query OK, 1 row affected (0.00 sec)

   tidb>INSERT INTO orders2(id, code, status) VALUES(9, SUBSTRING(MD5(RAND()),1,20), FLOOR(RAND()*10));
   Query OK, 1 row affected (0.00 sec)

   tidb>COMMIT;
   Query OK, 0 rows affected (0.00 sec)

   tidb>SELECT id, code, status FROM orders2;
   +----+----------------------+--------+
   | 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>SELECT _tidb_rowid, id, code, status FROM orders2;
   +---------------------+----+----------------------+--------+
   | _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)
   ```
   + You can find that, although the `SHARD_ROW_ID_BITS` is used, the generated hidden column `_tidb_rowid` is still continuous. 

6. `TRUNCATE` the data in `orders2` and insert new data. Note that after `TRUNCATE`, `orders2` stills has 8 default regions. This is because that `orders2` specifies the `PRE_SPLIT_REGIONS=3`.There is no need to be manually split again. 
   ```command
   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
   tidb>TRUNCATE TABLE orders2;
   Query OK, 0 rows affected (0.54 sec)

   tidb>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));
   Query OK, 5 rows affected (0.02 sec)
   Records: 5  Duplicates: 0  Warnings: 0

   tidb>SELECT id, code, status FROM orders2;
   +----+----------------------+--------+
   | 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>SELECT _tidb_rowid, id, code, status FROM orders2;
   +---------------------+----+----------------------+--------+
   | _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)
   ```

   + The conclusion is that as long as the insert statements are within the same transaction, regardless of whether the `SHARD_ROW_ID_BITS` property of the table is specified or not, the generated hidden column `_tidb_rowid` will still be continuous, and there may still be a possibility of hotspot. Below we will perform the insert operations in different transactions.

7. Insert data in different transactions
   ```command
   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
   tidb>TRUNCATE TABLE orders2;
   Query OK, 0 rows affected (0.53 sec)

   tidb>SET autocommit=ON;
   Query OK, 0 rows affected (0.00 sec)

   tidb>INSERT INTO orders2(id, code, status) VALUES(1, SUBSTRING(MD5(RAND()),1,20), FLOOR(RAND()*10));
   Query OK, 1 row affected (0.03 sec)

   tidb>INSERT INTO orders2(id, code, status) VALUES(1, SUBSTRING(MD5(RAND()),1,20), FLOOR(RAND()*10));
   Query OK, 1 row affected (0.01 sec)

   tidb>INSERT INTO orders2(id, code, status) VALUES(2, SUBSTRING(MD5(RAND()),1,20), FLOOR(RAND()*10));
   Query OK, 1 row affected (0.00 sec)

   tidb>INSERT INTO orders2(id, code, status) VALUES(2, SUBSTRING(MD5(RAND()),1,20), FLOOR(RAND()*10));
   Query OK, 1 row affected (0.01 sec)

   tidb>INSERT INTO orders2(id, code, status) VALUES(9, SUBSTRING(MD5(RAND()),1,20), FLOOR(RAND()*10));
   Query OK, 1 row affected (0.00 sec)

   tidb>SELECT id, code, status FROM orders2;
   +----+----------------------+--------+
   | 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>SELECT _tidb_rowid, id, code, status FROM orders2;
   +---------------------+----+----------------------+--------+
   | _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)
   ```
   + When different transactions perform inserts, we found that the automatically generated hidden column `_tidb_rowid` is not continuous.

8. Clean up table `orders2`. 
   ```command
   DROP TABLE orders2;
   ```
   ```output
   tidb>DROP TABLE orders2;
   Query OK, 0 rows affected (0.52 sec)
   ```

## Exercise 3
### Subject: Splitting a table

### Overview
+ In this exercise, we will create a clustered index for primary key table. After inserting data into the table, we will manually split the regions of the table to prevent hotspots in read and write operations.

### Duration
+ This exercise should take you approximately 10 minutes to complete.

### Tasks
+ Create a new Clustered table `orders`.
+ Insert 10 rows into the table `orders`.
+ Split the table `orders`.
+ Drop table table `orders`.

### Solution
1. Create a new clustered index for primary key table `orders`. Enter the following command at the terminal prompt and receive the results shown: 
   ```command
   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
   tidb>USE test;
   Database changed
   tidb>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
      -> );
   Query OK, 0 rows affected, 1 warning (0.54 sec)

   tidb>SHOW CREATE TABLE orders\G
   *************************** 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. Insert 10 rows into the table `orders`. 
   ```command
   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), FLOOR(RAND()*10), FLOOR(RAND()*10), 'Tom', now());
   SELECT COUNT(*) FROM orders;
   SELECT code, create_user FROM orders;
   ```
   ```output
   tidb>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());
   Query OK, 1 row affected (0.02 sec)

   tidb>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());
   Query OK, 1 row affected (0.00 sec)

   tidb>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());
   Query OK, 1 row affected (0.01 sec)

   tidb>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());
   Query OK, 1 row affected (0.00 sec)

   tidb>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());
   Query OK, 1 row affected (0.00 sec)

   tidb>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());
   Query OK, 1 row affected (0.01 sec)

   tidb>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());
   Query OK, 1 row affected (0.01 sec)

   tidb>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());
   Query OK, 1 row affected (0.00 sec)

   tidb>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());
   Query OK, 1 row affected (0.00 sec)

   tidb>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), 'Tom', now());
   Query OK, 1 row affected (0.00 sec)

   tidb>SELECT COUNT(*) FROM orders;
   +----------+
   | COUNT(*) |
   +----------+
   |       10 |
   +----------+
   1 row in set (0.01 sec)

   tidb>SELECT code, create_user FROM orders;
   +----------+-------------+
   | 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. Split the table `orders` to prevent from reading and writing hotspot. 
   ```command
   SHOW TABLE orders REGIONS\G
   SPLIT TABLE orders BETWEEN (0) and (9223372036854775807) regions 4;
   SHOW TABLE orders REGIONS\G
   ```
   ```output
   tidb>SHOW TABLE orders REGIONS\G
   *************************** 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)

   tidb>SPLIT TABLE orders BETWEEN (0) and (9223372036854775807) regions 4;
   +--------------------+----------------------+
   | TOTAL_SPLIT_REGION | SCATTER_FINISH_RATIO |
   +--------------------+----------------------+
   |                  3 |                    1 |
   +--------------------+----------------------+
   1 row in set (0.28 sec)

   tidb>SHOW TABLE orders REGIONS\G
   *************************** 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. Clean up the `orders` table. 
   ```command
   DROP TABLE orders;
   ```
   ```output
   tidb>DROP TABLE orders;
   Query OK, 0 rows affected (0.53 sec)
   ```

   ```command
   exit
   ```
   ```output
   tidb>exit
   Bye
   ```

## Exercise 4
### Subject: Key visualizer 

### Overview
+ In this exercise, you will use the sysbench tool to simulate concurrent data insertion and adopt the `AUTO_RANDOM` option to attempt to split hotspots.

### Duration
+ This exercise should take you approximately 40 minutes to complete.

### Tasks
+ Check the sysbench script `oltp_common_1.lua`, and find the `CREATE TABLE` statement. What are the characteristics of this table? What performance problems may it cause? Run sysbench with `oltp_common_1.lua`, and observe the output of the `key visualizer` in `TiDB Dashboard`.
+ Check the sysbench script `oltp_common_2.lua`, find the `CREATE TABLE` statement, and consider whether optimizing the table structure can avoid the performance issues.  Run sysbench with `oltp_common_2.lua`, and observe the output of the `key visualizer` in `TiDB Dashboard`.

### Solution
1. In the `key visualizer` of `TiDB Dashboard`, set the refresh interval to `30s`.
   
2. Examine the test script `oltp_common_1.lua` provided in this exercise and pay attention to the `CREATE TABLE` statement. Consider what problems may arise from the method of table creation used. 
   ```command
   $ 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)
   ...
   ```
   +  The table created is a clustered index for primary key, using `AUTO_INCREMENT` on the primary key column `id`. This may result in hotspot problems during concurrent insertion.

3. Check the configuration file of sysbench tool. 
   ```command
   $ cat config_lab01
   ```

   ```output
   $ cat config_lab01
   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: The running time of the test.
   +  thread: The number of concurrent sessions.
   +  report-interval: Printing report interval (unit: seconds).
   +  db-driver: MySQL compatible syntax.

4. Connect to TiDB and create a new database named `sbtest` for sysbench testing, and exit. 
   ```command
   $ mysql -h ${HOST_DB1_PRIVATE_IP} --port 4000 -u root
   ```
   ```command
   CREATE DATABASE sbtest;
   EXIT;
   ```
   ```output
   tidb>CREATE DATABASE sbtest;
   Query OK, 0 rows affected (0.14 sec)
   tidb>EXIT
   Bye
   ```

5. Start sysbench to simulate the scenario of concurrent data insertion. This step may take up to 5 minutes, and during the process, we can monitor whether there are hotspots generated through the traffic visualization chart in TiDB Dashboard.  
   ```command
   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 : The number of rows will be inserted to the table.

6. After the completion of the sysbench test, observe the write traffic within 1 hour in the traffic visualization chart in TiDB Dashboard. The following figure is generated with 20,000,000 insertions. Your figure might be different. Consider why this occurs and whether it can be optimized.

   ![30](https://pingcap-edu.s3.us-west-2.amazonaws.com/quick-demo/TableAndIdnexDesignSysBench1_aa_001_20231121.png)
   + Because the table is a clustered index for primary key and uses `AUTO_INCREMENT` to define the primary key, when continuously inserting data, we found that at the same time, the write pressure is concentrated on a certain region, which means there is a hotspot problem.

7. Check the `oltp_common_2.lua` script provided in this exercise's sysbench test and pay attention to the `CREATE TABLE` statement.
   ```command
   cat oltp_common_2.lua 
   ```
   ```output
   $ cat oltp_common_2.lua 
   ...
   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. Clean up the data of `oltp_common_1.lua`, and restart sysbench and simulate concurrent data insertion. This step may take 5 minutes. During this process, we can monitor whether hotspots are generated by using the traffic visualization graph in the TiDB Dashboard.
   ```command
   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'...
   ```

   ```command
   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'
   ```

9.  After the completion of the sysbench test, observe the incoming flow within 1 hour in the traffic visualization graph in TiDB Dashboard. The following figure is generated with 20,000,000 insertions. Your figure might be different. Consider the reasons and compare with the previous test to determine if the hotspot issue has been solved.

   ![30](https://pingcap-edu.s3.us-west-2.amazonaws.com/quick-demo/TableAndIdnexDesignSysBench2_aa_001_20231121.png)
   + Change the `AUTO_INCREMENT` attribute of the primary key to `AUTO_RANDOM`, so that the data will not be concentrated in the same region when inserting data, effectively solving the hotspot issue.

10. Clean up the data of `oltp_common_2.lua`. 
   ```command
   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'...
   ```
## End of Exercises