# 实验 2：分区表 ## 概述 + 在这些练习中，你将创建和管理 Range 分区，Range COLUMNS 分区，List 分区和 List COLUMNS 分区表。你将了解 Hash 分区和 Key 分区表之间的区别。最后，你将比较分区表和非分区表的性能。 ### 实验时长 + 本实验大约需 60 分钟完成。 > [!TIP] > 实验室环境部署完成后，点击底部栏上的 **前进** 进入 **模块 1**。 ## 模块 1：RANGE、RANGE COLUMN 和 RANGE INTERVAL 分区表 + 在本练习中，你将创建和管理 Range 分区、Range COLUMNS 分区和 Range INTERVAL 分区表 ### 步骤 1：打开终端，SSH 登录到 TiUP 控制机 + 点击这里 - [终端 1](ssh://monitor1-ssh#1) 打开一个新的终端，连接到为你的实践实验分配的虚拟机。 + 登录后显示的横幅如下所示。 ```output , #_ ~\_ ####_ Amazon Linux 2 ~~ \_#####\ ~~ \###| ~~ \#/ ___ ~~ V~' '-> ~~~ / A newer version of Amazon Linux is available! ~~._. _/ _/ _/ Amazon Linux 2023, GA and supported until 2028-03-15. _/m/' https://aws.amazon.com/linux/amazon-linux-2023/ ``` ### 步骤 2. 连接到 TiDB 并创建非分区表 `emp` ```bash mysql -h ${HOST_DB1_PRIVATE_IP} --port 4000 -u root ``` ```sql use test; ``` ```output Database changed ``` ```sql CREATE TABLE emp ( id INT PRIMARY KEY, fname VARCHAR(30), lname VARCHAR(30), hired DATE NOT NULL DEFAULT '2018-01-01', dept_code INT NOT NULL, region_id INT NOT NULL ); INSERT INTO emp VALUES(1, 'Chars', 'Smith', '2018-05-08', 1, 801); INSERT INTO emp VALUES(2, 'Mott', 'Johnson', '2018-06-01', 2, 401); INSERT INTO emp VALUES(3, 'Lundeen', 'Williams', '2018-06-22', 3, 201); INSERT INTO emp VALUES(4, 'Broe', 'Brown', '2018-09-22', 4, 101); INSERT INTO emp VALUES(5, 'Layla', 'Jones', '2018-10-12', 5, 301); INSERT INTO emp VALUES(6, 'Tom', 'Garcia', '2019-01-30', 2, 501); INSERT INTO emp VALUES(7, 'Donald', 'Miller', '2019-02-21', 2, 401); INSERT INTO emp VALUES(8, 'Kevin', 'Davis', '2019-03-09', 3, 301); INSERT INTO emp VALUES(9, 'Ken', 'Rodriguez', '2020-11-17', 1, 201); INSERT INTO emp VALUES(10, 'Bill', 'Martinez', '2020-11-19', 2, 201); INSERT INTO emp VALUES(11, 'Jack', 'Hernandez', '2020-12-01', 4, 201); INSERT INTO emp VALUES(12, 'Fran', 'Jones', '2020-12-01', 4, 201); INSERT INTO emp VALUES(13, 'Mark', 'Wilson', '2021-07-08', 4, 101); INSERT INTO emp VALUES(14, 'Bob', 'Thomas', '2021-07-08', 4, 101); INSERT INTO emp VALUES(15, 'Jeff', 'Taylor', '2022-04-03', 5, 101); INSERT INTO emp VALUES(16, 'Jim', 'Lee', '2022-04-04', 2, 201); INSERT INTO emp VALUES(17, 'Tim', 'Anderson', '2022-05-10', 2, 301); INSERT INTO emp VALUES(18, 'Mike', 'Jackson', '2022-05-10', 3, 401); INSERT INTO emp VALUES(19, 'Benjamin', 'Thompson', '2022-09-24', 1, 501); INSERT INTO emp VALUES(20, 'Charlotte', 'Clark', '2022-09-24', 1, 801); INSERT INTO emp VALUES(21, 'Mason', 'Jones', '2022-10-27', 1, 801); INSERT INTO emp VALUES(22, 'Emma', 'Robinson', '2023-06-15', 2, 301); INSERT INTO emp VALUES(23, 'Ava', 'Adams', '2023-07-03', 2, 301); INSERT INTO emp VALUES(24, 'Olivia', 'Lewis', '2023-08-01', 5, 701); INSERT INTO emp VALUES(25, 'Ava', 'Phillips', '2023-09-02', 3, 701); INSERT INTO emp VALUES(26, 'Isabella', 'Morgan', '2023-09-03', 2, 101); INSERT INTO emp VALUES(27, 'Donald', 'Edwards', '2023-09-03', 4, 101); INSERT INTO emp VALUES(28, 'Somes', 'Alexander', '2023-12-10', 1, 101); INSERT INTO emp VALUES(29, 'Crabb', 'Ford', '2024-06-30', 2, 401); INSERT INTO emp VALUES(30, 'Sisika', 'Williams', '2024-06-30', 2, 401); INSERT INTO emp VALUES(31, 'Noah', 'Anderson', '2024-07-02', 4, 101); INSERT INTO emp VALUES(32, 'Jacob', 'Martinez', '2025-01-11', 4, 501); INSERT INTO emp VALUES(33, 'Kim', 'Miller', '2025-01-30', 1, 401); INSERT INTO emp VALUES(34, 'Jim', 'Smith', '2025-04-24', 2, 501); INSERT INTO emp VALUES(35, 'Sam', 'Martinez', '2025-05-22', 3, 101); INSERT INTO emp VALUES(36, 'Candy', 'Morgan', '2025-08-03', 1, 301); SELECT COUNT(*) from emp; ``` ```output +----------+ | COUNT(*) | +----------+ | 36 | +----------+ 1 row in set (0.00 sec) ``` ### 步骤 3. 创建无主键的 Range 分区表 `employees1` ```sql CREATE TABLE employees1 ( id INT NOT NULL, fname VARCHAR(30), lname VARCHAR(30), hired DATE NOT NULL DEFAULT '2018-01-01', dept_code INT NOT NULL, region_id INT NOT NULL ) PARTITION BY RANGE (hired) ( PARTITION p0 VALUES LESS THAN ('2019-01-01'), PARTITION p1 VALUES LESS THAN ('2020-01-01'), PARTITION p2 VALUES LESS THAN ('2021-01-01'), PARTITION p3 VALUES LESS THAN ('2022-01-01'), PARTITION p4 VALUES LESS THAN ('2023-01-01'), PARTITION p5 VALUES LESS THAN ('2024-01-01'), PARTITION p6 VALUES LESS THAN ('2025-01-01'), PARTITION p7 VALUES LESS THAN ('2026-01-01'), PARTITION p8 VALUES LESS THAN MAXVALUE ); ``` ```output ERROR 1697 (HY000): VALUES value for partition 'p0' must have type INT ``` + 出现错误 `ERROR 1697 (HY000): VALUES value for partition 'p0' must have type INT` 的原因是，`PARTITION BY RANGE` 后的键必须为 `INT` 类型。我们需要相应地修改 SQL 语句。 ```sql CREATE TABLE employees1 ( id INT NOT NULL, fname VARCHAR(30), lname VARCHAR(30), hired DATE NOT NULL DEFAULT '2018-01-01', dept_code INT NOT NULL, region_id INT NOT NULL ) PARTITION BY RANGE (YEAR(hired)) ( PARTITION p0 VALUES LESS THAN (2019), PARTITION p1 VALUES LESS THAN (2020), PARTITION p2 VALUES LESS THAN (2021), PARTITION p3 VALUES LESS THAN (2022), PARTITION p4 VALUES LESS THAN (2023), PARTITION p5 VALUES LESS THAN (2024), PARTITION p6 VALUES LESS THAN (2025), PARTITION p7 VALUES LESS THAN (2026), PARTITION p8 VALUES LESS THAN MAXVALUE ); ``` ```output Query OK, 0 rows affected (0.13 sec) ``` ### 步骤 4. 创建无主键的 Range COLUMNS 分区表 `employees2` ```sql CREATE TABLE employees2 ( id INT NOT NULL, fname VARCHAR(30), lname VARCHAR(30), hired DATE NOT NULL DEFAULT '2018-01-01', dept_code INT NOT NULL, region_id INT NOT NULL ) PARTITION BY RANGE COLUMNS(hired) ( PARTITION p0 VALUES LESS THAN ('2019-01-01'), PARTITION p1 VALUES LESS THAN ('2020-01-01'), PARTITION p2 VALUES LESS THAN ('2021-01-01'), PARTITION p3 VALUES LESS THAN ('2022-01-01'), PARTITION p4 VALUES LESS THAN ('2023-01-01'), PARTITION p5 VALUES LESS THAN ('2024-01-01'), PARTITION p6 VALUES LESS THAN ('2025-01-01'), PARTITION p7 VALUES LESS THAN ('2026-01-01'), PARTITION p8 VALUES LESS THAN MAXVALUE ); ``` ```output Query OK, 0 rows affected (0.13 sec) ``` + 分区列的数据类型可以是整数、字符串 (CHAR 或 VARCHAR)、DATE 和 DATETIME。不支持 `YEAR()`等表达式。你可以使用一列或多列作为分区键。 ### 步骤 5. 创建有主键的 Range 分区表 `employees3` ```sql CREATE TABLE employees3 ( id INT PRIMARY KEY, fname VARCHAR(30), lname VARCHAR(30), hired DATE NOT NULL DEFAULT '2018-01-01', dept_code INT NOT NULL, region_id INT NOT NULL ) PARTITION BY RANGE (YEAR(hired)) ( PARTITION p0 VALUES LESS THAN (2019), PARTITION p1 VALUES LESS THAN (2020), PARTITION p2 VALUES LESS THAN (2021), PARTITION p3 VALUES LESS THAN (2022), PARTITION p4 VALUES LESS THAN (2023), PARTITION p5 VALUES LESS THAN (2024), PARTITION p6 VALUES LESS THAN (2025), PARTITION p7 VALUES LESS THAN (2026), PARTITION p8 VALUES LESS THAN MAXVALUE ); ``` ```output ERROR 1503 (HY000): A CLUSTERED INDEX must include all columns in the table's partitioning function ``` + 出现错误 `ERROR 1503 (HY000): A CLUSTERED INDEX must include all columns in the table's partitioning function` 的原因是，对于含有唯一键的表，分区表达式中的所有列都必须包含在唯一键中。主键也是一种唯一键。 ```sql CREATE TABLE employees3 ( id INT NOT NULL, fname VARCHAR(30), lname VARCHAR(30), hired DATE NOT NULL DEFAULT '2018-01-01', dept_code INT NOT NULL, region_id INT NOT NULL, PRIMARY KEY idx_id_hired(id, hired) ) PARTITION BY RANGE (YEAR(hired)) ( PARTITION p0 VALUES LESS THAN (2019), PARTITION p1 VALUES LESS THAN (2020), PARTITION p2 VALUES LESS THAN (2021), PARTITION p3 VALUES LESS THAN (2022), PARTITION p4 VALUES LESS THAN (2023), PARTITION p5 VALUES LESS THAN (2024), PARTITION p6 VALUES LESS THAN (2025), PARTITION p7 VALUES LESS THAN (2026), PARTITION p8 VALUES LESS THAN MAXVALUE ); ``` ```output Query OK, 0 rows affected (0.13 sec) ``` ### 步骤 6. 创建有主键的 COLUMNS Range 分区表 `employees4` ```sql CREATE TABLE employees4 ( id INT PRIMARY KEY, fname VARCHAR(30), lname VARCHAR(30), hired DATE NOT NULL DEFAULT '2018-01-01', dept_code INT NOT NULL, region_id INT NOT NULL ) PARTITION BY RANGE COLUMNS(hired) ( PARTITION p0 VALUES LESS THAN ('2019-01-01'), PARTITION p1 VALUES LESS THAN ('2020-01-01'), PARTITION p2 VALUES LESS THAN ('2021-01-01'), PARTITION p3 VALUES LESS THAN ('2022-01-01'), PARTITION p4 VALUES LESS THAN ('2023-01-01'), PARTITION p5 VALUES LESS THAN ('2024-01-01'), PARTITION p6 VALUES LESS THAN ('2025-01-01'), PARTITION p7 VALUES LESS THAN ('2026-01-01'), PARTITION p8 VALUES LESS THAN MAXVALUE ); ``` ```output ERROR 1503 (HY000): A CLUSTERED INDEX must include all columns in the table's partitioning function ``` + 出现错误 `ERROR 1503 (HY000): A CLUSTERED INDEX must include all columns in the table's partitioning function` 的原因是，对于有唯一键的表，分区表达式中的所有列都必须包含在唯一键中。主键也是唯一键。 ```sql CREATE TABLE employees4 ( id INT, fname VARCHAR(30), lname VARCHAR(30), hired DATE NOT NULL DEFAULT '2018-01-01', dept_code INT NOT NULL, region_id INT NOT NULL, PRIMARY KEY idx_id_hired(id, hired) ) PARTITION BY RANGE COLUMNS(hired) ( PARTITION p0 VALUES LESS THAN ('2019-01-01'), PARTITION p1 VALUES LESS THAN ('2020-01-01'), PARTITION p2 VALUES LESS THAN ('2021-01-01'), PARTITION p3 VALUES LESS THAN ('2022-01-01'), PARTITION p4 VALUES LESS THAN ('2023-01-01'), PARTITION p5 VALUES LESS THAN ('2024-01-01'), PARTITION p6 VALUES LESS THAN ('2025-01-01'), PARTITION p7 VALUES LESS THAN ('2026-01-01'), PARTITION p8 VALUES LESS THAN MAXVALUE ); ``` ```output Query OK, 0 rows affected (0.14 sec) ``` ### 步骤 7. 向分区表导入数据 + 将非分区表 `emp` 中的数据导入分区表 `employees1`、`employees2`、`employees3` 和 `employees4`，并检查分区信息。然后，使用 `reorganize partition` 命令将 `employees1`、`employees2`、`employees3` 和 `employees4` 中的两个分区合并为一个分区。 + 注意，`ANALYZE TABLE` 命令可能需要几秒钟才能运行完成。 ```sql INSERT INTO employees1 SELECT * FROM emp; INSERT INTO employees2 SELECT * FROM emp; INSERT INTO employees3 SELECT * FROM emp; INSERT INTO employees4 SELECT * FROM emp; ANALYZE TABLE employees1; ANALYZE TABLE employees2; ANALYZE TABLE employees3; ANALYZE TABLE employees4; SELECT table_name, partition_name, table_rows FROM information_schema.partitions WHERE table_schema = 'test' AND table_name IN ('employees1', 'employees2', 'employees3', 'employees4') ORDER BY table_name, partition_name; ``` ```output +------------+----------------+------------+ | table_name | partition_name | table_rows | +------------+----------------+------------+ | employees1 | p0 | 5 | | employees1 | p1 | 3 | | employees1 | p2 | 4 | | employees1 | p3 | 2 | | employees1 | p4 | 7 | | employees1 | p5 | 7 | | employees1 | p6 | 3 | | employees1 | p7 | 5 | | employees1 | p8 | 0 | | employees2 | p0 | 5 | | employees2 | p1 | 3 | | employees2 | p2 | 4 | | employees2 | p3 | 2 | | employees2 | p4 | 7 | | employees2 | p5 | 7 | | employees2 | p6 | 3 | | employees2 | p7 | 5 | | employees2 | p8 | 0 | | employees3 | p0 | 5 | | employees3 | p1 | 3 | | employees3 | p2 | 4 | | employees3 | p3 | 2 | | employees3 | p4 | 7 | | employees3 | p5 | 7 | | employees3 | p6 | 3 | | employees3 | p7 | 5 | | employees3 | p8 | 0 | | employees4 | p0 | 5 | | employees4 | p1 | 3 | | employees4 | p2 | 4 | | employees4 | p3 | 2 | | employees4 | p4 | 7 | | employees4 | p5 | 7 | | employees4 | p6 | 3 | | employees4 | p7 | 5 | | employees4 | p8 | 0 | +------------+----------------+------------+ 36 rows in set (0.01 sec) ``` + 运行 `ANALYZE TABLE` 命令后，你可能会看到警告消息代码 `1105` 。警告消息指出，由于没有全局统计信息，`dynamic pruning mode` 已禁用。你可以查看 [此文档](https://docs.pingcap.com/zh/tidb/v7.5/partitioned-table#%E5%8A%A8%E6%80%81%E8%A3%81%E5%89%AA%E6%A8%A1%E5%BC%8F) 了解详细信息。 ```sql ALTER TABLE employees1 REORGANIZE PARTITION p6,p7 into (PARTITION pErr VALUES LESS THAN (2026)); ALTER TABLE employees2 REORGANIZE PARTITION p6,p7 into (PARTITION pErr VALUES LESS THAN ('2026-01-01')); ALTER TABLE employees3 REORGANIZE PARTITION p6,p7 into (PARTITION pErr VALUES LESS THAN (2026)); ALTER TABLE employees4 REORGANIZE PARTITION p5,p7 into (PARTITION pErr VALUES LESS THAN ('2026-01-01')); ALTER TABLE employees4 REORGANIZE PARTITION p6,p7 into (PARTITION pErr VALUES LESS THAN ('2026-01-01')); ANALYZE TABLE employees1; ANALYZE TABLE employees2; ANALYZE TABLE employees3; ANALYZE TABLE employees4; SELECT table_name, partition_name, table_rows FROM information_schema.partitions WHERE table_schema = 'test' AND table_name IN ('employees1', 'employees2', 'employees3', 'employees4') ORDER BY table_name, partition_name; ``` ```output tidb>ALTER TABLE employees4 REORGANIZE PARTITION p5,p7 into (PARTITION pErr VALUES LESS THAN ('2026-01-01')); ERROR 8200 (HY000): Unsupported REORGANIZE PARTITION of RANGE; not adjacent partitions +------------+----------------+------------+ | table_name | partition_name | table_rows | +------------+----------------+------------+ | employees1 | p0 | 5 | | employees1 | p1 | 3 | | employees1 | p2 | 4 | | employees1 | p3 | 2 | | employees1 | p4 | 7 | | employees1 | p5 | 7 | | employees1 | p8 | 0 | | employees1 | pErr | 8 | | employees2 | p0 | 5 | | employees2 | p1 | 3 | | employees2 | p2 | 4 | | employees2 | p3 | 2 | | employees2 | p4 | 7 | | employees2 | p5 | 7 | | employees2 | p8 | 0 | | employees2 | pErr | 8 | | employees3 | p0 | 5 | | employees3 | p1 | 3 | | employees3 | p2 | 4 | | employees3 | p3 | 2 | | employees3 | p4 | 7 | | employees3 | p5 | 7 | | employees3 | p8 | 0 | | employees3 | pErr | 8 | | employees4 | p0 | 5 | | employees4 | p1 | 3 | | employees4 | p2 | 4 | | employees4 | p3 | 2 | | employees4 | p4 | 7 | | employees4 | p5 | 7 | | employees4 | p8 | 0 | | employees4 | pErr | 8 | +------------+----------------+------------+ 32 rows in set (0.01 sec) ``` + 创建 `employees4` 表时出现错误 `ERROR 8200 (HY000): Unsupported REORGANIZE PARTITION of RANGE; not adjacent partitions` 的原因是，对于 Range 分区表，你只能合并相邻的分区。 ### 步骤 8. 删除分区 + 使用 `DROP PARTITION` 命令从 `employees1`、`employees2`、`employees3` 和 `employees4` 中删除不必要的分区 `pErr`。 ```sql ALTER TABLE employees1 DROP PARTITION pErr; ALTER TABLE employees2 DROP PARTITION pErr; ALTER TABLE employees3 DROP PARTITION pErr; ALTER TABLE employees4 DROP PARTITION pErr; ANALYZE TABLE employees1; ANALYZE TABLE employees2; ANALYZE TABLE employees3; ANALYZE TABLE employees4; SELECT table_name, partition_name, table_rows FROM information_schema.partitions WHERE table_schema = 'test' AND table_name IN ('employees1', 'employees2', 'employees3', 'employees4') ORDER BY table_name, partition_name; ``` ```output +------------+----------------+------------+ | table_name | partition_name | table_rows | +------------+----------------+------------+ | employees1 | p0 | 5 | | employees1 | p1 | 3 | | employees1 | p2 | 4 | | employees1 | p3 | 2 | | employees1 | p4 | 7 | | employees1 | p5 | 7 | | employees1 | p8 | 0 | | employees2 | p0 | 5 | | employees2 | p1 | 3 | | employees2 | p2 | 4 | | employees2 | p3 | 2 | | employees2 | p4 | 7 | | employees2 | p5 | 7 | | employees2 | p8 | 0 | | employees3 | p0 | 5 | | employees3 | p1 | 3 | | employees3 | p2 | 4 | | employees3 | p3 | 2 | | employees3 | p4 | 7 | | employees3 | p5 | 7 | | employees3 | p8 | 0 | | employees4 | p0 | 5 | | employees4 | p1 | 3 | | employees4 | p2 | 4 | | employees4 | p3 | 2 | | employees4 | p4 | 7 | | employees4 | p5 | 7 | | employees4 | p8 | 0 | +------------+----------------+------------+ 28 rows in set (0.01 sec) ``` ### 步骤 9. 创建 Range INTERVAL 和 Range COLUMNS 分区表 ```sql CREATE TABLE employees5 ( id INT NOT NULL, fname VARCHAR(30), lname VARCHAR(30), hired DATE NOT NULL DEFAULT '2018-01-01', dept_code INT NOT NULL, region_id INT NOT NULL ) PARTITION BY RANGE (YEAR(hired)) INTERVAL (1) FIRST PARTITION LESS THAN (2019) LAST PARTITION LESS THAN (2026) MAXVALUE PARTITION; CREATE TABLE employees6 ( id INT NOT NULL, fname VARCHAR(30), lname VARCHAR(30), hired DATE NOT NULL DEFAULT '2018-01-01', dept_code INT NOT NULL, region_id INT NOT NULL ) PARTITION BY RANGE COLUMNS (hired) INTERVAL (1 MONTH) FIRST PARTITION LESS THAN ('2019-01-01') LAST PARTITION LESS THAN ('2026-01-01') MAXVALUE PARTITION; INSERT INTO employees5 SELECT * FROM emp; INSERT INTO employees6 SELECT * FROM emp; ANALYZE TABLE employees5; ANALYZE TABLE employees6; SELECT table_name, partition_name, table_rows FROM information_schema.partitions WHERE table_schema = 'test' AND table_name IN ('employees5', 'employees6') ORDER BY table_name, partition_name; ``` ```output +------------+-----------------+------------+ | table_name | partition_name | table_rows | +------------+-----------------+------------+ | employees5 | P_LT_2019 | 5 | | employees5 | P_LT_2020 | 3 | | employees5 | P_LT_2021 | 4 | | employees5 | P_LT_2022 | 2 | | employees5 | P_LT_2023 | 7 | | employees5 | P_LT_2024 | 7 | | employees5 | P_LT_2025 | 3 | | employees5 | P_LT_2026 | 5 | | employees5 | P_MAXVALUE | 0 | | employees6 | P_LT_2019-01-01 | 5 | | employees6 | P_LT_2019-02-01 | 1 | | employees6 | P_LT_2019-03-01 | 1 | | employees6 | P_LT_2019-04-01 | 1 | | employees6 | P_LT_2019-05-01 | 0 | | employees6 | P_LT_2019-06-01 | 0 | | employees6 | P_LT_2019-07-01 | 0 | | employees6 | P_LT_2019-08-01 | 0 | | employees6 | P_LT_2019-09-01 | 0 | | employees6 | P_LT_2019-10-01 | 0 | | employees6 | P_LT_2019-11-01 | 0 | | employees6 | P_LT_2019-12-01 | 0 | | employees6 | P_LT_2020-01-01 | 0 | | employees6 | P_LT_2020-02-01 | 0 | | employees6 | P_LT_2020-03-01 | 0 | | employees6 | P_LT_2020-04-01 | 0 | | employees6 | P_LT_2020-05-01 | 0 | | employees6 | P_LT_2020-06-01 | 0 | | employees6 | P_LT_2020-07-01 | 0 | | employees6 | P_LT_2020-08-01 | 0 | | employees6 | P_LT_2020-09-01 | 0 | | employees6 | P_LT_2020-10-01 | 0 | | employees6 | P_LT_2020-11-01 | 0 | | employees6 | P_LT_2020-12-01 | 2 | | employees6 | P_LT_2021-01-01 | 2 | | employees6 | P_LT_2021-02-01 | 0 | | employees6 | P_LT_2021-03-01 | 0 | | employees6 | P_LT_2021-04-01 | 0 | | employees6 | P_LT_2021-05-01 | 0 | | employees6 | P_LT_2021-06-01 | 0 | | employees6 | P_LT_2021-07-01 | 0 | | employees6 | P_LT_2021-08-01 | 2 | | employees6 | P_LT_2021-09-01 | 0 | | employees6 | P_LT_2021-10-01 | 0 | | employees6 | P_LT_2021-11-01 | 0 | | employees6 | P_LT_2021-12-01 | 0 | | employees6 | P_LT_2022-01-01 | 0 | | employees6 | P_LT_2022-02-01 | 0 | | employees6 | P_LT_2022-03-01 | 0 | | employees6 | P_LT_2022-04-01 | 0 | | employees6 | P_LT_2022-05-01 | 2 | | employees6 | P_LT_2022-06-01 | 2 | | employees6 | P_LT_2022-07-01 | 0 | | employees6 | P_LT_2022-08-01 | 0 | | employees6 | P_LT_2022-09-01 | 0 | | employees6 | P_LT_2022-10-01 | 2 | | employees6 | P_LT_2022-11-01 | 1 | | employees6 | P_LT_2022-12-01 | 0 | | employees6 | P_LT_2023-01-01 | 0 | | employees6 | P_LT_2023-02-01 | 0 | | employees6 | P_LT_2023-03-01 | 0 | | employees6 | P_LT_2023-04-01 | 0 | | employees6 | P_LT_2023-05-01 | 0 | | employees6 | P_LT_2023-06-01 | 0 | | employees6 | P_LT_2023-07-01 | 1 | | employees6 | P_LT_2023-08-01 | 1 | | employees6 | P_LT_2023-09-01 | 1 | | employees6 | P_LT_2023-10-01 | 3 | | employees6 | P_LT_2023-11-01 | 0 | | employees6 | P_LT_2023-12-01 | 0 | | employees6 | P_LT_2024-01-01 | 1 | | employees6 | P_LT_2024-02-01 | 0 | | employees6 | P_LT_2024-03-01 | 0 | | employees6 | P_LT_2024-04-01 | 0 | | employees6 | P_LT_2024-05-01 | 0 | | employees6 | P_LT_2024-06-01 | 0 | | employees6 | P_LT_2024-07-01 | 2 | | employees6 | P_LT_2024-08-01 | 1 | | employees6 | P_LT_2024-09-01 | 0 | | employees6 | P_LT_2024-10-01 | 0 | | employees6 | P_LT_2024-11-01 | 0 | | employees6 | P_LT_2024-12-01 | 0 | | employees6 | P_LT_2025-01-01 | 0 | | employees6 | P_LT_2025-02-01 | 2 | | employees6 | P_LT_2025-03-01 | 0 | | employees6 | P_LT_2025-04-01 | 0 | | employees6 | P_LT_2025-05-01 | 1 | | employees6 | P_LT_2025-06-01 | 1 | | employees6 | P_LT_2025-07-01 | 0 | | employees6 | P_LT_2025-08-01 | 0 | | employees6 | P_LT_2025-09-01 | 1 | | employees6 | P_LT_2025-10-01 | 0 | | employees6 | P_LT_2025-11-01 | 0 | | employees6 | P_LT_2025-12-01 | 0 | | employees6 | P_LT_2026-01-01 | 0 | | employees6 | P_MAXVALUE | 0 | +------------+-----------------+------------+ 95 rows in set (0.01 sec) ``` + 你会发现 Range INTERVAL 分区支持两种方法：PARTITION BY RANGE 和 PARTITION BY RANGE COLUMNS。它还可以根据设置的间隔自动进行分区。 ### 步骤 10. 清理表 ```sql DROP TABLE emp; DROP TABLE employees1; DROP TABLE employees2; DROP TABLE employees3; DROP TABLE employees4; DROP TABLE employees5; DROP TABLE employees6; ``` ```output Query OK, 0 rows affected (0.81 sec) ``` ## 模块 2：交换分区 + 在模块中，我们将创建一个 List COLUMNS 分区表，向表中插入数据，然后使用 EXCHANGE PARTITION 交换分区和非分区表之间的数据。 ### 步骤 1. 创建非分区表 `students` ```sql CREATE TABLE students ( id INT PRIMARY KEY, fname VARCHAR(30), lname VARCHAR(30), city VARCHAR(30), DOB DATE NOT NULL, dept INT NOT NULL ); ``` ```output tidb>CREATE TABLE students ( -> id INT PRIMARY KEY, -> fname VARCHAR(30), -> lname VARCHAR(30), -> city VARCHAR(30), -> DOB DATE NOT NULL, -> dept INT NOT NULL -> ); Query OK, 0 rows affected (0.53 sec) ``` ### 步骤 2. 检查变量 `tidb_enable_list_partition` 的值 ```sql SHOW VARIABLES LIKE 'tidb_enable_list_partition'; ``` ```output tidb>SHOW VARIABLES LIKE 'tidb_enable_list_partition'; +----------------------------+-------+ | Variable_name | Value | +----------------------------+-------+ | tidb_enable_list_partition | ON | +----------------------------+-------+ 1 row in set (0.00 sec) ``` + 在创建 List 表分区表之前，你需要将会话变量 tidb_enable_list_partition 的值设置为 ON。 ### 步骤 3. 创建新的 LIST COLUMNS 分区表 `students1` ```sql CREATE TABLE students1 ( id INT PRIMARY KEY, fname VARCHAR(30), lname VARCHAR(30), city VARCHAR(30), DOB DATE NOT NULL, dept INT NOT NULL ) PARTITION BY LIST ( city )( PARTITION p0 VALUES IN ('NewYork','BeiJing','Tokyo'), PARTITION p1 VALUES IN ('Atlanta', 'ShangHai', 'Sydney'), PARTITION p2 VALUES IN ('Boston', 'NanJing', 'Chicago')); ``` ```output ERROR 1697 (HY000): VALUES value for partition 'p0' must have type INT ``` + 出现错误 `ERROR 1697 (HY000): VALUES value for partition 'p0' must have type INT` 的原因是，PARTITION BY LIST 后的键必须为 `INT` 类型。 ```sql CREATE TABLE students1 ( id INT PRIMARY KEY, fname VARCHAR(30), lname VARCHAR(30), city VARCHAR(30), DOB DATE NOT NULL, dept INT NOT NULL ) PARTITION BY LIST COLUMNS ( city )( PARTITION p0 VALUES IN ('NewYork','BeiJing','Tokyo'), PARTITION p1 VALUES IN ('Atlanta', 'ShangHai', 'Sydney'), PARTITION p2 VALUES IN ('Boston', 'NanJing', 'Chicago')); ``` ```output ERROR 1503 (HY000): A CLUSTERED INDEX must include all columns in the table's partitioning function ``` + 出现错误 `ERROR 1503 (HY000): A CLUSTERED INDEX must include all columns in the table's partitioning function` 的原因是，对于有唯一键的表，分区表达式中的所有列都必须包含在唯一键中。主键也是唯一键。 ```sql CREATE TABLE students1 ( id INT NOT NULL, fname VARCHAR(30), lname VARCHAR(30), city VARCHAR(30), DOB DATE NOT NULL, dept INT NOT NULL, PRIMARY KEY(id, city) ) PARTITION BY LIST COLUMNS ( city )( PARTITION p0 VALUES IN ('NewYork','BeiJing','Tokyo'), PARTITION p1 VALUES IN ('Atlanta', 'ShangHai', 'Sydney'), PARTITION p2 VALUES IN ('Boston', 'NanJing', 'Chicago')); ``` ```output Query OK, 0 rows affected (0.54 sec) ``` ### 步骤 4. 向表 `students1` 中插入一些值 ```sql SELECT table_name, partition_name, partition_description, table_rows FROM information_schema.partitions WHERE table_schema = 'test' AND table_name='students1' ORDER BY partition_name; INSERT INTO students1 VALUES(1, 'Chars', 'Smith', 'NewYork', '2005-01-02', 1); INSERT INTO students1 VALUES(2, 'Tom', 'Garcia', 'NewYork', '2005-02-01', 6); INSERT INTO students1 VALUES(3, 'Crabb', 'Ford', 'NewYork', '2004-11-19', 7); ANALYZE TABLE students1; SELECT table_name, partition_name, partition_description, table_rows FROM information_schema.partitions WHERE table_schema = 'test' AND table_name='students1' ORDER BY partition_name; ``` ```output +------------+----------------+-------------------------------+------------+ | table_name | partition_name | partition_description | table_rows | +------------+----------------+-------------------------------+------------+ | students1 | p0 | 'NewYork','BeiJing','Tokyo' | 0 | | students1 | p1 | 'Atlanta','ShangHai','Sydney' | 0 | | students1 | p2 | 'Boston','NanJing','Chicago' | 0 | +------------+----------------+-------------------------------+------------+ 3 rows in set (0.01 sec) +------------+----------------+-------------------------------+------------+ | table_name | partition_name | partition_description | table_rows | +------------+----------------+-------------------------------+------------+ | students1 | p0 | 'NewYork','BeiJing','Tokyo' | 3 | | students1 | p1 | 'Atlanta','ShangHai','Sydney' | 0 | | students1 | p2 | 'Boston','NanJing','Chicago' | 0 | +------------+----------------+-------------------------------+------------+ 3 rows in set (0.00 sec) ``` + 我们可以看到所有数据都在 `p0` 分区上。 ### 步骤 5. 交换分区 + 将表 `students1` 的一个分区与非分区表 `students` 进行交换。 ```sql ALTER TABLE students1 EXCHANGE PARTITION `p0` WITH TABLE `students`; ``` ```output ERROR 1736 (HY000): Tables have different definitions ``` + 出现错误 `ERROR 1736 (HY000): Tables have different definitions` 的原因是，表 `students1` 中的主键是列 `(id, city)`，而表 `students` 中的主键只是列 `id`。 ```sql DROP TABLE students; CREATE TABLE students ( id INT NOT NULL, fname VARCHAR(30), lname VARCHAR(30), city VARCHAR(30), DOB DATE NOT NULL, dept INT NOT NULL, PRIMARY KEY(id, city) ); ALTER TABLE students1 EXCHANGE PARTITION `p0` WITH TABLE `students`; ANALYZE TABLE students1; SELECT table_name, partition_name, partition_description, table_rows FROM information_schema.partitions WHERE table_schema = 'test' AND table_name='students1' ORDER BY partition_name; SELECT * FROM students; ``` ```output +------------+----------------+-------------------------------+------------+ | table_name | partition_name | partition_description | table_rows | +------------+----------------+-------------------------------+------------+ | students1 | p0 | 'NewYork','BeiJing','Tokyo' | 0 | | students1 | p1 | 'Atlanta','ShangHai','Sydney' | 0 | | students1 | p2 | 'Boston','NanJing','Chicago' | 0 | +------------+----------------+-------------------------------+------------+ 3 rows in set (0.00 sec) +----+-------+--------+---------+------------+------+ | id | fname | lname | city | DOB | dept | +----+-------+--------+---------+------------+------+ | 1 | Chars | Smith | NewYork | 2005-01-02 | 1 | | 2 | Tom | Garcia | NewYork | 2005-02-01 | 6 | | 3 | Crabb | Ford | NewYork | 2004-11-19 | 7 | +----+-------+--------+---------+------------+------+ 3 rows in set (0.00 sec) ``` ### 步骤 6. 清理表 ```sql DROP TABLE students; DROP TABLE students1; ``` ```output Query OK, 0 rows affected (0.52 sec) ``` ## Module 3: Hash 和 Key 分区表 + 在本模块中，我们将创建一个 Hash 分区表和一个 Key 分区表。向两个表插入相同的数据后，我们将比较数据的分布情况。 ### 步骤 1. 创建新的 Hash 分区表 `employees1` ```sql CREATE TABLE employees1( id INT NOT NULL, fname VARCHAR(30), lname VARCHAR(30), hired DATE NOT NULL DEFAULT '1970-01-01', job_code INT ) PARTITION BY HASH(YEAR(hired)) PARTITIONS 5; ``` ```output Query OK, 0 rows affected (0.54 sec) ``` ### 步骤 2. 向 `employees1` 插入一些数据 ```sql INSERT INTO employees1 VALUES(1, 'Jim', 'Lee', '2023-04-04', 2); INSERT INTO employees1 VALUES(2, 'Tim', 'Anderson', '2023-05-10', 2); INSERT INTO employees1 VALUES(3, 'Mike', 'Jackson', '2023-05-10', 3); INSERT INTO employees1 VALUES(4, 'Benjamin', 'Thompson', '2023-09-24', 1); INSERT INTO employees1 VALUES(5, 'Charlotte', 'Clark', '2023-09-24', 1); ``` ```output Query OK, 1 row affected (0.00 sec) ``` + 我们发现插入的所有行的 YEAR(hired) 值都相同。让我们检查一下数据的分布情况。 ```sql ANALYZE TABLE employees1; SELECT table_name, partition_name, partition_description, table_rows FROM information_schema.partitions WHERE table_schema = 'test' AND table_name='employees1' ORDER BY partition_name; ``` ```outputs +------------+----------------+-----------------------+------------+ | table_name | partition_name | partition_description | table_rows | +------------+----------------+-----------------------+------------+ | employees1 | p0 | | 0 | | employees1 | p1 | | 0 | | employees1 | p2 | | 0 | | employees1 | p3 | | 5 | | employees1 | p4 | | 0 | +------------+----------------+-----------------------+------------+ 5 rows in set (0.01 sec) ``` + 你可以看到所有行都存储在分区 `p3`中。为什么呢？现在让我们截断表 `employees1` 并再次插入 5 行新数据。 ```sql TRUNCATE TABLE employees1; INSERT INTO employees1 VALUES(1, 'Jim', 'Lee', '2020-04-04', 2); INSERT INTO employees1 VALUES(2, 'Tim', 'Anderson', '2021-05-10', 2); INSERT INTO employees1 VALUES(3, 'Mike', 'Jackson', '2022-05-10', 3); INSERT INTO employees1 VALUES(4, 'Benjamin', 'Thompson', '2023-09-24', 1); INSERT INTO employees1 VALUES(5, 'Charlotte', 'Clark', '2024-09-24', 1); ``` ```output Query OK, 1 row affected (0.01 sec) ``` + 这次插入的所有行的 `YEAR(hired)` 值都不同。让我们检查一下数据的分布情况。 ```sql ANALYZE TABLE employees1; SELECT table_name, partition_name, partition_description, table_rows FROM information_schema.partitions WHERE table_schema = 'test' AND table_name='employees1' ORDER BY partition_name; ``` ```output +------------+----------------+-----------------------+------------+ | table_name | partition_name | partition_description | table_rows | +------------+----------------+-----------------------+------------+ | employees1 | p0 | | 1 | | employees1 | p1 | | 1 | | employees1 | p2 | | 1 | | employees1 | p3 | | 1 | | employees1 | p4 | | 1 | +------------+----------------+-----------------------+------------+ 5 rows in set (0.01 sec) ``` + 这次每个分区有一行数据。 ### 步骤 3. 创建新的 Key 分区表 `employees2` ```sql CREATE TABLE employees2( id INT NOT NULL, fname VARCHAR(30), lname VARCHAR(30), hired DATE NOT NULL DEFAULT '1970-01-01', job_code INT ) PARTITION BY KEY(hired) PARTITIONS 5; ``` ```output Query OK, 0 rows affected (0.54 sec) ``` ### 步骤 4. 向 `employees2` 插入一些数据 ```sql INSERT INTO employees2 VALUES(1, 'Jim', 'Lee', '2023-04-04', 2); INSERT INTO employees2 VALUES(2, 'Tim', 'Anderson', '2023-05-10', 2); INSERT INTO employees2 VALUES(3, 'Mike', 'Jackson', '2023-05-10', 3); INSERT INTO employees2 VALUES(4, 'Benjamin', 'Thompson', '2023-09-24', 1); INSERT INTO employees2 VALUES(5, 'Charlotte', 'Clark', '2023-09-24', 1); ``` ```output Query OK, 1 row affected (0.00 sec) ``` + 插入到 `employees2` 中的所有行的 `hired` 值都不同，但`YEAR(hired)` 的 `VALUES` 相同。让我们检查一下数据的分布情况。 ```sql ANALYZE TABLE employees2; SELECT table_name, partition_name, partition_description, table_rows FROM information_schema.partitions WHERE table_schema = 'test' AND table_name='employees2' ORDER BY partition_name; ``` ```output +------------+----------------+-----------------------+------------+ | table_name | partition_name | partition_description | table_rows | +------------+----------------+-----------------------+------------+ | employees2 | p0 | | 2 | | employees2 | p1 | | 2 | | employees2 | p2 | | 0 | | employees2 | p3 | | 1 | | employees2 | p4 | | 0 | +------------+----------------+-----------------------+------------+ 5 rows in set (0.02 sec) ``` + 这些行分布在 3 个分区中，YEAR(hired) 的值相同。让我们截断表 `employees2` 并再次插入 5 行新数据。 ```sql TRUNCATE TABLE employees2; INSERT INTO employees2 VALUES(1, 'Jim', 'Lee', '2021-05-10', 2); INSERT INTO employees2 VALUES(2, 'Tim', 'Anderson', '2021-05-10', 2); INSERT INTO employees2 VALUES(3, 'Mike', 'Jackson', '2021-05-10', 3); INSERT INTO employees2 VALUES(4, 'Benjamin', 'Thompson', '2021-05-10', 1); INSERT INTO employees2 VALUES(5, 'Charlotte', 'Clark', '2021-05-10', 1); ``` ```output Query OK, 1 row affected (0.00 sec) ``` + 插入到 `employees2` 中的所有行的 `hired` 值都相同。让我们检查一下数据的分布情况。 ```sql ANALYZE TABLE employees2; SELECT table_name, partition_name, partition_description, table_rows FROM information_schema.partitions WHERE table_schema = 'test' AND table_name='employees2' ORDER BY partition_name; ``` ```output +------------+----------------+-----------------------+------------+ | table_name | partition_name | partition_description | table_rows | +------------+----------------+-----------------------+------------+ | employees2 | p0 | | 0 | | employees2 | p1 | | 0 | | employees2 | p2 | | 0 | | employees2 | p3 | | 0 | | employees2 | p4 | | 5 | +------------+----------------+-----------------------+------------+ 5 rows in set (0.01 sec) ``` + 所有行都存储在分区 `p4`。为什么呢？让我们截断表 `employees2` 并再次插入 5 行新数据。 ```sql TRUNCATE TABLE employees2; INSERT INTO employees2 VALUES(1, 'Jim', 'Lee', '2020-04-04', 2); INSERT INTO employees2 VALUES(2, 'Tim', 'Anderson', '2021-05-10', 2); INSERT INTO employees2 VALUES(3, 'Mike', 'Jackson', '2022-05-10', 3); INSERT INTO employees2 VALUES(4, 'Benjamin', 'Thompson', '2023-09-24', 1); INSERT INTO employees2 VALUES(5, 'Charlotte', 'Clark', '2024-09-24', 1); ``` ```output Query OK, 1 row affected (0.00 sec) ``` + 插入到 `employees2` 中的所有行的 `hired` 值都不同。让我们检查一下数据的分布情况。 ```sql ANALYZE TABLE employees2; SELECT table_name, partition_name, partition_description, table_rows FROM information_schema.partitions WHERE table_schema = 'test' AND table_name='employees2' ORDER BY partition_name; ``` ```output +------------+----------------+-----------------------+------------+ | table_name | partition_name | partition_description | table_rows | +------------+----------------+-----------------------+------------+ | employees2 | p0 | | 1 | | employees2 | p1 | | 2 | | employees2 | p2 | | 1 | | employees2 | p3 | | 0 | | employees2 | p4 | | 1 | +------------+----------------+-----------------------+------------+ 5 rows in set (0.01 sec) ``` ### 步骤 5. 清理表 ```sql DROP TABLE employees1; DROP TABLE employees2; ``` ```output Query OK, 0 rows affected (0.53 sec) ``` ## 模块 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) ``` ## 实验结束