MySQL :: MySQL 8.4 参考手册 :: 10.9.4 索引提示

版本 8.4

8.0 日语

MySQL 8.4 参考手册 / ... / 索引提示

10.9.4 索引提示

索引提示为优化器提供有关如何在查询处理期间选择索引的信息。此处描述的索引提示不同于“第 10.9.3 节“优化器提示””中描述的优化器提示。索引提示和优化器提示可以单独使用，也可以一起使用。

索引提示适用于 SELECT 和 UPDATE 语句。它们也适用于多表 DELETE 语句，但不适用于单表 DELETE 语句，如本节后面所示。

索引提示在表名后面指定。（有关在 SELECT 语句中指定表的通用语法，请参阅“第 15.2.13.2 节“JOIN 子句””。）引用单个表的语法（包括索引提示）如下所示

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|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 不要使用某些特定的索引。如果 EXPLAIN 显示 MySQL 使用了可能的索引列表中的错误索引，则这些提示很有用。

FORCE INDEX 提示的作用类似于 USE INDEX (index_list)，但它还假定表扫描的成本非常高。换句话说，仅当无法使用指定的索引之一来查找表中的行时，才会使用表扫描。

注意

MySQL 8.4 支持索引级优化器提示 JOIN_INDEX、GROUP_INDEX、ORDER_INDEX 和 INDEX，它们等效于并旨在取代 FORCE INDEX 索引提示，以及 NO_JOIN_INDEX、NO_GROUP_INDEX、NO_ORDER_INDEX 和 NO_INDEX 优化器提示，它们等效于并旨在取代 IGNORE INDEX 索引提示。因此，您应该预计 USE INDEX、FORCE INDEX 和 IGNORE INDEX 在未来版本的 MySQL 中会被弃用，并在一段时间后被完全移除。

单表和多表 DELETE 语句均支持这些索引级优化器提示。

有关更多信息，请参阅“索引级优化器提示”。

每个提示都需要索引名称，而不是列名。要引用主键，请使用名称 PRIMARY。要查看表的索引名称，请使用 SHOW INDEX 语句或信息模式 STATISTICS 表。

index_name 值不需要是完整的索引名称。它可以是索引名称的明确前缀。如果前缀不明确，则会发生错误。

示例

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;

索引提示的语法具有以下特征

对于 USE INDEX，省略 index_list 在语法上是有效的，这意味着“不使用索引。” 对于 FORCE INDEX 或 IGNORE INDEX，省略 index_list 是语法错误。
您可以通过向提示添加 FOR 子句来指定索引提示的范围。这为优化器在查询处理的各个阶段选择执行计划提供了更精细的控制。若要仅影响 MySQL 在决定如何查找表中的行以及如何处理连接时使用的索引，请使用 FOR JOIN。若要影响用于对行进行排序或分组的索引使用，请使用 FOR ORDER BY 或 FOR GROUP BY。

您可以指定多个索引提示

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);

如果索引提示不包含 FOR 子句，则该提示的范围将应用于语句的所有部分。例如，此提示

IGNORE INDEX (i1)

等同于以下提示组合

IGNORE INDEX FOR JOIN (i1)
IGNORE INDEX FOR ORDER BY (i1)
IGNORE INDEX FOR GROUP BY (i1)

处理索引提示时，会按类型（USE、FORCE、IGNORE）和范围（FOR JOIN、FOR ORDER BY、FOR GROUP BY）将它们收集到单个列表中。例如

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} INDEX。（如果不存在，则使用优化器确定的索引集。）

IGNORE INDEX 将应用于上一步的结果。例如，以下两个查询是等效的

SELECT * FROM t1 USE INDEX (i1) IGNORE INDEX (i2) USE INDEX (i2);

SELECT * FROM t1 USE INDEX (i1);

对于 FULLTEXT 搜索，索引提示的工作原理如下

对于自然语言模式搜索，索引提示将被静默忽略。例如，IGNORE INDEX(i1) 将被忽略，不会发出任何警告，并且该索引仍将被使用。
对于布尔模式搜索，带有 FOR ORDER BY 或 FOR GROUP BY 的索引提示将被静默忽略。带有 FOR JOIN 或没有 FOR 修饰符的索引提示将被遵守。与提示如何应用于非 FULLTEXT 搜索不同，该提示将用于查询执行的所有阶段（查找行和检索、分组和排序）。即使为非 FULLTEXT 索引提供了提示，也是如此。
例如，以下两个查询是等效的
```
SELECT * FROM t
  USE INDEX (index1)
  IGNORE INDEX FOR ORDER BY (index1)
  IGNORE INDEX FOR GROUP BY (index1)
  WHERE ... IN BOOLEAN MODE ... ;

SELECT * FROM t
  USE INDEX (index1)
  WHERE ... IN BOOLEAN MODE ... ;
```

索引提示适用于 DELETE 语句，但前提是您使用多表 DELETE 语法，如下所示

mysql> EXPLAIN DELETE FROM t1 USE INDEX(col2) 
    -> WHERE col1 BETWEEN 1 AND 100 AND COL2 BETWEEN 1 AND 100\G
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that
corresponds to your MySQL server version for the right syntax to use near 'use
index(col2) where col1 between 1 and 100 and col2 between 1 and 100' at line 1
  
mysql> EXPLAIN DELETE t1.* FROM t1 USE INDEX(col2) 
    -> WHERE col1 BETWEEN 1 AND 100 AND COL2 BETWEEN 1 AND 100\G
*************************** 1. row ***************************
           id: 1
  select_type: DELETE
        table: t1
   partitions: NULL
         type: range
possible_keys: col2
          key: col2
      key_len: 5
          ref: NULL
         rows: 72
     filtered: 11.11
        Extra: Using where
1 row in set, 1 warning (0.00 sec)