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