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回 」走ってしまい、取得するデータが多くなるにつれパフォーマンスを低下させてしまう問題


```ruby:①
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で提供されています。
気になる方は、調べてみてください。