皆さんはデータベースのカラムは
どのように設定していますか？
ログイン履歴のテーブルがあったとして
userAgent というカラムがあったとすれば
Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/124.0.0.0 Safari/537.36 Edg/124.0.0.0
といった同じ要素が際限なく増える事があるため
userAgent 用のテーブルを
作ることがあるかもしれません。
同じ人が同じブラウザでアクセスした場合
userAgent が同じになる可能性が高いため
テーブル分けは非常に効率的と言えます。
A さんと B さんの userAgent が
同じになる可能性もそれなりにあると思われます。
Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/124.0.0.0 Safari/537.36 Edg/124.0.0.0
の id を 1 としておけば
ログイン履歴の userAgent のカラムは
1 を登録するだけで済みます。
今回はどういった場合に
テーブルを分けるのか
考察してみたいと思います。
ちなみに
ログイン履歴テーブルの userAgent カラムは
irohaboard_v0.10.10 で見かけたため
記事にしやすいと思い
拝借しました。
開発者の方には大変感謝致します。
冒頭の状況を分けるかどうか
はじめに冒頭の状況で
テーブルを分けるかどうかについて
検討してみたいと思います。
userAgent 自体が
現在は非推奨かもしれませんが
今回の考察には適しているため
お付き合い頂けると幸いです。
テーブルを分けるかどうかを考察する場合
・分けたテーブルの検索時間
・テーブルを分けた結果どの程度容量が削減するか
に焦点が当たると思います。
userAgent をテーブル化した場合
userAgent テーブルは
少なくとも 推定ユーザー数 × 3 件以上
登録される事を想定したいです。
なので 10 万人のユーザーがいるシステムの場合
userAgent テーブルの参照は
30 万のデータを検索する必要がある事になります。
しかしながら同じ userAgent が
平均 20 個登録されるならば
テーブルを分けない場合
userAgent を 600 万個べた書きする事になります。
userAgent の文字数が 100 文字とすると
6 億文字の容量が増える事になります。
userAgent だけで 600 MB も
容量を消費するのです。
テーブルを分けた場合は
30MB 程度で済むため
かなりの容量削減と
言えるのではないでしょうか
570 MB の容量を削減する事と
30 万件のデータを検索する事の
どちらが高速化に繋がるかを
考える事になると思います。
今回のケースは
570MB の容量が増えた場合は
データベースの検索に
時間がかかることが予測されます。
なので今回は 30 万件のデータを検索する方を
選択した方が良いと思います。
つまりテーブルを分ける方です。
前述のテーブルを分ける場合の工夫
前述のテーブルを分ける場合
userAgent テーブルに
データを挿入する条件として
ログイン履歴テーブルに同じ userAgent が登録される場合
を設定するといいと思います。
userAgent カラムが
32 など数値の場合は
userAgent テーブルを参照し
数値でない場合は
そのものを参照するといいと思います。
重複した userAgent を
登録する場合に初めて
userAgent テーブルに
追加するのです。
そうする事で
より無駄が無くなると思います。
まとめ
今回は冒頭の状況の
テーブル分けについて考察してみました。
同じ文字列が
何度も登録される場合は
テーブルを分ける事がありますので
その時に参考にして頂けると幸いです。
今回の結論は
「同じ長い文字列が何度も登録される場合はテーブルを分ける」
という結果になりました。
いずれは他のシチュエーションを考えて
データベースのテーブルについて
考察してみたいと思います。
閲覧ありがとうございました。