MySQL のサブクエリって、ほんとに遅いの？

データベース

みかみ

2017.06.03

この記事は公開されてから1年以上経過しています。情報が古い可能性がありますので、ご注意ください。

こんにちは、みかみです。

今日のクラスメソッドのAWSおみくじ、RDS（t1.micro）でしたー！（昨日は Aurora！＠＠ｖ

はじめに

前職＠アプリ開発時、MySQL のクエリチューニングをさせていただく機会がありました。

ユーザー入力のキーワードで全文検索 → 見つかったレコードを返すのですが、所要時間、約3分。。

Apache タイムアウトして画面真っ白。。。（泣きそうでした＞＜。

EXPLAINで実行計画を調べた際に、select_typeにはクエリの種類が表示されるのだが、代表的なサブクエリには次の3つのパターンがある。
SUBQUERY
DEPENDENT SUBQUERY
DERIVED
結論から言おう。遅いのは2番目、DEPENDENT SUBQUERYである。DEPENDENT SUBQUERYとはいわゆる相関サブクエリに相当するもので、サブクエリにおいて外部クエリのカラムを参照しているサブクエリのことである。そして、MySQLのオプティマイザはたびたび相関関係のないもの、つまり本来はSUBQUERYと分類されるべきものをDEPENDENT SUBQUERYであると判断してしまう。そのため、多くの場合においてサブクエリが遅くなってしまうのである。

引用：なぜMySQLのサブクエリは遅いのか。｜漢(オトコ)のコンピュータ道

DEPENDENT SUBQUERY ＝トラウマ。。。（こちらのブログ、当時、私のバイブルでしたｖ

そして、現在のデータ分析系のおしごとさせていただくようになってから読んだ本のコラムで

「サブクエリが遅いのは MySQL だけ！」

と、明言されているのを見ました。。。（その本は、PostgreSQL ベースで書かれてました。

やりたいこと

MySQL のサブクエリはほんとに遅いのか確かめてみたい。

確認環境

OS：Windows10（Mac VMware Fusion）
MySQL（local）：5.6.26 / 5.5.39
PostgreSQL（local）：9.6.1

やってみた

データと確認用クエリを準備

Sakila DB

MySQL のサンプル DB、Sakila Sample Database を使います。

MySQL ロゴのイルカさん、お名前をSakila さんとおっしゃるそうで（なので sakila-db なのですねv

いただいてきた sakila-schema.sql と sakila-data.sql を実行すると、sakila DB とテーブル一式、サンプルデータがロードできました。

mysql> select table_name, table_rows from information_schema.TABLES where table_schema = 'sakila';+----------------------------+------------+| table_name                 | table_rows |+----------------------------+------------+| actor                      |        200 || actor_info                 |       NULL || address                    |        603 || category                   |         16 || city                       |        600 || country                    |        109 || customer                   |        599 || customer_list              |       NULL || film                       |       1000 || film_actor                 |       5462 || film_category              |       1000 || film_list                  |       NULL || film_text                  |       1000 || inventory                  |       4581 || language                   |          6 || nicer_but_slower_film_list |       NULL || payment                    |      16086 || rental                     |      16005 || sales_by_film_category     |       NULL || sales_by_store             |       NULL || staff                      |          2 || staff_list                 |       NULL || store                      |          2 |+----------------------------+------------+23 rows in set (0.01 sec)

この Skila DB、国→映画→レンタルビデオ（DVD？のサンプルデータが入ってます。

今回は、顧客マスタ（customer）と支払い情報テーブル（payment）を使い、サブクエリを使った SQL を投げてみます。

オリジナルの顧客マスタはレコード数が少ないので、実行時間をわかりやすくするために、レコードを増やします。

customer と同じ定義の customer_2 テーブルを create して、customer_id カラムの型を BIGINT に変更（smallint だとオーバーフローしちゃうので。

