第24節(jié) 從庫(kù)數(shù)據(jù)查找和參數(shù)slave_rows_search_algorithms

從庫(kù)數(shù)據(jù)查找和參數(shù)slave_rows_search_algorithms

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

本節(jié)包含一個(gè)筆記如下：
https://www.jianshu.com/p/5183fe0f00d8

我們前面已經(jīng)知道了對(duì)于DML語(yǔ)句來(lái)講其數(shù)據(jù)的更改將被放到對(duì)應(yīng)的Event中。比如‘Delete’語(yǔ)句會(huì)將所有刪除數(shù)據(jù)的before_image放到DELETE_ROWS_EVENT中，從庫(kù)只要讀取這些before_image進(jìn)行數(shù)據(jù)查找，然后調(diào)用相應(yīng)的‘Delete’的操作就可以完成數(shù)據(jù)的刪除了。下面我們來(lái)討論一下從庫(kù)是如何進(jìn)行數(shù)據(jù)查找的。

本節(jié)我們假定參數(shù)binlog_row_image設(shè)置為‘FULL’也就是默認(rèn)值，關(guān)于binlog_row_image參數(shù)的影響在第11節(jié)已經(jīng)描述過(guò)了。

更多主從同步相關(guān)可以參考我的《深入理解MySQL主從原理 32節(jié)》專欄：

如果圖片不能顯示可查看下面鏈接：
https://www.jianshu.com/p/d636215d767f

一、從一個(gè)列子出發(fā)

在開始之前我們先假定參數(shù)‘slave_rows_search_algorithms’為默認(rèn)值，即：

• TABLE_SCAN,INDEX_SCAN

因?yàn)檫@個(gè)參數(shù)會(huì)直接影響到對(duì)索引的利用方式。

我們還是以‘Delete’操作為例，實(shí)際上對(duì)于索引的選擇‘Update’操作也是一樣的，因?yàn)槎际峭ㄟ^(guò)before_image去查找數(shù)據(jù)。我測(cè)試的表結(jié)構(gòu)、數(shù)據(jù)和操作如下：

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)
因?yàn)槲易隽薲ebug索引這里時(shí)間看起來(lái)很長(zhǎng)

對(duì)于這樣一個(gè)‘Delete’語(yǔ)句來(lái)講主庫(kù)會(huì)利用到索引 KEY a，刪除的三條數(shù)據(jù)我們實(shí)際上只需要一次索引的定位（參考btr_cur_search_to_nth_level函數(shù)），然后順序掃描接下來(lái)的數(shù)據(jù)進(jìn)行刪除就可以了。大概的流程如下圖：

這條數(shù)據(jù)刪除的三條數(shù)據(jù)的before_image將會(huì)記錄到一個(gè)DELETE_ROWS_EVENT中。從庫(kù)應(yīng)用的時(shí)候會(huì)重新評(píng)估應(yīng)該使用哪個(gè)索引， 優(yōu)先使用主鍵和唯一鍵。對(duì)于Event中的每條數(shù)據(jù)都需要進(jìn)行索引定位操作，并且對(duì)于非唯一索引來(lái)講第一次返回的第一行數(shù)據(jù)可能并不是刪除的數(shù)據(jù)，還需要需要繼續(xù)掃描下一行，在函數(shù)Rows_log_event::do_index_scan_and_update中有如下代碼：

while (record_compare(m_table, &m_cols))//比較每一個(gè)字段 如果不相等 掃描下一行
  {
    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;
    }
  }

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

但是如果我們?cè)趶膸?kù)增加一個(gè)主鍵，那么在從庫(kù)進(jìn)行應(yīng)用的時(shí)候流程如下：

我們從上面的流程來(lái)看，主庫(kù)‘Delete’操作和從庫(kù)‘Delete’操作主要的區(qū)別在于：

• 從庫(kù)每條數(shù)據(jù)都需要索引定位查找數(shù)據(jù)。
• 從庫(kù)在某些情況下通過(guò)非唯一索引查找的數(shù)據(jù)第一條數(shù)據(jù)可能并不是刪除的數(shù)據(jù)，因此還需要繼續(xù)進(jìn)行索引定位和查找。

對(duì)于主庫(kù)來(lái)講一般只需要一次數(shù)據(jù)定位查找即可，接下來(lái)訪問(wèn)下一條數(shù)據(jù)就好了。其實(shí)對(duì)于真正的刪除操作來(lái)講并沒有太多的區(qū)別。如果合理的使用了主鍵和唯一鍵可以將上面提到的兩點(diǎn)影響降低。在造成從庫(kù)延遲的情況中，沒有合理的使用主鍵和唯一鍵是一個(gè)比較重要的原因。

