Go to Qiita Advent Calendar Top
LoginSignup
3
1

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?

@tomohiko9090(kuzuba tomohiko)

EXPLAINとINDEXを使って、重くて実行できないクエリを改善する

Last updated at Posted at 2025-02-22

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

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

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

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

  1. クエリの前にEXPLAINをつけて実行計画を確認する
  2. 使えそうなインデックスを探す
  3. 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              |
+----+-------------+-------+------------+-------+---------------+------+---------+------+--------+----------+------------------------------------------+

実行計画を見ると、

  1. typeが両方ともALLで、フルスキャンが発生しています
  2. departmentsテーブルで10万行、department_membersテーブルで5万行を走査しています
  3. インデックスが全く使われていません（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                              |
+----+-------------+-------+------------+------+-------------------------+------------+---------+-------+-------+----------+------------------------------------------+

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

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

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

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

User.where(status: 1).explain

まとめ

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

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

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

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

3
1
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
Sign upLogin
3
1

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?