mysql> create table customer_2 like customer;Query OK, 0 rows affected (0.01 sec)mysql> alter table customer_2 modify customer_id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT;Query OK, 0 rows affected (0.05 sec)Records: 0  Duplicates: 0  Warnings: 0

customer から select したレコードを customer_2 に insert したら、ID だけインクリメント（AUTO_INCREMENT）してレコード数を増やします。

mysql> insert into customer_2 (select * from customer);Query OK, 599 rows affected (0.00 sec)Records: 599  Duplicates: 0  Warnings: 0mysql> insert into customer_2 (store_id, first_name, last_name, email, address_id, active, create_date, last_update) (select store_id, first_name, last_name, email, address_id, active, create_date, last_update from customer_2);Query OK, 599 rows affected (0.01 sec)Records: 599  Duplicates: 0  Warnings: 0

何度か insert して、120万件ほどに増やしました。

mysql> (select 'customer_2' as 'table', (select count(*) from customer_2) as records) union (select 'payment' as 'table', (select count(*) from payment) as records);+------------+---------+| table      | records |+------------+---------+| customer_2 | 1226752 || payment    |   16049 |+------------+---------+2 rows in set (0.39 sec)

テーブルには、こんなデータが入っています。

mysql> select * from customer_2 limit 3;+-------------+----------+------------+-----------+-------------------------------------+------------+--------+---------------------+---------------------+| customer_id | store_id | first_name | last_name | email                               | address_id | active | create_date         | last_update         |+-------------+----------+------------+-----------+-------------------------------------+------------+--------+---------------------+---------------------+|           1 |        1 | MARY       | SMITH     | MARY.SMITH@sakilacustomer.org       |          5 |      1 | 2006-02-14 22:04:36 | 2006-02-15 04:57:20 ||           2 |        1 | PATRICIA   | JOHNSON   | PATRICIA.JOHNSON@sakilacustomer.org |          6 |      1 | 2006-02-14 22:04:36 | 2006-02-15 04:57:20 ||           3 |        1 | LINDA      | WILLIAMS  | LINDA.WILLIAMS@sakilacustomer.org   |          7 |      1 | 2006-02-14 22:04:36 | 2006-02-15 04:57:20 |+-------------+----------+------------+-----------+-------------------------------------+------------+--------+---------------------+---------------------+3 rows in set (0.00 sec)mysql> select * from payment limit 3;+------------+-------------+----------+-----------+--------+---------------------+---------------------+| payment_id | customer_id | staff_id | rental_id | amount | payment_date        | last_update         |+------------+-------------+----------+-----------+--------+---------------------+---------------------+|          1 |           1 |        1 |        76 |   2.99 | 2005-05-25 11:30:37 | 2006-02-15 22:12:30 ||          2 |           1 |        1 |       573 |   0.99 | 2005-05-28 10:35:23 | 2006-02-15 22:12:30 ||          3 |           1 |        1 |      1185 |   5.99 | 2005-06-15 00:54:12 | 2006-02-15 22:12:30 |+------------+-------------+----------+-----------+--------+---------------------+---------------------+3 rows in set (0.00 sec)

確認用クエリ

準備した確認用クエリはこちら。

総額 $100 超ご利用いただいた顧客数をカウントします。

select  count(*)from  customer_2where  customer_id in(    select      s.customer_id    from (      select        customer_id,        sum(amount) as total      from        payment      group by customer_id) s    where      s.total > 100  );

payment から顧客ごとの支払い総額を出して、総額 $100 以上の顧客ID を抽出します。

payment から select した customer_id を、一つ一つ customer_2 の customer_id と比較する相関サブクエリとして処理される、はず。

MySQL 5.6 で確認

MySQL の Version は、5.6.26（XAMPP 5.6.12 でインストール）です。

確認用クエリを実行します。

mysql> select count(*) from customer_2 where customer_id in(select s.customer_id from (select customer_id, sum(amount) as total from payment group by customer_id) s where s.total > 100);+----------+| count(*) |+----------+|      395 |+----------+1 row in set (0.03 sec)

