株式会社インサイトテクノロジー 発行
http://www.insight-tec.com
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
2009.11.04
MySQLの真実
＜オプティマイザ編 その１＞
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
＜オプティマイザ編 その１＞
Sun Microsystemsは2009年4月に、 MySQLの性能が引き上げられた最新の
MySQL5.4プレビュー版を発表しました。GA版のリリース時期(見積)は年内を予
定しているとのことです。
MySQL 5.4の新機能の簡単な説明は以下で公開されております。
(A Quick Look at MySQL 5.4)
http://dev.mysql.com/tech-resources/articles/mysql-54.html
その新機能の中に「Subquery Optimizations and Join Improvements」
(サブクエリのオプティマイザおよびJOINの改善)が挙げられております。
MySQLはデータのアクセス方法(どのテーブルからアクセスしてJOINするか？、
Index Scanするか？ Table Scanするか？)を決定するオプティマイザに課題が
あるとよくいわれております。特にSQL文の中でJOIN またはサブクエリを使用
して実行すると、SQL文によっては実行時間が非常に長くなります。
そのためデータベースで一般的に行われる正規化などをせず、データをflatな
形で保存したほうが良いといわれてきました。これが、MySQL5.4では改善され
ているようなので、ここではMySQL 5.1とMySQL 5.4のオプティマイザを比較検
証して、オプティマイザがどの程度改善されているのか明らかにしたいと思い
ます。
▼環境
OpenSolaris 2009.06 (VMware Server環境)
MySQL 5.1.37
MySQL 5.4.3 β
DBT-3 1.9(http://sourceforge.net/projects/osdldbt)
(※)公開されているDBT-3.1.9はPostgreSQL版しかありませんが、MySQL版のパ
ッチが
http://ossipedia.ipa.go.jp/documents/dbt3-mysql/dbt3-1.9_mysql.html
にあります。
(※)すべてのTableでStorage EngineはInnoDBを使用しています。
MySQL 5.4.3 β版は2009/10/13にリリースされました。今回の検証には現在最
新版のMySQL 5.4.3 βを使用します。
各バージョンで以下を比較します。
1. 実行時間(1回目)
2. 実行時間(2回目以降)
3. 論理読み込み回数
論理読み込み回数は、SQL文の実行前と実行後に
mysql> show status を実行して、
Innodb_buffer_pool_read_requests の値の差分で計算することができます。
4. 実行計画
SQL文の実行前に explainをつけて実行します。
mysql> explain (SQL文)
それでは早速SQLを実行して見ることにしましょう。
▼MySQL 5.1の場合
1. 1 row in set (1 min 58.36 sec)
2. 1 row in set (5.76 sec)
3. 4070651回
4. 実行計画
+----+--------------------+----------+-----------------+---------------+--------------+---------+------+---------+--------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+--------------------+----------+-----------------+---------------+--------------+---------+------+---------+--------------------------+
| 1 | PRIMARY | lineitem | index | NULL | i_l_shipdate | 4 | NULL | 5976976 | Using where; Using index |
| 2 | DEPENDENT SUBQUERY | orders | unique_subquery | PRIMARY | PRIMARY | 4 | func | 1 | Using where |
| 3 | DEPENDENT SUBQUERY | customer | unique_subquery | PRIMARY | PRIMARY | 4 | func | 1 | Using where |
+----+--------------------+----------+-----------------+---------------+--------------+---------+------+---------+--------------------------+
▼MySQL 5.4の場合
1. 1 row in set (2 min 0.87 sec)
2. 1 row in set (5.25 sec)
3. 4064159回
4. 実行計画
+----+--------------------+----------+-----------------+---------------+--------------+---------+------+---------+--------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+--------------------+----------+-----------------+---------------+--------------+---------+------+---------+--------------------------+
| 1 | PRIMARY | lineitem | index | NULL | i_l_shipdate | 4 | NULL | 5989441 | Using where; Using index |
| 2 | DEPENDENT SUBQUERY | orders | unique_subquery | PRIMARY | PRIMARY | 4 | func | 1 | Using where |
| 3 | DEPENDENT SUBQUERY | customer | unique_subquery | PRIMARY | PRIMARY | 4 | func | 1 | Using where |
+----+--------------------+----------+-----------------+---------------+--------------+---------+------+---------+--------------------------+
あれ？
MySQL5.1とMySQL5.4で結果がほとんど変わってないように見えます。
MySQLのホームページでは12分が1.8秒になったとあったのに、何故差が出なか
ったのでしょうか？
■MySQL5.4でオプティマイザが改善されてない理由
MySQLのホームページを見ると、開発スケジュール内のリンクにMySQLのマイル
ストーンに関するドキュメントがあります。
開発スケジュール
http://forge.mysql.com/wiki/Development_Cycle
上記のホームページの中のMilestone Release Model
http://forge.mysql.com/w/images/1/17/MilestoneReleaseModel.pdf
この資料によれば、MySQLのSummit版(June 2009)でInnodb scalability and
performance 機能を追加、Azalea版(September 2009)でOptimizer - BKA and
Subquery opt 機能を追加後、MySQL 5.4版としてBranchさせてGA 版としてリ
リースするとあります。そして、AzaleaのMilestone Releasesは残念ながら
MySQL 5.4.7 RC版としてリリースするとあるため、MySQL 5.4.3 には今回検証
したい機能は追加されていないようです。
しかしMySQL 6.0の機能の一部をMySQL 5.4の機能として入れているのであれば
今回MySQL 5.4で入る予定のサブクエリのオプティマイザの改善はMySQL 6.0の
最新α版ですでに実装済みのはず。そこでMySQL 6.0.11 αでも今回のSQLを実
行してみることにしましょう。
▼MySQL 6.0.11 αの場合
1. 1 row in set (1 min 0.26 sec)
2. 1 row in set (0.10 sec)
3. 39953回
4. 実行計画
+----+-------------+----------+--------+--------------------------------------------+-------------+---------+------------------------------+--------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+----------+--------+--------------------------------------------+-------------+---------+------------------------------+--------+-------------+
| 1 | PRIMARY | customer | ALL | PRIMARY | NULL | NULL | NULL | 145399 | Using where |
| 1 | PRIMARY | orders | ref | PRIMARY,i_o_custkey | i_o_custkey | 5 | dbt3.customer.c_custkey | 7 | Using where |
| 1 | PRIMARY | lineitem | eq_ref | PRIMARY,i_l_orderkey,i_l_orderkey_quantity | PRIMARY | 8 | dbt3.orders.o_orderkey,const | 1 | Using index |
+----+-------------+----------+--------+--------------------------------------------+-------------+---------+------------------------------+--------+-------------+
検証結果を見ると、MySQL6.0では実行計画がガラッと変わり、実行時間は1回
目でほぼ半分、 2回目以降で約50分の1、 読み込み回数では100分の1になりま
した。
特に読み込み回数が激減したため今回のSQL 文を実行するのに必要なバッファ
キャッシュ量が約100分の1になったことになります。
やはり、オプティマイザの改善はMySQL6.0で行われているようです。
■optimizer_switchパラメータ
さらに、MySQL 5.4のマニュアルを見るとオプティマイザに関連した興味深い
記述がありました。
http://dev.mysql.com/doc/refman/5.4/en/
MySQL 5.4.2 からオプティマイザ関連でoptimizer_switchというシステム変数
が追加されたとの記載があります。
http://dev.mysql.com/doc/refman/5.4/en/server-system-variables.html#sysvar_optimizer_switch
MySQL 5.4.3 βと MySQL 6.0.11 αでshow variablesを使ってoptimizer_switch
を確認してみると、MySQL 6.0.11ではoptimizer_modeとしてsemijoin,
materialization,loosescan,firstmatch が追加されてonになっていることが
確認できます。
▼MySQL 5.4.3 βの場合
mysql> show variables like 'optimizer_switch';
+------------------+-------------------------------------------------------------------------------------------+
| Variable_name | Value |
+------------------+-------------------------------------------------------------------------------------------+
| optimizer_switch | index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on |
+------------------+-------------------------------------------------------------------------------------------+
▼MySQL 6.0.11αの場合
mysql> show variables like 'optimizer_switch';
+------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------+
| Variable_name | Value |
+------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------+
| optimizer_switch | firstmatch=on,index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,loosescan=on,materialization=on,semijoin=on |
+------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------+
そして、semijoin=offにすると実行計画は以下のように変わりました。
semijoin=off,materialization=on
+----+-------------+----------+-------+---------------+--------------+---------+------+---------+--------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+----------+-------+---------------+--------------+---------+------+---------+--------------------------+
| 1 | PRIMARY | lineitem | index | NULL | i_l_shipdate | 4 | NULL | 6001906 | Using where; Using index |
| 2 | SUBQUERY | orders | ALL | NULL | NULL | NULL | NULL | 1490258 | Using where |
| 3 | SUBQUERY | customer | ALL | NULL | NULL | NULL | NULL | 145399 | Using where |
+----+-------------+----------+-------+---------------+--------------+---------+------+---------+--------------------------+
さらに、materializationもoffにすると実行計画はMySQL 5.1と実行計画が全
く同じになりました。
semijoin=off,materialization=off
+----+--------------------+----------+-----------------+---------------+--------------+---------+------+---------+--------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+--------------------+----------+-----------------+---------------+--------------+---------+------+---------+--------------------------+
| 1 | PRIMARY | lineitem | index | NULL | i_l_shipdate | 4 | NULL | 6001906 | Using where; Using index |
| 2 | DEPENDENT SUBQUERY | orders | unique_subquery | PRIMARY | PRIMARY | 4 | func | 1 | Using where |
| 3 | DEPENDENT SUBQUERY | customer | unique_subquery | PRIMARY | PRIMARY | 4 | func | 1 | Using where |
+----+--------------------+----------+-----------------+---------------+--------------+---------+------+---------+--------------------------+
今度はsemijoinをon,materializationをoffにして実行すると、元のMySQL 6.0
の実行計画と変わりませんでした。
semijoin=on,materialization=off
+----+-------------+----------+--------+--------------------------------------------+-------------+---------+------------------------------+--------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+----------+--------+--------------------------------------------+-------------+---------+------------------------------+--------+-------------+
| 1 | PRIMARY | customer | ALL | PRIMARY | NULL | NULL | NULL | 145399 | Using where |
| 1 | PRIMARY | orders | ref | PRIMARY,i_o_custkey | i_o_custkey | 5 | dbt3.customer.c_custkey | 7 | Using where |
| 1 | PRIMARY | lineitem | eq_ref | PRIMARY,i_l_orderkey,i_l_orderkey_quantity | PRIMARY | 8 | dbt3.orders.o_orderkey,const | 1 | Using index |
+----+-------------+----------+--------+--------------------------------------------+-------------+---------+------------------------------+--------+-------------+
まとめると、MySQL 6.0でoptimizerの新機能で追加されたsemijoinがsubquery
の実行計画を改善させ、パフォーマンス向上につながったことになります。
ところで、semijoin,materialization,loosescan,firstmatchて何のパラメー
タ？？そもそも、MySQL 5.1以前でsubqueryが遅かった原因は？？？
詳細は次回に続く。
