MySQL内部一時テーブルの具体的な使用法

MySQL内部一時テーブルの具体的な使用法

連合

UNIONセマンティクス: 2つのサブクエリの結果を結合し、重複行を1行だけ保持します。

テーブルの初期化

テーブル t1(id INT 主キー、a INT、b INT、インデックス(a)) を作成します。
区切り文字 ;;
CREATE PROCEDURE idata()
始める
    i INT を宣言します。

    i=1 を設定します。
    (i<= 1000) の場合
        t1 VALUES (i,i,i) に挿入します。
        i=i+1 を設定します。
    終了しながら;
終わり;;
区切り文字 ;
idata() を呼び出します。

ステートメントの実行

(SELECT 1000 AS f) UNION (SELECT id FROM t1 ORDER BY id DESC LIMIT 2);

mysql> EXPLAIN (SELECT 1000 AS f) UNION (SELECT id FROM t1 ORDER BY id DESC LIMIT 2);
+----+--------------+-------------+-----------+--------+---------------+---------+-------+------+-------+---------+----------------------------------+
| id | select_type | テーブル | パーティション | タイプ | 可能なキー | キー | キー長 | ref | 行 | フィルター済み | 追加 |
+----+--------------+-------------+-----------+--------+---------------+---------+-------+------+-------+---------+----------------------------------+
| 1 | PRIMARY | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | テーブルは使用されていません |
| 2 | UNION | t1 | NULL | index | NULL | PRIMARY | 4 | NULL | 2 | 100.00 | 後方インデックススキャン。インデックスを使用 |
| NULL | UNION RESULT | <union1,2> | NULL | ALL | NULL | NULL | NULL | NULL | NULL | NULL | 一時を使用 |
+----+--------------+-------------+-----------+--------+---------------+---------+-------+------+-------+---------+----------------------------------+

2行目のKey=PRIMARYUsing temporary

  • サブクエリの結果に対してUNION RESULT実行するときに一時テーブルが使用されることを示します。

連合の結果

  • 一時メモリ テーブルを作成します。この一時メモリ テーブルには整数フィールド f が 1 つだけあり、f が主キーです。
  • 最初のサブクエリを実行し、1000を取得してメモリ内の一時テーブルに格納します。
  • 2番目のサブクエリを実行する
    • id=1000 の最初の行を取得し、それを一時的なインメモリ テーブルに挿入しようとしますが、値 1000 は一時的なインメモリ テーブルに既に存在します。
      • 一意制約に違反したため、挿入に失敗しました。実行を続行します。
    • 2行目のid=999を取得し、一時メモリテーブルに正常に挿入します。
  • メモリ内の一時テーブルから行ごとにデータを取得し、結果を返して、メモリ内の一時テーブルを削除します。結果には、id=1000 と id=999 の 2 つの行が含まれます。
  • 一時メモリテーブルは、データを一時的に保存する役割を果たします。また、一時メモリテーブルの主キー ID の一意性制約を使用して、UNION のセマンティクスを実装します。

ユニオンオール

UNION ALLは重複排除セマンティクスがありません。サブクエリは 1 回実行され、メモリ内に一時テーブルを必要とせずに結果がクライアントに直接送信されます。

mysql> EXPLAIN (SELECT 1000 AS f) UNION ALL (SELECT id FROM t1 ORDER BY id DESC LIMIT 2);
+----+-------------+--------+-----------+---------+-------+---------------+---------+-------+-------+--------+---------+----------------------------------+
| id | select_type | テーブル | パーティション | タイプ | 可能なキー | キー | キー長 | ref | 行 | フィルター済み | 追加 |
+----+-------------+--------+-----------+---------+-------+---------------+---------+-------+-------+--------+---------+----------------------------------+
| 1 | PRIMARY | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | テーブルは使用されていません |
| 2 | UNION | t1 | NULL | index | NULL | PRIMARY | 4 | NULL | 2 | 100.00 | 後方インデックススキャン。インデックスを使用 |
+----+-------------+--------+-----------+---------+-------+---------------+---------+-------+-------+--------+---------+----------------------------------+

グループ化

十分なメモリ

-- 16777216 バイト = 16 MB
mysql> '%tmp_table_size%' のような変数を表示します。
+----------------+----------+
| 変数名 | 値 |
+----------------+----------+
| tmp_table_size | 16777216 |
+----------------+----------+

ステートメントの実行