実行時間、30ms ？！

あれ？速い！（もっと遅いと思ってたのに。。＠＠？！

実行計画確認します。

mysql> explain select count(*) from customer_2 where customer_id in(select s.customer_id from (select customer_id, sum(amount) as total from payment group by customer_id) s where s.total > 100);+----+--------------+-------------+--------+--------------------+--------------------+---------+-------------------------+-------+--------------------------+| id | select_type  | table       | type   | possible_keys      | key                | key_len | ref                     | rows  | Extra                    |+----+--------------+-------------+--------+--------------------+--------------------+---------+-------------------------+-------+--------------------------+|  1 | PRIMARY      | <subquery2> | ALL    | NULL               | NULL               | NULL    | NULL                    |  NULL | NULL                     ||  1 | PRIMARY      | customer_2  | eq_ref | PRIMARY            | PRIMARY            | 8       | <subquery2>.customer_id |     1 | Using where; Using index ||  2 | MATERIALIZED | <derived3>  | ALL    | NULL               | NULL               | NULL    | NULL                    | 16086 | Using where              ||  3 | DERIVED      | payment     | index  | idx_fk_customer_id | idx_fk_customer_id | 2       | NULL                    | 16086 | NULL                     |+----+--------------+-------------+--------+--------------------+--------------------+---------+-------------------------+-------+--------------------------+4 rows in set (0.00 sec)

なんと。。

select_type に、相関サブクエリを表すDEPENDENT SUBQUERY （トラウマ）が出てきません。。

そして、MATERIALIZED って、なんですか？（あせ

eq_refって、JOIN するときの type では？（ちゃんとプライマリーキーで JOIN してる？！

調べてみたら、MySQL のオプティマイザ、進化してたのですね！＠＠ｖ

5.6 ならサブクエリ、そんなに遅くなくなったんだ！

どのくらい速くなったのかしら・・・？（わくわく

MySQL 5.5 で確認

MySQL 5.5.39 を XAMPP（1.8.2）でインストールして、先ほどと同じように Sakila DB を準備しました。

C:\Users\mikami.yuki>mysql -u root -ppasswordWarning: Using a password on the command line interface can be insecure.Welcome to the MySQL monitor.  Commands end with ; or \g.Your MySQL connection id is 1Server version: 5.5.39 MySQL Community Server (GPL)

mysql> (select 'customer_2' as 'table', (select count(*) from customer_2) as records) union (select 'payment' as 'table', (select count(*) from payment) as records);+------------+---------+| table      | records |+------------+---------+| customer_2 | 1226752 || payment    |   16049 |+------------+---------+2 rows in set (0.39 sec)

実行してみると・・・

mysql> select count(*) from customer_2 where customer_id in(select s.customer_id from (select customer_id, sum(amount) as total from payment group by customer_id) s where s.total > 100);+----------+| count(*) |+----------+|      395 |+----------+1 row in set (1 min 21.34 sec)

実行時間、約 1分20秒。。（ですよねー。。。

実行計画取ってみます。

mysql> explain select count(*) from customer_2 where customer_id in(select s.customer_id from (select customer_id, sum(amount) as total from payment group by customer_id) s where s.total > 100);+----+--------------------+------------+-------+---------------+--------------------+---------+------+---------+--------------------------+| id | select_type        | table      | type  | possible_keys | key                | key_len | ref  | rows    | Extra                    |+----+--------------------+------------+-------+---------------+--------------------+---------+------+---------+--------------------------+|  1 | PRIMARY            | customer_2 | index | NULL          | idx_fk_store_id    | 1       | NULL | 1226888 | Using where; Using index ||  2 | DEPENDENT SUBQUERY | <derived3> | ALL   | NULL          | NULL               | NULL    | NULL |     599 | Using where              ||  3 | DERIVED            | payment    | index | NULL          | idx_fk_customer_id | 2       | NULL |   16451 |                          |+----+--------------------+------------+-------+---------------+--------------------+---------+------+---------+--------------------------+3 rows in set (0.05 sec)

