快捷導(dǎo)航

分享MySQL?主從延遲與讀寫分離的七種解決方案

更新時(shí)間：2022年03月28日 11:33:08 作者：微觀技術(shù)

這篇文章主要介紹了分享MySQL?主從延遲與讀寫分離的七種解決方案，常見的解決方式是分庫分表，每次讀寫都是操作主庫的一個(gè)分表，從庫只用來做數(shù)據(jù)備份。當(dāng)主庫發(fā)生故障時(shí)，主從切換，保證集群的高可用性，下面詳細(xì)的相關(guān)資料介紹，需要的小伙伴可以參考一下

客戶端會(huì)集成 SDK，每次執(zhí)行 SQL 時(shí)，會(huì)判斷是寫或讀操作。
如果是寫 SQL，請(qǐng)求會(huì)發(fā)到主庫。
主數(shù)據(jù)庫執(zhí)行SQL，事務(wù)提交后，會(huì)生成 binlog ，并同步給從庫。
從庫通過 SQL 線程回放 binlog ，并在從庫表中生成相應(yīng)數(shù)據(jù)。
如果是讀 SQL，請(qǐng)求會(huì)通過負(fù)載均衡策略，挑選一個(gè) 從庫處理用戶請(qǐng)求。

看似非常合理，細(xì)想?yún)s不是那么回事。

主庫與從庫是采用異步復(fù)制數(shù)據(jù)，如果這兩者之間數(shù)據(jù)還沒有同步怎么辦?

主庫剛寫完數(shù)據(jù)，從庫還沒來得及拉取最新數(shù)據(jù)，讀請(qǐng)求就來了，給用戶的感覺，數(shù)據(jù)丟了?

針對(duì)這個(gè)問題，今天，我們就來探討下有什么解決方案?

一、強(qiáng)制走主庫

針對(duì)不用的業(yè)務(wù)訴求，區(qū)別性對(duì)待。

場(chǎng)景一：

如果是對(duì)數(shù)據(jù)的實(shí)時(shí)性要求不是很高，比如：大V有千萬粉絲，發(fā)布一條微博，粉絲晚幾秒鐘收到這條信息，并不會(huì)有特別大的影響。這時(shí)，可以走從庫。

場(chǎng)景二：

如果對(duì)數(shù)據(jù)的實(shí)時(shí)性要求非常高，比如金融類業(yè)務(wù)。我們可以在客戶端代碼標(biāo)記下，讓查詢強(qiáng)制走主庫。

二、從庫延遲查詢

由于主從庫之間數(shù)據(jù)同步需要一定的時(shí)間間隔，那么有一種策略是延遲從從庫查詢數(shù)據(jù)。

比如：

select sleep(1)
select * from order where order_id=11111;

在正式的業(yè)務(wù)查詢時(shí)，先執(zhí)行一個(gè)sleep 語句，給從庫預(yù)留一定的數(shù)據(jù)同步緩沖期。

因?yàn)槭遣捎靡坏肚?，?dāng)面對(duì)高并發(fā)業(yè)務(wù)場(chǎng)景時(shí)，性能會(huì)下降的非常厲害，一般不推薦這個(gè)方案。

三、判斷主從是否延遲?決定選主庫還是從庫

之前寫過一篇文章《京東一面：MySQL 主備延遲有哪些坑?主備切換策略》。

有講過什么是主備延遲?、主備延遲的常見原因?

方案一：

在從庫執(zhí)行命令 show slave status。

查看seconds_behind_master 的值，單位為秒，如果為 0，表示主備庫之間無延遲。

方案二：

比較主從庫的文件點(diǎn)位。

還是執(zhí)行show slave status，響應(yīng)結(jié)果里有截個(gè)關(guān)鍵參數(shù)。

