MySQL索引最左匹配原則

在使用 MySQL 數(shù)據(jù)庫(kù)進(jìn)行查詢(xún)優(yōu)化時(shí)，索引是一項(xiàng)至關(guān)重要的工具。理解索引的最左匹配原則及其底層實(shí)現(xiàn)原因，對(duì)于編寫(xiě)高效的 SQL 查詢(xún)至關(guān)重要。

本文將深入講解 MySQL 索引最左匹配原則，為什么不滿足最左匹配原則會(huì)導(dǎo)致索引失效，以及背后的底層原因。

什么是MySQL索引最左匹配原則？

MySQL 的索引最左匹配原則指的是：在聯(lián)合索引中，查詢(xún)條件必須從索引的最左邊的列開(kāi)始，且順序連續(xù)，才能有效使用索引。聯(lián)合索引是由多列字段組成的索引，最左匹配原則決定了 MySQL 使用索引的方式。

例如，如果有一個(gè)聯(lián)合索引 (A, B, C)，那么 MySQL 只有在查詢(xún)中使用 A 或 (A, B) 或 (A, B, C) 作為條件時(shí)，才能有效利用這個(gè)索引。這就是“最左匹配”的含義。

以下是最左匹配原則的幾種情況：

1. 使用 A：索引有效。
2. 使用 A 和 B：索引有效。
3. 只使用 B 或 C：索引失效。
4. 使用 B 和 C，但不使用 A：索引失效。
5. 使用 A 和 C：索引部分失效 A 走索引 C 不走索引。

為什么不滿足最左匹配原則索引會(huì)失效？

要理解為什么不滿足最左匹配原則時(shí)索引會(huì)失效，我們需要了解 MySQL 索引的底層數(shù)據(jù)結(jié)構(gòu)和查找過(guò)程。

1. 索引的數(shù)據(jù)結(jié)構(gòu) —— B+樹(shù)

MySQL 的 InnoDB 存儲(chǔ)引擎大多數(shù)情況下使用 B+樹(shù) 作為索引的數(shù)據(jù)結(jié)構(gòu)。B+樹(shù)是一種平衡樹(shù)結(jié)構(gòu)，適合于范圍查找和有序數(shù)據(jù)的存儲(chǔ)。每個(gè)節(jié)點(diǎn)按順序存儲(chǔ)鍵值，并且葉子節(jié)點(diǎn)之間使用鏈表連接，以便于范圍查詢(xún)。

當(dāng)我們創(chuàng)建一個(gè)聯(lián)合索引 (A, B, C) 時(shí)，MySQL 會(huì)以 A 作為最外層的鍵值，然后根據(jù) A 的值進(jìn)行進(jìn)一步排序，依次對(duì) B 和 C 進(jìn)行排序。這意味著在 B+樹(shù)中，數(shù)據(jù)的組織順序是 (A -> B -> C)。

2. 查找過(guò)程

• 當(dāng)我們按照 A 查詢(xún)時(shí)，MySQL 能在 B+樹(shù)中從根節(jié)點(diǎn)開(kāi)始，快速定位到對(duì)應(yīng)的 A 值，然后基于 A 的值進(jìn)一步向下查找相關(guān)的 B 和 C。
• 當(dāng)查詢(xún)條件包含 (A, B) 時(shí)，MySQL 仍然可以使用聯(lián)合索引從 A 查找到 B，并繼續(xù)查找 C。
• 但是如果直接跳過(guò) A，如只用 B 或 C 作為查詢(xún)條件，MySQL 無(wú)法通過(guò)聯(lián)合索引定位，因?yàn)樵?B+樹(shù)結(jié)構(gòu)中，沒(méi)有單獨(dú)針對(duì) B 或 C 的直接路徑。也就是說(shuō)，B+樹(shù)的索引是從左到右逐層組織的，跳過(guò)最左列會(huì)導(dǎo)致無(wú)法利用索引結(jié)構(gòu)進(jìn)行查找。

