mysql use index_MYSQL 慎用USE INDEX

1. 创建测试表， 并加入记录，创建索引。

(testing)root@localhost [test]> create table testa(id int, name varchar(9));

Query OK, 0 rows affected (0.04 sec)

(testing)root@localhost [test]> insert into testa values(1,'xg');

Query OK, 1 row affected (0.02 sec)

(testing)root@localhost [test]> insert into testa values(1,'xg');

Query OK, 1 row affected (0.00 sec)

(testing)root@localhost [test]> insert into testa values(1,'lr');

Query OK, 1 row affected (0.01 sec)

(testing)root@localhost [test]> insert into testa values(1,'xy');

Query OK, 1 row affected (0.00 sec)

(testing)root@localhost [test]> insert into testa values(1,'ar');

Query OK, 1 row affected (0.00 sec)

(testing)root@localhost [test]> insert into testa values(2,'ar');

Query OK, 1 row affected (0.01 sec)

(testing)root@localhost [test]> insert into testa values(3,'ar');

Query OK, 1 row affected (0.01 sec)

(testing)root@localhost [test]> commit;

Query OK, 0 rows affected (0.00 sec)

(testing)root@localhost [test]> alter table testa add index(id);

Query OK, 0 rows affected (0.03 sec)

Records: 0 Duplicates: 0 Warnings: 0

(testing)root@localhost [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)root@localhost [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)root@localhost [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)root@localhost [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)root@localhost [test]> alter table testa drop index id;

Query OK, 0 rows affected (0.07 sec)

Records: 0 Duplicates: 0 Warnings: 0

(testing)root@localhost [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。