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

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

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

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

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

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

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

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

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

例えば、こんなSELECT文があったとします。
```sql
SELECT * FROM users WHERE name = 'テスト太郎';
```

これの実行計画を見たい場合は、先頭に`EXPLAIN`をつけるだけ。
```sql
EXPLAIN
SELECT * FROM users WHERE name = 'テスト太郎';
```

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

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

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

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

```mermaid
erDiagram
    users {
        int id PK
        varchar(255) name
        int department_id
        int status
        datetime created_at
        index idx_department "(department_id, status)"
    }
    scores {
        int id PK
        int user_id FK
        varchar(50) category
        int score
        datetime created_at
        index idx_user "(user_id)"
    }
    users ||--o{ scores : has
```

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

```sql
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.実行計画の確認

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

```sql
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.インデックスの確認

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

```sql
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句で絞り込む

```sql
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：部門データの階層検索

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

```mermaid
erDiagram
    departments {
        int id PK
        varchar(255) name
        int parent_id FK
        int level
        int status
        datetime created_at
        index idx_parent "(parent_id)"
        index idx_status "(status)"
    }
    department_members {
        int id PK
        int department_id FK
        int member_count
        datetime created_at
        datetime deleted_at
        index idx_department "(department_id)"
    }
    departments ||--o{ departments : "has parent"
    departments ||--o{ department_members : has
```

改善前のクエリ

```sql
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.実行計画の確認

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

```sql
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.インデックスの確認

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

```sql
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句で絞り込む


```sql
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;
```

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

```sql
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でも簡単に実行計画を確認できます。

```ruby
User.where(status: 1).explain
```

## まとめ

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

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

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