MySQL - 4種基本索引、聚簇索引和非聚索引、索引失效情況

一、索引

1.1、簡單介紹

1.2、索引的分類

1.2.1、主鍵索引

1.2.2、單值索引（單列索引、普通索引）

1.2.3、唯一索引

1.2.4、復合索引

1.2.5、復合索引經典問題

1.3、索引原理

1.3.1、主鍵自動排序

1.3.2、索引的底層原理

1.3.3、B 樹和 B+樹的區別

1.4、聚簇索引和非聚簇索引

1.4.1、innoDB 中的主鍵索引

1.4.2、使用聚簇索引的優勢

1.4.3、使用聚簇索引需要注意什么

1.4.4、為什么主鍵通常建議使用自增 id

1.5、索引失效的場景

一、索引

1.1、簡單介紹

索引就是一種幫助? mysql 提高查詢效率的數據結構.

優點：

大大增加了查詢速度.

缺點：

索引實際上是一張表，因此需要消耗一部分空間資源.
對表中的數據進行增刪改的時候，需要更新索引，因此速度會受到一定影響.

1.2、索引的分類

1.2.1、主鍵索引

實際上就是我們創建數據庫時指定的主鍵（主鍵索引值不能為空、不能重復.），會自動創建索引，叫做 “主鍵索引”，在 innodb 引擎中就是所謂的 “聚簇索引”.

例如，以 id 為主鍵建表

create table user(id int PRIMARY KEY, name varchar(20), age int);

然后通過以下命令查看 user 表的索引

show index from user;

1.2.2、單值索引（單列索引、普通索引）

就是為表中的某一列創建的索引，一個表中可以有多個單列索引.?

例如，表中有字段 id、name、age，那么為其中的 name 創建一個索引，就叫單列索引.

創建方式有以下兩種：

a）建表時創建（注意，這種方式創建，索引名和字段名一致）

# 給 name 單獨創建索引
create table user(id int primary key, name varchar(20), age int, key(name));# 給 name 和 age 分別創建索引
create table user(id int primary key, name varchar(20), age int, key(name), key(age));

b）建表后創建

create table user(id int primary key, name varchar(20), age int);create index index_name on user(name);

c）刪除索引

drop index 索引名 on 表明

1.2.3、唯一索引

在創建表的時候，有時候我們會通過 unique 指定某個字段唯一，這個時候就會創建唯一索引.

Ps：允許有 null 值，并且可以有多個.

創建方式有以下兩種：

a）建表時指定

# 第一種寫法
create table user(id int primary key, name varchar(20) unique, age int);# 第二種寫法
create table user(id int primary key, name varchar(20), age int, unique(name));

b）建表后創建

create unique index index_name on user(name);

1.2.4、復合索引

就是我們為表中的多個字段一起創建一個索引.?

Ps：查詢時，在 where 條件后，必須要使用 and 連接復合索引字段，否則不生效.

創建方式有以下兩種：

a）建表時創建

create table user(id int primary key, name varchar(20), age int, key(name, age));

b）建表后創建

create index name_age_index on user(name, age);

1.2.5、復合索引經典問題

問題：有一個用戶表，給 name、age、gender 三個字段創建了一個復合索引 key(name, age, gender)，以下場景，哪種查詢索引會生效？

以下是 where 查詢后通過 and 拼接的字段.

name? ????????????????????????生效
name? age? ? ? ? ? ? ? ? ? 生效
name? age? gender? ? ?生效
name??gender??age? ? ?生效
age? gender? ? ? ? ? ? ? ? 失效
gender? ? ? ? ? ? ? ? ? ? ? ? 失效
gender??age??name? ? ?生效

該怎么判斷呢？符合索引生效只要滿足以下任意一個原則即可：

最左前綴元組：必須包含做前綴，也就意味著 name、 name age、name age gender 是生效的.
mysql 引擎為了更好的利用索引，在查詢過程中會動態調整查詢字段順序，便于利用索引，也就意味著只要包含所有索引字段即可（任意的組合都可以）.

1.3、索引原理

1.3.1、主鍵自動排序

當我創建一個 user 表（含主鍵 id），然后按照無序 id 的方式插入數據，會發現查詢結果盡然按照主鍵 id 排序了

為什么會進行排序呢？

排序之后相對來說，查詢更快.? 例如有 10 個自增 id，現在查詢 id = 3 的，那么只需要向下對比三次即可得到，而對于無序數據來說每次都需要遍歷一遍數據才能得到.

