MySQLで同じ実行計画でも実行時間に差がある時のパフォーマンスチューニング

MySQLで同じ実行計画でも実行時間に差がある時のパフォーマンスチューニングする際に手間取ったのに備忘録として残します。
実行計画上では、インデックスが使われているものの、速度改善の余地がある事例になります。

## 発生していた状況

特定の設定プロジェクトの中から、`未対応`なタスクで、最も作成した時間が古いものを1件抽出する際に、`未対応`なタスクが多くなった時のみパフォーマンス劣化が発生する、というものでした。

以下のようなテーブルを想定してみましょう。
設定プロジェクトと親タスクと、小タスクのテーブルがそれぞれ分かれており、`設定プロジェクトと親タスク`で外部制約キーが貼られており、`親タスクと子タスク`で外部制約キーが貼られています。

```sql
CREATE TABLE `projects` (
  `id` varchar(255) COLLATE utf8mb4_bin NOT NULL,
  `name` varchar(255) COLLATE utf8mb4_bin NOT NULL,
  PRIMARY KEY (`id`)
);

CREATE TABLE `parent_tasks` (
  `id` int auto_increment,
  `name` varchar(255) COLLATE utf8mb4_bin NOT NULL,
  `project_id` varchar(255) COLLATE utf8mb4_bin NOT NULL,
  PRIMARY KEY (`id`),
  CONSTRAINT `fk_parent_task_project_id` FOREIGN KEY (`project_id`) REFERENCES `projects` (`id`)
);

CREATE TABLE `child_tasks` (
  `id` int auto_increment,
  `name` varchar(255) COLLATE utf8mb4_bin NOT NULL,
  `parent_task_id` int NOT NULL,
  `state` varchar(255) COLLATE utf8mb4_bin NOT NULL,
  `created_at` datetime NOT NULL,
  PRIMARY KEY (`id`),
  KEY `index_child_tasks_on_state_created_at` (`state`,`created_at`),
  CONSTRAINT `fk_child_task_parent_id` FOREIGN KEY (`parent_task_id`) REFERENCES `parent_tasks` (`id`)
);
```


特定の設定プロジェクトの中から、`未対応`なタスクで、最も作成した時間が古いものを1件抽出するSQLは以下のようになります。

```SQL
SELECT
  ct.id
FROM
  child_tasks ct
JOIN parent_tasks pt ON ct.parent_task_id = pt.id
WHERE
  ct.state = 'initialize'
  AND pt.project_id = '43e784b6-7e9a-11ed-adcb-9e0127c25975' -- project_a -- 
ORDER BY ct.created_at limit 1
```

以下のような偏りのテーブルを作成します。
`未対応`の件数が増え、2000000 / 50000000である程度のカーディナリティがあり、
`未対応`のうち件数の多い project_aは `2022-12-01` に作成され、
`未対応`のうち件数の少ない project_bは `2022-12-02` に作成さました。

参考

<details>
<summary>準備用のクエリ</summary>

```sql
INSERT INTO projects ('43e784b6-7e9a-11ed-adcb-9e0127c25975', 'project_a');
INSERT INTO projects values ('54c7322c-7e9a-11ed-adcb-9e0127c25975', 'project_b');

INSERT INTO parent_tasks (name, project_id) values ('project_a_task', '43e784b6-7e9a-11ed-adcb-9e0127c25975');
-- 複数回実行して、レコードを増やす --
INSERT INTO parent_tasks (name, project_id) (SELECT 'project_a_task', '43e784b6-7e9a-11ed-adcb-9e0127c25975' from parent_tasks);
INSERT INTO parent_tasks (name, project_id) (SELECT 'project_b_task', '54c7322c-7e9a-11ed-adcb-9e0127c25975' from parent_tasks);

INSERT INTO child_tasks (name, parent_task_id, state, created_at) (SELECT 'project_a_task_1', id, 'initialize', now() from parent_tasks where project_id = '43e784b6-7e9a-11ed-adcb-9e0127c25975');
INSERT INTO child_tasks (name, parent_task_id, state, created_at) (SELECT 'project_a_task_2', id, 'done', now() from parent_tasks where project_id = '43e784b6-7e9a-11ed-adcb-9e0127c25975');
INSERT INTO child_tasks (name, parent_task_id, state, created_at) (SELECT 'project_b_task_1', id, 'done', now() from parent_tasks where project_id = '54c7322c-7e9a-11ed-adcb-9e0127c25975');
INSERT INTO child_tasks (name, parent_task_id, state, created_at) (SELECT 'project_b_task_2', id, 'done', now() from parent_tasks where project_id = '54c7322c-7e9a-11ed-adcb-9e0127c25975');

-- 末尾の500件のproject_bのタスクをinitializeかつ今に近い時間に変更する -- 
UPDATE child_tasks  SET created_at=now() where id > '<末尾の500件>';
UPDATE child_tasks  SET state='initialize' where id > '<末尾の500件>';
```
</details>


|  stete  | project | タスク作成時間| 件数  |
| ---- | ---- | ---- | ---- |  
|  initialize | project_a | すべて2022-12-01に作成  | 1999500  |
|  initialize | project_b | すべて2022-12-02に作成(2022-12-01以降にする) | 500  |
|  done  | project_a, project_b | 2022-12-01 | 50000000  |


実行計画はproject_a(43e784b6-7e9a-11ed-adcb-9e0127c25975)もproject_b(54c7322c-7e9a-11ed-adcb-9e0127c25975)も同じになります。

`project_a`
```
mysql> explain SELECT   ct.id FROM   child_tasks ct JOIN parent_tasks pt ON ct.parent_task_id = pt.id WHERE   ct.state = 'initialize'   AND pt.project_id = '43e784b6-7e9a-11ed-adcb-9e0127c25975' ORDER BY ct.created_at limit 1;
+----+-------------+-------+------------+--------+---------------------------------------------------------------+---------------------------------------+---------+------------------------------+----------+----------+-----------------------+
| id | select_type | table | partitions | type   | possible_keys                                                 | key                                   | key_len | ref                          | rows     | filtered | Extra                 |
+----+-------------+-------+------------+--------+---------------------------------------------------------------+---------------------------------------+---------+------------------------------+----------+----------+-----------------------+
|  1 | SIMPLE      | ct    | NULL       | ref    | index_child_tasks_on_state_created_at,fk_child_task_parent_id | index_child_tasks_on_state_created_at | 1022    | const                        | 25146433 |   100.00 | Using index condition |
|  1 | SIMPLE      | pt    | NULL       | eq_ref | PRIMARY,fk_parent_task_project_id                             | PRIMARY                               | 4       | index_test.ct.parent_task_id |        1 |    50.00 | Using where           |
+----+-------------+-------+------------+--------+---------------------------------------------------------------+---------------------------------------+---------+------------------------------+----------+----------+-----------------------+
2 rows in set, 1 warning (0.00 sec)
```

`project_b`
```
mysql> explain SELECT   ct.id FROM   child_tasks ct JOIN parent_tasks pt ON ct.parent_task_id = pt.id WHERE   ct.state = 'initialize'   AND pt.project_id = '54c7322c-7e9a-11ed-adcb-9e0127c25975' ORDER BY ct.created_at limit 1;
+----+-------------+-------+------------+--------+---------------------------------------------------------------+---------------------------------------+---------+------------------------------+----------+----------+-----------------------+
| id | select_type | table | partitions | type   | possible_keys                                                 | key                                   | key_len | ref                          | rows     | filtered | Extra                 |
+----+-------------+-------+------------+--------+---------------------------------------------------------------+---------------------------------------+---------+------------------------------+----------+----------+-----------------------+
|  1 | SIMPLE      | ct    | NULL       | ref    | index_child_tasks_on_state_created_at,fk_child_task_parent_id | index_child_tasks_on_state_created_at | 1022    | const                        | 25146433 |   100.00 | Using index condition |
|  1 | SIMPLE      | pt    | NULL       | eq_ref | PRIMARY,fk_parent_task_project_id                             | PRIMARY                               | 4       | index_test.ct.parent_task_id |        1 |    50.00 | Using where           |
+----+-------------+-------+------------+--------+---------------------------------------------------------------+---------------------------------------+---------+------------------------------+----------+----------+-----------------------+
2 rows in set, 1 warning (0.00 sec)
```

しかし実行時間は、`project_a`は一瞬でクエリが返却され、`project_b`は1分以上の時間がかかります。

`project_a`
```
mysql> SELECT   ct.id FROM   child_tasks ct JOIN parent_tasks pt ON ct.parent_task_id = pt.id WHERE   ct.state = 'initialize'   AND pt.project_id = '43e784b6-7e9a-11ed-adcb-9e0127c25975' ORDER BY ct.created_at limit 1;
+----+
| id |
+----+
|  1 |
+----+
1 row in set (0.00 sec)
```

`project_b`
```
mysql> SELECT   ct.id FROM   child_tasks ct JOIN parent_tasks pt ON ct.parent_task_id = pt.id WHERE   ct.state = 'initialize'   AND pt.project_id = '54c7322c-7e9a-11ed-adcb-9e0127c25975' ORDER BY ct.created_at limit 1;
+----------+
| id       |
+----------+
| 50402207 |
+----------+
1 row in set (1 min 29.11 sec)
```

これは実行計画の1行目によると、`Using index condition` が使用されていることから、created_atの昇順でレコードを取得し、fetchされたのが`25146433`件という事がわかります。
2行目では`25146433`件に対して、parent_tasksに対してjoinを行い、`Using where`による(ここではproject_idによる)絞り込みが行われています。

