Mysql性能優化:為什麼要用覆蓋索引?

語言: CN / TW / HK
  • 相信讀者看過很多MYSQL索引優化的文章,其中有很多優化的方法,比如最佳左前綴,覆蓋索引等方法,但是你真正理解為什麼要使用最佳左前綴,為什麼使用覆蓋索引會提升查詢的效率嗎?
  • 本篇文章將從MYSQL內部結構上講一下為什麼覆蓋索引能夠提升效率。

InnoDB索引模型

  • 在InnoDB中,表都是根據主鍵順序以索引的形式存放的,這種存儲方式的表稱為索引組織表。又因為前面我們提到的,InnoDB使用了B+樹索引模型,所以數據都是存儲在B+樹中的。
  • 每一個索引在InnoDB裏面對應一棵B+樹

主鍵索引和非主鍵索引的區別

  • 主鍵索引又叫聚簇索引 ,非主鍵索引又叫普通索引,那麼這兩種索引有什麼區別呢?
  • 主鍵索引的葉子節點存放的是整行數據,非主鍵索引的葉子節點存放的是主鍵的值。
  • 假設有一張User表(id,age,name,address),其中有id和age兩個字段,其中id是主鍵,age是普通索引,有幾行數據u1-u5的(id,age)的值是(100,1)、(200,2)、(300,3)、(500,5)和(600,6) ,此時的兩棵樹的示例如下:

 

  • 從上圖可以看出來,基於主鍵索引的樹的葉子節點存放的是整行User數據,基於普通索引age的葉子節點存放的是id(主鍵)的值。

什麼是回表?

  • 假設有一條查詢語句如下:
  select * from user where age=3;
  • 上面這條sql語句執行的過程如下:
    1、根據age這個普通索引在age索引樹上搜索,得到主鍵id的值為300。
    2、因為age索引樹並沒有存儲User的全部數據,因此需要根據在age索引樹上查詢到的主鍵id的值300再到id索引樹搜索一次,查詢到了u3。
    3、返回結果。
  • 上述執行的過程中,從age索引樹再到id索引樹的查詢的過程叫做回表(回到主鍵索引樹搜索的過程)。
  • 也就是説通過非主鍵索引的查詢需要多掃描一棵索引樹,因此需要儘量使用主鍵索引查詢。

為什麼使用覆蓋索引?

  • 有了上述提及到的幾個概念,便能很清楚的理解為什麼覆蓋索引能夠提升查詢效率了,因為少了一次回表的過程。
  • 假設我們使用覆蓋索引查詢,語句如下:
  select id from user where age=3;
  • 這條語句執行過程很簡單,直接在age索引樹中就能查詢到id的值,不用再去id索引樹中查找其他的數據,避免了回表。

總結

  • 覆蓋索引的使用能夠減少樹的搜索次數,避免了回表,顯著提升了查詢性能,因此覆蓋索引是一個常用的性能優化手段。

以上內容希望幫助到大家,很多PHPer在進階的時候總會遇到一些問題和瓶頸,業務代碼寫多了沒有方向感,更多PHP大廠PDF面試文檔,PHP進階架構視頻資料,PHP精彩好文免費獲取可以關注公眾號:PHP開源社區,或者訪問:

2021金三銀四大廠面試真題集錦,必看!

四年精華PHP技術文章整理合集——PHP框架篇

四年精華PHP技術文合集——微服務架構篇

四年精華PHP技術文合集——分佈式架構篇

四年精華PHP技術文合集——高併發場景篇

四年精華PHP技術文章整理合集——數據庫篇