mysql 强制使用索引和强制不使用索引

前一段面试时问过这个问题,当时不太懂,今天刚好看到这一块的内容就梳理下

首先说个概念

SQL提示

就是在sql中加入我自己的提示来达到我想要的效果

关于索引有三种sql提示

use index(index_name)

希望mysql参考的索引列表(也就是我想让mysql优先使用这些索引,其它索引不再考虑)

如在test1中查询

test1 有两个字段

主键id 

索引 name

explain select id from test1;

+------+-------------+-------+-------+---------------+---------+---------+------+------+-------------+
| id   | select_type | table | type  | possible_keys | key     | key_len | ref  | rows | Extra       |
+------+-------------+-------+-------+---------------+---------+---------+------+------+-------------+
|    1 | SIMPLE      | test1 | index | NULL          | PRIMARY | 4       | NULL |   18 | Using index |
+------+-------------+-------+-------+---------------+---------+---------+------+------+-------------+

这里用到了主键索引

我用use来限制下索引的使用,则出现下面的结果,优先使用我允许的索引

MariaDB [fei]> explain select id from test1 use index(name);
+------+-------------+-------+-------+---------------+------------+---------+------+------+-------------+
| id   | select_type | table | type  | possible_keys | key        | key_len | ref  | rows | Extra       |
+------+-------------+-------+-------+---------------+------------+---------+------+------+-------------+
|    1 | SIMPLE      | test1 | index | NULL          | name_index | 6       | NULL |   18 | Using index |
+------+-------------+-------+-------+---------------+------------+---------+------+------+-------------+

ignore index (index_name)

  忽略某个索引(不使用某个索引)

MariaDB [fei]>  explain select name from test1 ;
+------+-------------+-------+-------+---------------+------------+---------+------+------+-------------+
| id   | select_type | table | type  | possible_keys | key        | key_len | ref  | rows | Extra       |
+------+-------------+-------+-------+---------------+------------+---------+------+------+-------------+
|    1 | SIMPLE      | test1 | index | NULL          | name_index | 6       | NULL |   18 | Using index |
+------+-------------+-------+-------+---------------+------------+---------+------+------+-------------+

当我使用ignore限制时出现下面的情况,不在使用该了

MariaDB [fei]>  explain select name from test1 ignore index(name);
+------+-------------+-------+------+---------------+------+---------+------+------+-------+
| id   | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra |
+------+-------------+-------+------+---------------+------+---------+------+------+-------+
|    1 | SIMPLE      | test1 | ALL  | NULL          | NULL | NULL    | NULL |   18 |       |
+------+-------------+-------+------+---------------+------+---------+------+------+-------+

但是我对主键进行了测试,貌似ignore对主键并不适用

force index(index_name) 

强制使用索引

MariaDB [fei]>  explain select id from test1 force index(name);
+------+-------------+-------+-------+---------------+------------+---------+------+------+-------------+
| id   | select_type | table | type  | possible_keys | key        | key_len | ref  | rows | Extra       |
+------+-------------+-------+-------+---------------+------------+---------+------+------+-------------+
|    1 | SIMPLE      | test1 | index | NULL          | name_index | 6       | NULL |   18 | Using index |
+------+-------------+-------+-------+---------------+------------+---------+------+------+-------------+

force index 和 use index 的区别

use index 只是给mysql优化器一种选择的可能,具体的mysql优化器再进行优化选择

但是 force index 是强制mysql优化器使用某个索引