MySQL 5.6での機能強化点（その1） - パフォーマンスと使い勝手を大きく向上

オプティマイザ：性能の向上

オプティマイザの改良

続いては、オプティマイザの改善点についてご紹介します。オプティマイザにより、SQLの実行計画が生成されます。MySQL 5.6ではオプティマイザを改良しているため、今までと同じクエリであっても5.6の環境で実行するだけで、パフォーマンスが良くなるケースもあります。どのように改善されているのか、改善内容をご紹介します。

・サブクエリの高速化

サブクエリは、MySQLにとって苦手な処理でした。しかし、MySQL 5.6では、サブクエリが高速化されています。そのため、チューニングのためにサブクエリをJOINに書き換える、といった対応が不要になります。

・Block Nested Loop Join（BNLJ）がOUTER JOINに対応

BNLJは、インデックスが使えないJOIN処理を高速化できるアルゴリズムです。内部表へのアクセスにインデックスが使えない場合に、駆動表のレコード毎に内部表にアクセスするのではなく、駆動表のレコードをJOINバッファにためてからまとめて内部表へアクセスすることで、内部表へのアクセスを効率化できます。従来はINNER JOINにのみ対応していましたが、OUTER JOINにも対応しました。

・Batched Key Access（BKA）とマルチ・レンジ・リード(MRR)

ディスクアクセスの多いJOIN処理を高速化できるアルゴリズムです。インデックスを使って内部表にアクセスする場合に、MRRによってディスクの並び順に従ってデータファイルを読み取ることができます。また、BKAによって駆動表のレコードをJOINバッファにためてからMRRを使ってまとめて内部表にアクセスすることで、JOIN処理が高速化できます。

※BKAはデフォルトでは無効に設定されています。BKAを有効にする場合は、optimizer_switchパラメータを変更して下さい。詳細は、こちらを参照下さい。

・Index Condition Pushdown(ICP)

WHERE句の条件など、インデックスによる絞り込みを全てストレージエンジン側で行うことで、MySQLサーバー内のオーバーヘッドを削減します。

・LIMIT句使用時の最適化

ソートの中間ファイル作成をできる限り回避し、メモリ上で処理することで、LIMIT句の性能を向上させています。

・FROM句からのビュー/サブクエリの実データ取得を遅延

ビュー/サブクエリのマテリアライズをできる限り避けたり、マテリアライズ化したテーブルにインデックスを追加したりすることで、FROM句にビュー/サブクエリが含まれるクエリを高速化しています。

・オプティマイザ統計情報の永続化

実行計画を安定させるために、InnoDBテーブルやインデックスの統計情報を永続化できます。ANALYZE文での更新も可能です。

オプティマイザ：使い勝手の向上

Explainの改良やオプティマイザ・トレースなど、オプティマイザ関連の使い勝手も向上しています。

ExplainをSELECT以外でも実行可能に

SQLの実行計画を確認するExplainですが、従来はSELECTに対してのみ実行できました。そのため、例えばUPDATEのチューニングをする場合に、WHERE句が同じSELECT文に書き換えてからExplainを取得する、といった対応をしているケースもありましたが、MySQL 5.6ではこのような対応が不要になりました。SELECT以外にも、DELETE、INSERT、REPLACE、UPDATEに対してExplainを取得することが可能です。

ビジュアルExplain

MySQL 5.6では、従来のテキストフォーマットのExplain以外に、JSONフォーマットでExplainを出力することが可能になりました。JSONフォーマットのExplainはテキストフォーマットのExplainよりも情報量が多くなっている、という利点もありますが、それ以上に便利なのがMySQL Workbenchと連携して使える、ビジュアルExplainです。
MySQL WorkbenchのSQL EditorからExplainを取得することで、以下のようにExplain結果をビジュアルに表示することが可能です。

図3：ビジュアルExplain（クリックで拡大）

MySQL Workbenchの詳細については、別途連載している「モデリングツールから管理・開発統合環境に進化したMySQL Workbench 6.0」をご覧下さい。

また、JSONフォーマットでのExplainを取得する場合は、以下の構文で取得できます。

  EXPLAIN FORMAT=JSON <Explain対象のSQL文>;

オプティマイザ・トレース

オプティマイザが実行計画を生成する際にどのような判断をしたのか、詳細をトレースできるようになりました。以下の手順でオプティマイザ・トレースを取得することが可能です。トレースはJSON形式で出力されます。

  SET SESSION optimizer_trace='enabled=on,one_line=off';
  SET SESSION optimizer_trace_max_mem_size=102400;   ※必要に応じて設定(トレースを保持する量をバイト単位で指定、デフォルト値は16384)
  <任意のクエリを実行>
  SELECT * FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE\G
  SET SESSION optimizer_trace='enabled=off,one_line=off';

• ※optimizer_traceパラメータの詳細は、こちらを参照下さい。
• ※optimizer_trace_max_mem_sizeパラメータの詳細は、こちらを参照下さい。

レプリケーション、NoSQLオプションなど、他にも多くの点が機能強化

今回はInnoDBとオプティマイザの機能強化点についてご紹介しました。レプリケーションやパフォーマンススキーマ、NoSQLオプション（Memcached API）などの機能強化点については、次回の連載で詳細をご紹介する予定です。

※本稿において示されている見解は、私自身の見解であって、私の所属するオラクルの見解を必ずしも反映したものではありません。