こんにちは！アルダグラムでエンジニアをしている森下霞です。
先日、弊社では MySQL5.7のサポート期限が迫ってきたのでMySQL8に移行しました。そこで、開発者として手に入れて嬉しかったものについて、この記事で説明できればと思います。
CTE クエリのリードビリティーと多機能性
MySQL 8.0 で導入された CTE (Common Table Expressions) を使用するクエリを具体的な例で見るため、まずは3つのテーブルを用意します。
- anime (id, title, genre)
- scores (id, anime_id, score)
- episodes (id, anime_id, added_at)
テーブル作成の SQL クエリ
CREATE TABLE anime (
id INT PRIMARY KEY NOT NULL AUTO_INCREMENT,
title VARCHAR(255) NOT NULL,
genre VARCHAR(100) NOT NULL
);
CREATE TABLE scores (
id INT PRIMARY KEY NOT NULL AUTO_INCREMENT,
anime_id INT,
score DECIMAL,
INDEX idx_anime_id (anime_id),
FOREIGN KEY (anime_id) REFERENCES anime(id)
);
CREATE TABLE episodes (
id INT PRIMARY KEY NOT NULL AUTO_INCREMENT,
anime_id INT,
added_at DATETIME,
INDEX idx_anime_id (anime_id),
FOREIGN KEY (anime_id) REFERENCES anime(id)
);
データ生成
クエリのパフォーマンスを試すのに多めのデータを生成します。
まずは anime テーブルから。
再帰的な問い合わせを行う SELECT ステートメントであるリカーシブ CTE を使用して 1000 レコードを生成します。
INSERT INTO anime (title, genre) (
-- ループを1000回繰り返す
WITH RECURSIVE nrows AS (
-- 1回目。初期値
SELECT 1 AS i
UNION ALL
-- 999回
-- WHERE でループから出る条件を定義する
-- iは1000になったらループが止まる
SELECT i + 1 FROM nrows WHERE i < 1000
)
-- title_1, title_2, ..., title_1000
-- genre_0, genre_2, ..., genre_9 の中からランダムに設定する
SELECT CONCAT('title_', i), CONCAT('genre_', i % 10)
FROM nrows
);
注意：1000 以上を作成しようとすると、以下のエラーが出ます。
ERROR 3636 (HY000): Recursive query aborted after 1001 iterations. Try increasing @@cte_max_recursion_depth to a larger value.
デフォルトの @@cte_max_recursion_depth 値を変えないと、反復数は 1000 に限定されます。
10,000 レコードを作るのに現在のデータを使用して残りの 9 000 を生成します。
INSERT INTO anime (title, genre, sort_order) (
-- ループを10回繰り返す
WITH RECURSIVE nrows AS (
SELECT 1 AS i
UNION ALL
SELECT i + 1 FROM nrows WHERE i < 9
)
-- title_1001, ..., title_10000
SELECT CONCAT('title_', anime.id + 1), CONCAT('genre_', anime.id % 10)
FROM nrows
-- 10 x 1000
CROSS JOIN anime
);
注意：上記のクエリは5分ほど時間がかかりました。
同じく 10,000,000 の scores と 100,000 の episodes を生成します。
INSERT INTO scores (anime_id, score) (
WITH RECURSIVE nrows AS (
SELECT 1 AS i
UNION ALL
SELECT i + 1 FROM nrows WHERE i < 1000
)
-- 各アニメごとに 1000個 score を作成
-- score = 1..5
SELECT anime.id, rand() * 5
FROM nrows
CROSS JOIN anime
);
INSERT INTO episodes (anime_id, added_at) (
WITH RECURSIVE nrows AS (
SELECT 1 AS i
UNION ALL
SELECT i + 1 FROM nrows WHERE i < 10
)
-- 各アニメごとに 10個 episode を作成
-- added_at = 千日前から昨日まで
SELECT anime.id, now() - INTERVAL rand() * 1000 DAY
FROM nrows
CROSS JOIN anime
);
直近でエピソードが追加されたアニメを5個取得します。
WITH latest_episodes AS (
SELECT anime_id, MAX(added_at) AS last_episode_added_at
FROM episodes
GROUP BY anime_id
ORDER BY last_episode_added_at DESC
LIMIT 5
)
SELECT title, genre, last_episode_added_at
FROM anime
INNER JOIN latest_episodes ON (id = anime_id);
平均 score が上位5位のアニメを取得して、最後のエピソードが追加された日付を取得します。
WITH average_scores AS (
SELECT anime_id, AVG(score) AS average_score
FROM scores
GROUP BY anime_id
ORDER BY average_score DESC
LIMIT 5
), last_episodes AS (
SELECT anime_id, average_score, MAX(added_at) AS last_episode_added_at
FROM episodes
-- 他の CTE を参照する
INNER JOIN average_scores USING (anime_id)
GROUP BY anime_id
)
SELECT title, genre, average_score, last_episode_added_at
FROM anime
INNER JOIN last_episodes ON (id = anime_id);
平均スコアをリアルタイム計算ではなくデータで持ちたい場合に、anime テーブルに average_score のカラムを追加して、UPDATE時に CTE を使用して計算します。
WITH average_scores AS (
SELECT anime_id, AVG(score) AS average_score
FROM scores
GROUP BY anime_id
)
UPDATE anime
INNER JOIN average_scores ON (id = anime_id)
SET anime.average_score = average_scores.average_score;
CTE でスコープを指定して WHERE IN SELECT など使わずにクエリができるのはとても便利だと思います。
参考：https://dev.mysql.com/doc/refman/8.0/ja/with.html
Rails での使い方
Rails の ActiveRecord でクエリを行う際に、Rails 7.1 だとそのまま使えます。
latest_episodes = Episode.select('anime_id, MAX(added_at) AS last_episode_added_at')
.group(:anime_id)
.order('MAX(added_at) DESC')
.limit(5)
result = Anime.with(latest_episodes: latest_episodes)
.joins('INNER JOIN latest_episodes ON animes.id = latest_episodes.anime_id')
.select('animes.title, animes.genre, latest_episodes.last_episode_added_at')
Rails 7.1 の前は、gem activerecord-cte を導入して同じ使い方ができます。
インスタント ADD COLUMN
以前、MySQL 5.7の大規模なテーブルにカラムを追加しようとした話をしました。MySQL 5.7 だと100-500万ほど行数のテーブルで追加は1分以上かかりましたが、MySQL 8.0 で試しましょう。
mysql> ALTER TABLE scores ADD (username VARCHAR(255) NOT NULL);
Query OK, 0 rows affected (0.18 sec)
1000万のテーブルで 0.18秒。すごい。
EXPLAIN ANALYZE でクエリ分析
MySQL 5.7 だと、クエリの分析のために EXPLAIN を使うことができました。（手元でMySQL 5.7 がなく、例は MySQL 8.0 からです）
mysql> EXPLAIN WITH average_scores AS (
-> SELECT scores.anime_id, AVG(score) AS average_score
-> FROM scores
-> GROUP BY anime_id
-> ORDER BY average_score DESC
-> LIMIT 5
-> )
-> SELECT title, genre, average_scores.average_score
-> FROM anime
-> INNER JOIN average_scores ON (id = anime_id);
+----+-------------+------------+------------+--------+---------------+--------------+---------+-------------------------+---------+----------+---------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+------------+------------+--------+---------------+--------------+---------+-------------------------+---------+----------+---------------------------------+
| 1 | PRIMARY | <derived2> | NULL | ALL | NULL | NULL | NULL | NULL | 5 | 100.00 | Using where |
| 1 | PRIMARY | anime | NULL | eq_ref | PRIMARY | PRIMARY | 4 | average_scores.anime_id | 1 | 100.00 | NULL |
| 2 | DERIVED | scores | NULL | index | idx_anime_id | idx_anime_id | 5 | NULL | 9979137 | 100.00 | Using temporary; Using filesort |
+----+-------------+------------+------------+--------+---------------+--------------+---------+-------------------------+---------+----------+---------------------------------+
3 rows in set, 1 warning (0.02 sec)
idx_anime_id のインデックスを使うだろうと読み取れます。 scores のところで Extra カラムの Using temporary; Using filesort を見ると、「遅いだろう」と思うが、具体的にどうして遅いかよく想像できません。
実際にクエリを実行すると、3 min 2.43 sec がかかります。インデックス足りるのかな、、（筆者は score でインデックスがないの知らないふり）
MySQL 8.0 で導入された EXPLAIN ANALYZE を使ってみます。EXPLAIN ANALYZE は実際にクエリを実行するので EXPLAIN より時間がかかるが、クエリの実行方法について全部をきちんと表示してくれます。
mysql> EXPLAIN ANALYZE WITH average_scores AS (
-> SELECT scores.anime_id, AVG(score) AS average_score
-> FROM scores
-> GROUP BY anime_id
-> ORDER BY average_score DESC
-> LIMIT 5
-> )
-> SELECT title, genre, average_scores.average_score
-> FROM anime
-> INNER JOIN average_scores ON (id = anime_id);
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| EXPLAIN |
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| -> Nested loop inner join (cost=4.81 rows=5) (actual time=190808.750..190816.964 rows=5 loops=1)
-> Filter: (average_scores.anime_id is not null) (cost=0.61..3.06 rows=5) (actual time=190804.171..190804.181 rows=5 loops=1)
-> Table scan on average_scores (cost=2.50..2.50 rows=0) (actual time=0.064..0.068 rows=5 loops=1)
-> Materialize CTE average_scores (cost=2.50..2.50 rows=0) (actual time=190804.094..190804.101 rows=5 loops=1)
-> Limit: 5 row(s) (actual time=190803.398..190803.405 rows=5 loops=1)
-> Sort: average_score DESC, limit input to 5 row(s) per chunk (actual time=190803.362..190803.364 rows=5 loops=1)
-> Stream results (cost=2021033.40 rows=9979137) (actual time=120.644..190733.500 rows=10000 loops=1)
-> Group aggregate: avg(scores.score) (cost=2021033.40 rows=9979137) (actual time=120.540..190586.894 rows=10000 loops=1)
-> Index scan on scores using idx_anime_id (cost=1023119.70 rows=9979137) (actual time=89.077..184541.092 rows=10000000 loops=1)
-> Single-row index lookup on anime using PRIMARY (id=average_scores.anime_id) (cost=0.27 rows=1) (actual time=2.529..2.529 rows=1 loops=5)
|
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (3 min 10.85 sec)
あら、やはり違うインデックス追加した方が良さそう。idx_anime_id は本当に使われるが、avg(scores.score) の計算が結構時間かかります。
インデックス追加します。
ALTER TABLE scores
ADD INDEX idx_anime_id_score (anime_id, score);
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| EXPLAIN |
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| -> Nested loop inner join (cost=4.81 rows=5) (actual time=17735.319..17735.949 rows=5 loops=1)
-> Filter: (average_scores.anime_id is not null) (cost=0.61..3.06 rows=5) (actual time=17734.623..17734.632 rows=5 loops=1)
-> Table scan on average_scores (cost=2.50..2.50 rows=0) (actual time=0.017..0.021 rows=5 loops=1)
-> Materialize CTE average_scores (cost=2.50..2.50 rows=0) (actual time=17734.586..17734.592 rows=5 loops=1)
-> Limit: 5 row(s) (actual time=17734.018..17734.028 rows=5 loops=1)
-> Sort: average_score DESC, limit input to 5 row(s) per chunk (actual time=17734.011..17734.013 rows=5 loops=1)
-> Stream results (cost=2021033.40 rows=9979137) (actual time=7.424..17715.842 rows=10000 loops=1)
-> Group aggregate: avg(scores.score) (cost=2021033.40 rows=9979137) (actual time=7.185..17685.988 rows=10000 loops=1)
-> Covering index scan on scores using idx_anime_id_score (cost=1023119.70 rows=9979137) (actual time=3.152..11762.248 rows=10000000 loops=1)
-> Single-row index lookup on anime using PRIMARY (id=average_scores.anime_id) (cost=0.27 rows=1) (actual time=0.253..0.253 rows=1 loops=5)
|
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (17.77 sec)
データが多い（1000万行）のでまだ遅いけど10倍ほど早くなりました！
EXPLAIN ANALYZE の方が avg(scores.score) の計算が時間かかると教えたからクエリの改善ができました。
ちなみに、idx_anime_id_score があると anime_id の検索のクエリに効くから、元々追加された idx_anime_id がいらなくなるので削除します。
ALTER TABLE scores DROP INDEX idx_anime_id;
実際の運用では、CTE のところで述べた anime テーブルに average_score のカラムを追加した方が効率的です。ただし、そのカラムのアップデートのためにもインデックスが使われるのであった方が有利です。
なお、EXPLAIN ANALYZE はクエリを実行するが、実際のデータに影響を及ばさないので UPDATE でも安心で使われます。
-- クエリの前に average_score が NULL と確認
mysql> SELECT * FROM anime LIMIT 5;
+----+---------+---------+---------------+
| id | title | genre | average_score |
+----+---------+---------+---------------+
| 1 | title_1 | genre_1 | NULL |
| 2 | title_2 | genre_2 | NULL |
| 3 | title_3 | genre_3 | NULL |
| 4 | title_4 | genre_4 | NULL |
| 5 | title_5 | genre_5 | NULL |
+----+---------+---------+---------------+
5 rows in set (0.01 sec)
mysql> EXPLAIN ANALYZE WITH additional AS (
SELECT scores.anime_id, AVG(score) AS average_score
FROM scores
GROUP BY anime_id
)
UPDATE anime
JOIN additional ON (id = anime_id)
SET anime.average_score = additional.average_score;
------------------------------------------------------------------------------------+
| -> Update anime (immediate)
-> Nested loop inner join (cost=9880029733.95 rows=98775339054) (actual time=38458.646..38561.374 rows=10000 loops=1)
-> Table scan on anime (cost=997.80 rows=9898) (actual time=1.667..45.555 rows=10000 loops=1)
-> Index lookup on additional using <auto_key0> (anime_id=anime.id) (actual time=0.003..0.004 rows=1 loops=10000)
-> Materialize CTE additional (cost=3015225.15..3015225.15 rows=9979323) (actual time=38496.427..38505.236 rows=10000 loops=1)
-> Group aggregate: avg(scores.score) (cost=2017292.85 rows=9979323) (actual time=9.176..38356.804 rows=10000 loops=1)
-> Covering index scan on scores using idx_anime_id_score (cost=1019360.55 rows=9979323) (actual time=0.454..31747.897 rows=10000000 loops=1)
|
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (38.60 sec)
-- クエリ実行後にも average_score が NULL のまま
mysql> SELECT * FROM anime LIMIT 5;
+----+---------+---------+---------------+
| id | title | genre | average_score |
+----+---------+---------+---------------+
| 1 | title_1 | genre_1 | NULL |
| 2 | title_2 | genre_2 | NULL |
| 3 | title_3 | genre_3 | NULL |
| 4 | title_4 | genre_4 | NULL |
| 5 | title_5 | genre_5 | NULL |
+----+---------+---------+---------------+
5 rows in set (0.01 sec)
参考：https://dev.mysql.com/doc/refman/8.0/ja/explain.html#explain-analyze
降順インデックス
ORDER BY ... DESC のシンタクスは MySQL 8.0 の前もサポートはされていたが、無視されていました。ただし、単純なクエリだと昇順インデックスが降順のクエリでも使用されていました。
例えば、
ALTER TABLE scores
ADD INDEX idx_score (score);
を追加するとか、以下のどちらのクエリにもインデックスが MySQL 5.7 でも効きました。（手元で MySQL 5.7 がなく、例は MySQL 8.0 からです）
mysql> EXPLAIN ANALYZE SELECT * FROM scores ORDER BY score ASC LIMIT 5;
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| EXPLAIN |
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| -> Limit: 5 row(s) (cost=0.01 rows=5) (actual time=4.048..4.195 rows=5 loops=1)
-> Index scan on scores using idx_score (cost=0.01 rows=5) (actual time=4.020..4.160 rows=5 loops=1)
|
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.01 sec)
mysql> EXPLAIN ANALYZE SELECT * FROM scores ORDER BY score DESC LIMIT 5;
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| EXPLAIN |
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| -> Limit: 5 row(s) (cost=0.01 rows=5) (actual time=3.150..3.391 rows=5 loops=1)
-> Index scan on scores using idx_score (reverse) (cost=0.01 rows=5) (actual time=3.137..3.371 rows=5 loops=1)
|
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
DESC だと、reverse のスキャンを行います。
Index scan on scores using idx_score (reverse)
ただ、以下のクエリを行いたい場合は、MySQL 8.0 でしかできない降順インデックスで助かります。
インデックスがない場合。
mysql> EXPLAIN ANALYZE SELECT * FROM scores ORDER BY score DESC, username ASC LIMIT 5;
|
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| -> Limit: 5 row(s) (cost=1023119.70 rows=5) (actual time=45897.193..45897.206 rows=5 loops=1)
-> Sort: scores.score DESC, scores.username, limit input to 5 row(s) per chunk (cost=1023119.70 rows=9979137) (actual time=45897.166..45897.169 rows=5 loops=1)
-> Table scan on scores (cost=1023119.70 rows=9979137) (actual time=0.979..31617.572 rows=10000000 loops=1)
|
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (45.92 sec)
ASC のインデックスを追加してしまうと使われていない。インデックスの定義と同じクエリだと、使われています。
ALTER TABLE scores
-- ASC/DESC を定義しなければデフォルトで ASC になる
ADD INDEX idx_score_username (score, username);
-- このクエリに使われていない
mysql> EXPLAIN ANALYZE SELECT * FROM scores ORDER BY score DESC, username ASC LIMIT 5;
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| EXPLAIN |
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| -> Limit: 5 row(s) (cost=1023119.70 rows=5) (actual time=46615.320..46615.331 rows=5 loops=1)
-> Sort: scores.score DESC, scores.username, limit input to 5 row(s) per chunk (cost=1023119.70 rows=9979137) (actual time=46615.292..46615.296 rows=5 loops=1)
-> Table scan on scores (cost=1023119.70 rows=9979137) (actual time=7.170..32310.792 rows=10000000 loops=1)
|
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (46.63 sec)
-- 順番を変えると使われる
mysql> EXPLAIN ANALYZE SELECT * FROM scores ORDER BY score ASC, username ASC LIMIT 5;
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| EXPLAIN |
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| -> Limit: 5 row(s) (cost=0.01 rows=5) (actual time=3.209..3.474 rows=5 loops=1)
-> Index scan on scores using idx_score_username (cost=0.01 rows=5) (actual time=3.126..3.357 rows=5 loops=1)
|
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.03 sec)
DESC のインデックスの定義。
ALTER TABLE scores
ADD INDEX idx_score_username_desc (score DESC, username);
早くなりました。
mysql> EXPLAIN ANALYZE SELECT * FROM scores ORDER BY score DESC, username ASC LIMIT 5;
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| EXPLAIN |
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| -> Limit: 5 row(s) (cost=0.01 rows=5) (actual time=4.815..4.984 rows=5 loops=1)
-> Index scan on scores using idx_score_username_desc (cost=0.01 rows=5) (actual time=4.735..4.877 rows=5 loops=1)
|
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.02 sec)
参考：https://dev.mysql.com/doc/refman/8.0/ja/descending-indexes.html
関数インデックス
エピソードを追加日で検索する場合を考えてみましょう。最新のエピソードを検索します。
mysql> EXPLAIN ANALYZE SELECT * FROM episodes ORDER BY added_at DESC LIMIT 5;
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| EXPLAIN |
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| -> Limit: 5 row(s) (cost=10096.75 rows=5) (actual time=215.528..215.532 rows=5 loops=1)
-> Sort: episodes.added_at DESC, limit input to 5 row(s) per chunk (cost=10096.75 rows=100405) (actual time=215.520..215.522 rows=5 loops=1)
-> Table scan on episodes (cost=10096.75 rows=100405) (actual time=0.796..135.544 rows=100000 loops=1)
|
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.22 sec)
インデックスがないので全テーブルスキャンを行わなければなりません。（ただ、そもそも10万行しかないので早いが EXPLAIN の中身を見るために episodes を使わせていただきます）
インデックスを追加しました。単純なクエリでちゃんと使われています。
ALTER TABLE episodes
ADD INDEX idx_added_at_desc (added_at DESC);
mysql> EXPLAIN ANALYZE SELECT * FROM episodes ORDER BY added_at DESC LIMIT 5;
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| EXPLAIN |
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| -> Limit: 5 row(s) (cost=0.00 rows=5) (actual time=4.941..5.130 rows=5 loops=1)
-> Index scan on episodes using idx_added_at_desc (cost=0.00 rows=5) (actual time=4.480..4.660 rows=5 loops=1)
|
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.04 sec)
でも月毎でも検索を行いたい。
mysql> EXPLAIN ANALYZE SELECT * FROM episodes WHERE DATE_FORMAT(added_at, '%Y-%m') = '2024-05' ;
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| EXPLAIN |
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| -> Filter: (date_format(episodes.added_at,'%Y-%m') = '2024-05') (cost=9845.15 rows=97889) (actual time=0.840..255.670 rows=2850 loops=1)
-> Table scan on episodes (cost=9845.15 rows=97889) (actual time=0.679..120.887 rows=100000 loops=1)
|
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.26 sec)
added_at にインデックスがかかっているが、関数で検索すると効きません。
MySQL 8.0 で導入された関数インデックスを追加します。
ALTER TABLE episodes
ADD INDEX idx_added_at_month ((DATE_FORMAT(added_at, '%Y-%m')));
mysql> EXPLAIN ANALYZE SELECT * FROM episodes WHERE DATE_FORMAT(added_at, '%Y-%m') = '2024-05' ;
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| EXPLAIN |
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| -> Index lookup on episodes using idx_added_at_month (date_format(added_at,_utf8mb4'%Y-%m')='2024-05') (cost=453.75 rows=2850) (actual time=4.839..65.214 rows=2850 loops=1)
|
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.08 sec)
インデックス使われてます！3倍も早くなりました。
参考：https://dev.mysql.com/doc/refman/8.0/ja/create-index.html#create-index-functional-key-parts
総括
MySQL 5.7 から MySQL 8.0 にアップグレードしたことで、様々な課題に対する新しい解決法を手に入れました。
マイグレーションやクエリの実行速度が向上し、クエリ分析がよりしやすくなり、複雑なクエリも書きやすくなりました。
MySQL 8.0 の利便性の一部をまとめたこの記事がどなたかに役に立てば嬉しいです。
もっとアルダグラムエンジニア組織を知りたい人、ぜひ下記の情報をチェックしてみてください！
株式会社アルダグラムのTech Blogです。 世界中のノンデスクワーク業界における現場の生産性アップを実現する現場DXサービス「KANNA」を開発しています。 採用情報はこちら herp.careers/v1/aldagram0508/
Discussion