这篇文章主要介绍了springboot jpa分库分表项目实现过程详解,文中通过示例代码介绍的非常详细,对大家的学习或者工作具有一定的参考学习价值,需要的朋友可以参考下
分库分表场景
关系型数据库本身比较容易成为系统瓶颈,单机存储容量、连接数、处理能力都有限。当单表的数据量达到1000w或100g以后,由于查询维度较多,即使添加从库、优化索引,做很多操作时性能仍下降严重。此时就要考虑对其进行切分了,切分的目的就在于减少数据库的负担,缩短查询时间。
分库分表用于应对当前互联网常见的两个场景——大数据量和高并发。通常分为垂直拆分和水平拆分两种。
垂直拆分是根据业务将一个库(表)拆分为多个库(表)。如:将经常和不常访问的字段拆分至不同的库或表中。由于与业务关系密切,目前的分库分表产品均使用水平拆分方式。
水平拆分则是根据分片算法将一个库(表)拆分为多个库(表)。如:按照id的最后一位以3取余,尾数是1的放入第1个库(表),尾数是2的放入第2个库(表)等。
单纯的分表虽然可以解决数据量过大导致检索变慢的问题,但无法解决过多并发请求访问同一个库,导致数据库响应变慢的问题。所以通常水平拆分都至少要采用分库的方式,用于一并解决大数据量和高并发的问题。这也是部分开源的分片数据库中间件只支持分库的原因。
但分表也有不可替代的适用场景。最常见的分表需求是事务问题。同在一个库则不需考虑分布式事务,善于使用同库不同表可有效避免分布式事务带来的麻烦。目前强一致性的分布式事务由于性能问题,导致使用起来并不一定比不分库分表快。目前采用最终一致性的柔性事务居多。分表的另一个存在的理由是,过多的数据库实例不利于运维管理。综上所述,最佳实践是合理地配合使用分库+分表。
sharding-jdbc简介
sharding-jdbc是当当应用框架ddframe中,从关系型数据库模块dd-rdb中分离出来的数据库水平分片框架,实现透明化数据库分库分表访问。sharding-jdbc是继dubbox和elastic-job之后,ddframe系列开源的第3个项目。
定位为轻量级java框架,在java的jdbc层提供的额外服务。 它使用客户端直连数据库,以jar包形式提供服务,无需额外部署和依赖,可理解为增强版的jdbc驱动,完全兼容jdbc和各种orm框架。
sql解析功能完善,支持聚合、分组、排序、limit、or等查询,并支持binding table以及笛卡尔积表查询。
项目实践
数据准备
准备两个数据库。并在两个库中建好表, 建表sql如下:
drop table if exists `user_auth_0`; create table `user_auth_0` ( `user_id` bigint(20) not null, `add_date` datetime not null default current_timestamp, `email` varchar(16) default null, `password` varchar(255) default null, `phone` varchar(16) default null, `remark` varchar(16) default null, primary key (`user_id`), unique key `user_auth_phone` (`phone`), unique key `user_auth_email` (`email`) ) engine=innodb default charset=utf8mb4; drop table if exists `user_auth_1`; create table `user_auth_1` ( `user_id` bigint(20) not null, `add_date` datetime not null default current_timestamp, `email` varchar(16) default null, `password` varchar(255) default null, `phone` varchar(16) default null, `remark` varchar(16) default null, primary key (`user_id`), unique key `user_auth_phone` (`phone`), unique key `user_auth_email` (`email`) ) engine=innodb default charset=utf8mb4;
pom配置
<dependency> <groupid>org.springframework.boot</groupid> <artifactid>spring-boot-starter</artifactid> </dependency> <dependency> <groupid>org.springframework.boot</groupid> <artifactid>spring-boot-devtools</artifactid> <scope>runtime</scope> </dependency> <dependency> <groupid>org.projectlombok</groupid> <artifactid>lombok</artifactid> <optional>true</optional> </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> <!-- 引入jpa--> <dependency> <groupid>org.springframework.boot</groupid> <artifactid>spring-boot-starter-data-jpa</artifactid> </dependency> <!-- 引入mysql--> <dependency> <groupid>mysql</groupid> <artifactid>mysql-connector-java</artifactid> </dependency> <!-- druid --> <dependency> <groupid>com.alibaba</groupid> <artifactid>druid</artifactid> <version>1.1.9</version> </dependency> <!-- sharding-jdbc --> <dependency> <groupid>com.dangdang</groupid> <artifactid>sharding-jdbc-core</artifactid> <version>1.5.4</version> </dependency> <!-- fastjson --> <dependency> <groupid>com.alibaba</groupid> <artifactid>fastjson</artifactid> <version>1.2.51</version> </dependency>
application.yml配置
spring: jpa: properties: hibernate: dialect: org.hibernate.dialect.mysql5innodbdialect show-sql: true database0: driverclassname: com.mysql.jdbc.driver url: jdbc:mysql://localhost:3306/mazhq?servertimezone=utc&useunicode=true&characterencoding=utf-8 username: root password: 123456 databasename: mazhq database1: driverclassname: com.mysql.jdbc.driver url: jdbc:mysql://localhost:3306/liugh?servertimezone=utc&useunicode=true&characterencoding=utf-8 username: root password: 123456 databasename: liugh
分库分表最主要有几个配置
1. 有多少个数据源 (2个:database0和database1)
@data @configurationproperties(prefix = "database0") @component public class database0config { private string url; private string username; private string password; private string driverclassname; private string databasename; public datasource createdatasource() { druiddatasource result = new druiddatasource(); result.setdriverclassname(getdriverclassname()); result.seturl(geturl()); result.setusername(getusername()); result.setpassword(getpassword()); return result; } }
2. 用什么列进行分库以及分库算法 (一般是用具体值对2取余判断入哪个库,我采用的是判断值是否大于20)
@component public class databaseshardingalgorithm implements singlekeydatabaseshardingalgorithm<long> { @autowired private database0config database0config; @autowired private database1config database1config; @override public string doequalsharding(collection<string> collection, shardingvalue<long> shardingvalue) { long value = shardingvalue.getvalue(); if (value <= 20l) { return database0config.getdatabasename(); } else { return database1config.getdatabasename(); } } @override public collection<string> doinsharding(collection<string> availabletargetnames, shardingvalue<long> shardingvalue) { collection<string> result = new linkedhashset<>(availabletargetnames.size()); for (long value : shardingvalue.getvalues()) { if (value <= 20l) { result.add(database0config.getdatabasename()); } else { result.add(database1config.getdatabasename()); } } return result; } @override public collection<string> dobetweensharding(collection<string> availabletargetnames, shardingvalue<long> shardingvalue) { collection<string> result = new linkedhashset<>(availabletargetnames.size()); range<long> range = shardingvalue.getvaluerange(); for (long value = range.lowerendpoint(); value <= range.upperendpoint(); value++) { if (value <= 20l) { result.add(database0config.getdatabasename()); } else { result.add(database1config.getdatabasename()); } } return result; } }
3. 用什么列进行分表以及分表算法
@component public class tableshardingalgorithm implements singlekeytableshardingalgorithm<long> { @override public string doequalsharding(collection<string> tablenames, shardingvalue<long> shardingvalue) { for (string each : tablenames) { if (each.endswith(shardingvalue.getvalue() % 2 + "")) { return each; } } throw new illegalargumentexception(); } @override public collection<string> doinsharding(collection<string> tablenames, shardingvalue<long> shardingvalue) { collection<string> result = new linkedhashset<>(tablenames.size()); for (long value : shardingvalue.getvalues()) { for (string tablename : tablenames) { if (tablename.endswith(value % 2 + "")) { result.add(tablename); } } } return result; } @override public collection<string> dobetweensharding(collection<string> tablenames, shardingvalue<long> shardingvalue) { collection<string> result = new linkedhashset<>(tablenames.size()); range<long> range = shardingvalue.getvaluerange(); for (long i = range.lowerendpoint(); i <= range.upperendpoint(); i++) { for (string each : tablenames) { if (each.endswith(i % 2 + "")) { result.add(each); } } } return result; } }
4. 每张表的逻辑表名和所有物理表名和集成调用
@configuration public class datasourceconfig { @autowired private database0config database0config; @autowired private database1config database1config; @autowired private databaseshardingalgorithm databaseshardingalgorithm; @autowired private tableshardingalgorithm tableshardingalgorithm; @bean public datasource getdatasource() throws sqlexception { return builddatasource(); } private datasource builddatasource() throws sqlexception { //分库设置 map<string, datasource> datasourcemap = new hashmap<>(2); //添加两个数据库database0和database1 datasourcemap.put(database0config.getdatabasename(), database0config.createdatasource()); datasourcemap.put(database1config.getdatabasename(), database1config.createdatasource()); //设置默认数据库 datasourcerule datasourcerule = new datasourcerule(datasourcemap, database0config.getdatabasename()); //分表设置,大致思想就是将查询虚拟表goods根据一定规则映射到真实表中去 tablerule ordertablerule = tablerule.builder("user_auth") .actualtables(arrays.aslist("user_auth_0", "user_auth_1")) .datasourcerule(datasourcerule) .build(); //分库分表策略 shardingrule shardingrule = shardingrule.builder() .datasourcerule(datasourcerule) .tablerules(arrays.aslist(ordertablerule)) .databaseshardingstrategy(new databaseshardingstrategy("user_id", databaseshardingalgorithm)) .tableshardingstrategy(new tableshardingstrategy("user_id", tableshardingalgorithm)).build(); datasource datasource = shardingdatasourcefactory.createdatasource(shardingrule); return datasource; } @bean public keygenerator keygenerator() { return new defaultkeygenerator(); }
接口测试代码
1、实体类
/** * @author mazhq * @date 2019/7/30 16:41 */ @entity @data @table(name = "user_auth", uniqueconstraints = {@uniqueconstraint(name = "user_auth_phone", columnnames = {"phone"}), @uniqueconstraint(name = "user_auth_email", columnnames = {"email"})}) public class userauthentity implements serializable { private static final long serialversionuid = 7230052310725727465l; @id private long userid; @column(name = "phone", length = 16) private string phone; @column(name = "email", length = 16) private string email; private string password; @column(name = "remark",length = 16) private string remark; @column(name = "add_date", nullable = false, columndefinition = "datetime default now()") private date adddate; }
2. dao层
@repository public interface userauthdao extends jparepository<userauthentity, long> { }
3. controller层
/** * @author mazhq * @title: userauthcontroller * @date 2019/8/1 17:18 */ @restcontroller @requestmapping("/user") public class userauthcontroller { @autowired private userauthdao userauthdao; @postmapping("/save") public string save(){ for (int i=0;i<40;i++) { userauthentity userauthentity = new userauthentity(); userauthentity.setuserid((long)i); userauthentity.setadddate(new date()); userauthentity.setemail("test"+i+"@163.com"); userauthentity.setpassword("123456"); userauthentity.setphone("1388888888"+i); random r = new random(); userauthentity.setremark(""+r.nextint(100)); userauthdao.save(userauthentity); } return "success"; } @postmapping("/select") public string select(){ return jsonobject.tojsonstring(userauthdao.findall(sort.by(sort.order.desc("remark")))); } }
测试方式:
先调用:http://localhost:8080/user/save
再查询:http://localhost:8080/user/select
git地址:
以上就是本文的全部内容,希望对大家的学习有所帮助,也希望大家多多支持移动技术网。
如对本文有疑问, 点击进行留言回复!!
Unity UGUI 之 实现按钮 Button 长按和双击的功能效果
[U3D Learning Note] Unity C# Surival Guide (1) -- Quick Tips and Assets
网友评论