Sharding-JDBC 实现读写分离 迈不过友情╰ 2022-12-09 04:55 139阅读 0赞 Sharding-JDBC 是 apache 旗下的 ShardingSphere 中的一款产品,轻量,引入 jar 即可完成读写分离的需求,可以理解为增强版的 JDBC,现在被使用的较多。 搭建项目 **maven 依赖的库** <!-- 当前最新版 sharding-jdbc --> <dependency> <groupId>org.apache.shardingsphere</groupId> <artifactId>sharding-jdbc-core</artifactId> <version>4.1.1</version> </dependency> <!-- 结合官方文档使用了 HikariCP 数据库连接池 --> <dependency> <groupId>com.zaxxer</groupId> <artifactId>HikariCP</artifactId> <version>3.4.5</version> </dependency> <!-- MySQL 8.0.21 驱动 --> <dependency> <groupId>mysql</groupId> <artifactId>mysql-connector-java</artifactId> <version>8.0.21</version> </dependency> **获取数据源的工具类** package constxiong; import com.zaxxer.hikari.HikariDataSource; /** * 获取 DataSource 工具类,使用了 Hikari 数据库连接池 */ import javax.sql.DataSource; public final class DataSourceUtil { private static final int PORT = 3306; /** * 通过 Hikari 数据库连接池创建 DataSource * @param ip * @param username * @param password * @param dataSourceName * @return */ public static DataSource createDataSource(String ip, String username, String password, String dataSourceName) { HikariDataSource result = new HikariDataSource(); result.setDriverClassName(com.mysql.jdbc.Driver.class.getName()); result.setJdbcUrl(String.format("jdbc:mysql://%s:%s/%s?serverTimezone=UTC&useSSL=false&useUnicode=true&characterEncoding=UTF-8", ip, PORT, dataSourceName)); result.setUsername(username); result.setPassword(password); return result; } } 测试 Sharding-JDBC 读写分离 主库:172.31.32.184 从库:172.31.32.234 **观察通过 Sharding-JDBC 获取的 DataSource 是否会自动写入到主库,从库是否主动同步,从库同步数据的延迟时间** **测试代码** package constxiong; import org.apache.shardingsphere.api.config.masterslave.MasterSlaveRuleConfiguration; import org.apache.shardingsphere.shardingjdbc.api.MasterSlaveDataSourceFactory; import javax.sql.DataSource; import java.sql.Connection; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Statement; import java.time.LocalTime; import java.util.*; /** * 测试 ShardingSphere 读写分离 * 主库:172.31.32.184 * 从库:172.31.32.234 * * 观察通过 ShardingSphere 获取的 DataSource 是否会自动写入到主库,从库是否主动同步,从库同步数据的延迟时间 */ public class Test { //主库 DataSource private static DataSource dsSlave = DataSourceUtil.createDataSource("172.31.32.234", "root", "constxiong@123", "constxiong"); //从库 DataSource private static DataSource dsMaster = DataSourceUtil.createDataSource("172.31.32.184", "root", "constxiong@123", "constxiong"); public static void main(String[] args) throws SQLException { //启动线程打印主库与从库当前 cuser 数据量与时间,观察从库同步数据延迟 printMasterAndSlaveData(); //从 ShardingSphere 获取 DataSource,出入数据,观察插入数据的库是否为主库 DataSource ds = getMasterSlaveDataSource(); Connection conn = ds.getConnection(); Statement stt = conn.createStatement(); stt.execute("insert into cuser values(2, 'fj')"); } /** * 启动线程打印,两个主从库 cuser 表的信息、数据量、当前时间 * @throws SQLException */ private static void printMasterAndSlaveData() throws SQLException { Connection masterConn = dsMaster.getConnection(); Connection slaveConn = dsSlave.getConnection(); new Thread(() -> { while (true) { try { System.out.println("------master------" + LocalTime.now()); print(masterConn); System.out.println("------slave------" + LocalTime.now()); print(slaveConn); } catch (SQLException e) { } } }).start(); } private static void print(Connection conn) throws SQLException { Statement statement = conn.createStatement(); statement.execute("select * from cuser"); ResultSet rs = statement.getResultSet(); int count = 0; while (rs.next()) { int id = rs.getInt("id"); String name = rs.getString("name"); System.out.println(id + "-" + name); count++; } System.out.println("total: " + count); } /** * 设置 ShardingSphere 的主从库 * @return * @throws SQLException */ private static DataSource getMasterSlaveDataSource() throws SQLException { MasterSlaveRuleConfiguration masterSlaveRuleConfig = new MasterSlaveRuleConfiguration("ds_master_slave", "ds_master", Arrays.asList("ds_slave")); return MasterSlaveDataSourceFactory.createDataSource(createDataSourceMap(), masterSlaveRuleConfig, new Properties()); } /** * 用 主从库的 DataSource 构造 map * @return */ private static Map<String, DataSource> createDataSourceMap() { Map<String, DataSource> result = new HashMap<>(); result.put("ds_master", dsMaster); result.put("ds_slave", dsSlave); return result; } } **分析延迟信息** 数据默认配置的情况,在内网从库同步的时间延迟,在 200ms 左右,当然这个统计是不精确的,只是看个大概情况,**理论值应该是可以做毫秒级**。 ![886c6ea172dae5776eaac3b609570651.png][] 参考文档: * [https://shardingsphere.apache.org/document/legacy/4.x/document/en/manual/sharding-jdbc/configuration/config-java/\#read-write-split][https_shardingsphere.apache.org_document_legacy_4.x_document_en_manual_sharding-jdbc_configuration_config-java_read-write-split] * [https://shardingsphere.apache.org/document/legacy/4.x/document/en/manual/sharding-jdbc/usage/read-write-splitting/][https_shardingsphere.apache.org_document_legacy_4.x_document_en_manual_sharding-jdbc_usage_read-write-splitting] * [http://shardingsphere.apache.org/index\_zh.html][http_shardingsphere.apache.org_index_zh.html] 代码上传至: * [https://github.com/ConstXiong/toy/tree/master/demo/shardingsphere-read-write-split][https_github.com_ConstXiong_toy_tree_master_demo_shardingsphere-read-write-split] -------------------- ### 【Java学习资源】整理推荐 ### * [JDK1.8 中的日期与时间 API][JDK1.8 _ API] * [Sharding-JDBC 实现读写分离][Sharding-JDBC] -------------------- ### 【Java面试题与答案】整理推荐 ### * [基础与语法][Link 1] * [集合][Link 2] * [网络编程][Link 3] * [并发编程][Link 4] * [Web][] * [安全][Link 5] * [设计模式][Link 6] * [框架][Link 7] * [算法与数据结构][Link 8] * [异常][Link 9] * [文件解析与生成][Link 10] * [Linux][] * [MySQL][] * [Oracle][] * [Redis][] * [Dubbo][] [886c6ea172dae5776eaac3b609570651.png]: /images/20221123/bf41944c564943968a4dfe14a4aa5674.png [https_shardingsphere.apache.org_document_legacy_4.x_document_en_manual_sharding-jdbc_configuration_config-java_read-write-split]: https://shardingsphere.apache.org/document/legacy/4.x/document/en/manual/sharding-jdbc/configuration/config-java/#read-write-split [https_shardingsphere.apache.org_document_legacy_4.x_document_en_manual_sharding-jdbc_usage_read-write-splitting]: https://shardingsphere.apache.org/document/legacy/4.x/document/en/manual/sharding-jdbc/usage/read-write-splitting/ [http_shardingsphere.apache.org_index_zh.html]: http://shardingsphere.apache.org/index_zh.html [https_github.com_ConstXiong_toy_tree_master_demo_shardingsphere-read-write-split]: https://github.com/ConstXiong/toy/tree/master/demo/shardingsphere-read-write-split [JDK1.8 _ API]: https://www.javanav.com/val/fefecf2f089e48b294708e99954c57a1.html [Sharding-JDBC]: https://www.javanav.com/val/f05fe12cb8fd444aaa59fb7f747a3224.html [Link 1]: https://www.javanav.com/interview/93b0069472fd479393006c0e73043fc4.html [Link 2]: https://www.javanav.com/interview/4147d95e693945e7a50345fd33ec817e.html [Link 3]: https://www.javanav.com/interview/68a57ffc78de4133bb890bdd502ac418.html [Link 4]: https://www.javanav.com/interview/36a24fa3258e45c89873f474ce914912.html [Web]: https://www.javanav.com/interview/4e27cb63adb440c6825a159d35cf4010.html [Link 5]: https://www.javanav.com/interview/03affb2ce3f04fda8853f714e5764906.html [Link 6]: https://www.javanav.com/interview/07a32a9ff17a4006a53550ce658cafed.html [Link 7]: https://www.javanav.com/interview/5c466456e9b54ab0a749b5948e51f1d3.html [Link 8]: https://www.javanav.com/interview/943f917041ee4f05add24539f5b861b2.html [Link 9]: https://www.javanav.com/interview/d3f058cfc05d4c6dadd821b622e84d36.html [Link 10]: https://www.javanav.com/interview/67a50996c28f418e83b109ea42fc3eff.html [Linux]: https://www.javanav.com/interview/00cccb81729540d1923bd0a0ef49533a.html [MySQL]: https://www.javanav.com/interview/d311e78502a845f1a4bb307bd6aec2b3.html [Oracle]: https://www.javanav.com/interview/5f29e9677edf4a16be3dce30d257c9ab.html [Redis]: https://www.javanav.com/interview/066c62b3af4d4827b2243a822c3084a9.html [Dubbo]: https://www.javanav.com/interview/9a3e7a7740324757abaab953f7ebc455.html
还没有评论,来说两句吧...