前言

在之前我們已經(jīng)講解了一主一從，雙主雙從的MySQL集群搭建，在單機應(yīng)用的時候看起來沒有問題，但是在企業(yè)的生產(chǎn)環(huán)境中，在很多情況下都會有復(fù)制延遲的問題。所以我來了！！！

? 主從復(fù)制的原理我們在此處就不再贅述了，之前已經(jīng)講過了，這是一個老生常談的問題，原理性質(zhì)的也幾乎在面試中問爛了，這些原理性質(zhì)的東西并不是很難，但是你需要注意了，主從復(fù)制的延遲問題會成為一個難點，能非常全面的考驗同學(xué)們的技術(shù)實力。

1、如何查看同步延遲狀態(tài)？

? 在從服務(wù)器上通過 show slave status 查看具體的參數(shù)，有幾個參數(shù)比較重要：

? master_log_file: slave中的IO線程正在讀取的主服務(wù)器二進制日志文件的名稱

? read_master_log_pos: 在當(dāng)前的主服務(wù)器二進制日志中，slave中的IO線程已經(jīng)讀取的位置

? relay_log_file: sql線程當(dāng)前正在讀取和執(zhí)行的中繼日志文件的名稱

? relay_log_pos: 在當(dāng)前的中繼日志中，sql線程已經(jīng)讀取和執(zhí)行的位置

? relay_master_log_file: 由sql線程執(zhí)行的包含多數(shù)近期事件的主服務(wù)器二進制日志文件的名稱

? slave_io_running: IO線程是否被啟動并成功地連接到主服務(wù)器上

? slave_sql_running: sql線程是否被啟動

? seconds_behind_master: 從屬服務(wù)器sql線程和從屬服務(wù)器IO線程之間的事件差距，單位以秒計

? 在觀察同步延遲的時候，上述的幾個參數(shù)都是比較重要的，其中有一個最最重要的參數(shù)需要同學(xué)們引起注意，那就是seconds_behind_master，這個參數(shù)就表示當(dāng)前備庫延遲了多長時間，那么這個值是如何計算的呢？

? 在進行主從復(fù)制的時候，需要注意以下幾個關(guān)鍵的時刻：

? 1、主庫A執(zhí)行完成一個事務(wù)，寫入binlog，我們把這個時刻記為T1;

? 2、之后傳給備庫B,我們把備庫B接受完這個binlog的時刻記為T2;

? 3、備庫B執(zhí)行完成這個事務(wù)，我們把這個時刻記為T3;

? 所謂的主備延遲就是同一個事務(wù)，在備庫執(zhí)行完成的時間和主庫執(zhí)行完成的時間之間的差值，也就是T3-T1。SBM在進行計算的時候也是按照這樣的方式，每個事務(wù)的binlog中都有一個時間字段，用于記錄主庫寫入的時間，備庫取出當(dāng)前正在執(zhí)行的事務(wù)的時間字段的值，計算它與當(dāng)前系統(tǒng)時間的差值，得到SBM。

? 如果剛剛的流程聽明白了，那么下面我們就要開始分析產(chǎn)生這個時間差值的原因有哪些了，以方便我們更好的解決生產(chǎn)環(huán)境中存在的問題。

2、主從復(fù)制延遲產(chǎn)生的原因有哪些？

? 1、在某些部署環(huán)境中，備庫所在的機器性能要比主庫所在的機器性能差。此時如果機器的資源不足的話就會影響備庫同步的效率；

? 2、備庫充當(dāng)了讀庫，一般情況下主要寫的壓力在于主庫，那么備庫會提供一部分讀的壓力，而如果備庫的查詢壓力過大的話，備庫的查詢消耗了大量的CPU資源，那么必不可少的就會影響同步的速度

? 3、大事務(wù)執(zhí)行，如果主庫的一個事務(wù)執(zhí)行了10分鐘，而binlog的寫入必須要等待事務(wù)完成之后，才會傳入備庫，那么此時在開始執(zhí)行的時候就已經(jīng)延遲了10分鐘了

