MySQL の SubQuery と Join をハイパー雑にまとめていく
はじめに
どうも、最近どう頑張っても Netflix で配信されている吹き替え版 ミッションインポッシブル のトム・クルーズが好きになれないけんつです。
今回は最近ふと、MySQL の話とか SQL の話って今までまともにブログにアウトプットしたことがないことに気がついたのでぼちぼちアウトプットしていこうかなと思って書き始めます。
とりあえず、昨日 Twitter の TL で INNER JOIN とか SUBQUERY の話を見かけたのでそのへんから話して行こうかなと思っています。
開発環境
- MySQL 8.0.16
サンプル用のデータには「Sakila Sample Database」を使用する。
MySQL :: Sakila Sample Database
https://dev.mysql.com/doc/sakila/en/
他にもいくつかサンプルデータベースは存在するっぽいけど、今回は↑これを採用する。
MySQL :: Other MySQL Documentation https://dev.mysql.com/doc/index-other.html
サンプルデータ
MySQL :: Other MySQL Documentation https://dev.mysql.com/doc/index-other.html
このサイトから zip かなにかしらでダウンロードしてくる。
そのあとに次の source 文でデータを生成できる。
> source ./path/to/sakila-schema.sql; > source ./path/to/sakila-data.sql;
構造
これは公式ドキュメントから拾ってきたものだけど、sakila sample database は↑このような構造を取っている。
色々みていくとわかるのだけど、 テーブルにビューもあれば、ストアードプロシージャもあったりと一通り MySQL で使える機能は大体サンプルとして入っている。
余談
手元の環境を MySQL 5.7 から 8.0 に更新した話
普段は MySQL 5.7 のコンテナを使っていたけど、SUBQUERY のパフォーマンス比較の為に MySQL 8.0 に手元の環境を更新したかったのでこれを機に更新してみた。
例によっていつも docker コンテナで MySQL を使っているが、単にバージョンをあげただけではすんなりと使えなくて困ってしまったのでちょっとだけ紹介したい。
MySQL 8.0 では認証プラグインが変更されている
いつもどおり次のような docker-compose.yaml を書いて雑に使おうとすると次のようなメッセージが出力される。
$ mysql -u <ユーザ> -p<パスワード> -h <ホスト> mysql: [Warning] Using a password on the command line interface can be insecure. ERROR 2059 (HY000): Authentication plugin 'caching_sha2_password' cannot be loaded: /usr/lib64/mysql/plugin/caching_sha2_password.so: cannot open shared object file: No such file or directory
認証プライグインが mysql_native_password でなく caching_sha2_password になっている為にログインできない問題が発生する。
正確には Docker コンテナとして MySQL 8.0 を扱う際にプラグインがロードできていないっぽいのだけどこれの対策にやたら時間がかかってしまった。
結論から言えば、一時的な応急処置として my.cnf にデフォルトで mysql_native_password を使用すると設定を追記しただけなんだけども
なんかもやっとしている。
そのうちそのあたりの内容も記事にしたい。
とりあえず今は応急処置。
SubQuery
サブクエリは、テーブルを加工したりビューにして使い回すわけでも無い時に一時的にテーブルを生成することができるもののことを指す。
例題としてここではサンプルデータにおいて、映画の長さが120分以上で映画カテゴリーが1(種類的には"Action")のものの個数を表示することを考える。
SELECT title FROM film WHERE film.length > 120 AND film.film_id IN ( SELECT film_id FROM film_category WHERE category_id = 1 );
出力結果↓
MySQL [sakila]> SELECT title FROM film WHERE film.length > 120 AND film.film_id IN (SELECT film_id FROM film_category WHERE category_id = 1); +-------------------------+ | title | +-------------------------+ | AMERICAN CIRCUS | | ANTITRUST TOMATOES | | BAREFOOT MANCHURIAN | | BULL SHAWSHANK | | CAMPUS REMEMBER | | CASUALTIES ENCINO | | DARKO DORADO | | DARN FORRESTER | | DRAGON SQUAD | | DREAM PICKUP | | EASY GLADIATOR | | ENTRAPMENT SATISFACTION | | FOOL MOCKINGBIRD | | GOSFORD DONNIE | | KISSING DOLLS | | LAWRENCE LOVE | | MAGNOLIA FORRESTER | | MINDS TRUMAN | | MONTEZUMA COMMAND | | QUEST MUSSOLINI | | SHRUNK DIVINE | | SKY MIRACLE | | SOUTH WAIT | | SPEAKEASY DATE | | STORY SIDE | | UPRISING UPTOWN | | WOMEN DORADO | | WORST BANGER | +-------------------------+
いい感じなんじゃないか。
サブクエリを使うことで一時的にテーブルを作成することが出来るため
だたこの場合、実行計画を確認してみたところ不可解な点があった。
MySQL [sakila]> EXPLAIN SELECT title FROM film WHERE film.length > 120 AND film.film_id IN (SELECT film_id FROM film_category WHERE category_id = 1); +----+-------------+---------------+------------+--------+-----------------------------------+---------------------------+---------+------------------------------+------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+---------------+------------+--------+-----------------------------------+---------------------------+---------+------------------------------+------+----------+-------------+ | 1 | SIMPLE | film_category | NULL | ref | PRIMARY,fk_film_category_category | fk_film_category_category | 1 | const | 64 | 100.00 | Using index | | 1 | SIMPLE | film | NULL | eq_ref | PRIMARY | PRIMARY | 2 | sakila.film_category.film_id | 1 | 33.33 | Using where | +----+-------------+---------------+------------+--------+-----------------------------------+---------------------------+---------+------------------------------+------+----------+-------------+ 2 rows in set, 1 warning (0.00 sec)
なぜ、サブクエリを明示的に使っているのに select_type が SIMPLE になっているんだ・・・。
そこで SHOW WARNINGS を使って実行計画の追加情報を取ってみた。(MySQL5.7以下は EXPLAIN EXTENDED で更に詳細な実行計画を出したあとに行う必要があるっぽい？)
MySQL [sakila]> SHOW WARNINGS; +-------+------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Level | Code | Message | +-------+------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Note | 1003 | /* select#1 */ select `sakila`.`film`.`title` AS `title` from `sakila`.`film_category` join `sakila`.`film` where ((`sakila`.`film`.`film_id` = `sakila`.`film_category`.`film_id`) and (`sakila`.`film_category`.`category_id` = 1) and (`sakila`.`film`.`length` > 120)) | +-------+------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec)
めちゃくちゃ最適化されていた。
バッチリ JOIN されていることからおそらくサブクエリを使うまでしなくてもいい SQL を書いてしまっていたみたい。
といってもサブクエリは外部のクエリに対して依存していなしサブクエリで完結してしまっているからまぁ当然っちゃ当然かもしれない。
ここでなぜ JOIN で最適化されているかというと、それは サブクエリが大抵の場合 JOIN で代替することができるからというのがこのあとの話。
JOIN
複数のテーブルの情報を使う場合はサブクエリだけでなく JOIN を使うこともできる。
JOIN は機能の本質的にはサブクエリに似ているところもあるが具体的には条件に応じてテーブルを結合することができる。
例えば、category 1,2 (具体的にはアクションとアニメーション)が設定されている映画だけサブクエリで無理やり書くならば次のようになる。
(MySQL 5.5 以下なら相関サブクエリ扱いでスロークエリになるはず)
SELECT title FROM film WHERE EXISTS ( SELECT film_id FROM film_category WHERE film_category.category_id < 3 AND film.film_id = film_category.film_id );
そしてこれを JOIN で置き換えると次のようになる。
SELECT title FROM film JOIN film_category ON film.film_id = film_category.film_id AND category_id < 3;
WHERE 句でなく AND で接続してもよい。
JOIN は次に紹介する INNER JOIN で代替が効く場合がおおいため単にあまり使わない。
また JOIN 挙動をわかりやすく説明するためには、 film テーブルを使うとすごく説明が大変なので
film_category と category テーブルを用いて説明する。
それぞれテーブルの先頭10レコードは以下のようになっている。
MySQL [sakila]> select * from film_category limit 10; +---------+-------------+---------------------+ | film_id | category_id | last_update | +---------+-------------+---------------------+ | 1 | 6 | 2006-02-15 05:07:09 | | 2 | 11 | 2006-02-15 05:07:09 | | 3 | 6 | 2006-02-15 05:07:09 | | 4 | 11 | 2006-02-15 05:07:09 | | 5 | 8 | 2006-02-15 05:07:09 | | 6 | 9 | 2006-02-15 05:07:09 | | 7 | 5 | 2006-02-15 05:07:09 | | 8 | 11 | 2006-02-15 05:07:09 | | 9 | 11 | 2006-02-15 05:07:09 | | 10 | 15 | 2006-02-15 05:07:09 | +---------+-------------+---------------------+ 10 rows in set (0.00 sec) MySQL [sakila]> select * from category limit 10; +-------------+-------------+---------------------+ | category_id | name | last_update | +-------------+-------------+---------------------+ | 1 | Action | 2006-02-15 04:46:27 | | 2 | Animation | 2006-02-15 04:46:27 | | 3 | Children | 2006-02-15 04:46:27 | | 4 | Classics | 2006-02-15 04:46:27 | | 5 | Comedy | 2006-02-15 04:46:27 | | 6 | Documentary | 2006-02-15 04:46:27 | | 7 | Drama | 2006-02-15 04:46:27 | | 8 | Family | 2006-02-15 04:46:27 | | 9 | Foreign | 2006-02-15 04:46:27 | | 10 | Games | 2006-02-15 04:46:27 | +-------------+-------------+---------------------+ 10 rows in set (0.01 sec)
これらを JOIN して先頭 20 レコードを取得すると次のようになっている。
MySQL [sakila]> select * from film_category join category limit 20; +---------+-------------+---------------------+-------------+-------------+---------------------+ | film_id | category_id | last_update | category_id | name | last_update | +---------+-------------+---------------------+-------------+-------------+---------------------+ | 1 | 6 | 2006-02-15 05:07:09 | 1 | Action | 2006-02-15 04:46:27 | | 1 | 6 | 2006-02-15 05:07:09 | 2 | Animation | 2006-02-15 04:46:27 | | 1 | 6 | 2006-02-15 05:07:09 | 3 | Children | 2006-02-15 04:46:27 | | 1 | 6 | 2006-02-15 05:07:09 | 4 | Classics | 2006-02-15 04:46:27 | | 1 | 6 | 2006-02-15 05:07:09 | 5 | Comedy | 2006-02-15 04:46:27 | | 1 | 6 | 2006-02-15 05:07:09 | 6 | Documentary | 2006-02-15 04:46:27 | | 1 | 6 | 2006-02-15 05:07:09 | 7 | Drama | 2006-02-15 04:46:27 | | 1 | 6 | 2006-02-15 05:07:09 | 8 | Family | 2006-02-15 04:46:27 | | 1 | 6 | 2006-02-15 05:07:09 | 9 | Foreign | 2006-02-15 04:46:27 | | 1 | 6 | 2006-02-15 05:07:09 | 10 | Games | 2006-02-15 04:46:27 | | 1 | 6 | 2006-02-15 05:07:09 | 11 | Horror | 2006-02-15 04:46:27 | | 1 | 6 | 2006-02-15 05:07:09 | 12 | Music | 2006-02-15 04:46:27 | | 1 | 6 | 2006-02-15 05:07:09 | 13 | New | 2006-02-15 04:46:27 | | 1 | 6 | 2006-02-15 05:07:09 | 14 | Sci-Fi | 2006-02-15 04:46:27 | | 1 | 6 | 2006-02-15 05:07:09 | 15 | Sports | 2006-02-15 04:46:27 | | 1 | 6 | 2006-02-15 05:07:09 | 16 | Travel | 2006-02-15 04:46:27 | | 2 | 11 | 2006-02-15 05:07:09 | 1 | Action | 2006-02-15 04:46:27 | | 2 | 11 | 2006-02-15 05:07:09 | 2 | Animation | 2006-02-15 04:46:27 | | 2 | 11 | 2006-02-15 05:07:09 | 3 | Children | 2006-02-15 04:46:27 | | 2 | 11 | 2006-02-15 05:07:09 | 4 | Classics | 2006-02-15 04:46:27 | +---------+-------------+---------------------+-------------+-------------+---------------------+ 20 rows in set (0.00 sec)
何がおきているかというと、 film_category の 1 レコード目に対して category が全て結合されている。
結果が肥大化しやすいからあまり使いたくない感じがすごい。
ON 句を使って条件を設定してやればカテゴリ別に結合することもできる。
MySQL [sakila]> select * from film_category join category on film_category.category_id = category.category_id limit 20; +---------+-------------+---------------------+-------------+--------+---------------------+ | film_id | category_id | last_update | category_id | name | last_update | +---------+-------------+---------------------+-------------+--------+---------------------+ | 19 | 1 | 2006-02-15 05:07:09 | 1 | Action | 2006-02-15 04:46:27 | | 21 | 1 | 2006-02-15 05:07:09 | 1 | Action | 2006-02-15 04:46:27 | | 29 | 1 | 2006-02-15 05:07:09 | 1 | Action | 2006-02-15 04:46:27 | | 38 | 1 | 2006-02-15 05:07:09 | 1 | Action | 2006-02-15 04:46:27 | | 56 | 1 | 2006-02-15 05:07:09 | 1 | Action | 2006-02-15 04:46:27 | | 67 | 1 | 2006-02-15 05:07:09 | 1 | Action | 2006-02-15 04:46:27 | | 97 | 1 | 2006-02-15 05:07:09 | 1 | Action | 2006-02-15 04:46:27 | | 105 | 1 | 2006-02-15 05:07:09 | 1 | Action | 2006-02-15 04:46:27 | | 111 | 1 | 2006-02-15 05:07:09 | 1 | Action | 2006-02-15 04:46:27 | | 115 | 1 | 2006-02-15 05:07:09 | 1 | Action | 2006-02-15 04:46:27 | | 126 | 1 | 2006-02-15 05:07:09 | 1 | Action | 2006-02-15 04:46:27 | | 130 | 1 | 2006-02-15 05:07:09 | 1 | Action | 2006-02-15 04:46:27 | | 162 | 1 | 2006-02-15 05:07:09 | 1 | Action | 2006-02-15 04:46:27 | | 194 | 1 | 2006-02-15 05:07:09 | 1 | Action | 2006-02-15 04:46:27 | | 205 | 1 | 2006-02-15 05:07:09 | 1 | Action | 2006-02-15 04:46:27 | | 210 | 1 | 2006-02-15 05:07:09 | 1 | Action | 2006-02-15 04:46:27 | | 212 | 1 | 2006-02-15 05:07:09 | 1 | Action | 2006-02-15 04:46:27 | | 229 | 1 | 2006-02-15 05:07:09 | 1 | Action | 2006-02-15 04:46:27 | | 250 | 1 | 2006-02-15 05:07:09 | 1 | Action | 2006-02-15 04:46:27 | | 252 | 1 | 2006-02-15 05:07:09 | 1 | Action | 2006-02-15 04:46:27 | +---------+-------------+---------------------+-------------+--------+---------------------+ 20 rows in set (0.00 sec)
INNER JOIN
いわゆる内部結合と言われるもの。
サンプルデータベースの構造上の特性で inner join に変更しても条件付き join と実行結果が変わらない。
MySQL [sakila]> select * from film_category inner join category on film_category.category_id = category.category_id limit 20; +---------+-------------+---------------------+-------------+--------+---------------------+ | film_id | category_id | last_update | category_id | name | last_update | +---------+-------------+---------------------+-------------+--------+---------------------+ | 19 | 1 | 2006-02-15 05:07:09 | 1 | Action | 2006-02-15 04:46:27 | | 21 | 1 | 2006-02-15 05:07:09 | 1 | Action | 2006-02-15 04:46:27 | | 29 | 1 | 2006-02-15 05:07:09 | 1 | Action | 2006-02-15 04:46:27 | | 38 | 1 | 2006-02-15 05:07:09 | 1 | Action | 2006-02-15 04:46:27 | | 56 | 1 | 2006-02-15 05:07:09 | 1 | Action | 2006-02-15 04:46:27 | | 67 | 1 | 2006-02-15 05:07:09 | 1 | Action | 2006-02-15 04:46:27 | | 97 | 1 | 2006-02-15 05:07:09 | 1 | Action | 2006-02-15 04:46:27 | | 105 | 1 | 2006-02-15 05:07:09 | 1 | Action | 2006-02-15 04:46:27 | | 111 | 1 | 2006-02-15 05:07:09 | 1 | Action | 2006-02-15 04:46:27 | | 115 | 1 | 2006-02-15 05:07:09 | 1 | Action | 2006-02-15 04:46:27 | | 126 | 1 | 2006-02-15 05:07:09 | 1 | Action | 2006-02-15 04:46:27 | | 130 | 1 | 2006-02-15 05:07:09 | 1 | Action | 2006-02-15 04:46:27 | | 162 | 1 | 2006-02-15 05:07:09 | 1 | Action | 2006-02-15 04:46:27 | | 194 | 1 | 2006-02-15 05:07:09 | 1 | Action | 2006-02-15 04:46:27 | | 205 | 1 | 2006-02-15 05:07:09 | 1 | Action | 2006-02-15 04:46:27 | | 210 | 1 | 2006-02-15 05:07:09 | 1 | Action | 2006-02-15 04:46:27 | | 212 | 1 | 2006-02-15 05:07:09 | 1 | Action | 2006-02-15 04:46:27 | | 229 | 1 | 2006-02-15 05:07:09 | 1 | Action | 2006-02-15 04:46:27 | | 250 | 1 | 2006-02-15 05:07:09 | 1 | Action | 2006-02-15 04:46:27 | | 252 | 1 | 2006-02-15 05:07:09 | 1 | Action | 2006-02-15 04:46:27 | +---------+-------------+---------------------+-------------+--------+---------------------+ 20 rows in set (0.00 sec)
例題がよくなくてめちゃくちゃ説明しにくいので以下のサイトでも見てくれると助かる。
INNER JOIN はカラムを比較して同じ値を持つレコード同士を結合している。
OUTER JOIN
外部結合と言われるOUTER JOIN には LEFT OUTER JOIN と RIGHT OUTER JOIN の２種類がある。
INNER JOIN とは性質がことなり、OUTER JOIN はカラムが一致しなくても結合する。
LEFT OUTER JOIN
OUTER JOIN で左側のテーブルを起点に結合するため、以下のようになる。
MySQL [sakila]> select * from film_category left outer join category on film_category.category_id = category.category_id limit 20; +---------+-------------+---------------------+-------------+-------------+---------------------+ | film_id | category_id | last_update | category_id | name | last_update | +---------+-------------+---------------------+-------------+-------------+---------------------+ | 1 | 6 | 2006-02-15 05:07:09 | 6 | Documentary | 2006-02-15 04:46:27 | | 2 | 11 | 2006-02-15 05:07:09 | 11 | Horror | 2006-02-15 04:46:27 | | 3 | 6 | 2006-02-15 05:07:09 | 6 | Documentary | 2006-02-15 04:46:27 | | 4 | 11 | 2006-02-15 05:07:09 | 11 | Horror | 2006-02-15 04:46:27 | | 5 | 8 | 2006-02-15 05:07:09 | 8 | Family | 2006-02-15 04:46:27 | | 6 | 9 | 2006-02-15 05:07:09 | 9 | Foreign | 2006-02-15 04:46:27 | | 7 | 5 | 2006-02-15 05:07:09 | 5 | Comedy | 2006-02-15 04:46:27 | | 8 | 11 | 2006-02-15 05:07:09 | 11 | Horror | 2006-02-15 04:46:27 | | 9 | 11 | 2006-02-15 05:07:09 | 11 | Horror | 2006-02-15 04:46:27 | | 10 | 15 | 2006-02-15 05:07:09 | 15 | Sports | 2006-02-15 04:46:27 | | 11 | 9 | 2006-02-15 05:07:09 | 9 | Foreign | 2006-02-15 04:46:27 | | 12 | 12 | 2006-02-15 05:07:09 | 12 | Music | 2006-02-15 04:46:27 | | 13 | 11 | 2006-02-15 05:07:09 | 11 | Horror | 2006-02-15 04:46:27 | | 14 | 4 | 2006-02-15 05:07:09 | 4 | Classics | 2006-02-15 04:46:27 | | 15 | 9 | 2006-02-15 05:07:09 | 9 | Foreign | 2006-02-15 04:46:27 | | 16 | 9 | 2006-02-15 05:07:09 | 9 | Foreign | 2006-02-15 04:46:27 | | 17 | 12 | 2006-02-15 05:07:09 | 12 | Music | 2006-02-15 04:46:27 | | 18 | 2 | 2006-02-15 05:07:09 | 2 | Animation | 2006-02-15 04:46:27 | | 19 | 1 | 2006-02-15 05:07:09 | 1 | Action | 2006-02-15 04:46:27 | | 20 | 12 | 2006-02-15 05:07:09 | 12 | Music | 2006-02-15 04:46:27 | +---------+-------------+---------------------+-------------+-------------+---------------------+ 20 rows in set (0.00 sec)
こうすれば、film_id 別にソートされた状態にすることができたりする。
RIGHT OUTER JOIN
もう予想がつくと思うが右のテーブルを起点に外部結合する。
MySQL [sakila]> select * from film_category right outer join category on film_category.category_id = category.category_id limit 20; +---------+-------------+---------------------+-------------+--------+---------------------+ | film_id | category_id | last_update | category_id | name | last_update | +---------+-------------+---------------------+-------------+--------+---------------------+ | 19 | 1 | 2006-02-15 05:07:09 | 1 | Action | 2006-02-15 04:46:27 | | 21 | 1 | 2006-02-15 05:07:09 | 1 | Action | 2006-02-15 04:46:27 | | 29 | 1 | 2006-02-15 05:07:09 | 1 | Action | 2006-02-15 04:46:27 | | 38 | 1 | 2006-02-15 05:07:09 | 1 | Action | 2006-02-15 04:46:27 | | 56 | 1 | 2006-02-15 05:07:09 | 1 | Action | 2006-02-15 04:46:27 | | 67 | 1 | 2006-02-15 05:07:09 | 1 | Action | 2006-02-15 04:46:27 | | 97 | 1 | 2006-02-15 05:07:09 | 1 | Action | 2006-02-15 04:46:27 | | 105 | 1 | 2006-02-15 05:07:09 | 1 | Action | 2006-02-15 04:46:27 | | 111 | 1 | 2006-02-15 05:07:09 | 1 | Action | 2006-02-15 04:46:27 | | 115 | 1 | 2006-02-15 05:07:09 | 1 | Action | 2006-02-15 04:46:27 | | 126 | 1 | 2006-02-15 05:07:09 | 1 | Action | 2006-02-15 04:46:27 | | 130 | 1 | 2006-02-15 05:07:09 | 1 | Action | 2006-02-15 04:46:27 | | 162 | 1 | 2006-02-15 05:07:09 | 1 | Action | 2006-02-15 04:46:27 | | 194 | 1 | 2006-02-15 05:07:09 | 1 | Action | 2006-02-15 04:46:27 | | 205 | 1 | 2006-02-15 05:07:09 | 1 | Action | 2006-02-15 04:46:27 | | 210 | 1 | 2006-02-15 05:07:09 | 1 | Action | 2006-02-15 04:46:27 | | 212 | 1 | 2006-02-15 05:07:09 | 1 | Action | 2006-02-15 04:46:27 | | 229 | 1 | 2006-02-15 05:07:09 | 1 | Action | 2006-02-15 04:46:27 | | 250 | 1 | 2006-02-15 05:07:09 | 1 | Action | 2006-02-15 04:46:27 | | 252 | 1 | 2006-02-15 05:07:09 | 1 | Action | 2006-02-15 04:46:27 | +---------+-------------+---------------------+-------------+--------+---------------------+ 20 rows in set (0.00 sec)
こうすると見辛いがカテゴリ別にソートされた状態で出力することができる。
余談
データが優秀すぎて INNER JOIN と RIGHT JOIN の違いがハイパー説明しにくいので参考文献というなのわかりやすい資料を残しておく。
qiita.com
