從庫資料查詢和引數slave_rows_search_algorithms

---

注意：本文是《深入理解MySQL主從原理 32節》的第24節

本節包含一個筆記如下：
https://www.jianshu.com/p/5183fe0f00d8

---

我們前面已經知道了對於DML語句來講其資料的更改將被放到對應的Event中。比如‘Delete’語句會將所有刪除資料的before_image放到DELETE_ROWS_EVENT中，從庫只要讀取這些before_image進行資料查詢，然後呼叫相應的‘Delete’的操作就可以完成資料的刪除了。下面我們來討論一下從庫是如何進行資料查詢的。

本節我們假定引數binlog_row_image設定為‘FULL’也就是預設值，關於binlog_row_image引數的影響在第11節已經描述過了。

更多主從同步相關可以參考我的《深入理解MySQL主從原理 32節》專欄：

一、從一個列子出發

在開始之前我們先假定引數‘slave_rows_search_algorithms’為預設值，即：

• TABLE_SCAN,INDEX_SCAN

因為這個引數會直接影響到對索引的利用方式。

我們還是以‘Delete’操作為例，實際上對於索引的選擇‘Update’操作也是一樣的，因為都是通過before_image去查詢資料。我測試的表結構、資料和操作如下：

mysql> show create table tkkk \G
*************************** 1. row ***************************
       Table: tkkk