-- MySQL 5.6 で実行mysql> EXPLAIN SELECT id%10 AS m, COUNT(*) AS c FROM t1 GROUP BY m;
+----+-------------+-------+-------+-------+---------------+-------+------+------+----------------------------------------------+
| id | select_type | テーブル | タイプ | possible_keys | key | key_len | ref | 行 | 追加 |
+----+-------------+-------+-------+-------+---------------+-------+------+------+----------------------------------------------+
| 1 | SIMPLE | t1 | index | PRIMARY,a | a | 5 | NULL | 1000 | インデックスを使用; 一時を使用; ファイルソートを使用 |
+----+-------------+-------+-------+-------+---------------+-------+------+------+----------------------------------------------+

mysql> SELECT id%10 AS m, COUNT(*) AS c FROM t1 GROUP BY m;
+------+-----+
| m | c |
+------+-----+
| 0 | 100 |
| 1 | 100 |
| 2 | 100 |
| 3 | 100 |
| 4 | 100 |
| 5 | 100 |
| 6 | 100 |
| 7 | 100 |
| 8 | 100 |
| 9 | 100 |
+------+-----+

Using index : カバーリング インデックスが使用され、インデックス a が選択され、テーブルを返す必要がないことを示します。

一時テーブルの使用: Using temporaryことを示します

Using filesort : ソートが必要であることを示します

実行プロセス

  • 2つのフィールドmとcを持つ一時メモリテーブルを作成します。mは主キーです。
  • t1のインデックスaをスキャンし、リーフノード上のid値を1つずつ取り出し、id%10を計算し、xとして記録します。
    • 一時メモリテーブルに主キーxを持つ行がない場合、行(x,1)を挿入します。
    • 一時メモリ テーブルに主キー x を持つ行がある場合は、行 x の c 値に 1 を加算します。
  • トラバーサルが完了したら、フィールド m に従ってソートし、結果セットを取得してクライアントに返します。

選別プロセス

NULL による順序

-- 最終的なソート段階をスキップし、一時テーブルから直接データを取得します。mysql> EXPLAIN SELECT id%10 AS m, COUNT(*) AS c FROM t1 GROUP BY m ORDER BY NULL;
+----+-------------+-------+-------+---------------+-------+-------+------+------------------------------+
| id | select_type | テーブル | タイプ | possible_keys | key | key_len | ref | 行 | 追加 |
+----+-------------+-------+-------+---------------+-------+-------+------+------------------------------+
| 1 | SIMPLE | t1 | インデックス | PRIMARY,a | a | 5 | NULL | 1000 | インデックスを使用; 一時を使用 |
+----+-------------+-------+-------+---------------+-------+-------+------+------------------------------+

-- t1 のデータは 1 から始まります。mysql> SELECT id%10 AS m, COUNT(*) AS c FROM t1 GROUP BY m ORDER BY NULL;
+------+-----+
| m | c |
+------+-----+
| 1 | 100 |
| 2 | 100 |
| 3 | 100 |
| 4 | 100 |
| 5 | 100 |
| 6 | 100 |
| 7 | 100 |
| 8 | 100 |
| 9 | 100 |
| 0 | 100 |
+------+-----+

メモリ不足

tmp_table_size を 1024 に設定します。

ステートメントの実行

-- メモリ一時テーブルの上限は 1024 バイトですが、メモリ一時テーブルは 100 行のデータを完全に保持することはできません。メモリ一時テーブルはディスク一時テーブルに変換され、InnoDB エンジンがデフォルトで使用されます。 -- t1 が非常に大きい場合、このクエリに必要なディスク一時テーブルは大量のディスク領域を占有します。 mysql> SELECT id%100 AS m, count(*) AS c FROM t1 GROUP BY m ORDER BY NULL LIMIT 10;
+------+----+
| m | c |
+------+----+
| 1 | 10 |
| 2 | 10 |
| 3 | 10 |
| 4 | 10 |
| 5 | 10 |
| 6 | 10 |
| 7 | 10 |
| 8 | 10 |
| 9 | 10 |
| 10 | 10 |
+------+----+

最適化計画

インデックスを最適化する

一時テーブルがメモリ内で使用されるかディスク上で使用されるかに関係なく、 GROUP BY一意のインデックスを持つテーブルを構築する必要があり、実行コストが高くなります。

一時テーブルが必要な理由: 各行のid%100は順序が正しくないため、結果を記録してカウントするには一時テーブルが必要です。

入力データが順序どおりであることを保証できる場合は、 GROUP BYを計算するときに、左から右にスキャンして 1 つずつ蓄積するだけで済みます。

  • 最初の1に遭遇すると、X個の0が蓄積され、結果セットの最初の行は(0,X)
  • 最初の2つに遭遇すると、Y個の1が蓄積され、結果セットの最初の行は(1,Y)
  • プロセス全体で一時テーブルやソートは必要ありません。
