专辑：分库分表ShardingSphere_读写分离+分片-Java充电社【公众号：Java充电社】

## 10.1、背景

面对日益增加的系统访问量，数据库的吞吐量面临着巨大瓶颈。 对于同一时刻有大量并发读操作和较少写操作类型的应用系统来说，将数据库拆分为主库和从库，主库负责处理事务性的增删改操作，从库负责处理查询操作，能够有效的避免由数据更新导致的行锁，使得整个系统的查询性能得到极大的改善。

通过一主多从的配置方式，可以将查询请求均匀的分散到多个数据副本，能够进一步的提升系统的处理能力。 使用多主多从的方式，不但能够提升系统的吞吐量，还能够提升系统的可用性，可以达到在任何一个数据库宕机，甚至磁盘物理损坏的情况下仍然不影响系统的正常运行。

与将数据根据分片键打散至各个数据节点的水平分片不同，读写分离则是根据SQL语义的分析，将读操作和写操作分别路由至主库与从库。

![](https://itsoku.oss-cn-hangzhou.aliyuncs.com/itsoku/blog/article/414/0851a025-2d77-40c2-8a6e-3cc631f403e7.png)

读写分离的数据节点中的数据内容是一致的，而水平分片的每个数据节点的数据内容却并不相同。将水平分片和读写分离联合使用，能够更加有效的提升系统性能。

读写分离虽然可以提升系统的吞吐量和可用性，但同时也带来了数据不一致的问题。 这包括多个主库之间的数据一致性，以及主库与从库之间的数据一致性的问题。 并且，读写分离也带来了与数据分片同样的问题，它同样会使得应用开发和运维人员对数据库的操作和运维变得更加复杂。 下图展现了将分库分表与读写分离一同使用时，应用程序与数据库集群之间的复杂拓扑关系。

![](https://itsoku.oss-cn-hangzhou.aliyuncs.com/itsoku/blog/article/414/50d0b80e-f1e2-4006-aca1-62c86982df7d.png)

## 10.2、案例：实现2主2从+分片

### 1）需求

- 2个主库：ds_master_0，ds_master_1
- 2个从库：ds_slave_0，ds_slave_1，分别是上面2个主库的从库
- 4个库中都有一个t_user表(id,name)
- ds_master_0和其从库ds_slave_0存放t_user表id为偶数的数据，ds_master_1和其从库ds_slave_1存放t_user表id为基数的数据
- 稍后会演示常见的使用场景：写入数据的场景、无事务读取数据的场景、有事务读取的场景、强制将读路由到主库的场景，大家一定要注意观察sql路由情况
- 本案例不介绍主从如何同步，需要了解的朋友自行查找相关资料

### 2）准备sql

```sql
drop database if exists ds_master_0;
create database ds_master_0;
drop database if exists ds_master_1;
create database ds_master_1;
drop database if exists ds_slave_0;
create database ds_slave_0;
drop database if exists ds_slave_1;
create database ds_slave_1;

use ds_master_0;
drop table if exists t_user;
create table t_user(
	id bigint not null primary key,
	name varchar(64) not null
);
insert into t_user VALUES (1,'我是ds_master_0');

use ds_master_1;
drop table if exists t_user;
create table t_user(
	id bigint not null primary key,
	name varchar(64) not null
);
insert into t_user VALUES (2,'我是ds_master_1');

use ds_slave_0;
drop table if exists t_user;
create table t_user(
	id bigint not null primary key,
	name varchar(64) not null
);
insert into t_user VALUES (1,'我是ds_slave_0');

use ds_slave_1;
drop table if exists t_user;
create table t_user(
	id bigint not null primary key,
	name varchar(64) not null
);
insert into t_user VALUES (2,'我是ds_slave_1');
```

### 3）创建测试类

```java
public class MasterSlaveTests {

private static DataSource dataSource;

@BeforeAll
    public static void init() throws SQLException {
        /**
         * 1、配置真实数据源
         */
        HikariDataSource ds_master_0 = new HikariDataSource();
        ds_master_0.setDriverClassName("com.mysql.jdbc.Driver");
        ds_master_0.setJdbcUrl("jdbc:mysql://localhost:3306/ds_master_0?characterEncoding=UTF-8");
        ds_master_0.setUsername("root");
        ds_master_0.setPassword("root123");

HikariDataSource ds_slave_0 = new HikariDataSource();
        ds_slave_0.setDriverClassName("com.mysql.jdbc.Driver");
        ds_slave_0.setJdbcUrl("jdbc:mysql://localhost:3306/ds_slave_0?characterEncoding=UTF-8");
        ds_slave_0.setUsername("root");
        ds_slave_0.setPassword("root123");

HikariDataSource ds_master_1 = new HikariDataSource();
        ds_master_1.setDriverClassName("com.mysql.jdbc.Driver");
        ds_master_1.setJdbcUrl("jdbc:mysql://localhost:3306/ds_master_1?characterEncoding=UTF-8");
        ds_master_1.setUsername("root");
        ds_master_1.setPassword("root123");

HikariDataSource ds_slave_1 = new HikariDataSource();
        ds_slave_1.setDriverClassName("com.mysql.jdbc.Driver");
        ds_slave_1.setJdbcUrl("jdbc:mysql://localhost:3306/ds_slave_1?characterEncoding=UTF-8");
        ds_slave_1.setUsername("root");
        ds_slave_1.setPassword("root123");

// 将4个数据源加入 dataSourceMap
        Map<String, DataSource> dataSourceMap = new HashMap<>();
        dataSourceMap.put("ds_master_0", ds_master_0);
        dataSourceMap.put("ds_slave_0", ds_slave_0);
        dataSourceMap.put("ds_master_1", ds_master_1);
        dataSourceMap.put("ds_slave_1", ds_slave_1);

// 主从规则配置，就是配置主从关系，让系统知道哪个库是主库、他的从库列表是哪些？
        MasterSlaveRuleConfiguration master0SlaveRuleConfig =
                new MasterSlaveRuleConfiguration(
                        "ds0",
                        "ds_master_0",  //dataSourceMap中主库的key
                        Arrays.asList("ds_slave_0")); // dataSourceMap中ds_master_0从库的key
        // 配置读写分离规则
        MasterSlaveRuleConfiguration master1SlaveRuleConfig =
                new MasterSlaveRuleConfiguration(
                        "ds1",
                        "ds_master_1",  //dataSourceMap中主库的key
                        Arrays.asList("ds_slave_1")); // dataSourceMap中ds_master_1从库的key

/**
         * 2、配置t_user分片规则
         */
        TableRuleConfiguration userTableRuleConfiguration =
                new TableRuleConfiguration("t_user", "ds$->{0..1}.t_user");
        //设置t_user表的分库规则
        final InlineShardingStrategyConfiguration userTableShardingStrategy =
                new InlineShardingStrategyConfiguration("id", "ds$->{(id+1) % 2}");
        userTableRuleConfiguration.setDatabaseShardingStrategyConfig(userTableShardingStrategy);

/**
         * 3、创建分片配置对象ShardingRuleConfiguration
         */
        ShardingRuleConfiguration shardingRuleConfig = new ShardingRuleConfiguration();
        //将userTableRuleConfiguration放入表规则配置列表
        shardingRuleConfig.getTableRuleConfigs().add(userTableRuleConfiguration);
        //设置主从规则配置
        shardingRuleConfig.setMasterSlaveRuleConfigs(Arrays.asList(master0SlaveRuleConfig, master1SlaveRuleConfig));

/**
         * 4、配置一些属性
         */
        Properties props = new Properties();
        //输出sql
        props.put(ConfigurationPropertyKey.SQL_SHOW.getKey(), true);

/**
         * 5、创建数据源
         */
        dataSource = ShardingDataSourceFactory.createDataSource(dataSourceMap, shardingRuleConfig, props);
    }

}
```

### 4）关键代码

下图配置了4个数据源，以及配置了数据源之间的主从关系，要让shardingsphere知道他们的主从关系

![](https://itsoku.oss-cn-hangzhou.aliyuncs.com/itsoku/blog/article/414/fb4468f8-dfed-4392-bb73-354162fb8d43.png)

下图中配置了表的分片规则

![](https://itsoku.oss-cn-hangzhou.aliyuncs.com/itsoku/blog/article/414/9abbe4f4-3ac7-4d71-81fd-ce42cff63757.png)

还有一行关键代码，如下图

![](https://itsoku.oss-cn-hangzhou.aliyuncs.com/itsoku/blog/article/414/30dcd6e7-6b1f-4ec3-a20b-2da5877ae37e.png)

下面上测试案例，案例都位于上面这个类中，一定要注意看案例了。

### 5）案例1：无事务读取落入从库

```java
/**
 * 无事务查询
 *
 * @throws SQLException
 */
@Test
public void test1() throws SQLException {
    String sql = "select id,name from t_user where id = 1";
    try (Connection connection = dataSource.getConnection();
         PreparedStatement ps = connection.prepareStatement(sql);
         ResultSet rs = ps.executeQuery();) {
        while (rs.next()) {
            final long id = rs.getLong("id");
            final String name = rs.getString("name");
            System.out.println(String.format("id:%s,name:%s", id, name));
        }
    }
}
```

运行输出，如下

```sql
Logic SQL: select id,name from t_user where id = 1
Actual SQL: ds_slave_0 ::: select id,name from t_user where id = 1
id:1,name:我是ds_slave_0
```

**结论：**无事务查询，会落入从库。

### 6）案例2：事务中直接读取落入从库

> 下面将连接设置为手动提交，然后读取id为2的记录

```java
/**
 * 有事务查询
 *
 * @throws SQLException
 */
@Test
public void test2() throws SQLException {
    try (Connection connection = dataSource.getConnection();) {
        //手动开启事务
        connection.setAutoCommit(false);
        String sql = "select id,name from t_user where id = 2";
        PreparedStatement ps = connection.prepareStatement(sql);
        ResultSet rs = ps.executeQuery();
        while (rs.next()) {
            final long id = rs.getLong("id");
            final String name = rs.getString("name");
            System.out.println(String.format("id:%s,name:%s", id, name));
        }
        connection.commit();
    }
}
```

运行输出，如下，落入从库ds_slave_1了

```sql
Logic SQL: select id,name from t_user where id = 2
Actual SQL: ds_slave_1 ::: select id,name from t_user where id = 2
id:2,name:我是ds_slave_1
```

**结论：**开启事务，直接读取数据，会落入从库。

### 7）案例3：事务中写入之后读取落入主库

> 开启事务，然后写入id为3的数据，然后读取这条数据，然后读取已经存在的一条id为2的数据，看看效果

```java
/**
 * 有事务，写入数据，然后查询，（写入 & 查询都落入主库）
 *
 * @throws SQLException
 */
@Test
public void test3() throws SQLException {
    try (Connection connection = dataSource.getConnection();) {
        connection.setAutoCommit(false);
        System.out.println("-----------插入id为3数据-----------");
        String sql = "insert into t_user values (3,'张三')";
        PreparedStatement ps = connection.prepareStatement(sql);
        ps.executeUpdate();

System.out.println("-----------查询刚插入的数据-----------");
        sql = "select id,name from t_user where id = 3";
        ps = connection.prepareStatement(sql);
        ResultSet rs = ps.executeQuery();
        while (rs.next()) {
            final long id = rs.getLong("id");
            final String name = rs.getString("name");
            System.out.println(String.format("id:%s,name:%s", id, name));
        }

System.out.println("上面id为3的在t_master_0,下面来看看读取id为2的，看看会读取主库还是从库？");
        System.out.println("-----------查询id为2的数据-----------");

sql = "select id,name from t_user where id = 2";
        ps = connection.prepareStatement(sql);
        rs = ps.executeQuery();
        while (rs.next()) {
            final long id = rs.getLong("id");
            final String name = rs.getString("name");
            System.out.println(String.format("id:%s,name:%s", id, name));
        }
        connection.commit();
    }
}
```

运行输出，结果如下，3个sql都落入了主库。

```sql
-----------插入id为3数据-----------
Logic SQL: insert into t_user values (3,'张三')
Actual SQL: ds_master_0 ::: insert into t_user values (3, '张三')

-----------查询刚插入的数据-----------
Logic SQL: select id,name from t_user where id = 3
Actual SQL: ds_master_0 ::: select id,name from t_user where id = 3
id:3,name:张三

上面id为3的在t_master_0,下面来看看读取id为2的，看看会读取主库还是从库？
-----------查询id为2的数据-----------
Logic SQL: select id,name from t_user where id = 2
Actual SQL: ds_master_1 ::: select id,name from t_user where id = 2
id:2,name:我是ds_master_1
```

**结论：只要开启了手动事务，且第一个sql为insert，后面的不管路由到哪个库，都会落入主库**

### 8）案例4：通过HintManager强制查询走主库

> 可以通过hintManager.setMasterRouteOnly()强制走主库，代码如下

```java
/**
 * 通过hintManager.setMasterRouteOnly()强制走主库
 *
 * @throws SQLException
 */
@Test
public void test4() throws SQLException {
    String sql = "select id,name from t_user where id = 1";
    try (Connection connection = dataSource.getConnection();
         PreparedStatement ps = connection.prepareStatement(sql);) {

HintManager hintManager = null;
        try {
            //通过HintManager.setMasterRouteOnly()强制走主库，注意在finally中释放HintManager.close();
            hintManager = HintManager.getInstance();
            hintManager.setMasterRouteOnly();

ResultSet rs = ps.executeQuery();
            while (rs.next()) {
                final long id = rs.getLong("id");
                final String name = rs.getString("name");
                System.out.println(String.format("id:%s,name:%s", id, name));
            }
        } finally {
            if (hintManager != null) {
                hintManager.close();
            }
        }
    }
}
```

运行输出，如下，走主库了

```sql
Logic SQL: select id,name from t_user where id = 1
Actual SQL: ds_master_0 ::: select id,name from t_user where id = 1
id:1,name:我是ds_master_0
```

<a style="display:none" target="_blank" href="https://mp.weixin.qq.com/s/_S1DD2JADnXvpexxaBwLLg" style="color:red; font-size:20px; font-weight:bold">继续收门徒，亲手带，月薪 4W 以下的可以来找我</a>

## 最新资料

1. <a href="https://mp.weixin.qq.com/s?__biz=MzkzOTI3Nzc0Mg==&mid=2247484964&idx=2&sn=c81bce2f26015ee0f9632ddc6c67df03&scene=21#wechat_redirect" target="_blank">尚硅谷 Java 学科全套教程（总 207.77GB）</a>
2. <a href="https://mp.weixin.qq.com/s?__biz=MzkwOTAyMTY2NA==&mid=2247484192&idx=1&sn=505f2faaa4cc911f553850667749bcbb&scene=21#wechat_redirect" target="_blank">2021 最新版 Java 微服务学习线路图 + 视频</a>
3. <a href="https://mp.weixin.qq.com/s?__biz=MzkwOTAyMTY2NA==&mid=2247484573&idx=1&sn=7f3d83892186c16c57bc0b99f03f1ffd&scene=21#wechat_redirect" target="_blank">阿里技术大佬整理的《Spring 学习笔记.pdf》</a>
4. <a href="https://mp.weixin.qq.com/s?__biz=MzkwOTAyMTY2NA==&mid=2247484544&idx=2&sn=c1dfe907cfaa5b9ae8e66fc247ccbe84&scene=21#wechat_redirect" target="_blank">阿里大佬的《MySQL 学习笔记高清.pdf》</a>
5. <a href="https://mp.weixin.qq.com/s?__biz=MzkwOTAyMTY2NA==&mid=2247485167&idx=1&sn=48d75c8e93e748235a3547f34921dfb7&scene=21#wechat_redirect" target="_blank">2021 版 java 高并发常见面试题汇总.pdf</a>
6. <a href="https://mp.weixin.qq.com/s?__biz=MzkwOTAyMTY2NA==&mid=2247485664&idx=1&sn=435f9f515a8f881642820d7790ad20ce&scene=21#wechat_redirect" target="_blank">Idea 快捷键大全.pdf</a>

![](https://itsoku.oss-cn-hangzhou.aliyuncs.com/itsoku/blog/article/1/2883e86e-3eff-404a-8943-0066e5e2b454.png)