快捷導(dǎo)航

MySQL強(qiáng)制索引中USE/FORCE INDEX用法與避坑

更新時(shí)間：2026年03月10日 09:26:13 作者：Hoffer_

本文主要介紹了MySQL強(qiáng)制索引中USE/FORCE INDEX用法與避坑,文中通過(guò)示例代碼介紹的非常詳細(xì),對(duì)大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價(jià)值,需要的朋友們下面隨著小編來(lái)一起學(xué)習(xí)學(xué)習(xí)吧

MySQL 的查詢優(yōu)化器會(huì)根據(jù)統(tǒng)計(jì)信息（如基數(shù)、數(shù)據(jù)分布）自動(dòng)選擇它認(rèn)為 “最優(yōu)” 的索引。但有時(shí)它的判斷可能不準(zhǔn)，這時(shí)就需要我們手動(dòng)干預(yù)，這時(shí)候就用到這兩個(gè)語(yǔ)法：

USE INDEX：給優(yōu)化器一個(gè) “建議列表”，告訴它 “你可以從這些索引里選一個(gè)”，但它最終可能還是不采納。
FORCE INDEX：給優(yōu)化器一個(gè) “強(qiáng)制命令”，告訴它 “你必須用這個(gè)索引”，沒(méi)有商量余地。

一、USE INDEX 語(yǔ)法

SELECT *
FROM customer
USE INDEX (idx_last_name_first_name)  -- 放在 FROM 子句之后
WHERE last_name = 'BARBEE';

USE INDEX 后面跟著一個(gè)索引名列表，優(yōu)化器只能從這個(gè)列表里選擇。
如果想讓優(yōu)化器忽略某些索引，可以用 IGNORE INDEX。

1、關(guān)鍵區(qū)別：USE(建議) vs FORCE(強(qiáng)制)

特性	USE INDEX	FORCE INDEX
性質(zhì)	建議（Hint）	強(qiáng)制（Force）
優(yōu)化器態(tài)度	可以采納，也可以忽略	必須執(zhí)行，沒(méi)有選擇
適用場(chǎng)景	優(yōu)化器選錯(cuò)索引，但你有更好的候選	優(yōu)化器完全不使用索引，導(dǎo)致性能極差
風(fēng)險(xiǎn)	低，只是提供選項(xiàng)	高，強(qiáng)制使用可能導(dǎo)致更差的性能

2、實(shí)戰(zhàn)場(chǎng)景：什么時(shí)候用？

>>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)

場(chǎng)景一：優(yōu)化器選錯(cuò)了索引

在創(chuàng)建idx_last_name 和 idx_last_name_first_name 兩個(gè)索引后，

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

用 EXPLAIN 語(yǔ)句查看以下查找姓氏為 BARBEE 的語(yǔ)句的執(zhí)行計(jì)劃，

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

但發(fā)現(xiàn)使用 idx_last_name_first_name 更好.

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

就像例子里的情況：

表 customer 有兩個(gè)索引：idx_last_name 和 idx_last_name_first_name。
查詢 WHERE last_name = 'BARBEE' 時(shí)，優(yōu)化器選了 idx_last_name。
但你通過(guò)分析，認(rèn)為 idx_last_name_first_name 更適合后續(xù)的排序或覆蓋索引需求。
這時(shí)用 USE INDEX (idx_last_name_first_name) 來(lái)引導(dǎo)它。

場(chǎng)景二：優(yōu)化器完全不用索引

當(dāng)你的查詢條件明明有索引，但優(yōu)化器因?yàn)榻y(tǒng)計(jì)信息過(guò)時(shí)等原因，選擇了全表掃描，導(dǎo)致查詢極慢。這時(shí)就需要用 FORCE INDEX 來(lái)強(qiáng)制它使用索引。

二、FORCE INDEX 語(yǔ)法

MySQL 查詢優(yōu)化器會(huì)根據(jù)統(tǒng)計(jì)信息（如數(shù)據(jù)分布、基數(shù)）自動(dòng)選擇執(zhí)行計(jì)劃。但在某些情況下，它的判斷可能 “短視”，導(dǎo)致性能不佳。所以FORCE INDEX 就是用來(lái)強(qiáng)制它必須使用你指定的索引。

這里有一個(gè)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、為什么優(yōu)化器“不聽(tīng)話”？

案例：在film表查找語(yǔ)言為英語(yǔ)的影片（id = 1）：

SELECT *
FROM film
WHERE language_id = 1;

但在查看這個(gè)語(yǔ)句的執(zhí)行計(jì)劃的時(shí)候（用EXPLAIN語(yǔ)句）——

發(fā)現(xiàn)MySQL 查詢優(yōu)化器并沒(méi)有使用 idx_fk_language_id 索引。這是因?yàn)?film 表中的所有影片都是英文影片，因此 MySQL 查詢優(yōu)化器指定全表掃描。

所以在這個(gè)例子中的 film 表：

表中有 idx_fk_language_id 索引，查詢條件也是 WHERE language_id = 1。
但優(yōu)化器選擇了全表掃描（type: ALL），因?yàn)樗l(fā)現(xiàn)表中幾乎所有行的 language_id 都是 1（英文電影）。
對(duì)它來(lái)說(shuō)，全表掃描比走索引更快，因?yàn)樗饕乇淼拈_(kāi)銷超過(guò)了收益?。?！

優(yōu)化器的邏輯是：當(dāng)查詢需要返回大部分?jǐn)?shù)據(jù)時(shí)，全表掃描可能更高效，比走索引 + 回表的方式更快。

那么如果我們要用FORCE INDEX強(qiáng)制索引：

EXPLAIN
SELECT * 
FROM film
FORCE INDEX (id_fk_language_id) -- FORCE INDEX 后面跟著一個(gè)索引名列表，優(yōu)化器必須從這個(gè)列表中選擇一個(gè); 當(dāng)然如果列表中的索引不可用，查詢會(huì)報(bào)錯(cuò)。
WHERE language_id = 1;

這個(gè)例子只是為了舉例而用FORCE INDEX...

2、避坑

不要濫用：優(yōu)先讓優(yōu)化器自己做決定，只有在確認(rèn)它判斷錯(cuò)誤時(shí)才手動(dòng)干預(yù)。
驗(yàn)證性能：強(qiáng)制使用索引后，一定要用實(shí)際執(zhí)行時(shí)間來(lái)驗(yàn)證性能是否真的提升了。但在這個(gè)例子中，強(qiáng)制使用索引反而可能更慢，因?yàn)樾枰乇碜x取所有行。
覆蓋索引：如果查詢的所有列都在索引中（覆蓋索引），強(qiáng)制使用索引通常是有益的；如果需要回表，就要謹(jǐn)慎。
使用前務(wù)必用 EXPLAIN 分析，使用后務(wù)必驗(yàn)證性能。

到此這篇關(guān)于MySQL強(qiáng)制索引中USE/FORCE INDEX用法與避坑的文章就介紹到這了,更多相關(guān)MySQL強(qiáng)制索引內(nèi)容請(qǐng)搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

您可能感興趣的文章: