百度网站官网入口,贵州网站建设,如何将自己做的网站导入淘宝,做网站的公司挣钱吗SpringBoot第33讲#xff1a;SpringBoot集成ShardingJDBC - 基于JPA的读写分离 本文是SpringBoot第33讲#xff0c;主要介绍分表分库#xff0c;以及SpringBoot集成基于 ShardingJDBC 的读写分离实践 文章目录 SpringBoot第33讲#xff1a;SpringBoot集成ShardingJDBC - 基…SpringBoot第33讲SpringBoot集成ShardingJDBC - 基于JPA的读写分离 本文是SpringBoot第33讲主要介绍分表分库以及SpringBoot集成基于 ShardingJDBC 的读写分离实践 文章目录 SpringBoot第33讲SpringBoot集成ShardingJDBC - 基于JPA的读写分离1、知识准备1.1、读写分离库的场景和设计目标1.2、核心功能 2、简单示例2.1、准备DB和依赖配置2.2、Entity2.3、DAO2.4、Service2.5、Controller2.6、简单测试 3、进一步理解3.1、shardingJDBC的主从分离解决不了什么问题3.2、读写分离加分库分表 4、示例源码 1、知识准备 主要理解 ShardingJDBC 针对读写分离库的场景和设计目标等。 1.1、读写分离库的场景和设计目标 透明化读写分离所带来的影响让使用方尽量像使用一个数据库一样使用主从数据库集群是ShardingSphere读写分离模块的主要设计目标。 面对日益增加的系统访问量数据库的吞吐量面临着巨大瓶颈。 对于同一时刻有大量并发读操作和较少写操作类型的应用系统来说将数据库拆分为主库和从库主库负责处理事务性的增删改操作从库负责处理查询操作能够有效的避免由数据更新导致的行锁使得整个系统的查询性能得到极大的改善。
通过一主多从的配置方式可以将查询请求均匀的分散到多个数据副本能够进一步的提升系统的处理能力。 使用多主多从的方式不但能够提升系统的吞吐量还能够提升系统的可用性可以达到在任何一个数据库宕机甚至磁盘物理损坏的情况下仍然不影响系统的正常运行。
与将数据根据分片键打散至各个数据节点的水平分片不同读写分离则是根据SQL语义的分析将读操作和写操作分别路由至主库与从库。 读写分离的数据节点中的数据内容是一致的而水平分片的每个数据节点的数据内容却并不相同。将水平分片和读写分离联合使用能够更加有效的提升系统性能。
读写分离虽然可以提升系统的吞吐量和可用性但同时也带来了数据不一致的问题。 这包括多个主库之间的数据一致性以及主库与从库之间的数据一致性的问题。 并且读写分离也带来了与数据分片同样的问题它同样会使得应用开发和运维人员对数据库的操作和运维变得更加复杂。 下图展现了将分库分表与读写分离一同使用时应用程序与数据库集群之间的复杂拓扑关系。 详细可参考这篇文章MySQL第七讲MySQL分库分表详解
1.2、核心功能
提供一主多从的读写分离配置可独立使用也可配合分库分表使用。独立使用读写分离支持SQL透传。同一线程且同一数据库连接内如有写入操作以后的读操作均从主库读取用于保证数据一致性。基于Hint的强制主库路由。
2、简单示例 这里主要介绍SpringBoot集成基于ShardingJDBC的读写分离和数据分片实践主要承接之前的相关文章在JPA方式的基础上实现的。 2.1、准备DB和依赖配置
创建MySQL的schema db_user_sharding_master 和 db_user_sharding_slave, 导入SQL 文件如下
db_user_sharding_master
DROP TABLE IF EXISTS tb_role;
/*!40101 SET saved_cs_client character_set_client */;
/*!50503 SET character_set_client utf8mb4 */;
CREATE TABLE tb_role (id int NOT NULL AUTO_INCREMENT,name varchar(255) NOT NULL,role_key varchar(255) NOT NULL,description varchar(255) DEFAULT NULL,create_time datetime DEFAULT NULL,update_time datetime DEFAULT NULL,PRIMARY KEY (id)
) ENGINEInnoDB AUTO_INCREMENT2 DEFAULT CHARSETutf8;
/*!40101 SET character_set_client saved_cs_client */;--
-- Dumping data for table tb_role
--LOCK TABLES tb_role WRITE;
/*!40000 ALTER TABLE tb_role DISABLE KEYS */;
/*!40000 ALTER TABLE tb_role ENABLE KEYS */;
UNLOCK TABLES;--
-- Table structure for table tb_user
--DROP TABLE IF EXISTS tb_user;
/*!40101 SET saved_cs_client character_set_client */;
/*!50503 SET character_set_client utf8mb4 */;
CREATE TABLE tb_user (id int NOT NULL AUTO_INCREMENT,user_name varchar(45) NOT NULL,password varchar(45) NOT NULL,email varchar(45) DEFAULT NULL,phone_number int DEFAULT NULL,description varchar(255) DEFAULT NULL,create_time datetime DEFAULT NULL,update_time datetime DEFAULT NULL,PRIMARY KEY (id)
) ENGINEInnoDB AUTO_INCREMENT3 DEFAULT CHARSETutf8;
/*!40101 SET character_set_client saved_cs_client */;--
-- Dumping data for table tb_user
--LOCK TABLES tb_user WRITE;
/*!40000 ALTER TABLE tb_user DISABLE KEYS */;
INSERT INTO tb_user VALUES (2,qiwenjie,aaa,1172814226qq.com,123133332,qwj2,2022-04-06 20:44:34,2022-04-06 20:44:34);
/*!40000 ALTER TABLE tb_user ENABLE KEYS */;
UNLOCK TABLES;--
-- Table structure for table tb_user_role
--DROP TABLE IF EXISTS tb_user_role;
/*!40101 SET saved_cs_client character_set_client */;
/*!50503 SET character_set_client utf8mb4 */;
CREATE TABLE tb_user_role (user_id int NOT NULL,role_id int NOT NULL
) ENGINEInnoDB DEFAULT CHARSETutf8;
/*!40101 SET character_set_client saved_cs_client */;--
-- Dumping data for table tb_user_role
--LOCK TABLES tb_user_role WRITE;
/*!40000 ALTER TABLE tb_user_role DISABLE KEYS */;
/*!40000 ALTER TABLE tb_user_role ENABLE KEYS */;
UNLOCK TABLES;db_user_sharding_slave
DROP TABLE IF EXISTS tb_role;
/*!40101 SET saved_cs_client character_set_client */;
/*!50503 SET character_set_client utf8mb4 */;
CREATE TABLE tb_role (id int NOT NULL AUTO_INCREMENT,name varchar(255) NOT NULL,role_key varchar(255) NOT NULL,description varchar(255) DEFAULT NULL,create_time datetime DEFAULT NULL,update_time datetime DEFAULT NULL,PRIMARY KEY (id)
) ENGINEInnoDB AUTO_INCREMENT2 DEFAULT CHARSETutf8;
/*!40101 SET character_set_client saved_cs_client */;--
-- Dumping data for table tb_role
--LOCK TABLES tb_role WRITE;
/*!40000 ALTER TABLE tb_role DISABLE KEYS */;
/*!40000 ALTER TABLE tb_role ENABLE KEYS */;
UNLOCK TABLES;--
-- Table structure for table tb_user
--DROP TABLE IF EXISTS tb_user;
/*!40101 SET saved_cs_client character_set_client */;
/*!50503 SET character_set_client utf8mb4 */;
CREATE TABLE tb_user (id int NOT NULL AUTO_INCREMENT,user_name varchar(45) NOT NULL,password varchar(45) NOT NULL,email varchar(45) DEFAULT NULL,phone_number int DEFAULT NULL,description varchar(255) DEFAULT NULL,create_time datetime DEFAULT NULL,update_time datetime DEFAULT NULL,PRIMARY KEY (id)
) ENGINEInnoDB AUTO_INCREMENT3 DEFAULT CHARSETutf8;
/*!40101 SET character_set_client saved_cs_client */;--
-- Dumping data for table tb_user
--LOCK TABLES tb_user WRITE;
/*!40000 ALTER TABLE tb_user DISABLE KEYS */;
INSERT INTO tb_user VALUES (2,qwj-salve,xxx,xx,12111,qwj,2022-04-06 20:44:34,2022-04-06 20:44:34);
/*!40000 ALTER TABLE tb_user ENABLE KEYS */;
UNLOCK TABLES;--
-- Table structure for table tb_user_role
--DROP TABLE IF EXISTS tb_user_role;
/*!40101 SET saved_cs_client character_set_client */;
/*!50503 SET character_set_client utf8mb4 */;
CREATE TABLE tb_user_role (user_id int NOT NULL,role_id int NOT NULL
) ENGINEInnoDB DEFAULT CHARSETutf8;
/*!40101 SET character_set_client saved_cs_client */;--
-- Dumping data for table tb_user_role
--LOCK TABLES tb_user_role WRITE;
/*!40000 ALTER TABLE tb_user_role DISABLE KEYS */;
/*!40000 ALTER TABLE tb_user_role ENABLE KEYS */;
UNLOCK TABLES;引入maven依赖, 包含mysql驱动JPA包, 以及 sharding-jdbc 的依赖。
dependencygroupIdmysql/groupIdartifactIdmysql-connector-java/artifactIdversion8.0.28/version
/dependency
dependencygroupIdcom.github.wenhao/groupIdartifactIdjpa-spec/artifactIdversion3.1.0/version
/dependency
dependencygroupIdorg.springframework.boot/groupIdartifactIdspring-boot-starter-data-jpa/artifactId
/dependency
dependencygroupIdorg.apache.shardingsphere/groupIdartifactIdsharding-jdbc-spring-boot-starter/artifactIdversion4.1.1/version
/dependency增加yml配置
spring:shardingsphere:datasource:names: master,slave0master:type: com.zaxxer.hikari.HikariDataSourcedriver-class-name: com.mysql.cj.jdbc.Driverjdbc-url: jdbc:mysql://localhost:3306/db_user_sharding_master?allowPublicKeyRetrievaltrueuseSSLfalseautoReconnecttruecharacterEncodingutf8username: rootpassword: qwj930828slave0:type: com.zaxxer.hikari.HikariDataSourcedriver-class-name: com.mysql.cj.jdbc.Driverjdbc-url: jdbc:mysql://localhost:3306/db_user_sharding_slave?allowPublicKeyRetrievaltrueuseSSLfalseautoReconnecttruecharacterEncodingutf8username: rootpassword: qwj930828sharding:tables:tb_user:database-strategy:inline:sharding-column: idalgorithm-expression: masterkey-generator:column: idtype: SNOWFLAKEprops:worker:id: 123tb_role:database-strategy:inline:sharding-column: idalgorithm-expression: masterkey-generator:column: idtype: SNOWFLAKEprops:worker:id: 123tb_user_role:database-strategy:inline:sharding-column: idalgorithm-expression: masterkey-generator:column: idtype: SNOWFLAKEprops:worker:id: 123master-slave:name: msload-balance-algorithm-type: round_robinmaster-data-source-name: masterslave-data-source-names: slave0props:sql:show: truejpa:open-in-view: falsegenerate-ddl: falseshow-sql: falseproperties:hibernate:dialect: org.hibernate.dialect.MySQLDialectformat_sql: trueuse-new-id-generator-mappings: false2.2、Entity
user entity
package springboot.shardingjdbc.jpa.masterslave.entity;import java.time.LocalDateTime;
import java.util.Set;
import javax.persistence.CascadeType;
import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.FetchType;
import javax.persistence.GeneratedValue;
import javax.persistence.GenerationType;
import javax.persistence.Id;
import javax.persistence.JoinColumn;
import javax.persistence.JoinTable;
import javax.persistence.ManyToMany;
import javax.persistence.Table;
import lombok.Getter;
import lombok.Setter;
import lombok.ToString;/*** author qiwenjie*/
Getter
Setter
ToString
Entity
Table(name tb_user)
public class User implements BaseEntity {/*** user id.*/IdGeneratedValue(strategy GenerationType.IDENTITY)Column(name id, nullable false)private Long id;/*** username.*/private String userName;/*** user pwd.*/private String password;/*** email.*/private String email;/*** phoneNumber.*/private long phoneNumber;/*** description.*/private String description;/*** create date time.*/private LocalDateTime createTime;/*** update date time.*/private LocalDateTime updateTime;/*** join to role table.*/ManyToMany(cascade {CascadeType.REFRESH}, fetch FetchType.EAGER)JoinTable(name tb_user_role, joinColumns {JoinColumn(name user_id)}, inverseJoinColumns {JoinColumn(name role_id)})private SetRole roles;
}role entity
package springboot.shardingjdbc.jpa.masterslave.entity;import java.time.LocalDateTime;
import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.GenerationType;
import javax.persistence.Id;
import javax.persistence.Table;
import lombok.Getter;
import lombok.Setter;
import lombok.ToString;/*** author qiwenjie*/
Getter
Setter
ToString
Entity
Table(name tb_role)
public class Role implements BaseEntity {/*** role id.*/IdGeneratedValue(strategy GenerationType.IDENTITY)Column(name id, nullable false)private Long id;/*** role name.*/private String name;/*** role key.*/private String roleKey;/*** description.*/private String description;/*** create date time.*/private LocalDateTime createTime;/*** update date time.*/private LocalDateTime updateTime;
}2.3、DAO
user dao
package springboot.shardingjdbc.jpa.masterslave.dao;import org.springframework.stereotype.Repository;
import springboot.shardingjdbc.jpa.masterslave.entity.User;/*** author qiwenjie*/
Repository
public interface IUserDao extends IBaseDaoUser, Long {}role dao
package springboot.shardingjdbc.jpa.masterslave.dao;import org.springframework.stereotype.Repository;
import springboot.shardingjdbc.jpa.masterslave.entity.Role;/*** author qiwenjie*/
Repository
public interface IRoleDao extends IBaseDaoRole, Long {}2.4、Service
user service 接口
package springboot.shardingjdbc.jpa.masterslave.service;import org.springframework.data.domain.Page;
import org.springframework.data.domain.PageRequest;
import springboot.shardingjdbc.jpa.masterslave.entity.User;
import springboot.shardingjdbc.jpa.masterslave.entity.query.UserQueryBean;/*** author qiwenjie*/
public interface IUserService extends IBaseServiceUser, Long {/*** find by page.** param userQueryBean query* param pageRequest pageRequest* return page*/PageUser findPage(UserQueryBean userQueryBean, PageRequest pageRequest);
}user service 实现类
package springboot.shardingjdbc.jpa.masterslave.service.impl;import com.github.wenhao.jpa.Specifications;
import org.apache.commons.lang3.StringUtils;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.PageRequest;
import org.springframework.data.jpa.domain.Specification;
import org.springframework.stereotype.Service;
import springboot.shardingjdbc.jpa.masterslave.dao.IBaseDao;
import springboot.shardingjdbc.jpa.masterslave.dao.IUserDao;
import springboot.shardingjdbc.jpa.masterslave.entity.User;
import springboot.shardingjdbc.jpa.masterslave.entity.query.UserQueryBean;
import springboot.shardingjdbc.jpa.masterslave.service.IUserService;Service
public class UserDoServiceImpl extends BaseDoServiceImplUser, Long implements IUserService {/*** userDao.*/private final IUserDao userDao;/*** init.** param userDao2 user dao*/public UserDoServiceImpl(final IUserDao userDao2) {this.userDao userDao2;}/*** return base dao*/Overridepublic IBaseDaoUser, Long getBaseDao() {return this.userDao;}/*** find by page.** param queryBean query* param pageRequest pageRequest* return page*/Overridepublic PageUser findPage(UserQueryBean queryBean, PageRequest pageRequest) {SpecificationUser specification Specifications.Userand().like(StringUtils.isNotEmpty(queryBean.getName()), user_name, queryBean.getName()).like(StringUtils.isNotEmpty(queryBean.getDescription()), description,queryBean.getDescription()).build();return this.getBaseDao().findAll(specification, pageRequest);}
}role service 接口
package springboot.shardingjdbc.jpa.masterslave.service;import org.springframework.data.domain.Page;
import org.springframework.data.domain.PageRequest;
import springboot.shardingjdbc.jpa.masterslave.entity.Role;
import springboot.shardingjdbc.jpa.masterslave.entity.query.RoleQueryBean;public interface IRoleService extends IBaseServiceRole, Long {/*** find page by query.** param roleQueryBean query* param pageRequest pageRequest* return page*/PageRole findPage(RoleQueryBean roleQueryBean, PageRequest pageRequest);
}role service 实现类
package springboot.shardingjdbc.jpa.masterslave.service.impl;import com.github.wenhao.jpa.Specifications;
import org.apache.commons.lang3.StringUtils;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.PageRequest;
import org.springframework.data.jpa.domain.Specification;
import org.springframework.stereotype.Service;
import springboot.shardingjdbc.jpa.masterslave.dao.IBaseDao;
import springboot.shardingjdbc.jpa.masterslave.dao.IRoleDao;
import springboot.shardingjdbc.jpa.masterslave.entity.Role;
import springboot.shardingjdbc.jpa.masterslave.entity.query.RoleQueryBean;
import springboot.shardingjdbc.jpa.masterslave.service.IRoleService;Service
public class RoleDoServiceImpl extends BaseDoServiceImplRole, Long implements IRoleService {/*** roleDao.*/private final IRoleDao roleDao;/*** init.** param roleDao2 role dao*/public RoleDoServiceImpl(final IRoleDao roleDao2) {this.roleDao roleDao2;}/*** return base dao*/Overridepublic IBaseDaoRole, Long getBaseDao() {return this.roleDao;}/*** find page by query.** param roleQueryBean query* param pageRequest pageRequest* return page*/Overridepublic PageRole findPage(RoleQueryBean roleQueryBean, PageRequest pageRequest) {SpecificationRole specification Specifications.Roleand().like(StringUtils.isNotEmpty(roleQueryBean.getName()), name,roleQueryBean.getName()).like(StringUtils.isNotEmpty(roleQueryBean.getDescription()), description,roleQueryBean.getDescription()).build();return this.roleDao.findAll(specification, pageRequest);}
}2.5、Controller
user controller
package springboot.shardingjdbc.jpa.masterslave.controller;import java.time.LocalDateTime;
import io.swagger.annotations.ApiOperation;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.PageRequest;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.PathVariable;
import org.springframework.web.bind.annotation.PostMapping;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.bind.annotation.RestController;
import springboot.shardingjdbc.jpa.masterslave.entity.User;
import springboot.shardingjdbc.jpa.masterslave.entity.query.UserQueryBean;
import springboot.shardingjdbc.jpa.masterslave.entity.response.ResponseResult;
import springboot.shardingjdbc.jpa.masterslave.service.IUserService;/*** author qiwenjie*/
RestController
RequestMapping(/user)
public class UserController {Autowiredprivate IUserService userService;/*** param user user param* return user*/ApiOperation(Add/Edit User)PostMapping(add)public ResponseResultUser add(User user) {if (user.getId()null || !userService.exists(user.getId())) {user.setCreateTime(LocalDateTime.now());user.setUpdateTime(LocalDateTime.now());userService.save(user);} else {user.setUpdateTime(LocalDateTime.now());userService.update(user);}return ResponseResult.success(userService.find(user.getId()));}/*** return user list*/ApiOperation(Query User One)GetMapping(edit/{userId})public ResponseResultUser edit(PathVariable(userId) Long userId) {return ResponseResult.success(userService.find(userId));}/*** return user list*/ApiOperation(Query User Page)GetMapping(list)public ResponseResultPageUser list(RequestParam int pageSize, RequestParam int pageNumber) {return ResponseResult.success(userService.findPage(UserQueryBean.builder().build(), PageRequest.of(pageNumber, pageSize)));}
}2.6、简单测试
访问页面
http://localhost:8080/doc.html 插入数据写入master库
(注意主库和从库的数据同步不是shardingJDBC做的需要自行同步) 查询数据从slave中查询
查询结果
slave db中的数据
相关查询console打印出的日志
// 如下是插入
2023-08-05 16:48:10.814 INFO 36856 --- [nio-8081-exec-7] ShardingSphere-SQL : Logic SQL: insert into tb_user (create_time, description, email, password, phone_number, update_time, user_name) values (?, ?, ?, ?, ?, ?, ?)
2023-08-05 16:48:10.814 INFO 36856 --- [nio-8081-exec-7] ShardingSphere-SQL : SQLStatement: CommonSQLStatementContext(sqlStatementorg.apache.shardingsphere.sql.parser.sql.statement.dml.InsertStatement550b49d4, tablesContextorg.apache.shardingsphere.sql.parser.binder.segment.table.TablesContext4ef3f621)
2023-08-05 16:48:10.814 INFO 36856 --- [nio-8081-exec-7] ShardingSphere-SQL : Actual SQL: master ::: insert into tb_user (create_time, description, email, password, phone_number, update_time, user_name) values (?, ?, ?, ?, ?, ?, ?)
2023-08-05 16:48:10.859 INFO 36856 --- [nio-8081-exec-7] ShardingSphere-SQL : Logic SQL: select user0_.id as id1_1_0_, user0_.create_time as create_t2_1_0_, user0_.description as descript3_1_0_, user0_.email as email4_1_0_, user0_.password as password5_1_0_, user0_.phone_number as phone_nu6_1_0_, user0_.update_time as update_t7_1_0_, user0_.user_name as user_nam8_1_0_, roles1_.user_id as user_id1_2_1_, role2_.id as role_id2_2_1_, role2_.id as id1_0_2_, role2_.create_time as create_t2_0_2_, role2_.description as descript3_0_2_, role2_.name as name4_0_2_, role2_.role_key as role_key5_0_2_, role2_.update_time as update_t6_0_2_ from tb_user user0_ left outer join tb_user_role roles1_ on user0_.idroles1_.user_id left outer join tb_role role2_ on roles1_.role_idrole2_.id where user0_.id?
2023-08-05 16:48:10.859 INFO 36856 --- [nio-8081-exec-7] ShardingSphere-SQL : SQLStatement: SelectStatementContext(superCommonSQLStatementContext(sqlStatementorg.apache.shardingsphere.sql.parser.sql.statement.dml.SelectStatement60866f71, tablesContextorg.apache.shardingsphere.sql.parser.binder.segment.table.TablesContext488f6585), tablesContextorg.apache.shardingsphere.sql.parser.binder.segment.table.TablesContext488f6585, projectionsContextProjectionsContext(startIndex7, stopIndex541, distinctRowfalse, projections[ColumnProjection(owneruser0_, nameid, aliasOptional[id1_1_0_]), ColumnProjection(owneruser0_, namecreate_time, aliasOptional[create_t2_1_0_]), ColumnProjection(owneruser0_, namedescription, aliasOptional[descript3_1_0_]), ColumnProjection(owneruser0_, nameemail, aliasOptional[email4_1_0_]), ColumnProjection(owneruser0_, namepassword, aliasOptional[password5_1_0_]), ColumnProjection(owneruser0_, namephone_number, aliasOptional[phone_nu6_1_0_]), ColumnProjection(owneruser0_, nameupdate_time, aliasOptional[update_t7_1_0_]), ColumnProjection(owneruser0_, nameuser_name, aliasOptional[user_nam8_1_0_]), ColumnProjection(ownerroles1_, nameuser_id, aliasOptional[user_id1_2_1_]), ColumnProjection(ownerrole2_, nameid, aliasOptional[role_id2_2_1_]), ColumnProjection(ownerrole2_, nameid, aliasOptional[id1_0_2_]), ColumnProjection(ownerrole2_, namecreate_time, aliasOptional[create_t2_0_2_]), ColumnProjection(ownerrole2_, namedescription, aliasOptional[descript3_0_2_]), ColumnProjection(ownerrole2_, namename, aliasOptional[name4_0_2_]), ColumnProjection(ownerrole2_, namerole_key, aliasOptional[role_key5_0_2_]), ColumnProjection(ownerrole2_, nameupdate_time, aliasOptional[update_t6_0_2_])]), groupByContextorg.apache.shardingsphere.sql.parser.binder.segment.select.groupby.GroupByContext70473bc5, orderByContextorg.apache.shardingsphere.sql.parser.binder.segment.select.orderby.OrderByContext54fe8172, paginationContextorg.apache.shardingsphere.sql.parser.binder.segment.select.pagination.PaginationContext750817b, containsSubqueryfalse)
2023-08-05 16:48:10.860 INFO 36856 --- [nio-8081-exec-7] ShardingSphere-SQL : Actual SQL: slave0 ::: select user0_.id as id1_1_0_, user0_.create_time as create_t2_1_0_, user0_.description as descript3_1_0_, user0_.email as email4_1_0_, user0_.password as password5_1_0_, user0_.phone_number as phone_nu6_1_0_, user0_.update_time as update_t7_1_0_, user0_.user_name as user_nam8_1_0_, roles1_.user_id as user_id1_2_1_, role2_.id as role_id2_2_1_, role2_.id as id1_0_2_, role2_.create_time as create_t2_0_2_, role2_.description as descript3_0_2_, role2_.name as name4_0_2_, role2_.role_key as role_key5_0_2_, role2_.update_time as update_t6_0_2_ from tb_user user0_ left outer join tb_user_role roles1_ on user0_.idroles1_.user_id left outer join tb_role role2_ on roles1_.role_idrole2_.id where user0_.id?// 如下是查询
2023-08-05 18:25:55.299 INFO 36856 --- [nio-8081-exec-9] ShardingSphere-SQL : Logic SQL: select user0_.id as id1_1_0_, user0_.create_time as create_t2_1_0_, user0_.description as descript3_1_0_, user0_.email as email4_1_0_, user0_.password as password5_1_0_, user0_.phone_number as phone_nu6_1_0_, user0_.update_time as update_t7_1_0_, user0_.user_name as user_nam8_1_0_, roles1_.user_id as user_id1_2_1_, role2_.id as role_id2_2_1_, role2_.id as id1_0_2_, role2_.create_time as create_t2_0_2_, role2_.description as descript3_0_2_, role2_.name as name4_0_2_, role2_.role_key as role_key5_0_2_, role2_.update_time as update_t6_0_2_ from tb_user user0_ left outer join tb_user_role roles1_ on user0_.idroles1_.user_id left outer join tb_role role2_ on roles1_.role_idrole2_.id where user0_.id?
2023-08-05 18:25:55.317 INFO 36856 --- [nio-8081-exec-9] ShardingSphere-SQL : SQLStatement: SelectStatementContext(superCommonSQLStatementContext(sqlStatementorg.apache.shardingsphere.sql.parser.sql.statement.dml.SelectStatement60866f71, tablesContextorg.apache.shardingsphere.sql.parser.binder.segment.table.TablesContext194a9ab4), tablesContextorg.apache.shardingsphere.sql.parser.binder.segment.table.TablesContext194a9ab4, projectionsContextProjectionsContext(startIndex7, stopIndex541, distinctRowfalse, projections[ColumnProjection(owneruser0_, nameid, aliasOptional[id1_1_0_]), ColumnProjection(owneruser0_, namecreate_time, aliasOptional[create_t2_1_0_]), ColumnProjection(owneruser0_, namedescription, aliasOptional[descript3_1_0_]), ColumnProjection(owneruser0_, nameemail, aliasOptional[email4_1_0_]), ColumnProjection(owneruser0_, namepassword, aliasOptional[password5_1_0_]), ColumnProjection(owneruser0_, namephone_number, aliasOptional[phone_nu6_1_0_]), ColumnProjection(owneruser0_, nameupdate_time, aliasOptional[update_t7_1_0_]), ColumnProjection(owneruser0_, nameuser_name, aliasOptional[user_nam8_1_0_]), ColumnProjection(ownerroles1_, nameuser_id, aliasOptional[user_id1_2_1_]), ColumnProjection(ownerrole2_, nameid, aliasOptional[role_id2_2_1_]), ColumnProjection(ownerrole2_, nameid, aliasOptional[id1_0_2_]), ColumnProjection(ownerrole2_, namecreate_time, aliasOptional[create_t2_0_2_]), ColumnProjection(ownerrole2_, namedescription, aliasOptional[descript3_0_2_]), ColumnProjection(ownerrole2_, namename, aliasOptional[name4_0_2_]), ColumnProjection(ownerrole2_, namerole_key, aliasOptional[role_key5_0_2_]), ColumnProjection(ownerrole2_, nameupdate_time, aliasOptional[update_t6_0_2_])]), groupByContextorg.apache.shardingsphere.sql.parser.binder.segment.select.groupby.GroupByContext2d70c572, orderByContextorg.apache.shardingsphere.sql.parser.binder.segment.select.orderby.OrderByContext714045df, paginationContextorg.apache.shardingsphere.sql.parser.binder.segment.select.pagination.PaginationContext4f9cbea8, containsSubqueryfalse)
2023-08-05 18:25:55.321 INFO 36856 --- [nio-8081-exec-9] ShardingSphere-SQL : Actual SQL: slave0 ::: select user0_.id as id1_1_0_, user0_.create_time as create_t2_1_0_, user0_.description as descript3_1_0_, user0_.email as email4_1_0_, user0_.password as password5_1_0_, user0_.phone_number as phone_nu6_1_0_, user0_.update_time as update_t7_1_0_, user0_.user_name as user_nam8_1_0_, roles1_.user_id as user_id1_2_1_, role2_.id as role_id2_2_1_, role2_.id as id1_0_2_, role2_.create_time as create_t2_0_2_, role2_.description as descript3_0_2_, role2_.name as name4_0_2_, role2_.role_key as role_key5_0_2_, role2_.update_time as update_t6_0_2_ from tb_user user0_ left outer join tb_user_role roles1_ on user0_.idroles1_.user_id left outer join tb_role role2_ on roles1_.role_idrole2_.id where user0_.id?3、进一步理解 通过几个问题进一步理解。 3.1、shardingJDBC的主从分离解决不了什么问题
主库和从库的数据同步。主库和从库的数据同步延迟导致的数据不一致。主库双写或多写。
3.2、读写分离加分库分表
可以参考官方给的如下配置
dataSources:ds0: !!org.apache.commons.dbcp.BasicDataSourcedriverClassName: com.mysql.jdbc.Driverurl: jdbc:mysql://localhost:3306/ds0username: rootpassword: ds0_slave0: !!org.apache.commons.dbcp.BasicDataSourcedriverClassName: com.mysql.jdbc.Driverurl: jdbc:mysql://localhost:3306/ds0_slave0username: rootpassword: ds0_slave1: !!org.apache.commons.dbcp.BasicDataSourcedriverClassName: com.mysql.jdbc.Driverurl: jdbc:mysql://localhost:3306/ds0_slave1username: rootpassword: ds1: !!org.apache.commons.dbcp.BasicDataSourcedriverClassName: com.mysql.jdbc.Driverurl: jdbc:mysql://localhost:3306/ds1username: rootpassword: ds1_slave0: !!org.apache.commons.dbcp.BasicDataSourcedriverClassName: com.mysql.jdbc.Driverurl: jdbc:mysql://localhost:3306/ds1_slave0username: rootpassword: ds1_slave1: !!org.apache.commons.dbcp.BasicDataSourcedriverClassName: com.mysql.jdbc.Driverurl: jdbc:mysql://localhost:3306/ds1_slave1username: rootpassword: shardingRule: tables:t_order: actualDataNodes: ms_ds${0..1}.t_order${0..1}databaseStrategy:inline:shardingColumn: user_idalgorithmExpression: ms_ds${user_id % 2}tableStrategy: inline:shardingColumn: order_idalgorithmExpression: t_order${order_id % 2}keyGenerator:type: SNOWFLAKEcolumn: order_idt_order_item:actualDataNodes: ms_ds${0..1}.t_order_item${0..1}databaseStrategy:inline:shardingColumn: user_idalgorithmExpression: ms_ds${user_id % 2}tableStrategy:inline:shardingColumn: order_idalgorithmExpression: t_order_item${order_id % 2} bindingTables:- t_order,t_order_itembroadcastTables:- t_configdefaultDataSourceName: ds0defaultTableStrategy:none:defaultKeyGenerator:type: SNOWFLAKEcolumn: order_idmasterSlaveRules:ms_ds0:masterDataSourceName: ds0slaveDataSourceNames:- ds0_slave0- ds0_slave1loadBalanceAlgorithmType: ROUND_ROBINms_ds1:masterDataSourceName: ds1slaveDataSourceNames: - ds1_slave0- ds1_slave1loadBalanceAlgorithmType: ROUND_ROBIN
props:sql.show: true!! 表示实例化该类
表示可以包含一个或多个
[] 表示数组可以与减号相互替换使用
4、示例源码
todo
