mysql怎么提升索引的工作效率

mysql怎么提升索引的工作效率？針對這個問題，今天小編總結這篇有關索引的文章，希望能幫助更多想解決這個問題的朋友找到更加簡單易行的辦法。

1.索引是什么東西？

索引就是一個數據結構，我們把表中的記錄用一個適合高效查找的數據結構來表示，目的就是讓查詢變得更高效。

2.它到底怎么運作的？

這個問題就說來話長了，且聽我慢慢道來：

在mysql中使用最廣泛的數據引擎是InnoDB 引擎，它里面用的是 B+ 樹索引。

我們重點分析一下這個索引的原理：

要想理解B+樹索引要先從 二叉查找樹，平衡二叉樹和 B 樹說起因為B+樹索引就是由他們演化而來：

什么是二叉查找樹？

滿足這樣條件的就叫二叉查找樹：

每個節點左邊節點的值都小于該節點，右邊節點的值都大于該節點，沒有值相等的節點，最頂端的節點也就是“45”被稱為根節點。

二叉查找樹的查找過程：

若根結點的值等于查找的值，成功，

否則，若小于根結點的值，遞歸查左子樹（也就是根節點左邊的所有節點形成的樹）

若大于根結點的值，遞歸查右子樹（也就是根節點右邊所有節點形成的樹）。

假設用二叉查找樹創建book表的索引：

索引如下：

圖一

此處的bid為主鍵，每個節點存儲了主鍵的值和該條記錄的內容。

如果我要查找bid為6的圖書的信息，則先用6和根節點的主鍵值7比較發現比7小，

然后6再和7左邊的節點5比較發現比5大找到5右邊的節點6，找到了，取出6對應的記錄行的值ee.

總共經歷了3次比較，如果掃描全表需要經過5次比較。

什么是平衡二叉樹？

如果索引是這樣：

圖二

想要找到主鍵鍵值為9的記錄就需要6次比較，索引的優勢完全體現不出來。

為什么會這樣？原因就在于這棵樹太高了，如果能想辦法把它變得矮一點，胖一點就完美了。于是平衡二叉樹閃亮登場：

平衡二叉樹首先也是一個二叉樹，需要滿足二叉樹的所有條件，然后有所改進，規定了左右子樹的高度差不能超過1，如果插入數據導致高度差超過了1則自動進行調整，回復到平衡狀態。這也是平衡二叉樹名字的由來。

圖一就是一顆平衡二叉樹，圖二根節點的左子樹高度為0，右子樹高度為5，高度差是5超過了1所以不是一顆平衡二叉樹。

平衡二叉樹查找效率要高于二叉樹。

什么是B樹？

由前面的推導我們可以看出要想查找，比較的次數最少，必須想辦法降低樹形結構的高度，不管是二叉樹還是平衡二叉樹，每個節點最多只能有兩個子節點，這就注定了它的高度受限于子節點的個數，于是B樹橫空出世.

從上圖可以看到B樹的節點可以不止兩個子節點，這樣的好處就是樹可以變得又矮又胖，矮胖的樹是索引的最愛，用它做索引可以降低磁盤的IO.

B樹中的每個節點根據實際情況可以包含大量的鍵值，數據和指針，上圖所示為一個3階的B樹：

每個節點占用一個盤塊的磁盤空間，一個節點上有兩個升序排序的鍵值和三個指向子樹根節點的指針，指針存儲的是子節點所在磁盤塊的地址。兩個鍵值劃分成的三個范圍域對應三個指針指向的子樹的數據的范圍域。以根節點為例，鍵值為17和35，P1指針指向的子樹的數據范圍為小于17，P2指針指向的子樹的數據范圍為17~35，P3指針指向的子樹的數據范圍為大于35。

模擬查找關鍵字29的過程：

根據根節點找到磁盤塊1，讀入內存。【磁盤I/O操作第1次】

比較關鍵字29在區間（17,35），找到磁盤塊1的指針P2。

根據P2指針找到磁盤塊3，讀入內存。【磁盤I/O操作第2次】

比較關鍵字29在區間（26,30），找到磁盤塊3的指針P2。

根據P2指針找到磁盤塊8，讀入內存。【磁盤I/O操作第3次】

在磁盤塊8中的關鍵字列表中找到關鍵字29。

分析上面過程，發現需要3次磁盤I/O操作，和3次內存查找操作。由于內存中的鍵值是一個有序表結構，可以利用二分法查找提高效率。而3次磁盤I/O操作是影響整個B樹查找效率的決定因素。

什么是B+樹？

想想還有沒有可能進一步優化，在B樹中每個節點的內容由三部分組成：鍵值，指針，數據，而磁盤塊的容量是有限的，并不是每次讀取磁盤塊都會取出里面的數據，只是在最后一次讀取的時候才會取出里面的數據，能不能將數據只存儲在葉子節點里面，非葉子節點只存儲鍵值和指針呢？這樣就能最大化的利用磁盤塊空間，一個磁盤塊也就能存更多的東西了，沒錯，B+樹就是這么干的

假設在非葉子節點不存數據以后每個節點可以存儲4個鍵值和指針，就變成了上圖的B+樹

B+Tree相對于B-Tree有幾點不同：

1.    非葉子節點只存儲鍵值和指針。

2.    所有葉子節點之間都有一個鏈指針。

3.    數據記錄都存放在葉子節點中。

在B+樹上有兩個頭指針，一個頭指針指向根節點，可以進行從根節點開始的查找，還有一個頭指針指向鍵值最小的葉子節點，因為鍵值是按順序排列的所以可以進行鍵值的范圍查找。

在B+樹中由于一個節點存儲了更多的鍵值和指針，所以同樣的多的內容可以降低樹的高度，減少磁盤io次數，從而提高效率。

看完這篇文章，你掌握mysql提升索引的工作效率的方法了嗎？如果還想學到更多技能或想了解更多相關內容，歡迎關注億速云行業資訊頻道，感謝各位的閱讀。