こんな経験はないですか？

SQLを実行したら、いつまでたっても終わらない...
タイムアウトが発生してクエリが失敗する...

開発やデータ分析をしていると、よく遭遇するこんな「重いクエリ」問題。
今回は、実際に遭遇した重いクエリを、
EXPLAINとINDEXを使って改善した事例を紹介します。

重いクエリを生み出してしまったら、
以下の3つのステップでクエリの改善を検討すると良さそうです。

クエリの前にEXPLAINをつけて実行計画を確認する
使えそうなインデックスを探す
WHERE句で絞り込める条件を増やしてみる

実行計画（EXPLAIN）を簡単にチェックする方法

最初にEXPLAINについて少し紹介します。

EXPLAINは、MySQLでクエリの実行計画を確認するためのコマンドです。
クエリの先頭にEXPLAINをつけるだけで使えます。

例えば、こんなSELECT文があったとします。

SELECT * FROM users WHERE name = 'テスト太郎';

これの実行計画を見たい場合は、先頭にEXPLAINをつけるだけ。

EXPLAIN
SELECT * FROM users WHERE name = 'テスト太郎';

実行計画で特に注目したいのは、type列とrows列です。
typeが"ALL"の場合はテーブルをフルスキャンしているので要注意です。

rowsは処理する行数で、いわば「移動する歩数」です。
少ないほど早く実行できます。

クエリ改善例1：特定ユーザーのスコアデータ取得

あるシステムで、特定ユーザーのスコアデータを全て取得する必要がありました。

改善前のクエリはこちらです。

SELECT s.score
FROM scores s
JOIN users u ON u.id = s.user_id
WHERE u.name = 'テスト太郎'
AND s.category IN ('Math', 'English', 'Science')
ORDER BY 
    CASE s.category
        WHEN 'Math' THEN 1
        WHEN 'English' THEN 2
        WHEN 'Science' THEN 3
    END,
    s.id;

このクエリは5分経っても終わりませんでした。

1.実行計画の確認

まずは実行計画を確認してみます

EXPLAIN
SELECT s.score
FROM scores s
JOIN users u ON u.id = s.user_id
WHERE u.name = 'テスト太郎'
AND s.category IN ('Math', 'English', 'Science')
...

+----+-------------+-------+------------+-------+---------------+------+---------+------+---------+----------+------------------------------------------+
| id | select_type | table | partitions | type  | possible_keys | key  | key_len | ref  | rows    | filtered | Extra                                    |
+----+-------------+-------+------------+-------+---------------+------+---------+------+---------+----------+------------------------------------------+
|  1 | SIMPLE      | users | NULL       | ALL   | PRIMARY       | NULL | NULL    | NULL | 1000000 | 10.00    | Using where; Using temporary; Using filesort |
+----+-------------+-------+------------+-------+---------------+------+---------+------+---------+----------+------------------------------------------+

typeがALLなので、100万行以上のデータをフルスキャンしていることがわかりました。

2.インデックスの確認

次に利用可能なインデックスを確認します。

SHOW INDEX FROM users;

+-------+------------+----------------+--------------+-------------+-----------+-------------+
| Table | Non_unique | Key_name       | Seq_in_index | Column_name | Collation | Cardinality |
+-------+------------+----------------+--------------+-------------+-----------+-------------+
| users |          0 | PRIMARY        |            1 | id          | A         |     1000000 |
| users |          1 | idx_department |            1 | department_id| A        |        1000 |
| users |          1 | idx_department |            2 | status      | A        |        2000 |
+-------+------------+----------------+--------------+-------------+-----------+-------------+

既存のインデックスを見ると、department_idとstatusの複合インデックスがあることがわかりました。
これを使って、以下の点で絞り込みができそうです。

① ユーザーの所属部署（department_id）で絞り込み
② アクティブなユーザー（status）で絞り込み

3.where句で絞り込む

SELECT s.score
FROM scores s
JOIN users u ON u.id = s.user_id
WHERE u.department_id = 1  -- ①部署で絞り込み
AND u.status = 1  -- ②アクティブユーザーのみ
AND u.name = 'テスト太郎'
AND s.category IN ('Math', 'English', 'Science')
ORDER BY 
    CASE s.category
        WHEN 'Math' THEN 1
        WHEN 'English' THEN 2
        WHEN 'Science' THEN 3
    END,
    s.id;

このクエリは0.3秒で実行できました。

改善後の実行計画は、

+----+-------------+-------+------------+------+--------------------+---------------+---------+-------------+-------+----------+------------------------------------------+
| id | select_type | table | partitions | type | possible_keys      | key           | key_len | ref         | rows  | filtered | Extra                                    |
+----+-------------+-------+------------+------+--------------------+---------------+---------+-------------+-------+----------+------------------------------------------+
|  1 | SIMPLE      | users | NULL       | ref  | PRIMARY,idx_department | idx_department | 8       | const,const | 1000   | 10.00    | Using index condition; Using where         |
+----+-------------+-------+------------+------+--------------------+---------------+---------+-------------+-------+----------+------------------------------------------+

typeがrefになり、処理する行数も約1000行まで減っています。

ポイントは、出したい出力結果だけを考えるのではなく、DBが効率的に検索できるよう、インデックスを活用した絞り込み条件を提供することです。

クエリ改善例2：部門データの階層検索

