MySQLとOracleの実行計画を比較してみた

まいえすきゅーえりたい　ぽすぐれない　おらくるってる（狂ってる）tomoです。

今日はいつものMySQLリファレンスを読むではなく、夏休みの宿題にしていたこれをやってみます。

MySQLとOracleDBの実行計画を比較してみた

さて同じようなテーブルで同じデータを載せて。
実行計画を取ってみた時、どのくらい情報量が違うのか簡単に違いを見てみましょう。

前提として、以下をご認識ください。

一方はOSSのDBエンジン、もう一方はガチガチ商用DBエンジンです。情報量が違うのは当たり前であって、良し悪しを比較したいのではありません。そして製品比較をしたいのではありません。いつも商用DBメインで使っているエンジニアが、OSSのDBにこうゆう情報も出してほしいな！というのをお願いしたいと思っていて、それを考える元ネタメモだと思ってください。
- OSSでこれだけの情報出せるMySQLや、今回紹介しませんがPostgreSQL等、OSSのDBは本当すごいです。OSSに関わるすべての皆様に対する賛辞を最初にお伝えしたいです。あのワンコマンドでこれだけの情報出すだなんて、すごすぎるかっこよすぎる！最高ですね！！
性能検証が目的ではありません
- HWスペックが違いますし、そもそも性能検証結果を出すのはNGです

検証対象DB
DDLと投入データ
SELECT文
MySQLの実行計画
OracleDBの実行計画
MySQLの実行計画（EXPLAIN ANALYZE）に欲しい情報
余談：OracleDBで更に詳細なSQLのコスト情報を出す

検証対象DB

MySQLはコミュニティ、バージョンはできたてほやほやの8.1.0です。
8.1おめでとう！待ってたよー！！

mysql> SELECT version();
+-----------+
| version() |
+-----------+
| 8.1.0     |
+-----------+
1 row in set (0.00 sec)

OracleDBのバージョンはエンジンがEnterprise、現時点で long support version の19シリーズの19.9です。
私の相棒ちゃんです←

TOMO@oracle > select banner_full  from v$version;

BANNER_FULL
------------------------------
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 -ProductionVersion 19.9.0.0.0

経過: 00:00:00.01

DDLと投入データ

作ったTABLEはこんな感じ。意味のないカラムとかありますけど、見なかったことにしてください…

DDLだよ

--- MySQL用
CREATE DATABASE tomo;
USE tomo;
CREATE TABLE m_cust
 (
    cust_id VARCHAR(10) NOT NULL, 
    cust_flag INT(1),
    PRIMARY KEY(cust_id)
    )
;

CREATE TABLE m_cust_flag
 (
    cust_flag INT(1) NOT NULL,
    cust_gender INT(1),
    cust_country VARCHAR(30)
 )    
;

CREATE TABLE m_cust_info
 (
    cust_id VARCHAR(10) NOT NULL,
    first_name VARCHAR(30),
    last_name VARCHAR(30),
    cust_gender INT(1),
    FOREIGN KEY(cust_id) REFERENCES m_cust(cust_id) 
 ) 
;

--- Oracle用
--- VARCHAR2を乱用するのは見本として良くないので、みなさんはちゃんとCHARとか使い分けてください
CREATE TABLE m_cust
 (
    cust_id VARCHAR2(10) NOT NULL,
    cust_flag NUMBER(1,0),
    primary key(cust_id)
 ) 
;

CREATE TABLE m_cust_flag
 (
    cust_flag NUMBER(1,0) NOT NULL,
    cust_country VARCHAR2(30)
 ) 
;

CREATE TABLE m_cust_info
 (
    cust_id VARCHAR2(10) NOT NULL,
    first_name VARCHAR2(30),
    last_name VARCHAR2(30),
    cust_gender NUMBER(1,0),
    foreign key(cust_id) references m_cust(cust_id) 
 ) 
;

