20分鐘資料庫索引設計實戰

兜裡有辣條發表於2019-02-16
資料庫索引

在後端開發的工作中如何輕鬆、高效地設計大量資料庫索引呢？通過下面這四步，20分鐘後你就再也不會為資料庫的索引設計而發愁了。

順暢地閱讀這篇文章需要了解資料庫索引的組織方式，如果你還不熟悉的話，可以通過另一篇文章來快速瞭解一下——資料庫索引融會貫通。

這篇文章是一系列資料庫索引文章中的第三篇，這個系列包括了下面四篇文章：

  1. 資料庫索引是什麼？新華字典來幫你 —— 理解
  2. 資料庫索引融會貫通 —— 深入
  3. 20分鐘資料庫索引設計實戰 —— 實戰
  4. 資料庫索引為什麼用B+樹實現？ —— 擴充套件

這一系列涵蓋了資料庫索引從理論到實踐的一系列知識，一站式解決了從理解到融會貫通的全過程，相信每一篇文章都可以給你帶來更深入的體驗。

1. 整理查詢條件

我們設計索引的目的主要是為了加快查詢，所以，設計索引的第一步是整理需要用到的查詢條件，也就是我們會在where子句、join連線條件中使用的欄位。一般來說會整理程式中除了insert語句之外的所有SQL語句，按不同的表分別整理出每張表上的查詢條件。也可以根據對業務的理解新增一些暫時還沒有使用到的查詢條件。

對索引的設計一般會逐表進行，所以按資料表收集查詢條件可以方便後面步驟的執行。

2. 分析欄位的可選擇性

整理出所有查詢條件之後，我們需要分析出每個欄位的可選擇性，那麼什麼是可選擇性呢？

欄位的可選擇性指的就是欄位的值的區分度，例如一張表中儲存了使用者的手機號、性別、姓名、年齡這幾個欄位，且一個手機號只能註冊一個使用者。在這種情況下，像手機號這種唯一的欄位就是可選擇性最高的一種情況；而年齡雖然有幾十種可能，但是區分度就沒有手機號那麼大了；性別這樣的欄位則只有幾種可能，所以可選擇性最差。所以俺可選擇性從高到低排列就是：手機號 > 年齡 > 性別。

但是不同欄位的值分佈是不同的，有一些值的數量是大致均勻的，例如性別為男和女的值數量可能就差別不大，但是像年齡超過100歲這樣的記錄就非常少了。所以對於年齡這個欄位，20-30這樣的值就是可選擇性很小的，因為每一個年齡都有非常多的記錄；但是像100這樣的值，那它的可選擇性就非常高了。

如果我們在表中新增了一個欄位表示使用者是否是管理員，那麼在查詢網站的管理員資訊列表時，這個欄位的可選擇性就非常高。但是如果我們要查詢的是非管理員資訊列表時，這個欄位的可選擇性就非常低了。

從經驗上來說，我們會把可選擇性高的欄位放到前面，可選擇性低的欄位放在後面，如果可選擇性非常低，一般不會把這樣的欄位放到索引裡。

3. 合併查詢條件

雖然索引可以加快查詢的效率，但是索引越多就會導致插入和更新資料的成本變高，因為索引是分開儲存的，所有資料的插入和更新操作都要對相關的索引進行修改。所以設計索引時還需要控制索引的數量，不能盲目地增加索引。

一般我們會根據最左匹配原則來合併查詢條件，儘可能讓不同的查詢條件使用同一個索引。例如有兩個查詢條件where a = 1 and b = 1和where b = 1，那麼我們就可以建立一個索引idx_eg(b, a)來同時服務兩個查詢條件。

同時，因為範圍條件會終止使用索引中後續的欄位，所以對於使用範圍條件查詢的欄位我們也會盡可能放在索引的後面。

4. 考慮是否需要使用全覆蓋索引

最後，我們會考慮是否需要使用全覆蓋索引，因為全覆蓋索引沒有回表的開銷，效率會更高。所以一般我們會在回表成本特別高的情況下考慮是否使用全覆蓋索引，例如根據索引欄位篩選後的結果需要返回其他欄位或者使用其他欄位做進一步篩選的情況。

例如，我們有一張使用者表，其中有年齡、姓名、手機號三個欄位。我們需要查詢在指定年齡的所有使用者的姓名，已有索引idx_age_name(年齡, 姓名)，目前我們使用下面這樣的查詢語句進行查詢：

SELECT *
FROM 使用者表
WHERE 年齡 = ?;
複製程式碼

一般情況下，將一個索引優化為全覆蓋索引有兩種方式：

  1. 增加索引中的欄位，讓索引欄位覆蓋SQL語句中使用的所有欄位
    • 在這個例子中，我們可以建立一個同時包含所有欄位的索引idx_all(年齡, 姓名, 手機號)，以此提高查詢的效率。
  2. 減少SQL語句中使用的欄位，使SQL需要的欄位都包含在現有索引中
    • 在這個例子中，其實更好的方法是將SELECT子句修改為SELECT 姓名，因為我們的需求只是查詢使用者的姓名，並不需要手機號欄位，去掉SELECT子句多餘的欄位不僅能夠滿足我們的需求，而且也不用對索引做修改。

相關文章