一、前言
小编最近一直在研究关于分库分表的东西,前几天docker安装了mycat实现了分库分表,但是都在说mycat的bug很多。很多人还是倾向于shardingsphere,其实他是一个全家桶,有JDBC、Proxy 和 Sidecar组成,小编今天以最简单的JDBC来简单整合一下!
现在最新版已经是5.1.1,经过一天的研究用于解决了所有问题,完成了单库分表!!
二、踩过的坑
1. 数据源问题
不要使用druid-spring-boot-starter这个依赖,启动会有问题
<dependency> <groupId>com.alibaba</groupId> <artifactId>druid-spring-boot-starter</artifactId> <version>1.1.21</version></dependency>
报错信息:
Caused by: org.springframework.beans.factory.BeanCreationException: Error creating bean with name 'userMapper' defined in file [D:\jiawayun\demo\target\classes\com\example\demo\mapper\UserMapper.class]: Invocation of init method failed; nested exception is java.lang.IllegalArgumentException: Property 'sqlSessionFactory' or 'sqlSessionTemplate' are required
==解决方案:==
使用单独的druid
<dependency> <groupId>com.alibaba</groupId> <artifactId>druid</artifactId> <version>1.2.8</version></dependency>
建议使用==默认的数据源==,sharding-jdbc也是使用的默认的数据源,小编使用的自带的,忘记druid后面会不会有问题了!!
type: com.zaxxer.hikari.HikariDataSource
2. Insert 语句不支持分表路由到多个数据节点
报错信息:
Insert statement does not support sharding table routing to multiple data nodes.
解决方案:
解决不支持分表路由问题:https://blog.csdn.net/qq_52423918/article/details/125004312
三、导入maven依赖
<dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-web</artifactId></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><dependency> <groupId>junit</groupId> <artifactId>junit</artifactId> <scope>test</scope></dependency><dependency> <groupId>org.apache.shardingsphere</groupId> <artifactId>shardingsphere-jdbc-core-spring-boot-starter</artifactId> <version>5.1.1</version></dependency><dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-web</artifactId></dependency><dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-test</artifactId> <scope>test</scope></dependency><!-- lombok --><dependency> <groupId>org.projectlombok</groupId> <artifactId>lombok</artifactId> <version>1.18.10</version></dependency><!--jdbc--><dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-jdbc</artifactId></dependency><!-- mysql --><dependency> <groupId>mysql</groupId> <artifactId>mysql-connector-java</artifactId></dependency><!-- mybatis-plus --><dependency> <groupId>com.baomidou</groupId> <artifactId>mybatis-plus-boot-starter</artifactId> <version>3.5.1</version></dependency>
四、新建表
1. 新建二张表
命名为:user_0、user_1
CREATE TABLE `user_0` ( `cid` bigint(25) NOT NULL, `name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL, `gender` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL, `data` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL, PRIMARY KEY (`cid`) USING BTREE) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;SET FOREIGN_KEY_CHECKS = 1;
2. 数据库结构
五、框架全局展示
1. User实体类
@Datapublic class User implements Serializable { private static final long serialVersionUID = 337361630075002456L; private Long cid; private String name; private String gender; private String data;}
2. controller
@RestController@RequestMapping("/test")public class UserController { @Autowired private UserMapper userMapper; @GetMapping("/insertTest") public void insertTest(){ for (int i = 1 ; i < 10; i++) { User test = new User("王"+i,"男","数据" + i); userMapper.insert(test); } }}
3. mapper
我们直接省略了service,简单一下哈!!
public interface UserMapper extends BaseMapper<User> {}
4. application.yml配置
server: port: 8089spring: shardingsphere: mode: type: memory # 是否开启 datasource: # 数据源(逻辑名字) names: m1 # 配置数据源 m1: type: com.zaxxer.hikari.HikariDataSource driver-class-name: com.mysql.cj.jdbc.Driver url: jdbc:mysql://localhost:3306/test?useSSL=false&autoReconnect=true&characterEncoding=UTF-8&serverTimezone=UTC username: root password: root # 分片的配置 rules: sharding: # 表的分片策略 tables: # 逻辑表的名称 user: # 数据节点配置,采用Groovy表达式 actual-data-nodes: m1.user_$->{0..1} # 配置策略 table-strategy: # 用于单分片键的标准分片场景 standard: sharding-column: cid # 分片算法名字 sharding-algorithm-name: user_inline key-generate-strategy: # 主键生成策略 column: cid # 主键列 key-generator-name: snowflake # 策略算法名称(推荐使用雪花算法) key-generators: snowflake: type: SNOWFLAKE sharding-algorithms: user_inline: type: inline props: algorithm-expression: user_$->{cid % 2} props: # 日志显示具体的SQL sql-show: truelogging: level: com.wang.test.demo: DEBUGmybatis-plus: mapper-locations: classpath:mapper/*.xml type-aliases-package: com.example.demo.entity configuration: #在映射实体或者属性时,将数据库中表名和字段名中的下划线去掉,按照驼峰命名法映射 address_book ---> addressBook map-underscore-to-camel-case: true
5. 启动类
@MapperScan("com.example.demo.mapper")@SpringBootApplicationpublic class DemoApplication { public static void main(String[] args) { SpringApplication.run(DemoApplication.class, args); }}
六、测试插入九条数据
==本次测试策略是:行表达式分片策略:inline==
1. 插入数据
输入 :localhost:8089/test/insertTest
==分片成功==
2. 单个查询
@GetMapping("/selectOneTest")public void selectOneTest(){ User user = userMapper.selectOne(Wrappers.<User>lambdaQuery().eq(User::getCid,736989417020850176L)); System.out.println(user);}
这时他会根据cid去自动获取去那个表中获取数据
3. 全查询
@GetMapping("/selectListTest")public void selectListTest(){ List<User> list = userMapper.selectList(null); System.out.println(list);}
由于没有条件,他会去把两个表UNION ALL进行汇总
4. 分页查询
需要先配置mybatis-plus分页配置类:
@Configurationpublic class MybatisPlusConfig { @Bean public MybatisPlusInterceptor mybatisPlusInterceptor() { MybatisPlusInterceptor interceptor = new MybatisPlusInterceptor(); interceptor.addInnerInterceptor(new PaginationInnerInterceptor(DbType.MYSQL)); return interceptor; }}@GetMapping("/selectListPage")public void selectListPage(){ IPage<User> page = new Page(1,6); IPage<User> userIPage = userMapper.selectPage(page,null); List<User> records = userIPage.getRecords(); System.out.println(records);}
我们user_0有5条数据,user_1有4条数据
==我们发现它会向所有的表中去进行一遍分页查询,第一个表数据不够就会加上另一个表分页拿到的值==
==分页size为3时,一个user_0就可以满足分页条件,就会忽略user_1的分页数据。==
5. 非分片属性查询
我们先把user_0表性别修改两个为女,然后进行查询!看看没有分片的字段是否能够只去user_0去查询
@GetMapping("/selectListByGender")public void selectListByGender(){ List<User> list = userMapper.selectList(Wrappers.<User>lambdaQuery().eq(User::getGender, "女")); System.out.println(list);}
有图可见:不是分片的字段查询,回去全连接表去查询一遍,效率和不分表一样了哈!!
6. 分片属性来自一个表in查询
@GetMapping("/selectInList")public void selectList(){ List<User> users = userMapper.selectList(Wrappers.<User>lambdaQuery().in(User::getCid,736989417020850176L,736989418119757824L)); System.out.println(users);}
我们可以发现,我们根据分片字段进行in查询,sharding-jdbc会识别出来来自于那个表进而提高效率,不会所有的表进行全连接。
七、总结
这样就完成了最新版的sharding-jdbc的简单测试和一些坑的解决,总的来说配置很费劲,不能有一定的错误!
版权声明:内容来源于互联网和用户投稿 如有侵权请联系删除