[MySQLではソートとlimitを同時につける場合は、ソートされた結果の最初の row_count 行が見つかるとすぐにソートを終了する](https://dev.mysql.com/doc/refman/5.6/ja/limit-optimization.html)、とのことです。

> ORDER BY とともに LIMIT row_count を使用した場合、MySQL では、結果全体をソートするのではなく、ソートされた結果の最初の row_count 行が見つかるとすぐにソートを終了します。インデックスを使用して順序付けが行われている場合、これはきわめて高速になります。filesort を実行する必要がある場合、最初の row_count を見つける前に、LIMIT 句を使用しないクエリーに一致するすべての行が選択され、それらのほとんどまたはすべてがソートされます。初期の行が見つかったら、MySQL は結果セットの残りをすべてソートしません。

今回の事象では、created_atの昇順でレコードを取得したあとに、project_idで絞り込みが行われて、最初にヒットした1件で処理が終了します。

そのため、タスクが2022-12-０１で作成された`project_a`はすぐにクエリが終了し、
タスクが2022-12-02で作成された`project_b`は、2022-12-０１で作成された1999500件の絞り込みを行ってから初めの1件がヒットするため、クエリ時間が長くなるようです。


## どう解決したか

今回の問題は、以下だと考えました
- Joinした先のカラム(今回でいうproject_id)で絞り込みをする必要があるため、fetchする件数の影響を受けてしまう

上記はインデックスを使用できれば本来は発生しない問題ですが、Joinした先のカラムではインデックスを使用することができません。
今回は非正規化をし、インデックスを使用できるように修正を行いました。
`child_tasks`に`project_id` を追加した上で、`index_child_tasks_on_project_id_state_created_at`のインデックスを追加しました。

```SQL
ALTER TABLE child_tasks ADD `project_id` varchar(255) COLLATE utf8mb4_bin;
ALTER TABLE child_tasks ADD INDEX `index_child_tasks_on_project_id_state_created_at` (`project_id`,`state`,`created_at`);
UPDATE child_tasks  SET project_id='43e784b6-7e9a-11ed-adcb-9e0127c25975' where name = 'project_a_task_1' or name = 'project_a_task_2'; 
UPDATE child_tasks  SET project_id='54c7322c-7e9a-11ed-adcb-9e0127c25975' where name = 'project_b_task_1' or name = 'project_b_task_2'; 
```

修正後のクエリ

```sql
SELECT
  ct.id
FROM
  child_tasks ct
WHERE
  ct.state = 'initialize'
  AND ct.project_id = '43e784b6-7e9a-11ed-adcb-9e0127c25975' -- project_a -- 
ORDER BY ct.created_at limit 1
```

selectも速くなりました。

`project_a`
```
mysql> SELECT
    ->   ct.id
    -> FROM
    ->   child_tasks ct
    -> WHERE
    ->   ct.state = 'initialize'
    ->   AND ct.project_id = '43e784b6-7e9a-11ed-adcb-9e0127c25975'
    -> ORDER BY ct.created_at limit 1;
+----+
| id |
+----+
|  1 |
+----+
1 row in set (0.00 sec)
```

`project_b`

```
mysql> SELECT
    ->   ct.id
    -> FROM
    ->   child_tasks ct
    -> WHERE
    ->   ct.state = 'initialize'
    ->   AND ct.project_id = '54c7322c-7e9a-11ed-adcb-9e0127c25975'
    -> ORDER BY ct.created_at limit 1;
+----------+
| id       |
+----------+
| 50402207 |
+----------+
1 row in set (0.00 sec)
```

実行計画もシンプルになりました。

```
mysql> explain SELECT
    ->   ct.id
    -> FROM
    ->   child_tasks ct
    -> WHERE
    ->   ct.state = 'initialize'
    ->   AND ct.project_id = '54c7322c-7e9a-11ed-adcb-9e0127c25975'
    -> ORDER BY ct.created_at limit 1;
+----+-------------+-------+------------+------+----------------------------------------------------------------------------------------+--------------------------------------------------+---------+-------------+--------+----------+--------------------------+
| id | select_type | table | partitions | type | possible_keys                                                                          | key                                              | key_len | ref         | rows   | filtered | Extra                    |
+----+-------------+-------+------------+------+----------------------------------------------------------------------------------------+--------------------------------------------------+---------+-------------+--------+----------+--------------------------+
|  1 | SIMPLE      | ct    | NULL       | ref  | index_child_tasks_on_state_created_at,index_child_tasks_on_project_id_state_created_at | index_child_tasks_on_project_id_state_created_at | 2045    | const,const | 111342 |   100.00 | Using where; Using index |
+----+-------------+-------+------------+------+----------------------------------------------------------------------------------------+--------------------------------------------------+---------+-------------+--------+----------+--------------------------+
1 row in set, 1 warning (0.00 sec)
```


## まとめ
実行計画上では、インデックスが使われているものの、速度改善の余地がある事例でした。
実際には非正規化の判断をするまでにチーム内での合意や、アプリケーション上のコードを修正やマイグレーションが必要なためかなりハードな修正になります。(マイグレーション時のupdate文はかなり時間がかかりました...)
また親タスクと、小タスクにそれぞれproject_idが付与されるため、二重での管理が必要になります。保守性とパフォーマンスを考慮して修正をする必要があるかと思います。