データはこんなの入れました。
MySQLでもOracleDBでも同じSQLで入れられます。
（このデータに「ん？」と思った皆さんは、握手）

--- m_custにデータを入れる
insert into m_cust(cust_id,cust_flag) values('test-01', 1);
insert into m_cust(cust_id,cust_flag) values('test-02', 1);
insert into m_cust(cust_id,cust_flag) values('test-03', 1);
insert into m_cust(cust_id,cust_flag) values('test-04', 0);
insert into m_cust(cust_id,cust_flag) values('test-05', 0);
insert into m_cust(cust_id,cust_flag) values('test-06', 0);

--- m_cust_flagにデータを入れる
insert into m_cust_flag(cust_flag, cust_gender,cust_country) values(1, 0, 'Hyrule');
insert into m_cust_flag(cust_flag, cust_gender,cust_country) values(1, 1, 'Hyrule');
insert into m_cust_flag(cust_flag, cust_gender,cust_country) values(0, 0, 'past-Hyrule');
insert into m_cust_flag(cust_flag, cust_gender,cust_country) values(0, 1, 'past-Hyrule');

--- m_cust_infoにデータを入れる
insert into m_cust_info(cust_id, first_name,last_name, cust_gender) values('test-01', 'Swordsman', 'Rink', 1);
insert into m_cust_info(cust_id, first_name,last_name, cust_gender) values('test-02', 'PhD', 'Purua', 0);
insert into m_cust_info(cust_id, first_name,last_name, cust_gender) values('test-03', 'PhD', 'Robely', 1);
insert into m_cust_info(cust_id, first_name,last_name, cust_gender) values('test-04', 'Zonai', 'Raule', 1);
insert into m_cust_info(cust_id, first_name,last_name, cust_gender) values('test-05', 'Princess', 'Zelda', 0);

SELECT文

検索は以下のSELECT文にしてみました。
もうちょっと凝ったことがしたかったんですが、left joinとcase入れたから多少なにかエンジン側で工夫してくれたらいいな。
これもMySQLとOracleDB、同じSQL文で通ります。

select distinct c.cust_country as country, b.last_name as name,
  case b.cust_gender
    when 0 then 'female'
    when 1 then 'male'
    else 'seacret' end as gender
  from m_cust a
  left join m_cust_info b on a.cust_id = b.cust_id
  left join m_cust_flag c on a.cust_flag = c.cust_flag
  order by country;

MySQLの実行計画

さて、大好きな実行計画タイムです。
まずは普通のEXPLAINをしてみる。

mysql> EXPLAIN select distinct c.cust_country as country, b.last_name as name,
    ->   case b.cust_gender
    ->     when 0 then 'female'
    ->     when 1 then 'male'
    ->     else 'seacret' end as gender
    ->   from m_cust a
    ->   left join m_cust_info b on a.cust_id = b.cust_id
    ->   left join m_cust_flag c on a.cust_flag = c.cust_flag
    ->   order by country;
+----+-------------+-------+------------+------+---------------+---------+---------+----------------+------+----------+--------------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key     | key_len | ref            | rows | filtered | Extra                                      |
+----+-------------+-------+------------+------+---------------+---------+---------+----------------+------+----------+--------------------------------------------+
|  1 | SIMPLE      | a     | NULL       | ALL  | NULL          | NULL    | NULL    | NULL           |    5 |   100.00 | Using temporary; Using filesort            |
|  1 | SIMPLE      | b     | NULL       | ref  | cust_id       | cust_id | 42      | tomo.a.cust_id |    1 |   100.00 | NULL                                       |
|  1 | SIMPLE      | c     | NULL       | ALL  | NULL          | NULL    | NULL    | NULL           |    4 |   100.00 | Using where; Using join buffer (hash join) |
+----+-------------+-------+------------+------+---------------+---------+---------+----------------+------+----------+--------------------------------------------+
3 rows in set, 1 warning (0.00 sec)

