## Module 3: Splitting a Table + In this exercise, we will create a clustered index for primary key table. After inserting data into the table, we will manually split the regions of the table to prevent hotspots in read and write operations. ### Step 1. Create a new clustered index for primary key table `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) ``` ### Step 2. Insert some data into the table `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) ``` ### Step 3. Split the table `orders` to prevent from reading and writing hotspot ```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) ``` ### Step 4. Clean up the `orders` table ```sql DROP TABLE orders; ``` ```sql exit ``` ```output Bye ```