摘要：讀寫分離中間件具有獨立的。變量語句將被廣播考慮到節點間數據一致性問題，只會分發到主節點。節點健康檢查，提升數據庫系統可用性。

UCloud MySQL云數據庫讀寫分離

背景

數據顯示，關系型數據庫在OLTP業務下96.87%都在等待讀I/O，而處理器計算僅僅占了5.3%，這說明要提高數據庫的QPS性能，關鍵的一點是提高系統的IO能力。

另一個數據表明， 大多數業務對數據庫的訪問，是讀大于寫。 典型的如電商、O2O、互聯網金融等業務，讀寫比例可以達到 5:1 甚至 10:1 。

提高IO能力的方法， 除了升級硬件， 提升單個節點的磁盤I/O能力之外， 還有一個重要的方法是讀寫分離。 可以部署一主多從的主從復制集群， 進而將讀請求分發給多個數據庫節點并行處理?？紤]到大部分業務對數據庫的訪問以讀居多， 讀寫分離能夠給數據庫性能帶來明顯的增益。

首先，多個節點并行讀取，能夠提供幾倍于單個節點的磁盤數據讀取能力；第二，由于將讀請求分攤到多個節點， 單個節點的I/O也得以減輕， I/O等待時間得以減小。 最終，整個系統的I/O能力得到有效提升。

對于OLAP業務而言， 由于需要涉及大量的內存存儲和計算， CPU和內存可能成為瓶頸，讀寫分離也具有一定的意義。 通過將數據分析請求分流到多個節點，可以讓不同的數據分析操作，在多個節點并行地執行， 節點之間互不干擾， 充分發揮并行處理的優勢。

方案

UDB目前已提供完整的讀寫分離方案，具體做法如下：

1. 在控制臺上， 通過?創建從庫?， 創建出一主多從的主從復制集群；
2. 在控制臺上，通過?開啟讀寫分離， 為主從復制集群創建讀寫分離中間件。該中間件作為業務程序和主從復制集群之間的代理，中轉業務程序發往主從復制集群的請求。 中轉過程中，讀寫分離中間件將識別請求的類型，如果是讀請求，則根據某種分發規則（分發規則可配置），將讀請求分發到主從節點，如果是寫請求，則發往主節點。
3. 讀寫分離中間件具有獨立的IP。 需要做讀寫分離時，客戶可以將業務的數據庫訪問地址，直接切換到該IP即可，無需修改業務程序代碼， 支持標準SQL、系統命令、事務、視圖、存儲過程、觸發器等MySQL功能。

UDB讀寫分離中間件是永久免費的， 客戶只需要創建好主從節點，即可開啟并使用讀寫分離中間件，無需額外費用。

普通版UDB和高可用UDB均支持創建讀寫分離中間件。

目前， UDB讀寫分離功能已經在北京二可用區B、北京二可用區C、北京二可用區D、北京二可用區E、上海二可用區B、廣東可用區二B、香港可用區A上線，其他地域和可用區陸續上線中。

實現原理

如圖所示， 一個讀寫分離中間件由兩個高性能 Proxy 節點和 UCloud 分布式負載均衡產品 ULB 構成。 兩個 Proxy 采用雙活模式部署， 前端采用 ULB 來做負載均衡和容災，保證整個系統無單點。 客戶可以對讀請求的分發方式，進行自定義配置（配置方法詳見下文），Proxy 節點根據客戶配置分發讀請求。

讀寫分離中間件對業務請求的處理方式非常簡單， 有三個基本原則：

1. 從業務請求中，識別Select SQL， 只有Select SQL才考慮做讀寫分離；
2. 如果該Select SQL處于一個事務當中， 則將該Select SQL發往主節點，如果該Select SQL不處于事務當中， 則根據讀請求分發策略， 將該Select SQL發往主節點或者從節點。
3. 對于一些必須要廣播的語句， 如Use database、Set Session變量等語句，由中間件進行廣播，如果廣播未全部成功，則中斷客戶端連接，以此嚴格保證各節點的數據一致性。

以及針對一些特殊情況的修正：

