「对优化器使用Index Hint」

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

Filed under: K4NZDROID - @ 9:07 PM