この記事は ラクス Advent Calendar 2019 の5日目の記事です。
弊社、株式会社ラクスパートナーズ は特定派遣の会社なのでいくつかの現場に行かせてもらっているのですが、いまだに MySQL8.0 を使用している現場は見たことがありません。まあ、MySQLに性能や機能を求めてない現場ばかりだったというのもりますが。
本記事ではMySQL8.0で追加されたWITH句(CTE)を紹介します。速度面はではく、SQLの可読性の面を取り扱いMySQLのバージョンアップでミドル自体の性能向上以外にも、コードの可読性、メンテ性の向上も図れることを示せたらなと思います
本記事は公式ドキュメントに添います。引用はこのドキュメントからになります
MySQL :: MySQL 8.0 Reference Manual :: 13.2.13 WITH (Common Table Expressions)
使用するデータも公式にあるサンプルを使用します。下記サイトの Expert Guides - world database にあるデータを使用しています
MySQL :: Other MySQL Documentation
WITH句
WITH句はサブクエリ結果に名前をつけ、クエリ実行中のみ有効な一時テーブルを作成する機能です。使い方は他のDBMSと変わりません
WITH cte1 AS (SELECT a, b FROM table1), cte2 AS (SELECT c, d FROM table2) SELECT b, d FROM cte1 JOIN cte2 WHERE cte1.a = cte2.c;
このSQLではWITHのブロックで cte1、 cte2 の二つの名前のついた一時テーブルを作成します。以降のSELECT文ではその名前を使って一時テーブルに対し操作を行っています
例
スペイン語を話すヨーロッパの都市を抽出する WHERE句の副問合せ、JOIN句、WITH句 を用いた例を示します
WHERE句の副問合せ
副問合せは読みづらいし、書きづらいです。データ量が多くなれば速度も出ません。
SELECT * FROM city WHERE CountryCode IN (
SELECT CountryCode FROM countrylanguage
WHERE Language = 'Spanish' AND CountryCode IN (
SELECT Code FROM country WHERE Continent = 'Europe'
)
);
JOIN句
いままでよくやる手法です。副問合せよりマシですが、JOIN一つ一つ追わないと意図の把握ができません
SELECT a.* FROM city a
JOIN countrylanguage b
ON b.CountryCode = a.CountryCode AND b.Language = 'Spanish'
JOIN country c
ON c.Code = b.CountryCode AND c.Continent = 'Europe';
WITH句
JOINでつないでいたテーブルをWITH句に切り出し意味の分かるエイリアスを付けました。SELECT文は簡潔になり、WITH句を読まなくても意図がわかると思います
トランザクションが使える場面では似たことを TEMPORARY TABLE を使って行うと思いますが、WITH句を使うことで1つのクエリ内で完結できるのがいいですね
WITH europe_using_spanish AS (
SELECT a.CountryCode FROM countrylanguage a
JOIN country b ON b.Code = a.CountryCode
AND a.Language = 'Spanish' AND b.Continent = 'Europe'
)
SELECT a.* FROM city a
JOIN europe_using_spanish b ON b.CountryCode = a.CountryCode;
示したのは簡単な例でしたがそれでも違いが出るので、うまく使えることができれば可読性が高くメンテナンス性の高いクエリを書けるようになると思います。
新機能も性能も上がった新バージョンがあるのに、前バージョンでインデックスやパーテションをいじって速度アップを図るのは疲れますよね。MySQL8.0に上げることで性能だけでなくSQL、アプリのコードの改善も可能になりますヨ。ということで、バージョンアップの検討材料の材料になればと思います。