Index Hint,索引建议,索引提示,MySQL优化器有其自身的局限性,需要使用Index Hint来提示MySQL优化器如何处理查询。
- 什么是Index Hint
- Index Hint的语法
- Index Hint的几个特征
- USE | FORCE | IGNORE的优先级
- FULLTEXT搜索中的Index Hint
- Index Hint的注意事项
- 参考文献
什么是Index Hint
在各种的SQL实现中,建议(Hint)是指示数据库引擎如何执行查询的附加SQL标准。所以说建议属于SQL的一部分。当然,不同的数据库引擎在实现建议有各自不同的做法。详细的内容需要参考各自数据库的文档描述。
Index Hint,用于在查询处理期间提示查询优化器,告诉查询优化器如何选择索引。
Index Hint的语法
tbl_name [[AS] alias] [index_hint_list]
index_hint_list:
index_hint [, index_hint] …
index_hint:
USE {INDEX|KEY}
[FOR {JOIN|ORDER BY|GROUP BY}] ([index_list])
| IGNORE {INDEX|KEY}
[FOR {JOIN|ORDER BY|GROUP BY}] (index_list)
| FORCE {INDEX|KEY}
[FOR {JOIN|ORDER BY|GROUP BY}] (index_list)
index_list:
index_name [, index_name] …
索引提示是在表名之后指定的。
USE INDEX(index_list)告诉MySQL只使用某个索引来查找表中的行。IGNORE INDEX(index_list)告诉MySQL不要使用某些特定索引。FORCE INDEX类似于USE INDEX(index_list),另外还假设表扫描代价很高。换句话说,只有在无法使用索引来查找表中的行时,才使用表扫描。
如果EXPLAIN显示MySQL使用了可能索引列表中的错误索引,使用Index Hint是非常有用的。
index_name必须使索引名,不能使列表。index_name可以是索引名的前缀,但这个前缀必须是唯一的,如果使用这个前缀找到了两个匹配的索引名,则会触发错误。
下面的SQL是两个简单的使用案例:
SELECT * FROM table1 USE INDEX (col1_index,col2_index) WHERE col1=1 AND col2=2 AND col3=3; SELECT * FROM table1 IGNORE INDEX (col3_index) WHERE col1=1 AND col2=2 AND col3=3;
Index Hint的几个特征
省略USE INDEX中的index_list在语法上数据正确的,这意味着“不使用索引”。省略FORCE INDEX或IGNORE INDEX的index_list是语法错误。
FOR子句来指定Index Hint的范围。这个特点提供了对执行计划的各个阶段的更细粒度的控制。使用FOR JOIN可以影响MySQL在表中查找行以及处理连接时使用的索引。使用FOR ORDER BY或FOR GROUP BY可以影响排序或分组行的索引的使用情况。
可以使用多个Index Hint:
SELECT * FROM t1 USE INDEX (i1) IGNORE INDEX FOR ORDER BY (i2) ORDER BY a; -- 虽然出现了重复,但这并不是一个错误。 SELECT * FROM t1 USE INDEX (i1) USE INDEX (i1,i1); -- 但是,对同一个表来混合USE INDEX和FORCE INDEX是的错误,下面是一个错误的示例: SELECT * FROM t1 USE INDEX FOR JOIN (i1) FORCE INDEX FOR JOIN (i2);
如果Index Hint没有FOR子句,则建议的范围将作用于语句的所有部分。例如下面的两个建议是等价的:
-- 作用于语句的所有部分的建议 IGNORE INDEX (i1) -- 作用于指定范围的建议 IGNORE INDEX FOR JOIN (i1) IGNORE INDEX FOR ORDER BY (i1) IGNORE INDEX FOR GROUP BY (i1)
当处理Index Hint时,它们按类型(USE,FORCE,IGNORE)和范围(FOR JOIN,FOR ORDER BY,FOR GROUP BY)以单个列表的形式进行合并。例如下面的两个SQL使等价的:
SELECT * FROM t1 USE INDEX () IGNORE INDEX (i2) USE INDEX (i1) USE INDEX (i2); SELECT * FROM t1 USE INDEX (i1,i2) IGNORE INDEX (i2);
USE | FORCE | IGNORE的优先级
- 如果存在{USE | FORCE} INDEX,则首先应用。 如果没有,则使用优化器确定的索引集合。
- 然后对上一步的结果应用IGNORE INDEX。
例如,以下两个查询是等效的:
SELECT * FROM t1 USE INDEX (i1) IGNORE INDEX (i2) USE INDEX (i2); SELECT * FROM t1 USE INDEX (i1);
FULLTEXT搜索中的Index Hint
对于自然语言模式的搜索,Index Hint会被忽略。例如,IGNORE INDEX(i1)会被忽略,并且没有警告,索引仍然会被使用。
对于布尔模式的搜索,使用FOR ORDER BY或FOR GROUP BY的Index Hint会被忽略。具有FOR JOIN或无FOR修饰符的Index Hint是有效的。相比应用于非FULLTEXT搜索的建议,建议作用于查询执行的所有阶段(查找行和检索,分组和排序)。即使提供了非FULLTEXT索引,也是如此。
下面的两个查询使等价的:
SELECT * FROM t USE INDEX (index1) IGNORE INDEX (index1) FOR ORDER BY IGNORE INDEX (index1) FOR GROUP BY WHERE ... IN BOOLEAN MODE ... ; SELECT * FROM t USE INDEX (index1) WHERE ... IN BOOLEAN MODE ... ;
Index Hint的注意事项
在MySQL 5.0中,如果没有FOR,建议的范围仅适用于行检索。要使服务器支持这种行为,需要在服务器启动时设置old系统变量。如果启用了该变量,在MySQL复制的时候要小心。使用基于语句的二进制日志记录,主从的模式不同可能会导致复制错误。
参考文献
SQL Hint:https://en.wikipedia.org/wiki/Hint_(SQL)
MySQL Index Hint:https://dev.mysql.com/doc/refman/5.7/en/index-hints.html