mysql最左前綴法則導(dǎo)致索引失效的解決
1. 什么是最左前綴法則
最左前綴是在使用innodb存儲引擎索引時,需要遵守的法則。在一個聯(lián)合索引如idx(a, b, c)
,執(zhí)行查詢SQL時,如果查詢條件包含索引的最左前綴,那么可以使用聯(lián)合索引加速查詢。對于上述例子,最左前綴包括idx(a)
, idx(a, b)
, idx(a, b, c)
2. 為什么存在最左前綴
最左前綴涉及到聯(lián)合索引如何構(gòu)建這一問題。
我們構(gòu)建如下圖所示的數(shù)據(jù)庫表。
我們構(gòu)建a, b, c字段的聯(lián)合索引,idx(a, b, c)。
索引構(gòu)建的規(guī)則很簡單,先按照a排序,如果a一致,則按照b排序。如果b一致,按照c排序。如果所有索引字段都一致,那么按照主鍵排序。
有上述規(guī)則,我們可以得到如下索引圖
通過索引圖,我們觀察發(fā)現(xiàn)如下結(jié)論
- 在全局范圍內(nèi),a字段(紅框標記)全局有序
- 在全局范圍內(nèi),b字段(橘色框標記)全局無序,但局部有序(從左往右數(shù)的第一個數(shù)據(jù)頁)
- 在全局范圍內(nèi),c字段(綠框標記)全局無序
由索引的構(gòu)建規(guī)則,我們可以提煉非常關(guān)鍵的一條信息:右側(cè)字段對應(yīng)數(shù)據(jù)有序的前提是,左側(cè)字段數(shù)據(jù)確定。
我們?nèi)稳灰运饕龍D為例。b字段在全局的視角來看是沒有順序的。只有a字段確定下來,b字段才能有順序。當(dāng)a = Bill
時,b字段對應(yīng)的數(shù)據(jù)則呈現(xiàn)升序狀態(tài)
。同理,c字段要想有序,b字段必須確定下來
這就是為什么要遵守索引前綴法則。其核心原因就是聯(lián)合索引創(chuàng)建時,需要優(yōu)先滿足左側(cè)字段的有序性,然后才會考慮右側(cè)字段
3. 索引失效情況
知道了為什么存在最左前綴法則,我們來分析一下什么時候聯(lián)合索引會失效。
3.1 查詢條件未添加最左側(cè)列,索引失效
我們以dish_flavor
數(shù)據(jù)表為例進行分析
tip:數(shù)據(jù)庫的表最好不要太簡單,數(shù)據(jù)不要太少。否則優(yōu)化器可能并不會走索引,因為在數(shù)據(jù)量小的情況下,可能全表掃描效率更高
我們以dish_id
,name
,value
為字段,創(chuàng)建聯(lián)合索引
CREATE INDEX idx_dishid_name_value ON dish_flavor(dish_id, name, value);
我們分別執(zhí)行以下SQL,看看索引情況
EXPLAIN SELECT * FROM dish_flavor WHERE dish_id = 1 AND name = '中餐';
走了索引
EXPLAIN SELECT * FROM dish_flavor WHERE name = '中餐' AND value = '[]';
沒走索引
EXPLAIN SELECT * FROM dish_flavor WHERE value = '["不辣"]';
沒走索引
EXPLAIN SELECT * FROM dish_flavor WHERE dish_id = 1 AND value = '["不辣"]';
走了索引
以上四種情況只有第一條、第四條SQL走了索引。而這兩條SQL的共同點就是:都包含了dish_id
這個最左側(cè)的字段。因此,想要索引生效,必須包含最左側(cè)的字段
當(dāng)然了,第一句SQL和第四句SQL也是存在區(qū)別的。我們建立的索引是idx(dish_id, name, value)
,dish_id
緊鄰的是name
。因此第一句SQL索引全部生效EXPLAIN SELECT * FROM dish_flavor WHERE dish_id = 1 AND name = '中餐';
但第二句SQL,EXPLAIN SELECT * FROM dish_flavor WHERE dish_id = 1 AND value = '["不辣"]';
缺少了name
這個字段,而value
字段必須要name
字段確定才能夠有序,因此當(dāng)前SQL會走idx_dishid_name_value
索引,但僅僅到dish_id
這個字段為止。
我們可以觀察explain輸出表的ref列
第一句SQL兩個篩選條件都用于和索引進行比較
第二句SQL只有第一個篩選條件用于和索引進行比較
tip: explain輸出的表格,ref列表示的意思是,篩選條件是否和索引進行比較。下方是筆者從官方文檔中摘錄的信息
The ref column shows which columns or constants are compared to the index named in the key column to select rows from the table
3.2 使用OR,索引失效
執(zhí)行這條SQL:EXPLAIN SELECT * FROM dish_flavor WHERE dish_id = 1 OR name = '中餐';
,索引失效
想要解釋OR關(guān)鍵字為何會導(dǎo)致失效,其實很簡單。
OR在結(jié)果上可以等價于當(dāng)個SQL得到集合的并集,具體來說
SELECT * FROM dish_flavor WHERE dish_id = 1 OR name = '中餐'
=>
SELECT * FROM dish_flavor WHERE dish_id = 1 并上 SELECT * FROM dish_flavor WHERE name = '中餐'
對于拆解成的兩句SQL,前者可以走聯(lián)合索引,后者不能走(因為最左前綴法則)。事實上,MySQL可不會真這么拆解SQL,那對于MySQL來說,OR的兩個篩選條件一個能走idx,一個不能。這到底是能還是不能,干脆就不走聯(lián)合索引。
即使走了聯(lián)合索引,也只能對dish_id
做篩選,而存在的name
字段必須要全表掃描,因為dish_id沒法被確定,因此他全局無序。既然如此,那為什么不直接全表掃描呢?
因此,OR關(guān)鍵字會導(dǎo)致索引失效??偨Y(jié)一下:OR關(guān)聯(lián)的兩個查詢條件,必然存在一個條件無法滿足最左前綴法則,走不了索引。對于那個走不了索引的查詢條件,在不存在其它索引的前提下,必然需要全表掃描。因此OR關(guān)鍵字會破壞聯(lián)合索引,導(dǎo)致索引失效
tip: 當(dāng)前數(shù)據(jù)表中,只存在idx(dish_id, name, value)
現(xiàn)在,我們做些額外操作,在OR的查詢條件下,讓MySQL依然走索引。
我們?yōu)?code>name字段單獨創(chuàng)建索引CREATE INDEX idx_name ON dish_flavor(name);
現(xiàn)在我們在執(zhí)行含有OR的SQL,EXPLAIN SELECT * FROM dish_flavor WHERE dish_id = 1 OR name = '中餐';
我們發(fā)現(xiàn),MySQL不僅走了聯(lián)合索引,還走了idx_name
索引。聯(lián)合索引解決dish_id = 1
這個條件,idx_name索引解決name = '中餐'
這個條件,非常完美
tip: explain輸出的type字段,內(nèi)容為index_merge。這表明SQL走了多個索引
為了下文實驗不被多余索引干擾,我們刪除idx_name
索引
3.3 函數(shù)運算,索引失效
我們執(zhí)行SQLEXPLAIN SELECT * FROM dish_flavor WHERE dish_id + 1 = 1;
,會發(fā)現(xiàn)索引失效
索引失效
模糊匹配中
索引未失效
模糊匹配尾
索引未失效
筆者嘗試解釋上述現(xiàn)象
首先,模糊匹配本質(zhì)就是范圍查找。既然是范圍,那必然存在兩端。我們查找范圍的思路可以大致框定,對于有序的數(shù)據(jù),我們可以通過索引確定端點,端點間的數(shù)據(jù)就是我們模糊匹配的內(nèi)容。
首模糊
對于首模糊情況,他的起始端點無法走索引確定,因為起始端點可以是任何數(shù)據(jù)。
同樣的,他的結(jié)束端點也無法走索引確定,因為字符串字段構(gòu)建索引,依然遵循另一個層面的最左前綴法則,字符串比較就是從左到右一次比較。如果左側(cè)字符串無法確定,右側(cè)字符串就無法保證有序性,這種情況也可以歸類為全局無序,局部有序。
中間模糊
對于中間模糊情況,他的起始端點可以走索引確定。但他的尾端點無法確定,但這足夠了。因為有一部分內(nèi)容可以走索引,剩下內(nèi)容掃描整個索引即可
尾模糊
起始端點可以走索引確定,尾端點不需要確定,因為它可以無限匹配后續(xù)內(nèi)容
一句話總結(jié),模糊查詢依然遵循另一個維度的最左前綴法則,它依賴于字符串索引創(chuàng)建的規(guī)則。優(yōu)先匹配左側(cè)字符串,右側(cè)字符串確定順序的前提是左側(cè)字符串已確定。
到此這篇關(guān)于mysql最左前綴法則導(dǎo)致索引失效的解決的文章就介紹到這了,更多相關(guān)mysql最左前綴法內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家!
相關(guān)文章
一文帶你了解MySQL之InnoDB統(tǒng)計數(shù)據(jù)是如何收集的
通過show index可以看到關(guān)于索引的統(tǒng)計數(shù)據(jù),那么這些統(tǒng)計數(shù)據(jù)是怎么來的呢,它們是以什么方式收集的呢,本章將聚焦于InnoDB存儲引擎的統(tǒng)計數(shù)據(jù)收集策略,需要的朋友可以參考下2023-05-05mysql中int、bigint、smallint 和 tinyint的區(qū)別詳細介紹
最近使用mysql數(shù)據(jù)庫的時候遇到了多種數(shù)字的類型,主要有int,bigint,smallint和tinyint;接下來將詳細介紹以上三種類型的應(yīng)用2012-11-11mysql8.0.19基礎(chǔ)數(shù)據(jù)類型詳解
這篇文章主要介紹了mysql8.0.19基礎(chǔ)數(shù)據(jù)類型的相關(guān)知識,本文給大家介紹的非常詳細,對大家的學(xué)習(xí)或工作具有一定的參考借鑒價值 ,需要的朋友可以參考下2020-03-03MySQL數(shù)據(jù)庫數(shù)據(jù)刪除操作詳解
本文我們將要學(xué)習(xí)的是作為刪除數(shù)據(jù)使用的?“DELETE”?語句,“DELETE”?語句是用來刪除數(shù)據(jù)的,它不能用來刪除數(shù)據(jù)表本身。刪除數(shù)據(jù)表使用的是?“DROP”?語句,而?“DELETE”?的作用只是用來刪除記錄而已2022-08-08