Create Table: CREATE TABLE `tkkk` (
  `a` int(11) DEFAULT NULL,
  `b` int(11) DEFAULT NULL,
  `c` int(11) DEFAULT NULL,
  KEY `a` (`a`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)
mysql> select * from tkkk;
+------+------+------+
| a    | b    | c    |
+------+------+------+
|    1 |    1 |    1 |
|    2 |    2 |    2 |
|    3 |    3 |    3 |
|    4 |    4 |    4 |
|    5 |    5 |    5 |
|    6 |    6 |    6 |
|    7 |    7 |    7 |
|    8 |    8 |    8 |
|    9 |    9 |    9 |
|   10 |   10 |   10 |
|   11 |   11 |   11 |
|   12 |   12 |   12 |
|   13 |   13 |   13 |
|   15 |   15 |   15 |
|   15 |   16 |   16 |
|   15 |   17 |   17 |
+------+------+------+
16 rows in set (2.21 sec)
mysql> delete from tkkk where a=15;
Query OK, 3 rows affected (6.24 sec)
因為我做了debug索引這裡時間看起來很長

對於這樣一個‘Delete’語句來講主庫會利用到索引 KEY a ，刪除的三條資料我們實際上只需要一次索引的定位（參考btr_cur_search_to_nth_level函式），然後順序掃描接下來的資料進行刪除就可以了。大概的流程如下圖：

這條資料刪除的三條資料的before_image將會記錄到一個DELETE_ROWS_EVENT中。從庫應用的時候會重新評估應該使用哪個索引， 優先使用主鍵和唯一鍵 。對於Event中的每條資料都需要進行索引定位操作，並且對於非唯一索引來講第一次返回的第一行資料可能並不是刪除的資料，還需要需要繼續掃描下一行，在函式Rows_log_event::do_index_scan_and_update中有如下程式碼：

while (record_compare(m_table, &m_cols))//比較每一個欄位 如果不相等 掃描下一行
  {
    while((error= next_record_scan(false)))//掃描下一行
    {
      /* We just skip records that has already been deleted */
      if (error == HA_ERR_RECORD_DELETED)
        continue;
      DBUG_PRINT("info",("no record matching the given row found"));
      goto end;
    }
  }

這些代價是比主庫更大的。在這個列子中沒有主鍵和唯一鍵，因此依舊使用的是索引KEY a ，大概流程如下圖：

但是如果我們在從庫增加一個主鍵，那麼在從庫進行應用的時候流程如下：

我們從上面的流程來看，主庫‘Delete’操作和從庫‘Delete’操作主要的區別在於：

• 從庫每條資料都需要索引定位查詢資料。
• 從庫在某些情況下通過非唯一索引查詢的資料第一條資料可能並不是刪除的資料，因此還需要繼續進行索引定位和查詢。

對於主庫來講一般只需要一次資料定位查詢即可，接下來訪問下一條資料就好了。其實對於真正的刪除操作來講並沒有太多的區別。如果合理的使用了主鍵和唯一鍵可以將上面提到的兩點影響降低。在造成從庫延遲的情況中，沒有合理的使用主鍵和唯一鍵是一個比較重要的原因。

最後如果表上一個索引都沒有的話，那麼情況變得更加嚴重，簡單的圖如下：

我們可以看到每一行資料的更改都需要進行全表掃描，這種問題就非常嚴重了。這種情況使用引數‘slave_rows_search_algorithms’的HASH_SCAN選項也許可以提高效能，下面我們就來進行討論。

二、確認查詢資料的方式

前面的例子中我們接觸了引數‘slave_rows_search_algorithms’，這個引數主要用於確認如何查詢資料。其取值可以是下面幾個組合（來自官方文件），原始碼中體現為一個點陣圖：

• TABLE_SCAN,INDEX_SCAN（預設值）
• INDEX_SCAN,HASH_SCAN
• TABLE_SCAN,HASH_SCAN
• TABLE_SCAN,INDEX_SCAN,HASH_SCAN

在原始碼中有如下的說明，當然官方文件也有類似的說明：

  /*
    Decision table:
    - I  --> Index scan / search
    - T  --> Table scan
    - Hi --> Hash over index
    - Ht --> Hash over the entire table
    |--------------+-----------+------+------+------|
    | Index\Option | I , T , H | I, T | I, H | T, H |
    |--------------+-----------+------+------+------|
    | PK / UK      | I         | I    | I    | Hi   |
    | K            | Hi        | I    | Hi   | Hi   |
    | No Index     | Ht        | T    | Ht   | Ht   |
    |--------------+-----------+------+------+------|
  */

實際上原始碼中會有三種資料查詢的方式，分別是：

• ROW_LOOKUP_INDEX_SCAN

對應函式介面：Rows_log_event::do_index_scan_and_update

• ROW_LOOKUP_HASH_SCAN

對應函式介面：Rows_log_event::do_hash_scan_and_update
它又包含：
（1） Hi —> Hash over index
（2） Ht —> Hash over the entire table
後面討論

• ROW_LOOKUP_TABLE_SCAN

對應函式介面：Rows_log_event::do_table_scan_and_update

在原始碼中如下：

switch (m_rows_lookup_algorithm)//根據不同的演算法決定使用哪個方法
    {
      case ROW_LOOKUP_HASH_SCAN:
        do_apply_row_ptr= &Rows_log_event::do_hash_scan_and_update;
        break;
      case ROW_LOOKUP_INDEX_SCAN:
        do_apply_row_ptr= &Rows_log_event::do_index_scan_and_update;
        break;
      case ROW_LOOKUP_TABLE_SCAN:
        do_apply_row_ptr= &Rows_log_event::do_table_scan_and_update;
        break;

決定如何查詢資料以及通過哪個索引查詢正是通過引數‘slave_rows_search_algorithms’的設定和 表中是否有合適的索引 共同決定的，並不是完全由‘slave_rows_search_algorithms’引數決定。

下面這個圖就是決定的過程，可以參考函式decide_row_lookup_algorithm_and_key（圖24-1，高清原圖包含在文末原圖中）。

三、ROW_LOOKUP_HASH_SCAN方式的資料查詢

總的來講這種方式和ROW_LOOKUP_INDEX_SCAN和ROW_LOOKUP_TABLE_SCAN都不同，它是通過表中的資料和Event中的資料進行比對，而不是通過Event中的資料和表中的資料進行比對，下面我們將詳細描述這種方法。

假設我們將引數‘slave_rows_search_algorithms’設定為INDEX_SCAN,HASH_SCAN，且表上沒有主鍵和唯一鍵的話，那麼上圖的流程將會把資料查詢的方式設定為ROW_LOOKUP_HASH_SCAN。

在ROW_LOOKUP_HASH_SCAN又包含兩種資料查詢的方式：

• Hi —> Hash over index
• Ht —> Hash over the entire table

對於ROW_LOOKUP_HASH_SCAN來講，其首先會將Event中的每一行資料讀取出來存入到HASH結構中，如果能夠使用到Hi那麼還會額外維護一個集合（set），將索引鍵值存入集合，作為索引掃描的依據。如果沒有索引這個集合（set）將不會維護直接使用全表掃描，即Ht。
Ht —> Hash over the entire table會全表掃描，其中每行都會查詢hash結構來比對資料。Hi —> Hash over index則會通過前面我們說的集合（set）來進行索引定位掃描，每行資料也會去查詢hash結構來比對資料。

需要注意一點這個過程的單位是Event，我們前面說過一個DELETE_ROWS_EVENT可能包含了多行資料，Event最大為8K左右。 因此使用Ht —> Hash over the entire table的方式，將會從原來的每行資料進行一次全表掃描變為每個Event才進行一次全表掃描 。

但是對於Hi —> Hash over index來講效果就沒有那麼明顯了，因為如果刪除的資料重複值很少的情況下，依然需要足夠多的索引定位查詢才行，但是如果刪除的資料重複值較多那麼構造的集合（set）元素將會大大減少，也就減少了索引查詢定位的開銷。

考慮另外一種情況，如果我的每條delete語句一次只刪除一行資料而不是delete一條語句刪除大量的資料，那這種情況每個DELETE_ROWS_EVENT只有一條資料存在，那麼使用ROW_LOOKUP_HASH_SCAN方式並 不會提高效能 ，因為這條資料還是需要進行一次全表掃描或者索引定位才能查詢到資料，和預設的方式沒什麼區別。

整個過程參考如下介面：

• Rows_log_event::do_hash_scan_and_update：總介面，呼叫下面兩個介面。
• Rows_log_event::do_hash_row：將資料加入到hash結構，如果有索引還需要維護集合（set）。
• Rows_log_event::do_scan_and_update：查詢並且進行刪除操作，會呼叫Rows_log_event::next_record_scan進行資料查詢。
• Rows_log_event::next_record_scan：具體的查詢方式實現了Hi —> Hash over index和Ht —> Hash over the entire table的查詢方式

下面我們還是用最開始的列子，我們刪除了三條資料，因此DELETE_ROW_EVENT中包含了三條資料。假設我們引數‘slave_rows_search_algorithms’設定為INDEX_SCAN,HASH_SCAN。因為我的表中沒有主鍵和唯一鍵，因此會最終使用ROW_LOOKUP_HASH_SCAN進行資料查詢。但是因為我們有一個索引key a，因此會使用到Hi —> Hash over index。為了更好的描述Hi和Ht兩種方式，我們也假定另一種情況是表上一個索引都沒有，我將兩種方式放到一個圖中方便大家發現不同點，如下圖（圖24-2，高清原圖包含在文末原圖中）：

四、總結

我記得以前有位朋友問我主庫沒有主鍵如果我在從庫建立一個主鍵能降低延遲嗎？這裡我們就清楚了答案是肯定的，因為從庫會根據Event中的行資料進行使用索引的選擇。那麼總結一下：

• slave_rows_search_algorithms引數設定了HASH_SCAN並不一定會提高效能，只有滿足如下兩個條件才會提高效能：
  （1）（表中沒有任何索引）或者（有索引且本條update/delete的資料關鍵字重複值較多）。
  （2） 一個update/delete語句刪除了大量的資料，形成了很多個8K左右的UPDATE_ROW_EVENT/DELETE_ROW_EVENT。update/delete語句只修改少量的資料（比如每個語句修改一行資料）並不能提高效能。

• 從庫索引的利用是自行判斷的，順序為主鍵->唯一鍵->普通索引。

• 如果slave_rows_search_algorithms引數沒有設定HASH_SCAN，並且沒有主鍵/唯一鍵那麼效能將會急劇下降造成延遲。如果連索引都沒有那麼這個情況更加嚴重，因為更改的每一行資料都會引發一次全表掃描。

因此我們發現在MySQL中強制設定主鍵又多了一個理由。

---

第24節結束