パーティションのプルーニング
パーティションのプルーニングは、パーティション化されたテーブルに適用されるパフォーマンスの最適化です。クエリ ステートメントのフィルター条件を分析し、必要なデータが含まれていないパーティションを考慮から除外 (プルーニング) します。不要なパーティションを排除することで、TiDB はアクセスする必要のあるデータの量を削減し、クエリの実行時間を大幅に改善できる可能性があります。
次に例を示します。
CREATE TABLE t1 (
id INT NOT NULL PRIMARY KEY,
pad VARCHAR(100)
)
PARTITION BY RANGE COLUMNS(id) (
PARTITION p0 VALUES LESS THAN (100),
PARTITION p1 VALUES LESS THAN (200),
PARTITION p2 VALUES LESS THAN (MAXVALUE)
);
INSERT INTO t1 VALUES (1, 'test1'),(101, 'test2'), (201, 'test3');
EXPLAIN SELECT * FROM t1 WHERE id BETWEEN 80 AND 120;
+----------------------------+---------+-----------+------------------------+------------------------------------------------+
| id | estRows | task | access object | operator info |
+----------------------------+---------+-----------+------------------------+------------------------------------------------+
| PartitionUnion_8 | 80.00 | root | | |
| ├─TableReader_10 | 40.00 | root | | data:TableRangeScan_9 |
| │ └─TableRangeScan_9 | 40.00 | cop[tikv] | table:t1, partition:p0 | range:[80,120], keep order:false, stats:pseudo |
| └─TableReader_12 | 40.00 | root | | data:TableRangeScan_11 |
| └─TableRangeScan_11 | 40.00 | cop[tikv] | table:t1, partition:p1 | range:[80,120], keep order:false, stats:pseudo |
+----------------------------+---------+-----------+------------------------+------------------------------------------------+
5 rows in set (0.00 sec)
パーティションプルーニングの使用シナリオ
パーティション プルーニングの使用シナリオは、レンジ パーティション テーブルとハッシュ パーティション テーブルの 2 種類のパーティション テーブルで異なります。
ハッシュ パーティション テーブルでパーティション プルーニングを使用する
このセクションでは、ハッシュ パーティション テーブルでのパーティション プルーニングの適用可能な使用シナリオと適用不可能な使用シナリオについて説明します。
ハッシュ パーティション テーブルに適用されるシナリオ
パーティションのプルーニングは、ハッシュ パーティション テーブルの等価比較のクエリ条件にのみ適用されます。
create table t (x int) partition by hash(x) partitions 4;
explain select * from t where x = 1;
+-------------------------+----------+-----------+-----------------------+--------------------------------+
| id | estRows | task | access object | operator info |
+-------------------------+----------+-----------+-----------------------+--------------------------------+
| TableReader_8 | 10.00 | root | | data:Selection_7 |
| └─Selection_7 | 10.00 | cop[tikv] | | eq(test.t.x, 1) |
| └─TableFullScan_6 | 10000.00 | cop[tikv] | table:t, partition:p1 | keep order:false, stats:pseudo |
+-------------------------+----------+-----------+-----------------------+--------------------------------+
上記の SQL ステートメントでは、条件x = 1から、すべての結果が 1 つのパーティションに収まることがわかります。値1は Hash パーティションを通過した後、 p1パーティションにあることが確認できます。したがって、 p1パーティションのみをスキャンする必要があり、一致する結果がないp2 、 p3 、およびp4パーティションにアクセスする必要はありません。実行計画からTableFullScan演算子は 1 つだけ出現し、 access objectにはp1パーティションが指定されているため、 partition pruning有効であることが確認できます。
ハッシュ パーティション テーブルに適用できないシナリオ
このセクションでは、ハッシュ パーティション テーブルでのパーティション プルーニングの適用外の 2 つの使用シナリオについて説明します。
シナリオ 1
クエリ結果が 1 つのパーティション ( in 、 between 、 > 、 < 、 >= 、 <=など) にのみ含まれるという条件を確認できない場合は、パーティション プルーニング最適化を使用できません。例えば：
create table t (x int) partition by hash(x) partitions 4;
explain select * from t where x > 2;
+------------------------------+----------+-----------+-----------------------+--------------------------------+
| id | estRows | task | access object | operator info |
+------------------------------+----------+-----------+-----------------------+--------------------------------+
| Union_10 | 13333.33 | root | | |
| ├─TableReader_13 | 3333.33 | root | | data:Selection_12 |
| │ └─Selection_12 | 3333.33 | cop[tikv] | | gt(test.t.x, 2) |
| │ └─TableFullScan_11 | 10000.00 | cop[tikv] | table:t, partition:p0 | keep order:false, stats:pseudo |
| ├─TableReader_16 | 3333.33 | root | | data:Selection_15 |
| │ └─Selection_15 | 3333.33 | cop[tikv] | | gt(test.t.x, 2) |
| │ └─TableFullScan_14 | 10000.00 | cop[tikv] | table:t, partition:p1 | keep order:false, stats:pseudo |
| ├─TableReader_19 | 3333.33 | root | | data:Selection_18 |
| │ └─Selection_18 | 3333.33 | cop[tikv] | | gt(test.t.x, 2) |
| │ └─TableFullScan_17 | 10000.00 | cop[tikv] | table:t, partition:p2 | keep order:false, stats:pseudo |
| └─TableReader_22 | 3333.33 | root | | data:Selection_21 |
| └─Selection_21 | 3333.33 | cop[tikv] | | gt(test.t.x, 2) |
| └─TableFullScan_20 | 10000.00 | cop[tikv] | table:t, partition:p3 | keep order:false, stats:pseudo |
+------------------------------+----------+-----------+-----------------------+--------------------------------+
この場合、対応するハッシュ パーティションがx > 2条件で確認できないため、パーティションのプルーニングは適用できません。
シナリオ 2
パーティションのプルーニングのルールの最適化は、クエリ プランの生成フェーズで実行されるため、パーティションのプルーニングは、実行フェーズでのみフィルター条件を取得できるシナリオには適していません。例えば：
create table t (x int) partition by hash(x) partitions 4;
explain select * from t2 where x = (select * from t1 where t2.x = t1.x and t2.x < 2);
+--------------------------------------+----------+-----------+------------------------+----------------------------------------------+
| id | estRows | task | access object | operator info |
+--------------------------------------+----------+-----------+------------------------+----------------------------------------------+
| Projection_13 | 9990.00 | root | | test.t2.x |
| └─Apply_15 | 9990.00 | root | | inner join, equal:[eq(test.t2.x, test.t1.x)] |
| ├─TableReader_18(Build) | 9990.00 | root | | data:Selection_17 |
| │ └─Selection_17 | 9990.00 | cop[tikv] | | not(isnull(test.t2.x)) |
| │ └─TableFullScan_16 | 10000.00 | cop[tikv] | table:t2 | keep order:false, stats:pseudo |
| └─Selection_19(Probe) | 0.80 | root | | not(isnull(test.t1.x)) |
| └─MaxOneRow_20 | 1.00 | root | | |
| └─Union_21 | 2.00 | root | | |
| ├─TableReader_24 | 2.00 | root | | data:Selection_23 |
| │ └─Selection_23 | 2.00 | cop[tikv] | | eq(test.t2.x, test.t1.x), lt(test.t2.x, 2) |
| │ └─TableFullScan_22 | 2500.00 | cop[tikv] | table:t1, partition:p0 | keep order:false, stats:pseudo |
| └─TableReader_27 | 2.00 | root | | data:Selection_26 |
| └─Selection_26 | 2.00 | cop[tikv] | | eq(test.t2.x, test.t1.x), lt(test.t2.x, 2) |
| └─TableFullScan_25 | 2500.00 | cop[tikv] | table:t1, partition:p1 | keep order:false, stats:pseudo |
+--------------------------------------+----------+-----------+------------------------+----------------------------------------------+
このクエリがt2から行を読み取るたびに、 t1パーティションテーブルに対してクエリが実行されます。理論的には、この時点でt1.x = valのフィルター条件が満たされますが、実際には、パーティションのプルーニングは、実行フェーズではなく、クエリ プランの生成フェーズでのみ有効になります。
レンジ パーティション分割されたテーブルでパーティションのプルーニングを使用する
このセクションでは、レンジ パーティション分割されたテーブルでのパーティション プルーニングの適用可能な使用シナリオと適用不可能な使用シナリオについて説明します。
範囲パーティション分割されたテーブルに適用されるシナリオ
このセクションでは、レンジ パーティション テーブルでのパーティション プルーニングの 3 つの適用可能な使用シナリオについて説明します。
シナリオ 1
パーティション プルーニングは、Range パーティション分割されたテーブルの等価比較のクエリ条件に適用されます。例えば：
create table t (x int) partition by range (x) (
partition p0 values less than (5),
partition p1 values less than (10),
partition p2 values less than (15)
);
explain select * from t where x = 3;
+-------------------------+----------+-----------+-----------------------+--------------------------------+
| id | estRows | task | access object | operator info |
+-------------------------+----------+-----------+-----------------------+--------------------------------+
| TableReader_8 | 10.00 | root | | data:Selection_7 |
| └─Selection_7 | 10.00 | cop[tikv] | | eq(test.t.x, 3) |
| └─TableFullScan_6 | 10000.00 | cop[tikv] | table:t, partition:p0 | keep order:false, stats:pseudo |
+-------------------------+----------+-----------+-----------------------+--------------------------------+
パーティションのプルーニングは、 inクエリ条件を使用する等価比較にも適用されます。例えば：
create table t (x int) partition by range (x) (
partition p0 values less than (5),
partition p1 values less than (10),
partition p2 values less than (15)
);
explain select * from t where x in(1,13);
+-----------------------------+----------+-----------+-----------------------+--------------------------------+
| id | estRows | task | access object | operator info |
+-----------------------------+----------+-----------+-----------------------+--------------------------------+
| Union_8 | 40.00 | root | | |
| ├─TableReader_11 | 20.00 | root | | data:Selection_10 |
| │ └─Selection_10 | 20.00 | cop[tikv] | | in(test.t.x, 1, 13) |
| │ └─TableFullScan_9 | 10000.00 | cop[tikv] | table:t, partition:p0 | keep order:false, stats:pseudo |
| └─TableReader_14 | 20.00 | root | | data:Selection_13 |
| └─Selection_13 | 20.00 | cop[tikv] | | in(test.t.x, 1, 13) |
| └─TableFullScan_12 | 10000.00 | cop[tikv] | table:t, partition:p2 | keep order:false, stats:pseudo |
+-----------------------------+----------+-----------+-----------------------+--------------------------------+
上記の SQL ステートメントでは、 x in(1,13)条件から、すべての結果がいくつかのパーティションに分類されることがわかります。分析の結果、 x = 1のすべてのレコードはp0パーティションにあり、 x = 13のすべてのレコードはp2パーティションにあるため、 p0とp2パーティションのみにアクセスする必要があることがわかります。
シナリオ 2
パーティションのプルーニングはbetween 、 > 、 < 、 = 、 >= 、 <=などの間隔比較のクエリ条件に適用されます。例えば：
create table t (x int) partition by range (x) (
partition p0 values less than (5),
partition p1 values less than (10),
partition p2 values less than (15)
);
explain select * from t where x between 7 and 14;
+-----------------------------+----------+-----------+-----------------------+-----------------------------------+
| id | estRows | task | access object | operator info |
+-----------------------------+----------+-----------+-----------------------+-----------------------------------+
| Union_8 | 500.00 | root | | |
| ├─TableReader_11 | 250.00 | root | | data:Selection_10 |
| │ └─Selection_10 | 250.00 | cop[tikv] | | ge(test.t.x, 7), le(test.t.x, 14) |
| │ └─TableFullScan_9 | 10000.00 | cop[tikv] | table:t, partition:p1 | keep order:false, stats:pseudo |
| └─TableReader_14 | 250.00 | root | | data:Selection_13 |
| └─Selection_13 | 250.00 | cop[tikv] | | ge(test.t.x, 7), le(test.t.x, 14) |
| └─TableFullScan_12 | 10000.00 | cop[tikv] | table:t, partition:p2 | keep order:false, stats:pseudo |
+-----------------------------+----------+-----------+-----------------------+-----------------------------------+
シナリオ 3
パーティションのプルーニングは、パーティション式がfn(col)の単純な形式であり、クエリ条件が> 、 < 、 = 、 >= 、および<=のいずれかであり、 fn関数が単調であるシナリオに適用されます。
fn関数が単調な場合、任意のxおよびyに対して、 x > yの場合、 fn(x) > fn(y) .すると、このfn関数は厳密に単調であると言えます。任意のxおよびy場合、 x > yの場合はfn(x) >= fn(y) 。この場合、 fn 「単調」とも言えます。理論的には、すべての単調関数は、厳密かどうかに関係なく、パーティション プルーニングによってサポートされます。現在、TiDB は次の単調関数のみをサポートしています。
たとえば、パーティションのプルーニングは、パーティション式がfn(col)の形式である場合に有効になります。ここで、 fnは単調な関数to_daysです。
create table t (id datetime) partition by range (to_days(id)) (
partition p0 values less than (to_days('2020-04-01')),
partition p1 values less than (to_days('2020-05-01')));
explain select * from t where id > '2020-04-18';
+-------------------------+----------+-----------+-----------------------+-------------------------------------------+
| id | estRows | task | access object | operator info |
+-------------------------+----------+-----------+-----------------------+-------------------------------------------+
| TableReader_8 | 3333.33 | root | | data:Selection_7 |
| └─Selection_7 | 3333.33 | cop[tikv] | | gt(test.t.id, 2020-04-18 00:00:00.000000) |
| └─TableFullScan_6 | 10000.00 | cop[tikv] | table:t, partition:p1 | keep order:false, stats:pseudo |
+-------------------------+----------+-----------+-----------------------+-------------------------------------------+
範囲パーティション分割されたテーブルに適用できないシナリオ
パーティションのプルーニングのルールの最適化は、クエリ プランの生成フェーズで実行されるため、パーティションのプルーニングは、実行フェーズでのみフィルター条件を取得できるシナリオには適していません。例えば：
create table t1 (x int) partition by range (x) (
partition p0 values less than (5),
partition p1 values less than (10));
create table t2 (x int);
explain select * from t2 where x < (select * from t1 where t2.x < t1.x and t2.x < 2);
+--------------------------------------+----------+-----------+------------------------+-----------------------------------------------------------+
| id | estRows | task | access object | operator info |
+--------------------------------------+----------+-----------+------------------------+-----------------------------------------------------------+
| Projection_13 | 9990.00 | root | | test.t2.x |
| └─Apply_15 | 9990.00 | root | | CARTESIAN inner join, other cond:lt(test.t2.x, test.t1.x) |
| ├─TableReader_18(Build) | 9990.00 | root | | data:Selection_17 |
| │ └─Selection_17 | 9990.00 | cop[tikv] | | not(isnull(test.t2.x)) |
| │ └─TableFullScan_16 | 10000.00 | cop[tikv] | table:t2 | keep order:false, stats:pseudo |
| └─Selection_19(Probe) | 0.80 | root | | not(isnull(test.t1.x)) |
| └─MaxOneRow_20 | 1.00 | root | | |
| └─Union_21 | 2.00 | root | | |
| ├─TableReader_24 | 2.00 | root | | data:Selection_23 |
| │ └─Selection_23 | 2.00 | cop[tikv] | | lt(test.t2.x, 2), lt(test.t2.x, test.t1.x) |
| │ └─TableFullScan_22 | 2.50 | cop[tikv] | table:t1, partition:p0 | keep order:false, stats:pseudo |
| └─TableReader_27 | 2.00 | root | | data:Selection_26 |
| └─Selection_26 | 2.00 | cop[tikv] | | lt(test.t2.x, 2), lt(test.t2.x, test.t1.x) |
| └─TableFullScan_25 | 2.50 | cop[tikv] | table:t1, partition:p1 | keep order:false, stats:pseudo |
+--------------------------------------+----------+-----------+------------------------+-----------------------------------------------------------+
14 rows in set (0.00 sec)
このクエリがt2から行を読み取るたびに、 t1パーティションテーブルに対してクエリが実行されます。理論的には、この時点でt1.x> valフィルター条件が満たされますが、実際には、パーティションのプルーニングはクエリ プランの生成フェーズでのみ有効であり、実行フェーズでは有効ではありません。