ShardingSphere分库分表中间件

ShardingSphere主要有三大技术：
Sharding-JDBC
sharding-proxy：透明化数据库代理端
sharding-sidecar

分库分表主要分为垂直分库，垂直分表，水平分库和水平分表。

Sharding-JDBC
增强版JDBC
1 开源得轻量级java框架
作用：1 数据分片
2 读写分离
目的：简化对数据库的分库分表的数据操作

sharding-JDBC实现水平分表

创建数据库和表。
1 创建数据库course_db
2 在数据库创建两张表course_1 、course_2
3 约定规则，id为偶数添加到course_1 ,id为奇数添加到course_2

Sharding-JDBC 分片配置

需要的maven依赖

<dependencies>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
        </dependency>

        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
            <optional>true</optional>
        </dependency>
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>5.1.47</version>
        </dependency>
        <dependency>
            <groupId>com.baomidou</groupId>
            <artifactId>mybatis-plus-boot-starter</artifactId>
            <version>3.3.1.tmp</version>
        </dependency>
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>druid-spring-boot-starter</artifactId>
            <version>1.1.20</version>
        </dependency>
        <dependency>
            <groupId>junit</groupId>
            <artifactId>junit</artifactId>
            <version>4.12</version>
            <scope>test</scope>
        </dependency>
<!--        <dependency>-->
<!--            <groupId>org.apache.shardingsphere</groupId>-->
<!--            <artifactId>sharding-jdbc-core</artifactId>-->
<!--            <version>4.0.1</version>-->
<!--        </dependency>-->
        <dependency>
            <groupId>org.apache.shardingsphere</groupId>
            <artifactId>sharding-jdbc-spring-boot-starter</artifactId>
            <version>4.0.0-RC1</version>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-test</artifactId>
            <scope>test</scope>
            <exclusions>
                <exclusion>
                    <groupId>org.junit.vintage</groupId>
                    <artifactId>junit-vintage-engine</artifactId>
                </exclusion>
            </exclusions>
        </dependency>
    </dependencies>

springboot配置

#分片策略
#配置数据源,可以配置多个
spring.shardingsphere.datasource.names=m1
spring.shardingsphere.datasource.m1.type=com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.m1.driver-class-name=com.mysql.jdbc.Driver
spring.shardingsphere.datasource.m1.url=jdbc:mysql://localhost:3306/course_db
spring.shardingsphere.datasource.m1.username=root
spring.shardingsphere.datasource.m1.password=root
#表的分布和策略
spring.shardingsphere.sharding.tables.course.actual-data-nodes=m1.course_$->{1..2}

#指定course表中主键生成策略
spring.shardingsphere.sharding.tables.course.key-generator.column=cid
#雪花算法
spring.shardingsphere.sharding.tables.course.key-generator.type=SNOWFLAKE

#指定分片策略，cid为偶数添加到course_1,如果cid为奇数添加到course_2
spring.shardingsphere.sharding.tables.course.table-strategy.inline.sharding-column=cid
spring.shardingsphere.sharding.tables.course.table-strategy.inline.algorithm-expression=course_$->{cid % 2+1}

#打印sql日志
spring.shardingsphere.props.sql.show=true
spring.main.allow-bean-definition-overriding=true

一个实体类无法对应两个表。加设置spring.main.allow-bean-definition-overriding=true

实体类

import lombok.Data;

@Data
public class Course {
    private Long cid;
    private String cname;
    private Long userId;
    private String cstatus;
}

mapper包

@Repository
public interface CourseMapper extends BaseMapper<Course> {
}

测试代码

@SpringBootTest
@RunWith(SpringRunner.class)
public class DemoshardingApplicationTests {

    @Autowired
    private CourseMapper courseMapper;

    @Test
    public void contextLoads() {
        Course course=new Course();

        course.setCname("lisi");
        course.setCstatus("ling");
        course.setUserId(2L);
        courseMapper.insert(course);
    }

