MySQLでの検索効率の効率化 ~ 検索用テーブルの心得 ~

今日は仕事であったエピソードを綴ります。

テーマは、検索用テーブルの心得。

結論から言うと、joinの利用が困難な状況下で、両テーブルの情報を加味したレコードを抽出したければ、
両条件を満たすレコードのみを保持する検索用テーブルを用意することで、クエリ発行数や、検索効率の改善を図ることが出来るというものです。
キーポイントは、検索用テーブルを用意することで、物理削除が許されるので、検索効率が常に保たれることですね。

== この議論のスタート地点 ==
以前勤めていたところでは、クラサバな限定的で閉ざされたシステムに携わっており、
そこではOracleを使ってましたが、普通にviewを用意して、裏で好きにjoinしていたので、
条件に合致するレコードがなければそもそもレコードは表示されませんでした。

が、しかし、現在はwebアプリケーション開発に携わっており、データ量もアクセス数も膨大であり、かつ、DBの並列分散もされているので、
joinすることは現実的ではありません。
===================

では、ここからはちょっと詳しく。
以下のテーブルがあると仮定します。

table : table_1
column : user_id, group_id, ...

table : table_2
column : group_id, status, ...

ここで、user_id群 をもとに、そのユーザ達の属するgroupのうち、特定のstatusのレコードを「取得しなければならない」とき、
単純に上記2テーブルでがんばる場合、どんなにインデックスを丁寧にはっても、

手順1. table_1 から範囲指定でgroup_idを取得
手順2. そのgroup_id達をもとにtable_2で group_id に対して IN句でレコードを取得
手順3. クエリ発行元のソースコード上で、statusでフィルタ
手順4. もしフィルタ結果が0件の場合に、手順1からやり直し、対象のレコードを取得できるまで全レコードをなめる。
と、効率性の低い処理となります。これは、多少処理の順番を変えても、結局、積集合をもとめる処理なので、似た状況となります。

これを解消する方法として、極端な話、以下のテーブルを用意します。

table : table_3
column :guild_id

このテーブルへのCRUDは以下の通り。
C : table_1へユーザレコードが生成されるときに、そのユーザのgroup.statusが特定statusである場合にcreate
R : かつあい
U : かつあい
D : ソースコード上、対象レコードのgroup.statusが特定statusでなくなったことが判明した際。(ここは柔軟に)

こうすることで、このテーブルには、user_idにひも付き、かつ、statusが条件を満たすレコードのみが残るため、
検索精度は保たれ、クエリ発行数や検索効率の面で改善を図れます。


なかなか前提が限定される話でしたが、本日はこんな感じで。

ちなみに自分も勉強中の身で、身近なメンバーと相談した結果を記述しているだけなので、あくまで参考にする程度でお願いします。
また、更なる改善案があれば、ぜひともコメント頂ければと思います。

それでは。