MySQL索引知識點分析

1索引的概念

1.1定義

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

1.2型別

InnoDB包含三種索引類型，分別為普通索引、唯一索引（主鍵索引為一種特殊非空白的唯一索引）、全文索引。

重寫為：普通索引又稱為非唯一索引，沒有任何限制。唯一（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以后的版本上推出，用于优化回表查询；

3.4延申几个面试题：

为什么离散度低的列不走索引？

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

索引是不是越多越好？

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

3.5二級索引樹的總結

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

4主鍵索引與二級索引的差異

網路上找了一張區別圖

以上是MySQL索引知識點分析的詳細內容。更多資訊請關注PHP中文網其他相關文章！