OceanBaseにおけるパーティションテーブルの管理と最適化技術

パーティションの管理操作

パーティションテーブルでは、データ量の増加に応じて柔軟な管理が必要です。

◼ パーティションの追加
⚫ データ量が増大するにつれて、範囲パーティションは拡張可能である必要があります -> ADD PARTITION
⚫ 構文: ALTER TABLE ... ADD PARTITION
ALTER TABLE users ADD PARTITION(PARTITION p4 VALUES LESS THAN(3000));

◼ パーティションの削除
⚫ 時間範囲でパーティション分割されたテーブルでは、期限切れデータのクリーンアップが必要な場合があります -> DROP PARTITION
⚫ 構文: ALTER TABLE ... DROP PARTITION
ALTER TABLE users DROP PARTITION(P4);

◼ 使用上の制約
⚫ 範囲パーティションのみ、任意の一次範囲パーティションを削除できます
⚫ パーティションはappend方式のみで後ろに追加でき、新しく追加するパーティションの範囲値は常に最大値でなければなりません

パーティション選択とパーティションプルーニング

テーブル構造とクエリ例を通じて、パーティション選択とプルーニングの動作を確認します。

テーブル構造
create table employee_data(emp_id int primary key, dept_id int, salary int) partition by hash(emp_id) partitions 7;
--パーティション選択
explain select * from employee_data partition(p5);
| ===================================
|ID|OPERATOR     |NAME        |EST. ROWS|COST|
-----------------------------------
|0 |TABLE SCAN   |employee_data|1000 |2456|
===================================
出力 & フィルター: 
-------------------------------------
0 - output([employee_data.emp_id], [employee_data.dept_id], [employee_data.salary]), 
filter(nil), 
access([employee_data.emp_id], [employee_data.dept_id], [employee_data.salary]), 
partitions(p5)


--パーティションプルーニング
explain select * from employee_data where emp_id = 8 or emp_id = 3;
| ============================================
|ID|OPERATOR           |NAME        |EST. ROWS|COST|
--------------------------------------------
|0 |EXCHANGE IN DISTR  |            |4 |238 |
|1 | EXCHANGE OUT DISTR|            |4 |135 |
|2 |  TABLE GET        |employee_data|4 |135 |
============================================
出力 & フィルター:
-------------------------------------
0 - output([employee_data.emp_id], [employee_data.dept_id], [employee_data.salary]), filter(nil)
1 - output([employee_data.emp_id], [employee_data.dept_id], [employee_data.salary]), filter(nil)
2 - output([employee_data.emp_id], [employee_data.dept_id], [employee_data.salary]), filter(nil),
access([employee_data.emp_id], [employee_data.dept_id], [employee_data.salary]), 
partitions(p1, p3)

一次パーティションプルーニングの基本原理 - Hash/Listパーティション

パーティションプルーニングは、WHERE句の条件からパーティション列の値を計算し、どのパーティションにアクセスする必要があるかを判断するプロセスです。

パーティション関数が式であり、その式が等価条件全体として現れる場合もパーティションプルーニングが可能です
obclient> CREATE TABLE sales_data (product_id INT,region_id INT)PARTITION BY HASH(product_id + region_id) partitions 7;
obclient> EXPLAIN SELECT * FROM sales_data WHERE product_id + region_id = 3 \G
*************************** 1. row ***************************
クエリプラン: ===================================
|ID|OPERATOR |NAME        |EST. ROWS|COST|
-----------------------------------
|0 |TABLE SCAN|sales_data  |5 |1387|
===================================
出力 & フィルター:
-------------------------------------
0 - output([sales_data.product_id], [sales_data.region_id]), filter([sales_data.product_id + sales_data.region_id = 3]),
access([sales_data.product_id], [sales_data.region_id]), partitions(p3)


HASH(product_id + region_id):全体が等価条件に現れることでパーティションプルーニングが可能

一次パーティションプルーニングの基本原理 - Rangeパーティション

範囲パーティションの場合、WHERE句のパーティションキーの範囲とテーブル定義のパーティション範囲の交差部分によってアクセスするパーティションが決定されます。

CREATE TABLE orders(order_id INT,customer_id INT) PARTITION BY RANGE(order_id + 10)
(PARTITION p0 VALUES less than (500),PARTITION p1 VALUES less than (1000));

