1索引的概念

1.1定義

索引在關系型數(shù)據(jù)庫中，是一種單獨的、物理的對數(shù)據(jù)庫表中的一列或者多列值進行排序的一種存儲結(jié)構(gòu)，它是某個表中一列或者若干列值的集合，還有指向表中物理標識這些值的數(shù)據(jù)頁的邏輯指針清單。
索引的作用相當于圖書的目錄，可以根據(jù)目錄重點頁碼快速找到所需要的內(nèi)容，數(shù)據(jù)庫使用索引以找到特定值，然后順著指針找到包含該值的行，這樣可以是對應于表的SQL語句執(zhí)行得更快，可快速訪問數(shù)據(jù)庫表中的特定信息。

1.2類型

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

普通（Normal）：也叫非唯一索引，是普通索引，沒有任何限制。唯一（Unique）：唯一索引要求鍵值不能重復（可以為空），主鍵索引其實是一種特殊的唯一索引，不過他還多了一個限制條件，要求鍵值不能為空。主鍵索引用 primary key 創(chuàng)建。全文（Fulltext）：針對比較大的數(shù)據(jù)，比如我們存放是文章，課文，郵件，等等，有可能一個字段就需要幾kb，如果要解決like查詢在全文匹配的時候效率低下的問題，可以創(chuàng)建全文索引。只有文本類型的字段才可以創(chuàng)建全文索引，比如char、varchar、text。MyISAM和InnoDB都支持全文索引。

1.3作用

一句話總結(jié)：

索引能夠提高數(shù)據(jù)檢索的效率，降低數(shù)據(jù)庫的IO成本。

提出問題：我們用空間換時間，但是他的數(shù)據(jù)結(jié)構(gòu)、查詢的IO成本、以及是如何存儲數(shù)據(jù)的呢?

2索引的數(shù)據(jù)結(jié)構(gòu)B+樹的演進過程

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

頁是InnoDB管理存儲空間的基本單位，InnoDB將數(shù)據(jù)庫中的數(shù)據(jù)都是存儲在頁這個基本存儲單位?的；頁也是內(nèi)存和磁盤交互的基本單位，數(shù)據(jù)庫從磁盤中讀取若?個頁??的數(shù)據(jù)到內(nèi)存，也將內(nèi)存中若?個頁??的數(shù)據(jù)刷新到磁盤中。
?個頁的內(nèi)存??為16KB。

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

SELECT * FROM INNODB_USER LIMIT 0 , 10;

假如一條記錄的數(shù)據(jù)大小是4K，那么我們一個Page頁能存多少條數(shù)據(jù)呢？

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

Page里面的每一條數(shù)據(jù)都有一個關鍵的屬性叫做record_type
0 普通用戶記錄 1 目錄的索引記錄 2 最小 3 最大

畫個圖示例一下頁里面數(shù)據(jù)是怎么放的：

這個是我們的Page頁，每個Page頁都會存放數(shù)據(jù)，按照主鍵有序存放數(shù)據(jù)

我們知道數(shù)據(jù)的存儲是順序IO的，方便存放，可是存放方便那查詢是不是就不方便了，如果查的是最后一個是不是要遍歷整個頁的數(shù)據(jù)？

2.1問題

假如我們要查一條數(shù)據(jù)要怎么查?怎么才能快速查到數(shù)據(jù)？

• 如果我們Page頁中的數(shù)據(jù)是有連接方式的，想想我們學過的數(shù)據(jù)結(jié)構(gòu)，哪種結(jié)構(gòu)查詢快？
• 如果我們Page頁中的數(shù)據(jù)是有連接方式的，就能夠解決啊！沒錯，就是鏈表

Page頁中的數(shù)據(jù)是怎么連接的(數(shù)據(jù)在同一個頁中)：

MySQL把頁中的數(shù)據(jù)通過單向鏈表連接起來，如果是根據(jù)主鍵去查詢，使用二分法定位會非常快，如果是根據(jù)非主鍵索引去查，只能從最小的一個個開始遍歷單向鏈表。

多個Page頁是怎么建立連接（數(shù)據(jù)在不同的頁中）：

MySQL把不同的頁通過雙向向鏈表建立鏈接，這樣我們就可以通過上一頁找到下一頁，通過下一頁找到一頁，由于我們現(xiàn)在不能快速定位到數(shù)據(jù)的所在頁，我們只能從第一個頁沿著雙向鏈表一直往下找，在每個頁中再按照在同一頁的方式去查找指定的記錄，這個也是全表掃描嘛。

2.2問題

當Page頁越來越多，查詢會出現(xiàn)什么問題、怎么解決怎么優(yōu)化？

