0
0

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?

@harapeco-mgn

第17章｜今さら学ぶ「DB・SQL」

0
Last updated at Posted at 2026-02-23

第17章｜今さら学ぶ「DB・SQL」

📚 シリーズ目次はこちら → 「今さら学ぶ」シリーズ — はじめに
🗺️ KnowledgeNoteの設計を確認 → 設計マップ

この章でわかること

  • 正規化 — なぜテーブルを分けるのか
  • インデックス — 本の索引がなぜ必要か
  • 外部キー制約 — 「存在しない人を参照しない」ための約束
  • N+1問題 — 1人ずつ電話する vs 一斉メール
  • トランザクション — 「全部成功」か「全部取り消し」の仕組み
  • Seed / Faker — 開発用のダミーデータを一発投入する

🏠 たとえ話で掴む「データベース設計」

データベース設計は 住所録の整理 にたとえるとわかりやすいです。

友人の連絡先を管理するとき、全員の情報を1枚の巨大な紙にまとめると、引っ越しのたびに何箇所も書き直す羽目になります。「住所は住所の表」「名前は名前の表」と分けて管理し、番号で紐づければ、変更は1箇所で済みます。

この「分けて紐づける」がデータベース設計の核心です。

📖 リレーショナルデータベースの基本用語

技術的に整理すると、KnowledgeNoteが使っている リレーショナルデータベース（RDB） は、データを テーブル（表） の形で管理するデータベースです。PostgreSQL、MySQL、SQLiteなどが代表的な製品です。

用語 意味 たとえ
テーブル データの入れ物（表） Excelのシート
行（レコード / row） 1件のデータ シートの1行
カラム（列 / column） データの項目 シートの列見出し
主キー（Primary Key） 各行を一意に識別するID 社員番号
外部キー（Foreign Key） 他のテーブルの主キーを参照するカラム 部署番号（部署テーブルへのリンク）
SQL RDBに対する問い合わせ言語 データベースへの「注文書」

RailsではActiveRecord（→ 第15章）がSQLを自動生成してくれますが、この章ではActiveRecordの裏側で何が起きているかを理解するため、DB設計の原則とSQLの基礎を掘り下げます。

📐 正規化 — テーブルを分ける理由

正規化していないテーブルの問題

❌ 正規化していない1枚テーブル
+------+--------+-----------------+-----------+
| 記事  | 著者名  | 著者メール       | タグ       |
+------+--------+-----------------+-----------+
| Ruby | 田中   | tanaka@mail.com | Ruby,初心者 |
| Rails| 田中   | tanaka@mail.com | Rails      |
| SQL  | 鈴木   | suzuki@mail.com | DB,SQL     |
+------+--------+-----------------+-----------+

問題①：田中さんのメールが変わったら2行修正が必要（更新異常）
問題②：田中さんの記事を全部消すと、田中さんの情報も消える（削除異常）
問題③：タグが「Ruby,初心者」とカンマ区切り → 検索しにくい

正規化したテーブル

✅ 正規化（テーブルを分ける）

users テーブル           articles テーブル         tags / article_tags
+----+------+---------+  +----+-------+---------+  +----+------+
| id | name | email   |  | id | title | user_id |  | id | name |
+----+------+---------+  +----+-------+---------+  +----+------+
|  1 | 田中 | t@m.com |  |  1 | Ruby  |       1 |  |  1 | Ruby |
|  2 | 鈴木 | s@m.com |  |  2 | Rails |       1 |  |  2 | 初心者|
+----+------+---------+  |  3 | SQL   |       2 |  |  3 | Rails|
                          +----+-------+---------+  +----+------+

→ 田中さんのメールを変えても users テーブルの1行だけ修正すればOK
→ タグは1つずつ独立した行なので検索しやすい

正規化の段階（第1〜第3正規形）の詳細は奥が深いですが、面接で聞かれる基本は以下の通りです。

正規形 ルール KnowledgeNoteでの例
第1正規形 1つのセルに複数の値を入れない タグを「Ruby,Rails」とカンマ区切りにしない → tags テーブルに分離
第2正規形 主キーの一部にだけ依存するカラムを排除する 中間テーブル article_tags に tag_name を持たせない（tag_name は tags テーブルの属性）
第3正規形 主キー以外のカラムに依存するカラムを排除する articles に author_email を持たせない（email は users テーブルの属性）

実務で意識するのは主に以下の3点です。

① 1つのセルに複数の値を入れない（「Ruby,Rails」→ 別テーブルに分ける）
② 部分的な重複を排除する（著者情報を articles に持たず users に分ける）
③ 推移的な依存を排除する（AがBに依存、BがCに依存 → Cも別テーブルに）

📑 インデックス — 本の索引

インデックス は、DBの検索を高速にするための仕組みです。本の巻末にある「索引」と同じです。

索引がなければ、「ポリモーフィック」という単語を探すのに300ページを1ページずつめくる必要があります。索引があれば「ポリモーフィック → p.152」と一発でたどり着けます。

インデックスの追加方法

