MySQL パーティションテーブルの正しい使用方法

MySQL パーティションテーブルの概要

数億、あるいは数十億ものレコードを格納するテーブルに遭遇することはよくあります。これらのテーブルには大量の履歴レコードが格納されます。すべてのデータが共通テーブルにあるため、これらの履歴データをクリーンアップするのは面倒です。したがって、削除するには、where 条件 (通常、where 条件は時間) を持つ 1 つ以上の delete ステートメントのみを有効にできます。これにより、データベースに大きな負担がかかります。これらを削除しても、基礎となるデータファイルは小さくなりませんでした。このような問題に直面した場合、最も効果的な方法はパーティションテーブルを使用することです。最も一般的なパーティション分割方法は、時間でパーティション分割することです。

パーティショニングの最大の利点の 1 つは、履歴データを非常に効率的にクリーンアップできることです。

1. MySQLサーバーがパーティションテーブルをサポートしているかどうかを確認する

注文：

プラグインを表示します。

2. MySQLパーティションテーブルの特徴

論理的には 1 つのテーブルですが、物理的には複数のファイルに保存されます。

ハッシュパーティション（HASH）

HASHパーティションの特徴

MOD（パーティションキー、パーティション数）の値に応じて、テーブルの異なるパーティションにデータ行を格納します。
データはパーティション間で均等に分散できる
HASHパーティションのキー値はINT型の値であるか、関数を介してINT型に変換できる必要があります。

HASHパーティションテーブルを作成する方法

INT型フィールドcustomer_idをパーティションキーとして使用します。