當我們鏈表記錄變多，由于不能直接定位，我們出現(xiàn)了查詢緩慢問題，深入思考，所謂的查詢緩慢，其實就是下面兩個問題：

• 查詢時間的復雜度0（N）
• 讀寫磁盤的IO次數(shù)過多

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

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

我們發(fā)現(xiàn)，這個目錄里面有兩個很重要的信息：

• 內(nèi)容簡介（章節(jié)標題）
• 所在的頁碼

我們這個我們參考一個圖書的目錄的思想來達到我們快速查詢數(shù)據(jù)的目的：

給數(shù)據(jù)加一個目錄，查數(shù)據(jù)，我們先根據(jù)目錄頁找到數(shù)據(jù)在哪個頁的哪個地方，提升查詢性能。

可是，

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

建目錄是不是要有規(guī)律？比如字典的目錄就是根據(jù)字母順序建立的，你想到了什么？沒錯就是主鍵，Mysql里自增的主鍵剛好符合我們的要求，有規(guī)律，內(nèi)容還少，而且不可重復，真是完美的目錄，我們將每一頁的主鍵按規(guī)律存儲一下，添加一個指針指向數(shù)據(jù)的位置，查詢時直接根據(jù)主鍵大小，用二分法快速找到目錄，然后找到數(shù)據(jù)。
但是我們要給每一個數(shù)據(jù)頁都建目錄嗎？好像還必須如此，不給每一個頁建數(shù)據(jù)，你怎么定位到頁里的數(shù)據(jù)？難道全頁掃描嗎？
但是給每一個頁都建目錄，隨著目錄頁也出現(xiàn)多個，我們一個個目錄也去遍歷查詢性能也會下降。
我們可不可以給目錄建一個目錄？
于是，我們可以通過為目錄頁也建立一次目錄，向上抽取一層根結(jié)點，這樣就更加便于我們進行查詢了。

這棵樹，因為是根據(jù)主鍵存儲的，所以我們把它稱之為主鍵索引樹，因為主鍵索引樹里存儲了我們的表里的所有數(shù)據(jù)，那么在MySQL中 索引即數(shù)據(jù)，數(shù)據(jù)即索引也是這個原因了。

這就是MysqlB+樹主鍵索引樹的數(shù)據(jù)結(jié)構(gòu)，怎么樣，是不是比你直接死記硬背得到的知識印象更深刻

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

我們找到了提升查詢性能的辦法，那么，當Page頁出現(xiàn)增加、修改、刪除，都會遇到什么問題？

如果是有序增加，新增一條數(shù)據(jù)怎么辦？
頁寫滿了，那么是不是得開啟一個新頁！
并且頁的數(shù)據(jù)必須滿足一個條件：下一個數(shù)據(jù)頁中用戶記錄的主鍵值必須大于上一個頁中用戶記錄的主鍵值
因為是有序增加，我們直接在頁的雙向鏈表末端增加一個頁即可。
那如果是無序增加，新增一條數(shù)據(jù)怎么辦？

• 開啟一個新頁，并且找到數(shù)據(jù)的位置。
• 把舊數(shù)據(jù)移動到新頁，把新的數(shù)據(jù)放到有序的位置上。
• 葉子結(jié)點數(shù)據(jù)一直平移。
• 觸發(fā)葉子結(jié)點數(shù)據(jù)Page頁的分裂與合并觸發(fā)上層葉結(jié)點和根結(jié)點的再次分裂與合并。
• 這叫什么，“牽一發(fā)而動全身”,也叫做頁分裂?。?/li>

總結(jié)：Page頁出現(xiàn)增加、修改、刪除遇到的問題：

我們可以說，當無序增加、更新主鍵ID、刪除索引頁的更新操作時候，會有大量的樹結(jié)點調(diào)整，觸發(fā)子葉結(jié)點Page頁和上層葉結(jié)點和根節(jié)點頁的分頁與合并，造成大量磁盤碎片，損耗數(shù)據(jù)庫的性能，也就是解釋了我們?yōu)槭裁?strong>不要在頻繁更新修改的列上建索引，或者是不要去更新主鍵。

讓我們總結(jié)一下：

聚集索引（聚簇索引）:

主鍵索引樹也叫聚集索引或者是聚簇索引，在InnoDB中一張表只有一個聚集索引樹，如果一張表創(chuàng)建了主鍵索引，那么這個主鍵索引就是聚集索引，我們是根據(jù)聚集索引樹的鍵值，決定數(shù)據(jù)行的物理存儲順序，我們的聚集索引會對表中的所有列進行排序存儲，索引即數(shù)據(jù)，數(shù)據(jù)即索引，指的就是我們的主鍵索引樹啦。

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

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

