一、為什么用自增列作為主鍵

1、如果我們定義了主鍵(PRIMARY KEY)，那么InnoDB會選擇主鍵作為聚集索引。

如果沒有顯式定義主鍵，則InnoDB會選擇第一個不包含有NULL值的唯一索引作為主鍵索引。

如果也沒有這樣的唯一索引，則InnoDB會選擇內置6字節長的ROWID作為隱含的聚集索引(ROWID隨著行記錄的寫入而主鍵遞增，這個ROWID不像ORACLE的ROWID那樣可引用，是隱含的)。

2、數據記錄本身被存于主索引(一顆B+Tree)的葉子節點上，這就要求同一個葉子節點內(大小為一個內存頁或磁盤頁)的各條數據記錄按主鍵順序存放

因此每當有一條新的記錄插入時，MySQL會根據其主鍵將其插入適當的節點和位置，如果頁面達到裝載因子(InnoDB默認為15/16)，則開辟一個新的頁(節點)

3、如果表使用自增主鍵，那么每次插入新的記錄，記錄就會順序添加到當前索引節點的后續位置，當一頁寫滿，就會自動開辟一個新的頁

4、如果使用非自增主鍵(如果身份證號或學號等)，由于每次插入主鍵的值近似于隨機，因此每次新紀錄都要被插到現有索引頁得中間某個位置

此時MySQL不得不為了將新記錄插到合適位置而移動數據，甚至目標頁面可能已經被回寫到磁盤上而從緩存中清掉，此時又要從磁盤上讀回來，這增加了很多開銷

同時頻繁的移動、分頁操作造成了大量的碎片，得到了不夠緊湊的索引結構，后續不得不通過OPTIMIZE TABLE來重建表并優化填充頁面。

二、為什么使用數據索引能提高效率

數據索引的存儲是有序的

在有序的情況下，通過索引查詢一個數據是無需遍歷索引記錄的

極端情況下，數據索引的查詢效率為二分法查詢效率，趨近于 log2(N)

三、B+樹索引和哈希索引的區別

B+樹是一個平衡的多叉樹，從根節點到每個葉子節點的高度差值不超過1，而且同層級的節點間有指針相互鏈接，是有序的，如下圖：

哈希索引就是采用一定的哈希算法，把鍵值換算成新的哈希值，檢索時不需要類似B+樹那樣從根節點到葉子節點逐級查找，只需一次哈希算法即可,是無序的，如下圖所示：

四、哈希索引的優勢：

等值查詢，哈希索引具有絕對優勢(前提是：沒有大量重復鍵值，如果大量重復鍵值時，哈希索引的效率很低，因為存在所謂的哈希碰撞問題。)

五、哈希索引不適用的場景：

不支持范圍查詢

不支持索引完成排序

不支持聯合索引的最左前綴匹配規則

通常，B+樹索引結構適用于絕大多數場景，像下面這種場景用哈希索引才更有優勢：

在HEAP表中，如果存儲的數據重復度很低(也就是說基數很大)，對該列數據以等值查詢為主，沒有范圍查詢、沒有排序的時候，特別適合采用哈希索引，例如這種SQL：

#?僅等值查詢

select?id,?name?from?table?where?name='李明';?

而常用的 InnoDB 引擎中默認使用的是B+樹索引，它會實時監控表上索引的使用情況。

如果認為建立哈希索引可以提高查詢效率，則自動在內存中的“自適應哈希索引緩沖區”建立哈希索引(在InnoDB中默認開啟自適應哈希索引)。

通過觀察搜索模式，MySQL會利用index key的前綴建立哈希索引，如果一個表幾乎大部分都在緩沖池中，那么建立一個哈希索引能夠加快等值查詢。

注意：在某些工作負載下，通過哈希索引查找帶來的性能提升遠大于額外的監控索引搜索情況和保持這個哈希表結構所帶來的開銷。

但某些時候，在負載高的情況下，自適應哈希索引中添加的read/write鎖也會帶來競爭，比如高并發的join操作。like操作和%的通配符操作也不適用于自適應哈希索引，可能要關閉自適應哈希索引。

以上就是“數據庫面試題目及答案的詳細內容”，你能回答上來嗎?如果想要了解更多的Java面試題相關內容，可以關注動力節點Java官網。