【MySQL高性能】2.ShardingSphere-Jdbc单数据库分表

前言

本篇是建立在上篇的ShardingSphere Jdbc 读写分离的基础上做出改动

提前在数据库创建t_user_0、t_user_1表。

配置

application.yml

spring:
  shardingsphere:
    datasource:
      # 数据源名称，多数据源以逗号分隔
      names: master-write,slave-read
      master-write:
        # 主节点数据库配置
        type: com.zaxxer.hikari.HikariDataSource
        driver-class-name: com.mysql.cj.jdbc.Driver
        jdbc-url: jdbc:mysql://192.168.126.156:3306/test?useUnicode=true&characterEncoding=utf-8&useSSL=false&serverTimezone=Asia/Shanghai
        username: root
        password: 123456
      slave-read:
        # 从节点数据库配置
        type: com.zaxxer.hikari.HikariDataSource
        driver-class-name: com.mysql.cj.jdbc.Driver
        jdbc-url: jdbc:mysql://192.168.126.156:3306/test?useUnicode=true&characterEncoding=utf-8&useSSL=false&serverTimezone=Asia/Shanghai
        username: root
        password: 123456
    # 规则配置
    rules:
      # 读写分离配置
      readwrite-splitting:
        load-balancers:
          ms:
            type: ROUND_ROBIN
            props:
              workId: 1
        data-sources:
          ms:
            type: Static
            load-balancer-name: round-robin
            props:
              # 主节点写
              write-data-source-name: master-write
              # 从节点读
              read-data-source-names: slave-read
      # 分表设置
      sharding:
        # 分片算法配置, rules.sharding子属性
        sharding-algorithms:
          table-inline:
            type: INLINE
            props:
              algorithm-expression: t_user_$->{id % 2}
        tables:
          t_user:
            actual-data-nodes: master-write.t_user_$->{0..1}
            table-strategy:
              standard:
                # 分表的字段
                sharding-column: id
                # 分表策略
                sharding-algorithm-name: table-inline
    # 属性配置
    props:
      # 展示修改以后的sql语句
      sql-show: true

测试类

import com.terry.App;
import lombok.extern.java.Log;
import org.junit.jupiter.api.Test;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;
import org.springframework.jdbc.core.JdbcTemplate;

import java.util.List;
import java.util.Map;

/**
 * ShardingSphere-Jdbc 增删改查测试
 * @author terry
 * @version 1.0
 * @date 2022/4/25 23:01
 */
@SpringBootTest(classes = App.class)
@Log
public class TestShardingSphereJdbc {

    @Autowired
    private JdbcTemplate jdbcTemplate;

    @Test
    public void testQuery(){
        List<Map<String, Object>> maps = jdbcTemplate.queryForList(" SELECT * FROM t_user ");
        log.info("数据库数据：" + maps);
    }

    @Test
    public void testInsert(){
        // jdbcTemplate.update("INSERT INTO t_user(name) VALUES(?)", "李四");
        // 分表策略不能使用自增id，会报错
        jdbcTemplate.update("INSERT INTO t_user(id, name) VALUES(?, ?)", Arrays.asList(4, "李四").toArray());
    }
}

运行打印输出：

testInsert:
Actual SQL: master-write ::: INSERT INTO t_user_0(id, name) VALUES(?, ?) ::: [4, 李四]

testQuery:
Actual SQL: master-write :::  SELECT * FROM t_user_0 WHERE id = 4 
数据库数据：[{id=4, name=李四}]

根据分表策略来插入和查询。