株式会社インサイトテクノロジー 発行
http://www.insight-tec.com
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
2009.12.10
MySQLの真実
＜オプティマイザ編 その３＞
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
＜オプティマイザ編 その３＞
■環境
OpenSolaris 2009.06 (VMware Server環境)
MySQL 5.1.37
前回ではEXPLAIN EXTENDED+SQL 文の後でSHOW WARNINGSを実行して、サブクエ
リを使ったSQL文がMySQLの内部でどう書き換えられているかまで確認しました。
今回は変換された内部クエリでどのように実行されているのか、そして、どう
違うのかをMySQL 5.1 で調べたいと思います。
■サブクエリのSQL文の書き換え
前回、以下SQLを実行した結果、MySQL 5.1とMySQL 6.0で内部SQLが異なる点ま
で確認しました。
▼基SQL
mysql> explain extended
SELECT COUNT(l_orderkey) FROM lineitem
WHERE l_linenumber=1 AND
l_orderkey IN
(SELECT o_orderkey FROM orders
WHERE o_totalprice > 1000 AND
o_custkey IN
(SELECT c_custkey FROM customer
WHERE c_address LIKE 'Le%'));
▼MySQL 5.1の内部SQL
select count(`dbt3`.`lineitem`.`l_orderkey`) AS `COUNT(l_orderkey)`
from `dbt3`.`lineitem` where ((`dbt3`.`lineitem`.`l_linenumber` = 1)
and (`dbt3`.`lineitem`.`l_orderkey`,
(((`dbt3`.`lineitem`.`l_orderkey`)
in orders on PRIMARY where ((`dbt3`.`orders`.`o_totalprice` > 1000)
and (`dbt3`.`orders`.`o_custkey`,
(((`dbt3`.`orders`.`o_custkey`)
in customer on PRIMARY where ((`dbt3`.`customer`.`c_address` like 'Le%')
and ((`dbt3`.`orders`.`o_custkey`) = `dbt3`.`customer`.`c_custkey`)))))
and ((`dbt3`.`lineitem`.`l_orderkey`) = `dbt3`.`orders`.`o_orderkey`))))))
▼MySQL 6.0αの内部SQL
select count(`dbt3`.`lineitem`.`l_orderkey`) AS `COUNT(l_orderkey)`
from `dbt3`.`customer` join `dbt3`.`orders` join `dbt3`.`lineitem`
where ((`dbt3`.`lineitem`.`l_orderkey` = `dbt3`.`orders`.`o_orderkey`)
and (`dbt3`.`orders`.`o_custkey` = `dbt3`.`customer`.`c_custkey`)
and (`dbt3`.`lineitem`.`l_linenumber` = 1)
and (`dbt3`.`orders`.`o_totalprice` > 1000)
and (`dbt3`.`customer`.`c_address` like 'Le%'))
わかりづらいので成形すると、以下のようになります。
▼MySQL 5.1の場合
select count(`dbt3`.`lineitem`.`l_orderkey`) AS `COUNT(l_orderkey)`
from `dbt3`.`lineitem`
where ((`dbt3`.`lineitem`.`l_linenumber` = 1)
and (`dbt3`.`lineitem`.`l_orderkey`,
(((`dbt3`.`lineitem`.`l_orderkey`)
in orders on PRIMARY where ((`dbt3`.`orders`.`o_totalprice` > 1000)
and (`dbt3`.`orders`.`o_custkey`,
(((`dbt3`.`orders`.`o_custkey`)
in customer on PRIMARY where ((`dbt3`.`customer`.`c_address` like 'Le%')
and ((`dbt3`.`orders`.`o_custkey`) = `dbt3`.`customer`.`c_custkey`)))))
and ((`dbt3`.`lineitem`.`l_orderkey`) = `dbt3`.`orders`.`o_orderkey`))))))
▼MySQL 6.0αの場合
select count(`dbt3`.`lineitem`.`l_orderkey`) AS `COUNT(l_orderkey)`
from `dbt3`.`customer`
join `dbt3`.`orders`
join `dbt3`.`lineitem`
where ((`dbt3`.`lineitem`.`l_orderkey` = `dbt3`.`orders`.`o_orderkey`)
and (`dbt3`.`orders`.`o_custkey` = `dbt3`.`customer`.`c_custkey`)
and (`dbt3`.`lineitem`.`l_linenumber` = 1)
and (`dbt3`.`orders`.`o_totalprice` > 1000)
and (`dbt3`.`customer`.`c_address` like 'Le%'))
最も顕著な違いは、MySQL 5.1では、INで記載されていた箇所がEXISTSに変換
されており、MySQL 6.0では、INやEXISTSは使用せず複数のテーブルを結合し
ていることがわかります。
つまり、同じSQLを実行しても、MySQLのバージョンによって内部で実行される
SQLが異なっており、この結合の仕方がMySQL 5.1の処理時間が遅い原因である
と考えられます。
■MySQL 5.1が遅い原因
INからEXISTSに変換されている点はわかりましたが、EXISTSを使用したSQLと
テーブルを結合したSQLとで何故このように処理時間が大きく遅くなるのでし
ょうか？
（実行時間はMySQL 5.1が5.25秒、MySQL 6.0が0.10秒）
この点を、テーブル構造および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テーブルにあるデータをempテーブルから抽出するという簡単なクエリを
MySQL 5.1で実行してみました。その結果が以下になります。
select count(0) AS `count(*)` from `test`.`emp` where
(`test`.`emp`.`deptno`,(((`test`.`emp`.`deptno`)
in dept on PRIMARY where ((`test`.`dept`.`loc` = 'aaa0') and
((`test`.`emp`.`deptno`) = `test`.`dept`.`deptno`)))))
+----+--------------------+-------+-----------------+---------------+---------+
| id | select_type | table | type | possible_keys | key |
+----+--------------------+-------+-----------------+---------------+---------+
| 1 | PRIMARY | emp | index | NULL | emp_idx |
| 2 | DEPENDENT SUBQUERY | dept | unique_subquery | PRIMARY | PRIMARY |
+----+--------------------+-------+-----------------+---------------+---------+
---------+------+--------+----------+--------------------------+
key_len | ref | rows | filtered | Extra |
---------+------+--------+----------+--------------------------+
5 | NULL | 100031 | 100.00 | Using where; Using index |
4 | func | 1 | 100.00 | Using where |
---------+------+--------+----------+--------------------------+
上記、SQLの実行計画をみてみると、
1.emp表のセカンダリインデックスemp_idxをインデックスフルスキャン
2.1の各t1.c1の値に対して一致するt2が存在するかチェック
(selet 1 from dept where dept.loc = 'aaa0' and dept.deptno = 値の結果をチェック)
という順にテーブルを読み込んでいることがわかります。
この実行計画はOracleをよく使う人にとってはかなり違和感があるのではない
でしょうか。
何故なら、Oracleを使用している人なら、サブクエリの中のSELECT文を先に読
みこんでから外部のテーブルが参照されることを想定してSQLを書いているは
ずです。
ところがMySQLでは、外部のテーブルを参照した後にサブクエリ内のSQLを実行
しており、使用しているインデックスもサブクエリ内のdept表のインデックス
になっています。
ということはサブクエリ内のdept表にインデックスがないと、全件検索により
処理が遅くなってしまうということでしょうか？
上記を確認するために、dept表のプライマリキーを削除したテーブルで再度検
証してみました。
実行計画を確認すると、dept表のテーブルを全件検索していることがわかりま
す。
+----+--------------------+-------+-------+---------------+---------+---------+
| id | select_type | table | type | possible_keys | key | key_len |
+----+--------------------+-------+-------+---------------+---------+---------+
| 1 | PRIMARY | emp | index | NULL | emp_idx | 5 |
| 2 | DEPENDENT SUBQUERY | dept | ALL | NULL | NULL | NULL |
+----+--------------------+-------+-------+---------------+---------+---------+
------+--------+----------+--------------------------+
ref | rows | filtered | Extra |
------+--------+----------+--------------------------+
NULL | 100031 | 100.00 | Using where; Using index |
NULL | 972 | 100.00 | Using where |
------+--------+----------+--------------------------+
そして、実行時間は、
dept表にプライマリキー有： 0.11秒
dept表にプライマリキー無： 1分36.17秒
となり、サブクエリ内のカラムにインデックスが存在している方が圧倒的に速
いようです。
サブクエリを使ったSQL 文を書く人は、一般的にサブクエリ内のテーブルを抽
出した結果で外部のテーブルを参照すると思いがちですが、MySQL 5.1では、
外部のテーブルから参照してサブクエリと結合している為、サブクエリ内のカ
ラムにインデックスがないと、処理が遅延してしまうということになります。
つまり、このことが、MySQL 6.0の方が高速に処理されて、MySQL 5.1では遅か
った原因であると言えます。
MySQL 5.1は、Cost Base Optimizerですが、サブクエリを使ったSQL 文の場合、
必ず基のテーブル（今回だとempテーブル）からアクセスする仕様のようで、
外部のテーブルからアクセスしたほうが早いかIN内のサブクエリからアクセス
するほうが早いかをCost計算して、早いほうを実行計画とする機能はないよう
です。
このあたりが、MySQL ではまだまだオプティマイザに課題があるとよくいわれ
ている所以でしょうか。
次回はMySQL 6.0 のオプティマイザでどのように改善されたか調べていきたい
と思います。