でました、DEPENDENT SUBQUERY。。（あわあわ・・

customer_2 の 120万件に対して、フルインデックススキャンかかってます。。

key にも関係ないカラム（store_id）が選択されてたり。。。

ちなみに、実行するクエリを、IN 句ではなく JOIN を使うように変えてみると・・・

mysql> select count(*) from customer_2 c inner join (select s.customer_id from (select customer_id, sum(amount) as total from payment group by customer_id) s where s.total > 100) p on c.customer_id = p.customer_id;+----------+| count(*) |+----------+|      395 |+----------+1 row in set (0.01 sec)

実行時間、約 10ミリ秒（ですよねー。。。

当然、実行計画も変わりました。

mysql> explain select count(*) from customer_2 c inner join (select s.customer_id from (select customer_id, sum(amount) as total from payment group by customer_id) s where s.total > 100) p on c.customer_id = p.customer_id;+----+-------------+------------+--------+---------------+--------------------+---------+---------------+-------+--------------------------+| id | select_type | table      | type   | possible_keys | key                | key_len | ref           | rows  | Extra                    |+----+-------------+------------+--------+---------------+--------------------+---------+---------------+-------+--------------------------+|  1 | PRIMARY     | <derived2> | ALL    | NULL          | NULL               | NULL    | NULL          |   395 |                          ||  1 | PRIMARY     | c          | eq_ref | PRIMARY       | PRIMARY            | 8       | p.customer_id |     1 | Using where; Using index ||  2 | DERIVED     | <derived3> | ALL    | NULL          | NULL               | NULL    | NULL          |   599 | Using where              ||  3 | DERIVED     | payment    | index  | NULL          | idx_fk_customer_id | 2       | NULL          | 15422 |                          |+----+-------------+------------+--------+---------------+--------------------+---------+---------------+-------+--------------------------+4 rows in set (0.03 sec)

対象レコード数の差こそあるものの、これって、MySQL 5.6 の IN 句クエリの実行計画とほぼ同じ？！（やっぱり 5.6 ではオプティマイザが JOIN してくれてる＠＠！

PostgreSQL で確認

同じクエリを、PostgreSQL（9.6.1）でも確認してみます。

Sakila DB の DDL はそのまま使えないので、必要なテーブルだけ修正してデータを insert しました。

postgres=# (select 'customer_2' as table, (select count(*) from customer_2) as records) union (select 'payment' as table, (select count(*) from payment) as records);   table    | records------------+--------- customer_2 | 1226752 payment    |   16049(2 行)postgres=# \timingタイミングは on です。postgres=# select count(*) from customer_2 where customer_id in(select s.customer_id from (select customer_id, sum(amount) as total from payment group by customer_id) s where s.total > 100); count-------   395(1 行)時間: 12.069 ms

さすがポスグレ、 12 ms！

実行計画見てみると・・・

postgres=# explain select count(*) from customer_2 where customer_id in(select s.customer_id from (select customer_id, sum(amount) as total from payment group by customer_id) s where s.total > 100);                                            QUERY PLAN--------------------------------------------------------------------------------------------------- Aggregate  (cost=6914.32..6914.33 rows=1 width=8)   ->  Nested Loop  (cost=375.16..5380.88 rows=613376 width=0)         ->  HashAggregate  (cost=374.74..382.22 rows=599 width=34)               Group Key: payment.customer_id               Filter: (sum(payment.amount) > '100'::numeric)               ->  Seq Scan on payment  (cost=0.00..294.49 rows=16049 width=8)         ->  Index Only Scan using customer_2_pkey on customer_2  (cost=0.43..8.32 rows=1 width=8)               Index Cond: (customer_id = payment.customer_id)(8 行)

payment テーブルにフルスキャン（ Seq Scan ）かけてはいるものの、ちゃんと Nested Loop で結合使ってくれてます。