現場でのクエリチューニングで学んだSQL効率化のコツ
こんにちは、皆さん。Webエンジニアになって6年目になります。最近、新人エンジニアにSQLの効率化について話をしていて、「どうやってクエリを速くするのかよくわからない」という声をよく聞きます。僕も最初は「SELECT * FROM hoge」で済ませていた時期がありました。そんな経験はありませんか？
この記事では、僕が現場で学んだSQLの効率化テクニックを3つ紹介します。具体的なコード例や失敗談を交えて、「自分にもできそう」と思えるように書いていきます。
✨✨✨✨✨✨✨✨✨✨✨✨✨✨✨✨✨✨✨✨✨✨✨✨
最新ツール・トレンド情報をずんだもん×めたんが解説するYouTubeチャンネルを運営しています！
いいね、チャンネル登録してもらえると嬉しいです🙇♂️
ハジメル.dev: https://hajimeru-dev.vercel.app/
「ひとりで続けるのは難しい」「何から学べばいいか分からない」という方向けに、
プログラミングのマンツーマンレッスンサービス「ハジメル.dev」も運営しています。
未経験OK・オンライン完結・月額制/違約金なしなので、気軽に無料相談してみてください🙇♂️
海外テックニュースを追いたいけど、英語や情報量の多さで大変…という方向けに、
Hacker News の話題を日本語でサクッと追える「HackerNews 日本語まとめ & AI要約」
を個人開発しました！
技術トレンド収集に使ってもらえると嬉しいです🔥🙇♂️
→ HackerNews 日本語まとめ & AI要約: https://hn-matome-2ht.pages.dev/
「ニャンパイアサバイバー」というヴァンパイアサバイバーリスペクトのゲームを作成しました！
もしよろしければ遊んで頂けると嬉しいです😭
習い事教室の先生向けに、SNS 投稿・生徒募集・保護者通知の文章を AI で生成する Web サービス「おしらせAI」を個人開発しました。Next.js + Supabase + LLM で構成しており、無料で月 10 回まで試用できます。よければ触ってみてください。
→ おしらせAI: https://oshirase-ai.vercel.app/
✨✨✨✨✨✨✨✨✨✨✨✨✨✨✨✨✨✨✨✨✨✨✨✨
1. 実行計画を理解してクエリのボトルネックを見つける
SQLの効率化の第一歩は、「なぜこのクエリが遅いのか」を理解することです。実行計画（EXPLAIN）を使うと、クエリがどのようにテーブルをスキャンしているかを確認できます。
例えば、以下のようなクエリがあるとします。
SELECT * FROM users WHERE email = 'example@example.com';
このクエリが遅い場合、まずEXPLAINを実行してみましょう。
EXPLAIN SELECT * FROM users WHERE email = 'example@example.com';
結果を見ると、typeがALLやindexになっている場合はフルテーブルスキャンが行われている可能性があります。これはテーブルの行数が多いほど、パフォーマンスが悪化します。
僕が初めてこの問題に遭遇したのは、あるプロジェクトでユーザー検索機能を実装した時です。テスト環境では問題なかったのに、本番環境で数千件のデータがあるテーブルを検索すると、レスポンスが5秒以上かかるようになりました。原因はインデックスが貼られていなかったことでした。
この経験から、常にインデックスの有無を確認する癖をつけました。特に、WHERE句で頻繁に使うカラムにはインデックスを設定するのが基本です。
2. インデックス設計でクエリ速度を劇的に改善する
インデックスは、クエリのパフォーマンスを向上させるための重要な手段です。ただし、インデックスを多く設定すれば良いというわけではありません。適切なカラムを選び、設計を見直すことが必要です。
例えば、以下のテーブルがあるとします。
CREATE TABLE orders (
id INT PRIMARY KEY,
user_id INT,
order_date DATE,
status VARCHAR(20)
);
ユーザーIDと注文日で検索を行うことが多い場合、複合インデックスを設定するのが良いでしょう。
CREATE INDEX idx_user_order ON orders (user_id, order_date);
僕が携わったあるECサイトのプロジェクトでは、注文履歴の検索クエリが遅くて困っていました。元のクエリは、user_idだけで検索しており、order_dateがインデックスに含まれていなかったため、フルテーブルスキャンが発生していました。
複合インデックスを追加した後、クエリの実行時間が100msから5msに改善されました。このように、インデックスの設計次第でパフォーマンスは大きく変わります。
ただし、インデックスは書き込み時のオーバーヘッドがあるため、頻繁に更新されるカラムには設定しないように注意しています。
3. クエリの書き方で無駄を省く
クエリの書き方を工夫することで、無駄な処理を省略できます。例えば、SELECT *の代わりに必要なカラムだけを指定する、サブクエリをJOINに置き換えるなどがあります。
サブクエリの代わりにJOINを使う
以下のサブクエリを例に取ります。
SELECT * FROM users WHERE id IN (
SELECT user_id FROM orders WHERE order_date > '2023-01-01'
);
これをJOINに書き換えると、パフォーマンスが向上します。
SELECT users.* FROM users
JOIN orders ON users.id = orders.user_id
WHERE orders.order_date > '2023-01-01';
僕が初めてこのテクニックを使ったのは、あるレポート生成の機能を開発した時です。サブクエリを使ったクエリが30秒以上かかっていたのが、JOINに変更した後は3秒に短縮されました。
不要なJOINを避ける
また、必要のないJOINを避けることも重要です。例えば、ユーザー情報だけが必要なのに、注文情報をJOINしてしまうと、無駄なデータが読み込まれます。
SELECT users.name FROM users
LEFT JOIN orders ON users.id = orders.user_id;
このクエリは、ordersテーブルの全ての行をスキャンするため、遅くなる可能性があります。必要なデータだけを取得するように修正しましょう。
4. EXPLAINとパフォーマンスモニタリングで定量的に改善する
EXPLAINを使った分析は、定量的にパフォーマンスを評価するのに役立ちます。また、アプリケーション側でクエリの実行時間をログに記録し、モニタリングすることで、問題のあるクエリを特定できます。
僕が携わったあるプロジェクトでは、アプリケーションログにクエリの実行時間を出力するように設定しました。すると、1000ms以上かかるクエリが特定でき、優先的に最適化を行うことができました。
例えば、以下のようにクエリの実行時間をログに出力します。
$start = microtime(true);
$result = $pdo->query($sql);
$end = microtime(true);
echo "Query took " . ($end - $start) . " seconds";
このようにして、定量的なデータを元に改善を進めるのが効果的です。
5. よくある失敗と回避策
SQLの効率化を行う上で、よくある失敗をいくつか紹介します。
N+1問題
ORMを使っていると、N+1問題に遭遇することがあります。例えば、ユーザー一覧を取得し、それぞれのユーザーの注文数をカウントする場合、以下のようなコードを書くかもしれません。
$users = User::all();
foreach ($users as $user) {
$count = Order::where('user_id', $user->id)->count();
}
これだと、ユーザー数分だけクエリが実行され、パフォーマンスが悪化します。代わりに、JOINやサブクエリを使って一括で取得するようにしましょう。
インデックスの過剰設定
インデックスを多く設定すると、書き込み時のパフォーマンスが低下します。特に、頻繁に更新されるカラムにはインデックスを設定しないように注意しています。
僕が携わったあるプロジェクトでは、インデックスを5つも設定していたテーブルがありました。結果、INSERTが異常に遅くなり、原因を調べていたらインデックスが多すぎることが判明しました。
まとめ
SQLの効率化は、実行計画の理解、適切なインデックス設計、クエリの書き方改善、定量的なモニタリング、よくある失敗の回避などがポイントです。僕も最初はこれらのテクニックを知らずに苦労しましたが、徐々に慣れてきました。
これからSQLの効率化に挑戦する皆さん、ぜひ試してみてください。まずはEXPLAINを使って、自分のクエリの問題を見つけることから始めてみるのも良いでしょう。