1. 涉及到鎖的Select語句， 如Select For Update 、 Select Lock 等， 將被分發到主節點。
2. 將Set語句中的變量， 分為三種類型： Session、Global、User。 set Session、Set User變量語句將被廣播；考慮到節點間數據一致性問題， Set Global只會分發到主節點。 后續含全局變量的 Select 語句， 也只會發送到主節點。

功能限制

1.MySQL協議限制

1.1 不支持SSL加密

1.2 暫不支持壓縮協議

1.3 暫不支持綁定除3306之外的端口，UDB主從節點端口也必須為3306

2.SQL限制

2.1 支持savepoint語句（該語句將被分發到主節點）， 但暫不支持rollback to savepoint

2.2 暫不支持XA事務命令

2.3?Lock Tables/Unlock Tables?將被分發到主節點，而Proxy層不會有任何Lock狀態。因此， Lock Tables產生的鎖不會影響到從節點。

2.4 存儲過程，以及存儲過程后的Select語句， 一律分發到主節點。如：

call udb_test("000001",@pp,@qq);
select @pp,@qq;
select * from t1;

上述兩條 Select 語句，都將被分發到主節點。

2.5?show processlists、?Show master/slave status、kill query、COM_PROCESS_INFO、COM_STATISTICS命令，目前只會轉發到主節點，針對中間件和數據庫系統管理場景的， 更豐富的系統管理命令正在開發中。

2.6 暫不支持COM_TABLE_DUMP和COM_CHANGE_USER協議。

3.對Set語句的特別說明

3.1 Set Session、Set User變量語句， 將被廣播到主節點和從節點，如果廣播失敗，Proxy將斷開和客戶端連接， 從而撤銷廣播失敗導致的數據不一致； 考慮到節點間的數據一致性， Set global變量語句只會被分發到主節點。 后續含全局變量的 Select 語句， 也只會發送到主節點。

3.2 不允許在一條Set語句中，同時出現Global變量和Session、User變量。

4.不推薦使用讀寫分離的場景

a. 業務的SQL均為事務SQL（所有SQL都包含在事務中）， 由于事務只能被路由到主節點，故該場景下UDB讀寫分離無法起到分離讀請求的作用

b. 業務使用了大量存儲過程。 由于存儲過程只能被路由到主節點，故該場景下UDB讀寫分離無法起到分離讀請求的作用需要修改的點

c. 不推薦業務使用短連接來訪問讀寫分離。 UDB讀寫分離中間件處理業務數據庫連接的邏輯是： 業務每向讀寫分離中間件發起一個連接，讀寫分離會到每個主從節點均建立一個連接，用于后續的SQL轉發。 因此，如果業務使用短連接訪問讀寫分離，且業務發起短連接的頻率非常高，則讀寫分離中間件將頻繁地建連-斷連， 在進程內部產生大量TIME WAIT的TCP連接，占用甚至耗盡進程的句柄數，導致業務新來連接無法建立。

功能優勢

1. 讀寫分離地址統一，讀寫分離實現對業務程序透明。

在原有的主從節點模式中，主UDB節點和每個從節點有一個多帶帶的連接地址，用戶需要在業務程序中，多帶帶對每個地址自行進行配置管理，才能實現將寫請求發往主UDB節點， 而將讀請求發往從節點。

UDB的讀寫分離功能，則額外提供一個讀寫分離地址，用戶連接該地址后即可對所屬主從節點進行讀寫操作，讀寫語句的轉發邏輯完全對業務透明，降低維護成本。

2. 讀寫分離Proxy雙活無單點， 可用性和穩定性有保證。

相對于業內開源讀寫分離中間件的單節點部署方式， UDB讀寫分離Proxy采用雙活部署， 同時前端利用分布式負載均衡產品ULB來做負載均衡和容災， 整個Proxy層無單點， 高可用和穩定性得以保證。

3. 創建簡單， 配置靈活。

在控制臺上一鍵即可開啟/釋放讀寫分離功能， 提供四種讀請求分發模式， 供客戶根據業務需要靈活選擇和配置。這四種模式為：

3.1 主節點： 讀請求只下發給主節點， 從節點不下發任何請求；

