国际网站建设工具深圳网站建设者

国际网站建设工具,深圳网站建设者,公司建网站多少钱一个月,wordpress 实现注册《SpringBoot整合Sharding-JDBC全面教程#xff1a;常用 API 串联与实战指南》 你想要在SpringBoot项目中规范、高效地整合Sharding-JDBC#xff0c;掌握其分库分表、读写分离等核心功能的常用API#xff0c;并遵循行业最佳实践#xff0c;这份教程会帮你系统落地。 一、核…《SpringBoot整合Sharding-JDBC全面教程常用 API 串联与实战指南》你想要在SpringBoot项目中规范、高效地整合Sharding-JDBC掌握其分库分表、读写分离等核心功能的常用API并遵循行业最佳实践这份教程会帮你系统落地。一、核心前置说明1. 版本选型最佳实践选稳定兼容版JDK1.8Sharding-JDBC 5.x对JDK8兼容性最好SpringBoot2.7.x避免3.x初期兼容性问题Sharding-JDBC5.4.0Apache ShardingSphere 5.x稳定版含JDBC模块2. 依赖引入Mavendependencies!-- SpringBoot核心 --dependencygroupIdorg.springframework.boot/groupIdartifactIdspring-boot-starter-web/artifactId/dependency!-- SpringBoot数据访问 --dependencygroupIdorg.springframework.boot/groupIdartifactIdspring-boot-starter-data-jpa/artifactId/dependency!-- Sharding-JDBC核心依赖 --dependencygroupIdorg.apache.shardingsphere/groupIdartifactIdshardingsphere-jdbc-core-spring-boot-starter/artifactIdversion5.4.0/version/dependency!-- MySQL驱动 --dependencygroupIdmysql/groupIdartifactIdmysql-connector-java/artifactIdscoperuntime/scopeversion8.0.33/version/dependency!-- 数据源HikariCPSpringBoot默认性能最优 --dependencygroupIdcom.zaxxer/groupIdartifactIdHikariCP/artifactId/dependency!-- 测试 --dependencygroupIdorg.springframework.boot/groupIdartifactIdspring-boot-starter-test/artifactIdscopetest/scope/dependency/dependencies二、核心概念速览新手必懂分片键用于分库/分表的字段如订单ID、用户ID分片策略按规则将数据路由到指定库/表如取模、范围、哈希逻辑表分表的抽象名称如t_order物理表是t_order_0、t_order_1读写分离主库写、从库读Sharding-JDBC自动路由三、实战场景1单库分表最常用1. 数据库准备创建数据库demo_db并创建2个订单分表CREATEDATABASEIFNOTEXISTSdemo_db;USEdemo_db;-- 订单逻辑表t_order的物理表t_order_0、t_order_1CREATETABLEt_order_0(order_idBIGINTPRIMARYKEY,user_idINTNOTNULL,order_amountDECIMAL(10,2)NOTNULL,create_timeDATETIMEDEFAULTCURRENT_TIMESTAMP);CREATETABLEt_order_1(order_idBIGINTPRIMARYKEY,user_idINTNOTNULL,order_amountDECIMAL(10,2)NOTNULL,create_timeDATETIMEDEFAULTCURRENT_TIMESTAMP);2. 核心配置application.yml遵循最佳实践配置分层清晰、策略明确、注释完整spring:# Sharding-JDBC核心配置shardingsphere:# 数据源配置datasource:names:ds0# 数据源名称单库ds0:type:com.zaxxer.hikari.HikariDataSourcedriver-class-name:com.mysql.cj.jdbc.Driverurl:jdbc:mysql://localhost:3306/demo_db?useUnicodetruecharacterEncodingutf8useSSLfalseserverTimezoneAsia/Shanghaiusername:root# 替换为你的数据库账号password:root# 替换为你的数据库密码# 分库分表规则rules:sharding:# 分片表配置tables:t_order:# 逻辑表名actual-data-nodes:ds0.t_order_${0..1}# 物理表节点ds0库下的t_order_0/1# 分片策略按order_id取模分表table-strategy:standard:sharding-column:order_id# 分片键sharding-algorithm-name:t_order_inline# 分片算法名称# 分片算法配置sharding-algorithms:t_order_inline:type:INLINE# 行内表达式算法简单高效适合取模/范围props:algorithm-expression:t_order_${order_id % 2}# 算法规则order_id%2决定表后缀# 属性配置打印SQL调试必备生产可关闭props:sql-show:true3. 代码编写最佳实践分层解耦1实体类Order.javaimportlombok.Data;importjavax.persistence.*;importjava.math.BigDecimal;importjava.time.LocalDateTime;DataEntityTable(namet_order)// 映射逻辑表名publicclassOrder{IdprivateLongorderId;// 分片键订单IDprivateIntegeruserId;// 用户IDprivateBigDecimalorderAmount;// 订单金额privateLocalDateTimecreateTime;// 创建时间}2RepositoryOrderRepository.javaimportorg.springframework.data.jpa.repository.JpaRepository;importorg.springframework.stereotype.Repository;RepositorypublicinterfaceOrderRepositoryextendsJpaRepositoryOrder,Long{// 继承JpaRepository无需手写基础CRUD}3ServiceOrderService.javaimportorg.springframework.stereotype.Service;importjavax.annotation.Resource;importjava.util.List;ServicepublicclassOrderService{ResourceprivateOrderRepositoryorderRepository;// 新增订单Sharding-JDBC自动路由到对应分表publicOrdersaveOrder(Orderorder){returnorderRepository.save(order);}// 查询所有订单Sharding-JDBC自动聚合所有分表数据publicListOrderlistAllOrders(){returnorderRepository.findAll();}// 根据订单ID查询Sharding-JDBC自动路由到对应分表publicOrdergetOrderById(LongorderId){returnorderRepository.findById(orderId).orElse(null);}}4测试类ShardingJdbcDemoApplicationTests.javaimportorg.junit.jupiter.api.Test;importorg.springframework.boot.test.context.SpringBootTest;importjavax.annotation.Resource;importjava.math.BigDecimal;importjava.time.LocalDateTime;SpringBootTestpublicclassShardingJdbcDemoApplicationTests{ResourceprivateOrderServiceorderService;TestpublicvoidtestSaveOrder(){// 测试订单1orderId1 → 路由到t_order_1Orderorder1newOrder();order1.setOrderId(1L);order1.setUserId(1001);order1.setOrderAmount(newBigDecimal(99.99));order1.setCreateTime(LocalDateTime.now());orderService.saveOrder(order1);// 测试订单2orderId2 → 路由到t_order_0Orderorder2newOrder();order2.setOrderId(2L);order2.setUserId(1002);order2.setOrderAmount(newBigDecimal(199.99));order2.setCreateTime(LocalDateTime.now());orderService.saveOrder(order2);// 查询验证System.out.println(订单1orderService.getOrderById(1L));System.out.println(订单2orderService.getOrderById(2L));System.out.println(所有订单orderService.listAllOrders());}}4. 运行结果验证控制台会打印Sharding-JDBC路由后的SQL比如INSERT INTO t_order_1 (order_id, user_id, order_amount, create_time) VALUES (1, 1001, 99.99, ...)INSERT INTO t_order_0 (order_id, user_id, order_amount, create_time) VALUES (2, 1002, 199.99, ...)数据库中t_order_1有订单1t_order_0有订单2查询时自动聚合结果。四、实战场景2分库分表进阶若需按user_id分库、order_id分表修改配置如下核心变化spring:shardingsphere:datasource:names:ds0,ds1# 两个数据源分库ds0:type:com.zaxxer.hikari.HikariDataSourcedriver-class-name:com.mysql.cj.jdbc.Driverurl:jdbc:mysql://localhost:3306/demo_db_0?useUnicodetruecharacterEncodingutf8useSSLfalseserverTimezoneAsia/Shanghaiusername:rootpassword:rootds1:type:com.zaxxer.hikari.HikariDataSourcedriver-class-name:com.mysql.cj.jdbc.Driverurl:jdbc:mysql://localhost:3306/demo_db_1?useUnicodetruecharacterEncodingutf8useSSLfalseserverTimezoneAsia/Shanghaiusername:rootpassword:rootrules:sharding:tables:t_order:actual-data-nodes:ds${0..1}.t_order_${0..1}# 两个库每个库2个表# 分库策略按user_id取模database-strategy:standard:sharding-column:user_idsharding-algorithm-name:t_order_db_inline# 分表策略按order_id取模table-strategy:standard:sharding-column:order_idsharding-algorithm-name:t_order_table_inlinesharding-algorithms:t_order_db_inline:type:INLINEprops:algorithm-expression:ds${user_id % 2}t_order_table_inline:type:INLINEprops:algorithm-expression:t_order_${order_id % 2}props:sql-show:true五、实战场景3读写分离最佳实践主从架构spring:shardingsphere:datasource:names:master,slave0# 主库从库master:type:com.zaxxer.hikari.HikariDataSourcedriver-class-name:com.mysql.cj.jdbc.Driverurl:jdbc:mysql://localhost:3306/demo_db?useUnicodetruecharacterEncodingutf8useSSLfalseserverTimezoneAsia/Shanghaiusername:rootpassword:rootslave0:type:com.zaxxer.hikari.HikariDataSourcedriver-class-name:com.mysql.cj.jdbc.Driverurl:jdbc:mysql://localhost:3307/demo_db?useUnicodetruecharacterEncodingutf8useSSLfalseserverTimezoneAsia/Shanghaiusername:rootpassword:rootrules:readwrite-splitting:data-sources:demo_ds:# 读写分离数据源名称type:STATIC# 静态读写分离固定主从props:write-data-source-name:master# 写库read-data-source-names:slave0# 读库多个用逗号分隔load-balancer-name:round_robin# 负载均衡算法轮询load-balancers:round_robin:type:ROUND_ROBIN# 轮询算法简单高效props:sql-show:true六、常用API与最佳实践1. 自定义分片算法进阶场景若内置算法不满足需求可自定义分片算法importorg.apache.shardingsphere.sharding.api.sharding.standard.PreciseShardingAlgorithm;importorg.apache.shardingsphere.sharding.api.sharding.standard.PreciseShardingValue;importorg.springframework.stereotype.Component;importjava.util.Collection;// 自定义精确分片算法按order_id范围分表ComponentpublicclassCustomOrderShardingAlgorithmimplementsPreciseShardingAlgorithmLong{OverridepublicStringdoSharding(CollectionStringavailableTargetNames,PreciseShardingValueLongshardingValue){LongorderIdshardingValue.getValue();// 自定义规则order_id1000 → t_order_0否则→t_order_1if(orderId1000){returnt_order_0;}else{returnt_order_1;}}}配置中替换算法类型为CUSTOM指定算法类sharding-algorithms:t_order_custom:type:CUSTOMprops:sharding-algorithm-class-name:com.example.demo.algorithm.CustomOrderShardingAlgorithm2. 分布式ID生成最佳实践避免主键冲突Sharding-JDBC内置分布式ID生成器配置如下spring:shardingsphere:rules:sharding:key-generators:snowflake:# 雪花算法推荐type:SNOWFLAKEprops:worker-id:1# 工作节点ID集群需唯一tables:t_order:key-generate-strategy:column:order_id# 主键字段key-generator-name:snowflake# 关联雪花算法3. 最佳实践总结避坑指南分片键选择优先选查询频繁、值均匀的字段如订单ID避免用范围字段如时间作为唯一分片键易数据倾斜。避免跨库/跨表JOIN分库分表后JOIN性能极差尽量通过业务设计规避。生产环境关闭sql-show避免日志冗余影响性能。事务支持分库事务需用Sharding-JDBC的XA事务或BASE事务单库事务无特殊配置。七、核心测试与验证功能验证通过单元测试验证数据路由是否正确如不同分片键的值是否落到指定库/表。性能验证压测分库分表后的读写性能对比单库单表确保分片后性能提升。异常验证测试分片键为空、值超出范围等场景确保程序有异常处理。总结SpringBoot整合Sharding-JDBC的核心是数据源配置分片规则算法配置优先使用INLINE算法简单高效复杂场景自定义算法。最佳实践分片键选均匀字段、避免跨库JOIN、用雪花算法生成分布式ID、生产关闭SQL打印。常见场景优先级单库分表入门→ 分库分表进阶→ 读写分离高可用按需落地。