N+1問題によるクエリ発行数の増大を防ぐ方法｜ORM別の検出・対策を徹底解説

-- テーブル定義
CREATE TABLE posts (
    id SERIAL PRIMARY KEY,
    title VARCHAR(255) NOT NULL,
    body TEXT
);

CREATE TABLE comments (
    id SERIAL PRIMARY KEY,
    post_id INTEGER REFERENCES posts(id),
    content TEXT NOT NULL
);

-- 1回目：全記事を取得
SELECT * FROM posts;

-- 記事ごとにコメントを取得（N回）
SELECT * FROM comments WHERE post_id = 1;
SELECT * FROM comments WHERE post_id = 2;
SELECT * FROM comments WHERE post_id = 3;
-- ... post_id = N まで繰り返し

SELECT p.id, p.title, c.id AS comment_id, c.content
FROM posts p
LEFT JOIN comments c ON p.id = c.post_id;

-- 1回目：全記事を取得
SELECT * FROM posts;

-- 2回目：コメントをまとめて取得
SELECT * FROM comments WHERE post_id IN (1, 2, 3, ..., N);

# コントローラー
@posts = Post.all

# ビュー（各postごとにcommentsへクエリが飛ぶ）
@posts.each do |post|
  post.comments.each do |comment|
    puts comment.content
  end
end

# includes: Railsが最適な方法を自動選択
@posts = Post.includes(:comments).all

# preload: IN句で別クエリとして取得（2回）
@posts = Post.preload(:comments).all

# eager_load: LEFT JOINで1クエリに結合
@posts = Post.eager_load(:comments).all

# 各blogのauthorにアクセスするたびにクエリが発行される
blogs = Blog.objects.all()
for blog in blogs:
    print(blog.author.name)

# select_related: JOINで一括取得（ForeignKey/OneToOneField向け）
blogs = Blog.objects.select_related('author').all()

# prefetch_related: IN句で別クエリとして取得（ManyToManyField向け）
blogs = Blog.objects.prefetch_related('tags').all()

$posts = Post::all();
foreach ($posts as $post) {
    // 毎回クエリが発行される
    echo $post->comments->count();
}

// IN句で関連データを事前に一括取得
$posts = Post::with('comments')->get();

// ネストした関連も指定できる
$posts = Post::with(['comments', 'comments.user'])->get();

// 条件付きEager Loading
$posts = Post::with(['comments' => function ($query) {
    $query->where('approved', true);
}])->get();

var users []User
db.Find(&users)

for _, user := range users {
    var books []Book
    // ユーザーごとに個別クエリ
    db.Where("user_id = ?", user.ID).Find(&books)
    fmt.Println(user.Name, len(books))
}

// Preload: IN句で一括取得
var users []User
db.Preload("Books").Find(&users)

// Joins: JOINクエリを明示的に構築
var users []User
db.Joins("LEFT JOIN books ON books.user_id = users.id").
    Select("users.*, COUNT(books.id) as book_count").
    Group("users.id").
    Find(&users)

// 1回目：ユーザー一覧を取得
rows, _ := db.Query("SELECT id, name FROM users")
var userIDs []int
userMap := make(map[int]User)
for rows.Next() {
    var u User
    rows.Scan(&u.ID, &u.Name)
    userIDs = append(userIDs, u.ID)
    userMap[u.ID] = u
}

// 2回目：IN句でまとめて取得
query, args, _ := sqlx.In(
    "SELECT * FROM books WHERE user_id IN (?)", userIDs,
)
var books []Book
db.Select(&books, query, args...)

@Entity
public class Company {
    @OneToMany(mappedBy = "company", fetch = FetchType.LAZY)
    private List<Employee> employees;
}

// companiesを取得した後、employees にアクセスするたびに追加クエリ
List<Company> companies = companyRepository.findAll();
for (Company c : companies) {
    System.out.println(c.getEmployees().size()); // ここでN回クエリ
}

// JOIN FETCHを使った方法
@Query("SELECT c FROM Company c LEFT JOIN FETCH c.employees")
List<Company> findAllWithEmployees();

// EntityGraphを使った方法
@EntityGraph(attributePaths = {"employees"})
List<Company> findAll();

# Gemfile
gem 'bullet', group: :development

# config/environments/development.rb
config.after_initialize do
  Bullet.enable = true
  Bullet.alert = true        # ブラウザにアラート表示
  Bullet.console = true      # ブラウザコンソールに出力
  Bullet.rails_logger = true # Railsログに出力
end

pip install django-debug-toolbar

# settings.py
INSTALLED_APPS += ['debug_toolbar']
MIDDLEWARE += ['debug_toolbar.middleware.DebugToolbarMiddleware']
INTERNAL_IPS = ['127.0.0.1']

pip install nplusone

INSTALLED_APPS += ['nplusone.ext.django']
MIDDLEWARE += ['nplusone.ext.django.NPlusOneMiddleware']
NPLUSONE_RAISE = True  # N+1検出時に例外を発生させる

// AppServiceProvider.php
use Illuminate\Database\Eloquent\Model;

public function boot(): void
{
    // 開発環境で遅延ロードを禁止（N+1が発生すると例外をスロー）
    Model::preventLazyLoading(! app()->isProduction());
}

-- MySQL：一般クエリログを有効化
SET GLOBAL general_log = 'ON';
SET GLOBAL log_output = 'TABLE';

-- ログの確認
SELECT * FROM mysql.general_log
ORDER BY event_time DESC
LIMIT 50;

-- PostgreSQL：postgresql.conf
log_statement = 'all'
log_min_duration_statement = 0

# Railsの場合：preloadが適切
Post.preload(:tags, :categories)

# eager_loadだとデカルト積が発生して逆効果
Post.eager_load(:tags, :categories)

-- 子レコード数だけが必要な場合
SELECT p.id, p.title, (
    SELECT COUNT(*) FROM comments c WHERE c.post_id = p.id
) AS comment_count
FROM posts p;

# Rails
class Post < ApplicationRecord
  default_scope { includes(:author) }
end

# Django: Managerをカスタマイズ
class PostManager(models.Manager):
    def get_queryset(self):
        return super().get_queryset().select_related('author')

class Post(models.Model):
    objects = PostManager()

// NG：ネストが深く、N+1が多段で発生しやすい
{
  "posts": [{
    "id": 1,
    "author": { "name": "...", "profile": { "bio": "..." } },
    "comments": [{ "user": { "name": "..." } }]
  }]
}

// OK：フラットにして必要なIDだけ返す
{
  "posts": [{ "id": 1, "author_id": 10 }],
  "authors": [{ "id": 10, "name": "..." }]
}

# RSpec + Bulletの例
RSpec.configure do |config|
  config.before(:each) do
    Bullet.start_request
  end

  config.after(:each) do
    Bullet.perform_out_of_channel_notifications if Bullet.notification?
    Bullet.end_request
  end
end