例を通してMySQLパーティションテーブルの原理と一般的な操作を学びます

1. パーティションテーブルの意味

パーティションテーブル定義は、任意のサイズに設定できるルールに従って、ファイルシステム全体に単一のテーブルの複数の部分を割り当てます。実際、テーブルのさまざまな部分は、異なる場所に個別のテーブルとして保存されます。データのセグメント化を実現するためにユーザーが選択したルールはパーティション関数と呼ばれ、MySQL では係数、連続した数値範囲または数値リストの単純な一致、内部 HASH 関数、または線形 HASH 関数になります。

テーブルシャーディングとパーティション分割の違いは、論理的にはパーティション内に 1 つのテーブルしかないのに対し、テーブルシャーディングでは 1 つのテーブルが複数のテーブルに分解される点です。

2. パーティションテーブルの利点

1) パーティションテーブルの保守が容易になります。保存の意味を失ったデータについては、通常、そのデータに関連するパーティションを削除することで簡単にデータを削除することができます。逆に、場合によっては、新しいデータ専用の新しいパーティションを追加することで、新しいデータを追加するプロセスを簡単に実装できます。

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

3) クエリを最適化します。たとえば、SUM() と COUNT() は複数のパーティションで並列に処理でき、最終結果はすべてのパーティションの結果の合計になります。

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

3. パーティションテーブルの制限

1) テーブルには最大 1024 個のパーティションを含めることができます。

2) MySQL 5.1 では、パーティショニング式は整数または整数を返す式である必要があります。 MySQL 5.5 では、非整数式のパーティショニングがサポートされています。

3) パーティションフィールドに主キー列または一意のインデックス列がある場合は、すべての主キー列と一意のインデックス列を含める必要があります。つまり、パーティションフィールドに主キーまたはインデックス列が含まれていないか、主キーとインデックス列がすべて含まれています。

4) パーティションテーブルでは外部キー制約は使用できません。

5) MySQL のパーティショニングは、テーブルのすべてのデータとインデックスに適用されます。インデックスをパーティショニングせずにテーブルデータのみをパーティショニングしたり、テーブルをパーティショニングせずにインデックスのみをパーティショニングしたり、テーブルデータの一部のみをパーティショニングしたりすることはできません。

6) パーティションキーは INT 型、または INT 型を返す式である必要があり、NULL にすることができます。唯一の例外は、パーティションタイプが KEY パーティションの場合、他のタイプの列をパーティションキーとして使用できることです (BLOB または TEXT 列を除く)。

7) テーブルに主キーと一意のインデックスがある場合、主キーフィールドでパーティション分割するときに、一意のインデックス列にパーティションキーを含める必要があります。

8) 現在、MySQL は空間型と一時テーブル型のパーティション化をサポートしていません。全文インデックスはサポートされていません。

9) オブジェクトの制限 (パーティション式には、ストアド関数、ストアドプロシージャ、UDF、プラグイン、宣言された変数、またはユーザー変数を含めることはできません。)

10) 演算の制限 (パーティション式では加算、減算、乗算などの演算がサポートされていますが、演算の結果は INT または NULL である必要があります。DIV はサポートされていますが、/、|、&、^、<<、>>、~ はサポートされておらず、パーティション式で使用することもできません)

11) sql_mode の制限 (パーティションテーブルを作成した後は、mysql の sql_mode を変更しないことを強くお勧めします。モードが異なると、一部の関数や操作によって返される結果が異なる可能性があるためです)

12) query_cacheとINSERT DELAYEDはサポートされていません

13) パーティションキーはサブクエリにすることはできません (サブクエリが int 値または null を返す場合でも)。

14) サブパーティションの制限 (サブパーティション化できるのは RANG パーティションと LIST パーティションのみです。HASH パーティションと KEY パーティションはサブパーティション化できず、サブパーティションは HASH または KEY タイプである必要があります)

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

1) 水平分割（列属性に基づく行単位）

たとえば、10 年間の請求書レコードを含むテーブルを 10 個の異なるパーティションに分割し、各パーティションに 1 年間のレコードを含めることができます。

水平分割のいくつかのモード:

* 範囲: このモードでは、DBA はデータをさまざまな範囲に分割できます。

たとえば、テーブルを年ごとに 3 つのパーティションに分割できます。1980 年代のデータ、1990 年代のデータ、2000 年以降のデータ (2000 年を含む) です。

* ハッシュ: このモードでは、DBA はテーブルの 1 つ以上の列のハッシュキーを計算し、最終的にハッシュコードの異なる値に従ってデータ領域を分割できます。

たとえば、テーブルの主キーをパーティション分割するテーブルを作成できます。

* キー: 上記のハッシュモードの拡張です。ここでのハッシュキーは MySQL システムによって生成されます。

* リスト（定義済みリスト）：このモードでは、システムは DBA 定義リストの値に対応する行データを分割できます。例: DBA は、2004、2005、2006 の値に対応するデータに基づいて、3 つのパーティションにまたがるテーブルを作成します。

* 列パーティションは、範囲パーティションとリストパーティションを補完するものです。後者 2 つが整数パーティション (または整数への変換) のみをサポートしているという事実を補うもので、サポートされるデータ型が大幅に増加し (すべての整数型、日付と時刻型、文字型)、複数列パーティションもサポートされます。

注: 複数列のパーティションテーブルにデータを挿入する場合は、タプル比較が使用されます。つまり、複数列のソート、最初にフィールド 1 に従ってソート、次にフィールド 2 に従ってソートし、ソート結果に従ってデータをパーティション分割して保存します。

* 複合: 上記のモードの組み合わせ。

たとえば、最初に範囲パーティション化されたテーブルでは、パーティションの 1 つに対してハッシュパーティション化を実行できます。

垂直分割（列別）：

たとえば、テーブルに頻繁にアクセスされない大きなテキスト列と BLOB 列が含まれている場合、これらの使用頻度の低いテキスト列と BLOB 列を別のパーティションに分割することで、データの関連性を確保しながらアクセス速度を向上させることができます。

注: サブパーティション (キーワードサブパーティション): RANGE または LIST パーティションをさらに分割してサブパーティションを形成することができ、サブパーティションは HASH パーティションまたは KEY パーティションになります。複数のディスクでの使用をお勧めします。

パーティションテーブルがサポートされているかどうかを確認する

mysql> プラグインを表示します。
 +----------------------------+----------+--------------------+---------+---------+
 | 名前 | ステータス | タイプ | ライブラリ | ライセンス |
 +----------------------------+----------+--------------------+---------+---------+
 | パーティション | アクティブ | ストレージ エンジン | NULL | GPL |
 +----------------------------+----------+--------------------+---------+---------+
 または、mysql> SELECT PLUGIN_NAME as Name、PLUGIN_VERSION as Version、PLUGIN_STATUS as Status を使用します。
 -> INFORMATION_SCHEMA.PLUGINS から
 -> WHERE PLUGIN_TYPE='STORAGE ENGINE';

注意: MySQL 5.6.1 より前のバージョンでは、次のコマンドを使用して have_partitioning パラメータを表示できます。このパラメータは新しいバージョンでは削除されています。

 mysql> '%partition%' のような変数を表示します。

5. 実戦でよく使われるパーティションテーブルモード

1) RANGEパーティションモードを使用する

####テストテーブルt1を作成し、約400万行のデータを挿入します。パーティションがないと、特定の条件をクエリするのに長い時間がかかります。