次は、部門の階層構造を含むデータを検索する例です。

改善前のクエリ

SELECT d.id, d.name
FROM departments d
JOIN department_members dm ON dm.department_id = d.id
WHERE d.level > 1 
AND dm.deleted_at IS NULL
GROUP BY d.id;

このクエリも実行が終わりませんでした。

1.実行計画の確認

まずは実行計画を確認してみます。

EXPLAIN
SELECT d.id, d.name
FROM departments d
JOIN department_members dm ON dm.department_id = d.id
WHERE d.level > 1 
AND dm.deleted_at IS NULL
GROUP BY d.id;

出力結果

+----+-------------+-------+------------+-------+---------------+------+---------+------+--------+----------+------------------------------------------+
| id | select_type | table | partitions | type  | possible_keys | key  | key_len | ref  | rows   | filtered | Extra                                    |
+----+-------------+-------+------------+-------+---------------+------+---------+------+--------+----------+------------------------------------------+
|  1 | SIMPLE      | d     | NULL       | ALL   | PRIMARY       | NULL | NULL    | NULL | 100000 | 33.33    | Using where; Using temporary; Using filesort |
|  1 | SIMPLE      | dm    | NULL       | ALL   | NULL          | NULL | NULL    | NULL |  50000 | 50.00    | Using where; Using join buffer              |
+----+-------------+-------+------------+-------+---------------+------+---------+------+--------+----------+------------------------------------------+

実行計画を見ると、

typeが両方ともALLで、フルスキャンが発生しています
departmentsテーブルで10万行、department_membersテーブルで5万行を走査しています
インデックスが全く使われていません（keyがNULL）

2.インデックスの確認

利用可能なインデックスを確認してみましょう。

SHOW INDEX FROM departments;
SHOW INDEX FROM department_members;

+-------------+------------+------------+--------------+-------------+-----------+-------------+
| Table       | Non_unique | Key_name   | Seq_in_index | Column_name | Collation | Cardinality |
+-------------+------------+------------+--------------+-------------+-----------+-------------+
| departments |          0 | PRIMARY    |            1 | id          | A         |      100000 |
| departments |          1 | idx_parent |            1 | parent_id   | A         |       10000 |
| departments |          1 | idx_status |            1 | status      | A         |           2 |
+-------------+------------+------------+--------------+-------------+-----------+-------------+

+-------------------+------------+----------------+--------------+---------------+-----------+-------------+
| Table             | Non_unique | Key_name       | Seq_in_index | Column_name   | Collation | Cardinality |
+-------------------+------------+----------------+--------------+---------------+-----------+-------------+
| department_members|          0 | PRIMARY        |            1 | id            | A         |       50000 |
| department_members|          1 | idx_department |            1 | department_id | A         |        1000 |
+-------------------+------------+----------------+--------------+---------------+-----------+-------------+

statusのインデックスを使って、アクティブな部門のみに絞り込むように改善します。

3.where句で絞り込む

WITH active_departments AS (
    -- アクティブな部門のみに絞り込み
    SELECT id 
    FROM departments
    WHERE status = 1  -- アクティブな部門のみ
)
SELECT d.id, d.name
FROM departments d
JOIN department_members dm ON dm.department_id = d.id
WHERE d.level > 1 
  AND d.id IN (SELECT id FROM active_departments)
  AND dm.deleted_at IS NULL
GROUP BY d.id;

改善後の実行計画を確認してみましょう。

EXPLAIN
WITH active_departments AS (
    -- アクティブな部門のみに絞り込み
    SELECT id 
    FROM departments
    WHERE status = 1  -- アクティブな部門のみ
)
SELECT d.id, d.name
FROM departments d
JOIN department_members dm ON dm.department_id = d.id
WHERE d.level > 1 
  AND d.id IN (SELECT id FROM active_departments)
  AND dm.deleted_at IS NULL
GROUP BY d.id;

出力結果

+----+-------------+-------+------------+------+-------------------------+------------+---------+-------+-------+----------+------------------------------------------+
| id | select_type | table | partitions | type | possible_keys          | key        | key_len | ref   | rows  | filtered | Extra                                    |
+----+-------------+-------+------------+------+-------------------------+------------+---------+-------+-------+----------+------------------------------------------+
|  1 | SIMPLE      | d     | NULL       | ref  | PRIMARY,idx_status     | idx_status | 4       | const | 1000  | 33.33    | Using where; Using temporary             |
|  1 | SIMPLE      | dm    | NULL       | ref  | idx_department         | idx_department | 4    | d.id  |   50  | 50.00    | Using where                              |
+----+-------------+-------+------------+------+-------------------------+------------+---------+-------+-------+----------+------------------------------------------+

改善後の実行計画を見ると、

typeが両方ともrefになり、インデックスを使用した検索に変わりました
処理行数が大幅に減少（departments: 10万→1000行、department_members: 5万→50行）
両方のテーブルでインデックスが使われるようになりました

おまけ：Railsでの実行計画確認

Railsでも簡単に実行計画を確認できます。

User.where(status: 1).explain

まとめ

クエリを効率的に実行するためには、DBに「効率的な検索方法」を教えてあげることが重要です。

重いクエリに遭遇したら、

まずはEXPLAINで実行計画を確認
インデックスが使えないか探してみる
WHERE句での絞り込みを検討

という手順でクエリを改善していくと良いでしょう。