购买网站做友情链接,广东中山今天最新通知,想做电商从哪里入手,搭建一个网站的基本流程分库分表背景: 数据库性能瓶颈#xff1a;主要分为按照业务来划分或者按照数据量来划分。 拆分方式#xff1a; 水平拆分(每个表的结构都一样)#xff1a;订单表数据量大#xff0c;我们可以水平拆分 #xff0c;分成order表1、order表2、order表3 。。。 垂直拆分#x… 分库分表背景: 数据库性能瓶颈主要分为按照业务来划分或者按照数据量来划分。 拆分方式 水平拆分(每个表的结构都一样)订单表数据量大我们可以水平拆分 分成order表1、order表2、order表3 。。。 垂直拆分一个多字段的表拆分成多个表 例如order订单表和oderItem订单详情表 一个订单会购买多件商品因此订单order表中会只有一条数据orderItem订单项表会对应这个订单购买的多件商品。 文章目录技术选型1. 引入 Maven 依赖2. 规则配置3. 实体4. 接口5. 表结构6. 测试类7. 完整pom技术选型
组件/框架版本spring-boot2.4.3jpa2.4.3shardingsphere5.0.0-alphamysql5.7.3hikari3.4.5
分库分表
官网文档 https://shardingsphere.apache.org/document/current/cn/user-manual/shardingsphere-jdbc/usage/sharding/spring-boot-starter/
1. 引入 Maven 依赖 dependencygroupIdorg.apache.shardingsphere/groupIdartifactIdshardingsphere-jdbc-core-spring-boot-starter/artifactIdversion5.0.0-alpha/version/dependency2. 规则配置
#大多数企业选择方案: 一个库分表 场景一个客户多个订单 按照order_id 分表# 配置真实数据源
spring.shardingsphere.datasource.namesds0spring.shardingsphere.datasource.common.typecom.zaxxer.hikari.HikariDataSource
spring.shardingsphere.datasource.common.driver-class-namecom.mysql.jdbc.Driver
spring.shardingsphere.datasource.common.usernameroot
spring.shardingsphere.datasource.common.passwordrootspring.shardingsphere.datasource.ds_0.jdbc-urljdbc:mysql://localhost:3306/ds0?serverTimezoneUTCuseSSLfalse# 配置 t_order 表规则
spring.shardingsphere.rules.sharding.tables.t_order.actual-data-nodesds0.t_order_$-{0..1}# 配置分表策略
spring.shardingsphere.rules.sharding.tables.t_order.table-strategy.standard.sharding-columnorder_id
spring.shardingsphere.rules.sharding.tables.t_order.table-strategy.standard.sharding-algorithm-nametable-inline# 配置 分片算法
spring.shardingsphere.rules.sharding.sharding-algorithms.table-inline.typeINLINE
spring.shardingsphere.rules.sharding.sharding-algorithms.table-inline.props.algorithm-expressiont_order_$-{order_id % 2}# 分片算法配置
spring.shardingsphere.rules.sharding.tables.t_order.key-generate-strategy.columnorder_id
spring.shardingsphere.rules.sharding.tables.t_order.key-generate-strategy.key-generator-namesnowflake# 分布式序列算法配置
spring.shardingsphere.rules.sharding.key-generators.snowflake.typeSNOWFLAKE
spring.shardingsphere.rules.sharding.key-generators.snowflake.props.worker-id123# 具体的属性配置
spring.shardingsphere.props.sql-showtrue
3. 实体
package com.gblfy.distributedsharding.entity;import lombok.Data;import javax.persistence.*;Data
Entity
Table(name t_order)
public class OrderEntity {IdGeneratedValue(strategy GenerationType.IDENTITY)private Long orderId;private Integer userId;
}4. 接口
package com.gblfy.distributedsharding.mapper;import com.gblfy.distributedsharding.entity.OrderEntity;
import org.springframework.data.jpa.repository.JpaRepository;import java.util.List;public interface OrderMapper extends JpaRepositoryOrderEntity, Long {OrderEntity findByOrderId(Long orderId);ListOrderEntity findByUserId(Integer userId);
}
5. 表结构
CREATE DATABASE ds0;
use ds0;
CREATE TABLE t_order_0 (order_id bigint(20) unsigned NOT NULL,user_id int(11) DEFAULT NULL,PRIMARY KEY (order_id)
) ENGINEInnoDB DEFAULT CHARSETutf8mb4;CREATE TABLE t_order_1 (order_id bigint(20) unsigned NOT NULL,user_id int(11) DEFAULT NULL,PRIMARY KEY (order_id)
) ENGINEInnoDB DEFAULT CHARSETutf8mb4;6. 测试类
package com.gblfy.distributedsharding;import com.gblfy.distributedsharding.entity.OrderEntity;
import com.gblfy.distributedsharding.mapper.OrderMapper;
import org.junit.jupiter.api.Test;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;import java.util.Random;SpringBootTest
class DistributedShardingApplicationTests {Autowiredprivate OrderMapper orderMapper;Testvoid insert() {OrderEntity entity new OrderEntity();entity.setUserId(new Random().nextInt(999));orderMapper.save(entity);}Testvoid findByOrderId() {//按照order_id分表 会查询1次通过order_id分表orderMapper.findByOrderId(570271967295811584L);}Testvoid findByUserId() {//会查询2次知道user_id但是不知道哪个表orderMapper.findByUserId(556);}Testvoid updateByOrderId() {OrderEntity byOrderId orderMapper.findByOrderId(570279923689172992L);byOrderId.setUserId(1000);orderMapper.save(byOrderId);}
}7. 完整pom
dependenciesdependencygroupIdorg.springframework.boot/groupIdartifactIdspring-boot-starter-data-jpa/artifactId/dependencydependencygroupIdmysql/groupIdartifactIdmysql-connector-java/artifactIdscoperuntime/scope/dependencydependencygroupIdorg.projectlombok/groupIdartifactIdlombok/artifactIdoptionaltrue/optional/dependencydependencygroupIdorg.springframework.boot/groupIdartifactIdspring-boot-starter-test/artifactIdscopetest/scope/dependencydependencygroupIdorg.apache.shardingsphere/groupIdartifactIdshardingsphere-jdbc-core-spring-boot-starter/artifactIdversion5.0.0-alpha/version/dependency