3.2 節點均衡： 讀請求均勻分發給主從節點；

3.3 只讀節點均衡： 讀請求均勻分發給所有從節點，但不分發給主節點；

3.4 自定義： 客戶自定義讀請求的分發比例。

4. UDB節點健康檢查， 提升數據庫系統可用性。

讀寫分離Proxy會對所屬主從復制集群的所有節點，進行健康檢查。 當發現主節點不可用時， 將拒絕發往主節點的寫入請求以及系統命令等。當發現從節點不可用或者和主節點的數據延遲超過閾值（該閾值可配置）時，將把不可用的從節點剔除出分發目標列表，直到從節點恢復或者數據延遲低于閾值時，才將其重新加入分發目標列表。

5. 用于免費， 降低業務使用成本。

UDB讀寫分離功能對所有客戶永久免費使用， 客戶無需支付任何額外費用。

性能優勢

讀寫分離中間件的核心價值，在于添加從節點后，數據庫讀性能有顯著提高，從節點的讀請求處理能力，能夠得到充分發揮。 如果做不到這一點，中間件的 MySQL兼容度再好， 管理功能再強大，也無法滿足業務的本質需求。 UDB讀寫分離中間件，通過精心的結構設計，推敲和打磨每一行跟性能相關的代碼， 實現了讓讀性能隨從節點數量線性增長：增加相應數量的從節點，數據庫的讀性能也能夠隨之線性增長。?從這個意義上講，使用UDB讀寫分離中間件， 能夠將客戶所購買的從節點的每一點性能都壓榨出來，杜絕浪費。而這一點，是業內絕大多數中間件無法做到的。

從上圖可以看到， 采用Sysbench測試程序，在測試線程數>=128個（保證測試壓力足夠）的情況下， 讀QPS隨著從節點數增加而線性增加： 只有1個主節點時QPS最高能到5萬，1主1從QPS能夠到10萬， 1主2從時QPS峰值為15萬。

為了能夠更加直觀地說明：讓讀性能隨從節點數量線性擴展 這一效果， 我們從上圖中， 分別挑選1主0從， 1主1從， 1主2從三種配置下的最高讀QPS，得到以下性能曲線：

從圖中可以看出， 讀QPS隨著節點數的增加，呈現幾乎完美的線性增長。

ProxysQL是業內一款著名的數據庫中間件，主要功能有讀寫分離、數據庫管理、Cache等， 為國內外大量DBA所喜愛。甚至在某種程度上，ProxySQL是開源讀寫分離中間件的第一選擇。

產品發布之前，為了搞清楚UDB讀寫分離中間件在讀性能上離業內標桿還有多大差距， 我們做了兩個產品的讀性能對比測試。出乎意料的是， 我們發現在讀性能上，UDB讀寫分離中間件幾乎完勝ProxySQL：

從測試結果看， 對于配置完全一樣的兩個后端數據庫節點，UDB讀寫分離中間件讀性能峰值能夠到10w QPS， 而ProxySQL最高僅為7.5w QPS， 兩者之間有25%的差距（但是ProxySQL消耗的CPU比UDB讀寫分離中間件低，由于瓶頸在后端數據庫節點IO上，因此兩個中間件都沒有跑完測試機的CPU）。

考慮到ProxySQL采用C++開發， 而UDB讀寫分離中間件采用Go語言開發，這個結果讓我們感到驚訝。我們仔細檢查了測試方法并進行多次測試，得到的結果是一樣的。

從這次測試我們得到的結論是：一般而言，C++由于對底層更具掌控力，在性能上相較于Go會有更多的優勢，但這也并不是絕對的。性能往往更多地取決于模塊設計是否科學，代碼是否經過精心優化，以及是否能夠充分利用多核CPU強勁的計算能力。

具體的測試方法如下：

建表語句：

CREATE TABLE `sbtest1` (`id` int(10) unsigned NOT NULL,`k` int(10) unsigned NOT NULL DEFAULT "0",`c` char(120) NOT NULL DEFAULT "",`pad` char(60) NOT NULL DEFAULT "",PRIMARY KEY (`id`),KEY `k_1` (`k`)) ENGINE=InnoDB DEFAULT CHARSET=utf8 MAX_ROWS=1000000

