摘要：读写分离中间件具有独立的。变量语句将被广播考虑到节点间数据一致性问题，只会分发到主节点。节点健康检查，提升数据库系统可用性。

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的详细信息，并进行管理，如关闭、重启、设置等管理：

修改延迟阈值

点击读写分离管理页面延迟阈值栏的编辑图标， 即可修改延迟阈值：

修改读模式

点击读写分离管理页面读模式栏的编辑图标或点击设置读写分离按钮， 即可修改读模式：