https://blog.s-style.co.jp/2022/04/8765/
MySQL では sort_buffer_size 以上にソート領域が必要になった場合、もしくは一部の条件に当てはまるSQLについては、内部一時テーブルを作成し処理を行うという仕組みになっています。
この内部一時テーブルの仕組みに MySQL 8.0 から TempTable ストレージエンジンを利用する事ができるようになりました。
TempTable ストレージエンジンの挙動については、過去の弊社ブログ記事で説明しています。
TempTable ストレージエンジンはMySQL 8.0で導入されたということもあり、初期パッチバージョンに比べて改善が行われています。
今回の記事では、TempTableストレージエンジンの最近追加された機能についてご紹介します。
基本的な動作は以下のようになります。
sort_buffer_size 分のスレッドバッファが割り当てられ、クイックソートを行うインメモリ内部一時テーブルを作成しソートを実行ディスク内部一時テーブルを作成しソートを実行3については密かに改善が加えられており、以前のバージョンでは設定した sort_buffer_size が一度に割り当てられていたため、平均的なソート量を sort_buffer_size が上回る場合無駄なメモリ割り当てが行われていましたが、MySQL 8.0.12 以降では段階的に必要量のバッファが割り当てられるようになっています。 このため、sort_buffer_sizeに大きい値を設定したとしても無駄なメモリの利用を抑えられるようになっています。
MySQL 8.0.12 の時点で、filesort 操作用のメモリーを取得するために、オプティマイザは、MySQL 8.0.12 より前に行われた一定量の sort_buffer_size バイトを割り当てるのではなく、 sort_buffer_size システム変数で指定されたサイズまで必要に応じて増分的にメモリーバッファを割り当てます。 これにより、ユーザーは小さいソートに過剰なメモリー使用を考慮せずに、大きいソートを高速化するために sort_buffer_size を大きい値に設定できます。 (この利点は、マルチスレッド malloc が弱い Windows での複数の同時ソートでは発生しない場合があります。)
前述のブログでも説明していますが、インメモリ内部一時テーブル は internal_tmp_mem_storage_engineパラメータによりMEMORY ストレージエンジン、もしくは TempTable ストレージエンジンを設定できます。
なお、ディスク内部一時テーブル のストレージエンジンも以前は選択できましたが、MySQL 8.0.16 以降、InnoDB に固定されています。