Zenn
🙆

SQLのチューニング(処理速度を向上、N+1問題解決など)　テーブルの見直し　リリース速度を向上させる工夫　

2024/09/19に公開
Laravel
Rails
SQL
パフォーマンスチューニング
チューニング
tech

SQLのチューニングのメリット

企業の課題で処理速度の問題はよく抱えています。処理速度はほとんどの会社で課題になっています。
簡単にチューニングのやり方をこの記事でやっていきます。

DBが遅くなる原因

  • クエリの数が遅い SELECTとINSERTを大量発生させるケース

  • 1本のクエリが遅い

今回はSELECT関連のN＋1問題を扱っていきます。

Railsでデータベースのチューニングの検証

でもで簡単にチューニングしてみました。

新規作成

rails new db_tuning_demo
cd db_tuning_demo

これで新しく新規プロジェクトを発足していきます。

モデル作成

rails generate model Post title:string content:text
rails generate model Comment post:references content:text
rails db:migrate

seederでサンプルデータの作成

ここでわざとN + 1問題を発生させます。

100.times do |i|
  post = Post.create!(title: "Post #{i + 1}", content: "This is the content of post #{i + 1}")
  5.times do |j|
    Comment.create!(post: post, content: "This is comment #{j + 1} for post #{i + 1}")
  end
end

このように繰り返し処理を重ねていくとSELECT文が大量発生し、N＋1問題が発生します。

rails db:seed

でサンプルデータ投入です。

N+1問題の再現する

コントローラーとviewを作って再現していきます。
Postとその関連するCommentを一覧表示するためのコントローラとビューを作成します。

rails g controller Posts index

PostsControllerを次のように編集します。

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

<h1>Posts</h1>

<% @posts.each do |post| %>
  <h2><%= post.title %></h2>
  <p><%= post.content %></p>

  <h3>Comments:</h3>
  <ul>
    <% post.comments.each do |comment| %>
      <li><%= comment.content %></li>
    <% end %>
  </ul>
<% end %>

Rails.application.routes.draw do
  root 'posts#index'

  resources :posts, only: [:index]

サーバーを起動するとこの時点でN+1問題を発生します。

<img width="912" alt="スクリーンショット 2024-09-03 2 41 13" src="https://github.com/user-attachments/assets/d2d3c6f1-5202-4f3d-b04a-d69713fe1f01">

Eager Loadを使う

Eager LoadでN +1問題対策をしていきます。他にもJoinというやり方がありますが、
これは参考動画をご覧ください。

class PostsController < ApplicationController
  def index
    @posts = Post.includes(:comments).all #Eager Loadを使用
    #@posts = Post.preload(:comments).all Preloadを使用可
  end
end

Postと関連するCommentを一度に取得するためのクエリが実行され、N+1問題が解決されます。最初よりかは改善された？

<img width="873" alt="スクリーンショット 2024-09-03 3 38 54" src="https://github.com/user-attachments/assets/0e7983b8-0eb0-4144-8feb-71e0aa06c633">

クエリの遅延問題の検出と解決

インデックスを活用することでクエリのパフォーマンスを向上させることができるみたいです。

rails g migration add_index_to_posts_title

class AddIndexToPostsTitle < ActiveRecord::Migration[7.0]
  def change
    add_index :posts, :title
  end
end

そしてmigration実行です。

パフォーマンスの測定

Railsではパフォーマンスの測定があるみたいです。

gem 'bullet'

その後bundle installします。

# bulletによるパフォーマンス測定
  config.after_initialize do
    Bullet.enable = true
    Bullet.alert = true
    Bullet.bullet_logger = true
    Bullet.console = true
  end

N+1問題や不要なクエリが発生した場合にブラウザにアラートが表示されます。

N + 1問題その他対策

  • BULK INSERT
    大量のINSERTをする場合。

  • バッチ処理

  • データ移行

など 複数のINSERTを１つにまとめ、DBに対するクエリ数を減らし、パフォーマンスを向上させます。

このあたりも参考動画を調べてください。

Laravelでやる場合

上でやったことをLaravelバージョンでやればいいです。

その他DBのパフォーマンス低下原因(調査中)

1. 大量データの読み込み

Post.allを使って数千件のレコードを一度に読み込むと、システムに大きな負荷がかかります。

  • 解決策
    ①Pagination（ページネーション)によって大量のレコードをページごとに分割して読み込む。
@posts = Post.page(params[:page]).per(10)

②Batch Processing(大量のデータを一度に読み込まず、バッチで処理)

Post.find_each(batch_size: 100) do |post|
end

2.不適切なインデックス

テーブルに対してインデックスが適切に設定されていない場合、データ検索が遅くなる。
where句で頻繁に使われるカラムにインデックスがないと、クエリのパフォーマンスが著しく低下します。

  • 解決策
    インデックスの追加: 頻繁に検索に使うカラムにインデックスを追加
rails g migration add_index_to_posts_content

マイグレーションファイル。

add_index :posts, :content

複数のカラムを組み合わせて検索する場合、複合インデックス

add_index :posts, [:title, :created_at]

3.複雑なクエリとジョイン

複数のテーブルに対する複雑なクエリやJOINが多用されると、クエリの実行が遅くなり、特に、非効率なJOINやサブクエリがパフォーマンスに悪影響です。

@posts = Post.joins(:comments).select('posts.id, posts.title, comments.content')

データ正規化と非正規化のバランス: 正規化しすぎるとクエリが複雑化することがあります。必要に応じてデータの非正規化を検討していく必要あると思います。

4.テーブルの肥大化

テーブルが大きくなると、全体のパフォーマンスが低下します。(データが長期間蓄積されたログやイベントテーブルなど)

  • アーカイブ
    古いデータを別のテーブルやストレージにアーカイブし、主要なテーブルから削除

  • パーティショニング
    大規模なテーブルをパーティションに分割することで、データのアクセス速度を向上

・自分が書いたパーティションに関する記事
https://qiita.com/Hashimoto-Noriaki/items/6a4dd9c5f0e1d2cf5203

5.クエリキャッシュの未使用

同じクエリを何度も実行すると、パフォーマンスが低下します。Railsにはクエリキャッシュ機能があり、同じクエリが複数回実行される場合に結果をキャッシュします。
クエリキャッシュの活用: Railsのクエリキャッシュを活用する

# キャッシュを手動で有効化する例
Rails.cache.fetch("post/#{post.id}") do
  post.comments.to_a
end

6.データベースのロック

複数のクライアントが同時にデータベースの同じレコードに対して書き込み操作を行う場合、データベースがロックされ、他のクエリのパフォーマンスが低下します。

  • 解決策
    トランザクションの適切な使用: トランザクションを適切に使用し、必要以上にロックを保持しないようにする。
Post.transaction do
  post.lock!
  # 更新操作
end
  • デッドロックの回避
    複数のテーブルを更新する際の順序を決め、デッドロックを避ける。

参考動画

https://www.youtube.com/watch?v=UWSWKUMd71Q

Discussion