読者です 読者をやめる 読者になる 読者になる

Y-Ken Studio

新しもの好きのデータエンジニアが四方山話をお届けします。

InnoDBはALTER TABLE ○○○ ORDER BY ○○○ DESC;で並び替え済みテーブルを作れない

MyISAM全盛時代には、こんな参照性能高速化手法がありました。
ALTER TABLE foo_table ORDER BYsortDESC;といったクエリで予めテーブルの内容を並び替えておくことで、SELECT時のORDER BY句が省略出来るというものです。

しかしこれ、InnoDBでは使えないので気をつけましょう。

並び替え済みテーブルとはどういうもの?

公式のドキュメントでは以下のように述べられています。

ORDER BY で指定した順番の行で新しいテーブルを作成する事ができます。挿入と削除の後ではテーブルの順番が変わってしまう事を覚えておいて下さい。このオプションは、ほとんど毎回同じ順番で行のクエリを行う場合に便利です。テーブルに大きい変更を行った後にこのオプションを利用すると、高い性能を得る事が可能でしょう。テーブルが、後でカラムをオーダーしたい順番になっていれば、MySQL のソートは簡単になる場合があります。

ORDER BY 構文は、ソートの昇順や降順を指示する為に、それぞれが任意で ASC または DESC が付随する1つか2つのカラム名を指定できます。デフォルトは昇順です。カラム名だけがソート基準として許されていて、任意の式は許されていません。
http://dev.mysql.com/doc/refman/5.1/ja/alter-table.html

パフォーマンスについては以下の記事が参照になります。

MySQLの400万レコードで試すとSELECTで指定したときは6.0018秒に比べ、標準で指定しておいたときは0.0010秒となり、結果も同じです。
ただしこれは1テーブルにつき1カラムにしか指定できません。
また標準で並び順を指定したものをSELECT文でさらに指定をすると、標準で指定してないときと同じ結果になります。
http://kabu-san.com/sql/speedup.html

InnoDBでこのようなクエリを発行するとどうなるか

ORDER BY ignored as there is a user-defined clustered index in the tableというエラーが発生し、何も変更は加えられません。

mysql> ALTER TABLE test_table ORDER BY age DESC;
Query OK, 3 rows affected, 1 warning (0.88 sec)
Records: 3  Duplicates: 0  Warnings: 1

mysql> show warnings\G
*************************** 1. row ***************************
  Level: Warning
   Code: 1105
Message: ORDER BY ignored as there is a user-defined clustered index in the table 'test_table'
1 row in set (0.00 sec)

InnoDBではどうすれば良いのか?

テーブル設計次第ですが、1つ挙げるとすれば、Covering Indexというものがあります。
ORDER BYで利用する列と、取得する列を含めた複合インデックスを作ることで、
インデックスアクセスのみで処理を完結出来るという高速化手法です。

以下のブログが参考になります。

MySQLでインデックスを使って高速化するならCovering Indexが使えそう
http://blog.livedoor.jp/sasata299/archives/51336006.html

参考サイト