8.4.4 MySQL が内部一時テーブルを使用する仕組み

場合により、サーバーはクエリーの処理中に内部一時テーブルを作成します。それらのテーブルは、メモリー内に保持して、MEMORY ストレージエンジンによって処理したり、ディスク上に格納して、MyISAM ストレージエンジンによって処理したりできます。サーバーは最初にインメモリーテーブルとして内部で一時テーブルを作成し、それが大きくなりすぎた場合に、それをディスク上テーブルに変換することがあります。サーバーが内部一時テーブルを作成するタイミングや、サーバーがそれを管理するためにどのストレージエンジンを使用するかに関して、ユーザーは直接制御できません。

一時テーブルは、次のような条件で作成される可能性があります。

• UNION クエリーが一時テーブルを使用します。

• TEMPTABLE アルゴリズムを使用して評価されるものや、UNION またはアグリゲーションを使用するものなど、一部のビューで一時テーブルを必要とします。

• ORDER BY 句と別の GROUP BY 句がある場合、または、ORDER BY または GROUP BY に結合キュー内の最初のテーブルと異なるテーブルのカラムが含まれている場合は、一時テーブルが作成されます。

• DISTINCT と ORDER BY の組み合わせで、一時テーブルが必要になることがあります。

• SQL_SMALL_RESULT オプションを使用すると、MySQL では、クエリーにディスク上ストレージを必要とする要素 (後述) も含まれていないかぎり、インメモリー一時テーブルが使用されます。

• 複数テーブル UPDATE ステートメント。

• GROUP_CONCAT() または COUNT(DISTINCT) 評価。

• 派生テーブル (FROM 句内のサブクエリー)。

• サブクエリーまたは準結合実体化のために作成されるテーブル。

クエリーで一時テーブルを必要とするかどうかを判断するには、EXPLAIN を使用し、Extra カラムをチェックして、そこに Using temporary と示されているかどうかを確認します (セクション8.8.1「EXPLAIN によるクエリーの最適化」を参照してください)。EXPLAIN では、派生されるか、実体化された一時テーブルに対して、必ずしも Using temporary と表示しないことがあります。

内部一時テーブルが最初にインメモリーテーブルとして作成されたが、これが大きくなりすぎた場合、MySQL はこれを自動的にディスク上のテーブルに変換します。インメモリー一時テーブルの最大サイズは、tmp_table_size と max_heap_table_size の最小値です。これは、CREATE TABLE によって明示的に作成された MEMORY テーブルと異なります。そのようなテーブルの場合、max_heap_table_size システム変数でのみ、テーブルがどのくらい拡大でき、ディスク上フォーマットへの変換がないことが判断されます。

サーバーは内部一時テーブル (メモリー内またはディスク上のいずれか) を作成すると、Created_tmp_tables ステータス変数を増分します。サーバーはディスク上にテーブルを作成する (内部で、またはインメモリーテーブルを変換して) 場合、Created_tmp_disk_tables ステータス変数を増分します。

状況によっては、インメモリー一時テーブルの使用が妨げられる場合があり、その場合サーバーは代わりにディスク上テーブルを使用します。

• テーブル内の BLOB または TEXT カラムの存在

• GROUP BY または DISTINCT 句内の、バイナリ文字列の場合に 512 バイトまたは非バイナリ文字列の場合に 512 文字より大きい文字列カラムの存在。(MySQL 5.6.15 より前のこの制限は、文字列の型に関係なく 512 バイトです。)

• UNION または UNION ALL が使用された場合に、SELECT リスト内の 512 (バイナリ文字列の場合はバイト数、非バイナリ文字列の場合は文字数) より大きい最大長を持つ文字列カラムの存在。