3. 順序和連續(xù)性的重要性

最左匹配原則的另一個(gè)核心是 順序的連續(xù)性。在 (A, B, C) 的聯(lián)合索引中，必須按照從 A 開(kāi)始的順序使用。例如，如果只使用 (A, C) 而不包含 B，則只有索引 A 生效。

這是因?yàn)?MySQL 無(wú)法跳過(guò) B 直接找到 C，B+樹(shù)中的節(jié)點(diǎn)存儲(chǔ)順序需要依次按照 (A -> B -> C) 進(jìn)行匹配。因此，查詢(xún)條件必須保持與索引順序一致且連續(xù)，才能有效利用索引。

索引失效的底層原因

總結(jié)來(lái)看，索引失效的底層原因主要?dú)w結(jié)為以下幾點(diǎn)：

• B+樹(shù)的結(jié)構(gòu)限制：B+樹(shù)的索引組織是自上而下、從左到右的。在聯(lián)合索引中，每一層（列）依賴(lài)于上一層的值才能定位到下一層的節(jié)點(diǎn)，因此跳過(guò)最左列會(huì)導(dǎo)致 MySQL 無(wú)法利用索引路徑。
• 查詢(xún)路徑的建立：MySQL 在查詢(xún)過(guò)程中會(huì)根據(jù)索引結(jié)構(gòu)建立一條從根節(jié)點(diǎn)到目標(biāo)葉子節(jié)點(diǎn)的路徑。這條路徑的建立是基于聯(lián)合索引的最左列開(kāi)始的，一旦查詢(xún)條件不滿足最左匹配，MySQL 就無(wú)法構(gòu)建這條路徑，只能進(jìn)行全表掃描或其他類(lèi)型的掃描。
• 排序和存儲(chǔ)方式：聯(lián)合索引的列是按順序依次排序和存儲(chǔ)的。查詢(xún)條件中包含的列必須保持與索引中列的順序一致，才能利用索引中的排序信息。否則，MySQL 會(huì)放棄使用索引，因?yàn)樗鼰o(wú)法有效利用已有的排序來(lái)加速查找。

如何避免索引失效？

• 遵循最左匹配原則：盡量按照聯(lián)合索引的順序來(lái)設(shè)計(jì)查詢(xún)條件，確保查詢(xún)從最左側(cè)的列開(kāi)始。
• 適當(dāng)設(shè)計(jì)索引：如果某些查詢(xún)經(jīng)常只使用聯(lián)合索引中的中間列，可以考慮創(chuàng)建單獨(dú)的索引，以避免這種查詢(xún)導(dǎo)致索引失效。
• 避免跳過(guò)列：在使用聯(lián)合索引時(shí)，不要跳過(guò)其中的某些列。例如，如果 (A, B, C) 是聯(lián)合索引，那么在查詢(xún)中包含 A 和 B，即使不需要 C，也能確保索引有效。

總結(jié)

MySQL 索引最左匹配原則是聯(lián)合索引的一個(gè)重要特性，其根本原因在于 B+樹(shù)的數(shù)據(jù)結(jié)構(gòu)和查詢(xún)路徑的構(gòu)建方式。理解最左匹配原則和索引失效的底層原因，可以幫助我們?cè)谠O(shè)計(jì)數(shù)據(jù)庫(kù)和編寫(xiě)查詢(xún)時(shí)更好地優(yōu)化性能，從而避免不必要的全表掃描。

有效使用索引能夠大大提高查詢(xún)效率，但需要確保查詢(xún)條件與索引的設(shè)計(jì)一致，特別是在使用聯(lián)合索引時(shí)，遵循最左匹配原則是關(guān)鍵。

以上為個(gè)人經(jīng)驗(yàn)，希望能給大家一個(gè)參考，也希望大家多多支持腳本之家。

最新評(píng)論