大量データ環境でSQLのパフォーマンスを改善する方法
目次
はじめに
SQLを書いて、欲しいデータが取得できた。
画面にも正しく表示された。
エラーも出ていない。
それだけを見ると、
「ちゃんと動いているから、この実装で問題ない」
と思ってしまうかもしれません。
自分も以前は、まず「正しく動くこと」を中心に考えていました。
ただ、実際の開発で1億件近いレコードを持つテーブルを扱ったことで、考え方が変わりました。
大量のデータを扱う場合、
正しい結果が返ってくることと、適切な方法で結果を取得できていることは別問題です。
例えば、同じ1件のデータを取得するSQLでも、
SQL A 100件程度を確認 ↓ 目的の1件を取得
する場合と、
SQL B 数千万件を確認 ↓ 目的の1件を取得
する場合があります。
画面に表示される結果は同じです。
しかし、データ量が増えるほど、この差がパフォーマンスに大きく影響します。
この記事では、1億件近いテーブルを扱った経験から、SQLのパフォーマンスを確認するときに意識するようになったポイントを紹介します。
まず「実際に発行されたSQL」を見る
RailsではActive Recordを使うことで、SQLを直接書かなくてもデータを取得できます。
例えば、
User.find_by(email: "test@example.com")
と書けば、ユーザーを取得できます。
非常に便利ですが、実際にデータベースで処理されているのはSQLです。
例えば、Railsのログを見ると次のようなSQLを確認できます。
SELECT `users`.* FROM `users` WHERE `users`.`email` = 'test@example.com' LIMIT 1;
ここで確認したいのは、
- WHERE条件は想定通りか
- LIMITは付いているか
- 不要なJOINやORDER BYがないか
- 同じSQLが何度も発行されていないか
といった点です。
Railsでは to_sql を使って、Active Recordから生成されるSQLを確認することもできます。
Post.where(user_id: 1)
.order(created_at: :desc)
.limit(20)
.to_sql
大量データを扱うようになってから、自分はRailsのコードだけで判断せず、最終的にどのようなSQLが発行されるのかを見るようになりました。
EXPLAINでSQLの実行計画を見る
実際のSQLを確認したら、次に使いたいのが EXPLAIN です。
EXPLAIN SELECT * FROM users WHERE email = 'test@example.com';
EXPLAIN を使うと、MySQLがそのSQLをどのように実行しようとしているのか確認できます。
最初からすべての項目を理解する必要はありません。
自分はまず、次の項目を確認します。
| 項目 | 確認すること |
|---|---|
type | テーブルへのアクセス方法 |
possible_keys | 使用候補となるインデックス |
key | 実際に選ばれたインデックス |
rows | 読み取ると見積もられた行数 |
Extra | 追加の実行情報 |
特に大量データでは、key と rows を意識します。
例えば、
rows: 20
rows: 50,000,000上記２つでは、最終的に取得する件数が同じでも意味が大きく違います。
最終的に1件しか取得しないSQLでも、その1件を探すために大量の行を確認すると見積もられているのであれば、
もっと効率よく絞り込めないか？
と考えるきっかけになります。
また、type が ALL の場合はフルテーブルスキャンです。
ただし、
ALLだから絶対にダメというわけではありません。
小さいテーブルや、テーブル内の多くのデータを取得するケースなどでは、フルテーブルスキャンが必ずしも問題になるとは限りません。
そのため、テーブルサイズや検索条件などと合わせて判断します。
インデックスは「あるか」ではなく「使われているか」
例えば、次のSQLがあったとします。
SELECT * FROM posts WHERE user_id = 100 ORDER BY created_at DESC LIMIT 20;
大量のデータがある場合、
user_idで検索 ↓ created_at順に並べる ↓ 20件取得
という処理を、できるだけ効率よく行いたいところです。
検索パターンによっては、
CREATE INDEX index_posts_on_user_id_and_created_at ON posts (user_id, created_at);
のような複合インデックスを検討することもあります。
ただし、
インデックスを追加すれば速くなる
と単純に考えるのも危険です。
インデックスはINSERT・UPDATE・DELETE時にも更新されるため、増やせばその分だけ書き込み時のコストも発生します。
そのため、自分は、
実際のSQLを見る ↓ EXPLAINを見る ↓ 既存インデックスを見る ↓ 検索条件を確認する ↓ 必要なら改善する ↓ もう一度EXPLAINを見る
という順番で確認します。
インデックスが存在することではなく、実際に適切なインデックスが使われているかを見ることが大切です。
SQLの「発行回数」にも注意する
SQL単体の内容だけではなく、何回発行されているかも確認します。
RailsでよくあるのがN+1問題です。
例えば、
posts = Post.limit(100) posts.each do |post| puts post.user.name end
のような処理では、関連の読み込み方によっては、投稿ごとにユーザーを取得するSQLが発行される可能性があります。
画面では正常に表示されるので、
動いているから問題ない
ように見えます。
しかし、ログを見ると同じようなSQLが大量に発行されていることがあります。
状況に応じて、
Post.includes(:user)
などを利用し、関連データを効率よく取得できないか検討します。
ここでも、
「結果が正しいか」だけではなく、「その結果を出すためにSQLを何回発行したか」
を見ることが重要です。
必要以上のデータを取得していないか
大量データを扱う場合は、取得するデータ量も意識します。
例えば、画面で必要なのが id と name だけなのに、
SELECT * FROM users;
としているのであれば、本当にすべてのカラムが必要なのか確認します。
必要なカラムだけでよければ、
SELECT id, name FROM users;
のように取得対象を絞ることもできます。
また、一覧画面で20件しか表示しないのであれば、
LIMIT 20
を使って、取得件数を限定できるか考えます。
つまり、
どう検索するか
だけではなく、
どこまで取得するか
も重要です。
自分がSQLを見るときの流れ
大量データを扱う処理では、自分は次のような順番で確認します。
1. Railsのログを見る
↓
2. 実際に発行されたSQLを見る
↓
3. SQLの発行回数を見る
↓
4. EXPLAINを実行する
↓
5. type / key / rows / Extraを見る
↓
6. 既存のインデックスを確認する
↓
7. SQLやインデックスを改善する
↓
8. もう一度EXPLAINで確認する
特に大事だと思っているのが、最後の確認です。
SQLやインデックスを変更して、
たぶん速くなった
で終わらせるのではなく、もう一度実行計画を確認します。
改善したつもりで終わらせず、実行計画が実際に改善されていることを確認するところまでがセットです。
1億件近いテーブルを扱って変わったこと
データが少ない環境では、多少効率の悪いSQLでも普通に動いてしまいます。
例えば、
開発環境 100件 ↓ SQL実行 ↓ すぐ表示
だったとしても、本番環境では、
数千万〜1億件近いデータ ↓ 同じようなSQL ↓ 処理コストが大きくなる
ということがあります。
そのため、
開発環境で速かったから問題ないとは限りません。
自分が1億件近いテーブルを扱ったことで一番変わったのは、
今、動いているかだけではなく、
データが増えても問題なく動けるかを考えるようになったことです。
まとめ
SQLは、欲しいデータが取得できれば完成ではありません。
特に大量データを扱う場合は、
「正しく動くこと」と「適切に動くこと」の両方を見る必要があります。
自分は、
実際のSQLを見る ↓ 発行回数を見る ↓ EXPLAINを見る ↓ インデックスを見る ↓ 改善する ↓ もう一度確認する
という流れを意識するようになりました。
Railsのようなフレームワークを使っていると、SQLを直接書かなくても機能を実装できます。
だからこそ、
このコードの裏側では、どんなSQLが発行されているんだろう？
と、一度確認してみることが大切です。
「動いたからOK」ではなく、「どう動いたか」まで見る。
1億件近いデータを扱った経験から、自分が特に重要だと感じたポイントです。
