株式会社インサイトテクノロジー 発行
http://www.insight-tec.com
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
2009.12.24
MySQLの真実
＜オプティマイザ編 その４＞
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
＜オプティマイザ編 その４＞
■環境
OpenSolaris 2009.06 (VMware Server環境)
MySQL 6.0.9
前回ではMySQL 5.1 ではサブクエリを実行した場合、IN句はEXISTS句に書き換
えられることと、必ず外部表から参照してサブクエリの中の内部表と結合して
いることを確認しました。このためサブクエリ内のカラムにインデックスがな
いと処理が遅延していました。
今回はまずMySQL 6.0で前回と同じ簡単なテーブル構造およびSQL文で、SQL 文
の書き換えおよび実行計画を確認してみましょう。
▼テーブル emp(100000行)
empno integer, ename char(255),job char(255), deptno integer
primary key (empno)
index (deptno)
empnoの値:0～99999
deptnoの値:empnoを1000で割った余り
▼テーブル dept(1000行)
deptno integer, dname char(255), loc char(255)
primary key (deptno)
deptnoの値:0～999
locの値:'aaa' + deptnoの値(aaa0～aaa999)
▼SQL文
select count(*) from emp where deptno in (select deptno from dept where loc = 'aaa0');
まずは、dept表にprimary keyがある時の結果となります。
▼SQL文の書き換え
select count(0) AS `count(*)`
from `test`.`dept` join `test`.`emp`
where ((`test`.`emp`.`deptno` = `test`.`dept`.`deptno`)
and (`test`.`dept`.`loc` = 'aaa0'))
▼実行計画
+----+-------------+-------+------+---------------+---------+---------+
| id | select_type | table | type | possible_keys | key | key_len |
+----+-------------+-------+------+---------------+---------+---------+
| 1 | PRIMARY | dept | ALL | PRIMARY | NULL | NULL |
| 1 | PRIMARY | emp | ref | emp_idx | emp_idx | 5 |
+----+-------------+-------+------+---------------+---------+---------+
------------------+------+----------+-------------+
ref | rows | filtered | Extra |
------------------+------+----------+-------------+
NULL | 1024 | 100.00 | Using where |
test.dept.deptno | 1 | 100.00 | Using index |
------------------+------+----------+-------------+
▼実行時間
0.00秒
■MySQL 6.0での結果(内部表にPRIMARY KEYありの場合)
実行計画を見ると、MySQL 5.1では外部表(emp)からアクセスしてから内部表
(dept)にアクセスしていましたが、 MySQL 6.0では内部表(dept)にテーブルフ
ルスキャンしてから外部表(emp)にインデックススキャン(emp_idx)しており、
Oracle をよく使う人にとっても予想通りの実行計画になっています。
また、MySQL 5.1ではSQL文の書き換えはIN句がEXISTS句に書き換えられていま
したが MySQL 6.0ではJOINに書き換えられており、実行時間も0.11秒から0.00
秒まで速くなっています。
それでは、dept表のPRIMARY KEYが無い場合、MySQL 6.0ではどうなるでしょう
か？
MySQL 5.1では実行時間が1分36.17秒と劇的に遅くなりましたが、MySQL 6.0
では高速化されているのでしょうか？
同様の検証をして確認してみましょう。
▼SQL文の書き換え
select count(0) AS `count(*)`
from `test`.`emp` semi join (`test`.`dept`)
where ((`test`.`emp`.`deptno` = `test`.`dept`.`deptno`)
and (`test`.`dept`.`loc` = 'aaa0'))
▼実行計画
+----+-------------+-------+------+---------------+---------+---------+
| id | select_type | table | type | possible_keys | key | key_len |
+----+-------------+-------+------+---------------+---------+---------+
| 1 | PRIMARY | dept | ALL | NULL | NULL | NULL |
| 1 | PRIMARY | emp | ref | emp_idx | emp_idx | 5 |
+----+-------------+-------+------+---------------+---------+---------+
------------------+------+----------+--------------------------------+
ref | rows | filtered | Extra |
------------------+------+----------+--------------------------------+
NULL | 1017 | 100.00 | Using where; Materialize; Scan |
test.dept.deptno | 1 | 100.00 | Using index |
------------------+------+----------+--------------------------------+
▼実行時間
0.00秒
■MySQL 6.0での結果(内部表にPRIMARY KEYなしの場合)
実行計画も、内部表(dept) にテーブルフルスキャンしてから外部表(emp)にイ
ンデックススキャン(emp_idx)しています。
#dept表のExtraにあるMaterialize; Scanは？ですが。。。
そして、SQL文の書き換えをみると、IN句はSEMI JOINという結合句に書き換え
られており、これが、内部表にPRIMARY KEY が無くても実行時間が0.00秒と高
速に処理できている要因と言えます。
▼今回の検証結果
・PRIMARY KEY有
NAME MySQL 5.1 MySQL 6.0
-------------------- ----------------------------- --------------------
SQL文の書き換え EXISTS句に書き換え JOIN句に書き換え
実行計画 外部表→内部表(index scan) 内部表→外部表
(内部表へのアクセス) DEPENDENT SUBQUERY PRIMARY
実行時間 0.11秒 0.00秒
・PRIMARY KEY無
NAME MySQL 5.1 MySQL 6.0
-------------------- ----------------------------- ------------------------------------
SQL文の書き換え EXISTS句に書き換え SEMI JOIN(?)句に書き換え
実行計画 外部表→内部表(full scan) 内部表(Materialize; Scan(?))→外部表
(内部表へのアクセス) DEPENDENT SUBQUERY PRIMARY
実行時間 1分36.17秒 0.00秒
■inner joinとsemi joinの違い
ところで、今回内部的に変換されていたsemi join(セミ結合)とはどのような
結合なのでしょうか。
semi joinはMySQL独自のjoin機能ではありません。Oracle,SQL Serverなどで
もある機能です。Oracleのマニュアルには、
「セミ結合では、右側の複数の行が副問合せの基準を満たしているときに、述
語の左側の行から、EXISTS副問合せに重複することなく適合した行が戻され
ます。」
とありました。
ちょっとわかりにくいので以下のテーブルでsemi joinとinner joinの違いを
確認してみましょう。
▼テーブル t1(1行)
id integer
idの値:1
▼テーブル t2(3行)
id integer
idの値:1
テーブルt2には同じ値の行(1)が3つinsertされています。
以下のSQL文を実行すると下記の結果になります。
▼SQL文
select count(*) from t1 where id in (select id from t2);
▼実行結果
+----------+
| count(*) |
+----------+
| 1 |
+----------+
これをMySQL 6.0でどのようなSQL文に書き換えられているか確認すると
▼SQL文の書き換え
select count(0) AS `count(*)`
from `test`.`t1` semi join (`test`.`t2`)
where (`test`.`t2`.`id` = `test`.`t1`.`id`)
とsemi joinに書き換えられていました。
このsemi joinをjoinに書き換えて以下のSQL文を実行すると
▼SQL文
select count(0) AS `count(*)`
from `test`.`t1` join (`test`.`t2`)
where (`test`.`t2`.`id` = `test`.`t1`.`id`)
▼実行結果
+----------+
| count(*) |
+----------+
| 3 |
+----------+
となります。
テーブルt2には同じidの行が3つ入っているため、select id from t2の結果は
1,1,1と3行になりますが、IN句を使用しているため
select count(*) from t1 where id in (1,1,1)は
select count(*) from t1 where id in (1)と同じになります。
しかしIN句をそのまま単純にJOINに書き換えてしまうと、内部表をselectして
同じ値が出現した場合も別の行とみなして結合してしまうため、結果の行数が
増えてしまいます。
この問い合わせでは、サブクエリに適合した行が複数ある場合にも、外部表か
ら戻される必要があるデータは1つの行のみであり、このような結果にする為に
semi joinという結合が使用されています。
ちなみに、内部表にPRIMARY KEYがある場合、semi joinではなくinner joinが
使用されます。
これは、内部表で実行された結果が一意であり、適合した行が複数返されるこ
とがないのでsemi joinをinner join にして実行していると考えられます。
このsemi join、非常に興味深い動きをしているようなので、次回も引き続き
semi joinについて検証していこうと思います。
