Sharding JDBC 分库分表、读写分离、主从同步实战

前言

本文将介绍搭建MySQL一主二从架构，通过 Sharding JDBC 实现分库分表、读写分离、从表间负载均衡等功能。

架构图：

步骤一：搭建MySQL一主两从-主从同步集群

步骤二：在主库新建数据库，数据表

在主库新建 order_db 数据库，数据表 t_order_1, t_order_2。看两个从库slave1，slave2数据是否有同步，有同步则说明MySQL主从同步集群搭建成功。

DROP TABLE IF EXISTS `t_order_1`;
CREATE TABLE `t_order_1`  (
  `order_id` bigint(20) NOT NULL COMMENT '订单id',
  `price` decimal(10, 2) NOT NULL COMMENT '订单价格',
  `user_id` bigint(20) NOT NULL COMMENT '下单用户id',
  `status` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '订单状态',
  PRIMARY KEY (`order_id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci COMMENT = '订单表1' ROW_FORMAT = Dynamic;

DROP TABLE IF EXISTS `t_order_2`;
CREATE TABLE `t_order_2`  (
  `order_id` bigint(20) NOT NULL COMMENT '订单id',
  `price` decimal(10, 2) NOT NULL COMMENT '订单价格',
  `user_id` bigint(20) NOT NULL COMMENT '下单用户id',
  `status` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '订单状态',
  PRIMARY KEY (`order_id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci COMMENT = '订单表2' ROW_FORMAT = Dynamic;

步骤三：接入Sharding JDBC

添加pom文件

<dependency>
    <groupId>org.apache.shardingsphere</groupId>
    <artifactId>shardingsphere-jdbc-core</artifactId>
    <version>${latest.release.version}</version>
</dependency>

`修改配置文件`

t_order 采用水平分表，分片键为 order_id；写主库，读从库；两从库负载均衡采用轮询算法。

server:
  port: 56081
  servlet:
    context-path: /sharding-jdbc-simple-demo
spring:
  application:
    name: sharding-jdbc-simple-demo
  http:
    encoding:
      enabled: true
      charset: utf-8
      force: true
  main:
    allow-bean-definition-overriding: true
	
  # shardingsphere配置
  shardingsphere:
    # 配置3个MySQL数据源
    datasource:
      names: m1,m2,m3
      m1:
        type: com.alibaba.druid.pool.DruidDataSource
        driverClassName: com.mysql.jdbc.Driver
        url: jdbc:mysql://120.78.***.***:3339/order_db?characterEncoding=UTF-8&serverTimezone=Asia/Shanghai&useSSL=false
        username: root
        password: 123456
      m2:
        type: com.alibaba.druid.pool.DruidDataSource
        driverClassName: com.mysql.jdbc.Driver
        url: jdbc:mysql://120.78.***.***:3340/order_db?characterEncoding=UTF-8&serverTimezone=Asia/Shanghai&useSSL=false
        username: root
        password: 123456
      m3:
        type: com.alibaba.druid.pool.DruidDataSource
        driverClassName: com.mysql.jdbc.Driver
        url: jdbc:mysql://120.78.***.***:3341/order_db?characterEncoding=UTF-8&serverTimezone=Asia/Shanghai&useSSL=false
        username: root
        password: 123456
		
	#  读写分离及负载均衡配置
    sharding:
      master-slave-rules:
        ds0:
          master-data-source-name: m1
          slave-data-source-names: m2,m3
		  #负载均衡算法：ROUND_ROBIN:轮询算法（默认）；RANDOM：随机算法
          loadBalanceAlgorithmType: ROUND_ROBIN   
		  
	  #  标准分片表配置	  
      tables:
        t_order:
          actualDataNodes: ds0.t_order_$->{1..2}
		  # 分表策略
          tableStrategy:
            inline:
			  # 分片建
              shardingColumn: order_id
			  # 分片算法
              algorithmExpression: t_order_$->{order_id % 2 + 1}
			  
		  # 指定主键生成策略为SNOWFLAKE
          keyGenerator:
            type: SNOWFLAKE
            column: order_id
    props:
      sql:
        show: true
		
mybatis:
  configuration:
    map-underscore-to-camel-case: true
swagger:
  enable: true
logging:
  level:
    root: info
    org.springframework.web: info
    com.itheima.dbsharding: debug
    druid.sql: debug

步骤四：代码测试

OrderDao

@Mapper
@Component
public interface OrderDao {

    /**
     * 插入订单
     * @param price
     * @param userId
     * @param status
     * @return
     */
    @Insert("insert into t_order(price,user_id,status)values(#{price},#{userId},#{status})")
    int insertOrder(@Param("price")BigDecimal price,@Param("userId")Long userId,@Param("status")String status);

    /**
     * 根据id列表查询订单
     * @param orderIds
     * @return
     */
    @Select("<script>" +
            "select" +
            " * " +
            " from t_order t " +
            " where t.order_id in " +
            " <foreach collection='orderIds' open='(' separator=',' close=')' item='id'>" +
            " #{id} " +
            " </foreach>" +
            "</script>")
    List<Map> selectOrderbyIds(@Param("orderIds") List<Long> orderIds);
}

OrderDaoTest测试类

测试功能：①分表插入、分表查询是否正常；②是否写入主库，从库数据有无同步；③是否从库读取数据，从库间负载均衡算法是否正常。

@RunWith(SpringRunner.class)
@SpringBootTest(classes = {ShardingJdbcSimpleBootstrap.class})
public class OrderDaoTest {

    @Autowired
    OrderDao orderDao;

    @Test
    public void testInsertOrder(){
        for(int i=0;i<=20;i++){
            orderDao.insertOrder(new BigDecimal(i),1L,"SUCCESS");
        }
    }

    @Test
    public void testSelectOrderbyIds(){
        List<Long> ids = new ArrayList<>();
        ids.add(数据id);
        ids.add(数据id);

        for (int i = 0; i < 20; i++) {
            List<Map> maps = orderDao.selectOrderbyIds(ids);
            System.out.println(maps);
        }
    }

目录CONTENT