# 演習 1: クラスタ化インデックスと非クラスタ化インデックス


## 概要

この演習では、プライマリキーのテーブルに対してクラスタ化インデックスと非クラスタ化インデックスの両方を作成し、ホットスポットを回避するための関連設定を行います。クラスタ化インデックスをプライマリーキーにするテーブルを作成し、テーブルリージョンを分割してホットスポットの問題を回避します。監視を通じてホットスポットを検出する方法を練習し、テーブル構造を変更することでホットスポットの問題を回避します。

### 所要時間
+ この演習は約 60 分程度かかります。

> [!TIP]
> 演習環境がデプロイされたら、下のバーの**次のモジュール**をクリックして**モジュール 1**を開始します。

## モジュール 1: クラスタ化インデックスのベストプラクティス


このモジュールでは、クラスタ化インデックスをプライマリーキーにするテーブルを作成し、データ挿入のホットスポットを防止するための関連設定を構成します。


### タスク

+ 指定されたDDLで `orders1` という名前のテーブルを作成します。このテーブルは、アプリケーションの設計によって高い挿入同時性を持ちます。
+ テーブル `orders1` に生じる可能性のあるパフォーマンスの問題を特定します。
+ プライマリキーにクラスタ化インデックスを含むように `orders1` テーブルを再作成します。
+ `orders1` テーブルに複数のデータ挿入を行い、結果のデータ分布を分析します。


### 手順 1. リモート Linux にログインします

+ ここをクリック - [ターミナル 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` の定義を観察し、潜在的なパフォーマンス問題を指摘する


+ テーブル定義は以下の通りです。`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リージョンを分割してテーブルを事前にパーティション化することが推奨されます。


### 手順 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リージョンに分割します。


  ```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` テーブルにデータを5回挿入する


  ```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 属性が指定されているにもかかわらず、生成されたプライマリキーの値は連続しています。


### 手順 6. `orders1` テーブルのデータを `TRUNCATE` し、新しいデータを挿入する


+ 注意点として、`TRUNCATE` 後、`orders1` テーブルには1つのリージョンしか残りません。そのため、手動で分割する必要があります。
   ```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` テーブルのリージョンの分布を確認する


+ `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. `orders2` におけるデータを `TRUNCATE` する


+ 注意点として、`TRUNCATE` 後も `orders2` には8つのデフォルトリージョンが残ります。これは、`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), \
  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)
  ```


### 手順 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'...
  ```


## 演習終了