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

MySQL 8.4 参考手册 · 中文镜像

正文为中文，英文原文默认隐藏。点击任意中文段落即可展开 / 收起对应英文原文。

译文由机器翻译生成，仅供理解参考；有疑问请以官方英文原文为准。

MySQL 8.4 参考手册 / 10 优化 / 10.9 控制查询优化器 / 10.9.4 索引提示

中文点击中文段落可显示 / 隐藏英文原文本页译文覆盖 100%

10.9.4 索引提示10.9.4 Index Hints

索引提示为优化器提供有关在查询处理期间如何选择索引的信息。本文描述的索引提示与第 10.9.3 节，“优化器提示”中描述的优化器提示不同。索引提示和优化器提示可以单独使用，也可以一起使用。Index hints give the optimizer information about how to choose indexes during query processing. Index hints, described here, differ from optimizer hints, described in Section 10.9.3, “Optimizer Hints”. Index and optimizer hints may be used separately or together.

索引提示适用于SELECT和UPDATE语句。它们也适用于多表DELETE语句，但不适用于单表DELETE，如本节后面所示。Index hints apply to SELECT and UPDATE statements. They also work with multi-table DELETE statements, but not with single-table DELETE, as shown later in this section.

索引提示在表名之后指定。（有关在 SELECT 语句中指定表的一般语法，请参见第 15.2.13.2 节，“JOIN 子句”。）引用单个表（包括索引提示）的语法如下：Index hints are specified following a table name. (For the general syntax for specifying tables in a SELECT statement, see Section 15.2.13.2, “JOIN Clause”.) The syntax for referring to an individual table, including index hints, looks like this:

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 正在使用可能索引列表中的错误索引。The USE INDEX (index_list) hint tells MySQL to use only one of the named indexes to find rows in the table. The alternative syntax IGNORE INDEX (index_list) tells MySQL to not use some particular index or indexes. These hints are useful if EXPLAIN shows that MySQL is using the wrong index from the list of possible indexes.

FORCE INDEX 提示的作用类似于 USE INDEX (index_list)，区别在于假定表扫描的代价非常高。换句话说，只有在无法使用命名索引中的任何一个来查找表中的行时，才会使用表扫描。The FORCE INDEX hint acts like USE INDEX (index_list), with the addition that a table scan is assumed to be very expensive. In other words, a table scan is used only if there is no way to use one of the named indexes to find rows in the table.

Note

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 的未来版本中将被弃用，并在之后的某个时间点被完全移除。MySQL 8.4 supports the index-level optimizer hints JOIN_INDEX, GROUP_INDEX, ORDER_INDEX, and INDEX, which are equivalent to and intended to supersede FORCE INDEX index hints, as well as the NO_JOIN_INDEX, NO_GROUP_INDEX, NO_ORDER_INDEX, and NO_INDEX optimizer hints, which are equivalent to and intended to supersede IGNORE INDEX index hints. Thus, you should expect USE INDEX, FORCE INDEX, and IGNORE INDEX to be deprecated in a future release of MySQL, and at some time thereafter to be removed altogether.

这些索引级优化器提示支持单表和多表的 DELETE 语句。These index-level optimizer hints are supported with both single-table and multi-table DELETE statements.

更多信息，请参阅索引级优化器提示。For more information, see Index-Level Optimizer Hints.

每个提示需要索引名称，而不是列名称。要引用主键，请使用名称 PRIMARY。要查看表的索引名称，请使用 SHOW INDEX 语句或信息模式 STATISTICS 表。Each hint requires index names, not column names. To refer to a primary key, use the name PRIMARY. To see the index names for a table, use the SHOW INDEX statement or the Information Schema STATISTICS table.

一个 index_name 值不必是完整的索引名称。它可以是索引名称的明确前缀。如果前缀不明确，将会发生错误。An index_name value need not be a full index name. It can be an unambiguous prefix of an index name. If a prefix is ambiguous, an error occurs.

示例：Examples:

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;

索引提示的语法具有以下特征：The syntax for index hints has the following characteristics:

在 index_list 中省略 index_list 在语法上是有效的，这意味着 “不使用任何索引。” 在 FORCE INDEX 或 USE INDEX 中省略 IGNORE INDEX 是语法错误。It is syntactically valid to omit index_list for USE INDEX, which means “use no indexes.” Omitting index_list for FORCE INDEX or IGNORE INDEX is a syntax error.
你可以通过在索引提示中添加 FOR 子句来指定索引提示的作用范围。这提供了对优化器在查询处理各个阶段选择执行计划的更精细的控制。要仅影响 MySQL 决定如何查找表中的行以及如何处理连接时使用的索引，请使用 FOR JOIN。要影响排序或分组行时的索引使用，请使用 FOR ORDER BY 或 FOR GROUP BY。You can specify the scope of an index hint by adding a FOR clause to the hint. This provides more fine-grained control over optimizer selection of an execution plan for various phases of query processing. To affect only the indexes used when MySQL decides how to find rows in the table and how to process joins, use FOR JOIN. To influence index usage for sorting or grouping rows, use FOR ORDER BY or FOR GROUP BY.
您可以指定多个索引提示：You can specify multiple index hints:
```
SELECT * FROM t1 USE INDEX (i1) IGNORE INDEX FOR ORDER BY (i2) ORDER BY a;
```
在多个提示中（即使在同一个提示内）命名相同的索引并不是错误：It is not an error to name the same index in several hints (even within the same hint):
```
SELECT * FROM t1 USE INDEX (i1) USE INDEX (i1,i1);
```
然而，将 USE INDEX 和 FORCE INDEX 混合用于同一张表是一个错误：However, it is an error to mix USE INDEX and FORCE INDEX for the same table:
```
SELECT * FROM t1 USE INDEX FOR JOIN (i1) FORCE INDEX FOR JOIN (i2);
```

如果索引提示中未包含 FOR 子句，则提示的作用范围是应用于语句的所有部分。例如，这个提示：If an index hint includes no FOR clause, the scope of the hint is to apply to all parts of the statement. For example, this hint:

IGNORE INDEX (i1)

等同于这种提示组合：is equivalent to this combination of hints:

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）收集到一个列表中。例如：When index hints are processed, they are collected in a single list by type (USE, FORCE, IGNORE) and by scope (FOR JOIN, FOR ORDER BY, FOR GROUP BY). For example:

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

等同于：is equivalent to:

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

然后索引提示按以下顺序应用于每个范围：The index hints then are applied for each scope in the following order:

{USE|FORCE} INDEX 如果存在则会被应用。（如果不存在，则使用优化器确定的索引集合。）{USE|FORCE} INDEX is applied if present. (If not, the optimizer-determined set of indexes is used.)
IGNORE INDEX 会在上一步结果上应用。例如，以下两个查询是等价的：IGNORE INDEX is applied over the result of the previous step. For example, the following two queries are equivalent:
```
SELECT * FROM t1 USE INDEX (i1) IGNORE INDEX (i2) USE INDEX (i2);

SELECT * FROM t1 USE INDEX (i1);
```

对于 FULLTEXT 搜索，索引提示的工作方式如下：For FULLTEXT searches, index hints work as follows:

对于自然语言模式搜索，索引提示会被默默忽略。例如，IGNORE INDEX(i1) 会被忽略且没有警告，索引仍然被使用。For natural language mode searches, index hints are silently ignored. For example, IGNORE INDEX(i1) is ignored with no warning and the index is still used.
对于布尔模式搜索，带有 FOR ORDER BY 或 FOR GROUP BY 的索引提示会被默默忽略。带有 FOR JOIN 或没有 FOR 修饰符的索引提示会被采纳。与非 FULLTEXT 搜索中提示的应用方式不同，该提示会用于查询执行的所有阶段（查找行并检索、分组和排序）。即使提示是针对非 FULLTEXT 索引给出的，这一规则仍然适用。For boolean mode searches, index hints with FOR ORDER BY or FOR GROUP BY are silently ignored. Index hints with FOR JOIN or no FOR modifier are honored. In contrast to how hints apply for non-FULLTEXT searches, the hint is used for all phases of query execution (finding rows and retrieval, grouping, and ordering). This is true even if the hint is given for a non-FULLTEXT index.
例如，以下两个查询是等效的：For example, the following two queries are equivalent:
```
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 语法时，如下所示：Index hints work with DELETE statements, but only if you use multi-table DELETE syntax, as shown here:

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)

首页上一级下一节