mysql>

個人的な意見です

好きなところ
- 転記すると見辛いですが、すごく見やすい。特にどのTABLEを使っていて、どのKEYを使ったのかというのは本当にわかりやすい
  - 余談ですが、私はMySQLのSELECT等の「アウトプットの見やすさ」が何より好きです
- 各「select_type」に対して何をしたのかというアクションがわかりやすい
  - 例えばaテーブルに対しては一時テーブル使って、Fileソートしたんだな、とか
  - partitionsじゃないよ、typeは何したよ、Index何使ったよ等などが明瞭ですよね
苦手なところ
- SQL流れが、一見分かりづらい
  - それぞれに対して何をしたのか分かるけど、どんな流れでSQL文処理したのか？はわからない（ボトルネックになる箇所はわかるので必要ないのかもしれないですが）
  - 後述するANALYZEだと分かるんですが、実はNested loopしてるんですよね。そこの動きが見れなくて「ここのNested loopのところが遅いの直したいな」とか、わからないのでは？

では、ANALYZEしてみよう。

mysql> EXPLAIN ANALYZE select distinct c.cust_country as country, b.last_name as name,
    ->   case b.cust_gender
    ->     when 0 then 'female'
    ->     when 1 then 'male'
    ->     else 'seacret' end as gender
    ->   from m_cust a
    ->   left join m_cust_info b on a.cust_id = b.cust_id
    ->   left join m_cust_flag c on a.cust_flag = c.cust_flag
    ->   order by country;
+------------------+
| EXPLAIN                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                           |
+------------------+
| -> Sort: c.cust_country  (actual time=0.12..0.12 rows=5 loops=1)
    -> Table scan on <temporary>  (cost=4.52..7.13 rows=20) (actual time=0.108..0.109 rows=5 loops=1)
        -> Temporary table with deduplication  (cost=4.38..4.38 rows=20) (actual time=0.106..0.106 rows=5 loops=1)
            -> Left hash join (c.cust_flag = a.cust_flag)  (cost=2.38 rows=20) (actual time=0.0575..0.0833 rows=10 loops=1)
                -> Nested loop left join  (cost=2.5 rows=5) (actual time=0.021..0.0437 rows=5 loops=1)
                    -> Table scan on a  (cost=0.75 rows=5) (actual time=0.00607..0.00799 rows=5 loops=1)
                    -> Index lookup on b using cust_id (cust_id=a.cust_id)  (cost=0.27 rows=1) (actual time=0.00568..0.00674 rows=1 loops=5)
                -> Hash
                    -> Table scan on c  (cost=0.13 rows=4) (actual time=0.019..0.0245 rows=4 loops=1)
 |
+------------------+
1 row in set (0.01 sec)

mysql>

好きなところ
- 「a=cのJOINはhashで、Nested loopして突き合わせさせたんだな」という「DBがどうやってTABLEを検索しに行ったのか」の流れがわかりやすい
  - SQL作成時に脳内で描く「こうやって検索して〜」という流れとDBが実際内部で実施した流れが同じなのか、違うのかがわかる
  - 遅い時って大抵「あれここなんでこの順序でやった？」ということが多い
- costやactual time、loop等でどの処理で時間かかったのかわかりやすい
  - 既存のEXPLAINでも分かるんですが、「ここのhash join異様に時間かかっているな。ということはこのjoinの箇所が問題だな」という問題箇所の特定がより明確
苦手なところ
- 既存の「EXPLAIN」と比較すると、情報がごちゃごちゃして読み辛い
  - もちろんちゃんと読めるですが、元々が表形式で見やすかっただけに、余計見辛い感じがする

さて、TREEにした場合どうなるかというと

