企业网站管理系统c,大学生网页设计大赛作品,新手建立企业网站流程,辽宁网站建设的网络科技公司分库分表背景: 数据库性能瓶颈#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. 规则配置
#分库分表 场景一个客户多个订单 按照user_id分库 按照order_id 分表# 配置真实数据源
spring.shardingsphere.datasource.namesds0,ds1##############################################################################
# 1.连接池 2.驱动 3.用户名 4.密码 5.连接url SpringBoot2.x写法
#############################################################################
spring.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.passwordroot
spring.shardingsphere.datasource.ds_0.jdbc-urljdbc:mysql://localhost:3306/ds0?serverTimezoneUTCuseSSLfalse
spring.shardingsphere.datasource.ds_1.jdbc-urljdbc:mysql://localhost:3306/ds1?serverTimezoneUTCuseSSLfalse# 配置 t_order 表规则
#ds$-{0..1} 指的是ds0 ds1 2个数据库
#t_order_$-{0..1} 指的是 t_order_0 t_order_1 2个表
#ds$-{0..1}.t_order_$-{0..1} 指的是ds0 ds1 2个数据库下面的t_order_0和t_order_1 2个表
spring.shardingsphere.rules.sharding.tables.t_order.actual-data-nodesds$-{0..1}.t_order_$-{0..1}# 配置分库策略
# user_id 指的是按照user_id进行分库
# database-inline 自定义策略名称 下面会用到
spring.shardingsphere.rules.sharding.tables.t_order.database-strategy.standard.sharding-columnuser_id
spring.shardingsphere.rules.sharding.tables.t_order.database-strategy.standard.sharding-algorithm-namedatabase-inline# 配置分表策略
#order_id 指的是按照user_id进行分表
#table-inline 自定义策略名称 下面会用到
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# 配置 分片算法
#分库分片算法 取模算法
#ds$-{user_id % 2} 指的是user_id与2取模
spring.shardingsphere.rules.sharding.sharding-algorithms.database-inline.typeINLINE
spring.shardingsphere.rules.sharding.sharding-algorithms.database-inline.props.algorithm-expressionds$-{user_id % 2}#分表分片算法 取模算法
#ds$-{order_id % 2} 指的是order_id与2取模
spring.shardingsphere.rules.sharding.sharding-algorithms.table-inline.typeINLINE
spring.shardingsphere.rules.sharding.sharding-algorithms.table-inline.props.algorithm-expressiont_order_$-{order_id % 2}# 分片算法配置
#order_id 生成规则
#snowflake 雪花算法
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;CREATE DATABASE ds1;
use ds1;
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分表 会查询2次通过order_id分表但是不知道哪个库orderMapper.findByOrderId(570271967295811584L);}Testvoid findByUserId() {//按照user_id分库 会查询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