最后如果表上一個(gè)索引都沒有的話，那么情況變得更加嚴(yán)重，簡(jiǎn)單的圖如下：

我們可以看到每一行數(shù)據(jù)的更改都需要進(jìn)行全表掃描，這種問(wèn)題就非常嚴(yán)重了。這種情況使用參數(shù)‘slave_rows_search_algorithms’的HASH_SCAN選項(xiàng)也許可以提高性能，下面我們就來(lái)進(jìn)行討論。

二、確認(rèn)查找數(shù)據(jù)的方式

前面的例子中我們接觸了參數(shù)‘slave_rows_search_algorithms’，這個(gè)參數(shù)主要用于確認(rèn)如何查找數(shù)據(jù)。其取值可以是下面幾個(gè)組合（來(lái)自官方文檔），源碼中體現(xiàn)為一個(gè)位圖：

• TABLE_SCAN,INDEX_SCAN（默認(rèn)值）
• INDEX_SCAN,HASH_SCAN
• TABLE_SCAN,HASH_SCAN
• TABLE_SCAN,INDEX_SCAN,HASH_SCAN

在源碼中有如下的說(shuō)明，當(dāng)然官方文檔也有類似的說(shuō)明：

  /*
    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   |
    |--------------+-----------+------+------+------|
  */

實(shí)際上源碼中會(huì)有三種數(shù)據(jù)查找的方式，分別是：

• ROW_LOOKUP_INDEX_SCAN

對(duì)應(yīng)函數(shù)接口：Rows_log_event::do_index_scan_and_update

• ROW_LOOKUP_HASH_SCAN

對(duì)應(yīng)函數(shù)接口：Rows_log_event::do_hash_scan_and_update
它又包含：
（1） Hi —> Hash over index
（2） Ht —> Hash over the entire table
后面討論

• ROW_LOOKUP_TABLE_SCAN

對(duì)應(yīng)函數(shù)接口：Rows_log_event::do_table_scan_and_update

在源碼中如下：

switch (m_rows_lookup_algorithm)//根據(jù)不同的算法決定使用哪個(gè)方法
    {
      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;

決定如何查找數(shù)據(jù)以及通過(guò)哪個(gè)索引查找正是通過(guò)參數(shù)‘slave_rows_search_algorithms’的設(shè)置和 表中是否有合適的索引共同決定的，并不是完全由‘slave_rows_search_algorithms’參數(shù)決定。

下面這個(gè)圖就是決定的過(guò)程，可以參考函數(shù)decide_row_lookup_algorithm_and_key（圖24-1，高清原圖包含在文末原圖中）。

三、ROW_LOOKUP_HASH_SCAN方式的數(shù)據(jù)查找

總的來(lái)講這種方式和ROW_LOOKUP_INDEX_SCAN和ROW_LOOKUP_TABLE_SCAN都不同，它是通過(guò)表中的數(shù)據(jù)和Event中的數(shù)據(jù)進(jìn)行比對(duì)，而不是通過(guò)Event中的數(shù)據(jù)和表中的數(shù)據(jù)進(jìn)行比對(duì)，下面我們將詳細(xì)描述這種方法。

假設(shè)我們將參數(shù)‘slave_rows_search_algorithms’設(shè)置為INDEX_SCAN,HASH_SCAN，且表上沒有主鍵和唯一鍵的話，那么上圖的流程將會(huì)把數(shù)據(jù)查找的方式設(shè)置為ROW_LOOKUP_HASH_SCAN。

在ROW_LOOKUP_HASH_SCAN又包含兩種數(shù)據(jù)查找的方式：

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

對(duì)于ROW_LOOKUP_HASH_SCAN來(lái)講，其首先會(huì)將Event中的每一行數(shù)據(jù)讀取出來(lái)存入到HASH結(jié)構(gòu)中，如果能夠使用到Hi那么還會(huì)額外維護(hù)一個(gè)集合（set），將索引鍵值存入集合，作為索引掃描的依據(jù)。如果沒有索引這個(gè)集合（set）將不會(huì)維護(hù)直接使用全表掃描，即Ht。
Ht —> Hash over the entire table會(huì)全表掃描，其中每行都會(huì)查詢hash結(jié)構(gòu)來(lái)比對(duì)數(shù)據(jù)。Hi —> Hash over index則會(huì)通過(guò)前面我們說(shuō)的集合（set）來(lái)進(jìn)行索引定位掃描，每行數(shù)據(jù)也會(huì)去查詢hash結(jié)構(gòu)來(lái)比對(duì)數(shù)據(jù)。

需要注意一點(diǎn)這個(gè)過(guò)程的單位是Event，我們前面說(shuō)過(guò)一個(gè)DELETE_ROWS_EVENT可能包含了多行數(shù)據(jù)，Event最大為8K左右。 因此使用Ht —> Hash over the entire table的方式，將會(huì)從原來(lái)的每行數(shù)據(jù)進(jìn)行一次全表掃描變?yōu)槊總€(gè)Event才進(jìn)行一次全表掃描。

