写在前面的话
为什么要分库分表,这是基于mysql的b+树的特性来的,当单表的数据量大了之后要维护一棵庞大的树,这样会使得增删改查的性能大大降低。
本文基于一个普通Spring boot项目改造添加分库分表依赖而来,具体可以看这一篇Spring boot整合mybatis generator实现自动生成代码
从shardingsphere官网https://shardingsphere.apache.org/index_zh.html简介来看好像改造分库分表相当简单,只需要引入maven依赖即可
可是它并没有告诉你不同spring-boot版本要跟不同版本的sharding配合使用,比如我的demo初始化的时候spring-boot-starter-parent是2.2.6,shardingsphere是下面这个版本
<dependency>
<groupId>io.shardingsphere</groupId>
<artifactId>sharding-jdbc-spring-boot-starter</artifactId>
<version>3.0.0</version>
</dependency>
一启动就报如下异常
Error starting ApplicationContext. To display the conditions report re-run your application with 'debug' enabled.
2020-04-21 05:16:09.869 ERROR 11072 --- [ main] o.s.b.d.LoggingFailureAnalysisReporter :
***************************
APPLICATION FAILED TO START
***************************
Description:
The bean 'dataSource', defined in class path resource [io/shardingsphere/shardingjdbc/spring/boot/SpringBootConfiguration.class], could not be registered. A bean with that name has already been defined in class path resource [org/springframework/boot/autoconfigure/jdbc/DataSourceConfiguration$Hikari.class] and overriding is disabled.
Action:
Consider renaming one of the beans or enabling overriding by setting spring.main.allow-bean-definition-overriding=true
Disconnected from the target VM, address: '127.0.0.1:65495', transport: 'socket'
Process finished with exit code 1
即使在yml文件添加了如下的配置也没用
main:
allow-bean-definition-overriding: true
踩坑完毕,正文开始
org.springframework.boot使用2.0.3,sharding-jdbc使用3.0.0
<parent>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-parent</artifactId>
<version>2.0.3.RELEASE</version>
<relativePath/>
</parent>
<dependency>
<groupId>io.shardingsphere</groupId>
<artifactId>sharding-jdbc-spring-boot-starter</artifactId>
<version>3.0.0</version>
</dependency>
pom文件结构如下:
<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 https://maven.apache.org/xsd/maven-4.0.0.xsd">
<modelVersion>4.0.0</modelVersion>
<parent>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-parent</artifactId>
<version>2.0.3.RELEASE</version>
<relativePath/> <!-- lookup parent from repository -->
</parent>
<groupId>com.example</groupId>
<artifactId>mybatis-generator</artifactId>
<version>0.0.1-SNAPSHOT</version>
<name>mybatis-generator</name>
<description>Demo project for Spring Boot</description>
<properties>
<java.version>1.8</java.version>
</properties>
<dependencies>
<dependency>
<groupId>io.shardingsphere</groupId>
<artifactId>sharding-jdbc-spring-boot-starter</artifactId>
<version>3.0.0</version>
</dependency>
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>druid</artifactId>
<version>1.0.11</version>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-thymeleaf</artifactId>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-web</artifactId>
</dependency>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<scope>5.1.35</scope>
</dependency>
<dependency>
<groupId>org.mybatis.spring.boot</groupId>
<artifactId>mybatis-spring-boot-starter</artifactId>
<version>1.3.0</version>
</dependency>
<!--分页插件-->
<dependency>
<groupId>com.github.pagehelper</groupId>
<artifactId>pagehelper-spring-boot-starter</artifactId>
<version>1.2.3</version>
</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>
</dependencies>
<build>
<plugins>
<plugin>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-maven-plugin</artifactId>
</plugin>
<!--自动生成代码插件-->
<plugin>
<groupId>org.mybatis.generator</groupId>
<artifactId>mybatis-generator-maven-plugin</artifactId>
<version>1.3.2</version>
<configuration>
<configurationFile>${basedir}/src/main/resources/generator/generatorConfig.xml</configurationFile>
<overwrite>true</overwrite>
<verbose>true</verbose>
</configuration>
</plugin>
</plugins>
</build>
</project>
application.yml
server:
port: 8080
sharding:
jdbc:
datasource:
names: test
# 数据库test
test:
type: com.alibaba.druid.pool.DruidDataSource
driver-class-name: com.mysql.jdbc.Driver
url: jdbc:mysql://127.0.0.1:3306/test
username: root
password: 888888
config:
sharding:
props:
sql.show: true
tables:
user: #user表名
key-generator-column-name: id #主键
actual-data-nodes: test.user${0..1} #数据节点,均匀分布
table-strategy: #分表策略
inline: #行内表达式
sharding-column: id
algorithm-expression: user${id % 2} #按模运算分配
mybatis:
mapper-locations: classpath:mapping/*.xml
type-aliases-package: com.example.mybatisgenerator.model
# 打印sql
logging:
level:
com.example.mybatisgenerator.dao : debug
需要注意的是,分库分表的主键id要在代码里面生成,一般可以用雪花算法生成主键id,可参考旧文提及的分布式ID生成方式之雪花算法 ,如果采用数据库自增策略的话那么不同的表它们的id肯定会冲突
建表语句
DROP TABLE IF EXISTS `user`;
CREATE TABLE `user` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(255) NOT NULL,
`phone` varchar(255) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
执行测试
结果如下:
user0跟user1都有数据,user表是空的,可以理解为user表是逻辑表,
以上这种是属于单库分表,当然也可以配置多个数据源,主要是yml文件上的写法稍有差异。