MySQL データベーステーブルのパーティション分割に関する考慮事項 [推奨]

テーブルパーティショニングは、データベースパーティショニングとは異なります。では、テーブルパーティショニングを使用する場合、どのような点に注意する必要がありますか? 今日は、MySQL データベーステーブルパーティショニングの詳細を見ていきます。

1. パーティション列インデックス制約

テーブルに主キーまたは一意キーがある場合、パーティションテーブルのパーティション列は主キーまたは一意キーリストに含まれている必要があります。これは主キーの効率を確保するためです。そうしないと、同じ主キー領域内の項目がそれぞれパーティション A とパーティション B にある場合に問題が発生します。

2. 各パーティションタイプの条件

範囲各パーティションには、パーティション式の値が指定された連続した間隔内にある行が含まれます。これらの間隔は連続している必要があり、重複することはできません。

リストは整数フィールドまたは整数を返す式のみをサポートします。各パーティションリストの値リストは整数である必要があります。

ハッシュタイプは、整数フィールドまたは整数を返す式のみをサポートします。

キータイプは列名（1つ以上の列名）のみをサポートし、式はサポートしません。

3. 利用可能な機能を分割する

腹筋()

CEILING() (このリストの直後の CEILING() と FLOOR() を参照)

日（）

月曜()

曜日()

DAYOFYEAR()

日付差分()

抽出する（）

FLOOR() (このリストの直後の CEILING() と FLOOR() を参照)

時間（）

マイクロ秒()

分（）

MOD()

月（）

四半期（）

2番目（）

TIME_TO_SEC()

TO_DAYS()

平日（）

年（）

週()

知らせ：

パーティション関数には FROM_UNIXTIME 関数が含まれていないため、タイムスタンプを時間に変換してパーティション分割することはできません。日付または日時でのみパーティション分割できます。

たとえば、年ごとに次のように使用できます。

範囲によるパーティション (YEAR(日付))

月別:

範囲によるパーティション分割(日付 div 100)

#div は日付を整数に変換します。例: 2014-12-01 -> 20141201、100 は末尾から 2 桁を削除し、最終結果は 201412 になります。

順序によるパーティション分割の例:

テーブル「order」を作成します（
 `order_id` bigint(19) NOT NULL DEFAULT '0' COMMENT '注文ID: 年、月、日、時、分、秒、12桁、7桁の乱数',
 `date` 日付 NOT NULL デフォルト '0000-00-00' コメント '注文日',
 `amount` int(11) DEFAULT NULL COMMENT '支払い金額（セント単位）',
 `status` tinyint(1) デフォルト '0' コメント '0: 支払い待ち 1: 支払い成功 2: 支払い失敗 3: 検証失敗',
 `addtime` int(10) DEFAULT NULL COMMENT '注文追加時間',
 主キー (`order_id`,`date`)
)ENGINE=MyISAM デフォルト文字セット=utf8;

タイムスタンプを使用して時間でパーティション分割することはできないため、日付フィールドを追加します。このフィールドと order_id は主キーとして使用されます。パーティション列は主キーに含める必要があることはわかっています。次に日付を使って年と月の組み合わせを計算し、分割します。

ALTER TABLE order PARTITION BY RANGE( date DIV 100)
（
  パーティション p_2014_06 値が (201407) より小さい、
  パーティション p_2014_07 値が (201408) より小さい、
  パーティション p_2014_08 の値は (201409) より小さいです。
  パーティション p_2014_09 値が (201410) より小さい
  パーティション p_2014_10 の値が (201411) より小さい、
  パーティション p_catch_all 値が MAXVALUE 未満です
);

上記では、LESS THAN MAXVALUE は最後のパーティション p_catch_all を設定するため、add メソッドを使用してパーティションを追加することはできません。次のステートメントは使用できません。
ALTER TABLE order ADD PARTITION (PARTITION p_2014_11 VALUES LESS THAN (201412));

最後の p_catch_all パーティションのみを 2 つに分割できます。これには、p_catch_all パーティション内のデータが失われないという利点があります。データのマージと分割は、REORGANIZE PARTITION を使用して実行されます。

