Posted inMySQL

MySQLのTemporary Tablesを最適化する：複雑なクエリ処理時のI/Oボトルネックを防ぐ

MySQL tutorial - IT technology blog
MySQL tutorial - IT technology blog

クエリが30秒かかり、サーバーがフリーズ――よくある話

この問題に初めて遭遇したのは、e-commerceシステムのレポートクエリが30〜40秒かかるようになったときでした。MySQLサーバー自体は普段問題なく動いていましたが、月末のレポート出力のタイミングになると、CPUが急上昇してGrafana上のディスクI/Oが真っ赤に。お客さんからウェブが遅いと苦情が来て、タイムアウトまで発生する始末でした。

SHOW PROCESSLISTを実行すると、数十のクエリがCreating sort indexやCopying to tmp table on diskの状態で詰まっていました。そこで問題の本質が見えてきました――MySQLがtemporary tables（一時テーブル）を作成し、RAMに保持するのではなくディスクに書き出していたのです。

以前に深夜3時にデータベースが壊れてバックアップからリストアする羽目になって以来、毎日バックアップを確認するようになりました。しかしtemporary tablesのパフォーマンス問題はそれとは別物です。サーバーが即座にクラッシュするわけではなく、ユーザーが我慢できなくなるまで少しずつ遅くなっていくのです。

MySQLが一時テーブルを使う理由とディスクに書き出されるタイミング

MySQLは様々なクエリに対して内部的にtemporary tablesを自動生成します。インデックスのない列へのGROUP BY・ORDER BY、DISTINCT、FROM句内のサブクエリ、UNION、その他いくつかのケースが対象です。

最初はMEMORYエンジンを使ってRAMに一時テーブルを保持しようとします。しかし設定の上限を超えると、MySQLはMyISAM（MySQL 8.0以降はInnoDB）に変換してディスクに書き出します。ディスクI/OはRAMより何百倍も遅いため、ここでパフォーマンスが一気に落ちます。

この上限を決める変数が2つあります：

  • tmp_table_size — RAM上の内部一時テーブル1つあたりの最大サイズ
  • max_heap_table_size — MEMORYエンジンのテーブルの最大サイズ

MySQLはこの2つの変数のうち小さい方の値を実際の上限として使用します。デフォルトはどちらも通常16MB――大規模データセットに対する複雑なクエリには低すぎます。今すぐ確認してみましょう：

SHOW VARIABLES LIKE 'tmp_table_size';
SHOW VARIABLES LIKE 'max_heap_table_size';

一時テーブルが問題を引き起こしているか診断する

何かを変更する前に、これが本当の原因であることを確認しておく必要があります。SHOW STATUSでサーバー全体の統計を確認しましょう：

SHOW GLOBAL STATUS LIKE 'Created_tmp%';

出力結果は以下のようになります：

+-------------------------+-------+
| Variable_name           | Value |
+-------------------------+-------+
| Created_tmp_disk_tables | 1847  |
| Created_tmp_files       | 23    |
| Created_tmp_tables      | 9341  |
+-------------------------+-------+

Created_tmp_disk_tables / Created_tmp_tablesの比率を計算します。この割合が10〜15%を超えていれば問題があります。上記の例では1847/9341 ≈ 19.7%――かなり高い値です。特定の時間帯を計測するためにカウンターをリセットしましょう：

FLUSH STATUS;
-- 数時間待ってから再度計測する
SHOW GLOBAL STATUS LIKE 'Created_tmp%';

個別のクエリについては、EXPLAINを使って一時テーブルが作成されているか確認します：

EXPLAIN SELECT category, COUNT(*), AVG(price)
FROM orders
GROUP BY category
ORDER BY AVG(price) DESC;

結果のExtra列にUsing temporary; Using filesortと表示されていれば、そのクエリは一時テーブルを作成してディスク上でデータをソートしています。

解決策――シンプルなものから深掘りするものまで

1. RAM上の一時テーブルの上限を増やす

最も手っ取り早い方法です。/etc/mysql/my.cnfを編集します：

[mysqld]
tmp_table_size = 256M
max_heap_table_size = 256M

または、再起動なしで即時反映させることもできます（新しいセッションにのみ有効）：

SET GLOBAL tmp_table_size = 268435456;
SET GLOBAL max_heap_table_size = 268435456;

