MySQL索引詳解及演進過程及面試題延伸

1索引的概念

1.1定義

索引在關係型資料庫中，是一種單獨的、物理的對資料庫表中的一列或者多列值進行排序的一種儲存結構，它是某個表中一列或者若干列值的集合，還有指向表中物理標識這些值的資料頁的邏輯指標清單。
索引的作用相當於圖書的目錄，可以根據目錄重點頁碼快速找到所需要的內容，資料庫使用索引以找到特定值，然後順著指標找到包含該值的行，這樣可以是對應於表的SQL語句執行得更快，可快速存取資料庫表中的特定資訊。

1.2型別

在InnoDB裡面，索引型別有三種，普通索引、唯一索引（主鍵索引是特殊的非空的唯一索引）、全文索引。

普通（Normal）：也叫非唯一索引，是普通索引，沒有任何限制。唯一（Unique）：唯一索引要求鍵值不能重複（可以為空），主鍵索引其實是一種特殊的唯一索引，不過他還多了一個限制條件，要求鍵值不能為空。主鍵索參照 primary key 建立。全文（Fulltext）：針對比較大的資料，比如我們存放是文章，課文，郵件，等等，有可能一個欄位就需要幾kb，如果要解決like查詢在全文匹配的時候效率低下的問題，可以建立全文索引。只有文字型別的欄位才可以建立全文索引，比如char、varchar、text。MyISAM和InnoDB都支援全文索引。

1.3作用

一句話總結：

索引能夠提高資料檢索的效率，降低資料庫的IO成本。

提出問題：我們用空間換時間，但是他的資料結構、查詢的IO成本、以及是如何儲存資料的呢?

2索引的資料結構B+樹的演進過程

我們以一個 Page 的視角去看我們的B+樹演進過程。

頁是InnoDB管理儲存空間的基本單位，InnoDB將資料庫中的資料都是儲存在頁這個基本儲存單位⾥的；頁也是記憶體和磁碟互動的基本單位，資料庫從磁碟中讀取若⼲個頁⼤⼩的資料到記憶體，也將記憶體中若⼲個頁⼤⼩的資料重新整理到磁碟中。
⼀個頁的記憶體⼤⼩為16KB。

假設我們要執行這個SQL，得到了10條記錄：

SELECT * FROM INNODB_USER LIMIT 0 , 10;

假如一條記錄的資料大小是4K，那麼我們一個Page頁能存多少條資料呢？

16K 除以 4K 得到 4條記錄,對吧。

Page裡面的每一條資料都有一個關鍵的屬性叫做record_type
0 普通使用者記錄 1 目錄的索引記錄 2 最小 3 最大

畫個圖範例一下頁裡面資料是怎麼放的：

這個是我們的Page頁，每個Page頁都會存放資料，按照主鍵有序存放資料

我們知道資料的儲存是順序IO的，方便存放，可是存放方便那查詢是不是就不方便了，如果查的是最後一個是不是要遍歷整個頁的資料？

2.1問題

假如我們要查一條資料要怎麼查?怎麼才能快速查到資料？

• 如果我們Page頁中的資料是有連線方式的，想想我們學過的資料結構，哪種結構查詢快？
• 如果我們Page頁中的資料是有連線方式的，就能夠解決啊！沒錯，就是連結串列

Page頁中的資料是怎麼連線的(資料在同一個頁中)：

MySQL把頁中的資料通過單向連結串列連線起來，如果是根據主鍵去查詢，使用二分法定位會非常快，如果是根據非主鍵索引去查，只能從最小的一個個開始遍歷單向連結串列。

多個Page頁是怎麼建立連線（資料在不同的頁中）：

MySQL把不同的頁通過雙向向連結串列建立連結，這樣我們就可以通過上一頁找到下一頁，通過下一頁找到一頁，由於我們現在不能快速定位到資料的所在頁，我們只能從第一個頁沿著雙向連結串列一直往下找，在每個頁中再按照在同一頁的方式去查詢指定的記錄，這個也是全表掃描嘛。

2.2問題

當Page頁越來越多，查詢會出現什麼問題、怎麼解決怎麼優化？

當我們連結串列記錄變多，由於不能直接定位，我們出現了查詢緩慢問題，深入思考，所謂的查詢緩慢，其實就是下面兩個問題：

• 查詢時間的複雜度0（N）
• 讀寫磁碟的IO次數過多

我們想一下，平時看書時，想找某一頁的資料，怎麼做的？
查目錄對不對？目錄是個啥？不就是索引嘛！

百度上隨便找個目錄，貼個圖：

我們發現，這個目錄裡面有兩個很重要的資訊：

• 內容簡介（章節標題）
• 所在的頁碼

我們這個我們參考一個圖書的目錄的思想來達到我們快速查詢資料的目的：

給資料加一個目錄，查資料，我們先根據目錄頁找到資料在哪個頁的哪個地方，提升查詢效能。

