LoginSignup
1
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?

@Dragon1208(AGATA)

【初学者用】RailsにおけるSQLアンチパターンの解説と対応策

Last updated at Posted at 2024-10-05

はじめに

今までNext.jsやTypeScriptでフロントからバックまで触っていたのですが、Rails1年生となって開発に携わっている中で、遭遇する可能性のある主要なSQLアンチパターンとその解決方法をまとめました。

初めてRailsを通してDB設計をする方の参考になれば幸いです。

目次

  1. 文字列連結によるSQLインジェクション
  2. N+1クエリ問題
  3. 大量データの一括読み込み
  4. 不適切なインデックス設計
  5. 過度の正規化
  6. 単一テーブル継承（STI）の過剰使用
  7. 貪欲なフェッチ
  8. 不要なカラムの取得

1. 文字列連結によるSQLインジェクション

問題点

ユーザー入力を直接SQLクエリに組み込むと、SQLインジェクション攻撃の危険性があります。

悪い例

class UsersController < ApplicationController
  def search
    name = params[:name]
    @users = User.where("name = '#{name}'")
  end
end

解決策

プレースホルダーやパラメータ化されたクエリを使用します。

class UsersController < ApplicationController
  def search
    name = params[:name]
    @users = User.where("name = ?", name)
  end
end

2. N+1クエリ問題

N+1クエリ問題がわからないという方はこちら👇

問題点

関連するレコードを取得する際に、メインのクエリに加えて各レコードに対して別個のクエリが発行され、データベースへの不必要なアクセスが増加します。

悪い例

class PostsController < ApplicationController
  def index
    @posts = Post.all
  end
end

# View
<% @posts.each do |post| %>
  <h2><%= post.title %></h2>
  <p>Author: <%= post.author.name %></p>
<% end %>

※ post.author が呼び出されると、Active Recordは著者データを取得するための追加のクエリを発行します。

つまりこの例では、Post.allで全ての投稿を取得した後、ビュー内の各投稿に対してpost.author.nameを呼び出すたびに、以下のようにデータベースへの追加クエリが発生します。

SELECT * FROM posts;  -- メインクエリ
SELECT * FROM authors WHERE id = 1;  -- 1番目の投稿の著者を取得
SELECT * FROM authors WHERE id = 2;  -- 2番目の投稿の著者を取得
SELECT * FROM authors WHERE id = 3;  -- 3番目の投稿の著者を取得
-- ... 以下、投稿の数だけ著者を取得するクエリが続く

解決策

includesメソッドを使用して、関連するレコードを事前に読み込みます。

class PostsController < ApplicationController
  def index
    @posts = Post.includes(:author).all
  end
end

こうすることで以下のようなクエリに収まります

SELECT * FROM posts;  -- メインクエリ
SELECT * FROM authors WHERE id IN (1, 2, 3, ...);  -- 関連する全ての著者を一度に取得

3. 大量データの一括読み込み

問題点

大量のレコードを一度に読み込むと、メモリ使用量が急増し、アプリケーションのパフォーマンスが低下する可能性があります。

悪い例

class NewsletterJob < ApplicationJob
  def perform
    users = User.all
    users.each do |user|
      NewsletterMailer.send_newsletter(user).deliver_now
    end
  end
end

この例では、User.allによってすべてのユーザーレコードが一度にメモリに読み込まれます。ユーザー数が多い場合、これは深刻なメモリ問題を引き起こす可能性があります。

解決策

find_eachメソッドを使用して、バッチ処理を行います。

class NewsletterJob < ApplicationJob
  def perform
    User.find_each(batch_size: 1000) do |user|
      NewsletterMailer.send_newsletter(user).deliver_later
    end
  end
end

batch_sizeで一度に取得するレコード数を指定

4. 不適切なインデックス設計

問題点

適切なインデックスがないと、検索のパフォーマンスが低下し、データベースに負荷がかかります。

悪い例

class Order < ApplicationRecord
  belongs_to :user
end

class OrdersController < ApplicationController
  def index
    @orders = Order.where(user_id: params[:user_id])
                   .where('created_at >= ?', 30.days.ago)
                   .order(created_at: :desc)
  end
end

解決策

マイグレーションで以下の様に適切な複合インデックスを作成して同じ検索処理を実行

# migration
class AddIndexToOrders < ActiveRecord::Migration[6.1]
  def change
    add_index :orders, [:user_id, :created_at]
  end
end

# controller
class OrdersController < ApplicationController
  def index
    @orders = Order.where(user_id: params[:user_id])
                   .where('created_at >= ?', 30.days.ago)
                   .order(created_at: :desc)
  end
end

スキーマの違い👇

# アンチパターン
create_table "orders", force: :cascade do |t|
  t.bigint "user_id", null: false
  t.datetime "created_at", precision: 6, null: false
  t.datetime "updated_at", precision: 6, null: false
  t.foreign_key "users"
end

# 改善後
create_table "orders", force: :cascade do |t|
  t.bigint "user_id", null: false
  t.datetime "created_at", precision: 6, null: false
  t.datetime "updated_at", precision: 6, null: false
  t.foreign_key "users"
  t.index ["user_id", "created_at"] # ここに複合index付与
end

5. 過度の正規化

問題点

過度に正規化されたデータベース設計は、複雑なJOINクエリを必要とし、パフォーマンスの低下を招く可能性があります。

悪い例（過度に正規化）

スキーマ:

create_table "users", force: :cascade do |t|
  t.string "name"
  t.string "email"
  t.timestamps
end

create_table "addresses", force: :cascade do |t|
  t.bigint "user_id"
  t.string "country"
  t.string "city"
  t.string "street"
  t.timestamps
  t.index ["user_id"], name: "index_addresses_on_user_id"
