ShardingJdbc整合水平分表
创建数据库
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 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 ROW_FORMAT = Dynamic;
引入maven依赖
<dependencies> <dependency> <groupId>org.mybatis.spring.boot</groupId> <artifactId>mybatis-spring-boot-starter</artifactId> <version>2.1.1</version> </dependency> <dependency> <groupId>com.alibaba</groupId> <artifactId>druid-spring-boot-starter</artifactId> <version>1.1.16</version> </dependency> <dependency> <groupId>mysql</groupId> <artifactId>mysql-connector-java</artifactId> <version>5.1.47</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</artifactId> </dependency> <dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-test</artifactId> <scope>test</scope> </dependency> </dependencies>
分片规则配置
分片规则配置是sharding-jdbc进行对分库分表操作的重要依据，配置内容包括：数据源、主键生成策略、分片策
略等。
在application.properties中配置
server.port=56081
spring.application.name = sharding-jdbc-simple-demo
server.servlet.context-path = /sharding-jdbc-simple-demo
spring.http.encoding.enabled = true
spring.http.encoding.charset = UTF-8
spring.http.encoding.force = true
spring.main.allow-bean-definition-overriding = true
mybatis.configuration.map-underscore-to-camel-case = true
#sharding-jdbc分片规则配置
#数据源
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/order_db?useUnicode=true
spring.shardingsphere.datasource.m1.username = root
spring.shardingsphere.datasource.m1.password = mysql
# 指定t_order表的数据分布情况，配置数据节点 m1.t_order_1,m1.t_order_2
spring.shardingsphere.sharding.tables.t_order.actual-data-nodes = m1.t_order_$->{1..2}
# 指定t_order表的主键生成策略为SNOWFLAKE
spring.shardingsphere.sharding.tables.t_order.key-generator.column=order_id
spring.shardingsphere.sharding.tables.t_order.key-generator.type=SNOWFLAKE
# 指定t_order表的分片策略，分片策略包括分片键和分片算法
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 + 1}
# 打开sql输出日志
spring.shardingsphere.props.sql.show = true
swagger.enable = true
logging.level.root = info
logging.level.org.springframework.web = info
logging.level.com.itheima.dbsharding = debug
logging.level.druid.sql = debug
1.首先定义数据源m1，并对m1进行实际的参数配置。
2.指定t_order表的数据分布情况，他分布在m1.t_order_1，m1.t_order_2
3.指定t_order表的主键生成策略为SNOWFLAKE，SNOWFLAKE是一种分布式自增算法，保证id全局唯一
4.定义t_order分片策略，order_id为偶数的数据落在t_order_1，为奇数的落在t_order_2，分表策略的表达式为t_order_$->{order_id % 2 + 1}
数据库操作
package com.topcheer.dbsharding.simple.dao; import org.apache.ibatis.annotations.Insert; import org.apache.ibatis.annotations.Mapper; import org.apache.ibatis.annotations.Param; import org.apache.ibatis.annotations.Select; import org.springframework.stereotype.Component; import java.math.BigDecimal; import java.util.List; import java.util.Map; /** * Created by Administrator. */ @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); }
测试类
@RunWith(SpringRunner.class) @SpringBootTest(classes = {ShardingJdbcSimpleBootstrap.class}) public class ShardingJdbcDemoApplicationTests { @Autowired(required = false) OrderDao orderDao; @Test public void testInsertOrder(){ for(int i=1;i<20;i++){ orderDao.insertOrder(new BigDecimal(i),1L,"SUCCESS"); } } @Test public void testSelectOrderbyIds(){ List<Long> ids = new ArrayList<>(); ids.add(435435795839451136L); ids.add(435435794501468161L); List<Map> maps = orderDao.selectOrderbyIds(ids); System.out.println(maps); } }
当执行插入的方法的时候, 会把原来的sql进行解析，然后根据分片的规则，进行插入不同的表
但执行查询的时候
通过日志可以发现，根据传入order_id的奇偶不同，sharding-jdbc分别去不同的表检索数据，达到预期目标
流程分析
通过日志分析，Sharding-JDBC在拿到用户要执行的sql之后干了哪些事儿：
（1）解析sql，获取片键值，在本例中是order_id
（2）Sharding-JDBC通过规则配置 t_order_$->{order_id % 2 + 1}，知道了当order_id为偶数时，应该往
t_order_1表插数据，为奇数时，往t_order_2插数据。
（3）于是Sharding-JDBC根据order_id的值改写sql语句，改写后的SQL语句是真实所要执行的SQL语句。
（4）执行改写后的真实sql语句
（5）将所有真正执行sql的结果进行汇总合并，返回。
注意假如是配置类的形式进行配置的话，要排除
@Configuration public class ShardingJdbcConfig { //配置分片规则 // 定义数据源 Map<String, DataSource> createDataSourceMap() { DruidDataSource dataSource1 = new DruidDataSource(); dataSource1.setDriverClassName("com.mysql.jdbc.Driver"); dataSource1.setUrl("jdbc:mysql://rm-bp1y5jh79h6b3eh9clo.mysql.rds.aliyuncs.com:3306/order_db?useUnicode=true"); dataSource1.setUsername("root"); dataSource1.setPassword("1qaz@WSX"); Map<String, DataSource> result = new HashMap<>(); result.put("m1", dataSource1); return result; } // 定义主键生成策略 private static KeyGeneratorConfiguration getKeyGeneratorConfiguration() { KeyGeneratorConfiguration result = new KeyGeneratorConfiguration("SNOWFLAKE","order_id"); return result; } // 定义t_order表的分片策略 TableRuleConfiguration getOrderTableRuleConfiguration() { TableRuleConfiguration result = new TableRuleConfiguration("t_order","m1.t_order_$->{1..2}"); result.setTableShardingStrategyConfig(new InlineShardingStrategyConfiguration("order_id", "t_order_$->{order_id % 2 + 1}")); result.setKeyGeneratorConfig(getKeyGeneratorConfiguration()); return result; } // 定义sharding-Jdbc数据源 @Bean DataSource getShardingDataSource() throws SQLException { ShardingRuleConfiguration shardingRuleConfig = new ShardingRuleConfiguration(); shardingRuleConfig.getTableRuleConfigs().add(getOrderTableRuleConfiguration()); //spring.shardingsphere.props.sql.show = true Properties properties = new Properties(); properties.put("sql.show","true"); return ShardingDataSourceFactory.createDataSource(createDataSourceMap(), shardingRuleConfig,properties); } }
