來源：https://www.cnblogs.com/wingsless/p/11406481.html
標(biāo)題：利用ShardingSphere-JDBC實(shí)現(xiàn)分庫(kù)分表
作者：wingsless

利用ShardingSphere-JDBC實(shí)現(xiàn)分庫(kù)分表

1. ShardingSphere概述

1.1 概述

業(yè)務(wù)發(fā)展到一定程度，分庫(kù)分表是一種必然的要求，分庫(kù)可以實(shí)現(xiàn)資源隔離筷黔，分表則可以降低單表數(shù)據(jù)量业汰，提高訪問效率薯鼠。

分庫(kù)分表的技術(shù)方案叙甸，很久以來都有兩種理念：

• 集中式的Proxy秧耗，實(shí)現(xiàn)MySQL客戶端協(xié)議番官，使用戶無感知

• 分布式的Proxy童芹，在代碼層面進(jìn)行增強(qiáng)，實(shí)現(xiàn)一個(gè)路由程序

這兩種方式是各有利弊的鲤拿，集中式Proxy的好處是業(yè)務(wù)沒有感知假褪，一切交給DBA把控，分布式的Proxy其支持的語言有限近顷，比如本文要提及的ShardingShpere-
JDBC就只支持Java生音。

我們需要了解一點(diǎn)宁否，集中式的Proxy其實(shí)現(xiàn)非常復(fù)雜，這要從MySQL處理SQL語句的原理說起缀遍，因?yàn)椴皇潜疚囊撌龅闹攸c(diǎn)慕匠，因此只是簡(jiǎn)單的提及幾點(diǎn)：

1. SQL語句要被Parser解析成抽象語法樹

2. SQL要被優(yōu)化器解析出執(zhí)行計(jì)劃

3. SQL語句完成解析后，發(fā)給存儲(chǔ)引擎

因此大部分的中間件都選擇了自己實(shí)現(xiàn)SQL的解析器和查詢優(yōu)化器域醇，下面是著名的中間件dble的實(shí)現(xiàn)示意圖：

image

只要有解析的過程台谊，其性能損耗就是比較可觀的，我們也可以認(rèn)為這是一種重量級(jí)的解決方案譬挚。

與之形成對(duì)比的是ShardingSphere-JDBC锅铅，其原理示意圖如下：

image

每一個(gè)服務(wù)都持有一個(gè)Sharing-
JDBC，這個(gè)JDBC以Jar包的形式提供减宣，基本上可以認(rèn)為是一個(gè)增強(qiáng)版的jdbc驅(qū)動(dòng)盐须，需要一些分庫(kù)分表的配置，業(yè)務(wù)開發(fā)人員不需要去對(duì)代碼進(jìn)行任何的修改漆腌≡舻耍可以很輕松的移植到SpringBoot，ORM等框架上闷尿。

但是這個(gè)結(jié)構(gòu)也不是完美的塑径，每一個(gè)服務(wù)持有一個(gè)proxy意味著會(huì)在MySQL服務(wù)端新建大量的連接，維持連接會(huì)增加MySQL服務(wù)器的負(fù)載填具，雖然這種負(fù)載提升一般無法察覺晓勇。

1.2 概念

邏輯表

即水平拆分的表的總稱。比如訂單業(yè)務(wù)會(huì)被拆分成t_order0灌旧，t_order1兩張表，但是他們同屬于一個(gè)邏輯表：t_order

綁定表

分片規(guī)則一直的主表和子表绰筛。比如還是上面的t_order表枢泰，其分片鍵是order_id，其子表t_order_item的分片鍵也是order_id铝噩。在規(guī)則配置時(shí)將兩個(gè)表配置成綁定關(guān)系衡蚂，就不會(huì)在查詢時(shí)出現(xiàn)笛卡爾積。

