おはようございます、こんにちは、こんばんは。
スペースマーケットでWebエンジニアをしています、s0arです。
さむい ふゆ
激重クエリたんを救わねば
ある日、とんでもなく遅いAPIレスポンスが観測されました。
原因を調べていくと、激重クエリたんが泣いてました。
かわいいね。ぼくが救ってあげるね。
やったこと
そんなに大したことはしていませんが、SQL初心者には少し役に立つかもしれません。
クエリたんの気持ちが知りたい
まずクエリたんの気持ちを理解してあげましょう。
人はいろんな気持ちを隠して生きていますが、クエリたんは包み隠さずお話してくれます。
重いだけで根は素直なんですね。良い子だ。かわいいね。
今回重かったのはこのクエリたん
(※プライバシー保護の観点から、実際のクエリたんとはテーブル名等が異なります)
SELECT *
FROM events
WHERE deleted_at IS NULL
AND owner_id = 5000
AND (ended_at > '2023-11-22' AND ended_at <= '2023-11-28')
AND completed_at IS NOT NULL;
この子を救うためには、まず気持ちを理解してあげます。
EXPLAINを使います。
EXPLAIN SELECT *
FROM events
WHERE deleted_at IS NULL
AND owner_id = 5000
AND (ended_at > '2023-11-22' AND ended_at <= '2023-11-28')
AND completed_at IS NOT NULL;
このときに、表示された結果のtypeカラムの値を見てみます。
今回はここがindex_mergeになっていました。
index_merge is 何
この結合型はインデックスマージ最適化が使用されたことを示します。 この場合、出力行の key カラムには使用されたインデックスのリストが含まれ、key_len には使用されたインデックスの最長キーパートのリストが含まれます。 詳細については、セクション8.2.1.3「インデックスマージの最適化」を参照してください。
なるほど、わからん
簡単にいうと、MySQL様が複数のindexをよしなに組み合わせてフィルタしたりソートしたりしてくれてるんですね。でもこれがお馬鹿さんなことがあります。
クエリたんを救う
ではどうすれば救ってあげられるのでしょうか？
ぼくたちに彼女を救うだけの力はあるのでしょうか？
あります。適切なindexを貼ってあげれば良いのです。
ぼくは頭が良くないので、どの条件の組み合わせが遅いかを、条件を削除したりして調べました。
（SQLに自信ニキネキはもっと賢い方法を取るんだろうなあ…と思ったりしています）
今回の場合はdeleted_at, owner_id, ended_atでした。
なので、この組み合わせでindexを貼ってあげることで実行時間が大体1/30になりました。
（もともと900ms程度かかっていたものが30ms程度に改善）
今回の場合はこれで改善しましたが、原因が異なることがあるので、都度適切な対応が必要ですね。
クエリたんを救えた
こうしてかわいそうな激重クエリたんは救われました。
ですが、同じように苦しんでいるクエリたんはまだまだいます。
そして彼女たちはそれぞれ違った悩みを抱えているのです。
彼女たちを救うには、まずは気持ちを理解してあげるところから。
まずはEXPLAINから。そんなお話でした。
俺たちの戦いはこれからだ！
スペースを簡単に貸し借りできるサービス「スペースマーケット」のエンジニアによる公式ブログです。 弊社採用技術スタックはこちら -> whatweuse.dev/company/spacemarket
Discussion