Index Hintの使いどき

通知が多すぎて担当日を見逃してしまい、大遅刻。ごめんなさい！

# Index Hintとは

MySQLにはIndex Hint機能が実装されています。
ドキュメントから引用すると、こんな具合の構文ですが、要は使うINDEXを指定することができます。

```abnf
tbl_name [[AS] alias] [index_hint_list]

index_hint_list:
    index_hint [, index_hint] ...

index_hint:
    USE {INDEX|KEY}
      [FOR {JOIN|ORDER BY|GROUP BY}] ([index_list])
  | IGNORE {INDEX|KEY}
      [FOR {JOIN|ORDER BY|GROUP BY}] (index_list)
  | FORCE {INDEX|KEY}
      [FOR {JOIN|ORDER BY|GROUP BY}] (index_list)

index_list:
    index_name [, index_name] ...
```

たとえば、こんなテーブルがあったとして、

```sql
CREATE TABLE friend (
    user_id         INTEGER UNSIGNED NOT NULL,
    friend_user_id  INTEGER UNSIGNED NOT NULL,
    accepted_fg     BOOL NOT NULL DEFAULT 0,
    order_num       INTEGER UNSIGNED NOT NULL,
    PRIMARY KEY (user_id, friend_user_id),
    UNIQUE INDEX friend_order_uniq (user_id, order_num),
    INDEX friendship_idx (accepted_fg, user_id, friend_user_id)
) ENGINE=InnoDB;
```

こんなデータが入っているとします。

```sql
INSERT INTO friend (user_id, friend_user_id, accepted_fg, order_num) VALUES (1, 2, 1, 1), (2, 1, 1, 1);
INSERT INTO friend (user_id, friend_user_id, accepted_fg, order_num) VALUES (1, 3, 1, 2), (3, 1, 1, 1);
INSERT INTO friend (user_id, friend_user_id, accepted_fg, order_num) VALUES (1, 4, 1, 3), (4, 1, 1, 1);
INSERT INTO friend (user_id, friend_user_id, accepted_fg, order_num) VALUES (2, 3, 1, 2), (3, 2, 1, 2);
INSERT INTO friend (user_id, friend_user_id, accepted_fg, order_num) VALUES (4, 2, 1, 2), (2, 4, 1, 3);
INSERT INTO friend (user_id, friend_user_id, accepted_fg, order_num) VALUES (4, 3, 1, 3), (3, 4, 1, 3);
INSERT INTO friend (user_id, friend_user_id, accepted_fg, order_num) VALUES (1, 5, 0, 4), (5, 1, 0, 1);
INSERT INTO friend (user_id, friend_user_id, accepted_fg, order_num) VALUES (1, 6, 0, 5), (6, 1, 0, 1);
```

そこにこんなSQLを打ちます。

```sql
SELECT friend_user_id FROM friend WHERE user_id = 1 AND accepted_fg = 1 ORDER BY order_num LIMIT 5;
```

実行計画をみてみましょう。(注: これはMySQL5.5での結果です)

```sql
mysql> EXPLAIN SELECT friend_user_id FROM friend WHERE user_id = 1 AND accepted_fg = 1 ORDER BY order_num LIMIT 5;
+----+-------------+--------+------+------------------------------------------+----------------+---------+-------------+------+-----------------------------+
| id | select_type | table  | type | possible_keys                            | key            | key_len | ref         | rows | Extra                       |
+----+-------------+--------+------+------------------------------------------+----------------+---------+-------------+------+-----------------------------+
|  1 | SIMPLE      | friend | ref  | PRIMARY,friend_order_uniq,friendship_idx | friendship_idx | 5       | const,const |    3 | Using where; Using filesort |
+----+-------------+--------+------+------------------------------------------+----------------+---------+-------------+------+-----------------------------+
1 row in set (0.00 sec)
```

friendship_idxを使ってfilesortが発生しています。嫌な感じですね。
この場合、friendship_idxによる絞込よりソートにindexを使って欲しいでしょう。
friend_order_uniqを使ってほしいところです。
こんなときにINDEX Hintが役立ちます。

```sql
mysql> EXPLAIN SELECT friend_user_id FROM friend FORCE INDEX (friend_order_uniq) WHERE user_id = 1 AND accepted_fg = 1 ORDER BY order_num LIMIT 5;
+----+-------------+--------+------+-------------------+-------------------+---------+-------+------+-------------+
| id | select_type | table  | type | possible_keys     | key               | key_len | ref   | rows | Extra       |
+----+-------------+--------+------+-------------------+-------------------+---------+-------+------+-------------+
|  1 | SIMPLE      | friend | ref  | friend_order_uniq | friend_order_uniq | 4       | const |    5 | Using where |
+----+-------------+--------+------+-------------------+-------------------+---------+-------+------+-------------+
1 row in set (0.00 sec)
```

filesortが消えました。便利！
このように、場合によっては効率の悪いINDEXが選ばれるケースがあるのでINDEX HINTで使いたいINDEXを明示したほうが良いケースがあります。
ちなみに、 `USE INDEX` と `FORCE INDEX` のどっちが良いかという話はあると思いますが、ぼくは答えを持ちあわせていません。すみません。
先日 @kamipo 先生に聞いたところによると、（ぼくの理解があっていれば）`USE INEX` だと指定したINDEXを使ってくれないケースがあるので、明らかに使って欲しいINDEXが決まっている場合は `FORCE INDEX` を使ったほうが良いという具合のことを教えて頂きました。
なので、そういう具合で使い分けていくのが良いのではないでしょうか！

# 余談

MySQL 5.7だと上記のケースで適切にINDEXを選んでくれてなかなか再現してくれなくて例題作るのにハマりました。

```sql
mysql> EXPLAIN SELECT friend_user_id FROM friend WHERE user_id = 1 AND accepted_fg = 1 ORDER BY order_num LIMIT 5;
+----+-------------+--------+------------+------+------------------------------------------+-------------------+---------+-------+------+----------+------------------------------------+
| id | select_type | table  | partitions | type | possible_keys                            | key               | key_len | ref   | rows | filtered | Extra                              |
+----+-------------+--------+------------+------+------------------------------------------+-------------------+---------+-------+------+----------+------------------------------------+
|  1 | SIMPLE      | friend | NULL       | ref  | PRIMARY,friend_order_uniq,friendship_idx | friend_order_uniq | 4       | const |    5 |    10.00 | Using index condition; Using where |
+----+-------------+--------+------------+------+------------------------------------------+-------------------+---------+-------+------+----------+------------------------------------+
1 row in set, 1 warning (0.00 sec)
```

MySQL 5.7すごい！