? 4、主庫的寫操作是順序?qū)慴inlog，從庫單線程去主庫順序讀binlog，從庫取到binlog之后在本地執(zhí)行。mysql的主從復(fù)制都是單線程的操作，但是由于主庫是順序?qū)懀孕屎芨撸鴱膸煲彩琼樞蜃x取主庫的日志，此時的效率也是比較高的，但是當(dāng)數(shù)據(jù)拉取回來之后變成了隨機的操作，而不是順序的，所以此時成本會提高。

? 5、 從庫在同步數(shù)據(jù)的同時，可能跟其他查詢的線程發(fā)生鎖搶占的情況，此時也會發(fā)生延時。

? 6、 當(dāng)主庫的TPS并發(fā)非常高的時候，產(chǎn)生的DDL數(shù)量超過了一個線程所能承受的范圍的時候，那么也可能帶來延遲

? 7、 在進行binlog日志傳輸?shù)臅r候，如果網(wǎng)絡(luò)帶寬也不是很好，那么網(wǎng)絡(luò)延遲也可能造成數(shù)據(jù)同步延遲

? 這些就是可能會造成備庫延遲的原因

3、如何解決復(fù)制延遲的問題

? 先說一些虛的東西，什么叫虛的東西呢？就是一聽上去感覺很有道理，但是在實施或者實際的業(yè)務(wù)場景中可能難度很大或者很難實現(xiàn)，下面我們從幾個方面來進行描述：

1、架構(gòu)方面

? 1、業(yè)務(wù)的持久化層的實現(xiàn)采用分庫架構(gòu)，讓不同的業(yè)務(wù)請求分散到不同的數(shù)據(jù)庫服務(wù)上，分散單臺機器的壓力

? 2、服務(wù)的基礎(chǔ)架構(gòu)在業(yè)務(wù)和mysql之間加入緩存層，減少mysql的讀的壓力，但是需要注意的是，如果數(shù)據(jù)經(jīng)常要發(fā)生修改，那么這種設(shè)計是不合理的，因為需要頻繁地去更新緩存中的數(shù)據(jù)，保持數(shù)據(jù)的一致性，導(dǎo)致緩存的命中率很低，所以此時就要慎用緩存了

? 3、使用更好的硬件設(shè)備，比如cpu，ssd等，但是這種方案一般對于公司而言不太能接受，原因也很簡單，會增加公司的成本，而一般公司其實都很摳門，所以意義也不大，但是你要知道這也是解決問題的一個方法，只不過你需要評估的是投入產(chǎn)出比而已。

2、從庫配置方面

? 1、修改sync_binlog的參數(shù)的值

? 想要合理設(shè)置此參數(shù)的值必須要清楚地知道binlog的寫盤的流程：

?

 

? 可以看到，每個線程有自己的binlog cache，但是共用同一份binlog。

? 圖中的write，指的就是把日志寫入到文件系統(tǒng)的page cache，并沒有把數(shù)據(jù)持久化到磁盤，所以速度快

? 圖中的fsync，才是將數(shù)據(jù)持久化到磁盤的操作。一般情況下，我們認為fsync才占用磁盤的IOPS

? 而write和fsync的時機就是由參數(shù)sync_binlog來進行控制的。

? 1、當(dāng)sync_binlog=0的時候，表示每次提交事務(wù)都只write，不fsync

? 2、當(dāng)sync_binlog=1的時候，表示每次提交事務(wù)都執(zhí)行fsync

? 3、當(dāng)sync_binlog=N的時候，表示每次提交事務(wù)都write，但積累N個事務(wù)后才fsync。

? 一般在公司的大部分應(yīng)用場景中，我們建議將此參數(shù)的值設(shè)置為1，因為這樣的話能夠保證數(shù)據(jù)的安全性，但是如果出現(xiàn)主從復(fù)制的延遲問題，可以考慮將此值設(shè)置為100~1000中的某個數(shù)值，非常不建議設(shè)置為0，因為設(shè)置為0的時候沒有辦法控制丟失日志的數(shù)據(jù)量，但是如果是對安全性要求比較高的業(yè)務(wù)系統(tǒng)，這個參數(shù)產(chǎn)生的意義就不是那么大了。

