1.索引覆蓋（Covering Index）

索引覆蓋的意思就是直接從索引中獲取所有需要的字段，跳過回表（即根據(jù)索引找到主鍵后再去數(shù)據(jù)表查一遍）的步驟。
EXPLAIN標志：Using index。
需要SELECT的字段全部被包含在使用的索引中。

• 典型場景：需要頻繁查詢某幾個固定字段，且這些字段能組成一個復合索引。

• 應(yīng)用示例：例如，一個商品摘要的列表頁只需要 name 和 image、price字段。

  -- 查詢所需字段 (name, image,price) 完全被索引 idx_product 覆蓋
  EXPLAIN SELECT name, image,price FROM product WHERE p_id = 10086;

效果：如果使用SELECT *，MySQL需要先通過idx_product 找到主鍵，再回表到數(shù)據(jù)頁獲取其他字段（如amount, note），多了一次隨機I/O。而使用索引覆蓋，數(shù)據(jù)直接從索引頁返回，性能更高.

2.索引下推(Index Condition Pushdown, ICP)

索引下推的意思就是將WHERE條件中可被索引覆蓋的部分下推到存儲引擎層進行過濾，減少存儲引擎層向上層（Server層）返回的數(shù)據(jù)量，從而減少回表次數(shù)。
適用于二級索引，且WHERE條件中包含索引中非最左前綴列的過濾條件。
EXPLAIN標志：Using index condition。

-- idx_status  (p_id, status) 用于查找，amount > 100 在索引中無法判斷，但會被“下推”
SELECT * FROM product WHERE p_id = 10086 AND status = 'PAID' AND amount > 1000;

傳統(tǒng)方式會先通過索引找到所有p_id=10086 AND status='PAID'的行的主鍵，然后逐一回表檢查amount > 1000。啟用ICP后，MySQL會在存儲引擎層利用索引遍歷數(shù)據(jù)，如果某行不滿足amount > 1000，就直接跳過，連主鍵ID都不會取，更不會發(fā)起回表請求。這顯著減少了無效回表的次數(shù)。

3.索引合并 (Index Merge)

查詢條件中包含了多個字段，且每個字段都有獨立的單列索引，但沒有合適的復合索引。
Using intersect(...), Using union(...), Using sort_union(...)
僅適用于單表，不適用于全文索引
對同一個表使用多個索引，分別掃描后將結(jié)果合并（取交集、并集等）

-- WHERE 條件中使用了 OR 連接兩個不同索引的字段
SELECT * FROM product WHERE p_id = 10086 OR create_time > '2024-01-01';

MySQL會同時使用idx_product（雖然此索引包含p_id列）和idx_create_time這兩個索引，分別找到滿足各自條件的行的主鍵ID，然后將這兩個ID集合合并（取并集），最后再回表。這避免了只能使用其中一個索引而導致另一個條件被全表掃描的情況。
注意：有可能會碰到死鎖情況。
循環(huán)更新操作。Index Merge的兩個掃描路徑(路徑1和路徑2)的執(zhí)行可能存在微小的時序差異,導致不同事務(wù)在相同SQL下的鎖獲取順序不同。

具體來說:

• 事務(wù)A循環(huán)更新M1時,可能先掃描idx_material_id,再掃描idx_delivery_docs_no
• 事務(wù)B循環(huán)更新M2時,可能先掃描idx_delivery_docs_no,再掃描idx_material_id
• 這種差異雖然不影響最終結(jié)果,但會影響加鎖的先后順序
• type: index_merge ← 確認使用了Index Merge
• key: idx_picking_docs_no,idx_material_id ← 同時使用兩個索引

優(yōu)化方案：
使用聯(lián)合索引 （最佳），批量更新操作，強制使用索引。

核心要點

1. Index Merge不是萬能的: 雖然能優(yōu)化某些場景,但也可能帶來鎖順序不確定性
2. 聯(lián)合索引優(yōu)于Index Merge: 對于固定的多條件查詢,聯(lián)合索引是最佳選擇
3. 批量操作優(yōu)于循環(huán): 無論索引如何,批量SQL都比循環(huán)UPDATE性能更好
4. EXPLAIN是好幫手: 出現(xiàn)性能問題時,第一時間查看執(zhí)行計劃

避免Index Merge導致問題的建議

1. 優(yōu)先建立聯(lián)合索引: 根據(jù)高頻查詢的WHERE條件設(shè)計
2. 避免過多單列索引: 容易誤導優(yōu)化器
3. 使用索引提示: 必要時強制使用指定索引
4. 批量操作代替循環(huán): 減少SQL執(zhí)行次數(shù)
5. 定期分析執(zhí)行計劃: 及時發(fā)現(xiàn)Index Merge的不合理使用

4.索引跳躍掃描 (Index Skip Scan, ISS)

Using index for skip scan。
MySQL 8.0.13 引入，適用于前導列唯一值較少的場景。
查詢條件不包含聯(lián)合索引的最左前綴列時，也能使用該索引。

-- 查詢條件 status = 'PAID' 跳過了聯(lián)合索引 idx_user_status 的最左列 user_id
SELECT * FROM product WHERE status = 'PAID';

在MySQL 8.0之前，此查詢無法使用idx_status。ISS優(yōu)化器會“智能地”將其拆分為若干個子查詢，例如：SELECT ... WHERE user_id=1 AND status='PAID' UNION SELECT ... WHERE user_id=2 AND status='PAID' ...，從而有效利用索引。但它的生效前提是，被跳過的列（user_id）的不同取值數(shù)量要比較少。

多范圍讀取 (Multi-Range Read)

Using MRR
主要用于范圍查詢和Join操作
將二級索引查到的主鍵ID（Row IDs）先放入緩沖區(qū)排序，再按順序回表訪問。

-- 通過 create_time 索引進行范圍查詢，可能得到大量主鍵ID
SELECT * FROM product WHERE create_time BETWEEN '2026-01-01' AND '2026-01-31';

idx_create_time索引中存儲的主鍵ID（p_id）可能是無序的。如果直接回表，會導致大量的隨機I/O。啟用MRR后，MySQL會先將這些主鍵ID收集到內(nèi)存的read_rnd_buffer中并排序，然后再按順序批量回表訪問數(shù)據(jù)頁，將隨機I/O轉(zhuǎn)化為順序I/O，極大提升了I/O效率.

到此這篇關(guān)于Mysql索引下推、索引跳躍、索引覆蓋的具體使用的文章就介紹到這了,更多相關(guān)Mysql索引下推、索引跳躍、索引覆蓋內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

最新評論