但是對(duì)于Hi —> Hash over index來(lái)講效果就沒有那么明顯了，因?yàn)槿绻麆h除的數(shù)據(jù)重復(fù)值很少的情況下，依然需要足夠多的索引定位查找才行，但是如果刪除的數(shù)據(jù)重復(fù)值較多那么構(gòu)造的集合（set）元素將會(huì)大大減少，也就減少了索引查找定位的開銷。

考慮另外一種情況，如果我的每條delete語(yǔ)句一次只刪除一行數(shù)據(jù)而不是delete一條語(yǔ)句刪除大量的數(shù)據(jù)，那這種情況每個(gè)DELETE_ROWS_EVENT只有一條數(shù)據(jù)存在，那么使用ROW_LOOKUP_HASH_SCAN方式并 不會(huì)提高性能，因?yàn)檫@條數(shù)據(jù)還是需要進(jìn)行一次全表掃描或者索引定位才能查找到數(shù)據(jù)，和默認(rèn)的方式?jīng)]什么區(qū)別。

整個(gè)過(guò)程參考如下接口：

• Rows_log_event::do_hash_scan_and_update：總接口，調(diào)用下面兩個(gè)接口。
• Rows_log_event::do_hash_row：將數(shù)據(jù)加入到hash結(jié)構(gòu)，如果有索引還需要維護(hù)集合（set）。
• Rows_log_event::do_scan_and_update：查找并且進(jìn)行刪除操作，會(huì)調(diào)用Rows_log_event::next_record_scan進(jìn)行數(shù)據(jù)查找。
• Rows_log_event::next_record_scan：具體的查找方式實(shí)現(xiàn)了Hi —> Hash over index和Ht —> Hash over the entire table的查找方式

下面我們還是用最開始的列子，我們刪除了三條數(shù)據(jù)，因此DELETE_ROW_EVENT中包含了三條數(shù)據(jù)。假設(shè)我們參數(shù)‘slave_rows_search_algorithms’設(shè)置為INDEX_SCAN,HASH_SCAN。因?yàn)槲业谋碇袥]有主鍵和唯一鍵，因此會(huì)最終使用ROW_LOOKUP_HASH_SCAN進(jìn)行數(shù)據(jù)查找。但是因?yàn)槲覀冇幸粋€(gè)索引key a，因此會(huì)使用到Hi —> Hash over index。為了更好的描述Hi和Ht兩種方式，我們也假定另一種情況是表上一個(gè)索引都沒有，我將兩種方式放到一個(gè)圖中方便大家發(fā)現(xiàn)不同點(diǎn)，如下圖（圖24-2，高清原圖包含在文末原圖中）：

四、總結(jié)

我記得以前有位朋友問(wèn)我主庫(kù)沒有主鍵如果我在從庫(kù)建立一個(gè)主鍵能降低延遲嗎？這里我們就清楚了答案是肯定的，因?yàn)閺膸?kù)會(huì)根據(jù)Event中的行數(shù)據(jù)進(jìn)行使用索引的選擇。那么總結(jié)一下：

• slave_rows_search_algorithms參數(shù)設(shè)置了HASH_SCAN并不一定會(huì)提高性能，只有滿足如下兩個(gè)條件才會(huì)提高性能：
  （1）（表中沒有任何索引）或者（有索引且本條update/delete的數(shù)據(jù)關(guān)鍵字重復(fù)值較多）。
  （2） 一個(gè)update/delete語(yǔ)句刪除了大量的數(shù)據(jù)，形成了很多個(gè)8K左右的UPDATE_ROW_EVENT/DELETE_ROW_EVENT。update/delete語(yǔ)句只修改少量的數(shù)據(jù)（比如每個(gè)語(yǔ)句修改一行數(shù)據(jù)）并不能提高性能。

• 從庫(kù)索引的利用是自行判斷的，順序?yàn)橹麈I->唯一鍵->普通索引。

• 如果slave_rows_search_algorithms參數(shù)沒有設(shè)置HASH_SCAN，并且沒有主鍵/唯一鍵那么性能將會(huì)急劇下降造成延遲。如果連索引都沒有那么這個(gè)情況更加嚴(yán)重，因?yàn)楦牡拿恳恍袛?shù)據(jù)都會(huì)引發(fā)一次全表掃描。

因此我們發(fā)現(xiàn)在MySQL中強(qiáng)制設(shè)置主鍵又多了一個(gè)理由。

第24節(jié)結(jié)束