-- MySQL 5.7 で ALTER TABLE t1 ADD COLUMN z INT GENERATED ALWAYS AS(id % 100), ADD INDEX(z) を実行します。
-- カバーリング インデックスが使用され、一時テーブルやソートは必要ありません。mysql> EXPLAIN SELECT z, COUNT(*) AS c FROM t1 GROUP BY z;
+----+-------------+--------+-----------+---------+---------------+-------+--------+----------+-----------+-------------+
| id | select_type | テーブル | パーティション | タイプ | 可能なキー | キー | キー長 | ref | 行 | フィルター済み | 追加 |
+----+-------------+--------+-----------+---------+---------------+-------+--------+----------+-----------+-------------+
| 1 | SIMPLE | t1 | NULL | インデックス | z | z | 5 | NULL | 1000 | 100.00 | インデックスを使用 |
+----+-------------+--------+-----------+---------+---------------+-------+--------+----------+-----------+-------------+
2

直接ソート

GROUP BYステートメントで大量のデータを一時テーブルに格納する必要がある場合は、まずメモリ内の一時テーブルに格納し、その後ディスク上の一時テーブルに縮退する方が適切です。

一時ディスク テーブルを直接使用し、 GROUP BYステートメントでSQL_BIG_RESULTを使用できます (関連するデータの量が非常に大きいことをオプティマイザーに通知するため)。

ディスク一時テーブルは元々 B+ ツリー ストレージを使用しますが、これは配列ストレージほど効率的ではありません。オプティマイザーはSQL_BIG_RESULTを検出すると、配列ストレージを直接使用します。

  • つまり、一時テーブルの使用をあきらめて、直接ソート段階に進むということです。

実行プロセス

-- 一時テーブルは使用されませんが、ソート アルゴリズムは直接使用されます。mysql> EXPLAIN SELECT SQL_BIG_RESULT id%100 AS m, COUNT(*) AS c FROM t1 GROUP BY m;
+----+-------------+-------+-------+-------+---------------+-------+------+------+------+-----------------------------+
| id | select_type | テーブル | タイプ | possible_keys | key | key_len | ref | 行 | 追加 |
+----+-------------+-------+-------+-------+---------------+-------+------+------+------+-----------------------------+
| 1 | SIMPLE | t1 | index | PRIMARY,a | a | 5 | NULL | 1000 | インデックスを使用; ファイルソートを使用 |
+----+-------------+-------+-------+-------+---------------+-------+------+------+------+-----------------------------+

sort_bufferを初期化し、そこに整数フィールド(mと表記)を配置します。

t1のインデックスaをスキャンし、その中のid値を1つずつ取り出し、id%100の値をsort_bufferに格納します。

スキャンが完了したら、 sort_bufferのフィールド m をソートします (sort_buffer のメモリが不足している場合は、ディスク上の一時ファイルを使用してソートが行われます)

ソート後、順序付けられた配列が得られます。順序付けられた配列を走査して、各値が出現する回数を取得します (上記のインデックスを最適化する方法と同様)。

DISTINCTとの比較

-- 標準SQL、SELECT部分​​に集計関数COUNT(*)を追加します
SELECT a,COUNT(*) FROM t GROUP BY a ORDER BY NULL;
-- 非標準SQL
SELECT a FROM t GROUP BY a ORDER BY NULL;

SELECT DISTINCT a FROM t;

標準SQL: フィールド a でグループ化し、各グループに a が出現する回数を数える

非標準SQL: COUNT(*)は不要になり、合計計算ロジックを実行する必要がなくなりました。

  • フィールド a でグループ化し、同じ値 a を持つ 1 行のみを返します。これはDISTINCTセマンティクスと一致します。

集計関数が必要ない場合、 DISTINCTGROUP BYのセマンティクス、実行フロー、パフォーマンスは同じです。

  • フィールド a を持つ一時テーブルを作成し、このフィールド a に一意のインデックスを作成します。
  • テーブルtを走査し、データを1つずつ取り出して一時テーブルに挿入します。
    • 一意のキーの競合が見つかった場合はスキップします
    • それ以外の場合は挿入は成功です
  • トラバーサルが完了すると、一時テーブルが結果セットとしてクライアントに返されます。