可是，

2.3問題：怎麼建目錄呢？給每一個頁都建一個目錄嗎？

建目錄是不是要有規律？比如字典的目錄就是根據字母順序建立的，你想到了什麼？沒錯就是主鍵，Mysql裡自增的主鍵剛好符合我們的要求，有規律，內容還少，而且不可重複，真是完美的目錄，我們將每一頁的主鍵按規律儲存一下，新增一個指標指向資料的位置，查詢時直接根據主鍵大小，用二分法快速找到目錄，然後找到資料。
但是我們要給每一個資料頁都建目錄嗎？好像還必須如此，不給每一個頁建資料，你怎麼定位到頁裡的資料？難道全頁掃描嗎？
但是給每一個頁都建目錄，隨著目錄頁也出現多個，我們一個個目錄也去遍歷查詢效能也會下降。
我們可不可以給目錄建一個目錄？
於是，我們可以通過為目錄頁也建立一次目錄，向上抽取一層根結點，這樣就更加便於我們進行查詢了。

這棵樹，因為是根據主鍵儲存的，所以我們把它稱之為主鍵索引樹，因為主鍵索引樹裡儲存了我們的表裡的所有資料，那麼在MySQL中 索引即資料，資料即索引也是這個原因了。

這就是MysqlB+樹主鍵索引樹的資料結構，怎麼樣，是不是比你直接死記硬背得到的知識印象更深刻

2.4索引樹、頁的分裂與合併

我們找到了提升查詢效能的辦法，那麼，當Page頁出現增加、修改、刪除，都會遇到什麼問題？

如果是有序增加，新增一條資料怎麼辦？
頁寫滿了，那麼是不是得開啟一個新頁！
並且頁的資料必須滿足一個條件：下一個資料頁中使用者記錄的主鍵值必須大於上一個頁中使用者記錄的主鍵值
因為是有序增加，我們直接在頁的雙向連結串列末端增加一個頁即可。
那如果是無序增加，新增一條資料怎麼辦？

• 開啟一個新頁，並且找到資料的位置。
• 把舊資料移動到新頁，把新的資料放到有序的位置上。
• 葉子結點資料一直平移。
• 觸發葉子結點資料Page頁的分裂與合併觸發上層葉結點和根結點的再次分裂與合併。
• 這叫什麼，“牽一髮而動全身”,也叫做頁分裂！！

總結：Page頁出現增加、修改、刪除遇到的問題：

我們可以說，當無序增加、更新主鍵ID、刪除索引頁的更新操作時候，會有大量的樹結點調整，觸發子葉結點Page頁和上層葉結點和根節點頁的分頁與合併，造成大量磁碟碎片，損耗資料庫的效能，也就是解釋了我們為什麼不要在頻繁更新修改的列上建索引，或者是不要去更新主鍵。

讓我們總結一下：

聚集索引（聚簇索引）:

主鍵索引樹也叫聚集索引或者是聚簇索引，在InnoDB中一張表只有一個聚集索引樹，如果一張表建立了主鍵索引，那麼這個主鍵索引就是聚集索引，我們是根據聚集索引樹的鍵值，決定資料行的物理儲存順序，我們的聚集索引會對錶中的所有列進行排序儲存，索引即資料，資料即索引，指的就是我們的主鍵索引樹啦。

2.5根據我們剛才推演的，延申出幾個面試題

為什麼主鍵ID最好是趨勢遞增的？

你剛剛看完啊，不會沒記住吧，有序遞增，下一個資料頁中使用者記錄的主鍵值必須大於上一個頁中使用者的主鍵值，假如我是趨勢遞增，存入的資料肯定是在最末尾連結串列或者新增一個連結串列，就不會觸發頁的分裂與合併，導致新增的速度變慢。

三層B+數能存多少資料？

考察點：Page頁的大小，B+樹的定義
1GB = 1024 M， 1mb = 1024k，1k= 1024 bytes

答：
已知：索引邏輯單元 16bytes 位元組，16KB=16* 1024*1024，肯定比一千萬多，在InnoDB中B+樹的深度為3層就能滿足千萬級別的資料儲存。

mysql 大欄位為什麼要拆分？

一個Page頁可存放16K的資料，大欄位佔用大量的儲存空間，意味著一個Page頁可儲存的資料條數變少，那麼就需要更多的頁來儲存，需要更多的Page，意味著樹的深度會變高。那麼磁碟IO的次數會增加，效能下降，查詢更慢。大欄位不管是否被使用都會存放在索引上，佔據大量記憶體空間壓縮Page資料條數。

為什麼用B+樹？

B+樹的底層是多路平衡查詢樹，對於每一次的查詢的都是從根節點觸發，到子葉結點才存放資料，根節點和非葉子結點都是存放的索引指標，查詢葉子結點互，可以根據鍵值資料查詢。掃庫、掃表能力更強排序能力更強查詢效率和查詢效能穩定儲存能力更強、三層B+樹就能儲存千萬級別的資料。

