結論
A. ActiveRecord を使ったクエリ発行では
①可能ならハッシュ形式の条件指定をしよう
②文字列形式で指定するならプレースホルダを適切に使用しよう
B. ActiveRecord を活用しない場合は int なのか varchar なのか、ちゃんと意識してクエリ発行しないとインデックスが使われない
以上 Aについて再認識、Bについて知ることができました。
環境
- Ruby v2.7.5
- ActiveRecord v6.1.4.4
- MySQL v8.0.23
問題の概要と原因
実際の業務で経験した内容を外部向けに掲載可能になるよう、テーブル名などを変更し、さらに簡略化した上で記載しています。
先日、想定しているインデックスが使われていないクエリを見つけてその原因を探りました。
User は、複数の Book を持つ、という前提で以下を見るようにして下さい。
SELECT * from books where user_id = 1;
Book の user_id にはインデックスが張られていますが、👆のクエリはフルテーブルスキャンになっていました。ナゼ🤔？？
以下のクエリだと正しくインデックスが使われました。
SELECT * from books where user_id = "1";
そうです、最初の設計ミスで books の user_id カラムは int であるべきところが varchar になってしまっていました😲
これがインデックスが使われていない原因でした。
というか、varchar なところを int で指定すると「レコードは取れるのだけどインデックスはうまく使えない」になっちゃうんですね。これ、考えたことなかったので注意すべきところだなと思いました。
この機会に、以下についてはどうなってるんだろう？ということを確認してみました。
ActiveRecord と発行クエリについて確認
条件を文字列だけで指定する（生SQL埋め込み）
Book.where("user_id = 1")
SELECT `books`.* FROM `books` WHERE `books`.`user_id` = 1;
今回はこのような書き方になってしまっていたため、問題が生じていました。
そのまま文字列を埋め込むようなケースだと ActiveRecord は何も考慮してくれないので、このような非効率なクエリになってしまうようでした。
まぁこういう書き方に慣れちゃうと SQL インジェクションの要因にもなるコードを生むようになっちゃうのでそもそもよくない書き方です。
どうしてもこういった書き方が必要なケースについては次のプレースホルダを利用する癖をつけましょう。
プレースホルダを用いて文字列を埋め込む
Book.where("user_id = ?", 1)
SELECT `books`.* FROM `books` WHERE `books`.`user_id` = '1';
SQL インジェクションを防ぐために推奨されるプレースホルダを用いた書き方をすると、適切な type に修正したSQLを発行してくれました。これなら安心です。
補足
今回の例は簡略化しまくっているので、この例だとプレースホルダを利用する価値がないですが、本来は以下のように 1 の部分に変数を埋め込んで動的にクエリを作るようなケースを想定しています。
# NG
Book.where("user_id = #{user_id}")
# OK
Book.where("user_id = ?", user_id)
条件でハッシュを使う
Book.where(user_id: 1)
SELECT `books`.* FROM `books` WHERE `books`.`user_id` = '1';
こちらもプレースホルダを用いた方法と同様に、適切な type でSQLを発行してくれました。
今回例として上げているようなシンプルなクエリを発行する場合は基本こういう書き方をすると思うので、Rails がよしなにしてくれて問題がおきない感じですね。
これに助けられ続けていたので、生SQLをよくない書き方で埋め込んでいることによって生じている今回の問題にも気づくのが遅くなりました。
まとめ
最初に書いたことのの繰り返しですが、
ActiveRecord を使ったクエリ発行では
①可能ならハッシュ形式の条件指定をしよう
②文字列形式で指定するならプレースホルダを適切に使用しよう
またActiveRecord を活用しない場合は int なのか varchar なのか、ちゃんと意識してクエリ発行しないとインデックスが使われない
ActiveRecord ありがとう🙏