「USE INDEX 」と「FORCE INDEX 」の違い
USE INDEXでは、possible_keysにインデックスがリストアップされない場合に、
リストに加えるために利用します。ただ、それが実際に使われる(keyに表示される)かどうかは
オプティマイザの判断にゆだねられます。
それに対して、FORCE INDEXは実際に使われる(keyに表示される)ように強制するものです。
以下ケースでは、インデックスに対応するデータがある場合はヒント通りに実行されている。
root@localhost [sakila]>select * from actor limit 0,10; +----------+------------+--------------+---------------------+ | actor_id | first_name | last_name | last_update | +----------+------------+--------------+---------------------+ | 1 | PENELOPE | GUINESS | 2006-02-15 04:34:33 | | 2 | NICK | WAHLBERG | 2006-02-15 04:34:33 | | 3 | ED | CHASE | 2006-02-15 04:34:33 | | 4 | JENNIFER | DAVIS | 2006-02-15 04:34:33 | | 5 | JOHNNY | LOLLOBRIGIDA | 2006-02-15 04:34:33 | | 6 | BETTE | NICHOLSON | 2006-02-15 04:34:33 | | 7 | GRACE | MOSTEL | 2006-02-15 04:34:33 | | 8 | MATTHEW | JOHANSSON | 2006-02-15 04:34:33 | | 9 | JOE | SWANK | 2006-02-15 04:34:33 | | 10 | CHRISTIAN | GABLE | 2006-02-15 04:34:33 | +----------+------------+--------------+---------------------+ 10 rows in set (0.00 sec) root@localhost [sakila]>show create table actor\G *************************** 1. row *************************** Table: actor Create Table: CREATE TABLE `actor` ( `actor_id` smallint(5) unsigned NOT NULL AUTO_INCREMENT, `first_name` varchar(45) NOT NULL, `last_name` varchar(45) NOT NULL, `last_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (`actor_id`), KEY `idx_actor_last_name` (`last_name`) ) ENGINE=InnoDB AUTO_INCREMENT=201 DEFAULT CHARSET=utf8 1 row in set (0.00 sec) root@localhost [sakila]>ALTER TABLE actor ADD INDEX idx_fl(first_name,last_name); Query OK, 0 rows affected (0.10 sec) Records: 0 Duplicates: 0 Warnings: 0 root@localhost [sakila]>ALTER TABLE actor ADD index idx_actor_first_name (first_name); Query OK, 0 rows affected (0.06 sec) Records: 0 Duplicates: 0 Warnings: 0 root@localhost [sakila]>show create table actor\G *************************** 1. row *************************** Table: actor Create Table: CREATE TABLE `actor` ( `actor_id` smallint(5) unsigned NOT NULL AUTO_INCREMENT, `first_name` varchar(45) NOT NULL, `last_name` varchar(45) NOT NULL, `last_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (`actor_id`), KEY `idx_actor_last_name` (`last_name`), KEY `idx_fl` (`first_name`,`last_name`), KEY `idx_actor_first_name` (`first_name`) ) ENGINE=InnoDB AUTO_INCREMENT=201 DEFAULT CHARSET=utf8 1 row in set (0.00 sec) root@localhost [sakila]> root@localhost [sakila]>explain select * from actor where first_name = 'JOHNNY'; +----+-------------+-------+------+-----------------------------+--------+---------+-------+------+-----------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+------+-----------------------------+--------+---------+-------+------+-----------------------+ | 1 | SIMPLE | actor | ref | idx_fl,idx_actor_first_name | idx_fl | 137 | const | 2 | Using index condition | +----+-------------+-------+------+-----------------------------+--------+---------+-------+------+-----------------------+ 1 row in set (0.00 sec) root@localhost [sakila]>explain select * from actor use index(idx_actor_first_name) where first_name = 'JOHNNY'; +----+-------------+-------+------+----------------------+----------------------+---------+-------+------+-----------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+------+----------------------+----------------------+---------+-------+------+-----------------------+ | 1 | SIMPLE | actor | ref | idx_actor_first_name | idx_actor_first_name | 137 | const | 2 | Using index condition | +----+-------------+-------+------+----------------------+----------------------+---------+-------+------+-----------------------+ 1 row in set (0.00 sec) root@localhost [sakila]>explain select * from actor force index(idx_actor_first_name) where first_name = 'JOHNNY'; +----+-------------+-------+------+----------------------+----------------------+---------+-------+------+-----------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+------+----------------------+----------------------+---------+-------+------+-----------------------+ | 1 | SIMPLE | actor | ref | idx_actor_first_name | idx_actor_first_name | 137 | const | 2 | Using index condition | +----+-------------+-------+------+----------------------+----------------------+---------+-------+------+-----------------------+ 1 row in set (0.00 sec) root@localhost [sakila]> root@localhost [sakila]>explain select * from actor use index(idx_actor_last_name) where first_name = 'JOHNNY'; +----+-------------+-------+------+---------------+------+---------+------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+------+---------------+------+---------+------+------+-------------+ | 1 | SIMPLE | actor | ALL | NULL | NULL | NULL | NULL | 200 | Using where | +----+-------------+-------+------+---------------+------+---------+------+------+-------------+ 1 row in set (0.00 sec) root@localhost [sakila]>explain select * from actor force index(idx_actor_last_name) where first_name = 'JOHNNY'; +----+-------------+-------+------+---------------+------+---------+------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+------+---------------+------+---------+------+------+-------------+ | 1 | SIMPLE | actor | ALL | NULL | NULL | NULL | NULL | 200 | Using where | +----+-------------+-------+------+---------------+------+---------+------+------+-------------+ 1 row in set (0.00 sec) root@localhost [sakila]> root@localhost [sakila]>select * from actor where last_name = 'CAGE'; +----------+------------+-----------+---------------------+ | actor_id | first_name | last_name | last_update | +----------+------------+-----------+---------------------+ | 11 | ZERO | CAGE | 2006-02-15 04:34:33 | | 40 | JOHNNY | CAGE | 2006-02-15 04:34:33 | +----------+------------+-----------+---------------------+ 2 rows in set (0.00 sec) root@localhost [sakila]>explain select * from actor where last_name = 'CAGE'; +----+-------------+-------+------+---------------------+---------------------+---------+-------+------+-----------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+------+---------------------+---------------------+---------+-------+------+-----------------------+ | 1 | SIMPLE | actor | ref | idx_actor_last_name | idx_actor_last_name | 137 | const | 2 | Using index condition | +----+-------------+-------+------+---------------------+---------------------+---------+-------+------+-----------------------+ 1 row in set (0.00 sec) root@localhost [sakila]>explain select * from actor use index(idx_fl) where first_name = 'JOHNNY'; +----+-------------+-------+------+---------------+--------+---------+-------+------+-----------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+------+---------------+--------+---------+-------+------+-----------------------+ | 1 | SIMPLE | actor | ref | idx_fl | idx_fl | 137 | const | 2 | Using index condition | +----+-------------+-------+------+---------------+--------+---------+-------+------+-----------------------+ 1 row in set (0.00 sec) root@localhost [sakila]>explain select * from actor force index(idx_fl) where first_name = 'JOHNNY'; +----+-------------+-------+------+---------------+--------+---------+-------+------+-----------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+------+---------------+--------+---------+-------+------+-----------------------+ | 1 | SIMPLE | actor | ref | idx_fl | idx_fl | 137 | const | 2 | Using index condition | +----+-------------+-------+------+---------------+--------+---------+-------+------+-----------------------+ 1 row in set (0.00 sec) root@localhost [sakila]>
その他、パフォーマンス関連の確認に関してはSYSスキーマを利用すると良いでしょう。
GITからのダウンロード。
英語ですが、こちらのページに各Viewの詳細が説明されております。
https://github.com/MarkLeith/mysql-sys
SYS Schemaに関しては、こちらが参考になるかと思います。
https://oracleus.activeevents.com/2014/connect/fileDownload/session/72527FD42DFF7B2148314B9E72BE7B6A/CON3751_Leith-mysql_sys_schema_oow_2014.pdf