# 演習 2: パーティショニングされたテーブル ## 概要 この演習では、RANGE パーティショニング、RANGE COLUMNS パーティショニング、LIST パーティショニング、LIST COLUMNS パーティショニングを使用したテーブルを作成および管理します。さらに、HASH パーティショニングとキーでのパーティショニングの違いについて学びます。最後に、パーティショニングされたテーブルと非パーティショニングされたテーブルのパフォーマンスを比較します。 ### 所要時間 + この演習は約 60 分程度かかります。 > [!TIP] > 演習環境がデプロイされたら、下のバーの**次のモジュール**をクリックして**モジュール 1**を開始します。 ## モジュール 1: RANGE、RANGE COLUMN、RANGE INTERVAL パーティショニングテーブル + この演習では、RANGE パーティショニング、RANGE COLUMNS パーティショニングおよびRange INTERVAL パーティショニングのテーブルを作成および管理します。 ### 手順 1. リモート Linux にログインします + ここをクリック - [ターミナル 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. 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()`のような式はサポートされていません。 1つ以上の列をパーティションキーとして使用できます。 ### 手順 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レンジパーティションテーブル `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` の2つのパーティションを1つのパーティションにマージします。 + `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/ja/tidb/v7.5/partitioned-table#dynamic-pruning-mode) を参照してください。 ```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) ``` + Range パーティションテーブルの`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 の2つの方法をサポートしています。また、設定された間隔に従って自動的にパーティショニングを行うことができます。 ### 手順 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 COLUMNパーティションテーブルを作成し、テーブルにデータを挿入してから、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 COLUMNパーティションテーブル `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` のパーティションの1つを非パーティションテーブル `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) ``` ## モジュール 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` をトランケートして、再度新しい行を挿入してみましょう。 ```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) ``` + 今回、各パーティションに1行ずつあります。 ### 手順 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)` の値は同じです。データの分布を確認してみましょう。 ```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) ``` + 行は、`YEAR(hired)` の値が同じにもかかわらず、3つのパーティションに分散されています。テーブル `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 COLUMNパーティションに分割します。 + 元のテーブルとパーティションテーブルの両方に対してクエリを実行し、パフォーマンスを比較します。 ### 手順 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 COLUMNパーティションテーブル `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. 2つのテーブルの `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) ``` ## 演習終了