メモ データベースインデックス追加が遅い原因と対策

データベースインデックス追加が遅い原因と対策

はじめに

Railsのマイグレーションでインデックスを追加する際、以下のように実行時間がかかることがあります。

-- remove_index("users", {:name=>"index_users_on_email"})
-> 0.0244s
-- add_index("users", ["email"], {:name=>"index_users_on_email", :unique=>true, :using=>:btree})
-> 3.128s

インデックス削除は0.02秒なのに、追加は3秒もかかっています。なぜこのような差が生まれるのでしょうか？
今回はこの場合の原因と改善策をちょっと調べてみましたのでまとめます。

インデックス追加の仕組み

1. インデックス追加の基本的な流れ

Railsのマイグレーションで以下のコードを実行すると：

class AddUniqueIndexToUsers < ActiveRecord::Migration[7.0]
  def change
    add_index :users, :email, unique: true, using: :btree
  end
end

実際には以下のSQLが実行されます：

CREATE UNIQUE INDEX index_users_on_email 
ON users (email) USING btree;

2. インデックス追加時の処理内容

1. テーブル全体のスキャン: 既存の全レコードを読み取り
2. ソート処理: 指定カラムで全データをソート
3. 重複チェック: UNIQUE制約がある場合、重複値を検出
4. B-tree構造の構築: ソートされたデータからB-treeインデックスを構築
5. メタデータ更新: システムカタログにインデックス情報を記録

時間がかかる主な原因

1. テーブルサイズが大きい

最も影響が大きい要因です。レコード数が多いほど：

• スキャン時間が増加
• ソート処理に時間がかかる
• インデックス構築に時間がかかる

# 例：100万レコードのテーブル
class Users < ActiveRecord::Base
  # 100万件のレコードが存在
end

# インデックス追加時に100万件をスキャン・ソート
add_index :users, :email, unique: true

2. UNIQUE制約の処理

unique: trueを指定した場合、追加の処理が必要です：

# 重い処理
add_index :users, :email, unique: true  # 重複チェックが必要

# 軽い処理
add_index :users, :email  # 重複チェック不要

UNIQUE制約がある場合：

• 既存データの重複チェックが必要
• 重複が見つかった場合、エラー処理も含めて時間がかかる
• インデックス構築前にデータの整合性を保証する必要がある

3. ディスクI/Oの負荷

大量のデータを扱う場合：

• ディスクからの読み取り時間
• 新しいインデックスファイルの書き込み時間
• 特にHDDの場合、シーク時間が影響

4. ロック競合

インデックス作成中はテーブルにロックがかかります：

• 他のトランザクションが待機状態になる
• 書き込み処理がブロックされる

改善策

あまりにも時間がかかるのであれば以下のような方法を使うのも良いのかもしれないです。

1. バックグラウンド実行（PostgreSQL）

本番環境では、ダウンタイムを避けるためCONCURRENTLYオプションを使用：

class AddUniqueIndexToUsers < ActiveRecord::Migration[7.0]
  def change
    # オンラインでインデックス作成
    execute "CREATE UNIQUE INDEX CONCURRENTLY index_users_on_email ON users (email)"
  end
end

2. 段階的な実行

class AddUniqueIndexToUsers < ActiveRecord::Migration[7.0]
  def up
    # 1. まず重複データを確認
    duplicates = User.group(:email).having('count(*) > 1').pluck(:email)
    
    # 2. 重複データを処理（例：最新のものを残す）
    duplicates.each do |email|
      users = User.where(email: email).order(created_at: :desc)
      users.offset(1).destroy_all
    end
    
    # 3. その後インデックスを追加
    add_index :users, :email, unique: true
  end
  
  def down
    remove_index :users, :email
  end
end

3. メンテナンス時間の設定

# アクセスが少ない時間帯に実行
# 例：深夜2時〜4時
class AddUniqueIndexToUsers < ActiveRecord::Migration[7.0]
  def change
    # 事前にテーブルサイズを確認
    count = User.count
    puts "テーブルサイズ: #{count}件"
    
    if count > 1000000
      puts "警告: 大量データのため時間がかかる可能性があります"
    end
    
    add_index :users, :email, unique: true
  end
end

4. インデックス作成前の準備

class AddUniqueIndexToUsers < ActiveRecord::Migration[7.0]
  def change
    # 1. 統計情報を更新
    execute "ANALYZE users"
    
    # 2. 不要なインデックスを削除
    remove_index :users, :old_index_name if index_exists?(:users, :old_index_name)
    
    # 3. 新しいインデックスを追加
    add_index :users, :email, unique: true
  end
end

まとめ

インデックス追加が遅い主な原因は：

1. テーブルサイズ（最も重要）
2. UNIQUE制約の処理
3. ディスクI/Oの負荷
4. ロック競合

対策として：

• バックグラウンド実行の活用
• 段階的なデータ処理
• 適切なメンテナンス時間の設定
• 事前の準備作業

これらの対策により、インデックス追加の時間を短縮し、サービスへの影響を最小限に抑えることができるかと思いました！

おしまい