注目の記事

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: 0

mysql> 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_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
  );

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 -ppassword
Warning: 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 1
Server 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 で結合使ってくれてます。

実行時間比較

下表、120万件レコードに IN 句のクエリを投げた実行時間の比較です。

※コンソールからDB接続 → クエリを5回実行して、コンソールに表示された実行時間の平均値を出しました。

MySQL 5.5	MySQL 5.6	PostgreSQL 9.6
89.346 sec	0.08 sec	8.861 ms

※注：MySQL は秒単位、PostgreSQLはミリ秒単位です。。

やっぱ、PostgreSQL の方が速いですね。。（5.6 と比較しても、約10倍。。

でも MySQL も、5.6 以降ならば、そんなに遅くないんでない？！＠＠ｖ

おわりに（まとめ・所感）

MySQL 5.5 まで、サブクエリはやっぱり遅い。。
MySQL 5.6 からはそんなに遅くなくなった！
MySQL とはいっても、バージョンによって全然違う！
オプティマイザは進化してる！
DBのアーキテクチャって奥が深いｖ

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

はじめに

やりたいこと

確認環境

やってみた

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

Sakila DB

確認用クエリ

MySQL 5.6 で確認

MySQL 5.5 で確認

PostgreSQL で確認

実行時間比較

おわりに（まとめ・所感）

参考

イベント

EVENT【4/23リモート】クラスメソッドのフリーランスエンジニア会社説明会〜AWS / システムアーキテクト案件特集〜を開催します

EVENT【5/28（火）】AWSを最大活用するための1dayカンファレンス

EVENT【5/17（金）】認証機能の開発工数削減をデモで体験！次世代認証基盤サービス『Auth0 by Okta』導入実践ウェビナー

EVENT【5/14（火）】アノテーションの中途向けオンライン会社説明会を開催します

EVENT【5/23（木）】ユースケースに学ぶAWS運用のノウハウ～可視化から統制まで～

EVENT【5/16（木）】Snowflakeを触ってみよう！初めての方向けハンズオンセミナー

EVENT【5/10（金）名古屋】クラスメソッドグループの会社説明会を開催します！

EVENT【4/25（木）リモート】Google Cloudに携わるエンジニアのキャリア～クラスメソッド会社説明会～

EVENT【4/30（火）リモート】クラスメソッドの会社説明会を開催します

EVENT【5/9（木）】組織的なクラウド統制はじめの一歩

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

はじめに

やりたいこと

確認環境

やってみた

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

Sakila DB

確認用クエリ

MySQL 5.6 で確認

MySQL 5.5 で確認

PostgreSQL で確認

実行時間比較

おわりに（まとめ・所感）

参考

イベント

EVENT【4/23リモート】クラスメソッドのフリーランスエンジニア会社説明会 〜AWS / システムアーキテクト案件特集〜 を開催します

EVENT【5/28（火）】AWSを最大活用するための1dayカンファレンス

EVENT【5/17（金）】認証機能の開発工数削減をデモで体験！次世代認証基盤サービス『Auth0 by Okta』導入実践ウェビナー

EVENT【5/14（火）】アノテーションの中途向けオンライン会社説明会を開催します

EVENT【5/23（木）】ユースケースに学ぶAWS運用のノウハウ～可視化から統制まで～

EVENT【5/16（木）】Snowflakeを触ってみよう！初めての方向けハンズオンセミナー

EVENT【5/10（金） 名古屋】クラスメソッドグループの会社説明会を開催します！

EVENT【4/25（木）リモート】Google Cloudに携わるエンジニアのキャリア～クラスメソッド会社説明会～

EVENT【4/30（火）リモート】クラスメソッドの会社説明会を開催します

EVENT【5/9（木）】組織的なクラウド統制 はじめの一歩

関連記事

AWSの各DBの特徴が把握できる【Planning and Designing Databases on AWS】を受講してみた

EVENT【4/23リモート】クラスメソッドのフリーランスエンジニア会社説明会〜AWS / システムアーキテクト案件特集〜を開催します

EVENT【5/10（金）名古屋】クラスメソッドグループの会社説明会を開催します！

EVENT【5/9（木）】組織的なクラウド統制はじめの一歩