論理削除カジュアルに触発されて現場で見たデータベースについて考えた事を書いてみた

論理削除カジュアルに触発されたので、社会人になってここ半年くらい、現場で見たデータベースについて考えた事について書いてみます。

内容

• とりあえず外部キーにはインデックスを貼っておく
• ユニーク制約はデータベースレベルでやらないとしばしば崩れる
• 対称なテーブルには対称にindexを貼る
• serializableやacts_as_bitの列は一行でも検索のコードを書いたら別テーブルに括り出す

とりあえず外部キーにはインデックスを貼っておく

どういう傾向かはよく分からないのですが、アプリケーションのスキーマを見ていると、 deleted_atとの複合インデックスや、アプリケーション内のコードから呼び出される組み合わせのindexは貼ってあるのですが、 外部キーに対してインデックスを貼っていないことがあります*1。

いわゆる外部キーぎらいという奴です。

アプリケーションがよく動いているなら、まあ、いいじゃないか*2、とも思います。

しかし、調査のためにいつもと違うSQLを書くと、JOINのときの結合先の行を選ぶ際にインデックスが効かないせいでtemp落ちしたり、時間がかかりすぎるのでabortされたりします。

外部キーは、けっこうカーディナリティが小さいいいキーになることが多い気がしているので、迷ったらつけておいていいと思います*3

正直な感想として、セカンダリインデックスに凝らずとも外部キーだけでも問題ない場合も多い気もしています。

ユニーク制約はデータベースレベルでやらないとしばしば崩れる

ユニーク制約嫌いというのもありますが、ユニーク制約のバリデーションはある程度負荷がかかるサービスなら、アプリケーション層のバリデーションだけで行うと、それなりに崩れる*4ということを学んだので、データベース側にもユニーク制約をつけた方がよいと思います。

ユニークにしたいキーによって外部サービスと連携する予定があるなら、外部サービスはこちらのキーの重複なんて考慮してくれませんので、絶対つけたほうがよいです。

対称なテーブルには対称にindexを貼る

たとえば少し前に流行ったSTIのようなことをやる場合*5*6、 STIで扱うような同じカテゴリに属するものを扱う複数のテーブルがあるとき、これらのテーブルに貼ってあるindexが非対称なとき、結構めんどくさいことになります。

たとえば、こんな感じです。

create_table :text_books do
  t.integer  "user_id",
  t.string   "title"
  t.integer  "author_id"
  t.integer  "publisher_id"
  t.text     "description"
  t.datetime "created_at"
  t.datetime "updated_at"
end

add_index "text_books", ["user_id", "title"], name: "index_text_books_on_user_id_title", unique: true, using: :btree
add_index "text_books", ["user_id", "author_id"], name: "index_text_books_on_user_id_title", unique: true, using: :btree
add_index "text_books", ["user_id", "publisher_id"], name: "index_text_books_on_user_id_title", unique: true, using: :btree

create_table :comics do
  t.integer  "user_id",
  t.string   "title"
  t.integer  "author_id"
  t.integer  "publisher_id"
  t.text     "description"
  t.datetime "created_at"
  t.datetime "updated_at"
end

add_index "comics", ["title", "user_id"], name: "index_comics_on_user_id_title", unique: true, using: :btree
add_index "comics", ["author_id", "user_id"], name: "index_comics_on_user_id_title", unique: true, using: :btree
add_index "comics", ["publisher_id", "user_id"], name: "index_comics_on_user_id_title", unique: true, using: :btree

text_booksとcomicsで共通して同じクエリを使おうとすると、片方のindexが効かなくなって、 ほとんど同じなのにそれぞれ用にクエリを用意する必要が出てきて少し困ります。

複数の列で絞り込みかけたいとかいう話になってくるとめんどくさくなって、共通してキーに入ってきそうなキーが第一キーでないindexが貼ってある方は完全に諦めたりします*7。

serializableやacts_as_bitの列は一行でも検索のコードを書いたら別テーブルに括り出す

ある程度カテゴリーでまとまった情報を扱う列を作って、その列の中身をserializable細かい情報は全部底に入れてしまおう、という列がしばしばあります。

いささかひどい例ですが、以下のようなもの。

create_table :users do
  t.string   "email",
  t.string   "account_id"
  t.text     "detail"
  t.string   "authorities"
  t.datetime "created_at"
  t.datetime "updated_at"
end

class User < ActiveRecord::Base
  acts_as_bit :authorities, %(eat drink listen rest)
  serialize :detail, JSON

  def address
    detail[:address]
  end

  def secret_question
    detail[:secret_question]
  end
end

何が怖いかをいくつかあげてもいいのですが、本題ではないので*8。

これは、

• かなり検索性が悪い
• 複数のユーザー間でフォーマットが統一される事が期待できない

ということが想定されます。

検索性については、エラスティックサーチを入れれば、というのがあるのですが、何文字目が1/0のユーザーを捜すためにエラスティックサーチを入れるのは大仰な気がします。

また、detailの方、つまりもっと大きいハッシュを扱っている列ならいいのか、というと、そうでもなく、落ち着いてコードを眺めると、ある程度共通したフォーマットで扱いたいデータの組がありそうです。

そして、そういった組はテーブルに括り出した方が扱いやすいです。

最初はレコード間で大きなシリアライズされた列の中にある共通する列はよく見えない可能性もありますし、authoritiesのような列は検索もしないかもしれません。

なので、見える見ないではなくて、アプリケーションコードの中で、その列をWHERE句に入れて*9、SELECT文をかけるようなコードを書いたら、テーブルやカラムに括り出せないか検討するといいと思います。

コードが多くないうちはいいのではないか、という話ですが、コードが増えると想像以上に修正をする気が失せ、 また、それと同時にその形式のレコード数が増えて修正が難しくなるので、そういうコードを見かけたタイミングで考えてみるのでよいと思います。

そういえば、アンケートの列を週一くらいのペースでほいぽい付け替えたい、みたいな要求を受けた事があります。

当時は無理矢理LIKEで書いていたました*10。

しかし、いまはむちゃくちゃな件数溜まる前にアンケートの形式が決まる事を信じて、 絶対固定の部分と固定じゃない部分をはっきりさせた後、 固定じゃない部分はtextかblobの列に突っ込んでおいてElasticSearch入れて運用とかそういうことを考えたほうがまだましかもなと思います。

もう少しネタはあった気はしますが、遅くなったので今日はここまでにします。

*1:私も一回やらかしました。ごめんなさい...

*2:タイムアウトになるときがあるから頭が痛いのですが

*3:というより、Railsの人たちの中でつけない感じがあるだけで、外部キー制約をつけたらついてくるDBMSもあるというかMySQLはそうです

*4:アプリケーションサーバが重くて、ユーザに連打され複数のサーバで同じリクエストを受けつけ、しかもそれぞれのサーバでモデル層のユニーク制約のバリデーションを通過してから、DBに重複したSQLのリクエストを送信されるという感じ

*5:これはテーブルが共通なので表題にあげた問題は起こらないのですが

*6:別にSTIがいつもいいわけではなく、NULL列が多発するならマッパーの部分書き換えてでもCTIに変形していったほうがいいと思います。最初からCTIは仕様が決まりきっている場合でなければ帯に短したすきに長しをやりがちなのでよした方が

*7:初心者なので許してください

*8:たとえば、誤ってauthoritiesに要素を追加するときに順序を間違えると途端に動かなくなります。戻せば動くようになるという話ではなく、戻すまで、入れ替わった順序に基づいて文字列が更新されてしまうので、取り返しがつかなくなると思います

*9:特に、正規表現を使って

*10:ひぇー