## 模块 1：聚簇索引的最佳实践 在本模块中，你将为主键表创建聚簇索引，并配置相关设置以防止数据插入热点。 ### 任务 + 根据给定的 DDL 创建名为 `orders1` 的表。根据应用程序设计，该表将有高并发的插入操作。 + 识别 `orders1` 表可能出现的任何潜在性能问题。 + 重新创建 `orders1` 表，确保其主键包含聚簇索引。 + 向 `orders1` 表执行多次数据插入，并分析生成的数据分布。 ### 步骤 1：打开终端，SSH 登录到 TiUP 控制机 + 点击这里 - [终端 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` 表的定义，指出潜在的性能问题 + 表定义如下，点击 `Explain with 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 region 来预先对表进行分区。 ### 步骤 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 region。 ```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` 表插入五次数据 ```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 属性，但生成的主键值仍然是连续的。 ### Step 6. `TRUNCATE` `orders1` 表中的数据并插入新数据 + 请注意，`TRUNCATE` 操作后，`orders1` 表中只会剩下 1 个 region，因此需要手动拆分。 ```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) ```