Sharding-JDBC 实现读写分离

桃扇骨 2023-01-11 03:46 103阅读 0赞

Sharding-JDBC 是 apache 旗下的 ShardingSphere 中的一款产品，轻量，引入 jar 即可完成读写分离的需求，可以理解为增强版的 JDBC，现在被使用的较多。

搭建项目

**maven 依赖的库**

<dependency>
      <groupId>org.apache.shardingsphere</groupId>
      <artifactId>sharding-jdbc-core</artifactId>
      <version>4.1.1</version>
    </dependency>
    
    <dependency>
      <groupId>com.zaxxer</groupId>
      <artifactId>HikariCP</artifactId>
      <version>3.4.5</version>
    </dependency>
    
    <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学习资源】整理推荐 ###

*  [Druid 使用][Druid]
 *  [借助 libreoffice，word 转 pdf 体验][libreoffice_word _ pdf]
 *  [Java 动态代理][Java]
 *  [openjdk jol 工具打印 Java 对象内存布局][openjdk jol _ Java]
 *  [Spring整合MyBatis][Spring_MyBatis]
 *  [MyBatis 自定义插件][MyBatis]
 *  [MyBatis 延迟加载][MyBatis 1]
 *  [MyBatis 1对1 1对多 关联查询][MyBatis 1_1 1_]
 *  [MyBatis insert 获取自增主键][MyBatis insert]
 *  [MyBatis 批量插入][MyBatis 2]
 *  [分页插件 Mybatis-PageHelper][Mybatis-PageHelper]
 *  [MyBatis 原生使用][MyBatis 3]
 *  [Sharding-JDBC 实现分库分表][Sharding-JDBC]
 *  [JDK1.8 中的日期与时间 API][JDK1.8 _ API]
 *  [Sharding-JDBC 实现读写分离][Sharding-JDBC 1]

--------------------

### 【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/20221119/9257fdb5c6064bddb74dad23b4ebcb01.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
[Druid]: https://www.javanav.com/val/79860880b84442cfb0f6f5e094d53105.html
[libreoffice_word _ pdf]: https://www.javanav.com/val/501ababe5c48493780ed23dbbc7b2cbc.html
[Java]: https://www.javanav.com/val/ab11038d7de64b4b8c0e384d1d4a9ab0.html
[openjdk jol _ Java]: https://www.javanav.com/val/66e564087e244b2f99f85091de029605.html
[Spring_MyBatis]: https://www.javanav.com/val/687c224b31a34d3c9f99fee67e3d5bcc.html
[MyBatis]: https://www.javanav.com/val/a5535343f9b545eda9665f03d62345ba.html
[MyBatis 1]: https://www.javanav.com/val/973ded541e9244aa8b3169b9fb869d60.html
[MyBatis 1_1 1_]: https://www.javanav.com/val/a9fe4555c1614b40b0da07afeabf2a66.html
[MyBatis insert]: https://www.javanav.com/val/3ac331d2674b4c108469cce54ae126f3.html
[MyBatis 2]: https://www.javanav.com/val/2d21b1463f2e4faeaf0def0c49df34a4.html
[Mybatis-PageHelper]: https://www.javanav.com/val/a8a8009f878d47dc9167bf20b9e0ab57.html
[MyBatis 3]: https://www.javanav.com/val/07f60d7946204b3b893abd5dc8bd2fa3.html
[Sharding-JDBC]: https://www.javanav.com/val/eefddd846ae845668daf9d798755c057.html
[JDK1.8 _ API]: https://www.javanav.com/val/fefecf2f089e48b294708e99954c57a1.html
[Sharding-JDBC 1]: 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