テーブル `customer_login_log` を作成します (
 `customer_id` int(10) unsigned NOT NULL COMMENT 'ログインユーザーID',
 `login_time` タイムスタンプ NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT 'ユーザーログイン時間',
 `login_ip` int(10) unsigned NOT NULL COMMENT 'ログインIP',
 `login_type` tinyint(4) NOT NULL COMMENT 'ログインタイプ: 0 失敗 1 成功'
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='ユーザーログインログテーブル'

PARTITION BY HASH(customer_id) PARTITIONS 4;

非INT型フィールド login_time をパーティションキーとして使用します（最初にINT型に変換する必要があります）

テーブル `customer_login_log` を作成します (
 `customer_id` int(10) unsigned NOT NULL COMMENT 'ログインユーザーID',
 `login_time` タイムスタンプ NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT 'ユーザーログイン時間',
 `login_ip` int(10) unsigned NOT NULL COMMENT 'ログインIP',
 `login_type` tinyint(4) NOT NULL COMMENT 'ログインタイプ: 0 失敗 1 成功'
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='ユーザーログインログテーブル'

PARTITION BY HASH(UNIX_TIMESTAMP(login_time)) PARTITIONS 4;

customer_login_logテーブルがパーティション化されていない場合、物理ディスク上のファイルは

customer_login_log.frm # テーブルの元のデータ情報を格納します customer_login_log.ibd # Innodb データ ファイル

上記のようにHASHパーティションテーブルを構築すると、5つのファイルがあります

顧客ログインログ.frm 
顧客ログインログ#P#p0.ibd
顧客ログインログ#P#p1.ibd
顧客ログインログ#P#p2.ibd
顧客ログインログ#P#p3.ibd

デモ

使い方はパーティションなしと同じです。データベースは 1 つしかないように見えますが、実際にはパーティションファイルが複数あります。たとえば、データを挿入する場合、パーティションを指定する必要はありません。MySQL が自動的に処理します。

クエリ

範囲分割 (RANGE)

RANGEパーティションの特性

パーティションキー値の範囲に基づいて、データ行をテーブルの異なるパーティションに格納します。
複数のパーティションの範囲は連続している必要がありますが、重複することはできません。
デフォルトでは、VALUES LESS THAN 属性が使用されます。つまり、各パーティションには指定された値は含まれません。

RANGEパーティションの作成方法

p3 パーティションが定義されていない場合、挿入された customer_id が 29999 より大きいとエラーが報告されます。定義されている場合は、この制限を超えるすべてのデータが p3 に保存されます。

RANGEパーティショニングの適用可能なシナリオ

パーティションキーは日付または時刻型です (これにより、各パーティションテーブルのデータのバランスが取れます。上記の例で整数 ID をパーティションキーとして使用し、アクティブユーザーの数が 10000 から 19999 の間に集中している場合、p1 のデータ量は他のパーティションのデータ量よりもはるかに大きくなり、パーティションは無意味になります。また、パーティションが時間型でパーティション化されている場合、データを時系列順にアーカイブする場合は、1 つのパーティションのみをアーカイブする必要があります)
すべてのクエリにパーティションキーを含める (パーティション間クエリを避ける)
パーティション範囲ごとに履歴データを定期的にクリーンアップする

リストパーティション

LISTパーティションの特徴

パーティションキー値のリストによるパーティション分割
範囲分割と同様に、各パーティションのリスト値は繰り返すことができません
各データ行は対応するパーティションリストを見つけることができなければなりません。そうでない場合、データの挿入は失敗します。

LISTパーティションを作成する方法

login_type 10 の行を挿入すると、エラーが報告されます。

3. ログインログテーブル（customer_login_log）をパーティション分割する方法

ビジネスシナリオ

ユーザーがログインするたびに、customer_login_logログが記録されます。
ユーザーのログインログは1年間保存され、1年後には削除またはアーカイブできます。

ログインログテーブルのパーティションタイプとパーティションキー

RANGEパーティションの使用
login_timeをパーティションキーとして使用する

パーティション化されたユーザーログインログテーブル

データは年ごとにパーティションに保存されるため、変換には YEAR 関数が使用されます。

テーブル `customer_login_log` を作成します (
 `customer_id` int(10) unsigned NOT NULL COMMENT 'ログインユーザーID',
 `login_time` DATETIME NOT NULL COMMENT 'ユーザーログイン時間',
 `login_ip` int(10) unsigned NOT NULL COMMENT 'ログインIP',
 `login_type` tinyint(4) NOT NULL COMMENT 'ログインタイプ: 0 失敗 1 成功'
) エンジン=InnoDB 
範囲によるパーティション (YEAR(ログイン時間))(
パーティション p0 値が (2017) 未満の場合、
パーティション p1 値が (2018) 未満の場合、
パーティション p2 の値が小さい (2019)
）

データの挿入とクエリ

指定されたテーブルのパーティションデータを照会する

テーブル名、パーティション名、パーティションの説明、テーブル行を選択します。
information_schema.`PARTITIONS` WHERE table_name = 'customer_login_log';

さらに2つの18年ログを挿入し、p2テーブルに保存します。

前述のように、パーティションテーブルを作成するときは、MAXVALUE パーティションを作成するのが最適です。ここで作成しない理由は、データメンテナンスの都合のためです。MAXVALUE パーティションを作成すると、問題を見落としやすくなります。2019 年にデータを挿入すると、自動的にその MAXVALUE パーティションに格納され、後でデータメンテナンスを行うときに不便になります。そのため、MAXVALUE パーティションは作成されません。

代わりに、計画されたタスクを通じて毎年末にこのパーティションを追加します。たとえば、2018 年の終わりに、ログテーブルに 2019 年のログパーティションを作成する必要があります。そうしないと、2019 年のログを挿入できません。

次の文を使うことができます

パーティションの追加

ALTER TABLE customer_login_log ADD PARTITION (PARTITION p3 VALUES LESS THAN(2020))

パーティションを追加してデータを挿入する

パーティションの削除

2016 年から 2017 年までのデータを削除する場合、すでにデータがパーティション分割されているため、1 つのステートメントで p0 パーティションを削除するだけで済みます。

テーブル customer_login_log を変更してパーティション p0 を削除します。

p0 パーティションが削除され、2016 年のログがすべてクリアされていることがわかります。

パーティション履歴データをアーカイブする

データをアーカイブする必要がまた出てくるかもしれない

MySQLバージョン>=5.7、パーティション履歴データのアーカイブは非常に便利で、パーティションを交換する方法が提供されています

パーティションデータアーカイブの移行条件:

MySQL>=5.7
同じ構造
アーカイブされたデータテーブルはパーティション化されていないテーブルである必要があります
非一時テーブル。外部キー制約を持つことはできません。
アーカイブエンジンは次のようになります: archive

テーブルを作成し、パーティションを交換する

テーブル `arch_customer_login_log` を作成します (
 `customer_id` INT unsigned NOT NULL COMMENT 'ログインユーザーID',
 `login_time` DATETIME NOT NULL COMMENT 'ユーザーログイン時間',
 `login_ip` INT unsigned NOT NULL COMMENT 'ログインIP',
 `login_type` TINYINT NOT NULL COMMENT 'ログイン タイプ: 0 失敗 1 成功'
) エンジン=InnoDB ;

ALTER TABLE 顧客ログインログ 
 パーティション p1 をテーブル arch_customer_login_log と交換します。

元の customer_login_log テーブル (p1 パーティションのデータ) の 2017 年データは arch_customer_login_log テーブルに転送されていますが、p1 パーティションは削除されておらず、データのみが転送されているため、データが挿入されないようにパーティションを削除する DROP コマンドも実行する必要があります。

アーカイブデータのストレージエンジンをアーカイブエンジンに変更する

最後に、アーカイブデータのストレージエンジンをアーカイブエンジンに変更します。コマンドは

ALTER TABLE customer_login_log ENGINE=ARCHIVE;

アーカイブエンジンを使用する利点は、Innodb よりも占有スペースが少ないことですが、アーカイブエンジンはクエリ操作のみを実行でき、書き込み操作は実行できません。

4. パーティションテーブルを使用する際のポイント

ビジネスシナリオに基づいてパーティションキーを選択し、パーティション間のクエリを回避する
パーティション化されたテーブルをクエリする場合は、WHERE 句にパーティションキーを含めるのが最適です。
主キーまたは一意のインデックスを持つテーブルの場合、主キーまたは一意のインデックスはパーティションキーの一部である必要があります (これが、上記のパーティション分割時に主キーのログインログ ID (login_id) を削除した理由です。そうしないと、上記のように年ごとにパーティション分割することができないため、パーティションテーブルは実際には MyISAM エンジンに適しています)

MyISAM と Innodb インデックスの違い

1. 自動成長について

MyISAM エンジンの自動増分列はインデックスである必要があります。複合インデックスの場合、自動増分列は最初の列である必要はありません。前の列をソートした後に増分できます。

InnoDB エンジンの自動拡張列はインデックスである必要があります。複合インデックスの場合は、複合インデックスの最初の列である必要があります。

2. 主キーについて

MyISAM では、インデックスや主キーのないテーブルの存在が許可されます。

MyISAM インデックスは行アドレスを格納します。

InnoDBエンジンが主キーまたは空でない一意のインデックスを設定しない場合、6バイトの主キー（ユーザーには見えない）が自動的に生成されます。

Innodb データはプライマリインデックスの一部であり、追加インデックスにはプライマリインデックスの値が格納されます。

3. count()関数について

MyISAM はテーブル内の行の総数を格納します。count(*) from table; を選択すると、値が直接取得されます。

Innodb はテーブル内の行の総数を保存しません。select count(*) from table; を使用すると、テーブル全体がトラバースされるため、かなりの時間がかかります。ただし、where 条件を追加すると、MyISAM と Innodb は同じように処理します。

4. 全文インデックス

MyISAMはFULLTEXTタイプのフルテキストインデックスをサポートします

InnoDB は FULLTEXT タイプのフルテキストインデックスをサポートしていませんが、InnoDB は sphinx プラグインを使用してフルテキストインデックスをサポートできるため、効果は向上します。（Sphinxは、複数の言語でAPIインターフェースを提供し、さまざまなMySQLクエリを最適化できるオープンソースソフトウェアです）

5.テーブルから削除

このコマンドを使用すると、InnoDB はテーブルを再作成せず、データを 1 つずつ削除します。InnoDB 上の大量のデータを持つテーブルをクリアする場合は、このコマンドを使用しないことをお勧めします。 (truncate table を使用することをお勧めしますが、ユーザーにはこのテーブルを削除する権限が必要です)

6. インデックスの保存場所

MyISAM インデックスは、テーブル名 + .MYI ファイルとして個別に保存されます。

Innodb インデックスは、テーブルスペース内のデータと一緒に保存されます。

要約する

上記はこの記事の全内容です。この記事の内容が皆さんの勉強や仕事に一定の参考学習価値を持つことを願っています。ご質問があれば、メッセージを残してコミュニケーションしてください。123WORDPRESS.COM を応援していただきありがとうございます。

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