まとめ

  • 内部一時テーブルが使用されるシナリオ
    • ステートメントが実行中に同時にデータを読み取り、結果を取得できる場合、追加のメモリは必要ありません。
    • それ以外の場合は、中間結果を保存するために追加のメモリが必要になります。
  • join_bufferは順序付けされていない配列、 sort_bufferは順序付けされた配列、一時テーブルは2次元のテーブル構造です。
  • 実行ロジックで2次元テーブル機能の使用が必要な場合は、一時テーブルが推奨されます。GROUP GROUP BYステートメントの結果に明示的なソート要件がない場合は、 ORDER BY NULLを追加します(MySQL 5.6)
  • GROUP BYプロセスでインデックスを使用して、EXPLAIN 結果にUsing temporaryおよびUsing filesort
  • GROUP BYでカウントするデータの量が多くない場合は、一時メモリテーブルを使用するようにしてください( tmp_table_size適切に増やすことができます)
  • データの量が大きすぎる場合は、 SQL_BIG_RESULTを使用して、オプティマイザーにソート アルゴリズムを直接使用するように指示します (一時テーブルをスキップします)。

参考文献

「MySQL実践45講義」

これで、MySQL 内部一時テーブルの具体的な使用法に関するこの記事は終了です。MySQL 内部一時テーブルに関するより詳しい情報は、123WORDPRESS.COM の過去の記事を検索するか、以下の関連記事を引き続き参照してください。今後とも 123WORDPRESS.COM をよろしくお願いいたします。

以下もご興味があるかもしれません:
  • MySQL でよく使用されるデータベースとテーブル シャーディング ソリューションの概要
  • MySQLパーティションテーブルは月別に分類されています
  • MySQLはデータテーブル内の既存のテーブルを分割します
  • MySQL テーブル分割後にスムーズにオンラインになる方法
  • MySQLシリーズ マルチテーブル結合クエリ92および99構文例詳細チュートリアル
  • MySQLが内部一時テーブルを使用するタイミングについて簡単に説明します。
  • Python で MySQL テーブル情報を収集して集計する詳細な例
  • MYSQLテーブルの包括的な概要

<<:  VMware 仮想マシンに固定 IP アドレスを設定する方法 (グラフィック チュートリアル)

>>:  太陽系の惑星のアニメーション効果を実現するHTML+CSS3コード

推薦する

Dockerはローカルディレクトリとデータボリュームコンテナ操作をマウントします

1. DockerはローカルディレクトリをマウントしますDocker は、ホスト上のディレクトリをイ...

Vue 親コンポーネントが子コンポーネント関数の実装を呼び出す

Vue親コンポーネントは子コンポーネントの関数を呼び出す親コンポーネントはイベントを通じて子コンポー...

a href=# と a href=javascript:void(0) の違いの詳細な説明

a href="#"> リンクをクリックすると、ページがページ上部までスク...

Windows での MySQL インストール チュートリアル (画像とテキスト付き)

MySQL インストール手順 MySQL は、スウェーデンの MySQL AB によって開発された...

Homebrewを使用してMacにMySQLをインストールするときにログインできない問題を解決する

お使いのコンピュータが Mac の場合、homebrew を使用して MySQL をインストールする...

テーブルの最大幅と最小幅を設定する際の互換性の問題と解決策の詳細な説明

テーブル内の min-width と max-width プロパティの設定 <テーブル>...

HTML テーブルタグチュートリアル (24): 行の水平方向の配置属性 ALIGN

水平方向では、行の配置を左、中央、右に設定できます。基本的な構文<TR ALIGN="...

MySQL ストアド プロシージャのクエリ コマンドの概要

以下のように表示されます。 mysql.proc から名前を選択します (db='データベー...

Mysql のフィールドのデータの一部をバッチ置換する (推奨)

MYSQL のフィールドのデータの一部をバッチで置き換えます。具体的な導入は次のとおりです。 1....

CSS3 フィルターを使用して PNG 画像の色を変更するサンプル コード

この方法は、CSS3のdrop-shadow filterを使用して、png画像の不透明部分に任意の...

Bツリーの削除プロセスの紹介

前回の記事 https://www.jb51.net/article/154157.htm では、B...

現在のブラウザが JavaScript でヘッドレス ブラウザであるかどうかを検出する方法

目次ヘッドレスブラウザとは何ですか?なぜ「ヘッドレス」ブラウザと呼ばれるのでしょうか?ヘッドレスブラ...

CSS はコンテナ レベル (div...) タグを 1 つの位置 (ページの右端) に固定します。

コードは次のようになります。 。プロセス{ 境界線:1px 実線 #B7B7B8; 背景:#F8F8...

LinuxにPHP7をインストールする方法の詳細な説明

Linux に PHP7 をインストールするにはどうすればいいですか? 1. 依存パッケージをインス...

デザイン理論:人間中心のグリーンデザイン

「人間中心」と「グリーンデザイン」という2つの視点から考える——デザイン業界の同僚とも議論する2つの...