mysql> テーブル `t1` を作成します ( 
 `id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT 'テーブルの主キー', 
 `pid` int(10) unsigned NOT NULL COMMENT '製品ID', 
 `price` 小数点(15,2) NOT NULL COMMENT '単価', 
 `num` int(11) NOT NULL COMMENT '購入数量', 
 `uid` int(10) unsigned NOT NULL COMMENT '顧客ID', 
 `atime` datetime NOT NULL COMMENT '注文時間', 
 `utime` int(10) unsigned NOT NULL DEFAULT 0 COMMENT '変更時刻', 
 `isdel` tinyint(4) NOT NULL DEFAULT '0' COMMENT 'ソフト削除フラグ', 
 主キー (`id`,`atime`) 
 ） 
 t1(`pid`,`price`,`num`,`uid`,`atime`) VALUES(1,12.23,1,89757,CURRENT_TIMESTAMP()) に挿入します。 
 t1(`pid`,`price`,`num`,`uid`,`atime`) VALUES(1,12.23,1,89757,'2016-05-01 00:00:00') に挿入します。 
 t1(`pid`,`price`,`num`,`uid`,`atime`) に VALUES(1,12.23,1,89757,'2017-05-01 00:00:00') を挿入します。 
 t1(`pid`,`price`,`num`,`uid`,`atime`) に VALUES(1,12.23,1,89757,'2018-05-01 00:00:00') を挿入します。 
 t1(`pid`,`price`,`num`,`uid`,`atime`) に VALUES(1,12.23,1,89756,'2015-05-01 00:00:00') を挿入します。 
 t1(`pid`,`price`,`num`,`uid`,`atime`) VALUES(1,12.23,1,89756,'2016-05-01 00:00:00') に挿入します。 
 t1(`pid`,`price`,`num`,`uid`,`atime`) に VALUES(1,12.23,1,89756,'2017-05-01 00:00:00') を挿入します。 
 t1(`pid`,`price`,`num`,`uid`,`atime`) VALUES(1,12.23,1,89756,'2018-05-01 00:00:00') に挿入します。 
 /********************************** 大量データのマスタースレーブレプリケーション********************************/ 
 mysql> `t1`(`pid`,`price`,`num`,`uid`,`atime`) に INSERT INTO し、`pid`,`price`,`num`,`uid`,`atime` を `t1` から SELECT します。 
 mysql> `t1` から SELECT * WHERE `uid`=89757 AND `atime`< CURRENT_TIMESTAMP(); 
 セット内の行数は 1048576 行 (5.62 秒) #パーティション テーブルがない場合の 5.62 秒

既存のテーブルをパーティション分割する場合は、ALTER TABLE を使用してテーブルをパーティション分割されたテーブルに変更できます。この操作により、パーティション分割されたテーブルが作成され、データが自動的にコピーされてから、元のテーブルが削除されます。

注意: これにより、大量のサーバーリソースが消費されます (400 万個を超えるデータの処理には 1 分以上かかります)

mysql> ALTER TABLE t1 PARTITION BY RANGE (YEAR(atime)) 
 -> ( 
 -> パーティション p0 の値が (2016) 未満 
 -> パーティション p1 の値が (2017) 未満の場合、
 -> パーティション p2 の値が (2018) 未満の場合、 
 -> パーティション p3 の値が MAXVALUE 未満です);
 クエリは正常、4194304 行が影響を受けました (1 分 8.32 秒)
 mysql> EXPLAIN PARTITIONS SELECT * FROM `t1`; #パーティションのステータスを表示+----+-------------+------------+------------+---------+---------------+-------+----------+---------+---------+--------+
 | id | select_type | テーブル | パーティション | タイプ | 可能なキー | キー | キー長 | ref | 行 | フィルター済み | 追加 |
 +----+-------------+-----------+-----------+--------+---------------+-------+---------+---------+--------+-------+
 | 1 | シンプル | t1 | p0、p1、p2、p3 | すべて | NULL | NULL | NULL | NULL | 4180974 | 100.00 | NULL |
 +----+-------------+-----------+-----------+--------+---------------+-------+---------+---------+--------+-------+
 セットに 1 行、警告 2 件 (0.00 秒)

上記と同じクエリを使用して結果をテストします

mysql> `t1` から SELECT * WHERE `uid`=89757 AND `atime`< CURRENT_TIMESTAMP();
 セット内の行数は1048576行（4.46秒）#パーティション実行時間のない上記のクエリと比較すると、約1秒短くなります
 mysql> EXPLAIN PARTITIONS SELECT * FROM `t1` WHERE `uid`=89757 AND `atime`< CURRENT_TIMESTAMP(); #クエリで使用されるパーティションを表示します +----+-------------+----------+------------+----------+---------------+-------+-----------+-----------+------------+-------------+
 | id | select_type | テーブル | パーティション | タイプ | 可能なキー | キー | キー長 | ref | 行 | フィルター済み | 追加 |
 +----+-------------+--------+-----------+---------+---------------+-------+----------+----------+------------+-------------+
 | 1 | SIMPLE | t1 | p0,p1,p2 | ALL | NULL | NULL | NULL | NULL | 3135804 | 3.33 | where の使用 |
 +----+-------------+--------+-----------+---------+---------------+-------+----------+----------+------------+-------------+
 セットに 1 行、警告 2 件 (0.00 秒)

同時に、テーブルが分割された後、MySQLがデータを保存するデータフォルダ内のテーブルのストレージファイルも複数に分割されることに注意してください。

 -rw-r----- 1 mysql mysql 8.7K 2月14日 14:49 t1.frm
 -rw-r----- 1 mysql mysql 36M 2月14日 14:50 t1#P#p0.ibd
 -rw-r----- 1 mysql mysql 64M 2月14日 14:50 t1#P#p1.ibd
 -rw-r----- 1 mysql mysql 92M 2月14日 14:50 t1#P#p2.ibd
 -rw-r----- 1 mysql mysql 64M 2月14日 14:50 t1#P#p3.ibd

実際の運用環境では、別のアプローチが主に使用されます。元のテーブルと同じ新しいパーティションテーブルを作成し、元のテーブルからデータをエクスポートし、それを新しいテーブルにインポートし、最後に通常のインデックスを作成します。

mysql> テーブル `t2` を作成します ( 
 `id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT 'テーブルの主キー', 
 `pid` int(10) unsigned NOT NULL COMMENT '製品ID', 
 `price` 小数点(15,2) NOT NULL COMMENT '単価', 
 `num` int(11) NOT NULL COMMENT '購入数量', 
 `uid` int(10) unsigned NOT NULL COMMENT '顧客ID', 
 `atime` datetime NOT NULL COMMENT '注文時間', 
 `utime` int(10) unsigned NOT NULL DEFAULT 0 COMMENT '変更時刻', 
 `isdel` tinyint(4) NOT NULL DEFAULT '0' COMMENT 'ソフト削除フラグ', 
 主キー (`id`,`atime`) 
 ） 
 
 範囲列によるパーティション(atime) ( 
 パーティション p0 の値は ('2016-01-01') より小さいです。
 パーティション p1 の値は ('2016-02-01') より小さいです。
 パーティション p2 の値が ('2016-03-01') より小さい。
 パーティション p3 の値は ('2016-04-01') より小さいです。
 パーティション p4 の値が ('2016-05-01') より小さい。
 パーティション p5 の値は ('2016-06-01') より小さいです。
 パーティション p6 の値は ('2016-07-01') より小さいです。
 パーティション p7 の値は ('2016-08-01') より小さいです。
 パーティション p8 の値は ('2016-09-01') より小さいです。
 パーティション p9 の値は ('2016-10-01') より小さいです。
 パーティション p10 の値が ('2016-11-01') より小さい
 パーティション p11 の値は ('2016-12-01') より小さいです。
 パーティション p12 の値が ('2017-01-01') より小さい
 パーティション p13 の値は ('2017-02-01') より小さいです。
 パーティション p14 の値が ('2017-03-01') より小さい。
 パーティション p15 の値が ('2017-04-01') より小さい。
 パーティション p16 の値は ('2017-05-01') より小さいです。
 パーティション p17 の値は ('2017-06-01') より小さいです。
 パーティション p18 の値が ('2017-07-01') より小さい
 パーティション p19 の値が ('2017-08-01') より小さい
 パーティション p20 の値が ('2017-09-01') より小さい。
 パーティション p21 の値は ('2017-10-01') より小さいです。
 パーティション p22 の値は ('2017-11-01') より小さいです。
 パーティション p23 の値は ('2017-12-01') より小さいです。
 パーティション p24 の値が ('2018-01-01') より小さい
 パーティション p25 の値が ('2018-02-01') より小さい
 パーティション p26 の値は ('2018-03-01') より小さいです。
 パーティション p27 の値は ('2018-04-01') より小さいです。
 パーティション p28 の値は ('2018-05-01') より小さいです。
 パーティション p29 の値が ('2018-06-01') より小さい
 パーティション p30 の値が ('2018-07-01') より小さい。
 パーティション p31 の値は ('2018-08-01') より小さいです。
 パーティション p32 の値が ('2018-09-01') より小さい
 パーティション p33 の値は ('2018-10-01') より小さいです。
 パーティション p34 の値は ('2018-11-01') より小さいです。
 パーティション p35 の値は ('2018-12-01') より小さいです。
 パーティション p36 の値が MAXVALUE より小さい
 );

注: テーブルの主キーは id のみで、パーティションフィールドは atime です。ここで、主キーを id、stsdate 共同主キーに変更する必要があります。パーティションテーブルでは、パーティションフィールドが主キーまたは主キーの一部である必要があります。

mysql> EXPLAIN PARTITIONS SELECT * FROM `t2`\G;
 ************************** 1. 行 ****************************
 id: 1
 選択タイプ: シンプル
 テーブル: t2
 パーティション: p0、p1、p2、p3、p4、p5、p6、p7、p8、p9、p10、p11、p12、p13、p14、p15、p16、p17、p18、p19、p20、p21、p22、p23、p24、p25、p26、p27、p28、p29、p30、p31、p32、p33、p34、p35、p36
 タイプ: すべて
 可能なキー: NULL
 キー: NULL
 キー長さ: NULL
 参照: NULL
 行数: 1
 フィルター: 100.00
 追加: NULL
 セットに 1 行、警告 2 件 (0.00 秒)
 ******************************************************** データの挿入********************************************************
 `t2`(`pid`,`price`,`num`,`uid`,`atime`) に挿入し、`t1` から `pid`,`price`,`num`,`uid`,`atime` を選択します。
 クエリは正常、4194304 行が影響を受けました (1 分 18.54 秒)
 レコード: 4194304 重複: 0 警告: 0

またはデータをエクスポートしてインポートし、インデックスを追加します

mysqldump -u データベース名 -p --no-create-info データベース名 t2 > t2.sq

テーブル名を変更し、データをインポートしてテストし、元のテーブルを削除します。

2) LIST パーティションモードを使用する(元のテーブルに強力な主キーがある場合、新しいテーブルを作成するときに、元の主キーとパーティションフィールドが結合主キーとして作成されます)

