パーティションプルーニング機能を使用すると、関係のないパーティションへのアクセスを回避でき、SQLの実行効率が大幅に向上します。本記事では、パーティションプルーニングの原理と応用について主に説明します。
ユーザーがパーティションテーブルにアクセスする際、通常はその一部のパーティションのみにアクセスする必要があります。オプティマイザーが関係のないパーティションへのアクセスを回避する最適化プロセスをパーティションプルーニング（Partition Pruning）と呼びます。パーティションプルーニングは、パーティションテーブルが提供する重要な最適化手法であり、パーティションプルーニングにより、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 \G
*************************** 1. row ***************************
Query Plan: ===================================
|ID|OPERATOR |NAME|EST. ROWS|COST|
-----------------------------------
|0 |TABLE SCAN|TBL1|990 |383 |
===================================
Outputs & filters:
-------------------------------------
0 - output([TBL1.COL1], [TBL1.COL2]), filter(nil),
access([TBL1.COL1], [TBL1.COL2]), partitions(p1)
1 row in set
パーティションクリッピングの基本原理
Hash/リストパーティション
パーティションクリッピングとは、where 句の条件に基づいてパーティション列の値を計算し、その結果によってアクセスする必要があるパーティションを判断することです。パーティション条件が式であり、かつその式が等価条件として全体として現れる場合でも、パーティションクリッピングを行うことができます。
パーティション条件が式 c1 + c2 であり、かつその式が等価条件として全体として現れるパーティションクリッピングの例は以下のとおりです：
obclient> CREATE TABLE t1(c1 INT,c2 INT) PARTITION BY HASH(c1 + c2) PARTITIONS 5;
obclient> EXPLAIN SELECT * FROM t1 WHERE c1 + c2 = 1 \G
*************************** 1. row ***************************
Query Plan: ===================================
|ID|OPERATOR |NAME|EST. ROWS|COST|
-----------------------------------
|0 |TABLE SCAN|t1 |5 |1303|
===================================
Outputs & filters:
-------------------------------------
0 - output([t1.c1], [t1.c2]), filter([t1.c1 + t1.c2 = 1]),
access([t1.c1], [t1.c2]), partitions(p1)
Rangeパーティション
where 句のパーティションキーの範囲とテーブル定義のパーティション範囲の共通部分に基づいて、アクセスする必要があるパーティションを決定できます。
Rangeパーティションについて：
パーティション条件が列の場合、クエリ条件が等値条件であっても不等値条件であっても、パーティションカットがサポートされます。
パーティション条件が式であり、かつクエリ条件が等値条件の場合、パーティションカットがサポートされます。
たとえば、パーティション条件が式 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)
);
Query OK, 0 rows affected
obclient> EXPLAIN SELECT * FROM t1 WHERE c1 < 150 and c1 > 110\G
*************************** 1. row ***************************
Query Plan: ===================================
|ID|OPERATOR |NAME|EST. ROWS|COST|
-----------------------------------
|0 |TABLE SCAN|t1 |1 |46 |
===================================
Outputs & filters:
-------------------------------------
0 - output([t1.c1], [t1.c2]), filter([t1.c1 < 150], [t1.c1 > 110]),
access([t1.c1], [t1.c2]), partitions(p1)
1 row 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'))
);
Query OK, 0 rows affected
obclient> EXPLAIN SELECT * FROM tbl_r WHERE log_date > '2020/07/15' and log_date <'2020/10/07'\G
*************************** 1. row ***************************
Query Plan: ====================================================
|ID|OPERATOR |NAME |EST. ROWS|COST|
----------------------------------------------------
|0 |PX COORDINATOR | |1 |183 |
|1 | EXCHANGE OUT DISTR |:EX10000|1 |183 |
|2 | PX PARTITION ITERATOR| |1 |183 |
|3 | TABLE SCAN |tbl_r |1 |183 |
====================================================
Outputs & filters:
-------------------------------------
0 - output([INTERNAL_FUNCTION(tbl_r.log_id, tbl_r.log_value, tbl_r.log_date)]), filter(nil)
1 - output([INTERNAL_FUNCTION(tbl_r.log_id, tbl_r.log_value, tbl_r.log_date)]), filter(nil), dop=1
2 - output([tbl_r.log_date], [tbl_r.log_id], [tbl_r.log_value]), filter(nil)
3 - output([tbl_r.log_date], [tbl_r.log_id], [tbl_r.log_value]), filter([tbl_r.log_date > ?], [tbl_r.log_date < ?]),
access([tbl_r.log_date], [tbl_r.log_id], [tbl_r.log_value]), partitions(p[6-9])
1 row 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)
) ;
Query OK, 0 rows affected
obclient> EXPLAIN SELECT * FROM tbl2_rr
WHERE (col1 = 1 or col1 = 2) and (col2 > 101 and col2 < 150) \G
*************************** 1. row ***************************
Query Plan: ======================================
|ID|OPERATOR |NAME |EST. ROWS|COST|
--------------------------------------
|0 |TABLE SCAN|TBL2_RR|99 |53 |
======================================
Outputs & filters:
-------------------------------------
0 - output([TBL2_RR.COL1], [TBL2_RR.COL2]), filter(nil),
access([TBL2_RR.COL1], [TBL2_RR.COL2]), partitions(p0sp0)
1 row in set
一部のシナリオでは、パーティションクリッピングによってある程度の拡大が生じる可能性がありますが、オプティマイザーはクリッピングの結果が必要なデータの超集合であり、データが失われることはないように保証します。