mySQL5.1.52での複合インデックス実験

インデックスの実験。
以下のようなテーブル。

CREATE TABLE `test` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `admin_id` bigint(20) NOT NULL,
  `user_id` bigint(20) DEFAULT NULL,
  `give_interest_limit` int(20) DEFAULT NULL,
  `action` text,
  `created_at` datetime DEFAULT NULL,
  `updated_at` datetime DEFAULT NULL,
  PRIMARY KEY (`id`)
);

行数は3万件位入れておく。
こんな感じのバルクインサートが良いかも。

INSERT INTO `test` (`admin_id`, `user_id`, `give_interest_limit`) 
VALUES 
('14', '15', '1'),('14', '15', '1'),('14', '15', '1'),('14', '15', '1'),('14', '15', '1'),('14', '15', '1'),('14', '15', '1'),('14', '15', '1'),('14', '15', '1'),('14', '15', '1'),('14', '15', '1'),('14', '15', '1'),('14', '15', '1'),('14', '15', '1'),('14', '15', '1'),('14', '15', '1'),('14', '15', '1'),('14', '15', '1'),('14', '15', '1'),('14', '15', '1'),('14', '15', '1'),('14', '15', '1'),('14', '15', '1'),('14', '15', '1'),('14', '15', '1'),('14', '15', '1'),('14', '15', '1'),('14', '15', '1'),('14', '15', '1'),('14', '15', '1'),('14', '15', '1'),('14', '15', '1'),('14', '15', '1'),('14', '15', '1'),('14', '15', '1'),('14', '15', '1'),('14', '15', '1'),('14', '15', '1'),('14', '15', '1'),('14', '15', '1'),('14', '15', '1'),('14', '15', '1'),('14', '15', '1'),('14', '15', '1'),('14', '15', '1'),('14', '15', '1'),('14', '15', '1'),('14', '15', '1'),('14', '15', '1'),('14', '15', '1'),('14', '15', '1'),('14', '15', '1'),('14', '15', '1'),('14', '15', '1'),('14', '15', '1'),('14', '15', '1'),('14', '15', '1'),('14', '15', '1'),('14', '15', '1'),('14', '15', '1'),('14', '15', '1'),('14', '15', '1'),('14', '15', '1'),('14', '15', '1'),('14', '15', '1'),('14', '15', '1'),('14', '15', '1'),('14', '15', '1'),('14', '15', '1'),('14', '15', '1'),('14', '15', '1'),('14', '15', '1'),('14', '15', '1'),('14', '15', '1'),('14', '15', '1'),('14', '15', '1'),('14', '15', '1'),('14', '15', '1'),('14', '15', '1'),('14', '15', '1'),('14', '15', '1'),('14', '15', '1'),('14', '15', '1'),('14', '15', '1'),('14', '15', '1'),('14', '15', '1'),('14', '15', '1'),('14', '15', '1'),('14', '15', '1'),('14', '15', '1'),('14', '15', '1'),('14', '15', '1'),('14', '15', '1'),('14', '15', '1'),('14', '15', '1'),('14', '15', '1'),('14', '15', '1'),('14', '15', '1'),('14', '15', '1'),('14', '15', '1'),('14', '15', '1'),('14', '15', '1'),('14', '15', '1'),('14', '15', '1'),('14', '15', '1'),('14', '15', '1'),('14', '15', '1'),('14', '15', '1'),('14', '15', '1'),('14', '15', '1'),('14', '15', '1'),('14', '15', '1'),('14', '15', '1'),('14', '15', '1'),('14', '15', '1'),('14', '15', '1'),('14', '15', '1'),('14', '15', '1'),('14', '15', '1'),('14', '15', '1'),('14', '15', '1'),('14', '15', '1'),('14', '15', '1'),('14', '15', '1'),('14', '15', '1'),('14', '15', '1'),('14', '15', '1'),('14', '15', '1'),('14', '15', '1'),('14', '15', '1'),('14', '15', '1'),('14', '15', '1'),('14', '15', '1'),('14', '15', '1'),('14', '15', '1'),('14', '15', '1'),('14', '15', '1'),('14', '15', '1'),('14', '15', '1'),('14', '15', '1'),('14', '15', '1'),('14', '15', '1'),('14', '15', '1'),('14', '15', '1'),('14', '15', '1'),('14', '15', '1'),('14', '15', '1'),('14', '15', '1'),('14', '15', '1'),('14', '15', '1'),('14', '15', '1'),('14', '15', '1'),('14', '15', '1'),('14', '15', '1'),('14', '15', '1'),('14', '15', '1'),('14', '15', '1'),('14', '15', '1'),('14', '15', '1'),('14', '15', '1'),('14', '15', '1'),('14', '15', '1'),('14', '15', '1'),('14', '15', '1'),('14', '15', '1'),('14', '15', '1'),('14', '15', '1'),('14', '15', '1'),('14', '15', '1'),('14', '15', '1'),('14', '15', '1'),('14', '15', '1'),('14', '15', '1'),('14', '15', '1'),('14', '15', '1'),('14', '15', '1'),('14', '15', '1'),('14', '15', '1'),('14', '15', '1'),('14', '15', '1'),('14', '15', '1'),('14', '15', '1'),('14', '15', '1'),('14', '15', '1'),('14', '15', '1'),('14', '15', '1'),('14', '15', '1'),('14', '15', '1'),('14', '15', '1'),('14', '15', '1'),('14', '15', '1'),('14', '15', '1'),('14', '15', '1'),('14', '15', '1'),('14', '15', '1'),('14', '15', '1'),('14', '15', '1'),('14', '15', '1'),('14', '15', '1'),('14', '15', '1'),('14', '15', '1'),('14', '15', '1'),('14', '15', '1'),('14', '15', '1'),('14', '15', '1'),('14', '15', '1'),('14', '15', '1'),('14', '15', '1'),('14', '15', '1'),('14', '15', '1'),('14', '15', '1'),('14', '15', '1'),('14', '15', '1'),('14', '15', '1'),('14', '15', '1'),('14', '15', '1'),('14', '15', '1'),('14', '15', '1'),('14', '15', '1'),('14', '15', '1'),('14', '15', '1'),('14', '15', '1');

