Mysql索引基礎了解

了解索引前要先了解下面的概念

索引是什麼

索引是一個檔案,一個表可以有多個索引檔案,正常查找資料時查詢優化器判斷可以使用索引并且選擇索引後會優先讀取索引檔案,根據索引找到對應資料或者資料ID後再做進一步篩選

索引方式

1：hash:無序索引,不能範圍查找,不能用于排序,使用比較少,缺點較多一般純記憶體引擎會用

2：B+樹:有序索引,非葉子節點不儲存資料,隻儲存索引key,隻有葉子節點儲存資料,每次查找都要至葉子節點才能傳回資料,一般4層深度可索引千萬級别資料(每個資料頁16k,行資料1kb,一個索引key16位元組的情況,大多數情況使用這個)

下面主要讨論B+樹索引類型

重點:B+樹索引是有序的(索引不就應該有序嗎,不然找個毛線,hash 是個例外)

索引分類

按整個索引檔案分

1:聚集索引：葉子節點儲存的是真實索引對應行的完整資料(一個表有且必須有一個 聚集索引，主鍵，或者第一個唯一索引或者系統預設生成一個rowid)

2:非聚集索引：葉子節點儲存的是主鍵值;

按索引使用分

1:主鍵索引:唯一,值不能為null,建立主鍵則自動生成主鍵索引,無需額外再建立

2:唯一索引:不能重複,值可以有一個為null

3:全文索引:比對長文字,性能不太好,一般用ES等搜尋引擎代替mysql8.0 已經删除

4:聯合索引:多個字段一起建立的索引,索引key按照所選字段從左至右拼接,使用注意最左比對原則

幾個概念

1:回表：通過非聚集索引查找到資料後回到表的聚集索引查找原始資料

2:覆寫索引：單個索引檔案就能找到所有需要的字段資訊，無需回表查詢原始資料（注意這裡是單個索引檔案,簡單的說就是走一次索引就能完成查找的情況)

3:最左比對原則：針對B+樹索引中的聯合索引,查找的多個字段必須比對索引順序,索引字段可以不需要完全使用,但使用了的必須比對順序;(hash索引的要百分百比對)

為啥是最左比對,不是最右比對,右邊可以通配,左邊就不行,搞啥都要以左為準???

因為索引是有序的,而排序是從左開始排,是以必須左邊能比對上才能根據順序篩選資料,如果排序從右邊開始排,那就要最右原則了

eg:索引 a_b_c,則單字段a 可以用此索引,a and b 也是可用 a and b and c 也是可以;b and c, a and c ,a or b 是不可用的 (b and a 這種情況理論是不行的 但是sql有查詢優化器可能會将你的sql 改成 a and b 這樣就可以使用這個索引了)

4:索引失效:由于sql順序或者查找範圍 不滿足索引使用,導緻全表掃碼的情況,主要有以下情況:

a:索引字段中使用函數,如count,sum 或者日期轉換等

b:聯合索引執行的sql字段順序不符合最左比對原則

c:索引字段使用like %xxx 左邊通配的情況(xxx%,右邊通配可以用索引,誰叫排序是左邊開始排…)

d:連表字段類型不一緻,一個int 一個 varchat;

e:使用 or 關聯 并且or 後面字段沒有索引

5:索引合并:一條sql 同時使用幾個索引檔案的情況(mysql 5.0之後),sql 會根據多個個索引傳回的資料進行交集 或者并集處理最後得出所要資料,是否使用得看查詢優化器計算,用 explain 檢視sql type 顯示 index_merge,一般用or 且字段都有索引 是會使用索引合并的;

索引的缺點

a:索引需要占用空間;

b:索引是需要維護的,新增,更新索引字段,删除 資料等都要對索引進行維護,索引越多元護成本越大;

c:索引太多會影響查詢優化器的效率,甚至有可能選到效率更差的索引影響查詢速度;

PS資料庫索引為啥用B+樹而不是B樹?

A:磁盤最小機關頁有大小限制 一般為16KB,如果每次IO操作隻能讀取特定的頁數,那即一個頁内儲存的資料越多每次IO能擷取更多的索引資料,就是說索引樹的寬度可以更大,相對的樹的高度就更短

1:B數每個節點都存儲資料,每個節點資料不重複導緻樹的寬度是有限的,資料量一定時樹的深度就會加深,磁盤io 就會增加

2:B+樹隻有葉子節點才儲存資料,其他節點儲存的是key的最大跟最小值,葉子節點資料有序且不重複,每次查找必須找到葉子節點,但是因為非葉子節點隻儲存key,是以樹的寬度可以比較大,另一個因為資料都是在葉子節點是以周遊資料隻需要擷取葉子節點連表就行