検索機能はSQLパフォーマンスを意識して実装しよう

はじめに

多くの業務システムではRDBが未だに使用されており、また単純な取得ではない検索機能を求められることが多いです そういった機能はORMライブラリで提供された機能では満足にパフォーマンスが出せず直接SQLを書かなければならない場合もあります 本記事ではそういったパフォーマンスを求められるような検索機能を実装する際の心構え的なものを残していきたいと考えております 筆者個人の経験則を述べていきます 尚、筆者は主にPostgreSQLとMySQLを用いた業務アプリケーション開発経験があります。

アプローチ方法

パフォーマンスを求められる検索機能を求められた場合アプローチ方法としては以下の２つが主にあります。

• 検索用のデータを保存するテーブルを定義して、検索時にはそのテーブルを使用する
• 検索時のSQLを最適化する

前者について検索時のクエリを単純化できるため実装者が実装ミスをするというリスクを減らせるものの、追加や更新時に検索テーブルも適切に更新をする必要があるなど別のミスを生むリスクがあります。 また業務知識とは無関係なテーブルを作らないといけないのも個人的には良くないなと感じています。(絶対にNGというわけではないですが)

以上の理由から筆者はよく後者の検索時のSQLを最適化を行っています。 これは簡単に言うとSQLを頑張って書くということになります。 以降はSQLを最適化するにあたってどういったところを頑張るべきなのかを書いていきます。

基本的なこと

検索対象の項目にインデックスを貼る・インデックスが有効な問い合わせをする

当たり前のことですが、検索対象の項目にインデックスを貼りましょう。 また、実際に書いたSQLでインデックスが効いているか EXPLAIN などを使用して確認しましょう。

検索機能で取得するデータ件数を制限する

これは実装とは直接的には関係ないのですが重要なことなので記載します。 もしもあなたがSESあるいはSIerで顧客からRDBを使用した検索機能を求められた場合は最優先で取得するデータ件数の上限を設けられないか交渉しましょう。 これは単純な話で取得する件数が少なければ応答時間も少なくて済むためです。 また、昨今ではサーバーレスサービスを使用したアプリケーションで実装するのが主流であり、そうしたサービスではメモリ使用量がかなり制限されているケースが多いです。 データ件数を意識せずに実装するとメモリ消費量が上限に達してアプリケーションがクラッシュするリスクがありますし、対応するためにランニングコストが上がるのでできるだけ上限を持たせるように顧客を説得しましょう。

ORを使用する場合は注意する

具体的には各RDBの実装次第にはなるが、ORを使用した場合インデックスを貼っているカラムでもインデックスが効かない場合があります。 もしも書いたクエリが思ったようにパフォーマンスが出なくてインデックスが効いていない場合ORが原因になっていないか考慮したほうが良いでしょう。

FROM句でサブクエリを使用しない

これもRDBの実装次第になりますが、FROM句でサブクエリを使用するのは絶対にやめましょう。インデックスが効かなくなります。 FROM句でのサブクエリとは以下のようなものを指します。

FROM (SELECT * FROM TABLE_A WHERE foo > $1) AS A
INNER JOIN TABLE_B ON A.id = TABLE_B.a_id
...

このようなクエリを書いてしまう理由として例だとTABLE_Aが出てきた時点でfooの条件で絞り込めるじゃんという思考らしいのですが、実際には以降のクエリではインデックスが効かなくなるので返って遅くなります。 例だとサブクエリを使用せずTABLE_BをJOINする際にON句で TABLE_A.id = TABLE_B.a_id AND TABLE_A.foo > $1 とするのがベストではありますが、まあこういったことはオプティマイザに任せて素直にWHERE区で TABLE_A.foo > $1 と条件を追加したほうが良いでしょう(手動での最適化は限界があるので)。

JOINは慎重に

筆者の経験上検索機能が遅くなっている要因で多かったのが前節のFROM句でサブクエリを使用してしまっているのと、JOINが無秩序に行われてしまっているのが多かったです。 INNER JOINであれば合致するデータを絞り込める可能性があるのでまだ良いのですが、LEFT OUT JOINは遅くなりやすい傾向にあります。 かと言って、データをとってこないというわけにもいかないので具体的な対処は EXPLAIN を使用して原因を探る必要があるのですが、筆者が途中からアサインした案件でLEFT OUT JOINが一度のクエリで5個以上のテーブルに対して行われていた場合その検索機能は遅そうだなという印象を受けます。 INNER JOINで実現できるのであればLEFT OUT JOINではなくINNER JOINを利用しましょう。 場合によってはEagerローディングなどを利用したほうが良い場合もあるので検討したほうが良いです(これはこれで別の問題があるのですが)

まとめ

まとめると以下になります。

• インデックスを活用しよう
• 取得する件数は少なくしよう
• ORやJOINには注意しよう
• サブクエリをFROM句で使うのはやめよう

以上です。いかがでしたでしょうか？

---

GI Cloudは事業の拡大に向けて一緒に夢を追う仲間を募集しています

当社は「クラウドで日本のIT業界を変革し、世の中をもっとハッピーに」をミッションに掲げ、Google Cloudに特化した技術者集団として、お客様にコンサルティングからシステム開発、運用・保守まで一気通貫でサービスを提供しています。

まだ小規模な事業体ですが、スタートアップならではの活気と成長性に加えて、大手総合商社である伊藤忠グループの一員としてやりがいのある案件にもどんどんチャレンジできる環境が整っています。成長意欲の高い仲間と共にスキルを磨きながら、クラウドの力で世の中をもっとハッピーにしたい。そんな我々の想いに共感できる方のエントリーをお待ちしています。

採用ページ