這也就說明為啥主鍵不建議使用 uuid 去建立，而是使用 int 類型？因為在主鍵建立索引的時候，會先根據表中的主鍵去排序，排序后在查詢效率會更高.

1.3.2、索引的底層原理

假設有如下表和信息

索引的數據結構就是一個 b+ 樹，原理如下

a）排序，形成鏈表：表中的每一條數據組織成一個鏈表中的一個節點，結構由三部分構成：“主鍵 + 數據 + 指針”，數據就是表中的非主鍵索引字段（name, age），指針就是用來指向下一個節點，這些節點會現經過主鍵 id 的排序，最后組織成一個鏈表的結構，得到b+樹的葉子節點如下

b）頁管理：將鏈表進行分頁管理，每一頁的大小默認存儲 16kb，假設如下圖（真實情況一頁存放的數據有很多）.

c）頁目錄管理：將每一頁最左邊節點的主鍵和指針拿出來存放到頁目錄中，頁目錄的默認大小也是 16kb

d）如果頁目錄的大小占滿了，那么可能還會繼續向上生成頁目錄（父節點），不過一般開發存儲的數據，樹的高度都不會超過 4 的，也就是說，當需要查找某一數據時，最多只需要 1~3 次 I/O? 操作（注意：頂層的根節點時在內存中的）.

1.3.3、B 樹和 B+樹的區別

B+ 樹相當于是在 B 上的一種優化，主要區別如下：

B+ 樹非葉子節點只存儲鍵值對信息，B 樹 data 數據也需要存儲，而每一頁的存儲空間是有限的（默認 16 kb），那么如果 data 數據較大時，每個節點能存儲的 key 就很少，進而導致樹的深度較大，增大了查詢時的磁盤 IO 次數（每一層都進行一次 IO）.
B+ 樹的葉子節點保存全集數據，是一個鏈表結構，而非葉子節點只存儲 key，大大增加了非葉子節點存儲 key 的數量，降低了樹高.

1.4、聚簇索引和非聚簇索引

1.4.1、innoDB 中的主鍵索引

聚簇索引：由主鍵索引和輔助索引構成.

主鍵索引：主鍵索引中，葉子節點保存表中每一行的所有數據，當需要查找例如 where Id = 14，就會去主鍵索引?B+ 樹上找到的葉子節點，然后獲取行數據.

Ps：如果沒有定義主鍵，就會選擇唯一且非空的索引代替，如果非空索引也沒有，就會自己隱式定義一個主鍵作為聚簇索引

輔助索引（innoDB 中的非聚簇索引就是輔助索引）：就是在聚簇索引之上建立的索引，一般來說就是表中給其他字段建立的索引（非主鍵索引），也就是復合索引、單列索引、唯一索引，并且的葉子節點存儲的不再是行物理地址，而是主鍵值，因此輔助索引最少需要二次查詢才能找到數據，例如 where name='cyk'，步驟如下

在輔助索引 B+ 樹種檢索 name，然后到達葉子節點獲取對應的主鍵.
根據主鍵在聚簇索引 B+ 樹種在及進行一次檢索操作，最終到達葉子節點獲取整行數據.

非聚簇索引：在 myisam 使用的是非聚簇索引，也由兩顆 B+ 樹構成（主鍵索引、輔助索引），主鍵索引B+樹節點存儲了主鍵，輔助索引 B+ 樹種存儲了輔助鍵.? 葉子節點都是用一個地址指向真正的表的數據，因此輔助鍵無需像 innoDB 一樣訪問主鍵索引樹.

1.4.2、使用聚簇索引的優勢

問題：每次使用輔助索引檢索都需要經過兩次 B+ 樹查詢，看上去聚簇索引的效率明顯低于非聚簇索引，這不是多此一舉么，聚簇索引優勢在哪？

訪問同一數據也不同記錄時，會把頁加載到緩存中，再次訪問的時候，會在內存中完成訪問，不必訪問磁盤，而主鍵和數據又是一起被載入內存的，因此按照主鍵 id 來組織數據（排好序的），獲取更快.
innoDB 中的輔助索引葉子節點存儲主鍵值，而不是物理地址，因此當行數據發生改變時（對表進行增刪改），葉子節點也無需像 myisam 非聚簇索引的輔助索引一樣改變地址，只需要維護索引樹即可.
innoDB 中的輔助索引葉子節點存放的是主鍵值，而 myisam 中存儲的是物理地址，因此空間占用更小.