Mysql の一時テーブルとパーティションテーブルの違いの詳細な説明

一時テーブルとメモリテーブル

メモリ テーブルとは、メモリ エンジンを使用するテーブルを指します。テーブルを作成するための構文は、create table … engine=memory です。このタイプのテーブルのデータはメモリに保存され、システムを再起動するとクリアされますが、テーブル構造は引き続き存在します。 「奇妙」に見えるこの 2 つの機能を除けば、他の機能から見ると、通常のテーブルです。

一時テーブルではさまざまなエンジン タイプを使用できます。 InnoDB エンジンまたは MyISAM エンジンを使用して一時テーブルを使用する場合、データはディスクに書き込まれます。もちろん、一時テーブルでもメモリ エンジンを使用できます。

一時テーブルの特性

• テーブルを作成するための構文は、create temporary table … です。
• 一時テーブルは、それを作成したセッションからのみアクセスでき、他のスレッドからは見えません。したがって、図のセッション A によって作成された一時テーブル t は、セッション B からは見えません。
• 一時テーブルには、通常のテーブルと同じ名前を付けることができます。
• セッション A に同じ名前の一時テーブルと通常のテーブルがある場合、show create ステートメント、および add、delete、modify、query ステートメントは一時テーブルにアクセスします。
• show tables コマンドは一時テーブルを表示しません。

一時テーブルはそれを作成したセッションからのみアクセスできるため、セッションが終了すると自動的に削除されます。 この機能のおかげで、一時テーブルは結合最適化シナリオに特に適しています。

t1 のような一時テーブル temp_t を作成します。
テーブルtemp_tを変更し、インデックス(b)を追加します。
temp_t に挿入し、b>=1 かつ b<=2000 の場合に t2 から * を選択します。
t1 から * を選択して、temp_t を (t1.b=temp_t.b) で結合します。

異なるセッションの一時テーブルには同じ名前を付けることができます。複数のセッションが同時に結合最適化を実行する場合、テーブル名の重複によるテーブル作成の失敗を心配する必要はありません。データの削除を心配する必要はありません。通常のテーブルを使用する場合、プロセス実行中にクライアントが異常切断された場合、またはデータベースが異常再起動された場合、プロセスの途中で生成されたデータテーブルをクリーンアップする必要があります。一時テーブルは自動的にリサイクルされるため、この追加操作は必要ありません。一時テーブルの適用

シャードデータベースとテーブルシステムのクロスデータベースクエリ

データベースとテーブルをシャーディングする一般的なシナリオは、論理的に大きなテーブルを異なるデータベース インスタンスに分散することです。例えば。大きなテーブル ht をフィールド f に従って 1024 個のサブテーブルに分割し、32 個のデータベース インスタンスに分散します。

パーティション キーの選択は、「データベース間およびテーブル間のクエリの削減」に基づいています。ほとんどのステートメントに f の等価条件が含まれている場合は、 f をパーティション キーとして使用する必要があります。このように、プロキシ レイヤーは SQL ステートメントを解析した後、クエリのためにステートメントをルーティングするサブテーブルを決定できます。 例えば

f=N の場合、ht から v を選択します。

このとき、テーブルパーティションルール (たとえば、N%1024) を使用して、必要なデータがどのテーブルに配置されているかを確認できます。このタイプのステートメントは、1 つのシャード テーブルにのみアクセスする必要があり、シャード データベースおよびテーブルで最も一般的なステートメント形式です。

ただし、このテーブルに別のインデックス k があり、クエリ ステートメントが次のようになる場合は、

k >= M の場合、ht から v を選択し、t_modified で並べ替え、desc limit 100 にします。

このとき、パーティション フィールド f はクエリ条件で使用されていないため、すべてのパーティションで条件を満たすすべての行を検索し、その後、均一に order by 操作を実行することしかできません。この場合、一般的なアプローチは 2 つあります。

プロキシ層のプロセスコードにソートを実装すると、プロキシ側に大きな負担がかかり、特にメモリ不足や CPU ボトルネックなどの問題が発生しやすくなります。

各サブデータベースから取得したデータを MySQL インスタンス内のテーブルに集約し、この集約されたインスタンスに対して論理操作を実行します。

サマリー データベースに一時テーブル temp_ht を作成します。このテーブルには、v、k、t_modifified の 3 つのフィールドが含まれます。

各サブライブラリで実行

ht_x から v,k,t_modified を選択し、k >= M で t_modified で順序付けし、desc limit 100 を指定します。

サブデータベース実行の結果を temp_ht テーブルに挿入します。

埋め込む

temp_ht から v を選択し、t_modified desc limit 100 で順序付けします。

一時テーブルの名前を変更できるのはなぜですか?

一時テーブル temp_t(id int primary key) を作成します。engine=innodb;

このステートメントを実行すると、MySQL はこの InnoDB テーブルの frm ファイルを作成し、テーブル構造の定義とテーブル データを保存する場所を保存します。

この frm ファイルは一時ファイル ディレクトリに配置されます。ファイル名のサフィックスは .frm、プレフィックスは "#sql{プロセス ID}_{スレッド ID}_シリアル番号" です。 select @@tmpdir コマンドを使用して、インスタンスの一時ファイル ディレクトリを表示できます。

このプロセスのプロセス ID は 1234、セッション A のスレッド ID は 4、セッション B のスレッド ID は 5 です。したがって、セッション A とセッション B によって作成された一時テーブルには、ディスク上に重複したファイルが存在しません。