? 2、直接禁用salve上的binlog，當(dāng)從庫的數(shù)據(jù)在做同步的時候，有可能從庫的binlog也會進行記錄，此時的話肯定也會消耗io的資源，因此可以考慮將其關(guān)閉，但是需要注意，如果你搭建的集群是級聯(lián)的模式的話，那么此時的binlog也會發(fā)送到另外一臺從庫里方便進行數(shù)據(jù)同步，此時的話，這個配置項也不會起到太大的作用。

? 3、設(shè)置
innodb_flush_log_at_trx_commit 屬性，這個屬性在我講日志的時候講過，用來表示每一次的事務(wù)提交是否需要把日志都寫入磁盤，這是很浪費時間的，一共有三個屬性值，分別是0（每次寫到服務(wù)緩存，一秒鐘刷寫一次），1（每次事務(wù)提交都刷寫一次磁盤），2（每次寫到os緩存，一秒鐘刷寫一次），一般情況下我們推薦設(shè)置成2，這樣就算mysql的服務(wù)宕機了，卸載os緩存中的數(shù)據(jù)也會進行持久化。

4、從根本上解決主從復(fù)制的延遲問題

? 很多同學(xué)在自己線上的業(yè)務(wù)系統(tǒng)中都使用了mysql的主從復(fù)制，但是大家需要注意的是，并不是所有的場景都適合主從復(fù)制，一般情況下是讀要遠遠多于寫的應(yīng)用，同時讀的時效性要求不那么高的場景。如果真實場景中真的要求立馬讀取到更新之后的數(shù)據(jù)，那么就只能強制讀取主庫的數(shù)據(jù)，所以在進行實現(xiàn)的時候要考慮實際的應(yīng)用場景，不要為了技術(shù)而技術(shù)，這是很嚴重的事情。

? 在mysql5.6版本之后引入了一個概念，就是我們通常說的并行復(fù)制，如下圖：

 

? 通過上圖我們可以發(fā)現(xiàn)其實所謂的并行復(fù)制，就是在中間添加了一個分發(fā)的環(huán)節(jié)，也就是說原來的sql_thread變成了現(xiàn)在的coordinator組件，當(dāng)日志來了之后，coordinator負責(zé)讀取日志信息以及分發(fā)事務(wù)，真正的日志執(zhí)行的過程是放在了worker線程上，由多個線程并行的去執(zhí)行。

-- 查看并行的slave的線程的個數(shù)，默認是0.表示單線程
show global variables like 'slave_parallel_workers';
-- 根據(jù)實際情況保證開啟多少線程
set global slave_parallel_workers = 4;
-- 設(shè)置并發(fā)復(fù)制的方式，默認是一個線程處理一個庫，值為database
show global variables like '%slave_parallel_type%';
-- 停止slave
stop slave;
-- 設(shè)置屬性值
set global slave_parallel_type='logical_check';
-- 開啟slave
start slave
-- 查看線程數(shù)
show full processlist;

? 通過上述的配置可以完成我們說的并行復(fù)制，但是此時你需要思考幾個問題

? 1、在并行操作的時候，可能會有并發(fā)的事務(wù)問題，我們的備庫在執(zhí)行的時候可以按照輪訓(xùn)的方式發(fā)送給各個worker嗎？

? 答案是不行的，因為事務(wù)被分發(fā)給worker以后，不同的worker就開始獨立執(zhí)行了，但是，由于CPU的不同調(diào)度策略，很可能第二個事務(wù)最終比第一個事務(wù)先執(zhí)行，而如果剛剛好他們修改的是同一行數(shù)據(jù)，那么因為執(zhí)行順序的問題，可能導(dǎo)致主備的數(shù)據(jù)不一致。

? 2、同一個事務(wù)的多個更新語句，能不能分給不同的worker來執(zhí)行呢？

? 答案是也不行，舉個例子，一個事務(wù)更新了表t1和表t2中的各一行，如果這兩條更新語句被分到不同worker的話，雖然最終的結(jié)果是主備一致的，但如果表t1執(zhí)行完成的瞬間，備庫上有一個查詢，就會看到這個事務(wù)更新了一半的結(jié)果，破壞了事務(wù)邏輯的隔離性。

