一分鐘明白MySQL聚簇索引和非聚簇索引

阿偉~發表於2020-05-10
MySql索引

MySQL的InnoDB索引資料結構是B+樹，主鍵索引葉子節點的值儲存的就是MySQL的資料行，普通索引的葉子節點的值儲存的是主鍵值，這是瞭解聚簇索引和非聚簇索引的前提

什麼是聚簇索引？

很簡單記住一句話：找到了索引就找到了需要的資料，那麼這個索引就是聚簇索引，所以主鍵就是聚簇索引，修改聚簇索引其實就是修改主鍵。

什麼是非聚簇索引？

索引的儲存和資料的儲存是分離的，也就是說找到了索引但沒找到資料，需要根據索引上的值(主鍵)再次回表查詢,非聚簇索引也叫做輔助索引。

clustered index（MySQL官方對聚簇索引的解釋）

The InnoDB term for a primary key index. InnoDB table storage is organized based on the values of the primary key columns, to speed up queries and sorts involving the primary key columns. For best performance, choose the primary key columns carefully based on the most performance-critical queries. Because modifying the columns of the clustered index is an expensive operation, choose primary columns that are rarely or never updated.
注意標黑的那段話，聚簇索引就是主鍵的一種術語

一個例子

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

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引擎沒有聚簇索引。

相關文章