ShardingSphere你還不會嗎?(第一篇)
- 2021 年 5 月 29 日
- 筆記
作者:星晴(當地小有名氣,小到只有自己知道的杰倫粉)
一.需求
我們做項目的時候,數據量比較大,單表千萬級別的,需要分庫分表,於是在網上搜索這方面的開源框架,最常見的就是mycat,sharding-sphere,最終我選擇後者,用它來做分庫分表比較容易上手。
二. 簡介sharding-sphere
官網地址: //shardingsphere.apache.org/
3.1 pom.xml
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<scope>runtime</scope>
</dependency>
<!--shardingsphere start-->
<!-- for spring boot -->
<dependency>
<groupId>io.shardingsphere</groupId>
<artifactId>sharding-jdbc-spring-boot-starter</artifactId>
<version>3.1.0</version>
</dependency>
<!-- for spring namespace -->
<dependency>
<groupId>io.shardingsphere</groupId>
<artifactId>sharding-jdbc-spring-namespace</artifactId>
<version>3.1.0</version>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-data-jpa</artifactId>
</dependency>
# 數據源 cloud-db-0,cloud-db-1
sharding:
jdbc:
datasource:
names: cloud-db-0,cloud-db-1
# 第一個資料庫
cloud-db-0:
type: com.zaxxer.hikari.HikariDataSource
driver-class-name: com.mysql.cj.jdbc.Driver
jdbc-url: jdbc:mysql://localhost:3306/cloud-db-0?characterEncoding=utf-8&&serverTimezone=GMT%2B8
username: root
password: 123456
# 第二個資料庫
cloud-db-1:
type: com.zaxxer.hikari.HikariDataSource
driver-class-name: com.mysql.cj.jdbc.Driver
jdbc-url: jdbc:mysql://localhost:3306/cloud-db-1?characterEncoding=utf-8&&serverTimezone=GMT%2B8
username: root
password: 123456
# 水平拆分的資料庫(表) 配置分庫 + 分表策略 行表達式分片策略
# 分庫策略
config:
sharding:
default-database-strategy:
inline:
sharding-column: id
algorithm-expression: cloud-db-$->{id % 2}
#分表策略 其中user為邏輯表 分表主要取決於age行
tables:
user:
actual-data-nodes: cloud-db-$->{0..1}.user_$->{0..1}
table-strategy:
inline:
sharding-column: age
# 分片演算法表達式
algorithm-expression: user_$->{age % 2}
# 列印執行的資料庫以及語句
props:
sql:
show: true
SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;
-- ----------------------------
-- Table structure for user_0
-- ----------------------------
DROP TABLE IF EXISTS `user_0`;
CREATE TABLE `user_0` (
`id` int(11) NOT NULL,
`name` varchar(255) DEFAULT NULL,
`age` int(11) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
-- ----------------------------
-- Table structure for user_1
-- ----------------------------
DROP TABLE IF EXISTS `user_1`;
CREATE TABLE `user_1` (
`id` int(11) NOT NULL,
`name` varchar(255) DEFAULT NULL,
`age` int(11) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
SET FOREIGN_KEY_CHECKS = 1;
SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;
-- ----------------------------
-- Table structure for user_0
-- ----------------------------
DROP TABLE IF EXISTS `user_0`;
CREATE TABLE `user_0` (
`id` int(11) NOT NULL,
`name` varchar(255) DEFAULT NULL,
`age` int(11) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
-- ----------------------------
-- Table structure for user_1
-- ----------------------------
DROP TABLE IF EXISTS `user_1`;
CREATE TABLE `user_1` (
`id` int(11) NOT NULL,
`name` varchar(255) DEFAULT NULL,
`age` int(11) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
SET FOREIGN_KEY_CHECKS = 1;
@Data
@Entity
@Table(name = "user")
public class User {
/**
* 主鍵Id
*/
@Id
private int id;
/**
* 名稱
*/
private String name;
/**
* 年齡
*/
private int age;
}
public interface UserRepository extends JpaRepository<User,Integer> {
}
@Service
public class UserServiceImpl implements UserService {
@Autowired
private UserRepository userRepository;
@Override
public boolean save(User entity) {
userRepository.save(entity);
return true;
}
@Override
public List<User> getUserList() {
return userRepository.findAll();
}
}
@RestController
public class UserController {
@Autowired
private UserService userService;
@GetMapping("/select")
public List<User> select() {
return userService.getUserList();
}
@GetMapping("/insert")
public Boolean insert(User user) {
return userService.save(user);
}
}
//localhost:8080/insert?id=1&name=lhd&age=12
//localhost:8080/insert?id=2&name=lhd&age=13
//localhost:8080/insert?id=3&name=lhd&age=14
//localhost:8080/insert?id=4&name=lhd&age=15
//localhost:8080/select
2.分庫、分表查詢
1.分庫、分表插入
3.5 測試
UserController
UserServiceImpl
UserRepository
User
3.4 程式碼實現
cloud-db-1:
cloud-db-0: