普通索引和唯一索引，應該怎麼選擇？

Love&Share發表於2022-03-03
索引

1）什麼是唯一索引？

  • 不允許具有索引值相同的行，比如身份證唯一的

案例：假設你在維護一個市民系統，每個人都有一個唯一的身份證號，而且業務程式碼已經保證了不會寫入兩個重複的身份證號。如果市民系統需要按照身份證號查姓名，那該如何建索引？

 select name from CUser where id_card = 'xxxxxxxyyyyyyzzzzz';

2)為什麼說不建議把身份證號當做主鍵？

  • 身份證號欄位比較大

3)對上面的語句建索引我們該怎麼建？

  • 業務程式碼已經保證了不會寫入重複的身份證號，索引給 id_card 欄位建立唯一索引，或者建立一個普通索引。

4）從效能的角度考慮，應該選擇唯一索引還是普通索引呢？

  • 看下面的分析

案例：從這兩種索引對查詢語句和更新語句的效能影響來進行分析

 

 

 

4.1)select id from T where k=5在索引樹上查詢的過程在怎樣的？

  • 先通過 B+ 樹從樹根開始，按層搜尋到葉子節點，圖中右下角的這個資料頁，然後在資料頁內部（陣列）通過二分法來定位記錄。

4.1.1）如果K是普通索引，那什麼時候停止查詢？

  • 找到滿足條件的第一個記錄 (5,500) 後，需要查詢下一個記錄，直到碰到第一個不滿足 k=5 條件的記錄。

4.1.2）如果K是唯一索引，那什麼時候停止查詢？

  • 由於索引定義了唯一性，找到第一個滿足的就停止，不再往後找。

4.1.3）4.1.1和4.1.2這個不同帶來的效能差距會有多少呢？

  • 基本沒有

4.1.4）為什麼4.1.1和4.1.2幾乎沒有差距？

  • InnoDB 讀資料不是一條一條從磁碟讀到記憶體的，是一頁一頁讀出來的，K=5和它的下一條都在這一頁，所以普通索引多做的那次查詢不費力。

4.1.5）InnoDB 中，每個資料頁的大小預設是多少？

  • 16KB

看完了對查詢的影響，接下來我們來看看普通索引和唯一索引對更新語句效能的影響

4.2）什麼是change buffer？

  • 要更新一個資料頁時，如果資料頁在記憶體中就直接更新。

  • 資料頁不在記憶體中的話，在不影響資料一致性的前提下InnoDB 會將這些更新操作丟到 change buffer 中，這樣就不需要從磁碟中讀入這個資料頁了。等下次有人查詢的時候把資料頁讀到記憶體中了。那就順道更新了。

4.2.1）change buffer只是存在於記憶體中嗎？

  • 記憶體中有副本，磁碟中也有。

4.2.2）將change buffer 中的操作應用到原資料頁這個過程叫什麼？

  • merge

4.2.3）merge在哪些時刻會發生？

  • 訪問這個要更新的資料頁會觸發 merge

  • 系統有後臺執行緒會定期 merge

  • 據庫正常關閉（shutdown）的過程中，也會執行 merge。

4.2.4）change buffer的優點有哪些？

  • 減少讀磁碟，語句的執行速度會得到明顯的提升。

  • 資料讀入記憶體會佔用 buffer pool，這種方式還能夠避免佔用記憶體，提高記憶體利用率。

4.2.5）什麼條件下可以使用 change buffer 呢？

  • 對於唯一索引來說，所有的更新操作都要先判斷這個操作是否違反唯一性約束。比如，要插入 (4,400) 這個記錄，就要先判斷現在表中是否已經存在 k=4 的記錄，而這必須要將資料頁讀入記憶體才能判斷。如果都已經讀入到記憶體了，那直接更新記憶體會更快，就沒必要使用 change buffer 了。

  • 所以唯一索引不能用change buffer，只有普通索引才能用。

4.2.6）change buffer的大小是多少？

  • 它是 buffer pool 裡的記憶體，因此不能無限增大。

  • 通過引數 innodb_change_buffer_max_size可以設定大小。

  • 設定為 50 的時候，表示 change buffer 的大小最多隻能佔用 buffer pool 的 50%。

