MySQL 强制索引

---

简介：在本教程中，您将学习如何使用MySQL FORCE INDEX强制查询优化器使用指定的命名索引。 查询优化器是MySQL数据库服务器中的一个组件，它为SQL语句提供最佳的执行计划。 查询优化器使用可用的统计信息来提出所有候选计划中成本最低的计划。 例如，查询可能会请求价格在10到80之间的产品。如果统计数据显示80％的产品具有这些价​​格范围，那么它可能会认为全表扫描效率最高。但是，如果统计数据显示很少有产品具有这些价​​格范围，那么读取索引后跟表访问可能比全表扫描更快，更有效。 如果查询优化器忽略索引，您可以使用FORCE INDEX提示来指示它使用索引。 以下说明了FORCE INDEX提示语法：

SELECT * 
FROM table_name 
FORCE INDEX (index_list)
WHERE condition;

在此语法中，您将FORCE INDEX子句放在FROM子句之后，后跟查询优化器必须使用的命名索引列表。

---

MySQL FORCE INDEX示例

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

+--------------------+
| products           |
+--------------------+
| productCode        |
| productName        |
| productLine        |
| productScale       |
| productVendor      |
| productDescription |
| quantityInStock    |
| buyPrice           |
| MSRP               |
+--------------------+
9 rows in set (0.15 sec)

下面的语句显示索引的products表：

SHOW INDEXES FROM products;

+----------+------------+-----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| Table    | Non_unique | Key_name        | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
+----------+------------+-----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| products |          0 | PRIMARY         |            1 | productCode | A         |         110 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
| products |          1 | productLine     |            1 | productLine | A         |           7 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
| products |          1 | idx_productname |            1 | productName | A         |         110 |       20 |   NULL |      | BTREE      |         |               | YES     | NULL       |
+----------+------------+-----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
3 rows in set (0.17 sec)

要查找价格在10到80之间的产品，请使用以下语句：

SELECT 
    productName, 
    buyPrice
FROM
    products
WHERE
    buyPrice BETWEEN 10 AND 80
ORDER BY buyPrice;

您可以猜测，要返回产品，查询优化器必须扫描整个表，因为buyPrice列没有可用的索引：

EXPLAIN SELECT 
    productName, 
    buyPrice
FROM
    products
WHERE
    buyPrice BETWEEN 10 AND 80
ORDER BY buyPrice;

+----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-----------------------------+
| id | select_type | table    | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra                       |
+----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-----------------------------+
|  1 | SIMPLE      | products | NULL       | ALL  | NULL          | NULL | NULL    | NULL |  110 |    11.11 | Using where; Using filesort |
+----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-----------------------------+
1 row in set, 1 warning (0.02 sec)

让我们为buyPrice列创建一个索引：

CREATE INDEX idx_buyprice ON products(buyPrice);

然后再次执行查询：

EXPLAIN SELECT 
    productName, 
    buyPrice
FROM
    products
WHERE
    buyPrice BETWEEN 10 AND 80
ORDER BY buyPrice;

+----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-----------------------------+
| id | select_type | table    | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra                       |
+----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-----------------------------+
|  1 | SIMPLE      | products | NULL       | ALL  | idx_buyprice  | NULL | NULL    | NULL |  110 |    85.45 | Using where; Using filesort |
+----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-----------------------------+
1 row in set, 1 warning (0.10 sec)

令人惊讶的是，buyPrice即使索引存在，查询优化也不使用列的索引。原因是查询返回products表中110行中的94行，因此，查询优化器决定执行全表扫描。 要强制查询优化器使用idx_buyprice索引，请使用以下查询：

SELECT 
    productName, buyPrice
FROM
    products 
FORCE INDEX (idx_buyPrice)
WHERE
    buyPrice BETWEEN 10 AND 80
ORDER BY buyPrice;

这次，索引用于查找产品，如下面的EXPLAIN语句所示：

EXPLAIN SELECT 
    productName, buyPrice
FROM
    products 
FORCE INDEX (idx_buyprice)
WHERE
    buyPrice BETWEEN 10 AND 80
ORDER BY buyPrice;

这是输出：

+----+-------------+----------+------------+-------+---------------+--------------+---------+------+------+----------+-----------------------+
| id | select_type | table    | partitions | type  | possible_keys | key          | key_len | ref  | rows | filtered | Extra                 |
+----+-------------+----------+------------+-------+---------------+--------------+---------+------+------+----------+-----------------------+
|  1 | SIMPLE      | products | NULL       | range | idx_buyprice  | idx_buyprice | 5       | NULL |   94 |   100.00 | Using index condition |
+----+-------------+----------+------------+-------+---------------+--------------+---------+------+------+----------+-----------------------+
1 row in set, 1 warning (0.10 sec)

在本教程中，您学习了如何使用MySQL FORCE INDEX提示强制查询优化器使用命名索引列表。

---