SpringBoot第33讲:SpringBoot集成ShardingJDBC - 基于JPA的读写分离

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、Entity
      • 2.3、DAO
      • 2.4、Service
      • 2.5、Controller
      • 2.6、简单测试
    • 3、进一步理解
      • 3.1、shardingJDBC的主从分离解决不了什么问题?
      • 3.2、读写分离加分库分表?
    • 4、示例源码

1、知识准备

主要理解 ShardingJDBC 针对读写分离库的场景和设计目标等。

1.1、读写分离库的场景和设计目标?

透明化读写分离所带来的影响,让使用方尽量像使用一个数据库一样使用主从数据库集群,是ShardingSphere读写分离模块的主要设计目标。

面对日益增加的系统访问量,数据库的吞吐量面临着巨大瓶颈。 对于同一时刻有大量并发读操作和较少写操作类型的应用系统来说,将数据库拆分为主库和从库,主库负责处理事务性的增删改操作,从库负责处理查询操作,能够有效的避免由数据更新导致的行锁,使得整个系统的查询性能得到极大的改善。

通过一主多从的配置方式,可以将查询请求均匀的分散到多个数据副本,能够进一步的提升系统的处理能力。 使用多主多从的方式,不但能够提升系统的吞吐量,还能够提升系统的可用性,可以达到在任何一个数据库宕机,甚至磁盘物理损坏的情况下仍然不影响系统的正常运行。

与将数据根据分片键打散至各个数据节点的水平分片不同,读写分离则是根据SQL语义的分析,将读操作和写操作分别路由至主库与从库。

img

读写分离的数据节点中的数据内容是一致的,而水平分片的每个数据节点的数据内容却并不相同。将水平分片和读写分离联合使用,能够更加有效的提升系统性能

读写分离虽然可以提升系统的吞吐量和可用性,但同时也带来了数据不一致的问题。 这包括多个主库之间的数据一致性,以及主库与从库之间的数据一致性的问题。 并且,读写分离也带来了与数据分片同样的问题,它同样会使得应用开发和运维人员对数据库的操作和运维变得更加复杂。 下图展现了将分库分表与读写分离一同使用时,应用程序与数据库集群之间的复杂拓扑关系。

img

