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

版本 9.0

8.4 当前
8.0
5.7

8.0 日语

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

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 9.0 支持索引级优化器提示 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)