## モジュール 1: クラスタ化インデックスのベストプラクティス このモジュールでは、クラスタ化インデックスをプライマリーキーにするテーブルを作成し、データ挿入のホットスポットを防止するための関連設定を構成します。 ### タスク + 指定されたDDLで `orders1` という名前のテーブルを作成します。このテーブルは、アプリケーションの設計によって高い挿入同時性を持ちます。 + テーブル `orders1` に生じる可能性のあるパフォーマンスの問題を特定します。 + プライマリキーにクラスタ化インデックスを含むように `orders1` テーブルを再作成します。 + `orders1` テーブルに複数のデータ挿入を行い、結果のデータ分布を分析します。 ### 手順 1. リモート Linux にログインします + ここをクリック - [ターミナル 1](ssh://monitor1-ssh#1) で、ハンズオン演習用に割り当てられた仮想マシンに接続された新しいターミナルを開きます。 ### 手順 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) ```