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。

转载于:https://www.cnblogs.com/o-to-s/articles/3794406.html