# マイグレーションでインデックスを追加
class AddIndexToArticles < ActiveRecord::Migration[8.0]
  def change
    # 単一カラムのインデックス
    add_index :articles, :user_id

    # 複合インデックス（ユーザーごとの記事を日付順で検索する場合）
    add_index :articles, [:user_id, :created_at]

    # ユニークインデックス（重複を防ぐ）
    add_index :users, :email_address, unique: true
  end
end

どこにインデックスを貼るか

■ 必ず貼る
  → 外部キー（user_id, article_id 等）
  → uniqueness バリデーションがあるカラム（email_address 等）

■ 貼ると効果的
  → WHERE句でよく使うカラム（published, status 等）
  → ORDER BY でよく使うカラム（created_at 等）

■ 貼りすぎ注意
  → INSERT / UPDATE が遅くなる（インデックスの更新コスト）
  → めったに検索しないカラムには不要

🔗 外部キー制約 — 存在しないデータを参照させない

外部キー制約 は、「存在しないユーザーの記事を作らせない」ための仕組みです。

# マイグレーション
create_table :articles do |t|
  t.references :user, null: false, foreign_key: true
  # ↑ foreign_key: true で外部キー制約をDBレベルで設定
end

# 外部キー制約がないと…
Article.create(title: "テスト", body: "本文", user_id: 99999)
# → user_id: 99999 のユーザーが存在しなくても保存できてしまう！

# 外部キー制約があると…
Article.create(title: "テスト", body: "本文", user_id: 99999)
# → ActiveRecord::InvalidForeignKey エラー（DBが拒否）

Rails（Ruby）のバリデーションだけでなく、 DBレベルでもデータの整合性を守る のが実務のベストプラクティスです。

🔁 N+1問題 — 1人ずつ電話する vs 一斉メール

第15章では includes による解決方法を紹介しました。ここではもう少し掘り下げて、 複数のアソシエーションを含むケース と 検出ツール（Bullet gem） を扱います。

たとえ話

30人のクラスメイトに連絡するとき、1人ずつ電話をかけると30回の通話が必要です。でも一斉メールなら1通で済みます。

# ❌ N+1問題（1人ずつ電話）
articles = Article.all                    # 1回目のSQL
articles.each do |article|
  puts article.user.name                  # 記事の数だけSQLが走る（N回）
  puts article.tags.map(&:name).join(",") # さらにN回！
end
# → 合計 1 + N + N 回のSQL

# ✅ includes で解決（一斉メール）
articles = Article.includes(:user, :tags).all  # 3回のSQLで全部取得
articles.each do |article|
  puts article.user.name                  # 追加SQLなし
  puts article.tags.map(&:name).join(",") # 追加SQLなし
end

N+1問題の検出

# Gemfile（開発環境で使うN+1検出ツール）
group :development do
  gem "bullet"
end

Bullet gem を入れると、N+1問題が発生したときに ブラウザ上に警告 を表示してくれます。パフォーマンス全般については（→ 第24章で詳しく扱います）。

🔒 トランザクション — 全部成功か全部取り消し

トランザクション は、「複数の処理を1つのまとまりとして扱い、途中で失敗したら全部なかったことにする」仕組みです。

データベースのトランザクションには ACID と呼ばれる4つの特性があります。

特性 意味 一言で
A tomicity（原子性） 全部成功か全部取り消し。中途半端な状態にならない 「半分だけ完了」はありえない
C onsistency（一貫性） トランザクション前後でデータの整合性が保たれる 残高がマイナスにならない等
I solation（分離性） 同時に走る他のトランザクションの影響を受けない 並行処理でも結果が狂わない
D urability（永続性） コミット済みのデータは障害が起きても失われない 停電しても消えない

面接で「トランザクションの特性は？」と聞かれたら、ACIDの4つを挙げられれば十分です。

たとえ話：銀行振込

AさんからBさんに1万円を振り込むとき、「Aの口座から引く」と「Bの口座に足す」は必ずセットで実行されないと困ります。Aから引いた後にエラーが起きてBに入金されなかったら、1万円が消えてしまいます。

# KnowledgeNoteでの例：いいね＋通知を同時に処理
ActiveRecord::Base.transaction do
  like = current_user.likes.create!(likeable: article)  # いいねを保存
  Notification.create!(                                   # 通知を作成
    user: article.user,
    notifiable: like,
    action_type: "liked"
  )
end
# → 両方成功すればコミット（確定）
# → どちらかが失敗すればロールバック（全部取り消し）

トランザクションが必要な場面

■ 必要な場面
  → 複数テーブルを同時に更新するとき
  → 「いいね + 通知」「購入 + 在庫減少」「送金 + 入金」

■ 不要な場面
  → 1つのレコードだけの保存（saveは内部でトランザクションを使っている）

⚠️ トランザクション内では save ではなく save!（create! / update!）を使います。save は失敗時に false を返すだけでロールバックしませんが、save! は例外を発生させるのでトランザクションがロールバックされます。

🌱 Seed / Faker — 開発用ダミーデータの投入

Seed（シードデータ）

開発環境でアプリを動かすには、DBにデータが必要です。 Seed は初期データを一発で投入する仕組みです。