3什麼是二級索引樹

剛才看的是根據主鍵得來的索引，我們如果不查主鍵，或者說表裡壓根就沒有主鍵，怎麼辦？我們還可以根據幾個欄位來建立聯合索引(組合索引聚合索引。。哎呀名字而已怎麼叫都行)。

根據主鍵得到的索引樹叫主鍵索引樹，根據別的欄位得到的索引樹叫二級索引樹。

通過下面的SQL 可以建立一個組合索引

ALTER TABLE INNODB_USER ADD INDEX
SECOND_INDEX_AGE_USERNAME_PHONE('age','user_name','phone');

其實，看似建立了1個索引，但是你使用 age 查詢 age，user_name 查詢 age，user_name，phone 都能生效
您也可以認為建立了三個這樣的索引：

ALTER TABLE INNODB__USER ADD INDEX
SECOND_INDEX_AGE__USERNAME_PHONE('age');
ALTER TABLE INNODB_USER ADD INDEX
SECOND_INDEX_AGE_USERNAME_PHONE('age','user_name');
ALTER TABLE `INNODB_USER`ADD INDEX
SECOND_INDEX_AGE_USERNAME_PHONE('age','user_name','phone');

3.1那麼二級索引樹怎麼排序？

首先需要知道參與排序的欄位型別是否有有序？

如果是有序欄位，就按照有序欄位排序比如（int） 1 2 3 4。
如果是無序欄位，按照這個列的字元集的排序規則來排序，這點不去深入，知道就好。

我現在有一個組合索引（A-B-C）他會按照你建立欄位的順序來進行排序：
如果A相同按照B排序，如果B相同按照C排序，如果ABC全部相同，會按照聚集索引進行排序。

我們的Page會根據組合索引的欄位建立順序來儲存資料，年齡 使用者名稱 手機號。
它的資料結構其實是一樣的

3.2索引橋的概念是什麼呢（最左匹配原則）？

還是上面那個索引，年齡使用者名稱手機號，age,username,phone
那麼可以看到我們第一個欄位是AGE，如果需要這個索引生效，是不是在查詢的時候需要先使用Age查詢，然後如果還需要user_name，就使用user_name。

只使用了user_name 能使用到索引嗎？
其實是不行的，因為我是先使用age進行排序的，你必須先命中age，再命中user_name，再命中phone，這個其實
就是我們所說的最左匹配原則。

最左其實就是因為我們是按照組合索引的順序來儲存的。大家常說的"索引橋"也是這個原因。命中組合索引必須是像過橋一樣，必須現在從第一塊木板走到第二塊木板再走到第三塊木板。

3.3回表、覆蓋索引、索引下推

二級索引樹有三個重要的概念，分別是回表、覆蓋索引、索引下推。.

回表就是：我們查詢的資料不在二級索引樹中需要拿到ID去主鍵索引樹找的過程。

覆蓋索引就是：我們需要查詢的資料都在二級索引樹中，直接返回這種情況就叫做覆蓋索引。
索引下推（index condition pushdown ）簡稱ICP：在Mysql5.6以後的版本上推出，用於優化回表查詢；
可以參考我寫的另一篇部落格：有詳細介紹

連結: MySQL 回表,覆蓋索引,索引下推

看完二級索引，

3.4延申幾個面試題：

為什麼離散度低的列不走索引？

離散度是什麼概念？相同的資料越多離散度越低，相同的資料越少離散度就越高。
請問都是相同的資料，怎麼排序？沒辦法排序啊？
在B+Tree 裡面重複值太多，MySQL的優化器發現走索引跟使用全表掃描差不了多少的時候，就算建立了索引也不會走。走不走索引，是MySQL的優化器去決定的。

索引是不是越多越好？

空間上：用空間換時間，索引是需要佔用磁碟空間的。
時間上：命中索引，加快我們的查詢效率，如果是更新刪除，會導致頁的分裂與合併，影響插入和更新語句的響應時間，反而延緩效能。
如果是頻繁需要更新的列，不建議建立索引，因為頻繁觸發頁的分裂與合併。

3.5二級索引樹的總結

也叫作組合索引（複合索引），二級索引樹儲存的是我們建立索引時候的儲存了列名順序來儲存的，它只儲存了建立二級索引列名的部分資料，二級索引樹是為了輔助我們查詢，提高查詢效率誕生的，二級索引樹裡有三個動作：回表、覆蓋索引、索引下推。其中，效能最高的是覆蓋索引。

4主鍵索引與二級索引的區別

網上找了一張區別圖

到此這篇關於MySQL索引詳解及演進過程以及延申出面試題的文章就介紹到這了,更多相關MySQL索引內容請搜尋it145.com以前的文章或繼續瀏覽下面的相關文章希望大家以後多多支援it145.com！