パーティションプルーニング機能により、関係のないパーティションへのアクセスを回避し、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 + c2 であり、かつその式が等価条件の一部として全体として現れるパーティションプルーニングの例を以下に示します：
obclient> CREATE TABLE t1_h (c1 INT, c2 INT) PARTITION BY HASH(c1 + c2) PARTITIONS 5;
obclient> EXPLAIN SELECT * FROM t1_h WHERE c1 + c2 = 1;
実行結果は次のとおりです：
+------------------------------------------------------------------------------------+
| Query Plan |
+------------------------------------------------------------------------------------+
| =============================================== |
| |ID|OPERATOR |NAME|EST.ROWS|EST.TIME(us)| |
| ----------------------------------------------- |
| |0 |TABLE FULL SCAN|t1_h|1 |4 | |
| =============================================== |
| Outputs & filters: |
| ------------------------------------- |
| 0 - output([t1_h.c1], [t1_h.c2]), filter([t1_h.c1 + t1_h.c2 = 1]), rowset=16 |
| access([t1_h.c1], [t1_h.c2]), partitions(p1) |
| is_index_back=false, is_global_index=false, filter_before_indexback[false], |
| range_key([t1_h.__pk_increment]), range(MIN ; MAX)always true |
+------------------------------------------------------------------------------------+
11 rows in set
Rangeパーティション
WHERE 句のパーティションキーの範囲とテーブル定義のパーティション範囲の共通部分により、アクセスするパーティションを特定できます。
Rangeパーティションでは：
パーティション条件が列の場合、クエリ条件が等価条件でも不等号条件でも、パーティションプルーニングがサポートされます。
パーティション条件が式で、かつクエリ条件が等価条件の場合、パーティションプルーニングがサポートされます。
例えば、パーティション条件が式 c1、クエリ条件が不等号条件 c1 < 150 and c1 > 100 の場合、パーティションプルーニングが可能です。例：
obclient> CREATE TABLE t1_r (c1 INT, c2 INT) PARTITION BY RANGE(c1)
(PARTITION p0 VALUES LESS THAN(100),
PARTITION p1 VALUES LESS THAN(200)
);
obclient> EXPLAIN SELECT * FROM t1_r WHERE c1 < 150 and c1 > 110;
結果は次のとおりです：
+------------------------------------------------------------------------------------------+
| Query Plan |
+------------------------------------------------------------------------------------------+
| =============================================== |
| |ID|OPERATOR |NAME|EST.ROWS|EST.TIME(us)| |
| ----------------------------------------------- |
| |0 |TABLE FULL SCAN|t1_r|1 |4 | |
| =============================================== |
| Outputs & filters: |
| ------------------------------------- |
| 0 - output([t1_r.c1], [t1_r.c2]), filter([t1_r.c1 < 150], [t1_r.c1 > 110]), rowset=16 |
| access([t1_r.c1], [t1_r.c2]), partitions(p1) |
| is_index_back=false, is_global_index=false, filter_before_indexback[false,false], |
| range_key([t1_r.__pk_increment]), range(MIN ; MAX)always true |
+------------------------------------------------------------------------------------------+
11 rows in set
さらに、パーティション条件の式で YEAR()、TO_DAYS()、TO_SECONDS() などの日付関数を使用している場合、クエリ条件で指定された範囲に基づいてパーティションプルーニングを行うこともサポートされます。
例：
obclient> CREATE TABLE tbl_r (log_id BIGINT NOT NULL, log_value VARCHAR(50),log_date datetime NOT NULL)
PARTITION BY RANGE(to_days(log_date))
(PARTITION M202001 VALUES LESS THAN(to_days('2020/02/01'))
, PARTITION M202002 VALUES LESS THAN(to_days('2020/03/01'))
, PARTITION M202003 VALUES LESS THAN(to_days('2020/04/01'))
, PARTITION M202004 VALUES LESS THAN(to_days('2020/05/01'))
, PARTITION M202005 VALUES LESS THAN(to_days('2020/06/01'))
, PARTITION M202006 VALUES LESS THAN(to_days('2020/07/01'))
, PARTITION M202007 VALUES LESS THAN(to_days('2020/08/01'))
, PARTITION M202008 VALUES LESS THAN(to_days('2020/09/01'))
, PARTITION M202009 VALUES LESS THAN(to_days('2020/10/01'))
, PARTITION M202010 VALUES LESS THAN(to_days('2020/11/01'))
, PARTITION M202011 VALUES LESS THAN(to_days('2020/12/01'))
, PARTITION M202012 VALUES LESS THAN(to_days('2021/01/01'))
);
obclient> EXPLAIN SELECT * FROM tbl_r WHERE log_date > '2020/07/15' and log_date <'2020/10/07';
結果は次のとおりです：
+-----------------------------------------------------------------------------------------------------------------------------------------------------------+
| Query Plan |
+-----------------------------------------------------------------------------------------------------------------------------------------------------------+
| ============================================================= |
| |ID|OPERATOR |NAME |EST.ROWS|EST.TIME(us)| |
| ------------------------------------------------------------- |
| |0 |PX COORDINATOR | |1 |16 | |
| |1 |└─EXCHANGE OUT DISTR |:EX10000|1 |16 | |
| |2 | └─PX PARTITION ITERATOR| |1 |16 | |
| |3 | └─TABLE FULL SCAN |tbl_r |1 |16 | |
| ============================================================= |
| Outputs & filters: |
| ------------------------------------- |
| 0 - output([INTERNAL_FUNCTION(tbl_r.log_id, tbl_r.log_value, tbl_r.log_date)]), filter(nil), rowset=16 |
| 1 - output([INTERNAL_FUNCTION(tbl_r.log_id, tbl_r.log_value, tbl_r.log_date)]), filter(nil), rowset=16 |
| dop=1 |
| 2 - output([tbl_r.log_date], [tbl_r.log_id], [tbl_r.log_value]), filter(nil), rowset=16 |
| force partition granule |
| 3 - output([tbl_r.log_date], [tbl_r.log_id], [tbl_r.log_value]), filter([tbl_r.log_date > cast('2020/07/15', MYSQL_DATETIME(-1, -1))], [tbl_r.log_date |
| < cast('2020/10/07', MYSQL_DATETIME(-1, -1))]), rowset=16 |
| access([tbl_r.log_date], [tbl_r.log_id], [tbl_r.log_value]), partitions(p[6-9]) |
| is_index_back=false, is_global_index=false, filter_before_indexback[false,false], |
| range_key([tbl_r.__pk_increment]), range(MIN ; MAX)always true |
+-----------------------------------------------------------------------------------------------------------------------------------------------------------+
20 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> 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
一部のシナリオでは、パーティションプルーニングによりある程度の拡大が発生する可能性がありますが、オプティマイザーはプルーニングの結果がアクセス対象データの超集合であることを保証し、データの欠落は発生しません。