obclient> EXPLAIN SELECT * FROM orders WHERE order_id < 750 and  --非等価条件ではパーティションプルーニングが不可
order_id > 550 \G
クエリプラン: 
============================================
|ID|OPERATOR           |NAME    |EST. ROWS|COST|
--------------------------------------------
|0 |EXCHANGE IN DISTR  |        |19 |1498|
|1 | EXCHANGE OUT DISTR|        |19 |1387|
|2 |  TABLE SCAN       |orders  |19 |1387|
============================================
出力 & フィルター:
-------------------------------------
0 - output([orders.order_id], [orders.customer_id]), filter(nil)
1 - output([orders.order_id], [orders.customer_id]), filter(nil)
2 - output([orders.order_id], [orders.customer_id]), filter([orders.order_id < 750], 
[orders.order_id > 550]),
access([orders.order_id], [orders.customer_id]), partitions(p[0-1])

obclient> EXPLAIN SELECT * FROM orders WHERE order_id = 750 \G --等価条件ではパーティションプルーニングが可能

*************************** 1. row 
**********************
クエリプラン: ===================================
|ID|OPERATOR |NAME    |EST. ROWS|COST|
-----------------------------------
|0 |TABLE SCAN|orders  |1 |1387|
===================================
出力 & フィルター:
-------------------------------------
0 - output([orders.order_id], [orders.customer_id]), filter([orders.order_id = 750]),
access([orders.order_id], [orders.customer_id]), partitions(p1)

二次パーティションプルーニングの基本原理

二次パーティションの場合、まず一次パーティションキーでアクセスする一次パーティションを決定し、次に二次パーティションキーでアクセスする二次パーティションを決定します。その後、積集合を取ることでアクセスするすべての物理パーティションを決定します。

CREATE TABLE inventory(item_id INT ,warehouse_id INT) 
PARTITION BY hash(item_id) 
SUBPARTITION BY RANGE(warehouse_id) 
SUBPARTITION template (
    SUBPARTITION sp0 VALUES less than(50),
    SUBPARTITION sp1 VALUES less than(100)
) 
partitions 7

obclient> EXPLAIN SELECT * FROM inventory 
WHERE (item_id = 3 or item_id = 5) and (warehouse_id > 55 and warehouse_id < 85) \G
クエリプラン: ============================================
|ID|OPERATOR           |NAME      |EST. ROWS|COST|
--------------------------------------------
|0 |EXCHANGE IN DISTR   |          |1 |1487|
|1 | EXCHANGE OUT DISTR |          |1 |1387|
|2 |  TABLE SCAN        |inventory |1 |1387|
============================================
出力 & フィルター:
-------------------------------------
0 - output([inventory.item_id], [inventory.warehouse_id]), filter(nil)
1 - output([inventory.item_id], [inventory.warehouse_id]), filter(nil)
2 - output([inventory.item_id], [inventory.warehouse_id]), 
filter([inventory.item_id = 3 OR inventory.item_id = 5], 
[inventory.warehouse_id > 55], [inventory.warehouse_id < 85]),
access([inventory.item_id], [inventory.warehouse_id]), 
partitions(p3sp1, p5sp1)


(item_id = 3 or item_id = 5)--一次パーティションで2つの範囲にアクセス
(warehouse_id > 55 and warehouse_id < 85)--二次パーティションで1つの範囲にアクセス
partitions(p3sp1, p5sp1)--組み合わせによりアクセスするすべての物理パーティションを決定

パーティションテーブルの使用に関する推奨事項

パーティションテーブルを効果的に使用するためのガイドライン:

ビジネス要件に基づいたパーティション設計（ホットデータの分散、履歴データのメンテナンス性、ビジネスSQLの条件形式（パーティションプルーニング））
OceanBaseの各種パーティションタイプの設定要件の理解
パーティションキーは主キーのサブセットでなければならない
範囲パーティションでは、最後のパーティションにmaxvalueを使用できない
パーティションプルーニング、partition wise join最適化を考慮する
Leader bindingとTablegroupの活用
書き込み増幅問題を避けるため、テーブルのカスタム主キーを選択する際はランダムに生成された値ではなく、時系列で増加するような順序のある値を使用する
パーティション数の適切な設定：単一サーバーのパーティション上限、単一サーバーのテナントが作成できる最大パーティション数上限、単一テーブルのパーティション数上限を考慮する

タグ: OceanBase パーティションテーブルデータベース管理パーティションプルーニング Hashパーティション

7月21日 23:05 投稿

異端開発室