mysql> EXPLAIN FORMAT=TREE select distinct c.cust_country as country, b.last_name as name,
    ->   case b.cust_gender
    ->     when 0 then 'female'
    ->     when 1 then 'male'
    ->     else 'seacret' end as gender
    ->   from m_cust a
    ->   left join m_cust_info b on a.cust_id = b.cust_id
    ->   left join m_cust_flag c on a.cust_flag = c.cust_flag
    ->   order by country;
+------------------+
| EXPLAIN                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                               |
+------------------+
| -> Sort: c.cust_country
    -> Table scan on <temporary>  (cost=4.52..7.13 rows=20)
        -> Temporary table with deduplication  (cost=4.38..4.38 rows=20)
            -> Left hash join (c.cust_flag = a.cust_flag)  (cost=2.38 rows=20)
                -> Nested loop left join  (cost=2.5 rows=5)
                    -> Table scan on a  (cost=0.75 rows=5)
                    -> Index lookup on b using cust_id (cust_id=a.cust_id)  (cost=0.27 rows=1)
                -> Hash
                    -> Table scan on c  (cost=0.13 rows=4)
 |
+------------------+
1 row in set (0.00 sec)

mysql>

ちょっと情報がコンパクトになりましたね？
ざっと動きを見るならTREEの方が見やすそうですが、actual time、loop等が抜けているのでリリース前に実行計画見るのであればTREEでない方がいいかも。

OracleDBの実行計画

さて、同じようなTABLEで同じデータが入ったOracleDBで、実行計画を取ってみます。

TOMO@oracle > select distinct c.cust_country as country, b.last_name as name,
  case b.cust_gender
    when 0 then 'female'
    when 1 then 'male'
    else 'seacret' end as gender
  from m_cust a
  left join m_cust_info b on a.cust_id = b.cust_id
  left join m_cust_flag c on a.cust_flag = c.cust_flag
  order by country;

実行計画
----------------------------------------------------------
Plan hash value: 3044041168

--------------------------------------------------------------------------------------
| Id  | Operation	       | Name	     | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |	     |	2040 |	 173K|	  10  (20)| 00:00:01 |
|   1 |  SORT UNIQUE	       |	     |	2040 |	 173K|	   9  (12)| 00:00:01 |
|*  2 |   HASH JOIN RIGHT OUTER|	     |	2040 |	 173K|	   8   (0)| 00:00:01 |
|   3 |    TABLE ACCESS FULL   | M_CUST_FLAG |	 409 | 12270 |	   3   (0)| 00:00:01 |
|*  4 |    HASH JOIN OUTER     |	     |	 409 | 23313 |	   5   (0)| 00:00:01 |
|   5 |     TABLE ACCESS FULL  | M_CUST      |	  82 |	1640 |	   2   (0)| 00:00:01 |
|   6 |     TABLE ACCESS FULL  | M_CUST_INFO |	 409 | 15133 |	   3   (0)| 00:00:01 |
--------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("A"."CUST_FLAG"="C"."CUST_FLAG"(+))
   4 - access("A"."CUST_ID"="B"."CUST_ID"(+))


統計
----------------------------------------------------------
	 49  recursive calls
	  0  db block gets
	117  consistent gets
	  1  physical reads
	  0  redo size
	853  bytes sent via SQL*Net to client
	676  bytes received via SQL*Net from client
	  2  SQL*Net roundtrips to/from client
	 11  sorts (memory)
	  0  sorts (disk)
	  5  rows processed

前半の「統計」までを、MySQLには無いなと思うところ見ていきましょう。
くどいですが、これは有償DBです。情報量が多いのは当たり前なので、良し悪しを言いたいのではありません。

MySQLを見た後にみると、「MySQLのEXPLAINとEXPLAIN ANALYZEのいいとこ取り」をしているように思います。

実行計画に対し一意性（hash value値）がある
- これは私にとって重要です
  - 今朝までXXXXXXXXだったのに、今見たらYYYYYYYYYYになってる！オプティマイザ変えたな実行計画…！とすぐ分かる
  - Oracleでは、SQLに対するIDとこの実行計画のhash valueを紐づけて、DB側で「このSQLはこの実行計画で絶対動いてねー」という設定ができるのですが、その時このhash valueを使います
