在之前我們已經講解了一主一從，雙主雙從的mysql叢集搭建，在單機應用的時候看起來沒有問題，但是在企業的生產環境中，在很多情況下都會有複製延遲的問題。
主從複製的原理我們在此處就不再贅述了，之前已經講過了，這是一個老生常談的問題，原理性質的也幾乎在面試中問爛了，這些原理性質的東西並不是很難，但是你需要注意了，主從複製的延遲問題會成為一個難點，能非常全面的考驗同學們的技術實力。
1、如何檢視同步延遲狀態？
在從伺服器上通過 show slave status 檢視具體的引數，有幾個引數比較重要：
master_log_file: slave中的IO執行緒正在讀取的主伺服器二進位制日誌檔案的名稱
read_master_log_pos: 在當前的主伺服器二進位制日誌中，slave中的IO執行緒已經讀取的位置
relay_log_file: sql執行緒當前正在讀取和執行的中繼日誌檔案的名稱
relay_log_pos: 在當前的中繼日誌中，sql執行緒已經讀取和執行的位置
relay_master_log_file: 由sql執行緒執行的包含多數近期事件的主伺服器二進位制日誌檔案的名稱
slave_io_running: IO執行緒是否被啟動併成功的連線到主伺服器上
slave_sql_running: sql執行緒是否被啟動
seconds_behind_master: 從屬伺服器sql執行緒和從屬伺服器IO執行緒之間的事件差距，單位以秒計
在觀察同步延遲的時候，上述的幾個引數都是比較重要的，其中有一個最最重要的引數需要同學們引起注意，那就是seconds_behind_master，這個引數就表示當前備庫延遲了多長時間，那麼這個值是如何計算的呢？
在進行主從複製的時候，需要注意以下幾個關鍵的時刻：
1、主庫A執行完成一個事務，寫入binlog，我們把這個時刻記為T1;
2、之後傳給備庫B,我們把備庫B接受完這個binlog的時刻記為T2;
3、備庫B執行完成這個事務，我們把這個時刻記為T3;
所謂的主備延遲就是同一個事務，在備庫執行完成的時間和主庫執行完成的時間之間的差值，也就是T3-T1。SBM在進行計算的時候也是按照這樣的方式，每個事務的binlog中都有一個時間欄位，用於記錄主庫寫入的時間，備庫取出當前正在執行的事務的時間欄位的值，計算它與當前系統時間的差值，得到SBM。
如果剛剛的流程聽明白了，那麼下面我們就要開始分析產生這個時間差值的原因有哪些了，以方便我們更好的解決生產環境中存在的問題。
2、主從複製延遲產生的原因有哪些？
1、在某些部署環境中，備庫所在的機器效能要比主庫所在的機器效能差。此時如果機器的資源不足的話就會影響備庫同步的效率；
2、備庫充當了讀庫，一般情況下主要寫的壓力在於主庫，那麼備庫會提供一部分讀的壓力，而如果備庫的查詢壓力過大的話，備庫的查詢消耗了大量的CPU資源，那麼必不可少的就會影響同步的速度
3、大事務執行，如果主庫的一個事務執行了10分鐘，而binlog的寫入必須要等待事務完成之後，才會傳入備庫，那麼此時在開始執行的時候就已經延遲了10分鐘了
4、主庫的寫操作是順序寫binlog，從庫單執行緒去主庫順序讀binlog，從庫取到binlog之後在本地執行。mysql的主從複製都是單執行緒的操作，但是由於主庫是順序寫，所以效率很高，而從庫也是順序讀取主庫的日誌，此時的效率也是比較高的，但是當資料拉取回來之後變成了隨機的操作，而不是順序的，所以此時成本會提高。
5、 從庫在同步資料的同時，可能跟其他查詢的執行緒發生鎖搶佔的情況，此時也會發生延時。
6、 當主庫的TPS併發非常高的時候，產生的DDL數量超過了一個執行緒所能承受的範圍的時候，那麼也可能帶來延遲
7、 在進行binlog日誌傳輸的時候，如果網路頻寬也不是很好，那麼網路延遲也可能造成資料同步延遲
這些就是可能會造成備庫延遲的原因
3、如何解決複製延遲的問題
先說一些虛的東西，什麼叫虛的東西呢？就是一聽上去感覺很有道理，但是在實施或者實際的業務場景中可能難度很大或者很難實現，下面我們從幾個方面來進行描述：
1、架構方面
1、業務的持久化層的實現採用分庫架構，讓不同的業務請求分散到不同的資料庫服務上，分散單臺機器的壓力
2、服務的基礎架構在業務和mysql之間加入快取層，減少mysql的讀的壓力，但是需要注意的是，如果資料經常要發生修改，那麼這種設計是不合理的，因為需要頻繁的去更新快取中的資料，保持資料的一致性，導致快取的命中率很低，所以此時就要慎用快取了
3、使用更好的硬體裝置，比如cpu，ssd等，但是這種方案一般對於公司而言不太能接受，原因也很簡單，會增加公司的成本，而一般公司其實都很摳門，所以意義也不大，但是你要知道這也是解決問題的一個方法，只不過你需要評估的是投入產出比而已。
2、從庫配置方面
1、修改sync_binlog的引數的值
想要合理設定此引數的值必須要清楚的知道binlog的寫盤的流程：
可以看到，每個執行緒有自己的binlog cache，但是共用同一份binlog。
圖中的write，指的就是把日誌寫入到檔案系統的page cache，並沒有把資料持久化到磁碟，所以速度快
圖中的fsync，才是將資料持久化到磁碟的操作。一般情況下，我們認為fsync才佔用磁碟的IOPS
而write和fsync的時機就是由引數sync_binlog來進行控制的。
1、當sync_binlog=0的時候，表示每次提交事務都只write，不fsync
2、當sync_binlog=1的時候，表示每次提交事務都執行fsync
3、當sync_binlog=N的時候，表示每次提交事務都write，但積累N個事務後才fsync。
一般在公司的大部分應用場景中，我們建議將此引數的值設定為1，因為這樣的話能夠保證資料的安全性，但是如果出現主從複製的延遲問題，可以考慮將此值設定為100~1000中的某個數值，非常不建議設定為0，因為設定為0的時候沒有辦法控制丟失日誌的資料量，但是如果是對安全性要求比較高的業務系統，這個引數產生的意義就不是那麼大了。
2、直接禁用salve上的binlog，當從庫的資料在做同步的時候，有可能從庫的binlog也會進行記錄，此時的話肯定也會消耗io的資源，因此可以考慮將其關閉，但是需要注意，如果你搭建的叢集是級聯的模式的話，那麼此時的binlog也會傳送到另外一臺從庫裡方便進行資料同步，此時的話，這個配置項也不會起到太大的作用。
3、設定innodb_flush_log_at_trx_commit 屬性，這個屬性在我講日誌的時候講過，用來表示每一次的事務提交是否需要把日誌都寫入磁碟，這是很浪費時間的，一共有三個屬性值，分別是0（每次寫到服務快取，一秒鐘刷寫一次），1（每次事務提交都刷寫一次磁碟），2（每次寫到os快取，一秒鐘刷寫一次），一般情況下我們推薦設定成2，這樣就算mysql的服務當機了，解除安裝os快取中的資料也會進行持久化。
4、從根本上解決主從複製的延遲問題
很多同學在自己線上的業務系統中都使用了mysql的主從複製，但是大家需要注意的是，並不是所有的場景都適合主從複製，一般情況下是讀要遠遠多於寫的應用，同時讀的時效性要求不那麼高的場景。如果真實場景中真的要求立馬讀取到更新之後的資料，那麼就只能強制讀取主庫的資料，所以在進行實現的時候要考慮實際的應用場景，不要為了技術而技術，這是很嚴重的事情。
在mysql5.6版本之後引入了一個概念，就是我們通常說的並行複製，如下圖：
通過上圖我們可以發現其實所謂的並行複製，就是在中間新增了一個分發的環節，也就是說原來的sql_thread變成了現在的coordinator元件，當日志來了之後，coordinator負責讀取日誌資訊以及分發事務，真正的日誌執行的過程是放在了worker執行緒上，由多個執行緒並行的去執行。
-- 檢視並行的slave的執行緒的個數，預設是0.表示單執行緒
show global variables like 'slave_parallel_workers';
-- 根據實際情況保證開啟多少執行緒
set global slave_parallel_workers = 4;
-- 設定併發複製的方式，預設是一個執行緒處理一個庫，值為database
show global variables like '%slave_parallel_type%';
-- 停止slave
stop slave;
-- 設定屬性值
set global slave_parallel_type='logical_check';
-- 開啟slave
start slave
-- 檢視執行緒數
show full processlist;
通過上述的配置可以完成我們說的並行複製，但是此時你需要思考幾個問題
1、在並行操作的時候，可能會有併發的事務問題，我們的備庫在執行的時候可以按照輪訓的方式傳送給各個worker嗎？
答案是不行的，因為事務被分發給worker以後，不同的worker就開始獨立執行了，但是，由於CPU的不同排程策略，很可能第二個事務最終比第一個事務先執行，而如果剛剛好他們修改的是同一行資料，那麼因為執行順序的問題，可能導致主備的資料不一致。
2、同一個事務的多個更新語句，能不能分給不同的worker來執行呢？
答案是也不行，舉個例子，一個事務更新了表t1和表t2中的各一行，如果這兩條更新語句被分到不同worker的話，雖然最終的結果是主備一致的，但如果表t1執行完成的瞬間，備庫上有一個查詢，就會看到這個事務更新了一半的結果，破壞了事務邏輯的隔離性。
我們通過講解上述兩個問題的最主要目的是為了說明一件事，就是coordinator在進行分發的時候，需要遵循的策略是什麼？
1、不能造成更新覆蓋。這就要求更新同一行的兩個事務，必須被分發到同一個worker中。
2、同一個事務不能被拆開，必須放到同一個worker中。
聽完上面的描述，我們來說一下具體實現的原理和過程。
如果讓我們自己來設計的話，我們應該如何操作呢？這是一個值得思考的問題。其實如果按照實際的操作的話，我們可以按照粒度進行分類，分為按庫分發，按表分發，按行分發。
其實不管按照什麼方式進行分發，大家需要注意的就是在分發的時候必須要滿足我們上面說的兩條規則，所以當我們進行分發的時候要在每一個worker上定義一個hash表，用來儲存當前這個work正在執行的事務所涉及到的表。hash表的key值按照不同的粒度需要儲存不同的值：
按庫分發：key值是資料庫的名字，這個比較簡單
按表分發：key值是庫名+表名
按行分發：key值是庫名+表名+唯一鍵
1、MySQL5.6版本的並行複製策略
其實從mysql的5.6版本開始就已經支援了並行複製，只是支援的粒度是按庫並行，這也是為什麼現在的版本中可以選擇型別為database，其實說的就是支援按照庫進行並行複製。
但是其實用過的同學應該都知道，這個策略的並行效果，取決於壓力模型。如果在主庫上有多個DB，並且各個DB的壓力均衡，使用這個策略的效果會很好，但是如果主庫的所有表都放在同一DB上，那麼所有的操作都會分發給一個worker，變成單執行緒操作了，那麼這個策略的效果就不好了，因此在實際的生產環境中，用的並不是特別多。
2、mariaDB的並行複製策略
在mysql5.7的時候採用的是基於組提交的並行複製，換句話說，slave伺服器的回放與主機是一致的，即主庫是如何並行執行的那麼slave就如何怎樣進行並行回放，這點其實是參考了mariaDB的並行複製，下面我們來看下其實現原理。
mariaDB的並行複製策略利用的就是這個特性：
1、能夠在同一組裡提交的事務，一定不會修改同一行；
2、主庫上可以並行執行的事務，備庫上也一定是可以並行執行的。
在實現上，mariaDB是這麼做的：
1、在一組裡面一起提交的事務，有一個相同的commit_id,下一組就是commit_id+1;
2、commit_id直接寫到binlog裡面；
3、傳到備庫應用的時候，相同commit_id的事務會分發到多個worker執行；
4、這一組全部執行完成後，coordinator再去取下一批。
這是mariaDB的並行複製策略，大體上看起來是沒有問題的，但是你仔細觀察的話會發現他並沒有實現“真正的模擬主庫併發度”這個目標，在主庫上，一組事務在commit的時候，下一組事務是同時處於“執行中”狀態的。
我們真正想要達到的並行複製應該是如下的狀態，也就是說當第一組事務提交的是，下一組事務是執行的狀態，當第一組事務提交完成之後，下一組事務會立刻變成commit狀態。
但是按照mariaDB的並行複製策略，那麼備庫上的執行狀態會變成如下所示：
可以看到，這張圖跟上面這張圖的最大區別在於，備庫上執行的時候必須要等第一組事務執行完成之後，第二組事務才能開始執行，這樣系統的吞吐量就不夠了。而且這個方案很容易被大事務拖後腿，如果trx2是一個大事務，那麼在備庫應用的時候，trx1和trx3執行完成之後，就只能等trx2完全執行完成，下一組才能開始執行，這段時間，只有一個worker執行緒在工作，是對資源的浪費。
3、mysql5.7的並行複製策略
mysql5.7版本的時候，根據mariaDB的並行複製策略，做了相應的優化調整後，提供了自己的並行複製策略，並且可以通過引數slave-parallel-type來控制並行複製的策略：
1、當配置的值為DATABASE的時候，表示使用5.6版本的按庫並行策略；
2、當配置的值為LOGICAL_CLOCK的時候，表示跟mariaDB相同的策略。
此時，大家需要思考一個問題：同時處於執行狀態的所有事務，是否可以並行？
答案是不行的，因為多個執行中的事務是有可能出現鎖衝突的，鎖衝突之後就會產生鎖等待問題。
在mariaDB中，所有處於commit狀態的事務是可以並行，因為如果能commit的話就說明已經沒有鎖的問題，但是大家回想下，我們mysql的日誌提交是兩階段提交，如下圖，其實只要處於prepare狀態就已經表示沒有鎖的問題了。
因此，mysql5.7的並行複製策略的思想是：
1、同時處於prepare狀態的事務，在備庫執行是可以並行的。
2、處於prepare狀態的事務，與處於commit狀態的事務之間，在備庫上執行也是可以並行的。
基於這樣的處理機制，我們可以將大部分的日誌處於prepare狀態，因此可以設定
1、binlog_group_commit_sync_delay 引數，表示延遲多少微秒後才呼叫 fsync;
2、binlog_group_commit_sync_no_delay_count 引數，表示累積多少次以後才呼叫 fsync。
5、基於GTID的主從複製問題
在我們之前講解的主從複製實操中，每次想要複製，必須要在備機上執行對應的命令，如下所示：
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是哪個檔案，同時在檔案的哪個位置開始複製，正常情況下也沒有問題，但是如果是一個主備主從叢集，那麼如果主機當機，當從機開始工作的時候，那麼備機就要同步從機的位置，此時位置可能跟主機的位置是不同的，因此在這種情況下，再去找位置就會比較麻煩，所以在5.6版本之後出來一個基於GTID的主從複製。
GTID(global transaction id)是對於一個已提交事務的編號，並且是一個全域性唯一的編號。GTID實際上是由UUID+TID組成的，其中UUID是mysql例項的唯一標識，TID表示該例項上已經提交的事務數量，並且隨著事務提交單調遞增。這種方式保證事務在叢集中有唯一的ID，強化了主備一致及故障恢復能力。
1、基於GTID的搭建
1、修改mysql配置檔案，新增如下配置
gtid_mode=on
enforce-gtid-consistency=true
2、重啟主從的服務
3、從庫執行如下命令
change master to master_host='192.168.85.111',master_user='root',master_password='123456'
,master_auto_position=1;
4、主庫從庫插入資料測試。
2、基於GTID的並行複製
無論是什麼方式的主從複製其實原理相差都不是很大，關鍵點在於將組提交的資訊存放在GTId中。
show binlog events in 'lian-bin.000001';
previous_gtids:用於表示上一個binlog最後一個gtid的位置，每個binlog只有一個。
gtid:當開啟gtid的時候，每一個操作語句執行前會新增一個gtid事件，記錄當前全域性事務id，組提交資訊被儲存在gtid事件中，有兩個關鍵欄位，last_committed,sequence_number用來標識組提交資訊。
上述日誌看起來可能比較麻煩，可以使用如下命令執行：
其中last_committed表示事務提交的時候，上次事務提交的編號，如果事務具有相同的last_committed值表示事務就在一個組內，在備庫執行的時候可以並行執行。同時大家還要注意，每個last_committed的值都是上一個組事務的sequence_number值。
看到此處，大家可能會有疑問，如果我們不開啟gtid，分組資訊該如何儲存呢？
其實是一樣的，當沒有開啟的時候，資料庫會有一個Anonymous_Gtid，用來儲存組相關的資訊。
如果大家想看並行的效果的話，可以執行如下程式碼：
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);//關閉自動提交，不然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();
// 設定事務為非自動提交
conn.setAutoCommit(false);
// 比起st，pst會更好些
PreparedStatement pst = (PreparedStatement) conn.prepareStatement("");//準備執行語句
// 外層迴圈，總提交事務次數
for (int i = 1; i <= 10; i++) {
suffix = new StringBuffer();
// 第j次提交步長
for (int j = 1; j <= 10; j++) {
// 構建SQL字尾
suffix.append("(" +i*j+","+i*j+"),");
}
// 構建完整SQL
String sql = prefix + suffix.substring(0, suffix.length() - 1);
// 新增執行SQL
pst.addBatch(sql);
// 執行操作
pst.executeBatch();
// 提交事務
conn.commit();
// 清空上一次新增的資料
suffix = new StringBuffer();
}
// 頭等連線
pst.close();
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
// 結束時間
Long end = new Date().getTime();
// 耗時
System.out.println("100萬條資料插入花費時間 : " + (end - begin) / 1000 + " s"+" 插入完成");
}
public static void main(String[] args) {
for (int i = 1; i <=10; i++) {
new ConCurrentInsert().start();
}
}
}