aeroastroの日記

Over the Sky, Into the Future.

MySQLのUsing Temporaryについて

Using Temporary とは

MySQLにおいて、EXPLAIN文を用いてクエリの実行計画を見るときに、この文字列が出る可能性があります。例えば、以下のような場合です。

mysql> CREATE TABLE `books` (
    ->   `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
    ->   `title` varchar(255) NOT NULL,
    ->   `author` varchar(255) NOT NULL,
    ->   `description` varchar(255) NOT NULL,
    ->   `extra` TEXT,
    ->   `released_at` datetime NOT NULL,
    ->   PRIMARY KEY (`id`)
    -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.01 sec)

> EXPLAIN SELECT author, COUNT(title) FROM books GROUP BY author;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+---------------------------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra                           |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+---------------------------------+
|  1 | SIMPLE      | books | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    1 |   100.00 | Using temporary; Using filesort |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+---------------------------------+
1 row in set, 1 warning (0.00 sec)

MySQL 5.7 Reference Manual を見ると以下のように書かれています。

MySQL :: MySQL 5.7 Reference Manual :: 9.8.2 EXPLAIN Output Format

To resolve the query, MySQL needs to create a temporary table to hold the result. This typically happens if the query contains GROUP BY and ORDER BY clauses that list columns differently.

Using TemporaryはMySQLのクエリ実行計画において、結果を保持するために一時テーブルを作成する必要があることを示しています。

Webアプリケーションなど、それなりに高いパフォーマンスが求められる場合、一般的にはUsing Temporaryが出た時点でクエリの改善が求められます。クエリそのものを、効率的なクエリに置き換える作業や、データを利用しているアプリケーション側におけるロジックを含めた改善が必要です。

この記事では、クエリ最適化の話は一旦おいておいて、一時テーブルの構造と仕組みについて簡単にまとめたいと思います。

テーブルの種類とデータの形式について

一時テーブルとして実際に作られるテーブルは2種類であり、一時テーブルとして利用される際に以下の特徴を持っています。

Memory Engine

  • オンメモリで動作
  • VARCHAR, VARBINARY等のカラムは最大長に合わせて、CHAR, BINARYなどの固定長として保存される。
  • テーブルサイズが肥大しすぎた場合には、自動的にMyISAMに変換される。

MyISAM Engine

  • ディスク上で動作
  • VARCHAR, VARBINARY等のカラムは可変長で保存される。(MySQL 5.6.5以降のみ)

当然、Disk I/O の発生する MyISAM Engineの方がパフォーマンスが悪いのですが、それ以上に悪いのはMemory Engineとして確保されつつ、データサイズの閾値を超えてしまった為に、MyISAM Engineへと変換される場合です。では、この2つの使い分けはどのようになっているのでしょうか。

テーブルの使い分けについて

以下に、MyISAM Engineが利用される条件を示します。

クエリに起因するもの

  • BLOB、または TEXT が含まれる
  • 512 バイト、もしくは、512文字以上のカラムが、 GROUP BY もしくは、 DISTINCT に含まれる
  • UNION、もしくは、UNION ALL が利用された際に、512バイト、もしくは、512文字以上のカラムが含まれる
  • SHOW COLUMNS および DESCRIBE が利用されたとき

テーブルサイズに起因するもの

  • tmp_table_size を超過したもの (デフォルト値は16MB)
  • max_heap_table_size を超過したもの (デフォルト値は16MB)

設定値に起因するもの

  • big_tables オプションが ONであるとき

生成されるテーブルの構造を考えると、納得のいく条件から出来ています。 オンメモリで回したいのであれば、カラム長を一定以下に制限し、tmp_table_size および max_heap_table_size を増加させることが正攻法です。 逆に、メモリが足りないほど大規模なテーブルが生成されるのであれば big_tablesON にして(これはセッション内部だけでも可能)、初めからDiskを利用したほうがパフォーマンスが僅かに向上します。

一時テーブルの生成状況を計測する手段

一時テーブルが生成された際には、サーバーステータス変数の Created_tmp_tables および Created_tmp_disk_tables から知ることができます。前者は生成された一時テーブルの総数を、後者はそのうちDisk上に生成されたものの数を示しています。

mysql> SHOW GLOBAL STATUS WHERE Variable_name IN ('Created_tmp_tables', 'Created_tmp_disk_tables');
+-------------------------+-------+
| Variable_name           | Value |
+-------------------------+-------+
| Created_tmp_disk_tables | 0     |
| Created_tmp_tables      | 27    |
+-------------------------+-------+
2 rows in set (0.00 sec)

この値が妙に増えるようであれば、何らかの原因究明や対策を打っておいたほうがいいかもしれません。

まとめと今後の課題

ということで、本記事では一時テーブルの中身についてザックリとまとめてみました。 一時テーブルといっても、特定のEngineが使われているだけで、大したことはありません。とはいえ、メモリ上にテーブルが作成されたり、Disk I/Oが発生するなど、パフォーマンスの観点からは、あまり望ましくないのは確かです。

パフォーマンスに配慮したクエリを作っている限りは、なかなかUsing Temporaryにお目にかかることは無いのですが、Webアプリケーションで利用しているORMなどは、一時テーブルを作成するようなクエリを生成していることがあります。

例えば、 Ruby on RailsActiveRecord がテーブルのカラムを取得するために利用している SHOW FULL FIELDS FROM @table_name は典型的な例で、MyISAMの一時テーブルを生成しています。今回は一時テーブルの概要をまとめましたが、次の記事では、ORMが生成するクエリについての考察を書きたいと思います。

参考

MySQL :: MySQL 5.7 Reference Manual

初ブログ

エンジニアとして、ブログを書いていないことによる潜在的な損失が大きいと感じたので、真面目に書いてみることにしました。

更新頻度が低かったり、内容が稚拙だったりするかもしれませんが、生暖かい目で見ていただけると助かります。