什么是聯(lián)合索引？

首先，要理解最左匹配原則，得先知道什么是聯(lián)合索引。

• 單列索引：只針對一個表列創(chuàng)建的索引。例如，為 users 表的 name 字段創(chuàng)建一個索引。
• 聯(lián)合索引：也叫復(fù)合索引，是針對多個表列創(chuàng)建的索引。例如，為 users 表的 (last_name, first_name) 兩個字段創(chuàng)建一個聯(lián)合索引。

這個索引的結(jié)構(gòu)可以想象成類似于電話簿或字典。電話簿是先按姓氏排序，在姓氏相同的情況下，再按名字排序。你無法直接跳過姓氏，快速找到一個特定的名字。

什么是最左匹配原則？

最左匹配原則指的是：在使用聯(lián)合索引進行查詢時，MySQL/SQL數(shù)據(jù)庫從索引的最左前列開始，并且不能跳過中間的列，一直向右匹配，直到遇到范圍查詢（>、<、BETWEEN、LIKE）就會停止匹配。

這個原則決定了你的 SQL 查詢語句是否能夠使用以及如何高效地使用這個聯(lián)合索引。

核心要點：

1. 從左到右：索引的使用必須從最左邊的列開始。
2. 不能跳過：不能跳過聯(lián)合索引中的某個列去使用后面的列。
3. 范圍查詢右停止：如果某一列使用了范圍查詢，那么它右邊的列將無法使用索引進行進一步篩選。

舉例說明

假設(shè)我們有一個 users 表，并創(chuàng)建了一個聯(lián)合索引 idx_name_age，包含 (last_name, age) 兩個字段。

索引 idx_name_age 在磁盤上大致是這樣排序的（先按 last_name 排序，last_name 相同再按 age 排序）：

(Li, 30)
(Wang, 20)
(Wang, 22)
(Zhang, 25)
(Zhang, 28)

現(xiàn)在，我們來看不同的查詢場景：

?場景一：完全匹配最左列

SELECT * FROM users WHERE last_name = 'Wang';

• 分析：查詢條件包含了索引的最左列 last_name。
• 索引使用情況：? 可以使用索引。數(shù)據(jù)庫可以快速在索引樹中找到所有 last_name = 'Wang' 的記錄（(Wang, 20) 和 (Wang, 22)）。

?場景二：匹配所有列

SELECT * FROM users WHERE last_name = 'Wang' AND age = 22;

• 分析：查詢條件包含了索引的所有列，并且順序與索引定義一致。
• 索引使用情況：? 可以高效使用索引。數(shù)據(jù)庫先定位到 last_name = 'Wang'，然后在這些結(jié)果中快速找到 age = 22 的記錄。

?場景三：匹配最左連續(xù)列

SELECT * FROM users WHERE last_name = 'Zhang';

• 分析：雖然只用了 last_name，但它是索引的最左列。
• 索引使用情況：? 可以使用索引。和場景一類似。

?場景四：跳過最左列

SELECT * FROM users WHERE age = 25;

• 分析：查詢條件沒有包含索引的最左列 last_name。
• 索引使用情況：? 無法使用索引。這就像讓你在電話簿里直接找所有叫“偉”的人，你必須翻遍整個電話簿，也就是全表掃描。

??場景五：包含最左列，但中間有斷檔

-- 假設(shè)我們有一個三個字段的索引 (col1, col2, col3)
-- 查詢條件為 WHERE col1 = 'a' AND col3 = 'c';

• 分析：雖然包含了最左列 col1，但跳過了 col2 直接查詢 col3。
• 索引使用情況：? 部分使用索引。數(shù)據(jù)庫只能使用 col1 來縮小范圍，找到所有 col1 = 'a' 的記錄。對于 col3 的過濾，它無法利用索引，需要在第一步的結(jié)果集中進行逐行篩選。

??場景六：最左列是范圍查詢

SELECT * FROM users WHERE last_name > 'Li' AND age = 25;

• 分析：最左列 last_name 使用了范圍查詢 >。
• 索引使用情況：? 部分使用索引。數(shù)據(jù)庫可以使用索引找到所有 last_name > 'Li' 的記錄（即從 Wang 開始往后的所有記錄）。但是，對于 age = 25 這個條件，由于 last_name 已經(jīng)是范圍匹配，age 列在索引中是無序的，因此數(shù)據(jù)庫無法再利用索引對 age 進行快速篩選，只能在 last_name > 'Li' 的結(jié)果集中逐行檢查 age。

總結(jié)與最佳實踐

最左匹配原則的本質(zhì)是由索引的數(shù)據(jù)結(jié)構(gòu)（B+Tree） 決定的。索引按照定義的字段順序構(gòu)建，所以必須從最左邊開始才能利用其有序性。

如何設(shè)計好的聯(lián)合索引？

1. 高頻查詢優(yōu)先：將最常用于 WHERE 子句的列放在最左邊。
2. 等值查詢優(yōu)先：將經(jīng)常進行等值查詢（=）的列放在范圍查詢（>， <， LIKE）的列左邊。
3. 覆蓋索引：如果查詢的所有字段都包含在索引中（即覆蓋索引），即使不符合最左前綴，數(shù)據(jù)庫也可能直接掃描索引來避免回表，但這通常發(fā)生在二級索引掃描中，效率依然不如最左匹配。

到此這篇關(guān)于深入理解MySQL聯(lián)合索引最左匹配原則的文章就介紹到這了,更多相關(guān)MySQL聯(lián)合索引最左匹配內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

最新評論