工作中经常遇到大数据量的问题,每天的数据量可达百万,需要进行分库分表,从网上找了很多博客学习,经常遇到各种各样的问题,大都是版本选择和配置文件错误,其实仔细研究官方文档就可以了,今天总算是跑起来了,测试也没什么大问题,后面继续优化。
一、框架版本
1、shardingsphere5.5.0
2、springboot3.2.11
3、mybatis-plus3.5.5
二、maven依赖
<dependencies>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-web</artifactId>
</dependency>
<!-- mybatis-plus -->
<dependency>
<groupId>com.baomidou</groupId>
<artifactId>mybatis-plus-spring-boot3-starter</artifactId>
<version>3.5.5</version>
</dependency>
<dependency>
<groupId>com.mysql</groupId>
<artifactId>mysql-connector-j</artifactId>
<scope>runtime</scope>
</dependency>
<dependency>
<groupId>org.projectlombok</groupId>
<artifactId>lombok</artifactId>
<optional>true</optional>
</dependency>
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>druid-spring-boot-starter</artifactId>
<version>1.2.23</version>
</dependency>
<dependency>
<groupId>org.apache.shardingsphere</groupId>
<artifactId>shardingsphere-jdbc</artifactId>
<version>5.5.0</version>
<exclusions>
<exclusion>
<groupId>org.apache.shardingsphere</groupId>
<artifactId>shardingsphere-test-util</artifactId>
</exclusion>
</exclusions>
</dependency>
</dependencies>
三、配置文件(application.yml)
spring:
main:
allow-bean-definition-overriding: true
application:
name: ls-backend
datasource:
driver-class-name: org.apache.shardingsphere.driver.ShardingSphereDriver
url: jdbc:shardingsphere:classpath:sharding1.yaml
servlet:
multipart:
max-file-size: 100MB
max-request-size: 100MB
server:
port: 8085
servlet:
context-path: /
encoding:
force-response: true
#Mybatis扫描
mybatis:
config-location: classpath:/mybatis-config.xml
mybatis-plus:
mapper-locations: classpath:/mapper/*Mapper.xml
configuration:
log-impl: org.apache.ibatis.logging.stdout.StdOutImpl
四、sharding配置文件(sharding.yaml)
dataSources:
ds0:
driverClassName: com.mysql.cj.jdbc.Driver
dataSourceClassName: com.alibaba.druid.pool.DruidDataSource
url: jdbc:mysql://127.0.0.1:3306/ds0?useUnicode=true&useSSL=true&characterEncoding=utf8&zeroDateTimeBehavior=convertToNull&serverTimezone=GMT%2B8
username: root
password: root
ds1:
driverClassName: com.mysql.cj.jdbc.Driver
dataSourceClassName: com.alibaba.druid.pool.DruidDataSource
url: jdbc:mysql://127.0.0.1:3306/ds1?useUnicode=true&useSSL=true&characterEncoding=utf8&zeroDateTimeBehavior=convertToNull&serverTimezone=GMT%2B8
username: root
password: root
rules:
- !SHARDING
tables:
flight:
actualDataNodes: ds0.flight_$->{0..1}
tableStrategy:
standard:
shardingColumn: id
shardingAlgorithmName: flight-id-inline
shardingAlgorithms:
flight-id-inline:
type: INLINE
props:
algorithm-expression: flight_$->{id % 2}
props:
sql-show: true
这块要特别注意,属性名要严格遵守驼峰,"- !SHARDING"必须要加,目前不明白为什么,这块是完全按照官方文档来的,还有就是官方文档中写的url是jdbcUrl,但是运行会报错,dubug发现这块取url时使用的是url,故将此处改为url可正常运行。
五、测试
使用mybatis-plus新增数据,根据日志可发现shardingsphere会根据分片算法将逻辑表名变更为实际表名。日志如下:
Creating a new SqlSession
SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@165b679a] was not registered for synchronization because synchronization is not active
JDBC Connection [HikariProxyConnection@412916513 wrapping org.apache.shardingsphere.driver.jdbc.core.connection.ShardingSphereConnection@5ece1773] will not be managed by Spring
==> Preparing: SELECT id,num,date FROM flight WHERE id=?
==> Parameters: 2(Integer)
2024-11-06T10:55:42.313+08:00 INFO 9892 --- [ls-backend] [nio-8085-exec-4] ShardingSphere-SQL : Logic SQL: SELECT id,num,date FROM flight WHERE id=?
2024-11-06T10:55:42.313+08:00 INFO 9892 --- [ls-backend] [nio-8085-exec-4] ShardingSphere-SQL : Actual SQL: ds0 ::: SELECT id,num,date FROM flight_0 WHERE id=? ::: [2]
<== Columns: id, num, date
<== Row: 2, 2, 2024-11-06 10:35:02
<== Total: 1
Closing non transactional SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@165b679a]
flight逻辑表名会根据取模算法替换为实际表名。
结语
遇到问题不要慌,静下心来看看文档,多看看框架源代码。
这是第一次接触shardingsphere,有很多详细问题没有理解透彻,大佬勿喷!!!