---
title: "Clustered and Non-Clustered Indexes"
version: "v2"
status: "GA"
description: "In this hands-on lab, you will learn how to create clustered and non-clustered indexes. Specifically, we will cover the best practices of using clustered and non-clustered indexes, split the TiKV regions to prevent hotspots, and use the key visualizer."
---

# Lab 1: Clustered and Non-Clustered Indexes

## Overview

>[!NOTE]
> This hands-on lab is also included in the [TiDB Certification Training: TiDB SQL Tuning](https://www.pingcap.com/education/course-tidb-sql-tuning/) course.

In this lab, 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.

### Duration

+ This lab should take you approximately 60 minutes to complete.

> [!TIP]
> Once the lab environment is deployed, click **Next** on the bottom bar to begin **Module 1**.

## Module 1: Best Practices For Clustered Indexes

In this module, you will create clustered indexes for primary key tables and configure related settings to prevent data insertion hotspots.

### Tasks

+ Created a table named `orders1` by a given DDL. The table will have a high concurrency of insertions by the application design. 
+ Identify any potential performance issues that may arise with table `orders1`.
+ Recreate the `orders1` table to ensure it includes a clustered index for the primary key.
+ Perform multiple data insertions into the `orders1` table and analyze the resulting data distribution.

### Step 1. Log in to the remote Linux

+ Click here - [Terminal 1](ssh://monitor1-ssh#1) to open a new terminal connected to the virtual machine assigned for your hands-on lab.

+ The banner after you logged in:

    ```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/
    ```

### Step 2. Connect to database `test`

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

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

### Step 3. Observe the table definition of `orders1`, point out the potential performance problem

+ The table definition is shown below, click the `Explain with AI` button to get more information.

   ```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) 
   );
   ```

+ The table `orders1` utilizes `AUTO_INCREMENT` for its primary key. It is advisable to replace `AUTO_INCREMENT` with `AUTO_RANDOM` to prevent hotspot issues that may arise from sequential data insertion.

+ Considering the expected large volume of data insertion, it is recommended to preemptively partition the table by splitting the TiKV regions during the initial table creation.

### Step 4. Recreate table `orders1` as per the best practice

+ Create 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.

   ```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)
   ```

+ Check the Region distribution of the `orders1` table.

   ```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)
   ```

+ Split `orders1` table into multiple TiKV regions.

   ```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)
   ```

### Step 5. Insert data into the `orders1` table five times

   ```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 ORDER BY id;
   ```
   ```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)
   ```

+ Although the AUTO_RANDOM attribute has been specified for the primary key in the `orders1` table, the generated primary key values are still continuous.

### Step 6. `TRUNCATE` the data in the `orders1` table and insert new data

+ Note that, after `TRUNCATE`, there will only be 1 region left in the `orders1` table, so it needs to be split manually.
  
   ```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 ORDER BY id;
   ```
   ```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)
   ```

+ 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. 
  
### Step 7. Insert the data in different transactions

   ```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 ORDER BY id;
   ```
   ```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)
   ```

+ When different transactions perform insertion, we found that the values of the primary key column are random.

### Step 8. Clean up table `orders1`

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

## Module 2: Best Practices For Non-Clustered Indexes

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

### Step 1. Observe the table definition of `orders2`, point out the potential performance problem

   ```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
   );
   ```

+ This table has a non-clustered index for primary key, it will cause a write hotspot problem. To avoid this, you can use `SHARD_ROW_ID_BITS` and `PRE_SPLIT_REGIONS`.
   
### Step 2. Rebuild the `orders2` table

   ```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)
   ```

### Step 3. Confirm `orders2` table has a non-clustered index

   ```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)
   ```

### Step 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.
  
   ```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)
   ```

### Step 5. Insert data into the `orders2` table

   ```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 ORDER BY _tidb_rowid;
   ```
   ```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)
   ```

+ You can find that, although the `SHARD_ROW_ID_BITS` is used, the generated hidden column `_tidb_rowid` is still continuous. 

### Step 6. `TRUNCATE` the data in `orders2`

+ 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. 
  
   ```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 ORDER BY _tidb_rowid;
   ```
   ```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)
   ```

+ 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.

### Step 7. Insert data in different transactions

   ```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 ORDER BY _tidb_rowid;
   ```
   ```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)
   ```

+ When different transactions perform inserts, we found that the automatically generated hidden column `_tidb_rowid` is not continuous.

### Step 8. Clean up table `orders2`

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

## Module 3: Splitting a Table

+ 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.

### Step 1. Create a new clustered index for primary key table `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)
   ```

### Step 2. Insert some data into the table `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), \
   FLOOR(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)
   ```

### Step 3. Split the table `orders` to prevent from reading and writing hotspot. 
   ```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)
   ```

### Step 4. Clean up the `orders` table 
   ```sql
   DROP TABLE orders;
   ```

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

## Module 4: Using Key Visualizer 

+ 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.

### Step 1. Config the `key visualizer` of [TiDB PD Dashboard](http://${LAB:HOST_PD1_PUBLIC_IP}:2379/dashboard)

+ In the `key visualizer` of [TiDB PD Dashboard](http://${LAB:HOST_PD1_PUBLIC_IP}:2379/dashboard), set the refresh interval to `30s`. The default user name is `root` with no password.
   
### Step 2. Read the script `oltp_common_1.lua`

+ Pay attention to the `CREATE TABLE` statement. Consider what problems may arise from the method of table creation used. 

   ```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)
   ...
   ```

+  The table created has a clustered index for primary key. It uses `AUTO_INCREMENT` on the primary key column `id`. This may result in hotspot problems during concurrent insertion.

### Step 3. Check the configuration file of sysbench tool

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

+ Here are more detailed explanation:
   +  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.

### Step 4. Create a new database for sysbench testing

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

### Step 5. Run 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 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 : The number of rows will be inserted to the table.
+ After the completion of the sysbench test, observe the write traffic within 1 hour in the traffic visualization chart in [TiDB PD Dashboard](http://${LAB:HOST_PD1_PUBLIC_IP}:2379/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://lab-static.pingcap.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.

### Step 6. Clean up the data of `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'...
   ```

### Step 7. Read the script `oltp_common_2.lua`

+ Please pay attention to the `CREATE TABLE` statement.
  
   ```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)
   ...
   ```

### Step 8. Run sysbench to simulate the scenario of concurrent data insertion

+ Again, 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 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'
   ```

+ After the completion of the sysbench test, observe the incoming flow within 1 hour in the traffic visualization graph in [TiDB PD Dashboard](http://${LAB:HOST_PD1_PUBLIC_IP}:2379/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://lab-static.pingcap.com/quick-demo/TableAndIdnexDesignSysBench2_aa_001_20231121.png)

### Step 9. Clean up the data of `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'...
   ```

## End of Exercises