temporary table を試してみる（前篇：SQLの速度アップ）

こんにちは、tatsumi です。

やっと関西地方も遅い梅雨明けを迎えました。
暑さが厳しくなっておりますが、みなさまはいかがお過ごしでしょうか。
私はといえば、毎年恒例の富士登山へ行ってまいりました。
天気予報によると「曇り一時雨か雷雨」という状態でしたが、
5合目まで行ったら綺麗な空！
「なんだかー、行けそうな気がするー！！」ということで決行したものの、
頂上へ辿り着く直前に雨が降ってきてご来光が全く見れず。
山頂は暴風雨で身の危険を感じ、さっさと下山してきました。
「自然は壮大で美しい。だけれども、厳しい」
そんなことを改めて認識した夏でした。

ところで、今日は MySQL のTEMPORARY TABLEについて書いてみようと思います。
TEMPORARY TABLE とはずばり一時テーブルのことで、マニュアルによると

テーブルを作成する時、TEMPORARY キーワードを利用する事ができます。TEMPORARY テーブルは現在の接続でのみ現れ、接続が終了すると自動的にドロップされます。これは、2つの異なる接続同士、または、既存の同名の非TEMPORARY テーブルとお互いに対立する事無く、同じテンポラリ テーブル名を利用する事ができるという意味になります。(テンポラリ テーブルがドロップされるまで、既存テーブルは隠されています。)テンポラリ テーブルを作成する為には CREATE TEMPORARY TABLES 特権を持つ必要があります。

というもの。
作り方は簡単で、普通の CREATE TABLE 文に TEMPORARY キーワードをつけて
CREATE TEPMPORARY TABLE とするだけです。

「で？」と思われる方もいらっしゃいますが、
この TEMPORARY TABLE を上手く使うと色々なメリットがあります。
そのメリットとはざっくり2つ。

「SQLの速度アップ」と「ロックの回避」です。

今回は、SQLの速度アップについてご説明します。
複雑な集計などを行う際に、サブクエリを使うことがあると思います。
例えば、

select
  user.user_id,
  sammary_1.value,
  sammary_2.value,
  sammary_3.value
from
  user

-- 1列目に出したい集計値
left join (
  [サブクエリ]
) as summary_1
  on user.user_id = summary_1.user_id

-- 2列目に出したい集計値
left join (
  [サブクエリ]
) as summary_2
  on user.user_id = summary_2.user_id

-- 3列目に出したい集計値
left join (
  [サブクエリ]
) as summary_3
  on user.user_id = summary_3.user_id

といったような場合です。
このとき、全体のデータ量が少なければ大した問題にならないのですが、
ちょっとデータ量が多くなると急に遅くなります。
理由はEXPLAIN を実行すると一目瞭然ですが、
サブクエリの結果を結合する際に「条件のFULLスキャン」が発生するためです。

ここで TEMPORARY TABLE を使用します。

-- 1列目に出したい集計値
create temporary table summary_1(
PRIMARY KEY(user_id)
)
[サブクエリ];

-- 2列目に出したい集計値
create temporary table summary_2(
PRIMARY KEY(user_id)
)
[サブクエリ];

-- 3列目に出したい集計値
create temporary table summary_3(
PRIMARY KEY(user_id)
)
[サブクエリ];

-- 集計結果をまとめて表示する
select
  user.user_id,
  sammary_1.value,
  sammary_2.value,
  sammary_3.value
from
  user
left join summary_1
  on user.user_id = summary_1.user_id

left join summary_2
  on user.user_id = summary_2.user_id

left join summary_3
  on user.user_id = summary_3.user_id

実は TEMPORARY TABLE にも PRIMARY KEY などのインデックスを設定することが可能なので
上記のようにすれば、結合時にインデックスが使用されることになり
データ量が増えても比較的高速に検索することが可能になります。

もちろん、新規にテーブルを作成するコストやインデックスを作成するコストが新たに発生するので
上記のように書けば速くなるとは一概には言えません。
しかし、どうにも速度が出ないと悩んでいるのであれば試してみる価値があると思われます。

いかがだったでしょうか。
サブクエリを TEMPORARY TABLE に抜き出して
PRIMARY KEY を設定し、結合にインデックスを使用できるようにする。
あまり使用する頻度は高くないと思いますが、
こんなことができるというご紹介でした。

次回は TEMPORARY TABLE を使用して「ロックを回避する」ことについて書きます。