推荐 Spring Boot/Cloud 视频:
项目初期版本使用当当sharding-jdbc进行数据库的分库分表操作,后续项目存储更换为自研弹性数据库。总结学习当当数据库中间件sharding-jdbc,并完成demo,可运行,下面列出demo代码说明:
建表sql
/*
Navicat MySQL Data Transfer 在Navicat中直接运行本sql文件创建表
* 两个库:test_msg1 包含两个表: t_order_0 t_order_1
* test_msg2 包含两个表: t_order_0 t_order_1
* 建表sql如下
*/
SET FOREIGN_KEY_CHECKS=0;
-- ----------------------------
-- Table structure for `users`
-- ----------------------------
DROP TABLE IF EXISTS `t_order_0`;
CREATE TABLE `t_order_0` (
`id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '主键id',
`order_id` varchar(32) DEFAULT NULL COMMENT '顺序编号',
`user_id` varchar(32) DEFAULT NULL COMMENT '用户编号',
`userName` varchar(32) DEFAULT NULL COMMENT '用户名',
`passWord` varchar(32) DEFAULT NULL COMMENT '密码',
`user_sex` varchar(32) DEFAULT NULL,
`nick_name` varchar(32) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=28 DEFAULT CHARSET=utf8;
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 http://maven.apache.org/xsd/maven-4.0.0.xsd">
<modelVersion>4.0.0</modelVersion>
<groupId>com.chun</groupId>
<artifactId>spring-boot-mybatis-sharding-jdbc</artifactId>
<version>1.0-SNAPSHOT</version>
<parent>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-parent</artifactId>
<version>1.4.2.RELEASE</version>
<relativePath/> <!-- lookup parent from repository -->
</parent>
<properties>
<project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
<java.version>1.8</java.version>
</properties>
<dependencies>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter</artifactId>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-test</artifactId>
<scope>test</scope>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-web</artifactId>
</dependency>
<dependency>
<groupId>org.mybatis.spring.boot</groupId>
<artifactId>mybatis-spring-boot-starter</artifactId>
<version>1.1.1</version>
</dependency>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-devtools</artifactId>
<optional>true</optional>
</dependency>
<dependency>
<groupId>org.projectlombok</groupId>
<artifactId>lombok</artifactId>
</dependency>
<!--sharding-jdbc -->
<dependency>
<groupId>com.dangdang</groupId>
<artifactId>sharding-jdbc-core</artifactId>
<version>1.5.4</version>
</dependency>
<!--<dependency>-->
<!--<groupId>javax.persistence</groupId>-->
<!--<artifactId>persistence-api</artifactId>-->
<!--<version>1.0</version>-->
<!--</dependency>-->
</dependencies>
<build>
<plugins>
<plugin>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-maven-plugin</artifactId>
<configuration>
<fork>true</fork>
</configuration>
</plugin>
</plugins>
</build>
</project>
分库分表最主要有几个配置:
- 有多少个数据源
- 每张表的逻辑表名和所有物理表名
- 用什么列进行分库以及分库算法
- 用什么列进行分表以及分表算法
分为两个库:test_msg1 , test_msg2
,
每个库都包含两个表:t_order_0 , t_order_1
使用user_id
作为分库列;
使用order_id
作为分表列;