聚簇索引和非聚簇索引

聚簇索引和非聚簇索引是 InnoDB 里面的叫法

一張表它一定有聚簇索引，一張表只有一個聚簇索引在物理上也是連續存儲的

它產生的過程如下：

1. 表中有無有主鍵索引，如果有，則使用主鍵索引作為聚簇索引；
2. 如果沒有主鍵索引，則看表中有無唯一索引，那么使用第一個唯一索引；
3. 如果以上兩個條件都不滿足，InnoDB 則會生成隱藏聚簇索引。

聚簇索引

聚簇索引一般是主鍵索引，

例如主鍵索引 id 對應的聚簇索引結構圖（葉子節點存儲整表數據）：

?

非聚簇索引

非聚簇索引在 InnoDB 也叫做二級索引，非聚簇索引是普通列的索引（非主鍵索引）

例如普通 class_id 對應的非聚簇索引結構圖（葉子節點存儲的是聚簇索引）：

?

MySQL的InnoDB索引數據結構是B+樹

聚簇索引葉子結點存儲的是行數據，而非聚簇索引葉子節點存儲的是聚簇索引，因此通過聚簇索引可以找到真正的行數據；
由于非聚簇索引的葉子結點存儲的是聚簇索引，因此使用非聚簇索引還需要進行回表查詢，所以在查詢效率方面，聚簇索引要高于非聚簇索引；
聚簇索引一般為主鍵索引，而一個表中只能有一個主鍵，因此一個表中也只能有一個聚簇索引，而非聚簇索引則沒有數量上的限制。

---

?什么是回表查詢?

由于非聚簇索引的葉子節點存儲的不是真正的數據，而是聚簇索引，所以在使用普通索引進行查詢操作時，會先查詢到聚簇索引，然后再去聚簇索引對應的 B+ 數去查詢真正的數據，這個過程就叫做回表查詢。

---

例子

下面我們創建了一個學生表，做三種查詢，來說明什么情況下是聚簇索引，什么情況下不是。

create table student (id bigint,no varchar(20) ,name varchar(20) ,address varchar(20) ,PRIMARY KEY (`branch_id`) USING BTREE,UNIQUE KEY `idx_no` (`no`) USING BTREE
)ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC;

第一種，直接根據主鍵查詢獲取所有字段數據，此時主鍵是聚簇索引，因為主鍵對應的索引葉子節點存儲了id=1的所有字段的值。

select * from student where id = 1

第二種，根據編號查詢編號和名稱，編號本身是一個唯一索引，但查詢的列包含了學生編號和學生名稱，當命中編號索引時，該索引的節點的數據存儲的是主鍵ID，需要根據主鍵ID重新查詢一次，所以這種查詢下no不是聚簇索引

select no,name from student where no = 'test'

第三種，我們根據編號查詢編號（有人會問知道編號了還要查詢？要，你可能需要驗證該編號在數據庫中是否存在），這種查詢命中編號索引時，直接返回編號，因為所需要的數據就是該索引，不需要回表查詢，這種場景下no是索引覆蓋?

select no from student where no = 'test'

主鍵一定是聚簇索引，MySQL的InnoDB中一定有主鍵，即便研發人員不手動設置，則會使用unique索引，沒有unique索引，則會使用數據庫內部的一個行的id來當作主鍵索引,其它普通索引需要區分SQL場景，當SQL查詢的列就是索引本身時，我們稱這種場景下該普通索引也可以叫做聚簇索引，MyisAM引擎沒有聚簇索引。

---

什么是索引覆蓋

????????索引覆蓋是指在一個查詢語句中，某個索引已經 "覆蓋了" 需要被查詢出來的列，此時就不需要進行回表查詢了，這就叫做索引覆蓋！！（索引覆蓋它是非聚簇索引中的一個特殊情況）

當我們寫了這樣一個 SQL，實際上它走的是輔助索引，結構如下圖：

select id from student where name = 'Bob';

1. 輔助索引（非聚簇索引）中的查詢，一般是需要查詢兩次，第一次查詢出聚簇索引，然后根據聚簇索引回表查詢，最終拿到行數據。
2. 但是此處我的查詢需求剛好就是聚簇索引，因此一次查詢就可以拿到需要的列，不需要進行回表，這就是索引覆蓋~

?以下四種情況都屬于索引覆蓋 >>

// 聯合索引 (name,age)
select name from student where.....
?
select age from student where.....
?
select name,age from student where.....
?
select address,name,age from student where address = '深圳';

最后一個 SQL 因為 where 條件后面可以知道 address，所以也不需要回表查詢！！

---

索引下推

索引下推是指在查詢非聚簇索時，拿到了葉子結點的聚簇索引，然后對聚簇索引中包含的字段先做判斷，直接過濾掉不滿足條件的記錄，從而減少回表次數，這就是索引下推！！（索引下推是在 MySQL 5.6 之后才引入的，它屬于非聚簇索引中功能）

以 user 表中的聯合索引（name，age）為例：

select * from user where name='張%' and age='10';// 表中有四條數據
// 1  張三  10
// 2  張四  11
// 3  張五  12
// 4  老六  13

MySQL 5.6 之前沒有索引下推，它的執行流程如下：

① 在非聚簇索引中根據 name='張%' 查到聚簇索引中匹配的?id

② 使用匹配的 id 進行回表查詢

?此時會進行三次回表操作，而聯合索引中的 age 字段就沒用上。

MySQl 5.6 之后引入索引下推，它會根據 name='張%' 和 age 一起過濾數據：

【好處】：它的第二步操作就可以節省回表的次數

② 使用匹配的 id 進行回表查詢

?引入索引下推后，只執行了一次回表查詢，這就是索引下推的好處。

---

什么是最左匹配原則

• 最左匹配原則是指索引以最左邊的為起點，任何連續的索引都能匹配上，
• 當遇到范圍查詢 (>、<、between、like) 就會停止匹配。

比如聯合索引?index(a，b，c)，以下 SQL 來理解什么是最左匹配原則：

select * from user where a=1; // 只使用索引 aselect * from user where b=2; // 不使用索引select * from user where c=3; // 不使用索引select * from user where a=1 and b=2; // 只使用索引 a,bselect * from user where a=1 and c=3; // 只是用索引 aselect * from user where b=2 and c=3; // 不使用索引select * from user where a=1 and b=2 and c=3; // 使用索引 a,b,cselect * from user where a=1 and b like '%xxx' and c=3; // 只使用索引 a,b

【疑惑一】

? ? ? ? 不是說使用了 like，就停止匹配了嗎，為什么前面的索引下推使用了 name='張%' 還能再拿 age 進行過濾呢 ？

對于 like 查詢，它的常見寫法有三種：

模糊匹配后面任意字符：like '張%'
模糊匹配前面任意字符：like '%張'
模糊匹配前后任意字符：like '%張%'
? ? ? ? 這三種情況，只有第一種情況是會走索引的，其他的都會導致索引失效，所以前面索引下推例子中的 name='張%' 是不會停止匹配的~

【疑惑二】

? ? ? ? 當我們寫出這樣的條件語句 where a=1 and c=3 and b=2 時，引擎為什么不把它調整為 a，b，c 的順序呢？

? ? ? ? MySQL 8.0 之后才涉及到這樣的調優，但是具體會不會調優，是不一定的，因為索引調優的主動權在索引的優化器里面的，而優化器這個東西，它很玄學，所以不知道它會不會進行調優。