ど素人から毛を生やす。<延>

[MySQL]Havingで絞り込む場合は大人しく一時テーブルを使おうという話。

Web > Other 2023年6月13日(最終更新:1年前)

2023年6月13日に作成されたページです。
情報が古かったり、僕が今以上のど素人だった頃の記事だったりする可能性があります。

どもです。

あるGROUPに対し、日付の最大値が指定の期間内である、という抽出をしたくなりました。

HAVING MAX(日付) >= 期間初日 AND MAX(日付) < 期間末日+1

しかし、これを膨大なレコードに対して行った場合、めちゃくちゃ重くなることは明白です。
仮に100万レコードを対象にすれば、100万のGROUPが行われてからHAVINGが動くことになります。

そこで、対象「候補」レコードを前もって抽出することにしました。

SELECT parentID, childID
FROM   ☆☆
WHERE  日付 >= 期間初日 AND 日付 < 期間末日+1

この結果で絞り込み、HAVINGの前にWHEREでGROUPのIDを絞っておけば、100万のGROUPを行う心配はありません。

が、

ここで自分、見積もりが甘かった。
実際の運用では、この対象「候補」が5万件くらいあったのです。

つまり、100万のGROUPからのHAVINGは回避できた代わりに、5万のWHEREが発生してしまっていた
しかもテーブルの構造上、(parentID=** AND childID=**)OR(parentID=** AND childID=**)OR… と5万件続く、とんでもない事態に。
処理に10分くらいかかるバケモノSQLが生まれてしまいました。

じゃあこれどうすれば良かったの?
という問いに対する答えが、「いやテンポラリテーブル使えや」だったわけです。

CREATE TEMPORARY TABLE `tmp_table`(
	`parentID` INT(11) NOT NULL DEFAULT 0,
	`childID`  INT(11) NOT NULL DEFAULT 0,
	PRIMARY KEY (`parentID`, `childID`)
);

INSERT INTO tmp_table(`parentID`, `childID`)
SELECT `parentID`, `childID`
FROM  ☆☆
WHERE 日付 >= 期間初日 AND 日付 < 期間末日+1
GROUP BY `parentID`, `childID`

SELECT ***
FROM   tmp_table INNER JOIN ***
GROUP  tmp_table.`parentID`, tmp_table.`childID`
HAVING MAX(日付) >= 期間初日 AND MAX(日付) < 期間末日+1

これなら5万件まで絞った「候補」からJOINを始めるので、100万のGROUPも5万のWHEREも回避できます。
実際、これで10分かかっていた実行速度がほんの1~2秒くらいまで短縮。

てなわけで、HAVINGで抽出するSQLを書きたい場合、開発者は往々にして、実際の運用でどれだけの規模を捌くかなんてわかりゃしないので、大人しくテンポラリテーブル噛ませる癖をつけようぜって話でした。

この記事は役に立ちましたか?
  • _(:3」∠)_ 面白かった (0)
  • (・∀・) 参考になった (0)
  • (`・ω・´) 役に立った (0)