テーブルの順序を変更し、パーティションp_catch_allを再編成します。 
（
  パーティションp_2014_11の値は（201412）より小さい、
  パーティションp_catch_allの値がmaxvalueより小さい
);

パーティションを結合:

テーブルの順序を変更し、パーティションp_2014_10、p_2014_11、p_catch_allを再編成します。 
（
  パーティションp_catch_testの値がMAXVALUE未満
);

それを p_catch_all に割り当ててみませんか?パーティションが存在すると報告されるからです。

マージ時に最後のパーティション p_catch_all を含める必要があるのはなぜですか?最後のパーティションを除いて、再編成されたパーティション範囲では合計範囲を変更できないためです。
パーティションを削除しますが、データは削除しません。

テーブルを変更するテーブル名パーティションを削除する

注: 上記のステートメントは 5.5 では実行できますが、5.6 では問題があるようです。まずテストする必要があります。

パーティション分割後、where 条件が範囲の場合、パーティション分割は機能しません。たとえば、where date >= '2014-01-01' And date <= '2014-01-31' などです。
条件 where date = '2014-01-01' または where date in ('2014-01-01', '2014-01-02', '2014-01-03'...) では = またはを使用する必要があります。

補足: MySQL テーブルの 4 つのパーティションタイプ

1. テーブルパーティショニングとは

一般的に、テーブルパーティショニングとは、条件に応じて大きなテーブルを複数の小さなテーブルに分割することです。 MySQL 5.1 ではデータテーブルのパーティショニングのサポートが開始されました。たとえば、ユーザーテーブルに 600 万件を超えるレコードがある場合、テーブルは保存日または場所に応じてパーティション分割できます。もちろん、他の条件に基づいてパーティショニングを行うこともできます。

2. テーブルをパーティション分割する理由は何ですか?

大規模なテーブルやアクセスパターンが異なるテーブルのスケーラビリティと管理性を向上させ、データベースの効率を高めます。

パーティション分割の利点は次のとおりです。

単一のディスクまたはファイルシステムパーティションよりも多くのデータを保存できます。

保存の意味を失ったデータについては、通常、そのデータに関連するパーティションを削除することで簡単にデータを削除することができます。逆に、場合によっては、新しいデータ専用の新しいパーティションを追加することで、新しいデータを追加するプロセスを簡単に実装できます。パーティショニングに一般的に関連付けられるその他の利点としては、以下に示すものが挙げられます。 MySQL Partitioning のこれらの機能はまだ実装されていませんが、優先度の高い機能です。5.1 の製品バージョンに組み込む予定です。

一部のクエリは、主に、指定された WHERE 句を満たすデータを 1 つ以上のパーティションに保存することで大幅に最適化できます。これにより、検索で残りの他のパーティションを検索する必要がなくなります。パーティションテーブルの作成後にパーティションを変更できるため、パーティションスキームを最初に構成したときにデータを再編成しなかった場合は、データを再編成して、よく使用されるクエリの効率を向上させることができます。

SUM() や COUNT() などの集計関数を含むクエリは簡単に並列化できます。このようなクエリの簡単な例は、「SELECT salesperson_id, COUNT (orders) as order_total FROM sales GROUP BY salesperson_id;」です。「並列」とは、クエリを各パーティションで同時に実行でき、最終結果はすべてのパーティションの結果を合計するだけで得られることを意味します。

データクエリを複数のディスクに分散することで、クエリスループットを向上させることができます。

3. パーティションタイプ

RANGE パーティション分割: 指定された連続間隔内にある列の値に基づいて、複数の行をパーティションに割り当てます。

LIST パーティション分割: RANGE パーティション分割と似ていますが、違いは、LIST パーティション分割は離散値セット内の値と一致する列値に基づいている点です。

HASH パーティション分割: テーブルに挿入される行の列値を使用して計算されるユーザー定義式の戻り値に基づいてパーティションが選択されます。この関数には、負でない整数値を生成する、MySQL で有効な任意の式を含めることができます。