# db/seeds.rb
puts "🌱 シードデータを作成中..."

# ユーザーを作成
users = 5.times.map do |i|
  User.create!(
    name: Faker::Name.name,
    email_address: "user#{i + 1}@example.com",
    password: "password",
    password_confirmation: "password"
  )
end

# タグを作成
tag_names = %w[Ruby Rails SQL JavaScript Docker Git]
tags = tag_names.map { |name| Tag.create!(name: name) }

# 記事を作成
users.each do |user|
  rand(3..8).times do
    article = user.articles.create!(
      title: Faker::Lorem.sentence(word_count: 5),
      body: Faker::Lorem.paragraphs(number: 5).join("\n\n"),
      status: [:draft, :published, :published].sample  # 公開記事を多めに
    )
    # ランダムにタグをつける
    article.tags << tags.sample(rand(1..3))
  end
end

puts "✅ 完了！ユーザー: #{User.count}, 記事: #{Article.count}, タグ: #{Tag.count}"

$ rails db:seed    # シードデータ投入
$ rails db:reset   # DB削除 → 再作成 → マイグレーション → シード

Seed の冪等性 — 何度実行してもOKにする

上のシードファイルは rails db:seed を2回実行すると、同じデータが重複して作られてしまいます。何度実行しても同じ結果になる（ 冪等性がある）設計にしておくと安全です。

# find_or_create_by! を使うと、既に存在すれば作成をスキップする
tag_names = %w[Ruby Rails SQL JavaScript Docker Git]
tags = tag_names.map { |name| Tag.find_or_create_by!(name: name) }

# ユーザーも同様
users = 5.times.map do |i|
  User.find_or_create_by!(email_address: "user#{i + 1}@example.com") do |u|
    u.name = Faker::Name.name
    u.password = "password"
    u.password_confirmation = "password"
  end
end

find_or_create_by! は「見つかればそれを返す、なければ作る」メソッドです。チーム開発ではこのパターンを使っておくと、誰がいつ rails db:seed を実行しても安心です。テスト用データの生成（FactoryBot）については（→ 第22章で詳しく扱います）。

Faker — ダミーデータ生成

# Gemfile
group :development, :test do
  gem "faker"
end

# デフォルトは英語。日本語名を使う場合はロケールを設定する
Faker::Config.locale = 'ja'

Faker::Name.name           # => "佐藤 太郎"（ja ロケール時）
Faker::Internet.email      # => "taro.sato@example.com"
Faker::Lorem.sentence      # => ダミーの日本語テキスト
Faker::Lorem.paragraphs(number: 3)  # => 3段落のダミーテキスト

🛠️ KnowledgeNoteでの具体例：SQL を見てみよう

ActiveRecordが生成するSQLを確認してみます。

# Rubyコード
Article.published.recent.includes(:user, :tags).page(1).per(20)

-- 生成されるSQL
SELECT "articles".* FROM "articles"
  WHERE "articles"."status" = 1
  ORDER BY "articles"."created_at" DESC
  LIMIT 20 OFFSET 0;

SELECT "users".* FROM "users"
  WHERE "users"."id" IN (1, 2, 3);

SELECT "tags".* FROM "tags"
  INNER JOIN "article_tags" ON "tags"."id" = "article_tags"."tag_id"
  WHERE "article_tags"."article_id" IN (1, 2, 3, 4, 5);

3回のSQLで、記事20件 + 著者 + タグを全て取得できています。ActiveRecordを使うことでSQLインジェクションも防げます（→ 第20章で詳しく扱います）。

💼 面接で聞かれたら？

Q：N+1問題について説明してください。

「N+1問題とは、ループ内で関連データを取得するたびにSQLが発行される問題です。記事一覧で各記事の著者名を表示する場合、記事の取得に1回、著者の取得にN回（記事の数だけ）SQLが走り、合計N+1回になります。includes を使って関連データを事前にまとめて読み込むことで、2〜3回のSQLに削減できます。」

深掘りされたら：

  • 「インデックスとは？」→ DBの検索を高速にするための索引。外部キーやWHERE句で頻繁に使うカラムに設定する。ただし更新時のコストが増えるため、必要な箇所にだけ貼る。
  • 「トランザクションとは？」→ 複数のDB操作を1つのまとまりとして扱い、全て成功すればコミット、1つでも失敗すればロールバックする仕組み。データの整合性を保証する。

🔗 もっと深く知りたい人へ（1次情報リンク）

まとめ

  • ✅ 正規化はテーブルを分けて重複を排除する設計手法。更新・削除の異常を防ぐ
  • ✅ インデックスは検索高速化の「索引」。外部キーとWHERE頻出カラムには必ず貼る
  • ✅ 外部キー制約でDBレベルのデータ整合性を守る
  • ✅ N+1問題は includes で解決。Bullet gem で検出できる
  • ✅ トランザクションで複数テーブルの更新を「全部成功 or 全部取り消し」にする
  • ✅ Seed + Faker で開発用ダミーデータを一発投入

📚 シリーズ目次：「今さら学ぶ」シリーズ — はじめに

0
0
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
Sign upLogin
0
0

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?