详细可参考这篇文章: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`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8;
/*!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`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8;
/*!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','1172814226@qq.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
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
/*!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`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8;
/*!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`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8;
/*!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
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
/*!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 的依赖。

<dependency><groupId>mysql</groupId><artifactId>mysql-connector-java</artifactId><version>8.0.28</version>
</dependency>
<dependency><groupId>com.github.wenhao</groupId><artifactId>jpa-spec</artifactId><version>3.1.0</version>
</dependency>
<dependency><groupId>org.springframework.boot</groupId><artifactId>spring-boot-starter-data-jpa</artifactId>
</dependency>
<dependency><groupId>org.apache.shardingsphere</groupId><artifactId>sharding-jdbc-spring-boot-starter</artifactId><version>4.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?allowPublicKeyRetrieval=true&useSSL=false&autoReconnect=true&characterEncoding=utf8username: rootpassword: qwj930828slave0:type: com.zaxxer.hikari.HikariDataSourcedriver-class-name: com.mysql.cj.jdbc.Driverjdbc-url: jdbc:mysql://localhost:3306/db_user_sharding_slave?allowPublicKeyRetrieval=true&useSSL=false&autoReconnect=true&characterEncoding=utf8username: 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: false

2.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.*/@Id@GeneratedValue(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 Set<Role> 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.*/@Id@GeneratedValue(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 IBaseDao<User, 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 IBaseDao<Role, 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 IBaseService<User, Long> {/*** find by page.** @param userQueryBean query* @param pageRequest   pageRequest* @return page*/Page<User> 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 BaseDoServiceImpl<User, 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 IBaseDao<User, Long> getBaseDao() {return this.userDao;}/*** find by page.** @param queryBean   query* @param pageRequest pageRequest* @return page*/@Overridepublic Page<User> findPage(UserQueryBean queryBean, PageRequest pageRequest) {Specification<User> specification = Specifications.<User>and().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 IBaseService<Role, Long> {/*** find page by query.** @param roleQueryBean query* @param pageRequest   pageRequest* @return page*/Page<Role> 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 BaseDoServiceImpl<Role, 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 IBaseDao<Role, Long> getBaseDao() {return this.roleDao;}/*** find page by query.** @param roleQueryBean query* @param pageRequest   pageRequest* @return page*/@Overridepublic Page<Role> findPage(RoleQueryBean roleQueryBean, PageRequest pageRequest) {Specification<Role> specification = Specifications.<Role>and().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 ResponseResult<User> 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 ResponseResult<User> edit(@PathVariable("userId") Long userId) {return ResponseResult.success(userService.find(userId));}/*** @return user list*/@ApiOperation("Query User Page")@GetMapping("list")public ResponseResult<Page<User>> 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(sqlStatement=org.apache.shardingsphere.sql.parser.sql.statement.dml.InsertStatement@550b49d4, tablesContext=org.apache.shardingsphere.sql.parser.binder.segment.table.TablesContext@4ef3f621)
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_.id=roles1_.user_id left outer join tb_role role2_ on roles1_.role_id=role2_.id where user0_.id=?
2023-08-05 16:48:10.859  INFO 36856 --- [nio-8081-exec-7] ShardingSphere-SQL                       : SQLStatement: SelectStatementContext(super=CommonSQLStatementContext(sqlStatement=org.apache.shardingsphere.sql.parser.sql.statement.dml.SelectStatement@60866f71, tablesContext=org.apache.shardingsphere.sql.parser.binder.segment.table.TablesContext@488f6585), tablesContext=org.apache.shardingsphere.sql.parser.binder.segment.table.TablesContext@488f6585, projectionsContext=ProjectionsContext(startIndex=7, stopIndex=541, distinctRow=false, projections=[ColumnProjection(owner=user0_, name=id, alias=Optional[id1_1_0_]), ColumnProjection(owner=user0_, name=create_time, alias=Optional[create_t2_1_0_]), ColumnProjection(owner=user0_, name=description, alias=Optional[descript3_1_0_]), ColumnProjection(owner=user0_, name=email, alias=Optional[email4_1_0_]), ColumnProjection(owner=user0_, name=password, alias=Optional[password5_1_0_]), ColumnProjection(owner=user0_, name=phone_number, alias=Optional[phone_nu6_1_0_]), ColumnProjection(owner=user0_, name=update_time, alias=Optional[update_t7_1_0_]), ColumnProjection(owner=user0_, name=user_name, alias=Optional[user_nam8_1_0_]), ColumnProjection(owner=roles1_, name=user_id, alias=Optional[user_id1_2_1_]), ColumnProjection(owner=role2_, name=id, alias=Optional[role_id2_2_1_]), ColumnProjection(owner=role2_, name=id, alias=Optional[id1_0_2_]), ColumnProjection(owner=role2_, name=create_time, alias=Optional[create_t2_0_2_]), ColumnProjection(owner=role2_, name=description, alias=Optional[descript3_0_2_]), ColumnProjection(owner=role2_, name=name, alias=Optional[name4_0_2_]), ColumnProjection(owner=role2_, name=role_key, alias=Optional[role_key5_0_2_]), ColumnProjection(owner=role2_, name=update_time, alias=Optional[update_t6_0_2_])]), groupByContext=org.apache.shardingsphere.sql.parser.binder.segment.select.groupby.GroupByContext@70473bc5, orderByContext=org.apache.shardingsphere.sql.parser.binder.segment.select.orderby.OrderByContext@54fe8172, paginationContext=org.apache.shardingsphere.sql.parser.binder.segment.select.pagination.PaginationContext@750817b, containsSubquery=false)
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_.id=roles1_.user_id left outer join tb_role role2_ on roles1_.role_id=role2_.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_.id=roles1_.user_id left outer join tb_role role2_ on roles1_.role_id=role2_.id where user0_.id=?
2023-08-05 18:25:55.317  INFO 36856 --- [nio-8081-exec-9] ShardingSphere-SQL                       : SQLStatement: SelectStatementContext(super=CommonSQLStatementContext(sqlStatement=org.apache.shardingsphere.sql.parser.sql.statement.dml.SelectStatement@60866f71, tablesContext=org.apache.shardingsphere.sql.parser.binder.segment.table.TablesContext@194a9ab4), tablesContext=org.apache.shardingsphere.sql.parser.binder.segment.table.TablesContext@194a9ab4, projectionsContext=ProjectionsContext(startIndex=7, stopIndex=541, distinctRow=false, projections=[ColumnProjection(owner=user0_, name=id, alias=Optional[id1_1_0_]), ColumnProjection(owner=user0_, name=create_time, alias=Optional[create_t2_1_0_]), ColumnProjection(owner=user0_, name=description, alias=Optional[descript3_1_0_]), ColumnProjection(owner=user0_, name=email, alias=Optional[email4_1_0_]), ColumnProjection(owner=user0_, name=password, alias=Optional[password5_1_0_]), ColumnProjection(owner=user0_, name=phone_number, alias=Optional[phone_nu6_1_0_]), ColumnProjection(owner=user0_, name=update_time, alias=Optional[update_t7_1_0_]), ColumnProjection(owner=user0_, name=user_name, alias=Optional[user_nam8_1_0_]), ColumnProjection(owner=roles1_, name=user_id, alias=Optional[user_id1_2_1_]), ColumnProjection(owner=role2_, name=id, alias=Optional[role_id2_2_1_]), ColumnProjection(owner=role2_, name=id, alias=Optional[id1_0_2_]), ColumnProjection(owner=role2_, name=create_time, alias=Optional[create_t2_0_2_]), ColumnProjection(owner=role2_, name=description, alias=Optional[descript3_0_2_]), ColumnProjection(owner=role2_, name=name, alias=Optional[name4_0_2_]), ColumnProjection(owner=role2_, name=role_key, alias=Optional[role_key5_0_2_]), ColumnProjection(owner=role2_, name=update_time, alias=Optional[update_t6_0_2_])]), groupByContext=org.apache.shardingsphere.sql.parser.binder.segment.select.groupby.GroupByContext@2d70c572, orderByContext=org.apache.shardingsphere.sql.parser.binder.segment.select.orderby.OrderByContext@714045df, paginationContext=org.apache.shardingsphere.sql.parser.binder.segment.select.pagination.PaginationContext@4f9cbea8, containsSubquery=false)
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_.id=roles1_.user_id left outer join tb_role role2_ on roles1_.role_id=role2_.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

本文来自互联网用户投稿,该文观点仅代表作者本人,不代表本站立场。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如若转载,请注明出处:http://www.rhkb.cn/news/80290.html

如若内容造成侵权/违法违规/事实不符,请联系长河编程网进行投诉反馈email:809451989@qq.com,一经查实,立即删除!

相关文章

uni-app:实现点击按钮出现底部弹窗(uni.showActionSheet+自定义)

一、通过uni.showActionSheet实现底部选择 效果 代码 <template><view><button click"showActionsheet">点击打开弹窗</button></view> </template><script> export default {methods: {showActionsheet() {uni.showAct…

消息队列项目(2)

我们使用 SQLite 来进行对 Exchange, Queue, Binding 的硬盘保存 对 Message 就保存在硬盘的文本中 SQLite 封装 这里是在 application.yaml 中来引进对 SQLite 的封装 spring:datasource:url: jdbc:sqlite:./data/meta.dbusername:password:driver-class-name: org.sqlite.…

Python 中的机器学习简介:多项式回归

一、说明 多项式回归可以识别自变量和因变量之间的非线性关系。本文是关于回归、梯度下降和 MSE 系列文章的第三篇。前面的文章介绍了简单线性回归、回归的正态方程和多元线性回归。 二、多项式回归 多项式回归用于最适合曲线拟合的复杂数据。它可以被视为多元线性回归的子集。…

APP外包开发的学习流程

学习iOS App的开发是一项有趣和富有挑战性的任务&#xff0c;是一个不断学习和不断进步的过程。掌握基础知识后&#xff0c;不断实践和尝试新的项目将使您的技能不断提升。下面和大家分享一些建议&#xff0c;可以帮助您开始学习iOS App的开发。北京木奇移动技术有限公司&#…

DAY02_Spring—第三方资源配置管理Spring容器Spring注解开发Spring整合Mybatis和Junit

目录 一 第三方资源配置管理1 管理DataSource连接池对象问题导入1.1 管理Druid连接池1.2 管理c3p0连接池 2 加载properties属性文件问题导入2.1 基本用法2.2 配置不加载系统属性2.3 加载properties文件写法 二 Spring容器1 Spring核心容器介绍问题导入1.1 创建容器1.2 获取bean…

软件架构师思维塑造

一、软件系统设计的六项原则 1、单一职责原则&#xff08;Single Responsibility Principle&#xff09; 2、开闭原则&#xff08;Open Closed Principle&#xff09; 3、里氏替换原则&#xff08;Liskov Substitution Principle&#xff09; 4、迪米特法则&#xff08;Law of …

微服务 云原生:基于 Gogs + Drone 进行项目 CI/CD

传统构建部署 以一个简单的前后端项目来说&#xff0c;分别编写前后端的 Dockerfile 文件并构建镜像&#xff0c;然后编写 docker-compose.yml 构建部署&#xff0c;启动运行。 一个简单的例子&#xff1a; 前端&#xff1a; 项目名&#xff1a;kubemanagement-web技术栈&am…

Java中Date方法详解

先进行专栏介绍 本专栏是自己学Java的旅途&#xff0c;纯手敲的代码&#xff0c;自己跟着黑马课程学习的&#xff0c;并加入一些自己的理解&#xff0c;对代码和笔记 进行适当修改。希望能对大家能有所帮助&#xff0c;同时也是请大家对我进行监督&#xff0c;对我写的代码进行…

eeglab(自用)

目录 1.加载、显示数据 2.绘制脑电头皮图 3.绘制通道光谱图 4.预处理工具 5.ICA去除伪迹 5. 提取数据epoch 1.加载、显示数据 观察事件值(Event values)&#xff1a;该数据集中包含2400个事件&#xff0c;每个事件指定了EEG.event结构的字段Type(类型)、position(位置)和…

【Linux命令详解 | cat命令】Linux系统中用于显示或连接文件的命令

文章标题 简介一&#xff0c;参数列表二&#xff0c;使用介绍1. 显示文件内容2. 创建文件3. 连接文件4. 显示行号5. 压缩空行6. 显示特殊字符7. 显示行号和特殊字符8. 从标准输入读取9. 显示文件开头或结尾10. 备份文件11. 显示文件内容至多屏幕大小12. 转义正则表达式13. 显示…

java文件

一.File类 二.扫描指定目录&#xff0c;并找到名称中包含指定字符的所有普通文件&#xff08;不包含目录&#xff09;&#xff0c;并且后续询问用户是否要删除该文件 我的代码: import java.io.File; import java.io.IOException; import java.util.Scanner;public class Tes…

瑞芯微RK3568核心板-4G联网测试

​ &#x1f308;引言 RK3568是瑞芯微针对AIOT和工业市场推出的一款高性能、低功耗、功能丰富的应用处理器。它采用了四核ARM架构64位Cortex-A55处理器&#xff0c;主频高达2.0GHz&#xff0c;集成瑞芯微自研1TOPS算力NPU, 同时集成Mali-G52 2EE GPU&#xff0c;支持4K60fps …

React Native连接Zebra斑马打印机通过发送CPCL指令打印(Android 和 iOS通用)

自 2015 年发布以来&#xff0c;React Native 已成为用于构建数千个移动应用程序的流行跨平台移动开发框架之一。通常&#xff0c;我们有开发人员询问如何将 Link-OS SDK 与 React Native 应用程序集成&#xff0c;以便在 Zebra 打印机上打印标签。在本教程中&#xff0c;我们将…

深度学习常用的激活函数

深度学习的核心思想是通过多层次的神经网络结构&#xff0c;逐步抽取和表示数据中的高级特征&#xff0c;从而实现对复杂数据模式的学习和识别。 神经网络结构&#xff1a; 深度学习使用多层次的神经网络&#xff0c;包括输入层、隐藏层和输出层。这些网络结构允许模型自动学习…

一、Webpack相关(包括webpack-dev-server用以热更新和html-webpack-plugin)

概念与功能&#xff1a; webpack是前端项目工程化的具体解决方案。它提供了友好的前端模块化开发支持&#xff0c;以及代码压缩混淆、处理浏览器端JavaScript的兼容性、性能优化等强大的功能。 快速上手&#xff1a;隔行变色 -S实际是--save的简写&#xff0c;表示安装的第三方…

以太网DHCP协议(十)

目录 一、工作原理 二、DHCP报文 2.1 DHCP报文类型 2.2 DHCP报文格式 当网络内部的主机设备数量过多是&#xff0c;IP地址的手动设置是一件非常繁琐的事情。为了实现自动设置IP地址、统一管理IP地址分配&#xff0c;TCPIP协议栈中引入了DHCP协议。 一、工作原理 使用DHCP之…

Java 并发容器和框架Fork/Join详解

目 录 一 使用场景 1 大规模数据处理 2 复杂计算 3 并行搜索 4 并行排序 二 Fork/Join框架介绍 三 Fork/Join框架模块 四 Fork/Join框架核心思想 1分治思想(Divide-and-Conquer) 2 work-stealing(工作窃取)算法 五 Fork/Join框架执行流程 1 实现原理&#xff1a; 2…

消息队列(3) -封装数据库的操作

前言 上一篇博客我们写了, 关于交换机, 队列,绑定, 写入数据库的一些建库建表的操作 这一篇博客中,我们将建库建表操作,封装一下实现层一个类来供上层服务的调用 , 并在写完该类之后, 测试代码是否完整 实现封装 在写完上述的接口类 与 xml 后, 我们想要 创建一个类 ,来调用…

智慧城市规划新引擎:探秘数字孪生中的二维与三维GIS技术差异

智慧城市作为人类社会发展的新阶段&#xff0c;正日益引领着我们迈向数字化未来的时代。在智慧城市的建设过程中&#xff0c;地理信息系统&#xff08;GIS&#xff09;扮演着举足轻重的角色。而在GIS的发展中&#xff0c;二维和三维GIS作为两大核心技术&#xff0c;在城市规划与…

thinkphp8.0多应用模式下提示控制器不存在

thinkphp 8.0 开启多应用模式 1、按照官方文档说明 &#xff0c;已经安装了 think-multi-app composer require topthink/think-multi-app 2、控制器的命名空间也没写错。 3、访问路径与目录名、控制器、方法名一样&#xff0c;访问地址是没错的。 4、网上有说&#xff0c;在…