KEY パーティショニング: HASH パーティショニングと似ていますが、KEY パーティショニングでは 1 つ以上の列の計算のみがサポートされ、MySQL サーバーは独自のハッシュ関数を提供するという点が異なります。 1 つ以上の列に整数値が含まれている必要があります。

RANGE パーティション分割

指定された連続した間隔内にある列の値に基づいて、複数の行をパーティションに割り当てます。

これらの間隔は連続していて重複していない必要があり、VALUES LESS THAN 演算子を使用して定義されます。以下に例を挙げます。

SQLコード:

従業員テーブルを作成する（
  id INT NOT NULL、
  fname VARCHAR(30)、
  lname VARCHAR(30)、
  雇用日 NULL ではない デフォルト '1970-01-01',
  区切られた日付 NULL ではない デフォルト '9999-12-31'、
  ジョブコード INT NOT NULL、
  store_id INT NULLではない
）
パーティション BY RANGE (store_id) (
  パーティションp0の値が（6）未満である、
  パーティションp1の値が(11)未満である、
  パーティションp2の値が(16)未満である、
  パーティション p3 値が (21) 未満
);

このパーティション分割スキームによれば、店舗 1 ～ 5 で働く従業員に対応するすべての行はパーティション P0 に格納され、店舗 6 ～ 10 で働く従業員は P1 に格納されます。各パーティションは、最低から最高の順に定義されていることに注意してください。これは PARTITION BY RANGE 構文の要件であり、この点では C または Java の「switch ... case」ステートメントに似ています。データ (72、'Michael'、'Widenius'、'1998-06-25′、NULL、13) を含む新しい行の場合、それが p2 パーティションに挿入されることは簡単に判断できますが、番号 21 のストアが追加された場合はどうなるでしょうか。このシナリオでは、store_id が 20 より大きいストアを含めるルールがないため、サーバーは行を保存する場所を認識できず、エラーが発生します。このエラーを回避するには、CREATE TABLE ステートメントで「catchall」VALUES LESS THAN 句を使用します。この句は、明示的に指定した最大値より大きいすべての値をキャッチします。

SQLコード:

従業員テーブルを作成する（
  id INT NOT NULL、
  fname VARCHAR(30)、
  lname VARCHAR(30)、
  雇用日 NULL ではない デフォルト '1970-01-01',
  区切られた日付 NULL ではない デフォルト '9999-12-31'、
  ジョブコード INT NOT NULL、
  store_id INT NULLではない
）
範囲によるパーティション (store_id) (
  パーティションp0の値が（6）未満の場合、
  パーティションp1の値が(11)未満である、
  パーティション p2 値が (16) 未満の場合、
  パーティション p3 の値が MAXVALUE 未満です
);

MAXVALUE は可能な最大の整数値を表します。これで、store_id 列の値が 16 (定義されている最大値) 以上であるすべての行がパーティション p3 に格納されます。将来、店舗数が 25、30 などに増えた場合は、ALTER TABLE ステートメントを使用して、店舗 21 ～ 25、26 ～ 30 などに新しいパーティションを追加できます。ほぼ同じ構造で、従業員のジョブコード、つまり job_code 列の値の連続した間隔に基づいてテーブルをパーティション分割することもできます。たとえば、2 桁のジョブコードが一般 (店舗) 従業員を表し、3 桁のコードがオフィスおよびサポートスタッフを表し、4 桁のコードが管理者を表すと仮定すると、次のステートメントを使用してパーティションテーブルを作成できます。

SQLコード:

従業員テーブルを作成する（
  id INT NOT NULL、
  fname VARCHAR(30)、
  lname VARCHAR(30)、
  雇用日 NULL ではない デフォルト '1970-01-01',
  区切られた日付 NULL ではない デフォルト '9999-12-31'、
  ジョブコード INT NOT NULL、
  store_id INT NULLではない
）
範囲によるパーティション (job_code) (
  パーティション p0 値が (100) 未満
  パーティション p1 の値が (1000) 未満の場合、
  パーティション p2 の値が (10000) 未満
);

この例では、店舗従業員に関連するすべての行はパーティション p0 に格納され、オフィスおよびサポートスタッフに関連するすべての行はパーティション p1 に格納され、管理に関連するすべての行はパーティション p2 に格納されます。 VALUES LESS THAN 句で式を使用することもできます。ここで最も注目すべき制限は、MySQL が LESS THAN (<) 比較の一部として返される式を評価できる必要があることです。したがって、式は NULL に評価できません。このため、従業員テーブルの hired、separated、job_code、store_id 列は NOT NULL として定義されています。店舗番号に基づいてテーブルデータを分割できることに加えて、2 つの DATE (日付) のいずれかに基づく式を使用してテーブルデータを分割することもできます。たとえば、各従業員が会社を退職した年、つまり YEAR(separated) の値に基づいてテーブルをパーティション分割するとします。このパーティション分割スキームを実装する CREATE TABLE ステートメントの例を次に示します。

SQLコード:

従業員テーブルを作成する（
  id INT NOT NULL、
  fname VARCHAR(30)、
  lname VARCHAR(30)、
  雇用日 NULL ではない デフォルト '1970-01-01',
  区切られた日付 NULL ではない デフォルト '9999-12-31'、
  ジョブコード INT、
  ストアID INT
）
範囲によるパーティション（年（区切り））（
  パーティション p0 値が小さい (1991)、
  パーティション p1 値が (1996) 未満の場合、
  パーティション p2 値が (2001) 未満
  パーティション p3 の値が MAXVALUE 未満です
);

