MySQLアンチパターン：とりあえずUNION

この投稿は、MySQL Advent Calendar 2021 の15日目で公開する記事です。文中の DB は EOLでUpgrade待ったなしの Amazon Aurora MySQL (5.6互換)での話です。バージョン古くてごめんなさい。

昨日は @keny_lala さんの 「pt-online-schema-changeとgh-ostの比較(データが損失するかもしれないAlterTable編)」 でした。Uniq違反でデータ損失…うっ、頭が…と2021年のビッグなヤラカシを思い出してえぐられてます。

2020年のある日の社内Slack

「インデックスを追加しても速くならないSQLがあって困っています」
「MySQLはわからんのだけど見てみましょうかー」
「ああ、条件式に OR が入ってる。サービス成長の機能拡張で、似ているけれどちょっと違う機能入れたから、ユーザ便宜性のためには両方からSELECTしないといけなくなったって感じにみえるな。よくあるやつだ。 OR まじると実行計画をまともにする難易度はあがるよなあ」

手元で OR の左右でなら効くようなINDEXを2本追加してみる

ADD index index_test_1(companyid, driveid, documentid)
ADD index index_test_2(companyid, roomid)

「なにあともあれ EXPLAIN と」

id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t_content	ref	ix_uniqdoc,index_test_1,index_test_2	ix_uniqdoc	8	const	1830667	Using where

180万行 SIMPLE は辛いかも。そして期待の追加indexは認識されている(possible)が使ってもらえていない(key列には出ない)。

MySQLの実行計画を仕事目線でみるのは初めて。PostgreSQLのとはずいぶん感じが違う。実行計画が単純だからこの表現なのだろう。

「possible_keys が複数形で、隣の key が単数形と」
「1行につき採用するindexは一つが前提で EXPLAIN のこの結果形式なのだろうな」
「OR の左右でそれぞれに Index Scan して合成してくれればよかったんだけどねえ。PostgreSQL の Bitmap Index Scan は残念ながら MySQLにはないってことだ」

「SubQueryはそれぞれに1行（実行計画の1ステップ）になってくれそうなものだ」
「SubQueryに分割すると、それぞれの中で使えるindexを探して実行計画にしてくれるのでは？」

EXPLAIN
    (SELECT * FROM t_content
    WHERE companyid = ? AND roomid IN (?, ?, ?)
    )
    UNION
    (SELECT * FROM t_content
     WHERE companyid = ? AND driveid = ? AND documentid IN (?, ?, ?)
    );

SELECT ... UNION SELECT ... に組み替えます。」

「roomid検索とdocumentid検索のUNIONにしてやると、それぞれの SELECTでindexを使って、いい感じ(msec単位)になります。」

id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	PRIMARY	t_content	range	ix_uniqdoc,index_test_1,index_test_2	index_test_2	16	NULL	3	Using index condition; Using where
2	UNION	t_content	range	ix_uniqdoc,index_test_1,index_test_2	index_test_1	339	NULL	2	Using index condition; Using where
NULL	UNION RESULT	<union1,2>	ALL	NULL	NULL	NULL	NULL	NULL	Using temporary

---

サーバアプリケーション開発の手が足りないと雇われたのですが、しばらくやって様子がわかってくると本当に足りないのはDBAですねえと、ロール追加する契機になった仕事はこんな感じだったのです。

2021年ある日の RDS Performance Insights

  SELECT recordid,updateUnixTime
  FROM t_jobqueue
  WHERE jobid = 'Gxxxx' AND status = ?? 
UNION
  SELECT recordid,updateUnixTime
  FROM t_jobqueue
  WHERE jobid = 'Gxxxx' AND status = ?? AND updateUnixTime < 1635288240001 
UNION
  SELECT recordid,updateUnixTime
  FROM t_jobqueue
  WHERE jobid = 'Gxxxx' AND status = ?? AND updateUnixTime < 1635292740001 
ORDER BY updateUnixTime ASC

なんだろう、この冗長なSQLは？
あ。WHERE に OR があったら UNION に組み替えると速くなると悪い一般化をしてしまったのか。頭を抱える DBAなのでした。2020年のチューニング処方箋が説明不足でした。

OR条件を「とりあえずUNION」はアンチパターン

WHERE条件式に OR があるとき、UNION に組み換える人間オプティマイザの実例は、上でも出しましたが、適用条件があって、いつやってもいいわけじゃないです。

• UNION する副問い合わせで異なるインデックスを使いたいとき
• UNION が十分速くおわるくらい両者の中間結果が小さいとき

MySQLでは使えるインデックスが1つだけという制約があるので、OR の両側の条件にそれぞれ効率のいいインデックスがあっても、片方しか使ってくれない。
これを回避するための人間オプティマイザが、「それぞれのインデックスを使える副問い合わせに分割して、結果をUNIONする」

jobid, status のような値の分散が少ないものはindexキーに入れても効果はうすいので、実は単文で FullScan のほうがまだ速いのです。

WHERE jobid = 'Gxxxx' AND (status = $1 
 OR (updateUnixTime < 1635288240001 AND status = $2)
 OR (updateUnixTime < 1635292740001 AND status = $3)))

それ以前の話として、プロダクトコードとして commit する前に EXPLAIN で確認するべきです。

さらにTopSQLにツッコミ

ここは汎用性のない、上記のTopSQLにべったりの余談です。

• jobid, status はカーディナリティで1桁（値のバリエーションが10個もない)
• updateUnixTime も過去全部という条件

このままではリリースからの時間経過とともにこのSQLが対象とする行数が青天井に増えていきそうです。companyid も userid も SQLに含まれないので、全体管理用のバックエンド側のバッチ処理っぽいです。それなら応答時間はあまり気にしなくてもいいかもしれませんが限度はあるでしょう。
Full Scan なので、実際に条件を満たして結果として返す数が小さくても、DB内部処理範囲が青天井に増えていくのは運用性の危機となります。

バッチ処理なら、t_jobqueue.updateUnixTime 処理済みの最大値を別のところに保存して、SQLの条件を片開き条件から ${処理済み最大値} < updateUnixTime AND updateUnixTime < $4 のように下限上限の両側指定にすれば、全体行数に依存しない、ゆっくりと成長する内部処理範囲にできるでしょう。

成長するサービスをやるなら、ユーザ数・サービス稼働日数に比例する行数 N は大敵です。
例のSQLでは、アンチパターンの「とりあえずUNION」をやめても 3N が N になるだけで本質的な改善にはなっていません。SQLの内部処理範囲を、 N に依存しない定数に限りなく近いものにしないと、運用環境では使い物になりません。

まとめ

• MySQLのUNIONチューニングにも適用条件はあります
  • いつでもどこでも使えるチューニングなんて無い
• プロダクトコードのSQLを書く人は全員 EXPLAIN 読めるようになりましょう
  • たとえばこういう記事を参考に https://qiita.com/mtanabe/items/33a80bf2749a872645e6
• 運用環境で日々行が増え続ける TABLE の FullScan は一匹のこらず駆逐しましょう