ShardingSphere小记
Apache ShardingSphere 是一款分布式的数据库生态系统, 可以将任意数据库转换为分布式数据库,并通过数据分片、弹性伸缩、加密等能力对原有数据库进行增强。Apache ShardingSphere 设计哲学为 Database Plus,旨在构建异构数据库上层的标准和生态。它关注如何充分合理地利用数据库的计算和存储能力,而并非实现一个全新的数据库。它站在数据库的上层视角,关注它们之
1、介绍
Apache ShardingSphere 是一款分布式的数据库生态系统, 可以将任意数据库转换为分布式数据库,并通过数据分片、弹性伸缩、加密等能力对原有数据库进行增强。
Apache ShardingSphere 设计哲学为 Database Plus,旨在构建异构数据库上层的标准和生态。 它关注如何充分合理地利用数据库的计算和存储能力,而并非实现一个全新的数据库。 它站在数据库的上层视角,关注它们之间的协作多于数据库自身。
2、官网
最新版本:https://shardingsphere.apache.org/document/current/cn/overview/
3.1.0版本:https://shardingsphere.apache.org/document/3.1.0/cn/quick-start/shardingsphere-jdbc-quick-start/
3、Sharding-JDBC使用 (4.1.0版本)
完整代码下载:https://download.csdn.net/download/qq_17522211/87607601
3.1、POM
<parent>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-parent</artifactId>
<version>2.4.4</version>
<relativePath/>
</parent>
<dependency>
<groupId>org.apache.shardingsphere</groupId>
<artifactId>sharding-jdbc-spring-boot-starter</artifactId>
<version>4.1.1</version>
</dependency>
<dependency>
<groupId>com.zaxxer</groupId>
<artifactId>HikariCP</artifactId>
</dependency>
3.2、application.properties
server.port=8080
# 文档地址:https://shardingsphere.apache.org/document/4.1.0/en/manual/sharding-jdbc/configuration/config-spring-boot/
# 一个实体类对应多张表,覆盖
spring.main.allow-bean-definition-overriding=true
# 打印sql
spring.shardingsphere.props.sql.show=true
# 定义数据源
spring.shardingsphere.datasource.names=db0,db1,db2
# 第一个数据库
spring.shardingsphere.datasource.db0.type=com.zaxxer.hikari.HikariDataSource
spring.shardingsphere.datasource.db0.driver-class-name=com.mysql.cj.jdbc.Driver
spring.shardingsphere.datasource.db0.jdbc-url=jdbc:mysql://127.0.0.1:3306/db0?characterEncoding=utf8&useSSL=false&serverTimezone=UTC&rewriteBatchedStatements=true&statementInterceptors=com.fintell.common.mysql.MonitorMysql
spring.shardingsphere.datasource.db0.username=root
spring.shardingsphere.datasource.db0.password=123456
# 第二个数据库
spring.shardingsphere.datasource.db1.type=com.zaxxer.hikari.HikariDataSource
spring.shardingsphere.datasource.db1.driver-class-name=com.mysql.cj.jdbc.Driver
spring.shardingsphere.datasource.db1.jdbc-url=jdbc:mysql://127.0.0.1:3306/db1?characterEncoding=utf8&useSSL=false&serverTimezone=UTC&rewriteBatchedStatements=true&statementInterceptors=com.fintell.common.mysql.MonitorMysql
spring.shardingsphere.datasource.db1.username=root
spring.shardingsphere.datasource.db1.password=123456
# 第三个数据库
spring.shardingsphere.datasource.db2.type=com.zaxxer.hikari.HikariDataSource
spring.shardingsphere.datasource.db2.driver-class-name=com.mysql.cj.jdbc.Driver
spring.shardingsphere.datasource.db2.jdbc-url=jdbc:mysql://127.0.0.1:3306/db2?characterEncoding=utf8&useSSL=false&serverTimezone=UTC&rewriteBatchedStatements=true&statementInterceptors=com.fintell.common.mysql.MonitorMysql
spring.shardingsphere.datasource.db2.username=root
spring.shardingsphere.datasource.db2.password=123456
# 水平拆分的数据库(表) 配置分库 + 分表策略 行表达式分片策略
# 分库策略
spring.shardingsphere.sharding.default-database-strategy.inline.sharding-column=id
spring.shardingsphere.sharding.default-database-strategy.inline.algorithm-expression=db$->{id % 3}
# 分表策略
spring.shardingsphere.sharding.tables.book.actual-data-nodes=db$->{0..2}.book_$->{0..1}
# 分表字段列及分表规则
spring.shardingsphere.sharding.tables.book.table-strategy.inline.sharding-column=count
spring.shardingsphere.sharding.tables.book.table-strategy.inline.algorithm-expression=book_$->{count % 2}
# 指定主键
spring.shardingsphere.sharding.tables.book.key-generator.column=id
# 指定生成算法 SNOWFLAKE:雪花算法 UUID算法
spring.shardingsphere.sharding.tables.book.key-generator.type=SNOWFLAKE
# 广播表
#spring.shardingsphere.sharding.tables.broad_config.key-generator.column=id
spring.shardingsphere.sharding.broadcast-tables=broad_config
3.3、entity
import com.baomidou.mybatisplus.annotation.TableName;
import groovy.transform.EqualsAndHashCode;
import io.swagger.annotations.ApiModel;
import io.swagger.annotations.ApiModelProperty;
import lombok.Builder;
import lombok.Data;
import lombok.experimental.Accessors;
@Data
@Builder
@EqualsAndHashCode(callSuper = true)
@Accessors(chain = true)
@TableName("book")
@ApiModel(value="Book对象", description="")
public class Book {
@ApiModelProperty(value = "id")
private int id;
@ApiModelProperty(value = "书籍名称")
private String name;
@ApiModelProperty(value = "数量")
private int count;
}
import com.baomidou.mybatisplus.annotation.TableName;
import groovy.transform.EqualsAndHashCode;
import io.swagger.annotations.ApiModel;
import io.swagger.annotations.ApiModelProperty;
import lombok.Builder;
import lombok.Data;
import lombok.experimental.Accessors;
@Data
@Builder
@EqualsAndHashCode(callSuper = true)
@Accessors(chain = true)
@TableName("broad_config")
@ApiModel(value="broad_config对象", description="")
public class BroadConfig {
@ApiModelProperty(value = "id")
private Long id;
@ApiModelProperty(value = "vkey")
private String vkey;
@ApiModelProperty(value = "value")
private String value;
}
3.4、dao
import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.shardingjdbc.entity.Book;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;
import java.util.List;
@Mapper
public interface BookMapper extends BaseMapper<Book> {
@Select("select * from book limit ${start},${size}")
List<Book> selectPageMy(@Param("start") int start, @Param("size") int size);
@Select("select * from book where id = 1 limit ${start},${size}")
List<Book> selectById(@Param("start") int start, @Param("size") int size);
@Select("select * from book where count = 1 limit ${start},${size}")
List<Book> selectByCount(@Param("start") int start, @Param("size") int size);
@Select("select * from book where id = 1 and count = 1 limit ${start},${size}")
List<Book> selectByIdAndCount(@Param("start") int start, @Param("size") int size);
}
import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.shardingjdbc.entity.BroadConfig;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;
import java.util.List;
@Mapper
public interface BroadConfigMapper extends BaseMapper<BroadConfig> {
@Select("select * from broad_config limit ${start},${size}")
List<BroadConfig> selectPageMy(@Param("start") int start, @Param("size") int size);
}
3.5、service && serviceImpl
import com.baomidou.mybatisplus.core.metadata.IPage;
import com.shardingjdbc.entity.Book;
import java.util.List;
public interface BookService {
/**
* 查询书籍列表
* @return
*/
List<Book> getBookList();
/**
* 分页查询
*/
IPage<Book> getBookListPage(Integer page, Integer limit);
/**
* 分页查询
*/
IPage<Book> getBookListPage(Integer page, Integer limit, Integer id, Integer count);
/**
* 保存
* @param book
* @return
*/
boolean save(Book book);
/**
* 批量插入
*/
boolean saveBatch(List<Book> books);
/**
* 批量插入:事务模式
*/
boolean saveBatchTran(List<Book> books);
}
import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
import com.baomidou.mybatisplus.core.metadata.IPage;
import com.baomidou.mybatisplus.core.toolkit.Wrappers;
import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import com.baomidou.mybatisplus.extension.service.impl.ServiceImpl;
import com.shardingjdbc.entity.Book;
import com.shardingjdbc.mapper.BookMapper;
import com.shardingjdbc.service.BookService;
import org.springframework.beans.BeansException;
import org.springframework.context.ApplicationContext;
import org.springframework.context.ApplicationContextAware;
import org.springframework.jdbc.datasource.DataSourceTransactionManager;
import org.springframework.stereotype.Service;
import org.springframework.transaction.TransactionDefinition;
import org.springframework.transaction.TransactionStatus;
import org.springframework.transaction.support.DefaultTransactionDefinition;
import org.springframework.util.StringUtils;
import java.util.List;
@Service
public class BookServiceImpl extends ServiceImpl<BookMapper, Book> implements BookService, ApplicationContextAware {
private static ApplicationContext applicationContext;
@Override
public List<Book> getBookList() {
return baseMapper.selectList(Wrappers.<Book>lambdaQuery());
}
@Override
public IPage<Book> getBookListPage(Integer page, Integer limit) {
Page<Book> platformPage = new Page<>(page, limit);
QueryWrapper<Book> qw = new QueryWrapper<>();
qw.orderByAsc("id");
IPage<Book> operationPage = this.page(platformPage, qw);
return operationPage;
}
@Override
public IPage<Book> getBookListPage(Integer page, Integer limit, Integer id, Integer count) {
Page<Book> platformPage = new Page<>(page, limit);
QueryWrapper<Book> qw = new QueryWrapper<>();
if (!StringUtils.isEmpty(id)) {
qw.eq("id", id);
}
if (!StringUtils.isEmpty(count)) {
qw.eq("count", count);
}
qw.orderByAsc("id");
IPage<Book> operationPage = this.page(platformPage, qw);
return operationPage;
}
@Override
public boolean save(Book book) {
return super.save(book);
}
@Override
public boolean saveBatch(List<Book> books) {
return super.saveBatch(books, 7);
}
@Override
public boolean saveBatchTran(List<Book> books) {
DefaultTransactionDefinition definition = new DefaultTransactionDefinition();
definition.setPropagationBehavior(TransactionDefinition.PROPAGATION_REQUIRES_NEW);
definition.setIsolationLevel(TransactionDefinition.ISOLATION_READ_COMMITTED);
DataSourceTransactionManager transactionManager = (DataSourceTransactionManager)applicationContext.getBean("transactionManager");
TransactionStatus transactionStatus = transactionManager.getTransaction(definition);
for (Book book : books) {
//BookMapper有insert方法,单条插入
applicationContext.getBean(BookMapper.class).insert(book);
}
transactionManager.commit(transactionStatus);
return true;
}
@Override
public void setApplicationContext(ApplicationContext ac) throws BeansException {
applicationContext = ac;
}
}
import com.baomidou.mybatisplus.core.metadata.IPage;
import com.shardingjdbc.entity.BroadConfig;
import java.util.List;
public interface BroadConfigService {
/**
* 查询书籍列表
*/
List<BroadConfig> getBroadConfigList();
/**
* 分页查询
*/
IPage<BroadConfig> getBroadConfigPage(Integer page, Integer limit);
/**
* 保存
*/
boolean add(BroadConfig broadConfig);
}
import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
import com.baomidou.mybatisplus.core.metadata.IPage;
import com.baomidou.mybatisplus.core.toolkit.Wrappers;
import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import com.baomidou.mybatisplus.extension.service.impl.ServiceImpl;
import com.shardingjdbc.entity.BroadConfig;
import com.shardingjdbc.mapper.BroadConfigMapper;
import com.shardingjdbc.service.BroadConfigService;
import org.springframework.stereotype.Service;
import java.util.List;
@Service
public class BroadConfigServiceImpl extends ServiceImpl<BroadConfigMapper, BroadConfig> implements BroadConfigService {
@Override
public List<BroadConfig> getBroadConfigList() {
return baseMapper.selectList(Wrappers.lambdaQuery());
}
@Override
public IPage<BroadConfig> getBroadConfigPage(Integer page, Integer limit) {
Page<BroadConfig> platformPage = new Page<>(page, limit);
QueryWrapper<BroadConfig> qw = new QueryWrapper<>();
qw.orderByAsc("id");
IPage<BroadConfig> operationPage = this.page(platformPage, qw);
return operationPage;
}
@Override
public boolean add(BroadConfig broadConfig) {
return save(broadConfig);
}
}
3.6、test
import com.baomidou.mybatisplus.core.metadata.IPage;
import com.shardingjdbc.entity.Book;
import com.shardingjdbc.mapper.BookMapper;
import com.shardingjdbc.service.BookService;
import org.junit.Test;
import org.junit.runner.RunWith;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;
import org.springframework.test.context.junit4.SpringRunner;
import javax.annotation.Resource;
import java.util.ArrayList;
import java.util.List;
/**
* 序号 数据条数 方式 耗时
* 1 10000 saveBatch?&rewriteBatchedStatements=true 11785ms
* 2 10000 saveBatch 10798ms
* 3 10000 saveBatchTran :事务控制 11800ms
*/
@RunWith(SpringRunner.class)
@SpringBootTest
public class BookServiceImplTest {
@Autowired
private BookService bookService;
@Resource
private BookMapper bookMapper;
/**
* 批量新增 :super.save() 方式
*/
@Test
public void saveBatchBySave() {
long start = System.currentTimeMillis();
for (int i = 0; i < 100; i++) {
Book book = Book.builder().id(i).name("name" + i).count(i).build();
bookService.save(book);
}
System.err.println(System.currentTimeMillis() - start);
}
/**
* 批量新增 :super.saveBatch() 方式
*/
@Test
public void saveBatch() {
List<Book> books = new ArrayList<>();
long start = System.currentTimeMillis();
for (int i = 0; i < 10000; i++) {
Book book = Book.builder().id(i).name("name" + i).count(i).build();
books.add(book);
}
bookService.saveBatch(books);
System.err.println(System.currentTimeMillis() - start);
}
/**
* 批量新增 :事务 方式
*/
@Test
public void saveBatchByTran() {
List<Book> books = new ArrayList<>();
long start = System.currentTimeMillis();
for (int i = 0; i < 10000; i++) {
Book book = Book.builder().id(i).name("name" + i).count(i).build();
books.add(book);
}
bookService.saveBatchTran(books);
System.err.println(System.currentTimeMillis() - start);
}
/**
* 使用了分页插件
* 查询 :mybatis-plus 的 QueryWrapper 方式
*/
@Test
public void listPage() {
IPage<Book> listPage = bookService.getBookListPage(5, 10);
for (Book record : listPage.getRecords()) {
System.err.println(record);
}
}
/**
* 使用了分页插件
* 查询:mybatis-plus 的 QueryWrapper 方式
* 指定 id 和 count,则只会查询某张表,但是分页仍然从0查到最大
* SELECT id,name,count FROM book_1 WHERE id = ? AND count = ? ORDER BY id ASC LIMIT ?,? ::: [[1, 1, 0, 50]]
*/
@Test
public void listPageByIdCountWrapper() {
IPage<Book> listPage = bookService.getBookListPage(5, 10, 1, 1);
for (Book record : listPage.getRecords()) {
System.err.println(record);
}
}
/**
* 未使用分页插件
* 查询:写sql方式 :select * from book limit ${start},${size}
* 若使用sql查询,则shardingjdbc会自动转为子表查询
* 1、因未指定id和count ,则查询所有表
*/
@Test
public void selectPageMy() {
List<Book> books = bookMapper.selectPageMy(1, 2);
for (Book book : books) {
System.err.println(book);
}
}
/**
* 未使用分页插件
* 查询:写sql方式 :select * from book where id = 1 limit ${start},${size}
* 若使用sql查询,则shardingjdbc会自动转为子表查询
* 1、因指定了 id ,则仅查询某些库
*/
@Test
public void selectById() {
List<Book> books = bookMapper.selectById(1, 2);
for (Book book : books) {
System.err.println(book);
}
}
/**
* 未使用分页插件
* 查询:写sql方式 :select * from book where count = 1 limit ${start},${size}
* 若使用sql查询,则shardingjdbc会自动转为子表查询
* 1、因指定了 count ,则仅查询所有库的某些表
*/
@Test
public void selectByCount() {
List<Book> books = bookMapper.selectByCount(1, 2);
for (Book book : books) {
System.err.println(book);
}
}
/**
* 未使用分页插件
* 查询:写sql方式 :select * from book where id = 1 and count = 1 limit ${start},${size}
* 若使用sql查询,则shardingjdbc会自动转为子表查询
* 1、因指定了 id 和 count ,则仅查询某张表
*/
@Test
public void selectByIdAndCount() {
List<Book> books = bookMapper.selectByIdAndCount(1, 2);
for (Book book : books) {
System.err.println(book);
}
}
/**
* 查询所有:mybatis-plus 的 QueryWrapper 方式
*/
@Test
public void listAll() {
List<Book> bookList = bookService.getBookList();
for (Book book : bookList) {
System.err.println(book);
}
}
}
import com.shardingjdbc.entity.BroadConfig;
import com.shardingjdbc.mapper.BroadConfigMapper;
import com.shardingjdbc.service.BroadConfigService;
import org.junit.jupiter.api.Test;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;
import javax.annotation.Resource;
import java.util.List;
@SpringBootTest
public class BroadConfigServiceImplTest {
@Autowired
private BroadConfigService broadConfigService;
@Resource
private BroadConfigMapper broadConfigMapper;
/**
* 批量新增 :super.save() 方式
*/
@Test
public void saveBatchBySave() {
long start = System.currentTimeMillis();
for (int i = 0; i < 1; i++) {
BroadConfig broadConfig = BroadConfig.builder().id((long) i + 1).vkey("key-" + i).value("value-" + i).build();
System.err.println(broadConfig);
broadConfigService.add(broadConfig);
}
System.err.println(System.currentTimeMillis() - start);
}
@Test
public void selectPageMy() {
List<BroadConfig> listPage = broadConfigMapper.selectPageMy(5, 10);
for (BroadConfig record : listPage) {
System.err.println(record);
}
}
}
4、结论
条件
- 分库:db$->{id % 3} - 通过id % 3来获取数据库
- 分表:book_$->{count % 2} - 通过count % 2来获取表
表结构如下:
结论说明
4.1、结论1
序号 | 指定id(分库) | 指定count(分表) | 查询类型 |
---|---|---|---|
1 | × | × | 所有库所有表 |
2 | × | ✔ | 所有库匹配表 |
3 | ✔ | × | 匹配库所有表 |
4 | ✔ | ✔ | 匹配库匹配表 |
4.2、结论2
无论是使用QueryWrapper查询 还是 使用写sql方式,sharding均可进行转换。
- QueryWrapper:baseMapper.selectList(Wrappers.lambdaQuery());
- 写sql:@Select(“select * from book limit p a g e , {page}, page,{size}”)
4.3、结论3
批量入库,三种方式效率差异不大。
PS:若使用saveBatch,batchSize如果配置大了会报错。
/**
* 序号 数据条数 方式 耗时
* 1 10000 saveBatch?&rewriteBatchedStatements=true 11785ms
* 2 10000 saveBatch 10798ms
* 3 10000 saveBatchTran :事务控制 11800ms
*/
4.4、结论4
分页查询: limit 5, 10
1、使用分页插件
- 查询条件,可以匹配到某个库、某张表,使用 limit 0,50 查询
- 查询条件,无法匹配到某个库、某张表,使用 limit 0,50 查询
- 也就是,若使用了分页查询,无论是否条件匹配,均使用 limit 0, 50 查询
2、未使用分页插件
- 查询条件,可以匹配到某个库、某张表,使用 limit 5,10 查询
- 查询条件,无法匹配到某个库、某张表,使用 limit 0,15 查询
分页方案优化
1、通过记录上次查询结果的最后一条记录的ID进行下一页的查询
SELECT * FROM user WHERE id > 100000 LIMIT 10
2、放弃使用它自身的这种排序或者分页
使用nosql工具,如:MongoDB,ElasticSearch,solr等创建二级索引。
利用ElasticSearch,把我们常用的搜索条件和排序字段都索引进去,这样我们先查询es,然后返回id(分表策略选取的id),然后再拿这个id去各个表中查询,性能也完全没有问题。
如果该id在各个表中刚好是主键,那性能会非常快,因为主键不但是唯一索引,更因为是聚簇索引,聚簇索引上面存的是该列数据。
3、若可以精确到某个库、某张表
3.1、增加查询总数方法
3.2、舍弃PageHelper分页插件查询
3.3、使用sql进行分页查询:若可以匹配某个库、某张表,且代码写死limit,则会按照sql进行查询
// 调用执行
bookMapper.selectByIdAndCount(5, 10);
// 方法实现
@Select("select * from book where id = 1 and count = 1 limit ${start},${size}")
List<Book> selectByIdAndCount(@Param("start") int start, @Param("size") int size);
可以精确到某个库、某张表 (使用分页插件)
// 调用执行
bookService.getBookListPage(5, 10, 1, 1);
// 实现代码
public IPage<Book> getBookListPage(Integer page, Integer limit, Integer id, Integer count) {
Page<Book> platformPage = new Page<>(page, limit);
QueryWrapper<Book> qw = new QueryWrapper<>();
if (!StringUtils.isEmpty(id)) {
qw.eq("id", id);
}
if (!StringUtils.isEmpty(count)) {
qw.eq("count", count);
}
qw.orderByAsc("id");
IPage<Book> operationPage = this.page(platformPage, qw);
return operationPage;
}
无法精确到某个库、某张表 (使用分页插件)
// 调用执行
bookService.getBookListPage(5, 10);
// 实现代码
public IPage<Book> getBookListPage(Integer page, Integer limit) {
Page<Book> platformPage = new Page<>(page, limit);
QueryWrapper<Book> qw = new QueryWrapper<>();
qw.orderByAsc("id");
IPage<Book> operationPage = this.page(platformPage, qw);
return operationPage;
}
可以精确到某个库、某张表 (未使用分页插件)
// 调用执行
bookMapper.selectByIdAndCount(5, 10);
// 实现代码
@Select("select * from book where id = 1 and count = 1 limit ${start},${size}")
List<Book> selectByIdAndCount(@Param("start") int start, @Param("size") int size);
无法精确到某个库、某张表 (未使用分页插件)
// 调用执行
bookMapper.selectByCount(5, 10);
// 实现代码
@Select("select * from book where count = 1 limit ${start},${size}")
List<Book> selectByCount(@Param("start") int start, @Param("size") int size);
4.5、结论5
广播表新增、删除、修改时会在所有库进行新增、删除、修改
// 调用执行
broadConfigService.add(broadConfig);
// 实现代码
@Override
public boolean add(BroadConfig broadConfig) {
return save(broadConfig);
}
开放原子开发者工作坊旨在鼓励更多人参与开源活动,与志同道合的开发者们相互交流开发经验、分享开发心得、获取前沿技术趋势。工作坊有多种形式的开发者活动,如meetup、训练营等,主打技术交流,干货满满,真诚地邀请各位开发者共同参与!
更多推荐
所有评论(0)