このスキームでは、1991 年以前に雇用されたすべての従業員の記録はパーティション p0 に格納され、1991 年から 1995 年の間に雇用されたすべての従業員の記録はパーティション p1 に格納され、1996 年から 2000 年の間に雇用されたすべての従業員の記録はパーティション p2 に格納され、2000 年以降に雇用されたすべての従業員に関する情報はパーティション p3 に格納されます。
RANGE パーティション分割は、次のような状況で特に役立ちます。1) パーティション上の「古い」データを削除する必要がある場合は、パーティションを削除するだけです。上記の最新の例に示されているパーティションスキームを使用する場合は、単に「ALTER TABLE employees DROP PARTITION p0;」を使用するだけで、1991 年以前に仕事を辞めた従業員に対応するすべての行を削除できます。行数が多いテーブルの場合、これは「DELETE FROM employees WHERE YEAR (separated) <= 1990;」などの DELETE クエリを実行するよりもはるかに効率的です。 2) 日付や時刻の値、または他の系列から増加する値を含む列を使用したい場合。 3) テーブルをパーティション分割するために使用される列に直接依存するクエリを頻繁に実行します。たとえば、「SELECT COUNT(*) FROM employees WHERE YEAR(separated) = 2000 GROUP BY store_id;」などのクエリを実行すると、MySQL は、残りのパーティションには WHERE 句を満たすレコードを含めることができないため、パーティション p2 のみをスキャンする必要があることをすぐに判断できます。

注: この最適化は MySQL 5.1 のソースコードではまだ有効になっていませんが、作業は進行中です。

リストパーティション

RANGE によるパーティション分割と似ていますが、違いは、LIST パーティション分割は、個別の値セット内の値と一致する列値に基づいている点です。

LIST パーティション分割は、「PARTITION BY LIST(expr)」を使用して実装されます。ここで、「expr」は列値、または整数値を返す列値に基づく式です。次に、「VALUES IN (value_list)」によって各パーティションを定義します。ここで、「value_list」はコンマで区切られた整数のリストです。注意: MySQL 5.1 以降では、LIST パーティショニングを使用する場合、整数のリストのみを一致させることができます。

SQLコード:

従業員テーブルを作成する（
  id INT NOT NULL、
  fname VARCHAR(30)、
  lname VARCHAR(30)、
  雇用日 NULL ではない デフォルト '1970-01-01',
  区切られた日付 NULL ではない デフォルト '9999-12-31'、
  ジョブコード INT、
  ストアID INT
);

