パーティションプルーニング機能を使用すると、関係のないパーティションへのアクセスを回避でき、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/リストパーティション
パーティションクリッピングとは、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
一部のシナリオでは、パーティションクリッピングによってある程度の拡大が生じる可能性がありますが、オプティマイザーはクリッピングの結果が必要なデータの超集合であり、データ損失が発生しないように保証します。