この記事は、株式会社うるる（ULURU） Advent Calendar 2023 の19日目の記事です。
私は長年にわたり検索システムの開発に携わっており、ここ１０年近くの開発では全文検索システム(SolrやElasticsearchなど)を利用したシステムに携わっています。全文検索システムを利用してからは検索の性能で苦労する機会はほとんどないのですが、全文検索システムを利用する前、RDBで検索をおこなっていたときにはとても苦労することがありました。今回はその苦労したクエリについて記事にしたいと思います。
対応に苦労したクエリ
RDBで検索を行う際は、主にBツリーインデックスが利用されます。このBツリーインデックスは検索する際、大抵は１テーブルにつき一つしか使用されません。そのため該当する件数が多い条件を復数指定するような検索をした際は性能がよくありません。
株式会社うるるではNJSSという入札情報の検索サービスを提供しています。この入札情報の検索を例にしますと、落札情報を検索する場合、データは1,700万件以上あり、検索条件には業種や履行場所といった条件を指定することができます。例えば業種が「土木工事か舗装工事」、履行場所が「東京都か神奈川県」といった具体で検索を行う事ができるのですが、このとき各条件に該当する件数はそれぞれ数百万件あります。
RDBでインデックスを作成して対応しようとする場合、業種と履行場所の複合インデックスを作成して、なるべくインデックスのスキャンだけにする方法があるかと思います。しかし、検索する条件が１項目に対して復数あったり、検索する項目が復数あったりすると、Bツリーインデックスでは効率よくデータを選択できず、該当する件数分に応じたデータ量を捜査せざるを得ない状況にすぐ陥ります。それでもテーブルのデータを読むよりデータ量が少ない分遥かに早いのですが、捜査する件数が多くなるとリニアに遅くなりますし、数百万件のデータとなってくるとやはり時間がかかります。また実際のところ履行場所は1案件に対して復数ありえるのですがそうした1:Nのデータには更にjoinのコストがかかります。また業種や履行場所以外にも、様々な検索条件やソート条件があるのですが、そうした多くの条件を複合インデックスで対応するには限界があります。
実際、当時行われた対応策（NJSSとは全く別のシステムです）としては、主に複合インデクスを作成することで対応していたのですが、その他には非正規化した検索用のテーブルを利用すること、また仕様として選択性の高い条件を検索時の必須項目にすることなどがありました。どうしても捜査するデータ量が問題で性能が十分でなかった部分は、物理的に読み込むブロックが減るようにするために物理的な配置の工夫も行いました。
結果的にそれで対応はできたのですが、条件によってはどうしても遅くなるものがあったり、条件追加などのメンテナンスがしづらかったりとの苦労や制約のあるものでした。
全文検索システムではどうだったか？
全文検索システムではこうした選択性の低い条件を復数指定する検索でも素早くレスポンスが返ってきます。そのためこうした通常の検索の性能では幸い苦労したことはありません。全文検索システムは全文検索機能を行える様になること以外にも、そうした検索も難なく早い性能で実現できるという強みがあります。
なぜ全文検索システムだと高速に処理できるのかについては、随所にビットマップによる索引の活用が見受けられるため、それによるものではないかと考えています。ビットマップによる索引や演算はRDBでも利用できるものがあり、仕組みの詳細はそちら（後述のOracleのビットマップ索引のドキュメントなど）が参考になります。
RDBでの対応策
私は実際に試したことはないのですが、今回紹介したような私の苦労したクエリにも対応できるRDBもあるようです。（ただし実際の効果や性能については未確認です）
Oracleではビットマップ索引が解決策になりそうでした。ただし、ビットマップ索引はエンタープライズエディションでしか使用できません。
PostegreSQLの場合は復数のインデックスを組み合わせて使用できるようです。これは今回この記事を書くために調べて初めて知ったのですがすごい利点だと思います。もし使う機会があればぜひ試してみたいと思いました。
最後に
今回の記事は以上になります。１０年以上前に苦労した時の話で、今はそもそもそうした苦労される人は少なかったり、別のよりよい解決策があったりするかもしれませんが、なんらかの参考になれば幸いです。