以下のようなインデックスを貼ってみる。

ALTER TABLE `test`
ADD INDEX admin_id(`admin_id`) ,
ADD INDEX user_id(`user_id`) ,
ADD INDEX admin_id__user_id(`admin_id`,`user_id`),
ADD INDEX user_id__admin_id(`user_id`,`admin_id`);

■explainしてみる
とりあえずpossible_keysが、使えそうなインデックスで、keyが実際に使われたインデックスと見ればOKです。
explainの読み方はいつも参考にしている方のブログがわかりやすいです。

explain select * from test
 where
admin_id = 14 AND user_id = 15;

■結果

+----+-------------+-------+------+------------------------------------------------------+----------+---------+-------+--------+-------------+
| id | select_type | table | type | possible_keys                                        | key      | key_len | ref   | rows   | Extra       |
+----+-------------+-------+------+------------------------------------------------------+----------+---------+-------+--------+-------------+
|  1 | SIMPLE      | test  | ref  | admin_id,user_id,admin_id__user_id,user_id__admin_id | admin_id | 8       | const | 146559 | Using where |
+----+-------------+-------+------+------------------------------------------------------+----------+---------+-------+--------+-------------+

■あれ
なぜか`admin_id__user_id`を使ってくれない。

■消してみる

ALTER TABLE `test`
DROP INDEX `admin_id`;

■explainしてみる

explain select * from test
 where
admin_id = 14 AND user_id = 15;

■結果