測試命令：

./src/sysbench --db-driver=mysql 
--mysql-table-engine=innodb 
--mysql-host=10.9.99.169  --mysql-port=3306 --mysql-user=root --mysql-password="liuly624@cloud" --mysql-db=sbtest 
--oltp-tables-count=5 --oltp-table-size=50000000 --report-interval=2 --max-requests=0 --time=300 --threads=128 
--rand-init=on --rand-type=special --rand-spec-pct=5 --percentile=99 --oltp_auto_inc=off 
--test=/data/sysbench/tests/include/oltp_legacy/select.lua run

具體的測試步驟是：

1. 分別采用64、128、192、256線程，直連UDB主節點進行壓測，記錄QPS；
2. 分別采用64、128、192、256線程，連接讀寫分離中間件，后端配置1主0從共1個UDB節點進行壓測， 記錄QPS；
3. 分別采用64、128、192、256線程，連接讀寫分離中間件，后端配置1主1從共2個UDB節點進行壓測， 記錄QPS（注： 主從節點的配置完全一致，下同）；
4. 分別采用64、128、192、256線程，連接讀寫分離中間件，后端配置1主2從共3個UDB節點進行壓測， 記錄QPS。
5. 分別采用64、128、192、256線程，連接ProxySQL中間件， 后端配置1主2從共3個節點，記錄QPS。 我們在測試時發現，由于ProxySQL是以SQL語句為粒度進行轉發，而且轉發目的是以group 為單位，主從節點不在同一個group。因此雖然后端配置了3個節點，實際利用的是2個從節點。因此，在上面性能測試結果最后一張圖中，這次測試只體現ProxySQL后端接2個從節點的性能。

管理功能

SQL自定義路由

UDB讀寫分離中間件支持SQL自定義路由功能。 通過兩種方式， 可以將一條SQL，指定路由到主節點或者某個從節點。

方式1：SQL模板

可以通過以下中間件自定義SQL，為中間件配置SQL路由規則：

uinsert sql_route ("SQL模板", "UDB Id");
舉例：
uinsert sql_route ("select money from t_account where uid=? and name=?" : "udbha-robert");

其中，?select money from t_account where uid=? and name=??為SQL模板，該模板將實際參數用?號代替， 用來概括結構相同，但參數不同的同一類SQL。?udbha-robert為要路由到的UDB節點id。 該自定義SQL下發到中間件節點后， 凡是和SQL模板結構相同的SQL， 都將被路由到udbha-robert這個UDB節點。

注意： 現階段，SQL模板的結構，和實際SQL語句的結構，必須完全一致。假如SQL模板為：select money from t_account where uid=? and name=??則業務發起SQL， 必須保證where查詢條件中的uid在前， name在后。 否則中間件會認為結構和SQL模板不一樣的SQL。

方式2：SQL Hints

對于Select語句， 可以在SQL前面的注釋中，增加forcemater, forceslave命令， 來指定將該Select SQL路由主節點， 或者某個從節點。舉例：

/*force_master*/ select money from t_account where uid="tony";?: 該語句將被路由到主節點/*force_slave*/ select money from t_account where uid="tony";?: 該語句將被路由到某個節點

注意：注釋必須為： /* */， #和–類型的SQL不具備該功能。

如果要刪除某一條SQL路由規則，可采用以下自定義SQL：

udelete sql_route ("select money from t_account where uid=?");

查看中間件中全部路由規則，可以采用以下自定義SQL：

ushow all_sql_route;

查看上一條SQL路由目的地

要查看上一條SQL被路由到了哪個節點，可以采用以下命令：

mysql> ushow last_route;
+-------------+------------+
| LastSqlCmd  | Route      |
+-------------+------------+
| show tables | udb-123qwe |
+-------------+------------+
1 row in set (0.00 sec)

其中，udb-123qwe 為 show tables 這條SQL被路由到的節點。

業務ip訪問白名單

