mysqlのインデックスについて質問です。

質問

mysqlのインデックスについて質問です。
http://archiva.jp/web/server-side/sql_02.html に、
『mysqlでは1つのクエリ実行で、1つのテーブルにつき1つのインデックスしか使用できない』
とあります。
よく以下のようなテーブル定義を見かけますが、

CREATE TABLE IF NOT EXISTS `data` (
`id` int(10) NOT NULL default '0',
`user_id` int(10) NOT NULL default '0',
`file_id` int(10) NOT NULL default '0',
`name` text collate utf8_unicode_ci NOT NULL default '',
PRIMARY KEY (`id`),
KEY `user_id` (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

idとuser_idを条件にするクエリであれば、上記制約を満たすため、
KEY `id_user_id_idx` (`id`,`user_id`)
の複合クエリを定義すれば良いのでしょうか？

また、idとuser_idとfile_idを条件にするクエリも考慮に入れた場合、
テーブル定義は以下のようにするのでしょうか？

CREATE TABLE IF NOT EXISTS `data` (
`id` int(10) NOT NULL default '0',
`user_id` int(10) NOT NULL default '0',
`file_id` int(10) NOT NULL default '0',
`name` text collate utf8_unicode_ci NOT NULL default '',
PRIMARY KEY (`id`),
KEY `user_id` (`user_id`),
KEY `id_user_id_idx` (`id`,`user_id`),
KEY `id_user_id_file_id_idx` (`id`,`user_id`,`file_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

最後に、create table 時のインデックスの指定は、
INDEX `user_id` (`user_id`),
のようにもできますが、INDEXとKEYの違いは何かあるのでしょうか。

回答No.1

spieder · ベストアンサー

古いMySQLなら"1つのクエリで1テーブルにつき1インデックスしか使われない"というのは正しいです。しかし、新しいMySQL(ver.5.0以降)ならインデックスマージが行われるので、もし複数のIndexが使えるとオプティマイザが判断をした場合には、複数のインデックスが使われます。

ただ、例で挙がられた"idとuser_idを条件にするクエリ"の場合、PRIMARY KEYであるidカラムで1行のみに絞り込めます。わざわざ複数のIndexを使わずに、PRIMARY KEYを使って対象の1行を取り出してから"指定されたuser_idと一致するかどうか判断する"という最適化をオプティマイザが判断してしまうとuser_idのインデックスは利用されません。

また、検索条件に完全に一致したインデックスが無くても、対象行の絞込みが行える場合には、オプティマイザはインデックスを利用して対象行の絞込みを行ないます。インデックスで絞り込んだ中から条件に一致した結果を返すので、インデックスだけで絞り込んだ場合より多少遅くなりますが、まったくインデックスが無かった場合より早く結果を返せます。

また、複合カラムインデックスは、初めのカラムだけが条件に指定された場合でも有効に活用されますので、検索条件次第ですが、同じカラムの並びでインデックスを複数作成する必要はありません。

http://dev.mysql.com/doc/refman/5.1/ja/mysql-indexes.html

インデックスを大量に作成すると更新時の処理が重くなるので、更新と参照のバランスを考えて必要なインデックスを作成してください。

"INDEXとKEYの違いは？"については、同じINDEX作成用の定義文なので、好みにあわせて好きなほうを使ってもらってかまいません。

参考URL：

http://dev.mysql.com/doc/refman/5.1/ja/index-merge-optimization.html