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。