在UDB主從節點前，增加了讀寫分離中間件后， UDB看到的客戶端Ip是中間件的Ip， 而不是業務真實ip。 因此， MySQL根據用戶名+訪問ip來做權限管理的功能， 便不再可用。 為了解決這一問題， UDB讀寫分離中間件提供業務ip訪問白名單機制。該機制具備兩個功能： 1. 業務ip訪問白名單： 在該白名單中的業務ip，才能登錄到中間件，否則拒絕登錄。 2. 業務操作白名單：業務Ip登錄后，發起的操作將被中間件進行鑒別。 如果該操作在操作白名單中，則中間件予以通過；否則將被拒絕。

業務ip訪問白名單， 和業務操作白名單，均可通過4條自定義SQL進行配置。同時為了減少用戶學習成本， 這4條自定義SQL的語法，和MySQL用戶權限管理語句：create user、grant、revoke、drop user高度相似。

舉例： 假如用戶需要創建一個名為robert的賬號， 并只允許該賬號在10.10.1.%網段， 和10.10.2.%網段的UHost，訪問數據庫。 而且， robert 在10.10.1.%網段發起訪問時， 只具備select權限，但不具備其他權限（比如create table、insert等）；robert 在10.10.2.%網段發起訪問時， 除了不具備create 庫表的權限，具備所有其他權限，而且能夠授權給其他用戶。

為了實現這個權限配置，可以采用以下做法：

1. 登錄讀寫分離中間件（使用高權限用戶，比如root）
2. 創建mysql用戶：

使用標準的create user、grant命令， 到主udb節點（可直連或者通過讀寫分離中間件）去創建用戶。其中， 用戶ip必須為%?create user "robert"@"%" identified by "123qwe";?grant all privileges on test.* to "robert"@"%";?注： udb等云數據庫， 均不可對整個實例(.)進行授權， 而只能以庫或表為單位進行授權

創建成功后， 可以使用該用戶名（robert）， 在任意uhost上， 登錄讀寫分離中間件。

1. 使用ucreate user命令， 創建ip訪問白名單：

ucreate user "robert"@"10.10.1.%";

該命令執行后， 允許10.10.1.%網段的robert賬號登錄中間件， 其他ip禁止。 但登錄后， 權限只有show databases 和 show processlist，暫無其他權限。

1. 使用ugrant 、urevoke等命令，配置ip操作權限白名單。比如：?ugrant select to "robert"@"10.10.1.%";
   注： 和標準grant命令不同的是， ugrant省略了 指定授權對象(on?.） 這個語法， ugrant的授權對象，直接繼承自grant 執行該命令， 為 "robert"@"10.10.1.%" 用戶開通 select 權限 如果要為10.10.2.%上的roert賬號， 開通除create table之外的其他權限，可以這樣做：?ugrant all privileges to "robert"@"10.10.2.%" with grant option;?urevoke create from "robert"@"10.10.2.%";?執行該命令， 允許 "robert"@"10.10.2.%" 執行除create table、database 之外的所有其他操作； 同時，還支持級聯授權，允許"robert"@"10.10.2.%" 將權限授予其他用戶（發起ucreate user 和ugrant命令）。
2. 使用udrop命令， 刪除訪問ip訪問控制白名單。如：

udrop user "robert"@"10.10.1.%";

注意： 如果robert用戶下的所有ip訪問白名單都被刪除， 則視為系統沒有配置白名單， 此時可以用robert賬號從任意uhost上登錄。

1. 提供權限配置查詢命令：

ushow users;

使用介紹

開啟讀寫分離

在UCloud控制臺， 創建好UDB主從節點之后， 點擊詳情到達管理二級頁面，即可看到讀寫分離：

在讀寫分離頁面點擊開啟讀寫分離：

點擊確認開啟成功后，控制臺可以看到讀寫分離Proxy的詳細信息，并進行管理，如關閉、重啟、設置等管理：

修改延遲閾值

點擊讀寫分離管理頁面延遲閾值欄的編輯圖標， 即可修改延遲閾值：

修改讀模式

點擊讀寫分離管理頁面讀模式欄的編輯圖標或點擊設置讀寫分離按鈕， 即可修改讀模式：