【架构实战】读写分离中间件对比（ShardingSphere/MyCat）

一、为什么需要读写分离在大多数互联网应用中读操作远多于写操作读请求70-80% 写请求20-30%单机数据库的问题主库处理所有写请求 部分读请求 ↓ 连接池耗尽 → 响应变慢 → 用户投诉解决方案读写分离写请求 ──► 主库 │ 读请求 ──┼──► 从库1 │ └──► 从库2二、ShardingSphere实战1. ShardingSphere-JDBC架构应用 ──► ShardingSphere-JDBC嵌入应用 ├── 改写SQL ├── 路由到正确的数据源 └── 结果合并 │ ┌────┼────┐ ▼ ▼ ▼ 主库 从库1 从库2依赖配置dependencygroupIdorg.apache.shardingsphere/groupIdartifactIdshardingsphere-jdbc-core/artifactIdversion5.3.0/version/dependency数据源配置# application.ymlspring:shardingsphere:datasource:names:master,slave0,slave1master:type:com.zaxxer.hikari.HikariDataSourcedriver-class-name:com.mysql.cj.jdbc.Driverjdbc-url:jdbc:mysql://192.168.1.100:3306/order_dbusername:rootpassword:passwordslave0:type:com.zaxxer.hikari.HikariDataSourcedriver-class-name:com.mysql.cj.jdbc.Driverjdbc-url:jdbc:mysql://192.168.1.101:3306/order_dbusername:rootpassword:passwordslave1:type:com.zaxxer.hikari.HikariDataSourcedriver-class-name:com.mysql.cj.jdbc.Driverjdbc-url:jdbc:mysql://192.168.1.102:3306/order_dbusername:rootpassword:passwordrules:readwrite_splitting:data-sources:ds_master_slave:type:Staticprops:write-data-source-name:masterread-data-source-names:slave0,slave1load-balancer:type:ROUND_ROBINprops:alpha:5# 强制路由到主库写操作后立即读取props:query-with-connection-preference:trueJava配置方式ConfigurationpublicclassShardingSphereConfig{BeanpublicDataSourcedataSource(){MapString,DataSourcedataSourceMapnewHashMap();// 主库DataSourcemastercreateDataSource(jdbc:mysql://192.168.1.100:3306/order_db);dataSourceMap.put(master,master);// 从库1DataSourceslave0createDataSource(jdbc:mysql://192.168.1.101:3306/order_db);dataSourceMap.put(slave0,slave0);// 从库2DataSourceslave1createDataSource(jdbc:mysql://192.168.1.102:3306/order_db);dataSourceMap.put(slave1,slave1);// 读写分离配置ReadwriteSplittingDataSourceRuleConfigurationdataSourceConfignewReadwriteSplittingDataSourceRuleConfiguration(ds_master_slave,// 数据源名称master,// 主库Arrays.asList(slave0,slave1),// 从库round_robin// 负载均衡策略);// 负载均衡配置PropertiespropsnewProperties();props.setProperty(alpha,5);// 权重MapString,DataSourcedataSourcesnewHashMap();dataSources.put(ds_master_slave,ReadwriteSplittingDataSourceFactory.create(dataSourceMap,dataSourceConfig,props));returndataSources.get(ds_master_slave);}}强制路由到主库// 使用Hint强制路由到主库HintManagerhintManagerHintManager.getInstance();hintManager.setMasterRouteOnly();// 写完数据后立即读取走主库try(HintManagerhintManagerHintManager.getInstance()){hintManager.setMasterRouteOnly();OrderorderorderService.createOrder(request);// 这里读取走主库returnorderService.getOrder(order.getId());}2. ShardingSphere-Proxy架构应用不需要修改──► MySQL Client │ ShardingSphere-Proxy 独立的MySQL服务进程 │ ┌──────────┼──────────┐ ▼ ▼ ▼ 主库 从库1 从库2Docker部署# docker-compose.ymlversion:3services:shardingsphere-proxy:image:apache/shardingsphere-proxy:5.3.0container_name:shardingsphere-proxyports:-3307:3307environment:MODE_TYPE:StandaloneJVM_OPTS:-Xmx512m -Xms512mvolumes:-./conf:/opt/shardingsphere-proxy/confnetworks:-shardingspherenetworks:shardingsphere:driver:bridgeserver.yaml配置# conf/server.yamlschemaName:readwrite_splittingdataSources:ds_master:url:jdbc:mysql://192.168.1.100:3306/order_db?serverTimezoneUTCusername:rootpassword:passwordconnectionPoolClassName:com.zaxxer.hikari.HikariDataSourcemaxPoolSize:50minPoolSize:10ds_slave_0:url:jdbc:mysql://192.168.1.101:3306/order_db?serverTimezoneUTCusername:rootpassword:passwordconnectionPoolClassName:com.zaxxer.hikari.HikariDataSourcemaxPoolSize:50minPoolSize:10ds_slave_1:url:jdbc:mysql://192.168.1.102:3306/order_db?serverTimezoneUTCusername:rootpassword:passwordconnectionPoolClassName:com.zaxxer.hikari.HikariDataSourcemaxPoolSize:50minPoolSize:10rules:-!readwrite_splittingdataSources:prds:type:Staticprops:write-data-source-name:ds_masterread-data-source-names:ds_slave_0,ds_slave_1loadBalancerName:round_robinloadBalancers:round_robin:type:ROUND_ROBIN应用连接// 应用连接ShardingSphere-Proxy像连接普通MySQL一样Stringurljdbc:mysql://192.168.1.100:3307/readwrite_splitting;三、MyCat实战1. MyCat安装# 下载wgethttp://dl.mycat.org.cn/2.0/Mycat-server-2.0.0-release/tar-zxfMycat-server-2.0.0-release.tar.gz-C/usr/local/# 配置环境变量exportMYCAT_HOME/usr/local/mycatexportPATH$PATH:$MYCAT_HOME/bin# 启动mycat start# 查看状态mycat status2. server.xml配置!-- conf/server.xml --?xml version1.0 encodingUTF-8?!DOCTYPEmycat:serverSYSTEMserver.dtdmycat:serverxmlns:mycathttp://io.mycat/systempropertynamedefaultSqlParserdruidparser/propertypropertynameserverPort8066/propertypropertynamemanagerPort9066/property/systemusernamerootpropertynamepasswordpassword/propertypropertynameschemasorder_db/property/user/mycat:server3. schema.xml配置!-- conf/schema.xml --?xml version1.0 encodingUTF-8?!DOCTYPEmycat:schemaSYSTEMschema.dtdmycat:schemaxmlns:mycathttp://io.mycat/schemanameorder_dbcheckSQLschemafalsesqlMaxLimit100!-- 定义逻辑表可选 --tablenameorderprimaryKeyiddataNodedn1,dn2//schema!-- 数据节点 --dataNodenamedn1dataHostdh1databaseorder_db/dataNodenamedn2dataHostdh2databaseorder_db/!-- 数据主机读写分离 --dataHostnamedh1maxCon1000minCon10balance1writeType0dbTypemysqldbDrivernativeheartbeatselect user()/heartbeat!-- 写库 --writeHosthosthostM1url192.168.1.100:3306userrootpasswordpassword!-- 读库 --readHosthosthostS1url192.168.1.101:3306userrootpasswordpassword/readHosthosthostS2url192.168.1.102:3306userrootpasswordpassword//writeHost/dataHost/mycat:schema4. balance负载均衡策略策略说明适用场景0所有读操作发送到writeHost不推荐可能读到旧数据1读操作分发到所有readHost常用读写分离2随机分发到readHost不推荐3只分发到readHost无可用则writeHost读写分离高可用推荐配置dataHostnamedh1balance1writeType0!-- balance1读操作分发到所有readHost --/dataHost四、ShardingSphere vs MyCat对比维度ShardingSphereMyCat架构JDBC层轻量独立服务重量部署嵌入应用/独立Proxy独立部署性能较高略低维护社区活跃社区相对沉寂分库分表支持支持读写分离支持支持学习成本中较高配置复杂度中高五、读写分离最佳实践1. 什么时候用读写分离适合场景读多写少读:写 7:3单机数据库成为瓶颈数据量适中单表1000万不适合场景写多读少强一致性要求读写分离有延迟单表数据量过亿需要分库分表2. 读写延迟处理方案1强制路由到主库// ShardingSpheretry(HintManagerhintManagerHintManager.getInstance()){hintManager.setMasterRouteOnly();returnorderMapper.selectById(id);}方案2应用层判断publicOrdergetOrderAfterCreate(LongorderId){// 创建订单后等待一小段时间让主从同步完成// 然后可以走从库OrderorderorderMapper.selectById(orderId);// 如果是刚创建的从主库读if(order.getCreateTime().isAfter(LocalDateTime.now().minusSeconds(5))){returngetOrderFromMaster(orderId);}returnorder;}3. 监控配置spring:shardingsphere:rules:readwrite-splitting:data-sources:ds_0:type:Staticload-balancer:type:ROUND_ROBINprops:# 开启SQL日志sql-show:true-- MyCat监控mysql-h192.168.1.100-P9066-u root-ppassword-eshow datanode;mysql-h192.168.1.100-P9066-u root-ppassword-eshow heartbeat;六、总结读写分离是提升数据库读性能的有效手段ShardingSphere轻量级嵌入应用易于使用MyCat独立服务功能强大但配置复杂强制主库路由解决读写延迟问题监控告警确保复制健康选型建议新项目推荐ShardingSphere需要分库分表优先ShardingSphere遗留系统改造可选MyCat思考题你们用的什么读写分离方案有没有遇到过主从延迟的问题个人观点仅供参考