MySQLチューニング虎の巻／ソートに関連するトピックとクエリの書き換え

003

更新日: 2012/02/10
公開日: 2012/01/17

通知

　ところで、ここで少し横道に逸れるがストレージエンジンでソートの挙動に違いが出るパターンをひとつ紹介しよう。先ほどのクエリはMyISAM版のテーブルで実行したものであるが、InnoDBでは次のようなEXPLAINになる。

mysql> EXPLAIN SELECT * FROM CountryLanguage WHERE Language LIKE 'j%' ORDER BY CountryCode,Language\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: countrylanguage
         type: index
possible_keys: NULL
          key: PRIMARY
      key_len: 33
          ref: NULL
         rows: 1045
        Extra: Using where
1 row in set (0.00 sec)

　なんと寸分違わないクエリなのにUsing filesortが消えてしまった！？

　なぜこのような挙動になるかというのは、InnoDBのデータ構造に起因する。InnoDBはクラスタインデックスという構造になっており、主キーのリーフノードにデータが格納されている。WHERE Language LIKE 'j%'という条件はもちろん（B+ツリー）インデックスで解決することはできないので、テーブルスキャンが行われることになる。ただし、InnoDBにとってのテーブルスキャンとは主キーのスキャンにほかならない。従ってテーブルスキャンの結果は主キー順にソートされることになるので、改めてソートを行う必要はないのである。

　また、InnoDBの場合、セカンダリインデックスには主キーの値がリーフノードに含まれているという点も見逃せない。InnoDBでセカンダリインデックスを用いてレコードをフェッチする場合、まずはセカンダリインデックスから該当するレコードに対する主キーの値が導きだされ、その値に従って主キーからレコードがフェッチされるのである。InnoDBにおいてマルチカラムインデックスを用いる場合には、セカンダリインデックスの一番最後に主キーが含まれているということを知っていれば、これをソートや検索で利用できるのでお得である。

漢(オトコ)のコンピュータ道: 知って得するInnoDBセカンダリインデックス活用術！