? 我們通過講解上述兩個問題的最主要目的是為了說明一件事，就是coordinator在進行分發(fā)的時候，需要遵循的策略是什么？

? 1、不能造成更新覆蓋。這就要求更新同一行的兩個事務(wù)，必須被分發(fā)到同一個worker中。

? 2、同一個事務(wù)不能被拆開，必須放到同一個worker中。

? 聽完上面的描述，我們來說一下具體實現(xiàn)的原理和過程。

? 如果讓我們自己來設(shè)計的話，我們應(yīng)該如何操作呢？這是一個值得思考的問題。其實如果按照實際的操作的話，我們可以按照粒度進行分類，分為按庫分發(fā)，按表分發(fā)，按行分發(fā)。

? 其實不管按照什么方式進行分發(fā)，大家需要注意的就是在分發(fā)的時候必須要滿足我們上面說的兩條規(guī)則，所以當(dāng)我們進行分發(fā)的時候要在每一個worker上定義一個hash表，用來保存當(dāng)前這個work正在執(zhí)行的事務(wù)所涉及到的表。hash表的key值按照不同的粒度需要存儲不同的值：

? 按庫分發(fā)：key值是數(shù)據(jù)庫的名字，這個比較簡單

? 按表分發(fā)：key值是庫名+表名

? 按行分發(fā)：key值是庫名+表名+唯一鍵

1、MySQL5.6版本的并行復(fù)制策略

? 其實從mysql的5.6版本開始就已經(jīng)支持了并行復(fù)制，只是支持的粒度是按庫并行，這也是為什么現(xiàn)在的版本中可以選擇類型為database，其實說的就是支持按照庫進行并行復(fù)制。

? 但是其實用過的同學(xué)應(yīng)該都知道，這個策略的并行效果，取決于壓力模型。如果在主庫上有多個DB，并且各個DB的壓力均衡，使用這個策略的效果會很好，但是如果主庫的所有表都放在同一DB上，那么所有的操作都會分發(fā)給一個worker，變成單線程操作了，那么這個策略的效果就不好了，因此在實際的生產(chǎn)環(huán)境中，用的并不是特別多。

2、mariaDB的并行復(fù)制策略

? 在mysql5.7的時候采用的是基于組提交的并行復(fù)制，換句話說，slave服務(wù)器的回放與主機是一致的，即主庫是如何并行執(zhí)行的那么slave就如何怎樣進行并行回放，這點其實是參考了mariaDB的并行復(fù)制，下面我們來看下其實現(xiàn)原理。

? mariaDB的并行復(fù)制策略利用的就是這個特性：

? 1、能夠在同一組里提交的事務(wù)，一定不會修改同一行；

? 2、主庫上可以并行執(zhí)行的事務(wù)，備庫上也一定是可以并行執(zhí)行的。

? 在實現(xiàn)上，mariaDB是這么做的：

? 1、在一組里面一起提交的事務(wù)，有一個相同的commit_id,下一組就是commit_id+1;

? 2、commit_id直接寫到binlog里面；

? 3、傳到備庫應(yīng)用的時候，相同commit_id的事務(wù)會分發(fā)到多個worker執(zhí)行；

? 4、這一組全部執(zhí)行完成后，coordinator再去取下一批。

? 這是mariaDB的并行復(fù)制策略，大體上看起來是沒有問題的，但是你仔細觀察的話會發(fā)現(xiàn)他并沒有實現(xiàn)“真正的模擬主庫并發(fā)度”這個目標(biāo)，在主庫上，一組事務(wù)在commit的時候，下一組事務(wù)是同時處于“執(zhí)行中”狀態(tài)的。

? 我們真正想要達到的并行復(fù)制應(yīng)該是如下的狀態(tài)，也就是說當(dāng)?shù)谝唤M事務(wù)提交的是，下一組事務(wù)是運行的狀態(tài)，當(dāng)?shù)谝唤M事務(wù)提交完成之后，下一組事務(wù)會立刻變成commit狀態(tài)。

 