mysql> テーブル `tb01` を作成します ( 
 `id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT 'テーブルの主キー', 
 `pid` int(10) unsigned NOT NULL COMMENT '製品ID', 
 `price` 小数点(15,2) NOT NULL COMMENT '単価', 
 `num` int(11) NOT NULL COMMENT '購入数量', 
 `uid` int(10) unsigned NOT NULL COMMENT '顧客ID', 
 `atime` datetime NOT NULL COMMENT '注文時間', 
 `utime` int(10) unsigned NOT NULL DEFAULT 0 COMMENT '変更時刻', 
 `isdel` tinyint(4) NOT NULL DEFAULT '0' COMMENT 'ソフト削除フラグ', 
 主キー (`id`,`num`) 
 ); 
 *****************************テストデータを挿入****************************************************************
 `tb01`(`pid`,`price`,`num`,`uid`,`atime`) に挿入し、`tb` から `pid`,`price`,`num`,`uid`,`atime` を選択します。
 クエリは正常、3145728 行が影響を受けました (46.26 秒)
 レコード: 3145728 重複: 0 警告: 0
 mysql> ALTER TABLE tb01 PARTITION BY LIST(num) 
 （ 
 パーティションpl01の値（1,3） 
 パーティションpl02の値は(2,4)で、 
 パーティションpl03 (5,7)の値、 
 パーティションpl04の値（6,8） 
 パーティションpl05の値（9,10） 
 );
 クエリは正常、3145728 行が影響を受けました (48.86 秒)
 レコード: 3145728 重複: 0 警告: 0
次のファイルはmysqlデータファイルに生成されます -rw-r----- 1 mysql mysql 8.7K 2月15日 11:35 tb01.frm
 -rw-r----- 1 mysql mysql 56M 2月15日 11:36 tb01#P#pl01.ibd
 -rw-r----- 1 mysql mysql 32M 2月15日 11:36 tb01#P#pl02.ibd
 -rw-r----- 1 mysql mysql 36M 2月15日 11:36 tb01#P#pl03.ibd
 -rw-r----- 1 mysql mysql 36M 2月15日 11:36 tb01#P#pl04.ibd
 -rw-r----- 1 mysql mysql 52M 2月15日 11:36 tb01#P#pl05.ibd

mysql> EXPLAIN PARTITIONS SELECT * FROM `tb01`;
+----+-------------+-------+-------------------------+-------+---------------+-------+---------+---------+---------+-------+
| id | select_type | テーブル | パーティション | タイプ | 可能なキー | キー | キー長 | ref | 行 | フィルター済み | 追加 |
+----+-------------+-------+-------------------------+-------+---------------+-------+---------+---------+---------+-------+
| 1 | シンプル | tb01 | pl01、pl02、pl03、pl04、pl05 | すべて | NULL | NULL | NULL | NULL | 3136392 | 100.00 | NULL |
+----+-------------+-------+-------------------------+-------+---------------+-------+---------+---------+---------+-------+
セットに 1 行、警告 2 件 (0.00 秒)

3) COLUMNSパーティション

どちらの列も共同主キーではない複数列パーティションテーブルtb02を作成します。

mysql> テーブルtb02を作成します(
 -> nullではないint、
 -> b int が null ではない
 -> )
 -> 範囲列によるパーティション(a,b)(
 -> パーティションp0の値は（0,10）未満です。
 -> パーティションp1の値は(10,20)未満です。
 -> パーティションp2の値は(10,30)未満です。
 -> パーティション p3 の値が (maxvalue, maxvalue) より小さい
 -> );
 mysql> EXPLAIN PARTITIONS SELECT * FROM `tb02`; #View +----+-------------+------------+------------+-------+---------------+-------+--------+-------+--------+--------+--------+
 | id | select_type | テーブル | パーティション | タイプ | 可能なキー | キー | キー長 | ref | 行 | フィルター済み | 追加 |
 +----+-------------+-----------+-----------+-------+---------------+-------+-------+------+------+------+------+
 | 1 | シンプル | tb02 | p0、p1、p2、p3 | すべて | NULL | NULL | NULL | NULL | 1 | 100.00 | NULL |
 +----+-------------+-----------+-----------+-------+---------------+-------+-------+------+------+------+------+
 セットに 1 行、警告 2 件 (0.00 秒)
 mysql> insert into tb02 values (11,13); # テストデータを手動で挿入 クエリは正常、1行が影響を受けました (0.01秒)
 mysql> information_schema.partitions から PARTITION_NAME、PARTITION_EXPRESSION、TABLE_ROWS を選択します。ここで、table_schema=schema() かつ table_name='tb02' です。 
 +----------------+----------------------+------------+
 | パーティション名 | パーティション式 | テーブル行 |
 +----------------+----------------------+------------+
 | p0 | `a`,`b` | 0 |
 | p1 | `a`,`b` | 0 |
 | p2 | `a`,`b` | 0 |
 | p3 | `a`,`b` | 1 |
 +----------------+----------------------+------------+
 セット内の4行（0.03秒）

4) ハセパーティション

HASH は主に、設定された数のパーティション間でデータをできるだけ均等に分散するために使用されます。ハッシュパーティション分割を実行する場合、MySQL はパーティションキーに対してハッシュ関数を実行し、データをどのパーティションに配置するかを決定します。 HASH パーティショニングは、通常の HASH パーティショニングと線形 HASH パーティショニングに分けられます。前者はモジュロアルゴリズムを使用し、後者は線形 2 の累乗演算規則を使用します。

テーブル `tb03` を作成します ( 
 `id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT 'テーブルの主キー', 
 `pid` int(10) unsigned NOT NULL COMMENT '製品ID', 
 `price` 小数点(15,2) NOT NULL COMMENT '単価', 
 `num` int(11) NOT NULL COMMENT '購入数量', 
 `uid` int(10) unsigned NOT NULL COMMENT '顧客ID', 
 `atime` datetime NOT NULL COMMENT '注文時間', 
 `utime` int(10) unsigned NOT NULL DEFAULT 0 COMMENT '変更時刻', 
 `isdel` tinyint(4) NOT NULL DEFAULT '0' COMMENT 'ソフト削除フラグ', 
 主キー (`id`)
 ） 
 PARTITION BY HASH(id) パーティション 4;
 2行のデータを挿入します。
 tb03 に INSERT INTO(`pid`,`price`,`num`,`uid`,`atime`) VALUES(1,12.23,1,89757,CURRENT_TIMESTAMP()); 
 tb03 に INSERT INTO(`pid`,`price`,`num`,`uid`,`atime`) VALUES(1,12.23,1,89757,CURRENT_TIMESTAMP());
 mysql> パーティションの説明 select * from tb03 where id=1;
 +----+-------------+---------+-----------+--------+---------------+---------+-------+-------+------+------+------+------+
 | id | select_type | テーブル | パーティション | タイプ | 可能なキー | キー | キー長 | ref | 行 | フィルター済み | 追加 |
 +----+-------------+---------+-----------+--------+---------------+---------+-------+-------+------+------+------+------+
 | 1 | シンプル | tb03 | p1 | 定数 | プライマリ | プライマリ | 4 | 定数 | 1 | 100.00 | NULL |
 +----+-------------+---------+-----------+--------+---------------+---------+-------+-------+------+------+------+------+
 セットに 1 行、警告 2 件 (0.00 秒)
 mysql> パーティションの説明 select * from tb03 where id=2;
 +----+-------------+---------+-----------+--------+---------------+---------+-------+-------+------+------+------+------+
 | id | select_type | テーブル | パーティション | タイプ | 可能なキー | キー | キー長 | ref | 行 | フィルター済み | 追加 |
 +----+-------------+---------+-----------+--------+---------------+---------+-------+-------+------+------+------+------+
 | 1 | SIMPLE | tb03 | p2 | const | PRIMARY | PRIMARY | 4 | const | 1 | 100.00 | NULL |
 +----+-------------+---------+-----------+--------+---------------+---------+-------+-------+------+------+------+------+
 セットに 1 行、警告 2 件 (0.00 秒)

注: HASH パーティション分割では、各パーティションに可能な限り均等にデータが分散されるため、クエリの効率が向上しますが、パーティション管理のコストが増加します。たとえば、以前に 5 つのパーティションがあり、さらに 1 つのパーティションを追加する場合、アルゴリズムは mod(expr,5) から (expr,6) に変わり、元の 5 つのパーティションのほとんどのデータを再計算して再パーティション化する必要があります。線形 HASH パーティション分割を使用するとパーティション管理のコストは削減されますが、データは通常の HASH ほど均等に分散されません。

5) KEYパーティション