+----+-------------+-------+------+---------------------------------------------+---------+---------+-------+--------+-------------+
| id | select_type | table | type | possible_keys                               | key     | key_len | ref   | rows   | Extra       |
+----+-------------+-------+------+---------------------------------------------+---------+---------+-------+--------+-------------+
|  1 | SIMPLE      | test  | ref  | user_id,admin_id__user_id,user_id__admin_id | user_id | 9       | const | 146484 | Using where |
+----+-------------+-------+------+---------------------------------------------+---------+---------+-------+--------+-------------+

■あれ
`user_id`使われちゃった。
困ったな。

■消してみる

ALTER TABLE `test`
DROP INDEX `user_id`;

■explainしてみる

explain select * from test
 where
admin_id = 14 AND user_id = 15;

■結果

+----+-------------+-------+------+-------------------------------------+-------------------+---------+-------------+--------+-------------+
| id | select_type | table | type | possible_keys                       | key               | key_len | ref         | rows   | Extra       |
+----+-------------+-------+------+-------------------------------------+-------------------+---------+-------------+--------+-------------+
|  1 | SIMPLE      | test  | ref  | admin_id__user_id,user_id__admin_id | admin_id__user_id | 17      | const,const | 146466 | Using where |
+----+-------------+-------+------+-------------------------------------+-------------------+---------+-------------+--------+-------------+

■お、使ってくれた。
でも、user_id__admin_idは使ってくれるのかな。andの左右を逆にしてみよう。

■explainしてみる

explain select * from test
 where
user_id = 15 and admin_id = 14;

■結果

+----+-------------+-------+------+-------------------------------------+-------------------+---------+-------------+--------+-------------+
| id | select_type | table | type | possible_keys                       | key               | key_len | ref         | rows   | Extra       |
+----+-------------+-------+------+-------------------------------------+-------------------+---------+-------------+--------+-------------+
|  1 | SIMPLE      | test  | ref  | admin_id__user_id,user_id__admin_id | admin_id__user_id | 17      | const,const | 146466 | Using where |
+----+-------------+-------+------+-------------------------------------+-------------------+---------+-------------+--------+-------------+

■あれ・・・・
user_id__admin_id使ってくれない。

◎以上から2つの結論が導かれる。
■複合インデックスで順番を気にすべき！というのはネットで散々書かれているのでホントっぽいけど
割とオプティマイザが内部でAND等の順番を入れ替えてしまっていると考えられる。
適切なインデックスを用いよう！としている様子。
ただ、単一インデックスがあるとそちらを使ってしまう傾向があるみたいに見える（こちらの検証はまた別の機会にしてみるけどオプティマイザを追うなんてのは割と泣けるから嫌かなぁ）
ので、複雑になった時はUSE INDEXやIGNORE INDEX※を用いるといいかも。

※
#http://dev.mysql.com/doc/refman/5.1-olh/ja/index-hints.html
#USE INDEX (index_list) を指定することによって、テーブル内の行を検索するために、指定されたインデックスの 1 つのみを使用するよう MySQL に指示できます。代わりの構文 IGNORE INDEX (index_list) を使用すると、いくつかの特定の (1 つまたは複数の) インデックスを使用しないよう MySQL に指示できます。これらのヒントは、MySQL が可能なインデックスのリストの中から、間違ったインデックスを利用していることを、EXPLAIN が表示したときに便利なものです。
#また、USE INDEX (index_list) と同様の機能を持つが、テーブルスキャンが非常に負荷が大きいと見なされる点が追加された FORCE INDEX を使用することもできます。言い換えると、テーブル内の行を見つけるために与えられたインデックスを利用できない場合、テーブルスキャンを利用することができるということです。
#各ヒントには、カラムの名前ではなく、インデックスの名前が必要です。PRIMARY KEY の名前は PRIMARY です。テーブルのインデックス名を表示するには、SHOW INDEX を使用します。

例:
SELECT * FROM table1 USE INDEX (col1_index,col2_index)
WHERE col1=1 AND col2=2 AND col3=3;
SELECT * FROM table1 IGNORE INDEX (col3_index)
WHERE col1=1 AND col2=2 AND col3=3;