## 模块 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` 表的 region 分布 + 由于 `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. `TRUNCATE` `orders2` 表中的数据 + 请注意，`TRUNCATE` 操作后，`orders2` 表仍有 8 个默认 region。这是因为 `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) ```