Railsアプリケーションのデータベースチューニングについて

レスポンスが遅くなりやすい原因

・フロントエンド: スクリプト / 画像データの読み込み
・インフラ: サーバのメモリやCPUなどのリソースが不足
・バックエンド: データベースへの問い合わせ時間

今回は、バックエンド: データベースへの問い合わせ時間についてそのボトルネックの見つけ方やその解消のTipsを紹介します。

###そもそもデータベースへの問い合わせ時間とは何を指しているか?

データベースへの問い合わせ時間 = 「1つあたりのSQLあたりの平均時間 x 実行したSQLの数」

ざっくりとした原因

1. 実行に時間のかかるSQLを実行している
インデックスがないSQLを実行していたり、余計なデータまで取得してしまうSQLを実行しているなど

2. 不必要なSQLを実行している
同じSQLを複数回実行していたり、1 回で取得できるデータを複数SQLで取得しているなど

実行に時間のかかるSQL: EXPLAIN

EXPLAINの見方

mysql> EXPLAIN SELECT * FROM Country,City WHERE Country.Code=City.CountryCode AND Country.Name LIKE 'A%';
+----+-------------+---------+--------+---------------+---------+---------+------------------------+------+-------------+
| id | select_type | table   | type   | possible_keys | key     | key_len | ref                    | rows | Extra       |
+----+-------------+---------+--------+---------------+---------+---------+------------------------+------+-------------+
|  1 | SIMPLE      | City    | ALL    | NULL          | NULL    | NULL    | NULL                   | 4079 |             |
|  1 | SIMPLE      | Country | eq_ref | PRIMARY       | PRIMARY | 3       | world.City.CountryCode |    1 | Using where |
+----+-------------+---------+--------+---------------+---------+---------+------------------------+------+-------------+
2 rows in set (0.00 sec)

id: SQL内で出されるSELECT文1つ1つに割り当てられる番号。
select_type: クエリ内で実行されたSQLの種類。
table: 参照しているテーブル名。
type: 対象のテーブルに対して索引を使っているか否か、また使っているならばどう使っているか。
possible_keys: オプティマイザが使用するインデックスの候補として挙げたキーの一覧。
key: possible_keysの中から実際にオプティマイザが選択した物。
key_len: keyの長さ。
ref: 検索時にkeyの比較対象となっているカラム。定数の場合はconstと表示される。
rows: クエリによって取得が想定される行数。
Extra: クエリの追加情報。

不必要なSQL: SQLの発行数を減らす ~Eager Loading~

N+1問題
SQLクエリが 「取得するデータ量(N) + 1回 」走ってしまい、取得するデータが多くなるにつれパフォーマンスを低下させてしまう問題

①

players = Player.limit(5)
players.each do |player|
  puts player.location.name
end

1. Player Load (107.1ms)  SELECT `players`.* FROM `players` WHERE (`players`.`deleted_at` IS NULL) LIMIT 5

2. Location Load (48.5ms)  SELECT `locations`.* FROM `locations` WHERE `locations`.`id` = 1 AND (`locations`.`deleted_at` IS NULL) LIMIT 1
東京都
3. Location Load (8.1ms)  SELECT `locations`.* FROM `locations` WHERE `locations`.`id` = 1 AND (`locations`.`deleted_at` IS NULL) LIMIT 1
東京都
4. Location Load (5.4ms)  SELECT `locations`.* FROM `locations` WHERE `locations`.`id` = 1 AND (`locations`.`deleted_at` IS NULL) LIMIT 1
東京都
5. Location Load (4.9ms)  SELECT `locations`.* FROM `locations` WHERE `locations`.`id` = 10 AND (`locations`.`deleted_at` IS NULL) LIMIT 1
全国
6. Location Load (6.1ms)  SELECT `locations`.* FROM `locations` WHERE `locations`.`id` = 10 AND (`locations`.`deleted_at` IS NULL) LIMIT 1
全国

#取得したデータ数5つに対してSQLが6回実行されている。

対策
事前に関連テーブルのデータを読み込む = Eager Loading

①のコードをincludesを使って改善

Player.includes(:locations).limit(5).each do |player|
  puts player.location.name
end 

1. Player Load (211.7ms)  SELECT `players`.* FROM `players` WHERE (`players`.`deleted_at` IS NULL) LIMIT 5
2. Location Load (27.1ms)  SELECT `locations`.* FROM `locations` WHERE `locations`.`id` IN (1, 10) AND (`locations`.`deleted_at` IS NULL)
  
東京都
東京都
東京都
全国
全国

Eager Loadingに関連した3つのメソッド
■preload
preloadメソッドはアソシエーションのデータをJOINせずに、複数のクエリ発行によって取得。アソシエーション先のテーブルのカラムを検索条件に指定することはできない。

Player.preload(:locations).limit(5).each do |player|
  puts player.location.name
end

1. Player Load (1460.1ms)  SELECT `players`.* FROM `players` WHERE (`players`.`deleted_at` IS NULL) LIMIT 5
2. Location Load (44.8ms)  SELECT `locations`.* FROM `locations` WHERE `locations`.`id` IN (1,10) AND (`locations`.`deleted_at` IS NULL)

東京都
東京都
東京都
全国
全国

■eager_load
アソシエーションのデータを対象テーブルとLEFT OUTER JOINすることでまとめて取得。
JOINにより1つのSQLクエリで取得するため、preloadとは違ってアソシエーション先のテーブル(JOINしたテーブル)のカラムを使って絞り込める。
※JOINはレコード数の多いテーブルでは時間がかかることもあるため、条件を指定する必要のない場合はpreloadのほうが適切

Player.eager_load(:location).where(locations: {id: 1}).limit(5).each do |player|
  puts player.lraocation.name
end

SQL (1777.9ms)
SELECT `players`.`id` AS t0_r0, `players`.`cd` AS t0_r1, `players`.`mail` AS 
・
・
・
FROM `players` 
LEFT OUTER JOIN `locations` ON `locations`.`id` = `players`.`Location_id`
WHERE `locations`.`id` = 1 AND (`players`.`deleted_at` IS NULL) LIMIT 5

東京都
東京都
東京都
東京都
東京都  

■includes
preloadとeager_joinを状況によって使い分けてくれる。
・基本的にはpreload方式
・アソシエーション先のテーブルのカラムに対し、検索条件をしている時は、eager_load方式

遅いSQL/不要なSQLを検知するツール

rack-mini-profiler: 実行に時間のかかるSQLを特定
Bullet: N+1問題を検出
※どちらもgemで提供されています。
気になる方は、調べてみてください。