摘要:在本教程中,您将学习如何使用 MySQL FORCE INDEX
强制查询优化器使用指定的命名索引。
查询优化器是MySQL数据库服务器中的一个组件,它为SQL语句制定最佳的执行计划。
查询优化器使用可用的统计信息来提出所有候选计划中成本最低的计划。
例如,查询可能请求价格在 10 到 80 之间的产品。如果统计数据显示 80% 的产品具有这些价格范围,那么它可能会认为全表扫描是最有效的。但是,如果统计数据显示很少有产品具有这些价格范围,那么读取索引然后进行表访问可能比全表扫描更快、更高效。
如果查询优化器忽略索引,您可以使用FORCE INDEX
提示来指示它使用索引。
下面说明了FORCE INDEX
提示语法:
SELECT *
FROM table_name
FORCE INDEX (index_list)
WHERE condition;
Code language: SQL (Structured Query Language) (sql)
在此语法中,将FORCE INDEX
子句放在 FROM 子句后面,后跟查询优化器必须使用的命名索引列表。
MySQL 强制索引示例
我们将使用示例数据库中的products
表进行演示。
以下语句显示了products
表的索引:
SHOW INDEXES FROM products;
Code language: SQL (Structured Query Language) (sql)
要查找价格在 10 到 80 之间的产品,请使用以下语句:
SELECT
productName,
buyPrice
FROM
products
WHERE
buyPrice BETWEEN 10 AND 80
ORDER BY buyPrice;
Code language: SQL (Structured Query Language) (sql)
正如您所猜测的,为了返回产品,查询优化器必须扫描整个表,因为buyPrice
列没有可用的索引:
EXPLAIN SELECT
productName,
buyPrice
FROM
products
WHERE
buyPrice BETWEEN 10 AND 80
ORDER BY buyPrice;
Code language: SQL (Structured Query Language) (sql)
让我们为buyPrice
列创建一个索引:
CREATE INDEX idx_buyprice ON products(buyPrice);
Code language: SQL (Structured Query Language) (sql)
并再次执行查询:
EXPLAIN SELECT
productName,
buyPrice
FROM
products
WHERE
buyPrice BETWEEN 10 AND 80
ORDER BY buyPrice;
Code language: SQL (Structured Query Language) (sql)
令人惊讶的是,即使buyPrice
列存在索引,查询优化也没有使用该索引。原因是查询返回了products
表的 110 行中的 94 行,因此,查询优化器决定执行全表扫描。
要强制查询优化器使用idx_buyprice
索引,请使用以下查询:
SELECT
productName, buyPrice
FROM
products
FORCE INDEX (idx_buyPrice)
WHERE
buyPrice BETWEEN 10 AND 80
ORDER BY buyPrice;
Code language: SQL (Structured Query Language) (sql)
这次,索引用于查找产品,如以下EXPLAIN
语句所示:
EXPLAIN SELECT
productName, buyPrice
FROM
products
FORCE INDEX (idx_buyprice)
WHERE
buyPrice BETWEEN 10 AND 80
ORDER BY buyPrice;
Code language: SQL (Structured Query Language) (sql)
这是输出:
在本教程中,您学习了如何使用 MySQL FORCE INDEX
提示来强制查询优化器使用命名索引列表。