springboot使用shardingsphere_springboot shardingsphere

springboot使用shardingsphere

1、使用方法

shardingjdbc是基于aop原理，在应用程序对本地执行的sql进行拦截，解析，改写，路由处理。使用方便，只需要在配置文件编写实现，支持java语言，性能较高。

1. 核心概念：

• 逻辑表：
  同结构的水平分表的逻辑名就是表在sql语句中的逻辑标识，比如我有一张表 like_record_tpl，我根据它分了10张表，like_record_0~9，那么我在mybatis中的sql语句是对逻辑名"like_record"进行操作。

CREATE TABLE `like_record_tpl` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '主键id',
  `user_id` bigint(20) NOT NULL DEFAULT '0' COMMENT '用户id',
  `data_id` varchar(64) NOT NULL DEFAULT '' COMMENT '实体id',
  `like_status` tinyint(4) NOT NULL DEFAULT '0' COMMENT '点赞状态 1-点赞 0-取消点赞',
  `create_time` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00' COMMENT '创建时间',
  `update_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_user_data` (`user_id`,`data_id`),
  KEY `idx_data` (`data_id`)
) ENGINE=InnoDB COMMENT='';

CREATE TABLE `like_record_0` LIKE `like_record_tpl`;
CREATE TABLE `like_record_1` LIKE `like_record_tpl`;
CREATE TABLE `like_record_2` LIKE `like_record_tpl`;
CREATE TABLE `like_record_3` LIKE `like_record_tpl`;
CREATE TABLE `like_record_4` LIKE `like_record_tpl`;
CREATE TABLE `like_record_5` LIKE `like_record_tpl`;
CREATE TABLE `like_record_6` LIKE `like_record_tpl`;
CREATE TABLE `like_record_7` LIKE `like_record_tpl`;
CREATE TABLE `like_record_8` LIKE `like_record_tpl`;
CREATE TABLE `like_record_9` LIKE `like_record_tpl`;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22

2. springboot中引入shardingsphere
   引入依赖：

<!-- shardingsphere -->
<dependency>
    <groupId>org.apache.shardingsphere</groupId>
    <artifactId>sharding-jdbc-spring-boot-starter</artifactId>
    <version>4.1.1</version>
</dependency>
1
2
3
4
5
6

//首先指定数据源, 其中ds是标识符，用来指定数据源，数据库名为game-like与knights-community
//1.配置game-like数据源
spring.shardingsphere.datasource.names=ds-game-like, ds-knights-community
spring.shardingsphere.datasource.ds-game-like.type=com.zaxxer.hikari.HikariDataSource
spring.shardingsphere.datasource.ds-game-like.driver-class-name=com.mysql.cj.jdbc.Driver
spring.shardingsphere.datasource.ds-game-like.jdbc-url=jdbc:mysql://localhost:3306/game_like?characterEncoding=utf8&serverTimezone=GMT%2B8
spring.shardingsphere.datasource.ds-game-like.username=
spring.shardingsphere.datasource.ds-game-like.password=
//2.配置knights-community数据源,同上
//3.like_record_xx 按用户id分表 10张表
spring.shardingsphere.sharding.tables.like_record.actual-data-nodes=ds-game-like.like_record_$->{0..9}
spring.shardingsphere.sharding.tables.like_record.table-strategy.inline.sharding-column=user_id
spring.shardingsphere.sharding.tables.like_record.table-strategy.inline.algorithm-expression=like_record_$->{user_id % 10}
//4. like_log指定knights_community为数据源,并且like_log不分表
spring.shardingsphere.sharding.tables.like_log.actual-data-nodes=ds-knights-community.like_log
//5. 开启sql打印
spring.shardingsphere.props.sql.show=true
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

在 mybatis中对逻辑表名‘like_record和like_log’进行操作就可以了