次の表に示すように、配信権を持つ 4 つの地域に 20 のオーディオビジュアルストアが分散していると仮定します。

====================
地域店舗ID番号 北地区 3、5、6、9、17
東地区1、2、10、11、19、20
西地区4、12、13、14、18
中央地区7、8、15、16
====================

同じリージョン内のストアに属する行が同じパーティションに格納されるようにテーブルをパーティション分割するには、次の「CREATE TABLE」ステートメントを使用できます。

SQLコード:

従業員テーブルを作成する（
  id INT NOT NULL、
  fname VARCHAR(30)、
  lname VARCHAR(30)、
  雇用日 NULL ではない デフォルト '1970-01-01',
  区切られた日付 NULL ではない デフォルト '9999-12-31'、
  ジョブコード INT、
  ストアID INT
）
リストによるパーティション(store_id)
  パーティション pNorth 値 (3,5,6,9,17)、
  パーティション pEast 値 (1,2,10,11,19,20)、
  パーティション pWest 値 (4,12,13,14,18)、
  パーティション pCentral 値 (7,8,15,16)
);

これにより、テーブル内の特定の地域の従業員レコードを簡単に追加または削除できるようになります。たとえば、ウエストサイドにあるビデオ店がすべて他の企業に売却されたとします。次に、クエリ「ALTER TABLE employees DROP PARTITION pWest;」を使用して、West District ビデオストアで働く従業員に関連するすべてのレコード (行) を削除できます。これは、同じ効果を持つ DELETE クエリ「DELETE query DELETE FROM employees WHERE store_id IN (4,12,13,14,18);」よりもはるかに効率的です。 [重要なポイント]: 列値 (またはパーティション式の戻り値) がパーティション値リストにない行を挿入しようとすると、「INSERT」クエリは失敗し、エラーが報告されます。たとえば、上記のスキームが LIST パーティション分割に使用されていると仮定すると、次のクエリは失敗します。

SQLコード:

従業員に VALUES(224, 'Linus', 'Torvalds', '2002-05-01', '2004-10-12', 42, 21) を挿入します。

これは、パーティション pNorth、pEast、pWest、または pCentral を定義するために使用される値のリストに「store_id」列の値 21 が見つからないためです。 LIST パーティションには、他の値を含む「VALUES LESS THAN MAXVALUE」などの定義がないことに注意することが重要です。一致する値は値のリスト内に見つかる必要があります。
LIST パーティションと RANGE パーティションを組み合わせて複合サブパーティションを生成できるだけでなく、LIST パーティションと HASH パーティションおよび KEY パーティションを組み合わせて複合サブパーティションを生成することもできます。

ハッシュパーティション

パーティションは、テーブルに挿入される行の列値を使用して評価されるユーザー定義式の戻り値に基づいて選択されます。この関数には、負でない整数値を生成する、MySQL で有効な任意の式を含めることができます。

HASH パーティション分割を使用してテーブルをパーティション分割するには、CREATE TABLE ステートメントに「PARTITION BY HASH (expr)」句を追加します。ここで、「expr」は整数を返す式です。フィールドタイプが MySQL 整数である列の名前にすることもできます。さらに、その後に「PARTITIONS num」句を追加する必要がある可能性が高くなります。ここで、num は、テーブルが分割されるパーティションの数を表す負でない整数です。

SQLコード:

従業員テーブルを作成する（
  id INT NOT NULL、
  fname VARCHAR(30)、
  lname VARCHAR(30)、
  雇用日 NULL ではない デフォルト '1970-01-01',
  区切られた日付 NULL ではない デフォルト '9999-12-31'、
  ジョブコード INT、
  ストアID INT
）
ハッシュによるパーティション(store_id)
パーティション4;

PARTITIONS 句を含めない場合、パーティションの数はデフォルトで 1 になります。例外: NDB Cluster テーブルの場合、デフォルトのパーティション数はクラスターデータノードの数と同じになります。この修正では、すべての行がパーティションに収まるようにするために、MAX_ROWS 設定が考慮される可能性があります。

