mysql use index_MySQL USE INDEX

MySQL USE INDEX

简介：在本教程中，您将学习如何使用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示例

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

+------------------------+

| customers |

+------------------------+

| customerNumber |

| customerName |

| contactLastName |

| contactFirstName |

| phone |

| addressLine1 |

| addressLine2 |

| city |

| state |

| postalCode |

| country |

| salesRepEmployeeNumber |

| creditLimit |

+------------------------+

13 rows in set (0.08 sec)

首先，使用customers表的所有索引   ：

SHOW INDEXES FROM customers;

+-----------+------------+------------------------+--------------+------------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+

| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |

+-----------+------------+------------------------+--------------+------------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+

| customers | 0 | PRIMARY | 1 | customerNumber | A | 122 | NULL | NULL | | BTREE | | | YES | NULL |

| customers | 1 | salesRepEmployeeNumber | 1 | salesRepEmployeeNumber | A | 16 | NULL | NULL | YES | BTREE | | | YES | NULL |

+-----------+------------+------------------------+--------------+------------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+

2 rows in set (0.03 sec)

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

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%' \G;

以下显示了语句的输出：

*************************** 1. row ***************************

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: 152,152

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.01 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%' \G;

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

以下说明输出：

*************************** 1. row ***************************

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: 152,152

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.55 sec)

这些是变化：

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

键列有idx_name_fl和idx_name_lf。这意味着查询优化器使用推荐的索引。

USE INDEX是万一有用EXPLAIN的查询优化器使用了错误的指数从可能的索引列表中显示。

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