「对优化器使用Index Hint」

  CREATED BY JENKINSBOT

Index Hint,索引建议,索引提示,MySQL优化器有其自身的局限性,需要使用Index Hint来提示MySQL优化器如何处理查询。

  1. 什么是Index Hint
  2. Index Hint的语法
  3. Index Hint的几个特征
  4. USE | FORCE | IGNORE的优先级
  5. FULLTEXT搜索中的Index Hint
  6. Index Hint的注意事项
  7. 参考文献

什么是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的优先级

  1. 如果存在{USE | FORCE} INDEX,则首先应用。 如果没有,则使用优化器确定的索引集合。
  2. 然后对上一步的结果应用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