MySQL中的聚簇索引、非聚簇索引、聯(lián)合索引和唯一索引詳細介紹
一、索引類型
索引根據(jù)底層實現(xiàn)可分為B-Tree索引和哈希索引，大部分時候我們使用的都是B-Tree索引，因為它良好的性能和特性更適合于構(gòu)建高并發(fā)系統(tǒng)。
根據(jù)索引的存儲方式來劃分，索引可以分為聚簇索引和非聚簇索引。聚簇索引的特點是葉子節(jié)點包含了完整的記錄行，而非聚簇索引的葉子節(jié)點只有所以字段和主鍵ID。
根據(jù)聚簇索引和非聚簇索引還能繼續(xù)下分還能分為普通索引、覆蓋索引、唯一索引以及聯(lián)合索引等。
二、聚簇索引和非聚簇索引
聚簇索引也叫聚集索引，它實際上并不是一種單獨的索引類型，而是一種數(shù)據(jù)存儲方式，聚簇索引的葉子節(jié)點保存了一行記錄的所有列信息。也就是說，聚簇索引的葉子節(jié)點中，包含了一個完整的記錄行。
非聚簇索引也叫輔助索引、普通索引，它的葉子節(jié)點只包含一個主鍵值，通過非聚簇索引查找記錄要先找到主鍵，然后通過主鍵再到聚簇索引中找到對應(yīng)的記錄行，這個過程被稱為回表。
例如一個包含了用戶姓名和年齡的的數(shù)據(jù)表，假設(shè)主鍵是用戶ID，聚簇索引的結(jié)構(gòu)為（橙色的代表id，綠色是指向子節(jié)點的指針）：
葉子節(jié)點中，為了突出記錄，把(id, name, age)區(qū)分開來了，實際上是連在一起的，它們是構(gòu)成一條記錄的整體。
而一個非聚簇索引（以age為索引）的結(jié)構(gòu)是：
它的葉子節(jié)點中，不包含整個記錄的完整信息，除了age字段本身以外，只包含當前記錄的主鍵id。如果想要獲取整行記錄數(shù)據(jù)還需要再通過id號到聚簇索引中回表查詢。
InnoDB中，每個表必須有一個聚簇索引，默認是根據(jù)主鍵建立的。如果表中沒有主鍵，InnoDB會選擇一個合適的列作為聚簇索引，如果找不到合適的列，會使用一列隱藏的列DB_ROW_ID作為聚簇索引。
三、覆蓋索引
非聚簇索引中因為不含有完整的數(shù)據(jù)信息，查找完整的數(shù)據(jù)記錄需要回表，所以一次查詢操作實際上要做兩次索引查詢。而如果所有的索引查詢都要經(jīng)過兩次才能查到，那么肯定會引起效率下降，畢竟能少查一次就少查一次。
以上面的age索引為例，它是一個非聚簇索引，如果我想通過年齡查詢用戶的id，執(zhí)行了下面一條語句：
1 | select id from userinfo where age = 10; |
這種情況是否還有必要去回表？因為我只需要id的值，通過age這個索引就已經(jīng)能拿到id了，如果還去回表一次不就做了無用的操作了嗎？實際上確實是不需要的。索引查詢中，如果輔助索引已經(jīng)能夠得到查詢的所有信息了，就無需再回表，這個就是覆蓋索引。
四、聯(lián)合索引
聯(lián)合索引指的是同時對多列創(chuàng)建的索引，創(chuàng)建聯(lián)合索引后，葉子節(jié)點會同時包含每個索引列的值，并且同時根據(jù)多列排序，這個排序和我們所理解的字典序類似。
例如對同時對上面的姓名和年齡創(chuàng)建的索引結(jié)構(gòu)：
(name, age)都是簡寫，想不出十幾個名字。。。。。
每個葉子節(jié)點同時保存了所有的索引列，除此之外，還是只包含了主鍵id。
最左前綴匹配原則
當對多列創(chuàng)建索引后，并不是只要包含了創(chuàng)建索引的列就能使用索引，索引的使用要遵循最左前綴匹配原則。
假設(shè)對列(A, B, C)創(chuàng)建索引，那么只有以下場景能使用索引：
- 對列(A, B, C)/(A, C)或者(A, B)進行查詢會匹配索引，對(C, A)或者(B, C)來說不能使用索引。
- 通配符只能使用LIKE 'val%'形式，不能使用LIKE '%VAL%'，后者會導(dǎo)致全表掃描。
- 索引列不能進行運算，例如WHERE A + 1 = 5這種場景會導(dǎo)致索引失效。
- 索引列不能包含范圍值查詢，如LIKE/BETWEEN/>/<等都會導(dǎo)致后面的列無法匹配索引。
- 索引列不能包含有NULL值。
索引下推
新版本的MySQL（5.6以上）中引入了索引下推的機制：可以在索引遍歷過程中，對索引中包含的字段先做判斷，直接過濾掉不滿足條件的記錄，減少回表次數(shù)。
例如針對上面表中的(name, age)做聯(lián)合索引，正常情況下的查詢邏輯：
- 通過name找到對應(yīng)的主鍵ID
- 根據(jù)id記錄的列匹配age條件
這種做法會導(dǎo)致很多不必要的回表，例如表中存在(張三, 10)和(張三, 15)兩條記錄，此刻要查詢(張三, 20)的記錄。查詢時先通過張三定位到所有符合條件的主鍵ID，然后在聚簇索引中遍歷滿足條件的行，看是否有符合age = 20的記錄。實際情況是沒有滿足條件的記錄的，這個回表過程也相當于是在做無用之功。
索引下推的主要功能就是改善這一點，在聯(lián)合索引中，先通過姓名和年齡過濾掉不用回表的記錄，然后再回表查詢索引，減少回表次數(shù)。
五、唯一索引
唯一索引是一種不允許具有相同索引值的索引，系統(tǒng)在創(chuàng)建該索引時檢查是否有重復(fù)的鍵值，每次對更新或增加記錄時都會檢查這一點。主鍵索引就是唯一索引。
到此這篇關(guān)于MySQL中的聚簇索引、非聚簇索引、聯(lián)合索引和唯一索引詳細介紹的文章就介紹到這了,更多相關(guān)MySQL索引內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！
相關(guān)文章
MySQL如何追蹤數(shù)據(jù)庫中對特定表的更新操作
這篇文章主要為大家詳細介紹了五種不同的方法 來追蹤 MySQL 數(shù)據(jù)庫中對特定表（如 statistics_test）的更新操作,并提供詳細的代碼示例和適用場景分析,希望對大家有所幫助2025-06-06
這篇文章主要給大家介紹了關(guān)于Mysql如何同時交換兩個表的表名,以及MySQL命令rename修改表名的相關(guān)資料,文中通過實例代碼介紹的非常詳細,需要的朋友可以參考下2022-01-01
這篇文章主要分享的是超詳細匯總21個值得收藏的mysql優(yōu)化實踐,對正在學習的同學有一定的參考價值，需要的同學可以參考一下2022-01-01
MySQL如何導(dǎo)入csv格式數(shù)據(jù)文件解決方案
本文將詳細介紹MySQL如何導(dǎo)入csv格式數(shù)據(jù)文件并提供詳細解決方案，需要了解的朋友可以參考下2012-11-11
MYSQL數(shù)據(jù)庫中的日期比較與計算是經(jīng)常用到的，例如比較兩個日期大小，計算兩個日期相差多少天2018-03-03
近我將筆記本重裝了,為了保留之前的程序,我把相關(guān)的注冊表和環(huán)境備份了下來,重裝之后重新導(dǎo)入成功再現(xiàn)了部分軟件,下面給大家分享mysql 8.0.29 卸載問題記錄,感興趣的朋友一起看看吧2024-04-04
這篇文章主要介紹了mySQL服務(wù)器連接,斷開及cmd使用操作，具有很好的參考價值，希望對大家有所幫助。一起跟隨小編過來看看吧2020-07-07
這篇文章主要介紹了MySQL中的全表掃描和索引樹掃描?，從本文的學習可以輕松的知道，全表掃描的效率相比于索引樹掃描相對較低一點，但是差距不是很大，具體示例代碼詳解跟隨小編一起看看吧2022-05-05
MySQL數(shù)據(jù)庫跨版本遷移的實現(xiàn)三種方式
本文主要介紹了MySQL數(shù)據(jù)庫跨版本遷移的實現(xiàn),主要包含mysqldump,物理文件遷移和原地升級三種,具有一定的參考價值,感興趣的可以了解一下2024-05-05
日志滾動解決日志文件過大問題，比如我開啟了general_log，這個日志呢是記錄mysql服務(wù)器上面所運行的所有sql語句；比如我開啟了mysql的慢查詢2014-01-01