重要な注意点：この値は同時接続数と掛け合わさります。100接続それぞれが256MBの一時テーブルを使用すると、理論上のRAM消費は25GBに達します。16GB RAMのサーバーで同時接続50〜100程度の場合、私は128〜256MBに設定することが多いです。参考となる計算式：

tmp_table_size ≤ (RAM × 0.25) ÷ max_connections

2. クエリを書き直して一時テーブルの生成を避ける

こちらのほうが根本的な解決策です。多くの場合、一時テーブルが生成されるのはクエリが最適化されていないことが原因です。

GROUP BY時にSELECT *を避ける：

-- 悪い例：すべての列を一時テーブルに引き込んでからグループ化する
SELECT * FROM orders GROUP BY category;

-- 良い例：必要な列だけを取得する
SELECT category, COUNT(*), SUM(amount)
FROM orders
GROUP BY category;

FROM句のサブクエリをCTE（MySQL 8.0+）に置き換える：

-- 旧来の方法：derived table = 隠れた一時テーブル
SELECT u.name, t.total
FROM users u
JOIN (
  SELECT user_id, SUM(amount) AS total
  FROM orders
  GROUP BY user_id
) t ON u.id = t.user_id;

-- CTEを使った改善版 — MySQL 8.0のオプティマイザがより効率的に処理できる
WITH order_totals AS (
  SELECT user_id, SUM(amount) AS total
  FROM orders
  GROUP BY user_id
)
SELECT u.name, ot.total
FROM users u
JOIN order_totals ot ON u.id = ot.user_id;

3. 適切な場所にインデックスを追加してMySQLが一時テーブルをスキップできるようにする

MySQLがtemporary tableを作成する主な原因の多くは、ORDER BYやGROUP BYに適したインデックスがないことです。複合インデックスを追加することで多くの場合は解決できます：

-- 一時テーブルが作成されやすいクエリ
SELECT category, status, COUNT(*)
FROM orders
WHERE created_at > '2024-01-01'
GROUP BY category, status;

-- WHERE句とGROUP BY両方の列を含む複合インデックスを追加する
ALTER TABLE orders
ADD INDEX idx_date_cat_status (created_at, category, status);

インデックスを追加したら、EXPLAINを再実行してExtra列からUsing temporaryが消えたか確認しましょう。

4. TempTableエンジンを使う（MySQL 8.0+）

MySQL 8.0では、古いMEMORYエンジンの代わりにTempTableエンジンが導入されました。TempTableはVARCHAR、TEXT、BLOBといった型に対してより効率的で、MEMORYエンジンでは早期にディスクへ書き出されがちだったケースも改善されています。

-- 現在使用しているエンジンを確認する
SHOW VARIABLES LIKE 'internal_tmp_mem_storage_engine';

-- TempTableを使用するように設定する（MySQL 8.0のデフォルト）
SET GLOBAL internal_tmp_mem_storage_engine = 'TempTable';

-- TempTable用のRAMプールの上限を設定する（デフォルト1GB）
SET GLOBAL temptable_max_ram = 1073741824;

ベストな方法：計測→最適化→再計測のサイクルで取り組む

実際のところ、あらゆるケースに対応できる唯一の解決策はありません。私が普段行っている手順は以下のとおりです：

  1. ベースラインの計測：FLUSH STATUSを実行してサーバーを24時間稼働させ、Created_tmp_disk_tables / Created_tmp_tablesを再計測します
  2. 問題のあるクエリを特定する：my.cnfでlog_queries_not_using_indexes = ONを設定してスロークエリログを有効にし、最も頻繁に一時テーブルを作成しているクエリを見つけます
  3. まずクエリを最適化する：インデックスの追加やクエリの書き直しが可能か検討します――このステップが最もROIの高い場合が多いです
  4. 設定を調整する：クエリの最適化を終えてから、避けられないケースに対応するためにtmp_table_sizeを引き上げます
  5. 再度モニタリングする：数日後に再計測し、ディスク一時テーブルの比率が改善前後でどう変化したか比較します

この最適化を行った結果、システムのCreated_tmp_disk_tables / Created_tmp_tables比率は約20%から3%未満まで改善されました。月次レポートのクエリは30秒から2〜3秒に短縮され、Grafana上のディスクI/Oはその日のうちに正常値に戻りました。

覚えておきたいのは、一時テーブルがディスクに書き出されることが常に悪いわけではないということです。本当に大きなデータセットに対しては、完全に避けることができない場合もあります。目標は完全な排除ではなく、その頻度とサイズを許容できるレベルまで抑えることです。

Share:
Tags: