索引提示

来自泡泡学习笔记
跳到导航 跳到搜索

索引提示在查询处理期间向优化器提供如何选择索引的信息。索引和优化器提示可以单独使用或一起使用。

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


索引提示在表名之后指定。包含索引提示的单个表的语法如下所示： 

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），但增加了一个假设表扫描非常昂贵的条件。换句话说，只有在无法使用命名索引中的任何一个来查找表中的行时，才会使用表扫描。


每个提示都需要索引名称，而不是列名称。要引用主键，请使用名称 PRIMARY。要查看表中的索引名称，请使用 SHOW INDEX 语句或 Information Schema 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)


  • 在 MySQL 5.0 中，没有 FOR 子句的提示范围仅适用于行检索。为了在没有 FOR 子句时使服务器使用旧的行为方式，请在服务器启动时启用旧的系统变量。在复制设置中要小心启用此变量。对于基于语句的二进制日志，源端和复制实例可能具有不同的模式，这可能会导致复制错误。


  • 当处理索引提示时，它们会被按类型（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);

然后，对于每个范围，按照以下顺序应用索引提示：

  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搜索，索引提示的工作方式如下：
  1. 对于自然语言模式搜索，索引提示将被静默忽略。例如，IGNORE INDEX(i1)将被静默忽略，不会发出警告，并且索引仍然会被使用。
  2. 对于布尔模式搜索，带有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)
取自“http://bubblestudy.info/wiki/index.php?title=索引提示&oldid=3208”