我要评论mysql 的查询优化器会根据统计信息（如基数、数据分布）自动选择它认为 “最优” 的索引。但有时它的判断可能不准，这时就需要我们手动干预，这时候就用到这两个语法：
- use index：给优化器一个 “建议列表”，告诉它 “你可以从这些索引里选一个”，但它最终可能还是不采纳。
- force index：给优化器一个 “强制命令”，告诉它 “你必须用这个索引”，没有商量余地。
一、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(强制)
| 特性 | use index | force index |
|---|---|---|
| 性质 | 建议（hint） | 强制（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';
就像例子里的情况：
- 表 customer 有两个索引：idx_last_name 和 idx_last_name_first_name。
- 查询 where last_name = 'barbee' 时，优化器选了 idx_last_name。
- 但你通过分析，认为 idx_last_name_first_name 更适合后续的排序或覆盖索引需求。
- 这时用 use index (idx_last_name_first_name) 来引导它。
场景二：优化器完全不用索引
当你的查询条件明明有索引，但优化器因为统计信息过时等原因，选择了全表扫描，导致查询极慢。这时就需要用 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 表：
- 表中有 idx_fk_language_id 索引，查询条件也是 where language_id = 1。
- 但优化器选择了全表扫描（type: all），因为它发现表中几乎所有行的 language_id 都是 1（英文电影）。
- 对它来说，全表扫描比走索引更快，因为索引回表的开销超过了收益！！！
优化器的逻辑是：当查询需要返回大部分数据时，全表扫描可能更高效，比走索引 + 回表的方式更快。
那么如果我们要用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用法与避坑的文章就介绍到这了,更多相关mysql强制索引内容请搜索代码网以前的文章或继续浏览下面的相关文章希望大家以后多多支持代码网！
发表评论