パーティションプルーニング機能により、関係のないパーティションへのアクセスを回避し、SQLの実行効率を大幅に向上させることができます。本記事では、主にパーティションプルーニングの原理と応用について説明します。
ユーザーがパーティションテーブルにアクセスする際、多くの場合、そのうちの一部のパーティションのみにアクセスする必要があります。オプティマイザーが関係のないパーティションへのアクセスを回避する最適化プロセスを、パーティションプルーニングと呼びます。パーティションプルーニングは、パーティションテーブルが提供する重要な最適化手法であり、これによりSQLの実行効率を大幅に向上させることができます。パーティションプルーニングの特性を活用し、アクセス時にパーティションを特定する条件を追加することで、関係のないデータへのアクセスを回避し、クエリ性能を最適化できます。
パーティションプルーニング自体は比較的複雑なプロセスであり、オプティマイザーはユーザーテーブルのパーティション情報とSQLで指定された条件に基づいて、関連するパーティション情報を抽出する必要があります。SQL内の条件はしばしば複雑であるため、抽出ロジック全体の複雑さもそれに伴って増加します。このプロセスは、OceanBaseデータベースのQuery Rangeサブモジュールによって実行されます。
ユーザーがパーティションテーブルにアクセスする際、col1が1のデータはすべて1番目のパーティション(p1)に存在するため、0番、2番、3番、4番のパーティションにアクセスする必要はなく、そのパーティションのみにアクセスすればよいことがわかります。以下のように：
obclient> CREATE TABLE tbl1(col1 INT,col2 INT) PARTITION BY HASH(col1) PARTITIONS 5;
obclient> SELECT * FROM tbl1 WHERE col1 = 1;
EXPLAINを使用して実行計画を確認すると、パーティションプルーニングの結果を見ることができます。
obclient> EXPLAIN SELECT * FROM tbl1 WHERE col1 = 1;
結果は次のとおりです：
+------------------------------------------------------------------------------------+
| Query Plan |
+------------------------------------------------------------------------------------+
| =============================================== |
| |ID|OPERATOR |NAME|EST.ROWS|EST.TIME(us)| |
| ----------------------------------------------- |
| |0 |TABLE FULL SCAN|TBL1|1 |4 | |
| =============================================== |
| Outputs & filters: |
| ------------------------------------- |
| 0 - output([TBL1.COL1], [TBL1.COL2]), filter([TBL1.COL1 = 1]), rowset=16 |
| access([TBL1.COL1], [TBL1.COL2]), partitions(p1) |
| is_index_back=false, is_global_index=false, filter_before_indexback[false], |
| range_key([TBL1.__pk_increment]), range(MIN ; MAX)always true |
+------------------------------------------------------------------------------------+
11 rows in set
パーティションプルーニングの基本原理
Hash/Listパーティショニング
パーティションプルーニングとは、where 句の条件に基づいてパーティション列の値を計算し、その結果によってアクセスするパーティションを判断する手法です。クエリ条件が等価条件である場合にのみ、パーティションプルーニングを実行できます。
パーティションキーを c1 とし、クエリ条件を等価条件とするパーティションテーブルを作成します。例：
obclient> CREATE TABLE t1(c1 INT,c2 INT) PARTITION BY HASH(c1) PARTITIONS 5;
obclient> SELECT * FROM t1 WHERE c1 = 1;
EXPLAIN を使用してパーティションプルーニングの結果を確認します。
obclient> EXPLAIN SELECT * FROM t1 WHERE c1 = 1;
実行結果は次のとおりです：
+------------------------------------------------------------------------------------+
| Query Plan |
+------------------------------------------------------------------------------------+
| =============================================== |
| |ID|OPERATOR |NAME|EST.ROWS|EST.TIME(us)| |
| ----------------------------------------------- |
| |0 |TABLE FULL SCAN|T1 |1 |4 | |
| =============================================== |
| Outputs & filters: |
| ------------------------------------- |
| 0 - output([T1.C1], [T1.C2]), filter([T1.C1 = 1]), rowset=16 |
| access([T1.C1], [T1.C2]), partitions(p1) |
| is_index_back=false, is_global_index=false, filter_before_indexback[false], |
| range_key([T1.__pk_increment]), range(MIN ; MAX)always true |
+------------------------------------------------------------------------------------+
11 rows in set
クエリ条件に不等号が含まれる場合、パーティションプルーニングはできません。
obclient> EXPLAIN SELECT * FROM t1 WHERE c1 > 1;
実行結果は次のとおりです：
+------------------------------------------------------------------------------------+
| Query Plan |
+------------------------------------------------------------------------------------+
| ============================================================= |
| |ID|OPERATOR |NAME |EST.ROWS|EST.TIME(us)| |
| ------------------------------------------------------------- |
| |0 |PX COORDINATOR | |1 |21 | |
| |1 |└─EXCHANGE OUT DISTR |:EX10000|1 |21 | |
| |2 | └─PX PARTITION ITERATOR| |1 |19 | |
| |3 | └─TABLE FULL SCAN |T1 |1 |19 | |
| ============================================================= |
| Outputs & filters: |
| ------------------------------------- |
| 0 - output([INTERNAL_FUNCTION(T1.C1, T1.C2)]), filter(nil), rowset=16 |
| 1 - output([INTERNAL_FUNCTION(T1.C1, T1.C2)]), filter(nil), rowset=16 |
| dop=1 |
| 2 - output([T1.C1], [T1.C2]), filter(nil), rowset=16 |
| force partition granule |
| 3 - output([T1.C1], [T1.C2]), filter([T1.C1 > 1]), rowset=16 |
| access([T1.C1], [T1.C2]), partitions(p[0-4]) |
| is_index_back=false, is_global_index=false, filter_before_indexback[false], |
| range_key([T1.__pk_increment]), range(MIN ; MAX)always true |
+------------------------------------------------------------------------------------+
19 rows in set
Rangeサブパーティション
where 句のパーティションキーとテーブルで定義されたパーティション範囲の共通部分によって、アクセスするパーティションが決定されます。クエリ条件が等価条件であっても不等価条件であっても、パーティションプルーニングが可能です。
例えば、以下のパーティションテーブルでは、パーティションキーは c1 です。クエリ条件が不等価条件 c1 < 150 and c1 > 100 の場合、パーティションプルーニングが可能です。例：
obclient> CREATE TABLE t1(c1 INT,c2 INT) PARTITION BY RANGE(c1)
(PARTITION p0 VALUES LESS THAN(100),
PARTITION p1 VALUES LESS THAN(200)
);
obclient> SELECT * FROM t1 WHERE c1 < 150 and c1 > 110;
EXPLAIN を使用して、パーティションプルーニングの結果を確認します。
obclient> EXPLAIN SELECT * FROM t1 WHERE c1 < 150 and c1 > 110;
実行結果は次のとおりです：
| Query Plan |
+------------------------------------------------------------------------------------------+
| =============================================== |
| |ID|OPERATOR |NAME|EST.ROWS|EST.TIME(us)| |
| ----------------------------------------------- |
| |0 |TABLE FULL SCAN|T1 |1 |4 | |
| =============================================== |
| Outputs & filters: |
| ------------------------------------- |
| 0 - output([T1.C1], [T1.C2]), filter([T1.C1 < 150], [T1.C1 > 110]), rowset=16 |
| access([T1.C1], [T1.C2]), partitions(p1) |
| is_index_back=false, is_global_index=false, filter_before_indexback[false,false], |
| range_key([T1.__pk_increment]), range(MIN ; MAX)always true |
+------------------------------------------------------------------------------------------+
11 rows in set
セカンダリパーティションプルーニングの基本原理
セカンダリパーティションについては、まずパーティションキーに基づいてアクセスするパーティションを特定し、次にサブパーティションキーに基づいてアクセスするサブパーティションを特定します。最後に、これらを掛け合わせることで、アクセスするすべての物理パーティションを決定します。
以下の例では、計算の結果、パーティションプルーニングの結果は p0、セカンダリパーティションプルーニングの結果は sp0 となります。したがって、アクセスする物理パーティションは p0sp0 です。
注意
この例では、アクセスする物理パーティションの識別子は p0sp0 ですが、この識別子はセカンダリパーティションの名前ではありません。テンプレート型のセカンダリパーティションテーブルでは、セカンダリパーティションの命名規則は ($part_name)s($subpart_name) です。例えば、p0ssp0 のようになります。
obclient> CREATE TABLE tbl2_rr(col1 INT,col2 INT)
PARTITION BY RANGE(col1)
SUBPARTITION BY RANGE(col2)
SUBPARTITION TEMPLATE
(SUBPARTITION sp0 VALUES LESS THAN(1000),
SUBPARTITION sp1 VALUES LESS THAN(2000)
)
(PARTITION p0 VALUES LESS THAN(100),
PARTITION p1 VALUES LESS THAN(200)
);
obclient> SELECT * FROM tbl2_rr
WHERE (col1 = 1 or col1 = 2) and (col2 > 101 and col2 < 150);
EXPLAIN を使用して、パーティションプルーニングの結果を確認します。
obclient> EXPLAIN SELECT * FROM tbl2_rr
WHERE (col1 = 1 or col1 = 2) and (col2 > 101 and col2 < 150);
実行結果は次のとおりです：
+-----------------------------------------------------------------------------------------------------------------------------------------------------+
| Query Plan |
+-----------------------------------------------------------------------------------------------------------------------------------------------------+
| ================================================== |
| |ID|OPERATOR |NAME |EST.ROWS|EST.TIME(us)| |
| -------------------------------------------------- |
| |0 |TABLE FULL SCAN|TBL2_RR|1 |4 | |
| ================================================== |
| Outputs & filters: |
| ------------------------------------- |
| 0 - output([TBL2_RR.COL1], [TBL2_RR.COL2]), filter([TBL2_RR.COL2 > 101], [TBL2_RR.COL2 < 150], [TBL2_RR.COL1 = 1 OR TBL2_RR.COL1 = 2]), rowset=16 |
| access([TBL2_RR.COL1], [TBL2_RR.COL2]), partitions(p0sp0) |
| is_index_back=false, is_global_index=false, filter_before_indexback[false,false,false], |
| range_key([TBL2_RR.__pk_increment]), range(MIN ; MAX)always true |
+-----------------------------------------------------------------------------------------------------------------------------------------------------+
11 rows in set
場合によっては、パーティションプルーニングである程度の拡大が発生する可能性がありますが、オプティマイザーはプルーニングされた結果がアクセス対象データの超集合であることを保証し、データの欠落は発生しません。