MySQL中的聚簇索引和非聚簇索引

MySQL中的聚簇索引和非聚簇索引

索引（Index ）是資料庫最佳化中最常用也是最重要的手段之一，透過索引通常可以幫助使用者解決大多數的 SQL 效能問題。索引是幫助 MySQL 高效獲取資料的資料結構，它用於快速找出在某個列中含有某一特定值的行。如果不使用索引，那麼 MySQL 必須從第 1 條記錄開始然後讀完整個表直到找出相關的行。表越大，花費的時間越多。如果表中查詢的列有一個索引，那麼 MySQL 就能快速到達一個位置去搜尋資料檔案的中間， 而 沒有必要 遍歷 所有資料。

索引 列 在MySQL 中也叫做“鍵（ key ）”， 索引 是儲存引擎用於快速找到記錄的一種資料結構。總體來說，索引有如下幾個優點：

①　 索引大大減少了伺服器需要掃描的資料量。

②　 索引可以幫助伺服器避免排序和臨時表。

③　 索引可以將隨機I/O 變為順序 I/O 。

 

索引的本質是空間換時間 ，透過索引這個快取來提高資料查詢的效率。 在MySQL 中， 每一個索引在InnoDB 裡面對應一棵 B+ 樹， 即 MySQL 的 InnoDB 索引資料結構是 B+ 樹，主鍵索引葉子節點儲存的就是 MySQL 的 整個 資料行，普通索引的葉子節點儲存的是 索引列和 主鍵值。

  1   什麼是聚 簇 索引（clustered index ）和非 聚簇索引（non-clustered index ） ？

從物理儲存角度 來分， 索引 可以 分為聚簇索引和非聚簇索引 ， 區別主要看葉子節點存了什麼資料 。

在InnoDB 裡，索引 B+Tree 的葉子節點儲存了整行資料的是主鍵索引，也被稱之為聚簇索引。聚簇索引是對磁碟上實際資料重新組織以按指定的一個或多個列的值排序的演算法。特點是儲存資料的順序和索引順序一致。一般情況下主鍵會預設建立聚簇索引，且一張表只允許存在一個聚簇索引 ， 因為 資料一旦儲存，順序只能有一種。找到了索引就找到了需要的資料，那麼這個索引就是聚簇索引，所以主鍵就是聚簇索引，修改聚簇索引其實就是修改主鍵。

索引B+Tree 的葉子節點 只 儲存了主鍵的值 和索引列 的是非主鍵索引，也被稱之為非聚簇索引。一個表可以有多個非聚簇索引 。 非聚簇 索引的儲存和資料的儲存是分離的，也就是說 可能 找到了索引但沒找到資料，需要根據索引上的值（主鍵）再次回表查詢，非聚簇索引也叫做輔助索引。

聚簇索引的葉子節點就是資料節點，而非聚簇索引的葉子節點仍然是索引節點，只不過有指向對應資料塊的指標。

聚簇索引查詢相對會更快一些，因為主鍵索引樹的葉子節點直接就是我們要查詢的整行資料了 ， 而非主鍵索引的葉子節點是主鍵的值，查到主鍵的值以後，還需要再透過主鍵的值再進行一次查詢（這個過程叫做回表 ， 也就是查了2 個索引樹）。

例如， 下面 的SQL 建立了一個學生表 ：

create table lhrdb.student (
    id bigint,
    no varchar(20) ,
    name varchar(20) ,
    address varchar(20) ,
    PRIMARY KEY (`id`),
    UNIQUE KEY `idx_no` (`no`)
)ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

對於如下的SQL 語句， 直接根據主鍵查詢 獲取 所有欄位資料，此時主鍵 是 聚簇索引，因為主鍵對應的索引葉子節點儲存了id=1 的所有欄位的值 ：

select * from lhrdb.student where id = 1;

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

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

對於如下的SQL 語句， 根據編號查詢編號，這種查詢命中編號索引時，直接返回編號，因為所需要的資料就是該索引，不需要回表查詢，這種場景下no 是聚簇索引

select no from student where no = 'test';

主鍵一定是聚簇索引， 如果開發 人員不手動設定 主鍵 ， 那麼MySQL 會 預設 使用 非空的 Unique 索引， 若 沒有 非空的 Unique 索引，則會使用資料庫內部的一個行的 id 來當作主鍵索引 ， 其它普通索引需要區分SQL 場景，當 SQL 查詢的列就是索引本身時，我們稱這種場景下該普通索引也可以叫做聚簇索引， MyisAM 引擎沒有聚簇索引。

 

---

---

About Me

........................................................................................................................ ● 本文作者：小麥苗，部分內容整理自網路，若有侵權請聯絡小麥苗刪除 ● 本文在itpub、部落格園、CSDN和個人微 信公眾號（ DB寶）上有同步更新 ● 本文itpub地址： http://blog.itpub.net/26736162 ● 本文部落格園地址： http://www.cnblogs.com/lhrbest ● 本文CSDN地址： https://blog.csdn.net/lihuarongaini ● 本文pdf版、個人簡介及小麥苗雲盤地址： http://blog.itpub.net/26736162/viewspace-1624453/ ● 資料庫筆試面試題庫及解答： http://blog.itpub.net/26736162/viewspace-2134706/ ● DBA寶典今日頭條號地址： ........................................................................................................................ ● QQ群號： 230161599 、618766405 ● 微 信群：可加我微 信，我拉大家進群，非誠勿擾 ● 聯絡我請加QQ好友 （ 646634621 ），註明新增緣由 ● 於 2020-05-01 06:00 ~ 2020-05-30 24:00 在西安完成 ● 最新修改時間：2020-05-01 06:00 ~ 2020-05-30 24:00 ● 文章內容來源於小麥苗的學習筆記，部分整理自網路，若有侵權或不當之處還請諒解 ● 版權所有，歡迎分享本文，轉載請保留出處 ........................................................................................................................ ● 小麥苗的微店： ● 小麥苗出版的資料庫類叢書： http://blog.itpub.net/26736162/viewspace-2142121/ ● 小麥苗OCP、OCM、高可用網路班： http://blog.itpub.net/26736162/viewspace-2148098/ ● 小麥苗騰訊課堂主頁： https://lhr.ke.qq.com/ ........................................................................................................................ 使用 微 信客戶端掃描下面的二維碼來關注小麥苗的微 信公眾號（ DB寶）及QQ群（DBA寶典）、新增小麥苗微 信， 學習最實用的資料庫技術。 ........................................................................................................................