前一段面试时问过这个问题,当时不太懂,今天刚好看到这一块的内容就梳理下
首先说个概念
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优化器使用某个索引