4.3）如果要在上面那張表中插入一個新記錄 (4,400) 的話，InnoDB 的處理流程是怎樣的？

  • 目標頁剛好在記憶體：

    • 對唯一索引來說，找到 3 和 5 之間的位置，判斷到沒有衝突，插入這個值，語句執行結束；

    • 對於普通索引來說，找到 3 和 5 之間的位置，插入這個值，語句執行結束。

  • 目標頁不在記憶體中：

    • 對於唯一索引來說，需要將資料頁讀入記憶體，判斷到沒有衝突，插入這個值，語句執行結束；

    • 對於普通索引來說，則是將更新記錄在 change buffer，語句執行就結束了。

4.4）通過上面的分析，你知道更新的時候該選擇唯一索引還是普通索引了嗎？為什麼？

  • 將資料從磁碟讀入記憶體涉及隨機 IO 的訪問，是資料庫裡面成本最高的操作之一。change buffer 因為減少了隨機磁碟訪問，所以對更新效能的提升是會很明顯的。

4.5）因為唯一索引和普通索引的差距造成過哪些實際bug?

  • 有個 DBA 的同學跟我反饋說，他負責的某個業務的庫記憶體命中率突然從 99% 降低到了 75%，整個系統處於阻塞狀態，更新語句全部堵住。而探究其原因後，我發現這個業務有大量插入資料的操作，而他在前一天把其中的某個普通索引改成了唯一索引。

4.6)普通索引的所有場景，使用 change buffer 都可以起到加速作用嗎？

  • 因為change buffer在merge之前把我們的更新語句快取下來了，那麼在merge之前，快取的更新語句越多越好。

  • 對於寫多讀少的業務來說，頁面在寫完以後馬上被訪問到的概率比較小，此時 change buffer 的使用效果最好。這種業務模型常見的就是賬單類、日誌類的系統。

  • 假設一個業務的更新模式是寫入之後馬上會做查詢，那麼即使滿足了條件，將更新先記錄在 change buffer，但之後由於馬上要訪問這個資料頁，會立即觸發 merge 過程。這樣隨機訪問 IO 的次數不會減少，反而增加了 change buffer 的維護代價。所以，對於這種業務模式來說，change buffer 反而起到了副作用。

5）經過上面的學習，普通索引和唯一索引應該怎麼選擇？

  • 它兩查詢沒啥差別，但是更新的話還是普通索引更勝一籌，所以我們儘量選擇普通索引。

6）如果所有的更新後面，都馬上伴隨著對這個記錄的查詢，那我們應該做哪些操作？

  • 關閉 change buffer

7）前面我們說過redo log，那change buffer 和 redo log的區別是什麼？

  • redo log 節省的是我們語句從記憶體寫入磁碟中的IO消耗

  • change buffer 節省的是我們資料從磁碟讀到記憶體的消耗

 

分析過程：

我們往資料庫插入兩條記錄

  insert into t(id,k) values(id1,k1),(id2,k2);

假如k1 所在的資料頁在記憶體 (InnoDB buffer pool) 中

k2 所在的資料頁不在記憶體中

 

 

這條更新語句做了如下的操作（按照圖中的數字順序）：

  1. Page 1 在記憶體中，直接更新記憶體；

  2. Page 2 沒有在記憶體中，就在記憶體的 change buffer 區域，記錄下“我要往 Page 2 插入一行”這個資訊

  3. 將上述兩個動作記入 redo log 中（圖中 3 和 4）。

8）通過上圖 你可以看到，change buffer 一開始是寫記憶體的，那麼如果這個時候機器掉電重啟，會不會導致 change buffer 丟失呢？change buffer 丟失可不是小事兒，再從磁碟讀入資料可就沒有了 merge 過程，就等於是資料丟失了。會不會出現這種情況呢？

  • 不會導致change buffer丟失。因為在更改change buffer 時也會寫redo log，也需要持久化

相關文章