你剛剛看完啊，不會沒記住吧，有序遞增，下一個數(shù)據(jù)頁中用戶記錄的主鍵值必須大于上一個頁中用戶的主鍵值，假如我是趨勢遞增，存入的數(shù)據(jù)肯定是在最末尾鏈表或者新增一個鏈表，就不會觸發(fā)頁的分裂與合并，導致添加的速度變慢。

三層B+數(shù)能存多少數(shù)據(jù)？

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

答：
已知：索引邏輯單元 16bytes 字節(jié)，16KB=16* 1024*1024，肯定比一千萬多，在InnoDB中B+樹的深度為3層就能滿足千萬級別的數(shù)據(jù)存儲。

mysql 大字段為什么要拆分？

一個Page頁可存放16K的數(shù)據(jù)，大字段占用大量的存儲空間，意味著一個Page頁可存儲的數(shù)據(jù)條數(shù)變少，那么就需要更多的頁來存儲，需要更多的Page，意味著樹的深度會變高。那么磁盤IO的次數(shù)會增加，性能下降，查詢更慢。大字段不管是否被使用都會存放在索引上，占據(jù)大量內(nèi)存空間壓縮Page數(shù)據(jù)條數(shù)。

為什么用B+樹？

B+樹的底層是多路平衡查找樹，對于每一次的查詢的都是從根節(jié)點觸發(fā)，到子葉結(jié)點才存放數(shù)據(jù)，根節(jié)點和非葉子結(jié)點都是存放的索引指針，查找葉子結(jié)點互，可以根據(jù)鍵值數(shù)據(jù)查詢。掃庫、掃表能力更強排序能力更強查詢效率和查詢性能穩(wěn)定存儲能力更強、三層B+樹就能存儲千萬級別的數(shù)據(jù)。

3什么是二級索引樹

剛才看的是根據(jù)主鍵得來的索引，我們?nèi)绻徊橹麈I，或者說表里壓根就沒有主鍵，怎么辦？我們還可以根據(jù)幾個字段來創(chuàng)建聯(lián)合索引(組合索引聚合索引。。哎呀名字而已怎么叫都行)。

根據(jù)主鍵得到的索引樹叫主鍵索引樹，根據(jù)別的字段得到的索引樹叫二級索引樹。

通過下面的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。
如果是無序字段，按照這個列的字符集的排序規(guī)則來排序，這點不去深入，知道就好。

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

我們的Page會根據(jù)組合索引的字段建立順序來存儲數(shù)據(jù)，年齡 用戶名 手機號。
它的數(shù)據(jù)結(jié)構(gòu)其實是一樣的

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

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

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

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

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

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

回表就是：我們查詢的數(shù)據(jù)不在二級索引樹中需要拿到ID去主鍵索引樹找的過程。

覆蓋索引就是：我們需要查詢的數(shù)據(jù)都在二級索引樹中，直接返回這種情況就叫做覆蓋索引。
索引下推（index condition pushdown ）簡稱ICP：在Mysql5.6以后的版本上推出，用于優(yōu)化回表查詢；
可以參考我寫的另一篇博客：有詳細介紹

鏈接: MySQL 回表,覆蓋索引,索引下推

看完二級索引，

3.4延申幾個面試題：

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

離散度是什么概念？相同的數(shù)據(jù)越多離散度越低，相同的數(shù)據(jù)越少離散度就越高。
請問都是相同的數(shù)據(jù)，怎么排序？沒辦法排序??？
在B+Tree 里面重復值太多，MySQL的優(yōu)化器發(fā)現(xiàn)走索引跟使用全表掃描差不了多少的時候，就算建立了索引也不會走。走不走索引，是MySQL的優(yōu)化器去決定的。

索引是不是越多越好？

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

3.5二級索引樹的總結(jié)

也叫作組合索引（復合索引），二級索引樹存儲的是我們創(chuàng)建索引時候的保存了列名順序來存儲的，它只保存了創(chuàng)建二級索引列名的部分數(shù)據(jù)，二級索引樹是為了輔助我們查詢，提高查詢效率誕生的，二級索引樹里有三個動作：回表、覆蓋索引、索引下推。其中，性能最高的是覆蓋索引。

4主鍵索引與二級索引的區(qū)別

網(wǎng)上找了一張區(qū)別圖

到此這篇關于MySQL索引詳解及演進過程以及延申出面試題的文章就介紹到這了,更多相關MySQL索引內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關文章希望大家以后多多支持腳本之家！

最新評論