慣れないインデックス貼り 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 文が使われるケースがこの "ユーザーが削除された場合" にのみインデックスが使われることを想定している。その場合、インデックスの作成・更新が圧倒的に多くなり、無駄なインデックスとして削除することが推奨される。そのインデックスを消すことで、作成・更新の速度が速くなるためである。