## 模块 4：性能比较 + 在本模块中，我们评估分区表对性能的影响。 + 使用 LIST COLUMNS 分区对现有表进行分区。 + 对原始表和分区表执行查询以比较性能。 ### 步骤 1. 创建非分区表 `orders_no_partition` ```sql CREATE TABLE orders_no_partition( ID int(11) NOT NULL AUTO_INCREMENT, Name char(35) NOT NULL DEFAULT '', StoreCode char(3) NOT NULL DEFAULT '', City char(35) NOT NULL DEFAULT '', Mount int(11) NOT NULL DEFAULT '0', Type int(11) NOT NULL DEFAULT '0', PRIMARY KEY(ID), Key idx_StoreCode(StoreCode) ); ``` ```output Query OK, 0 rows affected (0.14 sec) ``` ```sql EXIT ``` ```bash for i in `seq 1000`; do mysql -uroot -P4000 -h${HOST_DB1_PRIVATE_IP} -e "INSERT INTO test.orders_no_partition VALUES(NULL,SUBSTRING(MD5(RAND()),1,20), SUBSTRING(MD5(RAND()),1,3), SUBSTRING(MD5(RAND()),1,20), FLOOR(RAND()*10000), 1);"; done; for i in `seq 1000`; do mysql -uroot -P4000 -h${HOST_DB1_PRIVATE_IP} -e "INSERT INTO test.orders_no_partition VALUES(NULL,SUBSTRING(MD5(RAND()),1,20), SUBSTRING(MD5(RAND()),1,3), SUBSTRING(MD5(RAND()),1,20), FLOOR(RAND()*10000), 2);"; done; for i in `seq 1000`; do mysql -uroot -P4000 -h${HOST_DB1_PRIVATE_IP} -e "INSERT INTO test.orders_no_partition VALUES(NULL,SUBSTRING(MD5(RAND()),1,20), SUBSTRING(MD5(RAND()),1,3), SUBSTRING(MD5(RAND()),1,20), FLOOR(RAND()*10000), 3);"; done; for i in `seq 1000`; do mysql -uroot -P4000 -h${HOST_DB1_PRIVATE_IP} -e "INSERT INTO test.orders_no_partition VALUES(NULL,SUBSTRING(MD5(RAND()),1,20), SUBSTRING(MD5(RAND()),1,3), SUBSTRING(MD5(RAND()),1,20), FLOOR(RAND()*10000), 4);"; done; for i in `seq 1000`; do mysql -uroot -P4000 -h${HOST_DB1_PRIVATE_IP} -e "INSERT INTO test.orders_no_partition VALUES(NULL,SUBSTRING(MD5(RAND()),1,20), SUBSTRING(MD5(RAND()),1,3), SUBSTRING(MD5(RAND()),1,20), FLOOR(RAND()*10000), 5);"; done; for i in `seq 9`; do mysql -uroot -P4000 -h${HOST_DB1_PRIVATE_IP} -e "INSERT INTO test.orders_no_partition SELECT NULL, Name, StoreCode, City,Mount, Type FROM test.orders_no_partition;"; done; ``` ```output mysql: [Warning] Using a password on the command line interface can be insecure. ... ... mysql: [Warning] Using a password on the command line interface can be insecure. ``` ### 步骤 2. 创建 LIST COLUMNS 分区表 `orders_partition` ```bash mysql -h ${HOST_DB1_PRIVATE_IP} --port 4000 -u root ``` ```sql use test; ``` ```sql CREATE TABLE orders_partition( ID int(11) NOT NULL AUTO_INCREMENT, Name char(35) NOT NULL DEFAULT '', StoreCode char(3) NOT NULL DEFAULT '', City char(35) NOT NULL DEFAULT '', Mount int(11) NOT NULL DEFAULT '0', Type int(11) NOT NULL DEFAULT '0', PRIMARY KEY(ID, Type), Key idx_StoreCode(StoreCode) ) PARTITION BY LIST COLUMNS (Type)( PARTITION p0 VALUES IN (1), PARTITION p1 VALUES IN (2), PARTITION p2 VALUES IN (3), PARTITION p3 VALUES IN (4), PARTITION p4 VALUES IN (5) ); ``` ```output Query OK, 0 rows affected (0.15 sec) ``` ```sql EXIT ``` ```bash for i in `seq 1000`; do mysql -uroot -P4000 -h${HOST_DB1_PRIVATE_IP} -e "INSERT INTO test.orders_partition VALUES(NULL,SUBSTRING(MD5(RAND()),1,20), SUBSTRING(MD5(RAND()),1,3), SUBSTRING(MD5(RAND()),1,20), FLOOR(RAND()*10000), 1);"; done; for i in `seq 1000`; do mysql -uroot -P4000 -h${HOST_DB1_PRIVATE_IP} -e "INSERT INTO test.orders_partition VALUES(NULL,SUBSTRING(MD5(RAND()),1,20), SUBSTRING(MD5(RAND()),1,3), SUBSTRING(MD5(RAND()),1,20), FLOOR(RAND()*10000), 2);"; done; for i in `seq 1000`; do mysql -uroot -P4000 -h${HOST_DB1_PRIVATE_IP} -e "INSERT INTO test.orders_partition VALUES(NULL,SUBSTRING(MD5(RAND()),1,20), SUBSTRING(MD5(RAND()),1,3), SUBSTRING(MD5(RAND()),1,20), FLOOR(RAND()*10000), 3);"; done; for i in `seq 1000`; do mysql -uroot -P4000 -h${HOST_DB1_PRIVATE_IP} -e "INSERT INTO test.orders_partition VALUES(NULL,SUBSTRING(MD5(RAND()),1,20), SUBSTRING(MD5(RAND()),1,3), SUBSTRING(MD5(RAND()),1,20), FLOOR(RAND()*10000), 4);"; done; for i in `seq 1000`; do mysql -uroot -P4000 -h${HOST_DB1_PRIVATE_IP} -e "INSERT INTO test.orders_partition VALUES(NULL,SUBSTRING(MD5(RAND()),1,20), SUBSTRING(MD5(RAND()),1,3), SUBSTRING(MD5(RAND()),1,20), FLOOR(RAND()*10000), 5);"; done; for i in `seq 9`; do mysql -uroot -P4000 -h${HOST_DB1_PRIVATE_IP} -e "INSERT INTO test.orders_partition SELECT NULL, Name, StoreCode, City,Mount, Type from test.orders_partition;"; done; ``` ```output for i in `seq 1000`; do mysql -uroot -P4000 -h${HOST_DB1_PRIVATE_IP} -e "INSERT INTO test.orders_partition VALUES(NULL,SUBSTRING(MD5(RAND()),1,20), SUBSTRING(MD5(RAND()),1,3), SUBSTRING(MD5(RAND()),1,20), FLOOR(RAND()*10000), 1);"; done; mysql: [Warning] Using a password on the command line interface can be insecure. ... ... mysql: [Warning] Using a password on the command line interface can be insecure. ``` ### 步骤 3. 向 `orders_no_partition` 和 `orders_partition` 插入数据 ```bash mysql -h ${HOST_DB1_PRIVATE_IP} --port 4000 -u root ``` ```sql use test; ``` ```output Database changed ``` ```sql INSERT INTO test.orders_no_partition VALUES(NULL,SUBSTRING(MD5(RAND()),1,20), SUBSTRING(MD5(RAND()),1,3), SUBSTRING(MD5(RAND()),1,20), FLOOR(RAND()*10000), 6); INSERT INTO test.orders_no_partition VALUES(NULL,SUBSTRING(MD5(RAND()),1,20), SUBSTRING(MD5(RAND()),1,3), SUBSTRING(MD5(RAND()),1,20), FLOOR(RAND()*10000), 6); INSERT INTO test.orders_partition VALUES(NULL,SUBSTRING(MD5(RAND()),1,20), SUBSTRING(MD5(RAND()),1,3), SUBSTRING(MD5(RAND()),1,20), FLOOR(RAND()*10000), 6); ``` ```output tidb>INSERT INTO test.orders_partition VALUES(NULL,SUBSTRING(MD5(RAND()),1,20), SUBSTRING(MD5(RAND()),1,3), SUBSTRING(MD5(RAND()),1,20), FLOOR(RAND()*10000), 6); ERROR 1526 (HY000): Table has no partition for value from column_list ``` + 出现错误 `ERROR 1526 (HY000): Table has no partition for value from column_list` 的原因是，表 `orders_partition` 没有针对 `Type 6` 的分区，因此需要添加一个分区。 ```sql ALTER TABLE test.orders_partition ADD PARTITION (PARTITION p5 VALUES IN (6)); INSERT INTO test.orders_partition VALUES(NULL,SUBSTRING(MD5(RAND()),1,20), SUBSTRING(MD5(RAND()),1,3), SUBSTRING(MD5(RAND()),1,20), FLOOR(RAND()*10000), 6); INSERT INTO test.orders_partition VALUES(NULL,SUBSTRING(MD5(RAND()),1,20), SUBSTRING(MD5(RAND()),1,3), SUBSTRING(MD5(RAND()),1,20), FLOOR(RAND()*10000), 6); ``` ```output Query OK, 1 row affected (0.00 sec) ``` ### 步骤 4. 检查 `orders_no_partition` 表的状态 + 输出中 `Create_options` 列包含什么值？ ```sql SHOW TABLE STATUS LIKE 'orders_no_partition'\G ``` ```output tidb>SHOW TABLE STATUS LIKE 'orders_no_partition'\G *************************** 1. row *************************** Name: orders_no_partition Engine: InnoDB Version: 10 Row_format: Compact Rows: 2560000 Avg_row_length: 70 Data_length: 179200000 Max_data_length: 0 Index_length: 10240000 Data_free: 0 Auto_increment: 2560002 Create_time: 2023-11-16 15:10:53 Update_time: NULL Check_time: NULL Collation: utf8mb4_bin Checksum: Create_options: Comment: 1 row in set (0.01 sec) ``` + 答案：`orders_no_partition` 表的 `SHOW TABLE STATUS` 输出中，`Create_options` 列是空的。 ### 步骤 5. 检查 `orders_partition` 表的状态 + 输出中 `Create_options` 列包含什么值？ ```sql SHOW TABLE STATUS LIKE 'orders_partition'\G ``` ```output tidb>SHOW TABLE STATUS LIKE 'orders_partition'\G *************************** 1. row *************************** Name: orders_partition Engine: InnoDB Version: 10 Row_format: Compact Rows: 2560002 Avg_row_length: 70 Data_length: 179200140 Max_data_length: 0 Index_length: 51200040 Data_free: 0 Auto_increment: 2560004 Create_time: 2023-11-16 15:48:21 Update_time: NULL Check_time: NULL Collation: utf8mb4_bin Checksum: Create_options: partitioned Comment: 1 row in set (0.01 sec) ``` + 答案：`orders_partition` 表的 `SHOW TABLE STATUS` 输出中，`Create_options` 列显示为 partitioned。 ### 步骤 6. 比较两个表的 `CREATE TABLE` 语句 + `orders_partition` 表是如何分区的？ ```sql SHOW CREATE TABLE orders_no_partition\G SHOW CREATE TABLE orders_partition\G ``` ```output *************************** 1. row *************************** Table: orders_no_partition Create Table: CREATE TABLE `orders_no_partition` ( `ID` int(11) NOT NULL AUTO_INCREMENT, `Name` char(35) NOT NULL DEFAULT '', `StoreCode` char(3) NOT NULL DEFAULT '', `City` char(35) NOT NULL DEFAULT '', `Mount` int(11) NOT NULL DEFAULT '0', `Type` int(11) NOT NULL DEFAULT '0', PRIMARY KEY (`ID`) /*T![clustered_index] CLUSTERED */, KEY `idx_StoreCode` (`StoreCode`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin AUTO_INCREMENT=3182134 1 row in set (0.01 sec) *************************** 1. row *************************** Table: orders_partition Create Table: CREATE TABLE `orders_partition` ( `ID` int(11) NOT NULL AUTO_INCREMENT, `Name` char(35) NOT NULL DEFAULT '', `StoreCode` char(3) NOT NULL DEFAULT '', `City` char(35) NOT NULL DEFAULT '', `Mount` int(11) NOT NULL DEFAULT '0', `Type` int(11) NOT NULL DEFAULT '0', PRIMARY KEY (`ID`,`Type`) /*T![clustered_index] CLUSTERED */, KEY `idx_StoreCode` (`StoreCode`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin AUTO_INCREMENT=2677458 PARTITION BY LIST COLUMNS(`Type`) (PARTITION `p0` VALUES IN (1), PARTITION `p1` VALUES IN (2), PARTITION `p2` VALUES IN (3), PARTITION `p3` VALUES IN (4), PARTITION `p4` VALUES IN (5), PARTITION `p5` VALUES IN (6)) 1 row in set (0.00 sec) ``` + 答案：`orders_partition` 表使用 `Type` 列的值进行 LIST COLUMNS 分区。为了支持对 `Type` 列进行分区，`Type` 列必须是主键的一部分。 ### 步骤 7. 检查 `orders_partition` 每个分区的行数 + 查询 `INFORMATION_SCHEMA` 数据库中的 `PARTITIONS` 表，以获取 `orders_partition` 表中每个分区的行数和数据长度。 ```sql ANALYZE TABLE orders_partition; SELECT PARTITION_NAME, TABLE_ROWS, DATA_LENGTH FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_NAME='orders_partition'; ``` ```output +----------------+------------+-------------+ | PARTITION_NAME | TABLE_ROWS | DATA_LENGTH | +----------------+------------+-------------+ | p0 | 512000 | 35840000 | | p1 | 512000 | 35840000 | | p2 | 512000 | 35840000 | | p3 | 512000 | 35840000 | | p4 | 512000 | 35840000 | | p5 | 2 | 140 | +----------------+------------+-------------+ 6 rows in set (0.01 sec) ``` ### 步骤 8. 比较性能 ```sql SELECT * FROM test.orders_no_partition WHERE Type = 6; SELECT * FROM test.orders_partition WHERE Type = 6; ``` ```output +---------+----------------------+-----------+----------------------+-------+------+ | ID | Name | StoreCode | City | Mount | Type | +---------+----------------------+-----------+----------------------+-------+------+ | 2560001 | 71add0d7809cbcbcbe70 | 5a7 | ed366dcc516557dc9dd1 | 9718 | 6 | | 2560002 | 729981de40b9d6518610 | ce2 | ec29e10a4e200a85f39d | 2252 | 6 | +---------+----------------------+-----------+----------------------+-------+------+ 2 rows in set (0.74 sec) +---------+----------------------+-----------+----------------------+-------+------+ | ID | Name | StoreCode | City | Mount | Type | +---------+----------------------+-----------+----------------------+-------+------+ | 2560002 | 74daf6785e90d21e49fa | d0d | 7264824fb92dff278fc8 | 6453 | 6 | | 2560003 | 34558dcfaf8c36730107 | d79 | 712aa60533e92f925232 | 14 | 6 | +---------+----------------------+-----------+----------------------+-------+------+ 2 rows in set (0.01 sec) ``` + 注意：如果再次执行查询，你会发现它们在性能上没有差异，因为我们刚刚选择的数据被缓存了。 ```output tidb>SELECT * FROM test.orders_no_partition WHERE Type = 6; +---------+----------------------+-----------+----------------------+-------+------+ | ID | Name | StoreCode | City | Mount | Type | +---------+----------------------+-----------+----------------------+-------+------+ | 2560001 | 71add0d7809cbcbcbe70 | 5a7 | ed366dcc516557dc9dd1 | 9718 | 6 | | 2560002 | 729981de40b9d6518610 | ce2 | ec29e10a4e200a85f39d | 2252 | 6 | +---------+----------------------+-----------+----------------------+-------+------+ 1 row in set (0.00 sec) tidb>SELECT * FROM test.orders_partition WHERE Type = 6; +---------+----------------------+-----------+----------------------+-------+------+ | ID | Name | StoreCode | City | Mount | Type | +---------+----------------------+-----------+----------------------+-------+------+ | 2560002 | 74daf6785e90d21e49fa | d0d | 7264824fb92dff278fc8 | 6453 | 6 | | 2560003 | 34558dcfaf8c36730107 | d79 | 712aa60533e92f925232 | 14 | 6 | +---------+----------------------+-----------+----------------------+-------+------+ 2 rows in set (0.00 sec) ``` + 根据您的结果，分区对查询性能有什么影响？ + 答案：对于使用单个分区数据的查询，性能比从非分区表访问相同数据要好。但是，对于必须访问所有分区才能检索数据的查询，性能通常比从非分区表访问相同数据要差。 ### 步骤 9. 删除 `orders_no_partition` 和 `orders_partition` 表 ```sql DROP TABLE test.orders_no_partition; DROP TABLE test.orders_partition; ``` ```output Query OK, 0 rows affected (0.34 sec) ```