MySQL はデータ テーブルを管理します。物理ファイルに加えて、メモリ内に異なるテーブルを区別するメカニズムもあります。各テーブルは table_def_key に対応します。 一時テーブルの場合、table_def_key は「データベース名 + テーブル名」に「server_id + thread_id」を追加します。

つまり、セッション A とセッション B によって作成された 2 つの一時テーブル t1 は、table_def_key とディスク ファイル名が異なるため、共存できます。

パーティションテーブルのエンジンレベルの動作

ATE TABLE `t` (
		`ftime` 日時 NOT NULL、
		`c` int(11) デフォルト NULL,
		キー (`ftime`)
) エンジン=InnoDB デフォルト文字セット=latin1
範囲によるパーティション (YEAR(ftime))
の
B
 (パーティション p_2017 値が (2017) 未満) エンジン = InnoDB、
 	パーティション p_2018 値が (2018) 未満 エンジン = InnoDB、
 	パーティション p_2019 値が (2019) 未満 エンジン = InnoDB、
 PARTITION p_others の値が MAXVALUE 未満 ENGINE = InnoDB);
 t 値に挿入します('2017-4-1',1),('2018-4-1',1); 

テーブルが初期化されると、2 行のデータのみが挿入されます。sessionA の SELECT ステートメントは、ftime の 2 つのレコード間のギャップをロックします。ギャップとロックの状態は、次の図に示されています。

つまり、2 つのレコード 2017-4-1 と 2018-4-1 の間のギャップがロックされ、その後、sessionB の両方の挿入ステートメントがロック待機状態になります。ただし、効果の面では、最初の挿入ステートメントは正常に実行できます。これは、エンジンにとって、p2018 と p2019 は異なるテーブルであり、2017 年の次のレコードは 2018-4-1 ではなく、p2018 の最大値であるためです。そのため、時刻 t1 のインデックスは図のようになります。

パーティション テーブルのルールにより、セッション A は p2018 のみを操作します。セッション B は 2018-2-1 を挿入できますが、2017-12-1 に書き込むにはセッション A のギャップ ロックを待つ必要があります。

MYISAM エンジンの場合:

セッションAは100秒間スリープし、MyISAMはテーブルロックのみをサポートしているため、この更新によりテーブルt全体の読み取りがロックされます。ただし、結果として、Bの最初のステートメントは実行可能になり、2番目のステートメントはロック待機状態になります。

これは、MyISAM テーブル ロックがエンジン レイヤーでのみ実装されているためです。sessionA によって追加されたテーブル ロックは p2018 上にあるため、パーティションで実行されるクエリのみがブロックされ、他のパーティションに該当するクエリは影響を受けません。パーティション テーブルは悪くないようです。では、なぜ使用しないのでしょうか。パーティション テーブルを使用する理由の 1 つは、単一のテーブルが大きすぎることです。パーティション テーブルを使用しない場合は、手動のテーブル パーティション分割方法を使用する必要があります。

手動テーブル パーティション分割では、t_2017、t_2018、t_2019 を作成する必要があります。つまり、更新する必要があるすべてのサブテーブルを見つけて、それらを 1 つずつ実行する必要があります。これは、パーティション分割されたテーブルと変わりません。1 つは、サーバーが使用するパーティションを決定し、もう 1 つは、アプリケーション レイヤー コードが使用するサブテーブルを決定します。したがって、エンジン レイヤーと実際の違いはありません。実際、主な違いはサーバー レベル、つまりテーブルを開くときの動作にあります。

パーティショニング戦略

パーティション化されたテーブルに初めてアクセスするときは常に、MySQL はすべてのパーティションにアクセスする必要があります。パーティションの数が多い場合 (たとえば、1000 個のパーティションがチェックされている場合)、MySQL の起動時に open_files_limit がデフォルトで 1024 に設定され、テーブルにアクセスすると、すべてのファイルが開かれて上限を超えるため、エラーが報告されます。

mysiam が使用するパーティション分割戦略は一般的なパーティション分割戦略と呼ばれ、パーティションへの各アクセスはサーバー層によって制御されます。重大なパフォーマンスの問題があります。

Innodb エンジンは、Innodb 自体内でパーティションを開く動作を管理するローカル パーティショニング戦略を導入します。

パーティションテーブルのサーバーレベルの動作

サーバー層から見ると、パーティション テーブルは単なるテーブルです。

B は 2017 パーティションのみを操作しますが、A はテーブル t 全体の MDL ロックを保持しているため、B の alter ステートメントがブロックされます。共通のシャード テーブルを使用すると、別のシャード テーブルのクエリ ステートメントとの MDL 競合は発生しません。

まとめ：

• mysqlがパーティションテーブルを初めて開くときは、すべてのパーティションにアクセスする必要がある。
• サーバーレベルでは、これは同じテーブルとみなされるため、すべてのパーティションがMDLロックを共有します。
• エンジン レベルでは、これらは異なるテーブルと見なされるため、MDL ロック後は、パーティション テーブル ルールに従って必要なパーティションのみにアクセスされます。

パーティションテーブルの適用シナリオ

パーティション テーブルの利点は、ビジネスに対して透過的であることです。ユーザー パーティション テーブルと比較すると、パーティション テーブルを使用するビジネス コードはよりシンプルになり、パーティション テーブルでは履歴データを簡単にクリーンアップできます。

alter table t drop partition 操作はパーティション ファイルを削除します。その効果は drop と似ています。delete と比較すると、速度が速く、システムへの影響が少ないという利点があります。

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