ボクココ

少人数でのサービス開発運営に関するテックブログ

慣れないインデックス貼り vol.2

ども、@kimihom です。

f:id:cevid_cpp:20220222180904j:plain

前回のインデックス貼り に続き、インデックス対応についての追記を記す。

外部キー のインデックスの効果

よく、関連する外部キーにインデックスをデフォルトで貼る書き方がある。

class CreateTweets < ActiveRecord::Migration[5.0]
  def change
    create_table :articles do |t|
      t.string    :text
      t.integer   :user_id, index: true
    end
  end
end

ここでの user_id, index: true の箇所となる。さて、インデックスを貼ると、SELECT 文でインデックスが使われるようになるという点はわかりやすい。つまり以下のようなケースである。

@user = User.find(params[:id])
@articles = @user .articles
=>
SELECT * FROM articles WHERE user_id = ? ORDER BY id ASC LIMIT 100;

最初は良かったけど、そのうち記事は基本的にグループ単位で取得するケースが出てくる。

@user = User.find(params[:id])
@articles = @user.group.articles
SELECT * FROM articles WHERE group_id = ? ORDER BY id ASC LIMIT 100;

こうなると、user_idでの絞り込みインデックスは不要になるケースがある。

group_id でインデックスを貼ったことだし、user_id のインデックスはもう用がないね。消そう」

そして消した後、意外なところでインデックスが使われていたことがわかったりする。

対象ユーザーを削除すると、対象記事を "作者不明" にするケースを考えてみよう。現状の モデルは以下のように定義がされているとする。

class User
  has_many :articles, dependent: nullify
end

class Article
  belongs_to :user, optional: true
end
user = User.find(params[:id])
user.destroy

この時、 null にさせる articles を取ってくるために、Rails 側で勝手に SQL が発行される。

SELECT * FROM articles WHERE user_id = ?;

「なんか削除にすごく時間がかかるな〜。。」そう、user_id のインデックスがないので、この削除処理がものすごく時間がかかってしまうことが発生してしまう。

では、user_id だけのインデックスはやはり必要なのか?いいえ、SELECT 文をちょっと修正すれば他のインデックスを使わせるようにできる。

class User
  has_many :articles
  belongs_to group

  after_destroy :nullify_articles

  def nullify_articles
    self.group.where(user_id: self.id)
      .update_all(user_id: nil)
  end
end

class Article
  belongs_to :user, optional: true
end

group として絞り込んだ状態で、対象の user_id を指定する。このちょっとした SQL を追加するだけで、インデックスが使われるようになった。

めでたし。めでたし。

追記:

user_id でのインデックスを残しておいてもいいのでは?」

そう思われるかもしれない。これは 実際にインデックスが使われる 参照の量と、作成・更新する量とで検討が必要だ。 今回のケースの場合、 SELECT 文が使われるケースがこの "ユーザーが削除された場合" にのみインデックスが使われることを想定している。その場合、インデックスの作成・更新が圧倒的に多くなり、無駄なインデックスとして削除することが推奨される。そのインデックスを消すことで、作成・更新の速度が速くなるためである。

終わりに

このインデックス、大量データがある場合にだけしか必要性を感じることがないという点が、ローカル開発してる時と全く違う難点である。

大量データにインデックス削除とか怖いな〜と思うかもしれない。それでもしっかりと DB の現状を分析して、最適な対応ができるようにならなければ、DB負荷はどんどん増えていく。

さぁ、より最適なデータベースへ。それが私たちだけでなく、すべてのユーザーが望むことなのだから。