そのまま載せるわけにもいかないので置き換えて記載していきますが、運営サイドで、ユーザーの記事を検索する機能のある画面にて検索ボタンを押したらくるくるまわったまま他の画面にも遷移できないのでなんとかしてほしい、というような依頼があった。
この案件では監視ツールとしてdatadogを使っているので、まずは検索ボタンを押下したときの処理時間の割合調査から入っていき調査したところ３minかかっており、その割合はPDOが100%であることが判明した。
発行SQLは以下のようなものとなる。
SELECT count(id) AS count
FROM posts
WHERE status = "publish"
AND user_id IN (1)
ORDER BY created_at DESC
ローカル環境でもSTG環境でも再現できなかったので、システム構成が異なるかデータ量によるものと推測。
いづれにしてもpostsには1億レコード以上があり、これをローカルに持ってくるのは非常に手間。
とはいえ、本番環境で検索ボタンを押下したときに処理が中断されるときもあるので調査にならない。
SQLの処理時間が長くて 結果的にサーバーが処理継続できる上限時間を超えているせいかERR_CONNECTION_CLOSEDのエラーがでるが、根本はPDOが原因のはずなのでredashで本番環境に対して上記SQLを実行してみる。
相変わらず３minかかっている。
犯人はもうこいつで確定なので3minかかっているSQLをなんとかして早くする方法をググりまくる。
ヒントを得たのは以下の記事。
まさかとは思いながらもposts.user_idの型を見ると、なんと！
varcharであった！！！
システム開発において一般的にほにゃららIDはint型であることが多いが、なにか理由があってvarcharにしたのでしょう。
以下にint型の値をstring型に変えて実行してみるとあら不思議、3minかかってたSQL処理が5secで完了！
SELECT count(id) AS count
FROM posts
WHERE status = "publish"
AND user_id IN ('1')
ORDER BY created_at DESC
EXPLAIN.typeもALLになっていたのが、refに改善されました。
設計と実装しはセットなのでこういうところも意識して開発を進めていかないと、プロダクトが小さいうちは気付きませんが、大きくなっていくにつれて発覚してくることも多くあります。
参考までに、全体のレコード数は1億6000万件程度で発行SQLで取得したレコードは110万件程度でした。
使っているFWはsymfony3なのですが、laravelだとモデルにキャスト定義できてselect時に自動的に型変換してくれるので、アプリ側で別のモデルに条件設定するときにキャストするとかしないとか意識しなくていいわけです。
分かりやすさに努めているのでレイテンシーとかプロセスとかそんな言葉は使いません。