1. 建立測試表， 并加入記錄，建立索引。
(testing)[email protected] [test]> create table testa(id int, name varchar(9));
Query OK, 0 rows affected (0.04 sec)
(testing)[email protected] [test]> insert into testa values(1,'xg');
Query OK, 1 row affected (0.02 sec)
(testing)[email protected] [test]> insert into testa values(1,'xg');
Query OK, 1 row affected (0.00 sec)
(testing)[email protected] [test]> insert into testa values(1,'lr');
Query OK, 1 row affected (0.01 sec)
(testing)[email protected] [test]> insert into testa values(1,'xy');
Query OK, 1 row affected (0.00 sec)
(testing)[email protected] [test]> insert into testa values(1,'ar');
Query OK, 1 row affected (0.00 sec)
(testing)[email protected] [test]> insert into testa values(2,'ar');
Query OK, 1 row affected (0.01 sec)
(testing)[email protected] [test]> insert into testa values(3,'ar');
Query OK, 1 row affected (0.01 sec)
(testing)[email protected] [test]> commit;
Query OK, 0 rows affected (0.00 sec)
(testing)[email protected] [test]> alter table testa add index(id);
Query OK, 0 rows affected (0.03 sec)
Records: 0 Duplicates: 0 Warnings: 0
(testing)[email protected] [test]> alter table testa add index(name);
Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0
2. 使用USE INDEX：
(testing)[email protected] [test]> explain select * from testa use index(id) where name='xy' and id=1;
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
| 1 | SIMPLE | testa | ALL | id | NULL | NULL | NULL | 7 | Using where |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
1 row in set (0.01 sec)
表TESTA上面兩個字段都有建立索引， 而NAME字段的選擇性會比ID要好， 而使用了USE INDEX ID ， 由于MYSQL發現 ID的索引會使查詢成本過高， 而又因SQL語句裡有USE INDEX， 故無法走NMAE索引， 是以MYSQL選擇了全表掃描。
3. 去掉USE INDEX：
(testing)[email protected] [test]> explain select * from testa where name='xy' and id=1;
+----+-------------+-------+------+---------------+------+---------+-------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+-------+------+-------------+
| 1 | SIMPLE | testa | ref | id,name | name | 30 | const | 1 | Using where |
+----+-------------+-------+------+---------------+------+---------+-------+------+-------------+
1 row in set (0.00 sec)
在去掉這MYSQL選擇使用了正确的索引。
如果一定要使用ID這個索引， 可以使用FORCE INDEX 來完成。
(testing)[email protected] [test]> explain select * from testa force index(id) where name='xy' and id=1;
+----+-------------+-------+------+---------------+------+---------+-------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+-------+------+-------------+
| 1 | SIMPLE | testa | ref | id | id | 5 | const | 5 | Using where |
+----+-------------+-------+------+---------------+------+---------+-------+------+-------------+
1 row in set (0.00 sec)
這個也是有非常大的風險的， 如果這個索引失效， 或者删除， 都會導緻語句報錯。
(testing)[email protected] [test]> alter table testa drop index id;
Query OK, 0 rows affected (0.07 sec)
Records: 0 Duplicates: 0 Warnings: 0
(testing)[email protected] [test]> explain select * from testa force index(id) where name='xy' and id=1;
ERROR 1176 (42000): Key 'id' doesn't exist in table 'testa'
是以，請慎用MYSQL的HINT。