水平分库
创建两个数据库
edu_db_1
course_01
course_02
edu_db_2
course_01
course_02
数据库规则:
1.userid为偶数数据添加edu_db_1数据库,为奇数数据添加edu_db2数据库
表规则:
1.cid为偶数数据添加course_1表,
为奇数数据添加course_2表
在原来基础上增加一个pojo和多了一些配置文件mapper的东西。
# 配置Sharding-JDBC的分片策略 # 配置数据源,给数据源起名g1,g2...此处可配置多数据源 spring.shardingsphere.datasource.names=g1,m1,m2 # 配置数据源具体内容————————包含 连接池, 驱动, 地址, 用户名, 密码 # 由于上面配置数据源只有g1因此下面只配置g1.type,g1.driver-class-name,g1.url,g1.username,g1.password # 水平分表 spring.shardingsphere.datasource.g1.type=com.alibaba.druid.pool.DruidDataSource spring.shardingsphere.datasource.g1.driver-class-name=com.mysql.cj.jdbc.Driver spring.shardingsphere.datasource.g1.url=jdbc:mysql://192.168.31.200:3306/sharding_jdbc?serverTimezone=GMT%2B8 spring.shardingsphere.datasource.g1.username=root spring.shardingsphere.datasource.g1.password=player3. ##水平分库分表 spring.shardingsphere.datasource.m1.type=com.alibaba.druid.pool.DruidDataSource spring.shardingsphere.datasource.m1.driver-class-name=com.mysql.cj.jdbc.Driver spring.shardingsphere.datasource.m1.url=jdbc:mysql://192.168.31.200:3306/edu_db_1?serverTimezone=GMT%2B8 spring.shardingsphere.datasource.m1.username=root spring.shardingsphere.datasource.m1.password=player3. spring.shardingsphere.datasource.m2.type=com.alibaba.druid.pool.DruidDataSource spring.shardingsphere.datasource.m2.driver-class-name=com.mysql.cj.jdbc.Driver spring.shardingsphere.datasource.m2.url=jdbc:mysql://192.168.31.200:3306/edu_db_2?serverTimezone=GMT%2B8 spring.shardingsphere.datasource.m2.username=root spring.shardingsphere.datasource.m2.password=player3. # 配置表的分布,表的策略 goods 是水平分表 spring.shardingsphere.sharding.tables.goods.actual-data-nodes=g1.goods_$->{1..2} #水平分库分表的策略表是course m$->{1..2} 不是很懂,是上面数据源的别名,哈 spring.shardingsphere.sharding.tables.course.actual-data-nodes=m$->{1..2}.course_$->{1..2} # 指定goods表 主键gid 生成策略为 SNOWFLAKE spring.shardingsphere.sharding.tables.goods.key-generator.column=gid spring.shardingsphere.sharding.tables.goods.key-generator.type=SNOWFLAKE ##指定course表的,主键cid 生成策略 spring.shardingsphere.sharding.tables.course.key-generator.column=cid spring.shardingsphere.sharding.tables.course.key-generator.type=SNOWFLAKE # 指定分片策略 约定gid值是偶数添加到goods_1表,如果gid是奇数添加到goods_2表 spring.shardingsphere.sharding.tables.goods.table-strategy.inline.sharding-column=gid spring.shardingsphere.sharding.tables.goods.table-strategy.inline.algorithm-expression=goods_$->{gid % 2 + 1} # 指定分片策略 约定cid值是偶数添加到course_1表,如果gid是奇数添加到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} ##增加数据库的分片策略 user_id 根据这个字段走库,如果是 约定user_id值是偶数添加m1库,如果gid是奇数添加到m2库 #默认所有的数据库都走这个策略,我们不希望都用这个 #spring.shardingsphere.rules.sharding.default-database-strategy.standard.sharding-column=user_id #spring.shardingsphere.rules.sharding.default-database-strategy.standard.sharding-algorithm-name=default-database-strategy-inline spring.shardingsphere.sharding.tables.course.database-strategy.inline.sharding-column=user_id spring.shardingsphere.sharding.tables.course.database-strategy.inline.algorithm-expression=m$->{user_id % 2+1} # 打开sql输出日志 spring.shardingsphere.props.sql.show=true spring.main.allow-bean-definition-overriding=true
pojo类
package com.player3.sharding.jdbc.pojo; import lombok.Data; import lombok.ToString; /** * @author player3 */ @Data @ToString public class Course { private Long cid; private String cname; private Long userId; private String cstatus; }
mapper
/** * @author player3 */ @Repository public interface CourseMapper extends BaseMapper<Course> { }
sql:
CREATE TABLE course_1( cid BIGINT(20) primary key comment '商品id,主键', cname varchar(20) not null comment '商品名称', user_id BIGINT(20) not null COMMENT '用户id', cstatus VARCHAR(10) not null COMMENT '商品状态,已发布 or 未发布'); CREATE TABLE course_2( cid BIGINT(20) primary key comment '商品id,主键', cname varchar(20) not null comment '商品名称', user_id BIGINT(20) not null COMMENT '用户id', cstatus VARCHAR(10) not null COMMENT '商品状态,已发布 or 未发布');
测试类:多了一个查询的方法
package com.player3.Shardingjdbc; import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper; import com.player3.sharding.jdbc.ShardingJdbcApplication; import com.player3.sharding.jdbc.dao.CourseMapper; import com.player3.sharding.jdbc.dao.GoodsMapper; import com.player3.sharding.jdbc.pojo.Course; import com.player3.sharding.jdbc.pojo.Goods; import org.junit.jupiter.api.Test; import org.springframework.boot.test.context.SpringBootTest; import javax.annotation.Resource; @SpringBootTest(classes = ShardingJdbcApplication.class) public class ShardingjdbcTests { @Resource GoodsMapper goodsMapper; @ Resource CourseMapper courseMapper; //添加的方法 @Test public void addCourse(){ for (int i = 20; i < 20000; i++) { Course course = new Course(); course.setCname("java"+i); course.setUserId(Long.valueOf(i)); course.setCstatus("Normal"+i); courseMapper.insert(course); } } //查询的方法 @Test public void findCourseDb(){ QueryWrapper<Course> wrapper = new QueryWrapper<>(); wrapper.eq("user_id",203l); wrapper.eq("cid",689457744721215489l); Course course = courseMapper.selectOne(wrapper); System.out.println(course); } @Test public void addGoods() { System.out.println("访问成功"); Goods good = new Goods(); good.setGname("小米手机"); good.setUserId(100L); good.setGstatus("已发布"); goodsMapper.insert(good); } }
插入的规则就是:根据userid去区分库,然后根据主键id区分用哪个表。
查询的sql打印:
2022-01-16 13:13:56.008 INFO 8384 --- [ main] ShardingSphere-SQL : Rule Type: sharding
2022-01-16 13:13:56.009 INFO 8384 --- [ main] ShardingSphere-SQL : Logic SQL: SELECT cid,cname,user_id,cstatus FROM course
WHERE user_id = ? AND cid = ?
2022-01-16 13:13:56.009 INFO 8384 --- [ main] ShardingSphere-SQL : SQLStatement: SelectStatement(super=DQLStatement(super=AbstractSQLStatement(type=DQL, tables=Tables(tables=[Table(name=course, alias=Optional.absent())]), routeConditions=Conditions(orCondition=OrCondition(andConditions=[AndCondition(conditions=[Condition(column=Column(name=user_id, tableName=course), operator=EQUAL, compareOperator==, positionValueMap={}, positionIndexMap={0=0}), Condition(column=Column(name=cid, tableName=course), operator=EQUAL, compareOperator==, positionValueMap={}, positionIndexMap={0=1})])])), encryptConditions=Conditions(orCondition=OrCondition(andConditions=[])), sqlTokens=[TableToken(tableName=course, quoteCharacter=NONE, schemaNameLength=0)], parametersIndex=2, logicSQL=SELECT cid,cname,user_id,cstatus FROM course
WHERE user_id = ? AND cid = ?)), containStar=false, firstSelectItemStartIndex=8, selectListStopIndex=32, groupByLastIndex=0, items=[CommonSelectItem(expression=cid, alias=Optional.absent()), CommonSelectItem(expression=cname, alias=Optional.absent()), CommonSelectItem(expression=user_id, alias=Optional.absent()), CommonSelectItem(expression=cstatus, alias=Optional.absent())], groupByItems=[], orderByItems=[], limit=null, subqueryStatement=null, subqueryStatements=[], subqueryConditions=[])
2022-01-16 13:13:56.009 INFO 8384 --- [ main] ShardingSphere-SQL : Actual SQL: m2 ::: SELECT cid,cname,user_id,cstatus FROM course_2
WHERE user_id = ? AND cid = ? ::: [203, 689457744721215489]
Course(cid=689457744721215489, cname=java203, userId=203, cstatus=Normal203)