株式会社インサイトテクノロジー 発行
http://www.insight-tec.com
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
2009.12.02
MySQLの真実
＜オプティマイザ編 その２＞
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
オプティマイザは実行されたSQLを解析して実行計画を作成します。今回はオ
プティマイザが解析した結果である実行計画について確認していこうと思いま
す。
＜オプティマイザ編 その２＞
■環境
OpenSolaris 2009.06 (VMware Server環境)
MySQL 5.1.37
MySQL 6.0.11 α
■実行計画の確認
実行計画は、OraleではEXPLAIN PLAN FOR + SQL文で確認することができますが、
MySQLではどのようにすれば確認することができるでしょうか？
使用方法や見方に関しては、MySQL5.1 のマニュアルの
(http://dev.mysql.com/doc/refman/5.1/en/index.html)
7.2.1. Optimizing Queries with EXPLAIN
のなかに記述されており、ここを見ると、MySQLでは、EXPLAIN + SQL文で確認
することができることがわかります。前回使用したSQLを使用して実際に確認
してみましょう。
▼MySQL5.1で実行
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%'));
+----+--------------------+----------+-----------------+---------------+
| id | select_type | table | type | possible_keys |
+----+--------------------+----------+-----------------+---------------+
| 1 | PRIMARY | lineitem | index | NULL |
| 2 | DEPENDENT SUBQUERY | orders | unique_subquery | PRIMARY |
| 3 | DEPENDENT SUBQUERY | customer | unique_subquery | PRIMARY |
+----+--------------------+----------+-----------------+---------------+
--------------+---------+------+---------+--------------------------+
key | key_len | ref | rows | Extra |
--------------+---------+------+---------+--------------------------+
i_l_shipdate | 4 | NULL | 6001906 | Using where; Using index |
PRIMARY | 4 | func | 1 | Using where |
PRIMARY | 4 | func | 1 | Using where |
--------------+---------+------+---------+--------------------------+
上記を見ると、大外のSELECT文から順に評価されていることがわかります。
具体的には、以下の順でアクセスしています。
1.lineitem表のi_l_shipdateセカンダリーインデックスをフルスキャン
(約600万件)
2.1の検索結果とorder_keyが同じorders表を検索(unique index scan)
3.2の検索結果とcustkeyが同じcustomer表を検索(unique index scan)
■MySQL 6.0αで同一SQL実行
この読み込み順序ですが、MySQL 6.0αで実行するとだと以下のように変わり
ます。
+----+-------------+----------+--------+--------------------------------------------+
| id | select_type | table | type | possible_keys |
+----+-------------+----------+--------+--------------------------------------------+
| 1 | PRIMARY | customer | ALL | PRIMARY |
| 1 | PRIMARY | orders | ref | PRIMARY,i_o_custkey |
| 1 | PRIMARY | lineitem | eq_ref | PRIMARY,i_l_orderkey,i_l_orderkey_quantity |
+----+-------------+----------+--------+--------------------------------------------+
-------------+---------+------------------------------+--------+-------------+
key | key_len | ref | rows | Extra |
-------------+---------+------------------------------+--------+-------------+
NULL | NULL | NULL | 145399 | Using where |
i_o_custkey | 5 | dbt3.customer.c_custkey | 7 | Using where |
PRIMARY | 8 | dbt3.orders.o_orderkey,const | 1 | Using index |
-------------+---------+------------------------------+--------+-------------+
上記を見ると、INリストのSELECT文(customer)から順番に評価されていること
が分かります。
1.customer表をフルスキャン(約15万件)
2.1で一致したcustkeyを使ってorder表をセカンダリインデックススキャン
3.2で一致したorderkeyを使ってlineitem表をプライマリインデックススキャン
つまり、今回のSQL文はMySQL 5.1とMySQL 6.0αで実行計画が異なり、
MySQL 5.1の場合、lineitem→orders→customerの順にアクセスしたのに対し
て、MySQL 6.0αでは、customer→orders→lineitemの順にアクセスしたこと
になります。
また、MySQL 5.1ではlineitemテーブルを約600万件抽出後その他のテーブルを
抽出しているのに対して、MySQL 6.0αでは、customerテーブルを約14万件抽
出後、その他のテーブルとジョインしており、MySQL 6.0αの方が効率的に
データを抽出していることがわかります。
では何故MySQL 5.1とMySQL 6.0αで実行計画が異なっているのでしょうか。
■MySQL 5.1とMySQL 6.0αの差異
MySQL 5.1とMySQL 6.0αの差異を確認する為に、EXPLAINにEXTENDEDオプショ
ンを使用してみましょう。
このEXTENDEDオプションは、SELECT文内でテーブルやカラムをどのように展開
しているか確認することができます。
Oracleでいう、10053トレースと同じような機能といえます。
確認方法は、EXPLAIN EXTENDED + SQL文の後でSHOW WARNINGSを実行して確認
できます。
実際に確認してみましょう。
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> SHOW WARNINGS;
▼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とMySQL 6.0αで見たこともないような記述方法で
SQLが記述されており、結果が異なっていることがわかります。
ちなみに、このSQL、かなり怪しげなSQL文が生成されていますが、ちゃんと
MySQLクライアントで実行することができます。
mysql> explain
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%'))
+----+-------------+----------+--------+--------------------------------------------+
| id | select_type | table | type | possible_keys |
+----+-------------+----------+--------+--------------------------------------------+
| 1 | SIMPLE | customer | ALL | PRIMARY |
| 1 | SIMPLE | orders | ref | PRIMARY,i_o_custkey |
| 1 | SIMPLE | lineitem | eq_ref | PRIMARY,i_l_orderkey,i_l_orderkey_quantity |
+----+-------------+----------+--------+--------------------------------------------+
-------------+---------+------------------------------+--------+-------------+
key | key_len | ref | rows | Extra |
-------------+---------+------------------------------+--------+-------------+
NULL | NULL | NULL | 145399 | Using where |
i_o_custkey | 5 | dbt3.customer.c_custkey | 7 | Using where |
PRIMARY | 8 | dbt3.orders.o_orderkey,const | 1 | Using index |
-------------+---------+------------------------------+--------+-------------+
さて、気になるSQLの違いについてですが、これについては次回見ていくこと
にしましょう。
