數據庫 -- 索引并不是萬能的

在這里插入圖片描述

索引是對數據庫表中一列或多列的值進行排序的一種結構，使用索引可快速訪問數據庫表中的特定信息。如果想按特定職員的姓來查找他或她，則與在表中搜索所有的行相比，索引有助于更快地獲取信息。但是索引也不是萬能的 ，有時候發現我們 sql 中索引不生效的，我們深入理解下索引的原理，以及誤區,

---

InnoDB是如何存儲數據的？

MySQL把數據存儲和查詢操作抽象成了存儲引擎，不同的存儲引擎，對數據的存儲和讀取方式各不相同。MySQL支持多種存儲引擎，并且可以以表為粒度設置存儲引擎。因為支持事物，我們最常用的是InnoDB

雖然數據保存在磁盤中，但其處理是在內存進行的。為了減少磁盤隨機讀取次數，InnoDB 采用頁而不是行但粒度來保存數據，即數據被分成若干頁，以頁為單位保存在磁盤中，InnoDB的頁大小，一般是16kb。各頁中又一個頁目錄，方便按照主鍵查詢記錄。

數據頁結構：

在這里插入圖片描述

頁目錄通過槽把記錄分成不同的小組，沒個小組有若干條記錄。如圖所示，記錄中最前面的小方塊的數字，代表的是當前分組的記錄條數，最小和最大的槽指向 2個特殊的偽記錄。有了槽之后，我們按照主鍵搜索頁中記錄時，就可以采用二分法快速搜索，無需從最小記錄開始遍歷整個頁中記錄鏈表。

舉例：搜索主鍵(pk) = 15的記錄

• 先二分得出槽中間位是(0+6)/2=3 , 看到其指向的記錄是 12 < 15 , 所以需要從 #3 槽后繼續搜索；
• 再使用二分搜索出 #3槽和 #6槽的中間位 (3+6)/2=4.5 取整4，#4槽對應的記錄是 16 > 15,所以記錄一定在#4槽中；
• 在從 #3 槽指向的12號記錄開始向下搜索3次，定位到15號記錄。

---

聚簇索引和非聚簇索引

InnoDB中，表數據文件本身就是按B+Tree組織的一個索引結構，聚簇索引就是按照每張表的主鍵構造一顆B+樹，同時葉子節點中存放的就是整張表的行記錄數據，也將聚集索引的葉子節點稱為數據頁。這個特性決定了索引組織表中數據也是索引的一部分；

一般建表會用一個自增主鍵做聚簇索引，沒有的話MySQL會默認創建，但是這個主鍵如果更改代價較高，故建表時要考慮自增ID不能頻繁update這點。

我們日常工作中，根據實際情況自行添加的索引都是輔助索引，輔助索引就是一個為了需找主鍵索引的二級索引，現在找到主鍵索引再通過主鍵索引找數據；

• B+ 樹的特點包括：

• 最底層的節點叫作葉子節點，用來存放數據；

• 其他上層節點叫作非葉子節點，僅用來存放目錄項，作為索引；

• 非葉子節點分為不同層次，通過分層來降低每一層的搜索量；

• 所有節點按照索引鍵大小排序，構成一個雙向鏈表，加速范圍查找。

• 因此，InnoDB 使用 B+ 樹，既可以保存實際數據，也可以加速數據搜索，這就是聚簇索 引。如果把上圖葉子節點下面方塊中的省略號看作實際數據的話，那么它就是聚簇索引的示 意圖。由于數據在物理上只會保存一份，所以包含實際數據的聚簇索引只能有一個。

• InnoDB 會自動使用主鍵(唯一定義一條記錄的單個或多個字段)作為聚簇索引的索引鍵 (如果沒有主鍵，就選擇第一個不包含 NULL 值的唯一列)。上圖方框中的數字代表了索 引鍵的值，對聚簇索引而言一般就是主鍵。

為了實現非主鍵字段的快速搜索，就引出了二級索引，也叫作非聚簇索引、輔助索引。二級索引，也是利用的 B + 數的數據結構

這次二級索引的葉子節點中保存的不是實際數據，而是主鍵，獲得主鍵值后去聚簇索引中獲 得數據行。這個過程就叫作回表。

回表是什么意思？就是你執行一條sql語句，需要從兩個b+索引中去取數據

表tbl有a,b,c三個字段，其中a是主鍵，b上建了索引，然后編寫sql語句

SELECT * FROM tbl WHERE a=1

這樣不會產生回表，因為所有的數據在a的索引樹中均能找到

SELECT * FROM tbl WHERE b=1

這樣就會產生回表，因為where條件是b字段，那么會去b的索引樹里查找數據，但b的索引里面只有a,b兩個字段的值，沒有c，那么這個查詢為了取到c字段，就要取出主鍵a的值，然后去a的索引樹去找c字段的數據。查了兩個索引樹，這就叫回表。索引覆蓋就是查這個索引能查到你所需要的所有數據，不需要去另外的數據結構去查。其實就是不用回表。

---

考慮額外創建二級索引的代價

創建二級索引的代價，主要表現在維護代價、空間代價和回表代價三個方面。

• 維護代價：創建 N 個二級索引，就需要再創建 N 棵 B+ 樹，新增數據時不僅要修改聚簇索引，還需要修改這 N 個二級索引。
• 空間代價：雖然二級索引不保存原始數據，但要保存索引列的數據，所以會占用更多的空間
• 回表代碼：二級索引不保存原始數據，通過索引找到主鍵后需要再查詢聚簇索引，才能得到我們想要的數據

---

不是所有針對索引列的查詢都能用上索引

1. 索引只能匹配列前綴

比如下面的 LIKE 語句，搜索 name 后綴為 name123 的用戶無法走索引，執行計劃的 type=ALL 代表了全表掃描：

EXPLAIN?SELECT?*?FROM?person?WHERE?NAME?LIKE?'%name123'?LIMIT?100
在這里插入圖片描述

把百分號放到后面走前綴匹配，type=range 表示走索引掃描，key=name_score 看到實際走了索引

EXPLAIN?SELECT?*?FROM?person?WHERE?NAME?LIKE?'name123%'?LIMIT?100
在這里插入圖片描述

2. 條件涉及函數操作無法走索引。

比如搜索條件用到了 LENGTH 函數，肯定無法走索引

EXPLAIN?SELECT?*?FROM?person?WHERE?LENGTH(NAME)=7
在這里插入圖片描述

3.聯合索引只能匹配左邊的列

對 name 和 score 建了聯合索引，但是僅按照 score 列搜索無法走索引

EXPLAIN?SELECT?*?FROM?person?WHERE?SCORE>45678

個人博客地址：http://blog.yanxiaolong.cn/

總結

以上是生活随笔為你收集整理的创建了联合索引还用在单个字段上创建索引吗_数据库 索引并不是万能的的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。