歡迎光臨
每天分享高質量文章

面試小知識:MySQL索引相關

來自:苦逼的碼農(微信號:di201805)

作者:帥地

個人簡介:一個熱愛編程的在校生,我的世界不只有coding,還有writing。目前維護訂閱號「苦逼的碼農」,專註於寫「演算法與資料結構」,「Java」,「計算機網絡」。

前言

本模板主要是一些面試相關的題目,對於每一道問題,我會提供簡單的解答,答案的來源主要是基於自己看了各方資料之後的理解,如果有錯的,歡迎指點出來。

1、什麼是最左前綴原則?

以下回答全部是基於MySQL的InnoDB引擎

例如對於下麵這一張表

如果我們按照 name 欄位來建立索引的話,採用B+樹的結構,大概的索引結構如下

如果我們要進行模糊查找,查找name 以“張”開頭的所有人的ID,即 sql 陳述句為

1select ID from table where name like '張%'

由於在B+樹結構的索引中,索引項是按照索引定義裡面出現的欄位順序排序的,索引在查找的時候,可以快速定位到 ID 為 100的張一,然後直接向右遍歷所有開頭的人,直到條件不滿足為止。

也就是說,我們找到第一個滿足條件的人之後,直接向右遍歷就可以了,由於索引是有序的,所有滿足條件的人都會聚集在一起。

而這種定位到最左邊,然後向右遍歷尋找,就是我們所說的最左前綴原則

2、為什麼用 B+ 樹做索引而不用哈希表做索引?

1、哈希表是把索引欄位映射成對應的哈希碼然後再存放在對應的位置,這樣的話,如果我們要進行模糊查找的話,顯然哈希表這種結構是不支持的,只能遍歷這個表。而B+樹則可以通過最左前綴原則快速找到對應的資料。

2、如果我們要進行範圍查找,例如查找ID為100 ~ 400的人,哈希表同樣不支持,只能遍歷全表。

3、索引欄位通過哈希映射成哈希碼,如果很多欄位都剛好映射到相同值的哈希碼的話,那麼形成的索引結構將會是一條很長的鏈表,這樣的話,查找的時間就會大大增加。

3、主鍵索引和非主鍵索引有什麼區別?

例如對於下麵這個表(其實就是上面的表中增加了一個k欄位),且ID是主鍵。

主鍵索引和非主鍵索引的示意圖如下:

其中R代表一整行的值。

從圖中不難看出,主鍵索引和非主鍵索引的區別是:非主鍵索引的葉子節點存放的是主鍵的值,而主鍵索引的葉子節點存放的是整行資料,其中非主鍵索引也被稱為二級索引,而主鍵索引也被稱為聚簇索引

根據這兩種結構我們來進行下查詢,看看他們在查詢上有什麼區別。

1、如果查詢陳述句是 select * from table where ID = 100,即主鍵查詢的方式,則只需要搜索 ID 這棵 B+樹。

2、如果查詢陳述句是 select * from table where k = 1,即非主鍵的查詢方式,則先搜索k索引樹,得到ID=100,再到ID索引樹搜索一次,這個過程也被稱為回表。

現在,知道他們的區別了吧?

4、為什麼建議使用主鍵自增的索引?

對於這顆主鍵索引的樹

如果我們插入 ID = 650 的一行資料,那麼直接在最右邊插入就可以了

但是如果插入的是 ID = 350 的一行資料,由於 B+ 樹是有序的,那麼需要將下麵的葉子節點進行移動,騰出位置來插入 ID = 350 的資料,這樣就會比較消耗時間,如果剛好 R4 所在的資料頁已經滿了,需要進行頁分裂操作,這樣會更加糟糕。

但是,如果我們的主鍵是自增的,每次插入的 ID 都會比前面的大,那麼我們每次只需要在後面插入就行, 不需要移動位置、分裂等操作,這樣可以提高性能。也就是為什麼建議使用主鍵自增的索引。

    赞(0)

    分享創造快樂