end

モデルとコントローラ:

class Address < ApplicationRecord
  belongs_to :user
end

class User < ApplicationRecord
  has_one :address
end

class UsersController < ApplicationController
  def index
    @users = User.joins(:address).where(addresses: { country: 'Japan' })
  end
end

この設計では、ユーザーの国情報を取得するたびにJOINが必要になり、パフォーマンスが低下する可能性があります。

解決策

スキーマ:

create_table "users", force: :cascade do |t|
  t.string "name"
  t.string "email"
  t.string "country"
  t.timestamps
end

マイグレーションとモデル:

class AddCountryToUsers < ActiveRecord::Migration[6.1]
  def change
    add_column :users, :country, :string
  end
end

class User < ApplicationRecord
  # country カラムを直接使用
end

class UsersController < ApplicationRecord
  def index
    @users = User.where(country: 'Japan')
  end
end

この解決策では、頻繁に使用されるcountry情報をusersテーブルに直接保存することで、JOINを避け、クエリを単純化しています。

6. 単一テーブル継承（STI）の過剰使用

問題点

STIを過剰に使用するとテーブルが肥大化し、不要なカラムが増え、パフォーマンスが低下する可能性があります。

悪い例（STIの過剰使用）

スキーマ:

create_table "vehicles", force: :cascade do |t|
  t.string "type"
  t.string "manufacturer"
  t.integer "wheels"
  t.integer "propellers"
  t.integer "wings"
  t.timestamps
end

モデル:

class Vehicle < ApplicationRecord
end

class Car < Vehicle
end

class Boat < Vehicle
end

class Airplane < Vehicle
end

この設計では、vehiclesテーブルに全ての種類の乗り物の属性が含まれ、多くのNULL値が生じる可能性があります。

解決策（ポリモーフィック関連）

スキーマ: vehiclesテーブルには共通項目だけ持たせる

create_table "vehicles", force: :cascade do |t|
  t.string "manufacturer"
  t.string "details_type"
  t.bigint "details_id"
  t.timestamps
  t.index ["details_type", "details_id"], name: "index_vehicles_on_details"
end

create_table "car_details", force: :cascade do |t|
  t.integer "wheels"
  t.timestamps
end

create_table "boat_details", force: :cascade do |t|
  t.integer "propellers"
  t.timestamps
end

create_table "airplane_details", force: :cascade do |t|
  t.integer "wings"
  t.timestamps
end

モデル:

class Vehicle < ApplicationRecord
  belongs_to :details, polymorphic: true
end

class CarDetails < ApplicationRecord
  has_one :vehicle, as: :details
end

class BoatDetails < ApplicationRecord
  has_one :vehicle, as: :details
end

class AirplaneDetails < ApplicationRecord
  has_one :vehicle, as: :details
end

この解決策では、各種類の乗り物に特有の属性を別テーブルに分離し、ポリモーフィック関連を使用しています。これにより、テーブルの肥大化を防ぎ、より柔軟なデータモデルを実現しています。

【確認】 ポリモーフィック関連を定義する際の命名規則

  1. Railsの命名規則：
    Railsでは、ポリモーフィック関連を定義する際、関連名（この場合は details）に基づいて自動的にカラム名を生成します。

  2. カラム名の生成ルール：

    • 関連名 + "_type" → details_type
    • 関連名 + "_id" → details_id

  3. 自動マッピング：
    belongs_to :details, polymorphic: true と定義すると、Railsは自動的に：

    • details_type カラムを関連するモデル名の格納に使用
    • details_id カラムを関連するレコードのIDの格納に使用
      するように設定します。

つまり、belongs_to :details, polymorphic: true という記述と vehicles テーブルの details_type と details_id カラムの関係は、Railsの命名規則と自動マッピング機能によって結びつくことになります。これでポリモーフィック関連が成立します。

7. 貪欲なフェッチ

問題点

必要以上のデータを取得することで、メモリ使用量が増加し、アプリケーションのパフォーマンスが低下します。

悪い例（貪欲なフェッチ）

class ProductsController < ApplicationController
  def index
    @products = Product.all
    render json: @products
  end
end

この例では、データベース内の全ての製品を一度に取得しています。製品数が多い場合、メモリ使用量が急増し、レスポンス時間が長くなる可能性があります。

解決策（ページネーション）

class ProductsController < ApplicationController
  def index
    @products = Product.page(params[:page]).per(20)
    render json: @products
  end
end

この解決策では、kaminariなどのページネーションgemを使用して、一度に取得するデータ量を制限しています。これにより、メモリ使用量を抑え、レスポンス時間を短縮できます。

8. 不要なカラムの取得

問題点

必要のないカラムも含めてすべてのカラムを取得することで、データベースとアプリケーション間の不要なデータ転送が発生します。

悪い例（全カラムの取得）

スキーマ:

create_table "users", force: :cascade do |t|
  t.string "name"
  t.string "email"
  t.text "bio"
  t.date "birthday"
  t.string "password_digest"
  t.timestamps
end

コントローラ:

class UsersController < ApplicationController
  def index
    @users = User.all
    render json: @users
  end
end

この例では、パスワードダイジェストや更新日時など、不要な情報も含めて全てのカラムを取得しています。

解決策（必要なカラムのみ選択）

class UsersController < ApplicationController
  def index
    @users = User.select(:id, :name, :email)
    render json: @users
  end
end

この解決策では、必要なカラムのみを明示的に選択しています。これにより、データベースから転送されるデータ量を減らし、アプリケーションのパフォーマンスを向上させることができます。

終わりに

割と代表的なものだけだったかもしれませんが、キャッチアップなどされてる方の参考になると幸いです。

1
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
1
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?