MySQL强制索引中USE/FORCEINDEX用法与避坑

MySQL 的查询优化器会根据统计信息（如基数、数据分布）自动选择它认为 “最优” 的索引。但有时它的判断可能不准，这时就需要我们手动干预，这时候就用到这两个语法：

一、USE INDEX 语法

SELECT * FROM customer USE INDEX (idx_last_name_first_name) -- 放在 FROM 子句之后 WHERE last_name = 'BARBEE'; USE INDEX 后面跟着一个索引名列表，优化器只能从这个列表里选择。如果想让优化器忽略某些索引，可以用 IGNORE INDEX。

1、关键区别：USE(建议) vs FORCE(强制)

2、实战场景：什么时候用？

>>DESC customer; +-------------+-------------------+------+-----+-------------------+-----------------------------------------------+ | Field | Type | Null | Key | Default | Extra | +-------------+-------------------+------+-----+-------------------+-----------------------------------------------+ | customer_id | smallint unsigned | NO | PRI | NULL | auto_increment | | store_id | tinyint unsigned | NO | MUL | NULL | | | first_name | varchar(45) | NO | | NULL | | | last_name | varchar(45) | NO | MUL | NULL | | | email | varchar(50) | YES | | NULL | | | address_id | smallint unsigned | NO | MUL | NULL | | | active | tinyint(1) | NO | | 1 | | | create_date | datetime | NO | | NULL | | | last_update | timestamp | YES | | CURRENT_TIMESTAMP | DEFAULT_GENERATED on update CURRENT_TIMESTAMP | +-------------+-------------------+------+-----+-------------------+-----------------------------------------------+ 9 rows in set (0.00 sec)

场景一：优化器选错了索引

在创建idx_last_name 和 idx_last_name_first_name 两个索引后，

CREATE INDEX idx_last_name ON customer (last_name); CREATE INDEX idx_last_name_first_name ON customer (last_name, first_name);

用 EXPLAIN 语句查看以下查找姓氏为 BARBEE 的语句的执行计划，

EXPLAIN SELECT * FROM customer WHERE last_name = 'BARBEE';

但发现使用 idx_last_name_first_name 更好.

EXPLAIN SELECT * FROM customer USE INDEX(id_last_name_first_name) WHERE last_name = 'BARBEE';

就像例子里的情况：

场景二：优化器完全不用索引

当你的查询条件明明有索引，但优化器因为统计信息过时等原因，选择了全表扫描，导致查询极慢。这时就需要用 FORCE INDEX 来强制它使用索引。

二、FORCE INDEX 语法

MySQL 查询优化器会根据统计信息（如数据分布、基数）自动选择执行计划。但在某些情况下，它的判断可能 “短视”，导致性能不佳。所以FORCE INDEX 就是用来强制它必须使用你指定的索引。

这里有一个film表，显示其索引(配合下文浏览即可)：

+-------+------------+-----------------------------+--------------+----------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression | +-------+------------+-----------------------------+--------------+----------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+ | film | 0 | PRIMARY | 1 | film_id | A | 1000 | NULL | NULL | | BTREE | | | YES | NULL | | film | 1 | idx_title | 1 | title | A | 1000 | NULL | NULL | | BTREE | | | YES | NULL | | film | 1 | idx_fk_language_id | 1 | language_id | A | 1 | NULL | NULL | | BTREE | | | YES | NULL | | film | 1 | idx_fk_original_language_id | 1 | original_language_id | A | 1 | NULL | NULL | YES | BTREE | | | YES | NULL | +-------+------------+-----------------------------+--------------+----------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+ 4 rows in set (0.00 sec)

1、为什么优化器“不听话”？

案例：在film表查找语言为英语的影片（id = 1）：

SELECT * FROM film WHERE language_id = 1;

但在查看这个语句的执行计划的时候（用EXPLAIN语句）——

发现MySQL 查询优化器并没有使用 idx_fk_language_id 索引。这是因为 film 表中的所有影片都是英文影片，因此 MySQL 查询优化器指定全表扫描。

所以在这个例子中的 film 表：

优化器的逻辑是：当查询需要返回大部分数据时，全表扫描可能更高效，比走索引 + 回表的方式更快。

那么如果我们要用FORCE INDEX强制索引：

EXPLAIN SELECT * FROM film FORCE INDEX (id_fk_language_id) -- FORCE INDEX 后面跟着一个索引名列表，优化器必须从这个列表中选择一个; 当然如果列表中的索引不可用，查询会报错。 WHERE language_id = 1;

这个例子只是为了举例而用FORCE INDEX...

2、避坑

不要滥用：优先让优化器自己做决定，只有在确认它判断错误时才手动干预。验证性能：强制使用索引后，一定要用实际执行时间来验证性能是否真的提升了。但在这个例子中，强制使用索引反而可能更慢，因为需要回表读取所有行。覆盖索引：如果查询的所有列都在索引中（覆盖索引），强制使用索引通常是有益的；如果需要回表，就要谨慎。使用前务必用 EXPLAIN 分析，使用后务必验证性能。

到此这篇关于MySQL强制索引中USE/FORCE INDEX用法与避坑的文章就介绍到这了,