    @Test
    public void findCourse(){
        QueryWrapper<Course> wrapper=new QueryWrapper<>();
        wrapper.eq("cid",475046275276865537L);
        Course course = courseMapper.selectOne(wrapper);
        System.out.println(course.toString());

    }

}

水平分库

创建两个数据库edu_db_1和edu_db_2
两个数据库中都有两张表course_1和course_2

数据库规则：user_id为偶数添加到edu_db_1中
user_id为奇数添加到edu_db_2中
表规则： cid为偶数添加到course_1中
cid为奇数添加到course_2中

修改配置文件

#分片策略
#配置数据源,可以配置多个，水平分库
spring.shardingsphere.datasource.names=m1m2
spring.shardingsphere.datasource.m1.type=com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.m1.driver-class-name=com.mysql.jdbc.Driver
spring.shardingsphere.datasource.m1.url=jdbc:mysql://localhost:3306/edu_db_1?characterEncoding=utf-8
spring.shardingsphere.datasource.m1.username=root
spring.shardingsphere.datasource.m1.password=root
#第二个数据源
spring.shardingsphere.datasource.m2.type=com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.m2.driver-class-name=com.mysql.jdbc.Driver
spring.shardingsphere.datasource.m2.url=jdbc:mysql://localhost:3306/edu_db_2?characterEncoding=utf-8
spring.shardingsphere.datasource.m2.username=root
spring.shardingsphere.datasource.m2.password=root
#数据库和表的分布和策略
spring.shardingsphere.sharding.tables.course.actual-data-nodes=m$->{1..2}.course_$->{1..2}

#指定数据库分片的策略
spring.shardingsphere.sharding.default-database-strategy.inline.sharding-column=user_id
spring.shardingsphere.sharding.default-database-strategy.inline.algorithm-expression=m$->{user_id % 2+1}

#指定course表中主键生成策略
spring.shardingsphere.sharding.tables.course.key-generator.column=cid
#雪花算法
spring.shardingsphere.sharding.tables.course.key-generator.type=SNOWFLAKE

#指定分片策略，cid为偶数添加到course_1,如果cid为奇数添加到course_2
spring.shardingsphere.sharding.tables.course.table-strategy.inline.sharding-column=cid
spring.shardingsphere.sharding.tables.course.table-strategy.inline.algorithm-expression=course_$->{cid % 2+1}

#打印sql日志
spring.shardingsphere.props.sql.show=true

spring.main.allow-bean-definition-overriding=true

测试代码

//水平分库测试
@Test
public void addCourseDb(){
    Course course=new Course();
    course.setUserId(100L);
    course.setCstatus("Normal");
    course.setCname("javademo");
    courseMapper.insert(course);
}
//查询操作
@Test
public void findCourseDb(){
    QueryWrapper<Course> wrapper=new QueryWrapper();
    wrapper.eq("cid",475073813369847809L);
    wrapper.eq("user_id",100L);
    Course course = courseMapper.selectOne(wrapper);
    System.out.println(course.toString());
}
//条件越精准，查找的表越少

垂直分库

专库专表
创建数据库user_db 下面创建表t_user
创建数据库

创建user实体类和mapper

配置垂直分库策略
配置文件修改

spring.shardingsphere.datasource.names=m1,m2,m0
#第三个数据源
spring.shardingsphere.datasource.m0.type=com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.m0.driver-class-name=com.mysql.jdbc.Driver
spring.shardingsphere.datasource.m0.url=jdbc:mysql://localhost:3306/user_db?characterEncoding=utf-8
spring.shardingsphere.datasource.m0.username=root
spring.shardingsphere.datasource.m0.password=root
#配置user_db数据库中的t_user 专库专表
spring.shardingsphere.sharding.tables.t_user.key-generator.column=user_id
spring.shardingsphere.sharding.tables.t_user.key-generator.type=SNOWFLAKE
spring.shardingsphere.sharding.tables.t_user.actual-data-nodes=m$->{0}.t_user
spring.shardingsphere.sharding.tables.t_user.table-strategy.inline.sharding-column=user_id
spring.shardingsphere.sharding.tables.t_user.table-strategy.inline.algorithm-expression=t_user