Master_Log_File 讀到的主庫最新文件。
Read_Master_Log_Pos 讀到的主庫最新文件的坐標(biāo)位置。
Relay_Master_Log_File 從庫執(zhí)行到的最新文件。
Exec_Master_Log_Pos 從庫執(zhí)行到的最新文件的坐標(biāo)位置。

兩兩比較，上面的參數(shù)是否相等。

方案三：

比較 GTID 集合。

Auto_Position=1 主從之間使用 GTID 協(xié)議。
Retrieved_Gtid_Set 從庫收到的所有binlog日志的 GTID 集合。
Executed_Gtid_Set 從庫已經(jīng)執(zhí)行完成的 GTID 集合。

比較 Retrieved_Gtid_Set 和 Executed_Gtid_Set 的值是否相等。

在執(zhí)行業(yè)務(wù)SQL操作時(shí)，先判斷從庫是否已經(jīng)同步最新數(shù)據(jù)。從而決定是操作主庫，還是操作從庫。

缺點(diǎn)：

無論采用上面哪一種方案，如果主庫的寫操作頻繁不斷，那么從庫的值永遠(yuǎn)跟不上主庫的值，那么讀流量永遠(yuǎn)是打在了主庫上。

1.針對(duì)這個(gè)問題，有什么解決方案?

這個(gè)問題跟 MQ消息隊(duì)列既要求高吞吐量又要保證順序是一樣的，從全局來看確實(shí)無解，但是縮小范圍就容易多了，我們可以保證一個(gè)分區(qū)內(nèi)的消息有序。

回到主從庫之間的數(shù)據(jù)同步問題，從庫查詢哪條記錄，我們只要保證之前對(duì)應(yīng)的寫binglog已經(jīng)同步完數(shù)據(jù)即可，可以不用管主從庫的所有的事務(wù)binlog 是否同步。

問題是不是一下簡(jiǎn)單多了。

四、從庫節(jié)點(diǎn)判斷主庫位點(diǎn)

在從庫執(zhí)行下面命令，返回是一個(gè)正整數(shù) M，表示從庫從參數(shù)節(jié)點(diǎn)開始執(zhí)行了多少個(gè)事務(wù)。

select master_pos_wait(file, pos[, timeout]);

file 和 pos 表示主庫上的文件名和位置。
timeout 可選，表示這個(gè)函數(shù)最多等待 N 秒。

缺點(diǎn)：

master_pos_wait 返回結(jié)果無法與具體操作的數(shù)據(jù)行做關(guān)聯(lián)，所以每次接收讀請(qǐng)求時(shí)，從庫還是無法確認(rèn)是否已經(jīng)同步數(shù)據(jù)，方案實(shí)用性不高。

五、比較 GTID

執(zhí)行下面查詢命令：

阻塞等待，直到從庫執(zhí)行的事務(wù)中包含gtid_set，返回 0。
超時(shí)，返回 1。

select wait_for_executed_gtid_set(gtid_set, 1);

MySQL 5.7.6 版本開始，允許在執(zhí)行完更新類事務(wù)后，把這個(gè)事務(wù)的 GTID 返回給客戶端。具體操作，將參數(shù)session_track_gtids 設(shè)置為OWN_GTID，調(diào)用 API 接口mysql_session_track_get_first 返回結(jié)果解析出 GTID。

處理流程：

發(fā)起寫 SQL 操作，在主庫成功執(zhí)行后，返回這個(gè)事務(wù)的 GTID。
發(fā)起讀 SQL 操作時(shí)，先在從庫執(zhí)行 select wait_for_executed_gtid_set (gtid_set, 1)。
如果返回 0，表示已經(jīng)從庫已經(jīng)同步了數(shù)據(jù)，可以在從庫執(zhí)行查詢操作。
否則，在主庫執(zhí)行查詢操作。

缺點(diǎn)：

跟上面的 master_pos_wait 類似，如果寫操作與讀操作沒有上下文關(guān)聯(lián)，那么 GTID 無法傳遞。方案實(shí)用性不高。