聯(lián)合索引的排序邏輯：

• 聯(lián)合索引是多列組合的索引（如 idx(a, b, c)），其底層 B + 樹的排序規(guī)則是：先按列 a 排序 → a 值相同的行，再按列 b 排序 → a 和 b 都相同的行，最后按列 c 排序?？梢岳斫鉃椋核饕龢涫?“先按第一把鑰匙排序，第一把相同再按第二把，以此類推”。

非等值的范圍條件：

• 常見操作符：>、<、between、like '%xx'（注意：in 是 “等值多值查詢”，不算范圍條件；>=、<= 雖屬于范圍，但本質(zhì)是 “包含邊界的 「 等值 」 延伸”，優(yōu)化器更易利用）。

范圍條件對索引的影響：

• 聯(lián)合索引的 “可用性” 依賴于 前綴列的有序性 —— 只有前一列是 “等值匹配” 時，后一列的索引排序才有意義。一旦某一列用了 “非等值范圍條件”，其右側(cè)所有列的索引排序會直接 “混亂”，優(yōu)化器會放棄使用這些右側(cè)列的索引部分。

舉例

假設(shè)數(shù)據(jù)如下：

(name='張三', age=20, score=85)
(name='張三', age=20, score=90)
(name='張三', age=22, score=80)
(name='李四', age=19, score=95)
(name='李四', age=21, score=88)

情況一：

select * from student where name='張三' and age=20 and score=90;

• 邏輯：先通過 name='張三'（等值）定位到前 3 行 → 再通過 age=20（等值）定位到前 2 行 → 最后通過 score=90（等值）精準(zhǔn)命中目標(biāo)行。
• 結(jié)論：索引 idx(name, age, score) 全列有效。

情況二：

select * from student where name='張三' and age>20 and score=80;

• 邏輯：name='張三'（等值）定位到前 3 行 → age>20（非等值范圍條件）篩選出 age=22 的 1 行（此時，age>20 的部分中，score 沒有有序性可言 —— 因?yàn)槿绻懈鄶?shù)據(jù)，age=23 的 score 可能是 70，age=24 的 score 可能是 90，完全無序）。
• 結(jié)論：score 列的索引失效，優(yōu)化器只能用 name 和 age 的索引，score=80 需在篩選后的結(jié)果中 “逐行判斷”（無法利用索引快速查找）。

情況三：

select * from student where name='張三' and age>20 and score=80;

• 邏輯：同情況 2，age 是范圍列，其右側(cè)的 score 索引失效。

為輔助理解，這里補(bǔ)充說明：若查詢條件為where name='張三' and age>=20，age>20的部分索引仍會失效，只有age=20的部分查找時可以用到索引。

總結(jié)

>=、<= 能保留 “前綴等值部分的有序性”，而 >、< 會破壞邊界的等值連續(xù)性，導(dǎo)致索引選擇性更差，甚至完全失效。盡量使用大于等于（>=）或小于等于（<=）。

注意事項(xiàng)補(bǔ)充

1. 單列索引無此問題：只有聯(lián)合索引才有 “左側(cè) / 右側(cè)列” 的概念，單列索引無論用 >、< 還是 >=、<=，都能正常利用索引；
2. in 不算非等值范圍條件：in 是 “等值多值查詢”，比如 name in ('張三', '李四') and age=20，age 仍能利用索引（因?yàn)?name 雖多值但仍是等值匹配，age 排序有效）；
3. 范圍列盡量放聯(lián)合索引右側(cè)：設(shè)計聯(lián)合索引時，若一定要用到非等值范圍條件，應(yīng)將用非等值范圍條件的列放在最后，避免影響左側(cè)列的索引可用性；
4. 覆蓋索引可緩解失效影響：如果查詢的列都在聯(lián)合索引中（如 select name, age, score from ...），即使右側(cè)列失效，優(yōu)化器仍會用 “索引全掃描”（無需回表），效率依然高于全表掃描。

聯(lián)合索引中，什么時候索引是有效的，什么時候所以是無效的？

注意：是不是使用索引，和查詢條件的順序無關(guān)（優(yōu)化器會自動調(diào)整條件的順序），但和這些字段的查詢手段有關(guān)

例子：建立了abc的聯(lián)合索引，相當(dāng)于建立了 a的單列索引，ab的聯(lián)合索引，以及abc的聯(lián)合索引

情況一：模糊查詢生效失效的情況

一般根據(jù)最左匹配的原則，但在遇到范圍查詢后，匹配終止，也就是說，當(dāng)條件為：

a like ‘%str%’ 或者 a like ‘%str’ 時，不走索引；

當(dāng)條件為 a like ‘str%’ 或者 “>”, “<”, "between"時， 僅使用了聯(lián)合索引中a的部分

b，c 同理，根據(jù)查詢方式不同，即便條件中的3個字段都在索引里，也不一定使用了全索引

假如條件是 a = 1 and b = 2 and c = 3 這類情況，是必然走這個聯(lián)合索引了

情況二：a% and b的情況

b不走索引但走索引下推（b走了索引下推，減少了回表次數(shù)。。。。。如果b沒有索引下推，則還要在a%回表后進(jìn)行一次b篩選）

B是不走索引的話：

首先A%會走索引的進(jìn)行模糊查詢，將模糊查詢出來的主鍵進(jìn)行回表（如果覆蓋索引就不需要回表），回表后再根據(jù)B進(jìn)行篩選，這時候B是不走索引的
B使用索引下推的話：

首先A%會走索引的進(jìn)行模糊查詢，模糊查詢結(jié)束的時候，會將B條件索引下推到存儲引擎層，這時候會從模糊查詢的結(jié)果中篩選出來符合B的。最后再回表查詢對應(yīng)的字段（如果覆蓋索引就不需要回表）。減少了回表的次數(shù)。

到此這篇關(guān)于MySQL的聯(lián)合索引范圍條件失效問題解決辦法的文章就介紹到這了,更多相關(guān)MySQL聯(lián)合索引范圍條件失效內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

最新評論