## モジュール 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) ```