MySQL USE INDEX Hint

摘要：在本教程中，您将学习如何使用 MySQL USE INDEX提示指示查询优化器仅对查询使用命名索引列表。

MySQL USE INDEX 提示简介

在MySQL中，当你提交一个SQL查询时，查询优化器会尝试制定一个最优的查询执行计划。

为了确定最佳计划，查询优化器使用许多参数。选择使用哪个索引的最重要参数之一是存储密钥分布，也称为基数。

然而，基数可能不准确，例如在表因多次插入或删除而被大量修改的情况下。

要解决此问题，您应该定期运行ANALYZE TABLE语句来更新基数。

此外，MySQL 提供了另一种方法，允许您使用名为USE INDEX的索引提示来推荐查询优化器应该使用的索引。

下面说明了 MySQL USE INDEX提示的语法：

SELECT select_list
FROM table_name USE INDEX(index_list)
WHERE condition;
Code language: SQL (Structured Query Language) (sql)

在此语法中， USE INDEX指示查询优化器使用指定索引之一来查找表中的行。

请注意，当您建议使用索引时，查询优化器可能会根据它提出的查询计划决定使用或不使用它们。

MySQL 使用索引示例

我们将使用示例数据库中的客户表进行演示。

首先，使用SHOW INDEXES语句显示customers表的所有索引：

SHOW INDEXES FROM customers;
Code language: SQL (Structured Query Language) (sql)

其次，创建四个索引，如下：

CREATE INDEX idx_c_ln  ON customers(contactLastName);
CREATE INDEX idx_c_fn ON customers(contactFirstName);
CREATE INDEX idx_name_fl  ON customers(contactFirstName,contactLastName);
CREATE INDEX idx_name_lf  ON customers(contactLastName,contactFirstName);
Code language: SQL (Structured Query Language) (sql)

第三，查找联系人名字或联系人姓氏以字母 A 开头的客户。使用EXPLAIN语句检查使用了哪些索引：

EXPLAIN SELECT *
FROM
    customers
WHERE
    contactFirstName LIKE 'A%'
        OR contactLastName LIKE 'A%';
Code language: SQL (Structured Query Language) (sql)

下面显示了该语句的输出：

           id: 1
  select_type: SIMPLE
        table: customers
   partitions: NULL
         type: index_merge
possible_keys: idx_c_ln,idx_c_fn,idx_name_fl,idx_name_lf
          key: idx_c_fn,idx_c_ln
      key_len: 52,52
          ref: NULL
         rows: 16
     filtered: 100.00
        Extra: Using sort_union(idx_c_fn,idx_c_ln); Using where
1 row in set, 1 warning (0.00 sec)
Code language: SQL (Structured Query Language) (sql)

如您所见，查询优化器使用了idx_c_fn和idx_c_ln索引。

第四，如果您认为使用idx_c_fl和idx_c_lf索引更好，则可以使用USE INDEX子句，如下所示：

EXPLAIN SELECT *
FROM
    customers
USE INDEX (idx_name_fl, idx_name_lf)
WHERE
    contactFirstName LIKE 'A%'
        OR contactLastName LIKE 'A%';
Code language: SQL (Structured Query Language) (sql)

请注意，这仅用于演示目的，而不是最佳选择。

下图说明了输出：

           id: 1
  select_type: SIMPLE
        table: customers
   partitions: NULL
         type: index_merge
possible_keys: idx_name_fl,idx_name_lf
          key: idx_name_fl,idx_name_lf
      key_len: 52,52
          ref: NULL
         rows: 16
     filtered: 100.00
        Extra: Using sort_union(idx_name_fl,idx_name_lf); Using where
1 row in set, 1 warning (0.00 sec)
Code language: SQL (Structured Query Language) (sql)

这些是变化：

• possible_keys列仅列出USE INDEX子句中指定的索引。
• 键列同时具有idx_name_fl和idx_name_lf 。这意味着查询优化器改用了推荐的索引。

如果EXPLAIN显示查询优化器使用了可能索引列表中的错误索引，则USE INDEX非常有用。

在本教程中，您学习了如何使用 MySQL USE INDEX提示来指示查询优化器使用指定索引的唯一列表来查找表中的行。