shardingjdbc水平分表

springboot集成shardingjdbc水平分表案例：

 

　springboot的配置

　mybatis.configuration.log-impl=org.apache.ibatis.logging.stdout.StdOutImpl

   logging.level.com.example.gilbert.mapper=debug
　

　# 定义数据源

　spring.shardingsphere.datasource.names=ds1,ds2

　spring.shardingsphere.datasource.ds1.driver-class-name=com.mysql.jdbc.Driver

　# 数据源连接一
　spring.shardingsphere.datasource.ds1.type=com.alibaba.druid.pool.DruidDataSource
　spring.shardingsphere.datasource.ds1.url=jdbc:mysql://localhost:3306/order_db_1
　spring.shardingsphere.datasource.ds1.username=root
　spring.shardingsphere.datasource.ds1.password=123456
　# 数据源连接二
　spring.shardingsphere.datasource.ds2.driver-class-name=com.mysql.jdbc.Driver
　spring.shardingsphere.datasource.ds2.type=com.alibaba.druid.pool.DruidDataSource
　spring.shardingsphere.datasource.ds2.url=jdbc:mysql://localhost:3306/order_db_2
　spring.shardingsphere.datasource.ds2.username=root
　spring.shardingsphere.datasource.ds2.password=123456

 

　# 综合定义数据源头ds1,ds2 + 对应的分表t_order_1,t_order_2,t_order_3
　spring.shardingsphere.sharding.tables.t_order.actual-data-nodes=ds$->{1..2}.t_order_$->{1..3}

　# 定义虚拟逻辑表t_order的分片列名为：order_id, 主键id的通过雪花算法生成
　spring.shardingsphere.sharding.tables.t_order.key-generator.column=order_id
　spring.shardingsphere.sharding.tables.t_order.key-generator.props.worker.id=1
　spring.shardingsphere.sharding.tables.t_order.key-generator.type=SNOWFLAKE

　# 根据user_id进行分库（分片键和分片算法）
　spring.shardingsphere.sharding.tables.t_order.database-strategy.inline.algorithm-expression=ds$->{user_id % 2 + 1}
　spring.shardingsphere.sharding.tables.t_order.database-strategy.inline.sharding-column=user_id

　# 根据order_id进行分表（分片键和分片算法）
　spring.shardingsphere.sharding.tables.t_order.table-strategy.inline.algorithm-expression=t_order_$->{order_id % 3 + 1}
　spring.shardingsphere.sharding.tables.t_order.table-strategy.inline.sharding-column=order_id

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

流程分析
1. 解析sql，获取分片值，在本例中就是order_id;
2. Shardingjdbc通过规则配置t_order_$->{order_id % 2 + 1},知道了当id为偶数时应该往t_order_1表中插入数据，当order_id为奇数时往t_order_2中插入数据；
3. Shardingjdbc根据order_id的值改写sql语句，改写后的sql语句才是真实的待执行的sql语句；
4. 执行改写后的真实sql语句；
5. 将真实的sql语句执行的结果进行合并汇总；

mybatis,mysql,shardingjdbc相关依赖：

<dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
</dependency>

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

<dependency>
    <groupId>org.mybatis.spring.boot</groupId>
    <artifactId>mybatis-spring-boot-starter</artifactId>
    <version>2.2.2</version>
</dependency>

<dependency>
    <groupId>org.apache.shardingsphere</groupId>
    <artifactId>sharding-jdbc-spring-boot-starter</artifactId>
    <version>4.1.1</version>
</dependency>