Name欄に「どのTABLEに対して」「どうやって処理をして」「どの順に」が明確である
- ここはMySQLの既存のEXPLAINの好きなところと、EXPLAIN ANALYZEの好きなところが両方入ってる
hash joinの箇所等に「どのTABLEのどのカラムを、どうやって結合したよ」を*をつけて「Predicate Information」で欄外で詳細を記載してくれているので読みやすい
- 「"A"."CUST_FLAG"="C"."CUST_FLAG"(+)」　は、a.cust_falgに対し、c.cust_flagをjoinしたんだよ、という意味です。ちゃんとleft joinしたのね偉いこ
  - 長文SQLだとこの「Predicate Information」は大量に出るので、注釈してくれていると読みやすいのです
- 「HASH JOIN RIGHT OUTER」は外部ハッシュ結合したよってことです。おや、DISK読み込みしたのかしら？とここで思ったりします
Timeはミリ秒までなのでMySQLの「actual time」よりは精度は低い
- 個人的な意見ですが、これは軽量で処理することを求められるMySQLならではのいいところだと思います。OracleDBが遅いとは言わないですが、バックグラウンドプロセス等考えると、シンプルかつ高速がMySQLのいいところなので

MySQLに無いのは「統計」の箇所ですね

このSQLでどのくらいDISK読み込みしたか（physical reads）、DMLの場合はREDOにどのくらいログだすか（redo size）とか、データどのくらいこねくり回したか（consistent gets）、ソートどのくらいしたか（sorts (memory/disk)）等などのインフラリソースへの影響がまとまっている点です
- オンライン系のSQLであればphysical readsやconsistent gets等、バッチであればredo size等など、それぞれのSQLに応じて気になる点を確認しています

MySQLの実行計画（EXPLAIN ANALYZE）に欲しい情報

ということで単純比較してみました。
個人的な意見としては、

既存「EXPLAIN」は、流れは分かりづらいけれど、読みやすい
「EXPLAIN ANALYZE」は流れは分かるけど、読みづらい

なので、個人的に欲しいなとおもうのは

実行計画に一意性をもたせる値がほしい
- hash valueでなくてもいいので「あれこれ前回と変わったな」がわかるようにして欲しいような。MySQL開発のみなさんはどうしてるんだろう？
  - Oracle屋さんは、SQLレポートで見てますが、あそこまでやろうとすると機能が複雑になるので不要だと思ってます
「EXPLAIN ANALYZE」はOracleDBみたいに、元の「EXPLAIN」みたいに表形式にできないか
- 箇条書きみたいしているのが、分かるようで分かりづらい
OracleDBにある「統計」みたいなのが出せないか
- 個人的にphysical readsとconsistent gets、sorts等はほしいなぁ

余談：OracleDBで更に詳細なSQLのコスト情報を出す

ここは余談ですが、こんなことすると「このSQLがCPUどのくらい使ったか」が詳細にとれます。

TOMO@oracle > select name,value from v$mystat my inner join v$statname snon my.STATISTIC# = sn.STATISTIC# where sn.name in ('parse time cpu','parse time elapsed','DB time','CPU used by this session') order by name;

NAME			  VALUE
-------------------- ----------
CPU used by this ses	     52
sion

DB time 		    207
parse time cpu		     20
parse time elapsed	     61

DB Time（DB CPUと待機時間の合計）が取れるのもいいですよね。
DB Timeは「津島博士のパフォーマンス講座　第17回チューニングについて」に詳しく書かれているのでこちらをぜひ。
また、parseをどのくらいしているのかも分かるので、思ったより多かったりすると「あれおかしいな、なんでこんなにしてるの？」と疑ったりしてます。

ASMのきもち

DBとAnsibleが気になって仕方ない人のブログ