相互作用のデータモデリング

2017.04.10

361

データモデリングDBMSに関連するトピックを技術的な深さ・レベルに関係なく、これから継続的に共有していこうと思います。
最初のテーマは、相互作用のデータモデリングです。
今更ではありますが、これからもたくさん悩んだり検討したりする機会があると思うので、取り上げてみたいと思います。

まず例として、今朝、私が起きてから出勤するまでの行動を参考にすると、

起床。リビングルームに向かい、子供たちにおはようと挨拶をする。
妻にも挨拶をしようとしたが、ぐっすり寝ているので諦める。
朝ごはんを食べて、子供たちを見送り、身支度を整え家を出る。
バス停でバスを待ちながらスマートフォンでゲームをする。
その間、友人にゲームアイテムを配ったり、LINEしたりする。

このように、朝の短い間にも私たちは他人と関係を結んで相互作用しています。
自分と相手の相互作用をどのようにデータモデルに表現してDBに実装できるか考えてみよう。

[図1] 私たちは常に他人と相互に作用する
[著作] freebie.photography
[画像ソース] http://www.freeimageslive.co.uk/free_stock_image/network-concept-jpg

簡単に説明するために、NAVERのメッセージサービスを例に挙げます。

[図2] NAVERメッセージサービスUI画面

[図2]をERDで表現すると、おそらく[図3]のようなエンティティが導き出されるでしょう。
（便宜上、個人メッセージ/団体などの区分は省略しています。ERDには表現していませんが、auto_incrementのシリアル番号でpkを生成したと仮定します。）

[図3] 受信メッセージボックス画面から設計したERD

リストを表示するためのクエリを飛ばしてみよう。
受信メッセージボックス/送信メッセージボックスのクエリをそれぞれ作成してみよう。

[図4] 受信メッセージボックスのデータを取得するクエリ

インデックスは、(受信者ID、登録日時)を使用します。
一方、送信メッセージボックスには「リストを受信した日付」という項目がもう1つあります。

[図5] 送信メッセージボックスUI画面

モデルを以下のように変更しました。[図4]の受信メッセージボックスのクエリも変更しました。

[図6] 送信メッセージボックス画面から修正したERD

[図7] 送信メッセージボックスのデータを取得するクエリ

インデックスは、(送信者ID、送信日時)を使用します。
ここまでは何の問題もありません。

たとえば、次のようなアイデアが浮かんだとしよう。

「受信メッセージボックス、送信メッセージボックスを区別せずに、送受信したメッセージをまとめて一束のように表示したらどうだろう？」
「そのあと、メール、ブログと統合してマイニュースというサービスを作ってみたらどうだろう？」

簡単だと思ってとりかかると、すぐに絶叫に変わることでしょう。
開発者が苦しむ理由を簡単に理解できるように、クエリを作成してみよう。
[図4]、[図7]で作成した受信メッセージボックス/送信メッセージボックスのクエリをUNION ALLにすると、簡単に答えが出ます。

SELECT 相手ID, メッセージ内容, 送信日時, 既読日時
FROM (
    SELECT 送信者ID AS 相手ID, メッセージ内容, 送信日時, 既読日時
    FROM メッセージ
    WHERE 受信者ID = '自分'
    -- ORDER BY 受信 DESC -- (外でORDER BYをするのでオーバーヘッド)

    UNION ALL

    SELECT 受信者ID AS 相手ID, メッセージ内容, 送信日時, 既読日時
    FROM メッセージ
    WHERE 送信者ID = '自分'
    -- ORDER BY 送信 DESC -- (外でORDER BYをするのでオーバーヘッド)
)
ORDER BY 送信 DESC

結果は出ますが、データが増えれば増えるほど、上記のクエリが遅くなることが分かるでしょう。理由はインデックスのためです。
UNION ALL上部のクエリは、(受信者ID、送信日時)インデックスを選択します。
UNION ALL下部のクエリは、(送信者ID、送信日時)インデックスを選択します。
UNION ALL外側の部分、すなわちFROM節のinline view形式で構成されたテーブルをORDER BYする場合、インデックスを使用できないため、フルスキャンしてソートする必要があります。プランでは、file sort、temp tableなどの構文が確認できます。

サービスが稼働し、不特定多数のユーザーが送受信したメッセージリストのクエリが絶え間なく入ってくると、file sortが多くなるので、CPUが増加して、IOWAITが発生し、全体DBが遅くなって、おそらく障害につながるでしょう。送信メッセージ/受信メッセージが多いパワーユーザーほどインデックスに乗らずfile sortが必要なrow件数が多くなるので、さらに遅くなる構造であることが容易に予測できます。

では、モデルを以下のように変更するとどうなるでしょうか。