在關(guān)聯(lián)查詢時(shí)骏庸，如果沒有綁定關(guān)系毛甲，則t_order和t_order_item的關(guān)聯(lián)會(huì)出現(xiàn)這樣一種場(chǎng)景：

    select * from t_order0 inner join t_order_item0 on order_id = order_id where order_id in (0, 1);
    select * from t_order0 inner join t_order_item1 on order_id = order_id where order_id in (0, 1;
    select * from t_order1 inner join t_order_item0 on order_id = order_id where order_id in (0, 1;
    select * from t_order1 inner join t_order_item1 on order_id = order_id where order_id in (0, 1;

如果配置了綁定關(guān)系，則會(huì)精確地定位到order_id所在的表具被，消除笛卡爾積玻募。

廣播表

有一些表是沒有分片的必要的，比如省份信息表一姿，全國(guó)也就30多條數(shù)據(jù)七咧，這種表在每一個(gè)節(jié)點(diǎn)上都是一樣的跃惫，這種表叫做廣播表。

2. 利用SpringBoot實(shí)現(xiàn)分庫(kù)分表

要分庫(kù)分表首先需要有不同的數(shù)據(jù)源艾栋，我們啟動(dòng)兩個(gè)mysqld進(jìn)行爆存，監(jiān)聽3306和3307兩個(gè)端口，以多實(shí)例的形式模擬多數(shù)據(jù)源蝗砾。

我們的分庫(kù)是以用戶ID為依據(jù)的先较，分表是以表本身的主鍵為依據(jù)的。下面是一張示意表：

    -- 注意悼粮，這是邏輯表闲勺，實(shí)際不存在
    create table t_order
    (
      order_id bigint not null auto_increment primary key,
      user_id bigint not null,
      name varchar(100)
    );

    CREATE TABLE `t_order_item` (
      `order_id` bigint(20) NOT NULL,
      `item` varchar(100) DEFAULT NULL,
      `user_id` bigint(20) NOT NULL,
      PRIMARY KEY (`order_id`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

我現(xiàn)在有兩個(gè)數(shù)據(jù)源，每個(gè)數(shù)據(jù)源上根據(jù)order_id分成2兩表矮锈，也就是說每個(gè)實(shí)例上都應(yīng)該有這兩張表：

    create table t_order0
    (
      order_id bigint not null auto_increment primary key,
      user_id bigint not null,
      name varchar(100)
    );

    create table t_order1
    (
      order_id bigint not null auto_increment primary key,
      user_id bigint not null,
      name varchar(100)
    );

    -- 這是廣播表霉翔，新建在其中一個(gè)節(jié)點(diǎn)上就可以
    CREATE TABLE `t_config` (
      `id` int(11) NOT NULL AUTO_INCREMENT,
      `user_id` bigint(20) DEFAULT NULL,
      `config` varchar(100) DEFAULT NULL,
      PRIMARY KEY (`id`)
    ) ENGINE=InnoDB;

    CREATE TABLE `t_order_item0` (
      `order_id` bigint(20) NOT NULL,
      `item` varchar(100) DEFAULT NULL,
      `user_id` bigint(20) NOT NULL,
      PRIMARY KEY (`order_id`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

    CREATE TABLE `t_order_item1` (
      `order_id` bigint(20) NOT NULL,
      `item` varchar(100) DEFAULT NULL,
      `user_id` bigint(20) NOT NULL,
      PRIMARY KEY (`order_id`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

利用SpringBoot技術(shù)可以很快的構(gòu)建一個(gè)RESTful的Web服務(wù)，下面是application.properties的內(nèi)容：

    # 這里要注冊(cè)所有的數(shù)據(jù)源
    spring.shardingsphere.datasource.names=ds0,ds1

    # 這是數(shù)據(jù)源0的配置
    spring.shardingsphere.datasource.ds0.type=com.zaxxer.hikari.HikariDataSource
    spring.shardingsphere.datasource.ds0.jdbc-url=jdbc:mysql://localhost:3306/test?serverTimezone=GMT%2B8
    spring.shardingsphere.datasource.ds0.driver-class-name=com.mysql.jdbc.Driver
    spring.shardingsphere.datasource.ds0.username=root
    spring.shardingsphere.datasource.ds0.password=

    # 這是數(shù)據(jù)源1的配置
    spring.shardingsphere.datasource.ds1.type=com.zaxxer.hikari.HikariDataSource
    spring.shardingsphere.datasource.ds1.jdbc-url=jdbc:mysql://localhost:3307/test?serverTimezone=GMT%2B8
    spring.shardingsphere.datasource.ds1.driver-class-name=com.mysql.jdbc.Driver
    spring.shardingsphere.datasource.ds1.username=root
    spring.shardingsphere.datasource.ds1.password=

    # 分庫(kù)策略
    # 分庫(kù)的列是user_id
    spring.shardingsphere.sharding.default-database-strategy.standard.sharding-column=user_id
    spring.shardingsphere.sharding.default-database-strategy.standard.precise-algorithm-class-name=com.sinosun.demo.sharding.PreciseShardingAlgorithmImpl

    # 分表策略
    spring.shardingsphere.sharding.tables.t_order.actual-data-nodes=ds$->{0..1}.t_order$->{0..1}
    spring.shardingsphere.sharding.tables.t_order.table-strategy.inline.sharding-column=order_id
    spring.shardingsphere.sharding.tables.t_order.table-strategy.inline.algorithm-expression=t_order$->{order_id % 2}
    spring.shardingsphere.sharding.tables.t_order.key-generator.column=order_id
    spring.shardingsphere.sharding.tables.t_order.key-generator.type=SNOWFLAKE

    spring.shardingsphere.sharding.tables.t_order_item.actual-data-nodes=ds$->{0..1}.t_order_item$->{0..1}
    spring.shardingsphere.sharding.tables.t_order_item.table-strategy.inline.sharding-column=order_id
    spring.shardingsphere.sharding.tables.t_order_item.table-strategy.inline.algorithm-expression=t_order_item$->{order_id % 2}

    spring.shardingsphere.sharding.binding-tables=t_order, t_order_item

    # 廣播表, 其主節(jié)點(diǎn)是ds0
    spring.shardingsphere.sharding.broadcast-tables=t_config
    spring.shardingsphere.sharding.tables.t_config.actual-data-nodes=ds$->{0}.t_config

    spring.jpa.show-sql=true
    server.address=10.1.20.96
    server.port=8080

這是buid.gradle內(nèi)容苞笨，只列舉ShardingSphere相關(guān)的：

    dependencies {
        compile group: 'org.apache.shardingsphere', name: 'sharding-jdbc-spring-boot-starter', version: '4.0.0-RC1'
        compile group: 'org.apache.shardingsphere', name: 'sharding-jdbc-spring-namespace', version: '4.0.0-RC1'
    }

下圖是工程的代碼結(jié)構(gòu)债朵，供參考：

image

現(xiàn)在開始列舉代碼：

Order.java：

    package com.example.demo.entity;


    import javax.persistence.Column;
    import javax.persistence.Entity;
    import javax.persistence.GeneratedValue;
    import javax.persistence.GenerationType;
    import javax.persistence.Id;
    import javax.persistence.Table;
    import java.util.StringJoiner;

    @Entity
    @Table(name = "t_order")
    public class Order {
        @Id
        @GeneratedValue(strategy = GenerationType.IDENTITY)
        private long orderId;

        @Column(name = "user_id")
        private long userId;

        @Column(name = "name")
        private String name;

        public long getOrderId() {
            return orderId;
        }

        public void setOrderId(long orderId) {
            this.orderId = orderId;
        }

        public String getName() {
            return name;
        }

        public void setName(String name) {
            this.name = name;
        }

        public long getUserId() {
            return userId;
        }

        public void setUserId(long userId) {
            this.userId = userId;
        }

        @Override
        public String toString() {
            return new StringJoiner(", ", Order.class.getSimpleName() + "[", "]")
                    .add("orderId=" + orderId)
                    .add("userId=" + userId)
                    .add("name='" + name + "'")
                    .toString();
        }
    }

OrderItem.java:

    package com.example.demo.entity;

    import com.google.common.base.MoreObjects;

    import javax.persistence.Column;
    import javax.persistence.Entity;
    import javax.persistence.Id;
    import javax.persistence.Table;

    @Entity
    @Table(name = "t_order_item")
    public class OrderItem {
        @Id
        @Column(name = "order_id")
        private long orderId;

        @Column(name = "user_id")
        private long userId;

        @Column(name = "item")
        private String item;

        public long getOrderId() {
            return orderId;
        }

        public void setOrderId(long orderId) {
            this.orderId = orderId;
        }

        public long getUserId() {
            return userId;
        }

        public void setUserId(long userId) {
            this.userId = userId;
        }

        public String getItem() {
            return item;
        }

        public void setItem(String item) {
            this.item = item;
        }

        @Override
        public String toString() {
            return MoreObjects.toStringHelper(this)
                    .add("orderId", orderId)
                    .add("userId", userId)
                    .add("item", item)
                    .toString();
        }
    }

TConfig.java:

    package com.example.demo.entity;

    import com.google.common.base.MoreObjects;

    import javax.persistence.Column;
    import javax.persistence.Entity;
    import javax.persistence.GeneratedValue;
    import javax.persistence.GenerationType;
    import javax.persistence.Id;
    import javax.persistence.Table;

    @Entity
    @Table(name = "t_config")
    public class TConfig {
        @Id
        @GeneratedValue(strategy = GenerationType.IDENTITY)
        private int id;

        @Column(name = "user_id")
        private long userId;

        @Column(name = "config")
        private String config;

        public int getId() {
            return id;
        }

        public void setId(int id) {
            this.id = id;
        }

        public long getUserId() {
            return userId;
        }

        public void setUserId(long userId) {
            this.userId = userId;
        }

        public String getConfig() {
            return config;
        }

        public void setConfig(String config) {
            this.config = config;
        }

        @Override
        public String toString() {
            return MoreObjects.toStringHelper(this)
                    .add("id", id)
                    .add("userId", userId)
                    .add("config", config)
                    .toString();
        }
    }

OrderDao.java:

    package com.example.demo.dao;

    import com.example.demo.entity.Order;
    import org.springframework.data.jpa.repository.JpaRepository;

    public interface OrderDao extends JpaRepository<Order, Long> {
    }

OrderItemDao.java:

    package com.example.demo.dao;

    import com.example.demo.entity.OrderItem;
    import org.springframework.data.jpa.repository.JpaRepository;
    import org.springframework.data.jpa.repository.Query;
    import org.springframework.data.repository.query.Param;

    import java.util.Optional;

    public interface OrderItemDao extends JpaRepository<OrderItem, Long> {
        //為了測(cè)試綁定表
        @Query(value = "select n from Order t inner join OrderItem n on t.orderId = n.orderId where n.orderId=:orderId")
        Optional<OrderItem> getOrderItemByOrderId(@Param("orderId") Long orderId);
    }

ConfigDao.java:

    package com.example.demo.dao;

    import com.sinosun.demo.entity.TConfig;
    import org.springframework.data.jpa.repository.JpaRepository;

    public interface ConfigDao extends JpaRepository<TConfig, Integer> {
    }

OrderController.java:

    package com.example.demo.controller;

    import com.example.demo.dao.OrderDao;
    import com.example.demo.entity.Order;
    import org.springframework.beans.factory.annotation.Autowired;
    import org.springframework.web.bind.annotation.RequestMapping;
    import org.springframework.web.bind.annotation.RequestMethod;
    import org.springframework.web.bind.annotation.RequestParam;
    import org.springframework.web.bind.annotation.RestController;

    import java.util.Optional;

    @RestController
    public class OrderController {
        @Autowired
        private OrderDao orderDao;

        @RequestMapping(value = "/order", method = RequestMethod.GET)
        public Optional<Order> getOrderById(@RequestParam("id") Long id) {
            return this.orderDao.findById(id);
        }

        @RequestMapping(value = "/order/save", method = RequestMethod.POST)
        public Order saveOrder(@RequestParam("name") String name, @RequestParam("userid") Long userId) {
            Order order = new Order();
            order.setName(name);
            order.setUserId(userId);
            return this.orderDao.save(order);
        }
    }

OrderItemController.java:

    package com.example.demo.controller;

    import com.example.demo.dao.OrderItemDao;
    import com.example.demo.entity.OrderItem;
    import org.springframework.beans.factory.annotation.Autowired;
    import org.springframework.web.bind.annotation.RequestMapping;
    import org.springframework.web.bind.annotation.RequestMethod;
    import org.springframework.web.bind.annotation.RequestParam;
    import org.springframework.web.bind.annotation.RestController;

    import java.util.Optional;

    @RestController
    public class OrderItemController {
        @Autowired
        private OrderItemDao orderItemDao;

        @RequestMapping(value = "/orderItem", method = RequestMethod.GET)
        public Optional<OrderItem> getOrderItemById(@RequestParam(name = "id") Long id) {
            return this.orderItemDao.findById(id);
        }

        @RequestMapping(value = "/orderItem/save", method = RequestMethod.POST)
        public OrderItem saveOrderItem(@RequestParam("item") String item, @RequestParam("userid") Long userId, @RequestParam("orderid") Long orderId) {
            OrderItem orderItem = new OrderItem();
            orderItem.setUserId(userId);
            orderItem.setItem(item);
            orderItem.setOrderId(orderId);
            return this.orderItemDao.save(orderItem);
        }

        @RequestMapping(value = "/orderItem/query", method = RequestMethod.GET)
        public Optional<OrderItem> getOrderItemByOrderId(@RequestParam(name = "orderid") Long orderId) {
            return this.orderItemDao.getOrderItemByOrderId(orderId);
        }
    }

ConfigController.java:

    package com.example.demo.controller;

    import com.example.demo.dao.ConfigDao;
    import com.example.demo.entity.TConfig;
    import org.springframework.beans.factory.annotation.Autowired;
    import org.springframework.web.bind.annotation.RequestMapping;
    import org.springframework.web.bind.annotation.RequestMethod;
    import org.springframework.web.bind.annotation.RestController;

    import java.util.List;

    @RestController
    public class ConfigController {
        @Autowired
        private ConfigDao configDao;

        @RequestMapping(value = "/listConfig", method = RequestMethod.GET)
        public List<TConfig> getConfig() {
            return this.configDao.findAll();
        }
    }

這三段代碼寫完基本的功能就完備了，但是剛才配置的時(shí)候提過瀑凝，我們的目的是按照user_id進(jìn)行分庫(kù)序芦，比如user_id=0則分配這條數(shù)據(jù)到ds0去，如果為1則將數(shù)據(jù)分配到ds1去粤咪，這就要求我們自己實(shí)現(xiàn)分庫(kù)的算法谚中，ShardingSphere提供了接口，只需要去實(shí)現(xiàn)就可以了：

    package com.example.demo.sharding;

    import org.apache.shardingsphere.api.sharding.standard.PreciseShardingAlgorithm;
    import org.apache.shardingsphere.api.sharding.standard.PreciseShardingValue;

    import java.util.Collection;

    public class PreciseShardingAlgorithmImpl implements PreciseShardingAlgorithm<Long> {

        @Override
        public String doSharding(Collection<String> availableTargetNames, PreciseShardingValue<Long> shardingValue) {
            String dbName = "ds";
            Long val = shardingValue.getValue();
            dbName += val;
            for (String each : availableTargetNames) {
                if (each.equals(dbName)) {
                    return each;
                }
            }
            throw new IllegalArgumentException();
        }
    }

這段代碼很簡(jiǎn)單寥枝，其中有幾個(gè)地方只需要講明白了就可以宪塔。

• availableTargetNames：這是datasource的名字列表，在這里應(yīng)該是ds0和ds1囊拜；

• shardingValue：這是分片列的值某筐，我們只要其value部分就可以。

之后用一個(gè)循環(huán)遍歷["ds0",
"ds1"]集合冠跷，當(dāng)我們的dbName和其中一個(gè)相等時(shí)南誊，就能的到正確的數(shù)據(jù)源。這就簡(jiǎn)單的實(shí)現(xiàn)了根據(jù)user_id精確分配數(shù)據(jù)的目的蜜托。

這是實(shí)測(cè)例子中抄囚，shardingValue和availableTargetNames的實(shí)際值：

image

本次測(cè)試的請(qǐng)求是：

    curl -X POST \
      'http://10.1.20.96:8080/order/save?name=LiLei&userid=0' \
      -H 'Postman-Token: d5e15e85-c760-4252-a7d4-ef57b5e95c2e' \
      -H 'cache-control: no-cache'

下面看看實(shí)際效果，這是ds0的數(shù)據(jù)：

image

這是ds1的數(shù)據(jù)：

image

可以看到橄务，所有的數(shù)據(jù)都根據(jù)user_id分布到了不同的庫(kù)中幔托，所有的數(shù)據(jù)都根據(jù)order_id的奇偶分布到了不同的表中。

記錄下保存t_order請(qǐng)求返回的order_id蜂挪，組裝一條POST請(qǐng)求寫t_order_item表：

    curl -X POST \
      'http://10.1.20.96:8080/orderItem/save?item=pen&userid=0&orderid=371698107924086785' \
      -H 'Accept: */*' \
      -H 'Cache-Control: no-cache' \
      -H 'Connection: keep-alive' \
      -H 'Host: 10.1.20.96:8080' \
      -H 'Postman-Token: 347b6c4d-0e2c-474f-b53e-6f0994db5871,24b362da-e77e-4b04-94e1-fa20dcb15845' \
      -H 'User-Agent: PostmanRuntime/7.15.0' \
      -H 'accept-encoding: gzip, deflate' \
      -H 'cache-control: no-cache' \
      -H 'content-length: '

得到結(jié)果如下：

image

使用這個(gè)order_id去進(jìn)行聯(lián)合查詢：

    curl -X GET \
      'http://10.1.20.96:8080/orderItem/query?orderid=371698107924086785' \
      -H 'Accept: */*' \
      -H 'Cache-Control: no-cache' \
      -H 'Connection: keep-alive' \
      -H 'Host: 10.1.20.96:8080' \
      -H 'Postman-Token: d0da0523-d46e-429f-a8db-9f844cd77fe6,b61c6089-253d-4535-b473-158c037850be' \
      -H 'User-Agent: PostmanRuntime/7.15.0' \
      -H 'accept-encoding: gzip, deflate' \
      -H 'cache-control: no-cache'

得到返回如下:

image

測(cè)試廣播表柑司，可以用下面的請(qǐng)求：

    curl -X GET \
      http://10.1.20.96:8080/listConfig \
      -H 'Accept: */*' \
      -H 'Cache-Control: no-cache' \
      -H 'Connection: keep-alive' \
      -H 'Host: 10.1.20.96:8080' \
      -H 'Postman-Token: 1c9d0349-4b6d-4a2c-834f-4e2f94194649,3dff68f4-2e10-4e96-926a-344faa5f0a19' \
      -H 'User-Agent: PostmanRuntime/7.15.0' \
      -H 'accept-encoding: gzip, deflate' \
      -H 'cache-control: no-cache'

得到的結(jié)果：

image

3. 利用SpringBoot實(shí)現(xiàn)讀寫分離

上一小節(jié)中展示了如何利用SharingSphere+SpringBoot進(jìn)行數(shù)據(jù)的分片迫肖，這一小節(jié)著重描述一下如何進(jìn)行讀寫分離，下一小節(jié)計(jì)劃展示如何將讀寫分離和分片結(jié)合起來攒驰。

首先還是會(huì)利用多實(shí)例來模擬蟆湖，為了簡(jiǎn)單，我沒有配置復(fù)制玻粪，而是預(yù)置了幾條數(shù)據(jù)進(jìn)去隅津，判斷能否將讀寫請(qǐng)求分發(fā)到不同的節(jié)點(diǎn)上。

首先我們新建一張表：

    create table t_order
    (
      order_id bigint not null auto_increment primary key,
      user_id bigint not null,
      name varchar(100)
    );

    -- master
    insert into t_order(user_id, name) values (0, 'zhiquan');

    -- slave 
    insert into t_order(user_id, name) values (1, 'LiLei');

我會(huì)配置slave為讀數(shù)據(jù)源劲室，那么讀出的數(shù)據(jù)一定是user_id=1這一條伦仍。

數(shù)據(jù)是這樣的，首先是master：

image

然后是slave：

image

接下來開始粘貼代碼很洋，首先是配置：

application.properties:

    spring.shardingsphere.datasource.names=ds0,ds1

    spring.shardingsphere.datasource.ds0.type=com.zaxxer.hikari.HikariDataSource
    spring.shardingsphere.datasource.ds0.jdbc-url=jdbc:mysql://localhost:3306/test?serverTimezone=GMT%2B8
    spring.shardingsphere.datasource.ds0.driver-class-name=com.mysql.jdbc.Driver
    spring.shardingsphere.datasource.ds0.username=root
    spring.shardingsphere.datasource.ds0.password=

    spring.shardingsphere.datasource.ds1.type=com.zaxxer.hikari.HikariDataSource
    spring.shardingsphere.datasource.ds1.jdbc-url=jdbc:mysql://localhost:3307/test?serverTimezone=GMT%2B8
    spring.shardingsphere.datasource.ds1.driver-class-name=com.mysql.jdbc.Driver
    spring.shardingsphere.datasource.ds1.username=root
    spring.shardingsphere.datasource.ds1.password=

    spring.shardingsphere.masterslave.name=ms
    spring.shardingsphere.masterslave.master-data-source-name=ds0
    spring.shardingsphere.masterslave.slave-data-source-names=ds1

    server.port=8080
    spring.jpa.show-sql=true

具體的實(shí)現(xiàn)代碼就不粘貼了充蓝，和之前的小節(jié)沒有什么區(qū)別。下面開始測(cè)試喉磁，首先是一個(gè)GET請(qǐng)求：

    curl -X GET \
      'http://localhost:8080/getOrder?orderId=2' \
      -H 'Accept: */*' \
      -H 'Accept-Encoding: gzip, deflate' \
      -H 'Cache-Control: no-cache' \
      -H 'Connection: keep-alive' \
      -H 'Host: localhost:8080' \
      -H 'Postman-Token: 028a4539-a727-47f2-8862-2eed637883d0,ffbe396f-5c33-4266-a00e-d2a0246283f3' \
      -H 'User-Agent: PostmanRuntime/7.15.2' \
      -H 'cache-control: no-cache'

image

如上圖谓苟，和預(yù)期是一樣的，讀取到了slave上的數(shù)據(jù)协怒，那么現(xiàn)在看看寫操作：

    curl -X POST \
      'http://localhost:8080/saveOrder?userId=123&name=HanMeimei' \
      -H 'Accept: */*' \
      -H 'Accept-Encoding: gzip, deflate' \
      -H 'Cache-Control: no-cache' \
      -H 'Connection: keep-alive' \
      -H 'Content-Length: ' \
      -H 'Host: localhost:8080' \
      -H 'Postman-Token: f0497259-a82a-4dcf-9849-3dcdae431742,77fd1308-b5e8-4882-be07-fa128e6efc4d' \
      -H 'User-Agent: PostmanRuntime/7.15.2' \
      -H 'cache-control: no-cache'

image

現(xiàn)在看看主節(jié)點(diǎn)的表：

image

如上圖涝焙，這條數(shù)據(jù)已經(jīng)成功的寫入了master。