MySQL USE INDEX提示

时间:2019-11-20 08:52:15  来源:igfitidea点击:

简介:在本教程中,您将学习如何使用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;

使用这种语法,USE INDEX指示查询优化器使用命名索引之一在表中查找行。

请注意,当您建议使用索引时,查询优化器可能会决定是否使用它们,具体取决于它附带的查询计划。

MySQL USE INDEX示例

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

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

SHOW INDEXES FROM customers;

其次,创建四个索引,如下所示:

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

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

EXPLAIN SELECT *
FROM
    customers
WHERE
    contactFirstName LIKE 'A%'
        OR contactLastName LIKE 'A%';

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

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)

如您所见,查询优化器使用了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%';

请注意,这只是出于演示目的,尽管不是最佳选择。

以下说明了输出:

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)

这些是更改:

  • Possible_Keys列仅列出USE INDEX子句中指定的索引。

  • 密钥列同时具有idx_name_fl和idx_name_lf。
    这意味着查询优化器改为使用推荐的索引。

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

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