? 但是按照mariaDB的并行復(fù)制策略，那么備庫上的執(zhí)行狀態(tài)會變成如下所示：

 

? 可以看到，這張圖跟上面這張圖的最大區(qū)別在于，備庫上執(zhí)行的時候必須要等第一組事務(wù)執(zhí)行完成之后，第二組事務(wù)才能開始執(zhí)行，這樣系統(tǒng)的吞吐量就不夠了。而且這個方案很容易被大事務(wù)拖后腿，如果trx2是一個大事務(wù)，那么在備庫應(yīng)用的時候，trx1和trx3執(zhí)行完成之后，就只能等trx2完全執(zhí)行完成，下一組才能開始執(zhí)行，這段時間，只有一個worker線程在工作，是對資源的浪費。

3、mysql5.7的并行復(fù)制策略

? mysql5.7版本的時候，根據(jù)mariaDB的并行復(fù)制策略，做了相應(yīng)的優(yōu)化調(diào)整后，提供了自己的并行復(fù)制策略，并且可以通過參數(shù)slave-parallel-type來控制并行復(fù)制的策略：

? 1、當(dāng)配置的值為DATABASE的時候，表示使用5.6版本的按庫并行策略；

? 2、當(dāng)配置的值為LOGICAL_CLOCK的時候，表示跟mariaDB相同的策略。

? 此時，大家需要思考一個問題：同時處于執(zhí)行狀態(tài)的所有事務(wù)，是否可以并行？

? 答案是不行的，因為多個執(zhí)行中的事務(wù)是有可能出現(xiàn)鎖沖突的，鎖沖突之后就會產(chǎn)生鎖等待問題。

? 在mariaDB中，所有處于commit狀態(tài)的事務(wù)是可以并行，因為如果能commit的話就說明已經(jīng)沒有鎖的問題，但是大家回想下，我們mysql的日志提交是兩階段提交，如下圖，其實只要處于prepare狀態(tài)就已經(jīng)表示沒有鎖的問題了。

 

? 因此，mysql5.7的并行復(fù)制策略的思想是：

? 1、同時處于prepare狀態(tài)的事務(wù)，在備庫執(zhí)行是可以并行的。

? 2、處于prepare狀態(tài)的事務(wù)，與處于commit狀態(tài)的事務(wù)之間，在備庫上執(zhí)行也是可以并行的。

? 基于這樣的處理機制，我們可以將大部分的日志處于prepare狀態(tài)，因此可以設(shè)置

? 1、
binlog_group_commit_sync_delay 參數(shù)，表示延遲多少微秒后才調(diào)用 fsync;

? 2、
binlog_group_commit_sync_no_delay_count 參數(shù)，表示累積多少次以后才調(diào)用 fsync。

5、基于GTID的主從復(fù)制問題

? 在我們之前講解的主從復(fù)制實操中，每次想要復(fù)制，必須要在備機上執(zhí)行對應(yīng)的命令，如下所示：

change master to master_host='192.168.85.11',master_user='root',master_password='123456',master_port=3306,master_log_file='master-bin.000001',master_log_pos=154;

? 在此配置中我們必須要知道具體的binlog是哪個文件，同時在文件的哪個位置開始復(fù)制，正常情況下也沒有問題，但是如果是一個主備主從集群，那么如果主機宕機，當(dāng)從機開始工作的時候，那么備機就要同步從機的位置，此時位置可能跟主機的位置是不同的，因此在這種情況下，再去找位置就會比較麻煩，所以在5.6版本之后出來一個基于GTID的主從復(fù)制。

? GTID(global transaction id)是對于一個已提交事務(wù)的編號，并且是一個全局唯一的編號。GTID實際上是由UUID+TID組成的，其中UUID是mysql實例的唯一標(biāo)識，TID表示該實例上已經(jīng)提交的事務(wù)數(shù)量，并且隨著事務(wù)提交單調(diào)遞增。這種方式保證事務(wù)在集群中有唯一的ID，強化了主備一致及故障恢復(fù)能力。

