データベース最適化 — インデックスとクエリ改善
データベースは正直だ
「データベースは嘘をつかない」とユイが言った。「EXPLAIN ANALYZEを見れば、何が起きているか全部わかる」
Buzzの計測結果では、問題の84%がデータベースに起因していた。アキラはチームと一緒にPostgreSQLの深みに潜ることにした。
「まず現状を把握しよう」アキラはスロークエリログを開いた。上位3件が一目で分かった。
スロークエリトップ3:
1. タイムライン取得: 12,456ms（全クエリの34%の時間を消費）
2. ハッシュタグ検索: 8,934ms
3. ユーザーフォロワー一覧: 3,421ms
これら3クエリで全DB負荷の75%を占める。
ここを直せば劇的に改善できる。
EXPLAINの読み方
EXPLAIN ANALYZE はクエリの実行計画と実際の実行時間を表示する。「計画」だけでなく「実際」の数字が重要だ。
-- 問題のクエリ: タイムライン取得
EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT posts.*, users.username, users.avatar_url
FROM posts
INNER JOIN users ON posts.user_id = users.id
WHERE posts.user_id IN (
SELECT followee_id FROM follows WHERE follower_id = 123
)
AND posts.created_at > NOW() - INTERVAL '7 days'
ORDER BY posts.created_at DESC
LIMIT 50;-- 出力結果（問題あり）
Limit (cost=0.00..9234.56 rows=50 width=2048)
(actual time=0.000..12456.789 rows=50 loops=1)
-> Sort (cost=0.00..45678.90 rows=18234 width=2048)
(actual time=12345.678..12456.677 rows=50 loops=1)
Sort Key: posts.created_at DESC
Sort Method: external merge Disk: 8192kB ← ディスクソート！
-> Hash Join (cost=234.56..8901.23 rows=18234 width=2048)
(actual time=5.678..9876.543 rows=18234 loops=1)
Hash Cond: (posts.user_id = follows.followee_id)
-> Seq Scan on posts ← 問題！全件スキャン
(cost=0.00..8456.78 rows=1850000 width=1024)
(actual time=0.012..8901.234 rows=1850000 loops=1)
Filter: (created_at > (now() - '7 days'::interval))
Rows Removed by Filter: 1831766 ← 183万行スキャン！
-> Hash (cost=120.00..120.00 rows=9165 width=8)
(actual time=4.567..4.567 rows=9165 loops=1)
-> Seq Scan on follows
(cost=0.00..120.00 rows=9165 width=8)
Filter: (follower_id = 123)
Buffers: shared hit=23456 read=45678 ← ディスクread多い
Planning Time: 2.345 ms
Execution Time: 12456.890 ms ← 12秒！読み方のポイント
Loading diagram...
特に注目すべきは「Rows Removed by Filter: 1831766」。183万行を読み込んで捨てている。これが遅さの原因だ。
インデックスの基本
インデックスは本の索引と同じだ。索引なしで「Rails」という単語を探すには全ページを読む必要がある（Seq Scan）。索引があれば「R」→「Ra」→「Rails: 234ページ」とすぐに見つかる（Index Scan）。
# Railsのマイグレーションでインデックス追加
class AddIndexesToBuzzTables < ActiveRecord::Migration[7.1]
disable_ddl_transaction! # 本番テーブルへのインデックス追加（ロックなし）
def change
# postsテーブル
add_index :posts, :user_id,
algorithm: :concurrently # ロックなし（本番環境で重要）
add_index :posts, :created_at,
algorithm: :concurrently
add_index :posts, [:user_id, :created_at],
algorithm: :concurrently,
name: 'idx_posts_user_created'
# 論理削除（deleted_at）を使う場合の部分インデックス
add_index :posts, [:user_id, :created_at],
where: "deleted_at IS NULL",
name: 'idx_posts_active_user_created',
algorithm: :concurrently
# followsテーブル
add_index :follows, [:follower_id, :followee_id],
unique: true, # 重複フォロー防止
algorithm: :concurrently
add_index :follows, :followee_id,
algorithm: :concurrently
# usersテーブル
add_index :users, :username,
unique: true,
algorithm: :concurrently
end
endWARNING
インデックスはWRITEを遅くする。インデックスを追加するたびに、INSERT/UPDATE/DELETEでインデックスの更新も発生する。むやみに追加せず、クエリパターンを分析してから追加する。また、CONCURRENTLY オプションをつけないと本番のテーブルロックが発生するので必ず使うこと。
複合インデックスの設計
複合インデックスは左端から順番に使われる。設計を間違えると全く効果がない。
-- 複合インデックス idx_posts_user_created が (user_id, created_at) の場合
-- インデックスが使われるパターン ✅
SELECT * FROM posts WHERE user_id = 123;
SELECT * FROM posts WHERE user_id = 123 AND created_at > '2024-01-01';
SELECT * FROM posts WHERE user_id = 123 ORDER BY created_at DESC;
-- インデックスが使われないパターン ❌
SELECT * FROM posts WHERE created_at > '2024-01-01';
-- created_at 単体 → user_id が先なのでこのインデックスは使えない
-- 別途 created_at の単独インデックスが必要# インデックス設計の実例：Buzzのpostsテーブル
class AddOptimalIndexesToPosts < ActiveRecord::Migration[7.1]
disable_ddl_transaction!
def change
# パターン1: ユーザーのタイムライン取得
# WHERE user_id = ? ORDER BY created_at DESC LIMIT 50
add_index :posts, [:user_id, :created_at],
name: 'idx_posts_user_created',
algorithm: :concurrently
# パターン2: いいね数の多い投稿（トレンド）
# WHERE created_at > ? ORDER BY likes_count DESC
add_index :posts, [:created_at, :likes_count],
name: 'idx_posts_created_likes',
algorithm: :concurrently
# パターン3: 論理削除されていない投稿のみ（高選択性のフィルタ）
add_index :posts, [:user_id, :created_at],
where: "deleted_at IS NULL",
name: 'idx_posts_active',
algorithm: :concurrently
end
endカバリングインデックス
Index Only Scan を実現するカバリングインデックスは最も効果的な最適化の一つだ。テーブルへのアクセスが不要になるため、I/Oが激減する。
通常のインデックス（2回のアクセスが必要）
-- user_idのインデックスがある場合
SELECT id, content, created_at FROM posts WHERE user_id = 123;
-- 実行計画
Index Scan using idx_posts_user_id on posts
Index Cond: (user_id = 123)
-- ステップ1: インデックス（Bツリー）でuser_id=123の行を特定
-- ステップ2: テーブル（ヒープ）にアクセスしてcontent, created_atを取得
-- → 2段階のI/Oが必要
Execution Time: 12.3 msカバリングインデックス（テーブルアクセス不要）
# マイグレーション（PostgreSQL 11以降）
class AddCoveringIndexToPosts < ActiveRecord::Migration[7.1]
disable_ddl_transaction!
def change
# include でよく使うカラムをインデックスに含める
# インデックスのサイズは増えるが、テーブルアクセスが不要になる
add_index :posts, [:user_id, :created_at],
name: 'idx_posts_covering_timeline',
include: [:id, :content, :likes_count, :image_url],
algorithm: :concurrently
end
end-- カバリングインデックスを使ったクエリ
EXPLAIN ANALYZE
SELECT id, content, created_at, likes_count
FROM posts
WHERE user_id = 123
ORDER BY created_at DESC
LIMIT 50;
-- 実行計画（改善後）
Limit (cost=0.43..12.34 rows=50 width=256)
(actual time=0.089..0.234 rows=50 loops=1)
-> Index Only Scan Backward using idx_posts_covering_timeline on posts
(cost=0.43..98.76 rows=400 width=256)
(actual time=0.082..0.198 rows=50 loops=1)
Index Cond: (user_id = 123)
Heap Fetches: 0 ← テーブルアクセスゼロ！
Buffers: shared hit=5 ← I/Oが激減
Planning Time: 0.456 ms
Execution Time: 0.312 ms ← 12秒 → 0.3ms！（40,000倍！）全文検索の最適化
LIKE '%keyword%' は前方一致でないとインデックスが使えない。Buzzのハッシュタグ検索を改善する。
# 方法1: pg_trgm拡張（後方一致LIKEをインデックス化）
class EnableTrgmExtension < ActiveRecord::Migration[7.1]
def change
enable_extension 'pg_trgm'
# GINインデックス（全文検索向け、書き込みが遅いが検索が速い）
add_index :posts, :content,
using: :gin,
opclass: :gin_trgm_ops,
name: 'idx_posts_content_trgm'
# GISTインデックス（更新が速いが検索はGINより少し遅い）
# add_index :posts, :content,
# using: :gist,
# opclass: :gist_trgm_ops
end
end-- trgmインデックスを使った検索（LIKEが高速に）
EXPLAIN ANALYZE
SELECT id, content, likes_count
FROM posts
WHERE content LIKE '%#rails%';
-- 改善後
Bitmap Heap Scan on posts
Recheck Cond: ((content)::text ~~ '%#rails%'::text)
-> Bitmap Index Scan on idx_posts_content_trgm
Index Cond: ((content)::text ~~ '%#rails%'::text)
Execution Time: 45.678 ms ← 12秒 → 45ms！（266倍）# 方法2: pg_search gem（より高機能な全文検索）
# Gemfile
gem 'pg_search'
# app/models/post.rb
class Post < ApplicationRecord
include PgSearch::Model
# 複数フィールドを横断した全文検索
pg_search_scope :search_by_content,
against: {
content: 'A', # 重み: A（最高）
hashtags: 'B' # 重み: B
},
using: {
tsearch: {
prefix: true, # 前方一致も有効
dictionary: 'japanese', # 日本語辞書
any_word: true # いずれかの単語にマッチ
},
trigram: {
threshold: 0.1, # 類似度の閾値
only: [:content] # trigram はcontentのみに適用
}
},
associated_against: {
user: [:username, :bio] # 関連テーブルも検索対象に
}
# ハッシュタグの抽出（検索インデックス用）
before_save :extract_and_store_hashtags
private
def extract_and_store_hashtags
self.hashtags = content.scan(/#\w+/).map(&:downcase).uniq.join(' ')
end
end
# 使い方
Post.search_by_content('#rails')
.order(likes_count: :desc)
.limit(20)
.includes(:user)クエリの書き方で変わるパフォーマンス
同じ結果でもSQLの書き方でパフォーマンスが大きく変わる。
サブクエリ vs WITH（CTE）vs JOIN
# 悪い例: 相関サブクエリ（各行ごとに実行される O(N²)）
Post.where(
"likes_count > (
SELECT AVG(likes_count)
FROM posts p2
WHERE p2.user_id = posts.user_id
)"
)
# 良い例1: CTEで事前集計
Post.find_by_sql(<<~SQL)
WITH user_avg AS (
SELECT user_id, AVG(likes_count) as avg_likes
FROM posts
WHERE deleted_at IS NULL
GROUP BY user_id
)
SELECT posts.*
FROM posts
INNER JOIN user_avg ON posts.user_id = user_avg.user_id
WHERE posts.likes_count > user_avg.avg_likes
AND posts.deleted_at IS NULL
ORDER BY posts.created_at DESC
LIMIT 50
SQL
# 良い例2: ウィンドウ関数（PostgreSQL 8.4+）
Post.find_by_sql(<<~SQL)
SELECT *
FROM (
SELECT *,
AVG(likes_count) OVER (PARTITION BY user_id) as user_avg_likes
FROM posts
WHERE deleted_at IS NULL
) subq
WHERE likes_count > user_avg_likes
ORDER BY created_at DESC
LIMIT 50
SQLEXISTS vs COUNT vs ANY
# 最悪: COUNT は全件数える（全行スキャン）
if User.where(id: follower_ids).count > 0
# ...
end
# SQL: SELECT COUNT(*) FROM users WHERE id IN (...)
# 良い: EXISTS は1件見つかった時点で終了
if User.where(id: follower_ids).exists?
# ...
end
# SQL: SELECT 1 FROM users WHERE id IN (...) LIMIT 1
# さらに良い: Ruby側で判断（DBにアクセスしない）
if follower_ids.any?
# ...
endSELECT * の回避
# 悪い例: 全カラム取得（biographyなどの大きなカラムも含む）
users = User.all
users.each { |u| puts u.username }
# SELECT id, username, email, bio, settings, avatar_url, ...（全部）FROM users
# 良い例: 必要なカラムのみ
users = User.select(:id, :username, :avatar_url)
users.each { |u| puts u.username }
# SELECT id, username, avatar_url FROM users
# 転送データ量が激減（bio, settings等の大きなカラムを省略）
# Pluck: オブジェクト不要なら最速
usernames = User.pluck(:username)
# SELECT username FROM users
# ActiveRecord オブジェクトを作成しないため、メモリ効率が最高Railsでのクエリ最適化パターン
# app/models/post.rb
class Post < ApplicationRecord
belongs_to :user
has_many :comments
has_many :likes
# スコープで再利用可能なクエリを定義
scope :recent, -> { where(created_at: 7.days.ago..) }
scope :popular, -> { where('likes_count > ?', 100) }
scope :not_deleted, -> { where(deleted_at: nil) }
scope :with_author, -> { includes(:user) }
# タイムライン取得（最適化済み）
def self.timeline_for(user_id:, page: 1, per: 20)
# フォロー中のユーザーIDを1クエリで取得
following_ids = Follow.where(follower_id: user_id)
.select(:followee_id)
.limit(5000) # フォロー数に上限を設ける
not_deleted
.where(user_id: following_ids)
.recent
.with_author
.select('posts.id, posts.content, posts.likes_count,
posts.created_at, posts.user_id, posts.image_url')
.order(created_at: :desc)
.page(page).per(per) # kaminari pagination
end
# カウンターキャッシュでいいね数をキャッシュ
# マイグレーション: add_column :posts, :likes_count, :integer, default: 0
def self.increment_likes_count(post_id)
Post.where(id: post_id).update_all('likes_count = likes_count + 1')
# update_allは1クエリ、Active Recordオブジェクトを作成しない
end
end# config/initializers/query_logging.rb
# 本番でも遅いクエリを記録してAlertを送る
if Rails.env.production?
ActiveSupport::Notifications.subscribe('sql.active_record') do |*args|
event = ActiveSupport::Notifications::Event.new(*args)
next if event.payload[:name].in?(['SCHEMA', 'TRANSACTION'])
next if event.duration < 500 # 500ms未満はスキップ
Rails.logger.warn({
type: 'slow_query',
duration_ms: event.duration.round,
sql: event.payload[:sql].truncate(500),
binds: event.payload[:type_casted_binds]
}.to_json)
# Datadog にカスタムメトリクスを送る
StatsD.histogram('db.query.duration', event.duration,
tags: ["query:#{event.payload[:name]}"])
end
endパーティショニングで大テーブルを分割
Buzzのpostsテーブルが2,000万行を超えてきた。レンジパーティショニングで分割する。
class PartitionPostsTable < ActiveRecord::Migration[7.1]
def up
# 既存テーブルをパーティションテーブルに移行する
# 本番では: 新テーブル作成 → データ移行 → 切り替え の手順が安全
# ステップ1: パーティションテーブル作成
execute <<~SQL
CREATE TABLE posts_partitioned (
id BIGSERIAL,
user_id BIGINT NOT NULL,
content TEXT,
likes_count INT DEFAULT 0,
deleted_at TIMESTAMP,
created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (id, created_at) -- パーティションキーをPKに含める必要あり
) PARTITION BY RANGE (created_at);
SQL
# ステップ2: 月次パーティション作成
(1..12).each do |month|
next_month = month == 12 ? 1 : month + 1
next_year = month == 12 ? 2025 : 2024
execute <<~SQL
CREATE TABLE posts_2024_#{format('%02d', month)}
PARTITION OF posts_partitioned
FOR VALUES FROM ('2024-#{format('%02d', month)}-01')
TO ('#{next_year}-#{format('%02d', next_month)}-01');
-- 各パーティションにインデックス（自動的に作成される場合もある）
CREATE INDEX ON posts_2024_#{format('%02d', month)} (user_id, created_at DESC);
CREATE INDEX ON posts_2024_#{format('%02d', month)} (likes_count DESC, created_at DESC)
WHERE deleted_at IS NULL;
SQL
end
# ステップ3: デフォルトパーティション（範囲外データ用）
execute <<~SQL
CREATE TABLE posts_default
PARTITION OF posts_partitioned DEFAULT;
SQL
end
end-- パーティショニング後のクエリ（2024年1月のデータのみスキャン）
EXPLAIN ANALYZE
SELECT * FROM posts_partitioned
WHERE created_at BETWEEN '2024-01-01' AND '2024-01-31'
AND user_id = 123
ORDER BY created_at DESC
LIMIT 50;
-- 出力
Index Scan Backward using posts_2024_01_user_id_created_at_idx on posts_2024_01
(cost=0.43..45.67 rows=50 width=1024)
(actual time=0.082..0.456 rows=50 loops=1)
Index Cond: ((user_id = 123) AND (created_at BETWEEN '2024-01-01' AND '2024-01-31'))
Planning Time: 2.345 ms
Execution Time: 1.234 ms
-- Partition pruning: posts_2024_01 のみスキャン（2000万行 → 30万行）INFO
PostgreSQLのパーティショニングは、クエリに必ずパーティションキー（ここでは created_at）を含めないとパーティションプルーニングが働かない。WHERE句にパーティションキーを入れることを徹底する。ルールをRailsのスコープに組み込むと安全だ。
コネクションプールの最適化（PgBouncer）
データベースへの接続は「重い」操作だ。接続確立のたびにTCPハンドシェイク、認証、セッション初期化が走る。PgBouncerは接続を再利用することでこのオーバーヘッドを排除する。
# docker-compose.yml（ECSサイドカーとして動かすことも可能）
version: '3.8'
services:
pgbouncer:
image: pgbouncer/pgbouncer:1.21.0
environment:
POSTGRESQL_HOST: "buzz-aurora.cluster-xxx.ap-northeast-1.rds.amazonaws.com"
POSTGRESQL_PORT: "5432"
PGBOUNCER_DATABASE: "*"
PGBOUNCER_POOL_MODE: "transaction" # トランザクション単位でプール（最効率）
PGBOUNCER_MAX_CLIENT_CONN: "1000" # Rails → PgBouncer の最大接続数
PGBOUNCER_DEFAULT_POOL_SIZE: "25" # PgBouncer → Aurora の実際の接続数
PGBOUNCER_MIN_POOL_SIZE: "5" # 最小プールサイズ
PGBOUNCER_RESERVE_POOL_SIZE: "5" # 緊急用予備プール
PGBOUNCER_RESERVE_POOL_TIMEOUT: "3" # 予備プール使用開始までの秒数
PGBOUNCER_SERVER_IDLE_TIMEOUT: "600" # アイドル接続の切断（秒）
ports:
- "6432:6432"PgBouncer 導入効果:
導入前:
Rails 30タスク × 5スレッド = 150接続
Sidekiq 25スレッド = 25接続
合計: 175接続 → Aurora の上限に近い！
導入後:
Rails/Sidekiq → PgBouncer: 1000接続まで受け付け可能
PgBouncer → Aurora: わずか25接続
Aurora の負荷: 175接続 → 25接続（86%削減）
# config/database.yml（PgBouncer経由に変更）
production:
adapter: postgresql
host: localhost # PgBouncerがlocalhostで動いている場合
port: 6432 # PgBouncerのポート（Auroraは5432）
database: buzz_production
username: <%= ENV['DB_USERNAME'] %>
password: <%= ENV['DB_PASSWORD'] %>
pool: 5 # Railsスレッド数と合わせる（PgBouncerが1000まで受ける）
checkout_timeout: 5 # プール枯渇時のタイムアウトWARNING
PgBouncerを transaction モードで使うと、Railsの ActiveRecord::Base.transaction ブロック外でセッション変数を使う処理（例: SET search_path）が正しく動かないことがある。Advisory Lock, prepared statements, LISTEN/NOTIFY は transaction モードでは使えない。これらが必要な場合は session モードを使う（接続共有効率は下がる）。
インデックスの監視と不要インデックスの削除
インデックスは追加するだけでなく、使われていないインデックスを定期的に削除することも重要だ。
-- 使われていないインデックスを調べる
SELECT
schemaname,
tablename,
indexname,
idx_scan, -- インデックスが使われた回数
idx_tup_read, -- インデックスで読まれた行数
idx_tup_fetch, -- 実際に取得した行数
pg_size_pretty(pg_relation_size(indexrelid)) as size
FROM pg_stat_user_indexes
WHERE schemaname = 'public'
AND idx_scan = 0 -- 一度も使われていないインデックス
ORDER BY pg_relation_size(indexrelid) DESC;-- テーブルとインデックスのサイズ確認
SELECT
tablename,
pg_size_pretty(pg_total_relation_size(tablename::regclass)) as total_size,
pg_size_pretty(pg_relation_size(tablename::regclass)) as table_size,
pg_size_pretty(
pg_total_relation_size(tablename::regclass) -
pg_relation_size(tablename::regclass)
) as index_size
FROM pg_tables
WHERE schemaname = 'public'
ORDER BY pg_total_relation_size(tablename::regclass) DESC;# インデックス使用状況をRailsから確認するタスク
# lib/tasks/db_analysis.rake
namespace :db do
desc "未使用インデックスを表示"
task unused_indexes: :environment do
sql = <<~SQL
SELECT
tablename,
indexname,
idx_scan,
pg_size_pretty(pg_relation_size(indexrelid)) as size
FROM pg_stat_user_indexes
WHERE idx_scan < 10
AND indexname NOT LIKE '%pkey%'
AND schemaname = 'public'
ORDER BY pg_relation_size(indexrelid) DESC
LIMIT 20;
SQL
results = ActiveRecord::Base.connection.execute(sql)
puts "未使用インデックス（使用回数10回未満）:"
results.each do |row|
puts " #{row['tablename']}.#{row['indexname']}: #{row['size']} (#{row['idx_scan']}回)"
end
end
end改善前後の比較
一日かけてデータベース最適化を施した結果。
Loading diagram...
| エンドポイント | 最適化前 | 最適化後 | 改善率 |
|---|---|---|---|
| タイムライン | 12,456ms | 45ms | 277倍 |
| ハッシュタグ検索 | 8,934ms | 23ms | 388倍 |
| ユーザー検索 | 3,421ms | 8ms | 428倍 |
| DB接続数 | 175接続 | 25接続 | 86%削減 |
| エラー率 | 12.3% | 0.1% | 123倍改善 |
「これだけで10万ユーザーには耐えられる」とアキラは言った。「でも次のスパイクに備えて、読み取りのスケーリングが必要だ。次はRead Replicaを入れよう」
データベース最適化で基盤は整った。しかし単一サーバーでは読み取りの限界がある。次章では、Aurora Read ReplicaとRedisキャッシュで読み取りをスケールアウトする方法を学ぶ。
付録: データベース最適化チェックリスト
インデックス:
□ N+1クエリが発生していないか（Bullet gemで確認）
□ 頻繁に使うWHERE句のカラムにインデックスがあるか
□ ORDER BY に使うカラムにインデックスがあるか
□ 複合インデックスの順序は正しいか（左端優先）
□ 使われていないインデックスを削除したか
□ 本番での CONCURRENTLY オプションを忘れていないか
クエリ:
□ SELECT * を使っていないか（必要なカラムのみ）
□ COUNT(*) > 0 の代わりに exists? を使っているか
□ LIKE '%keyword%' をtrgmで高速化しているか
□ 相関サブクエリを JOIN に書き換えているか
テーブル設計:
□ テーブルが大きくなってきたらパーティショニングを検討
□ 論理削除（deleted_at）を使うなら部分インデックスを追加
□ カウンター（likes_count等）は update_all で更新
接続管理:
□ DB接続プール数 = Pumaスレッド数 に設定しているか
□ PgBouncer を導入して接続数を削減しているか