KEY パーティション分割は HASH パーティション分割に似ていますが、カスタム式は使用できません。ただし、Text や Blob などのテキストタイプを除く、多くのタイプのパーティションキーがサポートされています。

テーブル `tb04` を作成します ( 
 `id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT 'テーブルの主キー', 
 `pid` int(10) unsigned NOT NULL COMMENT '製品ID', 
 `price` 小数点(15,2) NOT NULL COMMENT '単価', 
 `num` int(11) NOT NULL COMMENT '購入数量', 
 `uid` int(10) unsigned NOT NULL COMMENT '顧客ID', 
 `atime` datetime NOT NULL COMMENT '注文時間', 
 `utime` int(10) unsigned NOT NULL DEFAULT 0 COMMENT '変更時刻', 
 `isdel` tinyint(4) NOT NULL DEFAULT '0' COMMENT 'ソフト削除フラグ', 
 主キー (`id`)
 ） 
 PARTITION BY KEY(id) パーティション 4;
 2行のデータを挿入します。
 tb04(`pid`,`price`,`num`,`uid`,`atime`) VALUES(1,12.23,1,89757,CURRENT_TIMESTAMP()) に挿入します。 
 tb04(`pid`,`price`,`num`,`uid`,`atime`) VALUES(1,12.23,1,89757,CURRENT_TIMESTAMP()) に挿入します。
 # 実行タスクを使用して、レコードが含まれるパーティションを確認します。mysql> explain partitions select * from tb04 where id=1;
 +----+-------------+---------+-----------+--------+---------------+---------+-------+-------+------+------+------+------+
 | id | select_type | テーブル | パーティション | タイプ | 可能なキー | キー | キー長 | ref | 行 | フィルター済み | 追加 |
 +----+-------------+---------+-----------+--------+---------------+---------+-------+-------+------+------+------+------+
 | 1 | シンプル | tb04 | p0 | const | プライマリ | プライマリ | 4 | const | 1 | 100.00 | NULL |
 +----+-------------+---------+-----------+--------+---------------+---------+-------+-------+------+------+------+------+
 セットに 1 行、警告 2 件 (0.00 秒)
 mysql> パーティションの説明 select * from tb04 where id=2;
 +----+-------------+---------+-----------+--------+---------------+---------+-------+-------+------+------+------+------+
 | id | select_type | テーブル | パーティション | タイプ | 可能なキー | キー | キー長 | ref | 行 | フィルター済み | 追加 |
 +----+-------------+---------+-----------+--------+---------------+---------+-------+-------+------+------+------+------+
 | 1 | SIMPLE | tb04 | p3 | const | PRIMARY | PRIMARY | 4 | const | 1 | 100.00 | NULL |
 +----+-------------+---------+-----------+--------+---------------+---------+-------+-------+------+------+------+------+
 セットに 1 行、警告 2 件 (0.00 秒)

6) パーティションテーブル管理

実稼働環境ではパーティションを変更しないことをお勧めします。Alter は古いテーブルのデータを読み取り、新しく定義されたテーブルに保存します。プロセス IO は非常に大きくなり、テーブル全体がロックされます。

*1* パーティションの削除: 例: 上記のtb01テーブル

--p05パーティションクエリデータは削除されません。主にパーティションデータが削除されたときに削除されるかどうかを確認するためです。

mysql> tb01からcount(1)を選択します。ここでnum=10です。
 +----------+
 | カウント(1) |
 +----------+
 |524288|
 +----------+
 セット内1列（0.37秒）
 mysql> alter table tb01 drop part pl05; #pl05 パーティションを削除します。たとえば、複数のパーティションを一度に削除するには、alter table tb01 drop part pl04,pl05; を実行します。
 クエリは正常、影響を受けた行は 0 行 (0.06 秒)
 レコード: 0 重複: 0 警告: 0
 mysql> select count(1) from tb01 where num=10; #結果データも削除されますので、注意して操作してください+----------+ 
 | カウント(1) |
 +----------+
 | 0 |
 +----------+
 1セット1行（0.01秒）

注意: パーティションを削除するとデータも削除されるため、注意して実行してください。ハッシュパーティションまたはキーパーティションは削除できません。

*2*パーティションを追加

注意: 新しいパーティションの値には、既存のパーティションの値リスト内の値を含めることはできません。そうでない場合はエラーが報告されます。新しく追加されたパーティションはデータを再編成し、元のデータは失われません。 MAXVALUE 値が設定された後は、パーティションを直接追加することはできません。たとえば、上記の t1 テーブルが例として使用されています。

mysql> ALTER TABLE t1 ADD PARTITION (PARTITION P4 VALUES LESS THAN (2018) ); 
 エラー 1481 (HY000): MAXVALUE は最後のパーティション定義でのみ使用できます
 例: tb01mysql から削除された pl05 パーティションを追加します> ALTER TABLE tb01 ADD PARTITION(PARTITION pl05 VALUES IN (9,10));
 クエリは正常、影響を受けた行は 0 行 (0.05 秒)
 レコード: 0 重複: 0 警告: 0

*3* パーティションの分解

注: Reorganize パーティションキーワードを使用すると、データを失うことなく、テーブルの一部またはすべてのパーティションを変更できます。パーティションの全体的な範囲は、分解の前後で一貫している必要があります。

例：

mysql> テーブル tb05 を作成します
 -> (依存関係、
 -> 生年月日、
 -> 給与 int
 -> )
 -> 範囲(給与)でパーティション分割
 -> (
 -> パーティションp1の値が（1000）未満、
 -> パーティションp2の値が（2000）未満の場合、
 -> パーティション p3 の値が maxvalue より小さい
 -> );
 クエリは正常、影響を受けた行は 0 行 (0.08 秒)
 ****テストデータを挿入しますmysql> insert tb05 values(1,'2016-03-06',80);
 クエリは正常、1 行が影響を受けました (0.01 秒)
 mysql>テーブルtb05を変更し、パーティションp1を(
 パーティションp01の値が（100）未満である、
 パーティションp02の値が（1000）未満
 ); ----データは失われませんmysql> explain partitions select * from tb05 where salary=80; #新しいパーティション p01 に収まっていることを確認します+----+-------------+----------+------------+---------------+-------+----------+-----------+------------+-------------+
 | id | select_type | テーブル | パーティション | タイプ | 可能なキー | キー | キー長 | ref | 行 | フィルター済み | 追加 |
 +----+-------------+--------+-----------+--------+---------------+-------+--------+----------+-----------+-------------+
 | 1 | SIMPLE | tb05 | p01 | ALL | NULL | NULL | NULL | NULL | 1 | 100.00 | where の使用 |
 +----+-------------+--------+-----------+--------+---------------+-------+--------+----------+-----------+-------------+
 セットに 1 行、警告 2 件 (0.00 秒)

*4*パーティションの結合

注: 2 つのパーティションを 1 つに結合します。

例: 上記のtb05テーブルのp01とp02をp1にマージする

mysql> alter table tb05 reorganizepartition p01,p02 into(partition p1 values less than (1000)); --データ損失なし クエリOK、0行が影響を受けました（0.05秒）
 レコード: 0 重複: 0 警告: 0
 mysql> パーティションの説明 select * from tb05 where salary=80;
 +----+-------------+--------+-----------+--------+---------------+-------+--------+----------+-----------+-------------+
 | id | select_type | テーブル | パーティション | タイプ | 可能なキー | キー | キー長 | ref | 行 | フィルター済み | 追加 |
 +----+-------------+--------+-----------+--------+---------------+-------+--------+----------+-----------+-------------+
 | 1 | SIMPLE | tb05 | p1 | ALL | NULL | NULL | NULL | NULL | 1 | 100.00 | where の使用 |
 +----+-------------+--------+-----------+--------+---------------+-------+--------+----------+-----------+-------------+
 セットに 1 行、警告 2 件 (0.00 秒)

*5* ハッシュパーティションテーブルを再定義します。

RANGE および LIST パーティションを再定義する場合、隣接するパーティションのみを再定義でき、パーティションをスキップすることはできません。再定義されたパーティション範囲は、元のパーティション範囲と一致している必要があり、パーティションタイプを変更することはできません。

例：

mysql> EXPLAIN PARTITIONS SELECT * FROM `tb03`;
 +----+-------------+-----------+-----------+-------+---------------+-------+-------+------+------+------+------+
 | id | select_type | テーブル | パーティション | タイプ | 可能なキー | キー | キー長 | ref | 行 | フィルター済み | 追加 |
 +----+-------------+-----------+-----------+-------+---------------+-------+-------+------+------+------+------+
 | 1 | シンプル | tb03 | p0、p1、p2、p3 | すべて | NULL | NULL | NULL | NULL | 4 | 100.00 | NULL |
 +----+-------------+-----------+-----------+-------+---------------+-------+-------+------+------+------+------+
 セットに 1 行、警告 2 件 (0.00 秒)
 mysql> Alter table tb03 パーティションを hash(id)partitions 8 に変更します。#データ損失なし クエリは正常、4 行が影響を受けました (0.13 秒)
 記録: 4 重複: 0 警告: 0
 mysql> EXPLAIN PARTITIONS SELECT * FROM `tb03`;
 +----+-------------+-------+------------------------+-------+---------------+-------+-------+------+------+------+------+
 | id | select_type | テーブル | パーティション | タイプ | 可能なキー | キー | キー長 | ref | 行 | フィルター済み | 追加 |
 +----+-------------+-------+------------------------+-------+---------------+-------+-------+------+------+------+------+
 | 1 | シンプル | tb03 | p0、p1、p2、p3、p4、p5、p6、p7 | すべて | NULL | NULL | NULL | NULL | 1 | 100.00 | NULL |
 +----+-------------+-------+------------------------+-------+---------------+-------+-------+------+------+------+------+
 セットに1行、警告2回（0.02秒）

*6* テーブルのすべてのパーティションを削除します。

例: テーブル tb03 のすべてのパーティションを削除する

mysql> Alter table tb03 remove partitioning; #データ損失なし クエリは正常、4行が影響を受けました (0.07 秒)
 記録: 4 重複: 0 警告: 0
 mysql> EXPLAIN PARTITIONS SELECT * FROM `tb03`;
 +----+-------------+---------+-----------+-------+---------------+-------+-------+------+------+------+------+
 | id | select_type | テーブル | パーティション | タイプ | 可能なキー | キー | キー長 | ref | 行 | フィルター済み | 追加 |
 +----+-------------+---------+-----------+-------+---------------+-------+-------+------+------+------+------+
 | 1 | シンプル | tb03 | NULL | すべて | NULL | NULL | NULL | NULL | 4 | 100.00 | NULL |
 +----+-------------+---------+-----------+-------+---------------+-------+-------+------+------+------+------+
 セットに 1 行、警告 2 件 (0.00 秒)

*7* パーティションのデフラグ

注: パーティションから多数の行を削除したり、可変長の行 (つまり、VARCHAR、BLOB、または TEXT タイプの列) に多くの変更を加えたりする場合は、ALTER TABLE ... OPTIMIZE PARTITION を使用して未使用の領域を再利用し、パーティションデータファイルを最適化できます。

 ALTER TABLE tb03 パーティション p1、p2 を最適化します。

*8* 分析パーティション:

パーティションのキー分布を読み取って保存します。


 mysql> ALTER TABLE tb04 CHECK パーティション p1,p2;
 +--------------+-------+----------+----------+
 | テーブル | Op | メッセージ タイプ | メッセージ テキスト |
 +--------------+-------+----------+----------+
 | testsms.tb04 | チェック | ステータス | OK |
 +--------------+-------+----------+----------+
 セット内の1行（0.01秒）

*9* パーティションをチェックします:

パーティション化されていないテーブルに対して CHECK TABLE を使用する場合とほぼ同じ方法でパーティションをチェックできます。このコマンドは、テーブルのパーティション p1 と p2 のデータまたはインデックスが破損しているかどうかを tb04 に伝えます。このような場合は、「ALTER TABLE ... REPAIR PARTITION」を使用してパーティションを修復してください。

mysql> ALTER TABLE tb04 CHECK パーティション p1,p2;
 +--------------+-------+----------+----------+
 | テーブル | Op | メッセージ タイプ | メッセージ テキスト |
 +--------------+-------+----------+----------+
 | testsms.tb04 | チェック | ステータス | OK |
 +--------------+-------+----------+----------+
 セット内の1行（0.01秒）

6. 実際の生産への簡単な適用

シナリオ: 以前は、パーティションのない大きなデータテーブル SmsSend (サンプルテーブル、約 2,800 万行) がありました。統計処理には非常に時間がかかりました。年パーティションを使用し、履歴データベースをバックアップして、2014 年のデータを新しいバックアップテーブル smssendbak に転送することを検討してください。オンラインでの再定義に時間がかかる場合は、交換処理を使用できます。

1) 現在のSmsSendテーブルを表示する

mysql> SHOW CREATE TABLE SmsSend; #作成情報を表示、パーティションなし | SmsSend | CREATE TABLE `SmsSend` (
 `Guid` char(36) NOT NULL COMMENT '一意の識別子',
 `SID` varbinary(85) DEFAULT NULL COMMENT '販売者固有番号',
 `Mobile` longtext NOT NULL COMMENT '携帯電話番号を受信する (「,」で区切る)',
 `SmsContent` varchar(500) NOT NULL COMMENT 'SMSコンテンツ',
 `SmsCount` int(11) NOT NULL DEFAULT '1' COMMENT 'メッセージ数',
 `Status` int(11) NOT NULL COMMENT '現在のステータス (0、送信待機中、1、送信成功、-1、送信失敗)',
 `SendChanelKeyName` varchar(20) DEFAULT NULL COMMENT '送信チャネルID',
 `SendTime` datetime NOT NULL COMMENT '送信成功時刻',
 `SendType` int(11) NOT NULL DEFAULT '1' COMMENT 'SMS送信タイプ（1、単一送信、2、グループ送信）',
 `ReceiveTime` datetime DEFAULT NULL COMMENT '返信レポートを受信した時刻',
 `Priority` int(11) NOT NULL DEFAULT '0' COMMENT 'Priority',
 `UserAccount` varchar(50) デフォルト NULL コメント 'Operator',
 `ChainStoreGuid` char(36) DEFAULT NULL COMMENT 'オペレーションストアの一意の識別子',
 `relationkey` longtextコメント 'Reply Report Association Identifier'、
 `meno`テキストコメント「メモ」、
 `isfree` bit（1）not null default b'0 'コメント'無料ですか？ '
 ) エンジン=InnoDB デフォルト文字セット=utf8mb4 |
 mysql> smssendからcount（*）を選択します
 | カウント(*) |
 +----------+
 | 28259803 |
 +----------+
 セットの1列（1分52.60秒）
 ＃オンラインパーティション化が遅く、大規模なデータの下でパフォーマンスを消費することがわかります。 
 -> (
  - >パーティションPY01値（2015）未満、
  - >パーティションPY02値（2016）未満、
  - >パーティションPY03値（2017）未満）; 
 クエリOK、28259803列の影響を受ける（20分36.05秒）
 記録：28259803複製：0警告：0
 #viewパーティションの数Recordsmysqlの数> SMSSENDパーティション（PY01）からCount（1）を選択します。
 +----------+
 | カウント(1) |
 +----------+
 | 10 |
 +----------+
 セット内の 1 行 (0.00 秒)
 mysql> partitions select * sendsend <'2015-01-01';
 | id | select_type | テーブル | パーティション | タイプ | 可能なキー | キー | キー長 | ref | 行 | フィルター済み | 追加 |
 +----+------------+--------+------------+------+------------+-----+-------+------+------+---------+----------+
 |
 +----+------------+--------+------------+------+------------+-----+-------+------+------+---------+----------+
 セットに 1 行、警告 2 件 (0.00 秒)
 mysql> SMSSENDパーティション（PY02）からcount（1）を選択します。
 +----------+
 | カウント(1) |
 +----------+
 | 10 |
 +----------+
 セットの1列（0.00秒）

2）元のSMSSENDテーブルと同じ構造を持つSMSSENDBAKバックアップテーブルをすばやく作成し、バックアップテーブルのすべてのパーティションを削除します

mysql> smssendのようなテーブルsmssendbakを作成します。
 クエリは正常、影響を受けた行は 0 行 (0.14 秒)
 mysql> ALTER TABLE SMSSENDBAKパーティションを削除します。
 クエリは正常、影響を受けた行は 0 行 (0.19 秒)
 レコード: 0 重複: 0 警告: 0

3）交換パーティションを使用してパーティションデータをバックアップテーブルに転送し、元のテーブルと新しいバックアップテーブルのパーティションレコードを表示します

smsensdbakレコード

mysql>テーブルSMSSEND ExchangeパーティションPY01をテーブルSMSSENDBAKで変更します。
 クエリは正常、影響を受けた行は 0 行 (0.13 秒)
 mysql> select count（1）from smssendパーティション（py01）;
 | カウント(1) |
 +----------+
 | 0 |
 +----------+
 セット内の 1 行 (0.00 秒)
 mysql> smssendbakからcount（1）を選択します
 |
 +----------+
 | 10 |
 +----------+
 セット内の 1 行 (0.00 秒)
*****************テストで使用されているテーブル************************************************************************
 基本的なテストテーブルを作成します。
 テーブル `tb` を作成します ( 
 `id` int（10）unsigned not null auto_incrementコメント 'テーブルプライマリキー'、 
 `pid` int（10）unsigned not Nullコメント「製品ID」、 
 「価格」10進数（15,2）nullコメント「単価」、 
 `num` int（11）nullコメント「購入数量」、 
 `uid` int（10）null not nullコメント「顧客ID」、 
 `atime` dateTime nullコメント「注文時間」、 
 `utime` int（10）unsigned not null default 0コメント「変更時間」、 
 `isdel`tinyint（4）not null default '0'コメント 'ソフト削除フラグ'、 
 ） ;
 データを挿入:
 tb（ `pid`、` fires `、` num`、 `uid`、` atime`）値（1,12.23,1,89757、current_timestamp（））に挿入します。 
 tb（ `pid`、` fires `、` num`、 `uid`、` atime`）値（1,12.23,1,89757、 '2016-05-01 00:00:00'）に挿入。 
 TB（「PID」、「価格」、「num」、「uid」、「atime」）値（1,12.23,1,89757、 '2017-05-01 00:00:00'）に挿入。 
 TB（「PID」、「価格」、「num」、「uid」、「atime」）値（1,12.23,1,89757、 '2018-05-01 00:00:00'）に挿入。 
 TB（「PID」、「価格」、「num」、「uid」、「atime」）値に挿入（1,12.23,1,89756、 '2015-05-01 00:00:00'）; 
 TB（「PID」、「価格」、「num」、「uid」、「atime」）値（1,12.23,1,89756、 '2016-05-01 00:00:00'）に挿入。 
 Tb（ `pid`、` fires `、` num`、 `uid`、` atime`）値（1,12.23,1,89756、 '2017-05-01 00:00:00'）に挿入します。 
 
 **************************************************************************
 `tb`（pid`、` fires `、` num`、 `uid`、` atime`）select `pid`、` price`、 `num`、` uid`、 `atime` from` tb`;に挿入します。
****自己成長したプライマリキーIDを削除する場合（変更プロセス中に、ライブラリを読み取り専用にすることをお勧めします）。
  テーブルTB変更ID INT（10）;

以上がこの記事の全内容です。皆様の勉強のお役に立てれば幸いです。また、123WORDPRESS.COM を応援していただければ幸いです。

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