测试代码

//user测试
@Test
public void addUserDb(){
    User user=new User();
    user.setUsername("Lucy");
    user.setUstatus("a");
    userMapper.insert(user);
}
@Test
public void findUserDb(){
    QueryWrapper wrapper=new QueryWrapper();
    wrapper.eq("userId",475248414334910465L);
    User user = userMapper.selectOne(wrapper);
    System.out.println(user.toString());
}

Sharding-JDBC公共表

1 公共表
存储固定数据的表，表数据很少发生变化，查询时候经常进行关联
在每个数据库中创建出相同数据结构的公共表。
向公共表中加数据，所有的表都会更新

再配置文件中配置公共表

#配置公共表
spring.shardingsphere.sharding.broadcast-tables=t_udict
#配置公共表id生成策略
spring.shardingsphere.sharding.tables.t_udict.key-generator.column=dictid
spring.shardingsphere.sharding.tables.t_udict.key-generator.type=SNOWFLAKE

测试代码

//测试公共表
@Test
public void addDict(){
    Udict udict=new Udict();
    udict.setUstatus("已开启");
    udict.setUvalue("this is value");
    udictMapper.insert(udict);
}

Sharding_JDBC 完成读写分离，主从复制

主服务器：开启binlog日志，记录增删改操作
从服务器：实时监控binlog

sharding-JDBC根据sql语句判断是路由到哪个数据库中。
sharding-JDBC不会做数据同步

2 Mysql配置读写分离
第一步：创建两个mysq服务
第二部：修改复制之后得配置文件，my.ini
第三步：安装修改之后得mysqlwindows服务mysql install mysqls1 --default-file=“C:\web\mysql-5.6 - s1\my.ini”
配置mysql主从配置
主mysql配置文件：

#开启日志
log-bin=mysql-bin
#设置服务id，主从不能一致
server-id=1
#设置需要同步得数据库
binlog-do-db=user_db
#屏蔽系统库同步
binlog-ignore-db=mysql
binlog-ignore-db=information_schema
binlog-ignore-db=performance_schema

从库配置

#开启日志
log-bin=mysql-bin
server-id=2
replicate_wild_do_table=user_db.%
replicate_wild_ignore_table=mysql.%
replicate_wild_ignore_table=information_schema.%
replicate_wild_ignore_table=performance_schema.%

设置从库下个主库同步数据
登录从服务器客户端
先停止同步
Stop SLAVE
修改从库指向到主库，
CHANGE MASTER TO
master_host=‘localhost’,
master_user=‘root’
master_password=‘root’
master_log_file=‘mysql-bin.000002’,
master_log_pos=154,

#启动同步
start slave
排查相关异常
show slave status

Sharding-JDBC操作
配置读写分离

spring.shardingsphere.Sharding.master-slave-rules.ds0.master-data-source-name=m0
spring.shardingsphere.Sharding.master-slave-rules.ds0.slave-data-source-name=s0
#t_user分表策略，固定分配到ds0的t_user真实表
spring.shardingsphere.Sharding.tables.t_user.actual-data-nodes=ds0.t_user

Sharding-proxy

1 定位为透明的书库代理端
需要独立安装，官网下载，解压
在conf目录下配置
1 修改server.yaml

authentication:
  users:
    root:
      password: root
    sharding:
      password: sharding 
      authorizedSchemas: sharding_db

props:
  max.connections.size.per.query: 1
  acceptor.size: 16  # The default value is available processors count * 2.
  executor.size: 16  # Infinite by default.
  proxy.frontend.flush.threshold: 128  # The default value is 128.
    # LOCAL: Proxy will run with LOCAL transaction.
    # XA: Proxy will run with XA transaction.
    # BASE: Proxy will run with B.A.S.E transaction.
  proxy.transaction.type: LOCAL
  proxy.opentracing.enabled: false
  query.with.cipher.column: true
  sql.show: false

2 修改config-sharding.yaml
把mysql的jar包放入lib中