1、基于GTID的搭建

? 1、修改mysql配置文件，添加如下配置

gtid_mode=on
enforce-gtid-consistency=true

? 2、重啟主從的服務(wù)

? 3、從庫執(zhí)行如下命令

change master to master_host='192.168.85.111',master_user='root',master_password='123456'
,master_auto_position=1;

? 4、主庫從庫插入數(shù)據(jù)測試。

2、基于GTID的并行復(fù)制

? 無論是什么方式的主從復(fù)制其實原理相差都不是很大，關(guān)鍵點在于將組提交的信息存放在GTId中。

show binlog events in 'lian-bin.000001';

 

previous_gtids:用于表示上一個binlog最后一個gtid的位置，每個binlog只有一個。

gtid:當(dāng)開啟gtid的時候，每一個操作語句執(zhí)行前會添加一個gtid事件，記錄當(dāng)前全局事務(wù)id，組提交信息被保存在gtid事件中，有兩個關(guān)鍵字段，last_committed,sequence_number用來標(biāo)識組提交信息。

 

上述日志看起來可能比較麻煩，可以使用如下命令執(zhí)行：

 

其中l(wèi)ast_committed表示事務(wù)提交的時候，上次事務(wù)提交的編號，如果事務(wù)具有相同的last_committed值表示事務(wù)就在一個組內(nèi)，在備庫執(zhí)行的時候可以并行執(zhí)行。同時大家還要注意，每個last_committed的值都是上一個組事務(wù)的sequence_number值。

看到此處，大家可能會有疑問，如果我們不開啟gtid，分組信息該如何保存呢？

其實是一樣的，當(dāng)沒有開啟的時候，數(shù)據(jù)庫會有一個Anonymous_Gtid，用來保存組相關(guān)的信息。

如果大家想看并行的效果的話，可以執(zhí)行如下代碼：

package com.mashibing;

import JAVA.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.Date;

public class ConCurrentInsert  extends Thread{
    public void run() {
        String url = "jdbc:mysql://192.168.85.111/lian2";
        String name = "com.mysql.jdbc.Driver";
        String user = "root";
        String password = "123456";
        Connection conn = null;
        try {
            Class.forName(name);
            conn = DriverManager.getConnection(url, user, password);//獲取連接
            conn.setAutoCommit(false);//關(guān)閉自動提交，不然conn.commit()運行到這句會報錯
        } catch (Exception e1) {
            e1.printStackTrace();
        }
        // 開始時間
        Long begin = new Date().getTime();
        // sql前綴
        String prefix = "INSERT INTO t1 (id,age) VALUES ";
        try {
            // 保存sql后綴
            StringBuffer suffix = new StringBuffer();
            // 設(shè)置事務(wù)為非自動提交
            conn.setAutoCommit(false);
            // 比起st，pst會更好些
            PreparedStatement pst = (PreparedStatement) conn.prepareStatement("");//準(zhǔn)備執(zhí)行語句
            // 外層循環(huán)，總提交事務(wù)次數(shù)
            for (int i = 1; i <= 10; i++) {
                suffix = new StringBuffer();
                // 第j次提交步長
                for (int j = 1; j <= 10; j++) {
                    // 構(gòu)建SQL后綴
                    suffix.Append("(" +i*j+","+i*j+"),");
                }
                // 構(gòu)建完整SQL
                String sql = prefix + suffix.substring(0, suffix.length() - 1);
                // 添加執(zhí)行SQL
                pst.addBatch(sql);
                // 執(zhí)行操作
                pst.executeBatch();
                // 提交事務(wù)
                conn.commit();
                // 清空上一次添加的數(shù)據(jù)
                suffix = new StringBuffer();
            }
            // 頭等連接
            pst.close();
            conn.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        // 結(jié)束時間
        Long end = new Date().getTime();
        // 耗時
        System.out.println("100萬條數(shù)據(jù)插入花費時間 : " + (end - begin) / 1000 + " s"+"  插入完成");
    }

    public static void main(String[] args) {
        for (int i = 1; i <=10; i++) {
            new ConCurrentInsert().start();
        }
    }
}