從庫數(shù)據(jù)查詢和參數(shù)slave_rows_search_alg

2024-09-07 22:12:54

字體：大中小

供稿：網(wǎng)友

　　本節(jié)我們假定參數(shù)binlog_row_image設(shè)置為‘FULL’也就是默認(rèn)值。

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

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

　　TABLE_SCAN,INDEX_SCAN

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

　　我們還是以‘Delete’操作為例，實際上對于索引的選擇‘Update’操作也是一樣的，因為都是通過before_image去查找數(shù)據(jù)。我測試的表結(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)

　　因為我做了debug索引這里時間看起來很長

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

　　這條數(shù)據(jù)刪除的三條數(shù)據(jù)的before_image將會記錄到一個DELETE_ROWS_EVENT中。從庫應(yīng)用的時候會重新評估應(yīng)該使用哪個索引，優(yōu)先使用主鍵和唯一鍵。對于Event中的每條數(shù)據(jù)都需要進(jìn)行索引定位操作，并且對于非唯一索引來講第一次返回的第一行數(shù)據(jù)可能并不是刪除的數(shù)據(jù)，還需要需要繼續(xù)掃描下一行，在函數(shù)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，

　　從庫每條數(shù)據(jù)都需要索引定位查找數(shù)據(jù)。

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

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

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

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

　　前面的例子中我們接觸了參數(shù)‘slave_rows_search_algorithms’，這個參數(shù)主要用于確認(rèn)如何查找數(shù)據(jù)。其取值可以是下面幾個組合(來自官方文檔)，源碼中體現(xiàn)為一個位圖：無錫婦科醫(yī)院排行 http://www.0510bhyy.com/

　　TABLE_SCAN,INDEX_SCAN(默認(rèn)值)

　　INDEX_SCAN,HASH_SCAN

　　TABLE_SCAN,HASH_SCAN

　　TABLE_SCAN,INDEX_SCAN,HASH_SCAN

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

　　/*

　　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ù)據(jù)查找的方式，分別是：

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

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

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

　　在源碼中如下：

　　switch (m_rows_lookup_algorithm)//根據(jù)不同的算法決定使用哪個方法

　　{

　　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ù)以及通過哪個索引查找正是通過參數(shù)‘slave_rows_search_algorithms’的設(shè)置和表中是否有合適的索引共同決定的，并不是完全由‘slave_rows_search_algorithms’參數(shù)決定。

　　下面這個圖就是決定的過程，可以參考函數(shù)decide_row_lookup_algorithm_and_key(如下圖)。

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

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

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

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

　　Hi --> Hash over index

　　Ht --> Hash over the entire table

　　對于ROW_LOOKUP_HASH_SCAN來講，其首先會將Event中的每一行數(shù)據(jù)讀取出來存入到HASH結(jié)構(gòu)中，如果能夠使用到Hi那么還會額外維護(hù)一個集合(set)，將索引鍵值存入集合，作為索引掃描的依據(jù)。如果沒有索引這個集合(set)將不會維護(hù)直接使用全表掃描，即Ht。

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

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

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

　　整個過程參考如下接口：

　　Rows_log_event::do_hash_scan_and_update：總接口，調(diào)用下面兩個接口。

　　Rows_log_event::do_hash_row：將數(shù)據(jù)加入到hash結(jié)構(gòu)，如果有索引還需要維護(hù)集合(set)。

　　Rows_log_event::do_scan_and_update：查找并且進(jìn)行刪除操作，會調(diào)用Rows_log_event::next_record_scan進(jìn)行數(shù)據(jù)查找。

　　Rows_log_event::next_record_scan：具體的查找方式實現(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。因為我的表中沒有主鍵和唯一鍵，因此會最終使用ROW_LOOKUP_HASH_SCAN進(jìn)行數(shù)據(jù)查找。但是因為我們有一個索引key a，因此會使用到Hi --> Hash over index。為了更好的描述Hi和Ht兩種方式，我們也假定另一種情況是表上一個索引都沒有，我將兩種方式放到一個圖中方便大家發(fā)現(xiàn)不同點，如下圖：

　　四、總結(jié)

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

　　slave_rows_search_algorithms參數(shù)設(shè)置了HASH_SCAN并不一定會提高性能，只有滿足如下兩個條件才會提高性能：

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

　　如果slave_rows_search_algorithms參數(shù)沒有設(shè)置HASH_SCAN，并且沒有主鍵/唯一鍵那么性能將會急劇下降造成延遲。如果連索引都沒有那么這個情況更加嚴(yán)重，因為更改的每一行數(shù)據(jù)都會引發(fā)一次全表掃描。

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

（編輯：武林網(wǎng)）

上一篇：大表count 致使宕機(jī)

下一篇：InnoDB 層全文索引字典表全面認(rèn)識 information_schema