ライナーハッシュ

MySQL は線形ハッシュもサポートしています。線形ハッシュは 2 の累乗の線形アルゴリズムを使用するのに対し、通常のハッシュはハッシュ関数値の係数を使用するという点で、通常のハッシュとは異なります。線形ハッシュパーティション分割と通常のハッシュパーティション分割の構文の唯一の違いは、「PARTITION BY」句に「LINEAR」キーワードが追加されていることです。

SQLコード:

従業員テーブルを作成する（
  id INT NOT NULL、
  fname VARCHAR(30)、
  lname VARCHAR(30)、
  雇用日 NULL ではない デフォルト '1970-01-01',
  区切られた日付 NULL ではない デフォルト '9999-12-31'、
  ジョブコード INT、
  ストアID INT
）
線形ハッシュによるパーティション(YEAR(採用))
パーティション4;

式 expr があるとします。線形ハッシュ関数を使用すると、レコードが保存されるパーティションは num パーティションのパーティション N になります。ここで、N は次のアルゴリズムに従って取得されます。1. num より大きい次の 2 の累乗を見つけます。この値を V と呼びます。これは次の式で取得できます。2. V = POWER(2, CEILING(LOG(2, num))) (たとえば、num が 13 であるとします。この場合、LOG(2,13) は 3.7004397181411 です。CEILING(3.7004397181411) は 4 なので、V = POWER(2,4) となり、16 に等しくなります)。 3. N = F(column_list) & (V – 1) と設定します。 4. N >= num の場合: V = CEIL(V / 2) と設定します。 N = N & (V – 1) と設定します。たとえば、線形ハッシュパーティション分割を使用し、4 つのパーティションを持つテーブル t1 が、次のステートメントによって作成されたとします。 CREATE TABLE t1 (col1 INT, col2 CHAR(5), col3 DATE) PARTITION BY LINEAR HASH( YEAR(col3) ) PARTITIONS 6; ここで、テーブル t1 に、col3 列の値が '2003-04-14' であるレコードと、col3 列の値が '1998-10-19' であるレコードの 2 つの行を挿入するとします。最初のレコードが保存されるパーティションは、次のように決定されます：V = Power（2、天井（2,7））= 8 n =年（ '2003-04-14′）= 2003＆7（3> = 6は偽りです：記録はパーティションで保存されます＃3）（8-1）= 1998＆7 = 6（6> = 4は真：追加の手順が必要です）n = 6＆天井（5 /2）= 6＆3 = 2（2> = 4はfalse：パーティション＃2でレコードが保存されます＃2）欠点は、従来の HASH パーティション分割を使用して得られるデータ分散と比較して、パーティション間のデータ分散がバランスが取れにくいことです。

KEYパーティション

HASH パーティショニングと同様ですが、KEY パーティショニングでは 1 つ以上の列の計算のみがサポートされ、MySQL サーバーが独自のハッシュ関数を提供する点が異なります。 1 つ以上の列に整数値が含まれている必要があります。

SQLコード:

テーブルtkを作成します（
  col1 INT NOT NULL、
  列2 CHAR(5)、
  col3 日付
）
線形キーによるパーティション (col1)
パーティション3;

KEY パーティション分割でキーワード LINEAR を使用すると、HASH パーティション分割で使用した場合と同じ効果があります。パーティション番号は、モジュラスアルゴリズムではなく、2 の累乗アルゴリズムによって取得されます。

要約する

以上が、MySQL データベーステーブルのパーティショニングに関する注意事項に関するこの記事の内容です。興味のある方は、MySQL テーブルデータの削除方法、いくつかの重要な MySQL 変数、MYSQL サブクエリとネストされたクエリの最適化例の分析などを参照してください。皆様のお役に立てれば幸いです。ご質問がありましたら、メッセージを残してください。どなたでもコミュニケーションや議論を歓迎いたします。

以下もご興味があるかもしれません: