动态网站转静态网站,网站关键词优化公司哪家好,怎么获取网站数据做统计,婚纱礼服外贸网站最近在开发项目搭建框架时#xff0c;考虑后期支付模块的订单数据量可能会比较大#xff0c;于是使用现在主流的shardingsphere的读写分离、水平分表来解决后期数据量大影响查询效率的问题。
项目技术栈#xff1a;jdk17Springboot3.3.3shardingsphere-jdbc5.5.0mybatis-pl…最近在开发项目搭建框架时考虑后期支付模块的订单数据量可能会比较大于是使用现在主流的shardingsphere的读写分离、水平分表来解决后期数据量大影响查询效率的问题。
项目技术栈jdk17Springboot3.3.3shardingsphere-jdbc5.5.0mybatis-plus3.5.7mybatis-plus-generator3.5.9mysql8.0.20
1.报错解决方案
shardingsphere的官网上各个版本的配置有一些差异官网文档的使用也写得不全全靠看别人blog和查看源码对应去解决。特别注意自定义class所在resources的文件路为META-INF\services\org.apache.shardingsphere.infra.algorithm.keygen.core.KeyGenerateAlgorithm让我花了一天的时间才解决。 若resources下的包名和文件名不是上面标红所示会报错
报这个错其实主要是我的自定义生成主键策略类没有加载进来一起这个错搞我真死了好多脑细胞。
SPI-00001: No implementation class load from SPI org.apache.shardingsphere.infra.algorithm.keygen.core.KeyGenerateAlgorithm with type MY_KEY_GENERATE_ALGORITHM.
org.apache.shardingsphere.infra.spi.exception.ServiceProviderNotFoundException: SPI-00001: No implementation class load from SPI org.apache.shardingsphere.infra.algorithm.keygen.core.KeyGenerateAlgorithm with type MY_KEY_GENERATE_ALGORITHM.at org.apache.shardingsphere.infra.spi.type.typed.TypedSPILoader.checkService(TypedSPILoader.java:129) ~[shardingsphere-infra-spi-5.5.0.jar:5.5.0]at org.apache.shardingsphere.sharding.checker.ShardingRuleConfigurationChecker.lambda$checkKeyGeneratorAlgorithms$1(ShardingRuleConfigurationChecker.java:76) ~[shardingsphere-sharding-core-5.5.0.jar:5.5.0]at java.base/java.util.stream.ForEachOps$ForEachOp$OfRef.accept(ForEachOps.java:183) ~[na:na]at java.base/java.util.stream.ReferencePipeline$2$1.accept(ReferencePipeline.java:179) ~[na:na]at java.base/java.util.Iterator.forEachRemaining(Iterator.java:133) ~[na:na]at java.base/java.util.Spliterators$IteratorSpliterator.forEachRemaining(Spliterators.java:1845) ~[na:na]at java.base/java.util.stream.AbstractPipeline.copyInto(AbstractPipeline.java:509) ~[na:na]at java.base/java.util.stream.AbstractPipeline.wrapAndCopyInto(AbstractPipeline.java:499) ~[na:na]at java.base/java.util.stream.ForEachOps$ForEachOp.evaluateSequential(ForEachOps.java:150) ~[na:na]at java.base/java.util.stream.ForEachOps$ForEachOp$OfRef.evaluateSequential(ForEachOps.java:173) ~[na:na]at java.base/java.util.stream.AbstractPipeline.evaluate(AbstractPipeline.java:234) ~[na:na]at java.base/java.util.stream.ReferencePipeline.forEach(ReferencePipeline.java:596) ~[na:na]at org.apache.shardingsphere.sharding.checker.ShardingRuleConfigurationChecker.checkKeyGeneratorAlgorithms(ShardingRuleConfigurationChecker.java:76) ~[shardingsphere-sharding-core-5.5.0.jar:5.5.0]at org.apache.shardingsphere.sharding.checker.ShardingRuleConfigurationChecker.check(ShardingRuleConfigurationChecker.java:60) ~[shardingsphere-sharding-core-5.5.0.jar:5.5.0]at org.apache.shardingsphere.sharding.checker.ShardingRuleConfigurationChecker.check(ShardingRuleConfigurationChecker.java:55) ~[shardingsphere-sharding-core-5.5.0.jar:5.5.0]at org.apache.shardingsphere.infra.rule.builder.database.DatabaseRulesBuilder.build(DatabaseRulesBuilder.java:67) ~[shardingsphere-infra-common-5.5.0.jar:5.5.0]at org.apache.shardingsphere.infra.metadata.database.ShardingSphereDatabase.create(ShardingSphereDatabase.java:91) ~[shardingsphere-infra-common-5.5.0.jar:5.5.0]at org.apache.shardingsphere.metadata.factory.ExternalMetaDataFactory.createGenericDatabases(ExternalMetaDataFactory.java:85) ~[shardingsphere-metadata-core-5.5.0.jar:5.5.0]at org.apache.shardingsphere.metadata.factory.ExternalMetaDataFactory.create(ExternalMetaDataFactory.java:72) ~[shardingsphere-metadata-core-5.5.0.jar:5.5.0]at org.apache.shardingsphere.mode.metadata.MetaDataContextsFactory.create(MetaDataContextsFactory.java:98) ~[shardingsphere-mode-core-5.5.0.jar:5.5.0]at org.apache.shardingsphere.mode.metadata.MetaDataContextsFactory.create(MetaDataContextsFactory.java:72) ~[shardingsphere-mode-core-5.5.0.jar:5.5.0]at org.apache.shardingsphere.mode.manager.standalone.StandaloneContextManagerBuilder.build(StandaloneContextManagerBuilder.java:53) ~[shardingsphere-standalone-mode-core-5.5.0.jar:5.5.0]at org.apache.shardingsphere.driver.jdbc.core.datasource.ShardingSphereDataSource.createContextManager(ShardingSphereDataSource.java:79) ~[shardingsphere-jdbc-5.5.0.jar:5.5.0]at org.apache.shardingsphere.driver.jdbc.core.datasource.ShardingSphereDataSource.init(ShardingSphereDataSource.java:67) ~[shardingsphere-jdbc-5.5.0.jar:5.5.0]at org.apache.shardingsphere.driver.api.ShardingSphereDataSourceFactory.createDataSource(ShardingSphereDataSourceFactory.java:95) ~[shardingsphere-jdbc-5.5.0.jar:5.5.0]at org.apache.shardingsphere.driver.api.yaml.YamlShardingSphereDataSourceFactory.createDataSource(YamlShardingSphereDataSourceFactory.java:135) ~[shardingsphere-jdbc-5.5.0.jar:5.5.0]at org.apache.shardingsphere.driver.api.yaml.YamlShardingSphereDataSourceFactory.createDataSource(YamlShardingSphereDataSourceFactory.java:70) ~[shardingsphere-jdbc-5.5.0.jar:5.5.0]at org.apache.shardingsphere.driver.jdbc.core.driver.DriverDataSourceCache.createDataSource(DriverDataSourceCache.java:55) ~[shardingsphere-jdbc-5.5.0.jar:5.5.0]at org.apache.shardingsphere.driver.jdbc.core.driver.DriverDataSourceCache.lambda$get$0(DriverDataSourceCache.java:48) ~[shardingsphere-jdbc-5.5.0.jar:5.5.0]at java.base/java.util.concurrent.ConcurrentHashMap.computeIfAbsent(ConcurrentHashMap.java:1708) ~[na:na]at org.apache.shardingsphere.driver.jdbc.core.driver.DriverDataSourceCache.get(DriverDataSourceCache.java:48) ~[shardingsphere-jdbc-5.5.0.jar:5.5.0]at org.apache.shardingsphere.driver.ShardingSphereDriver.connect(ShardingSphereDriver.java:56) ~[shardingsphere-jdbc-5.5.0.jar:5.5.0]at com.alibaba.druid.pool.DruidAbstractDataSource.createPhysicalConnection(DruidAbstractDataSource.java:1682) ~[druid-1.2.23.jar:na]at com.alibaba.druid.pool.DruidAbstractDataSource.createPhysicalConnection(DruidAbstractDataSource.java:1803) ~[druid-1.2.23.jar:na]at com.alibaba.druid.pool.DruidDataSource$CreateConnectionThread.run(DruidDataSource.java:2914) ~[druid-1.2.23.jar:na]查看shardingsphere5.5.0官网关于此信息链接如下
服务器错误码 :: ShardingSphere
官网写得的这个提示没卵用
后面查看shardingsphere5.5.0的源码发现UUIDKeyGenerateAlgorithm.java和SnowflakeKeyGenerateAlgorithm.java都是继承了KeyGenerateAlgorithm.java然后根据上面的报错猜测是我的resources包名和文件名不对于是改源码中的包名和文件名就OK了。 2.我的项目配置 3.自定义策略的类 MyKeyGenerateAlgorithm.java
package com.tfq.shardingshperedemo.config;import org.apache.shardingsphere.infra.algorithm.core.context.AlgorithmSQLContext;
import org.apache.shardingsphere.infra.algorithm.keygen.core.KeyGenerateAlgorithm;
import java.text.SimpleDateFormat;
import java.util.Collection;
import java.util.Date;
import java.util.LinkedList;
import java.util.concurrent.ThreadLocalRandom;/*** Description: 生成支付订单号算法* Author: tfq* Date: 2024-12-26 15:33*/public final class MyKeyGenerateAlgorithm implements KeyGenerateAlgorithm {/*** param context algorithm SQL context* param keyGenerateCount key generate count* return*/Overridepublic CollectionString generateKeys(final AlgorithmSQLContext context, final int keyGenerateCount) {CollectionString result new LinkedList();ThreadLocalRandom threadLocalRandom ThreadLocalRandom.current();for (int index 0; index keyGenerateCount; index) {result.add(generateKey(threadLocalRandom));}return result;}/*** 订单编号时间戳6位随机数例如20241127124523123456前面是日期时间戳后面是订单号** param threadLocalRandom* return*/private String generateKey(ThreadLocalRandom threadLocalRandom) {String nowDate new SimpleDateFormat(yyyyMMddHHmmss).format(new Date());String randomNum threadLocalRandom.nextLong(100000, 999999) ;return nowDate randomNum;}Overridepublic String getType() {return MY_KEY_GENERATE_ALGORITHM;}
}
上面getType返回的String:MY_KEY_GENERATE_ALGORITHM要配置到下shardingsphere-level-table.yaml的keyGenerators(生成主键方法)的type后面。
4.pom.xml的配置如下
?xml version1.0 encodingUTF-8?
project xmlnshttp://maven.apache.org/POM/4.0.0 xmlns:xsihttp://www.w3.org/2001/XMLSchema-instancexsi:schemaLocationhttp://maven.apache.org/POM/4.0.0 https://maven.apache.org/xsd/maven-4.0.0.xsdmodelVersion4.0.0/modelVersionparentgroupIdorg.springframework.boot/groupIdartifactIdspring-boot-starter-parent/artifactIdversion3.3.3/versionrelativePath/ !-- lookup parent from repository --/parentgroupIdcom.tfq/groupIdartifactIdshardingshperedemo/artifactIdversion0.0.1-SNAPSHOT/versionnameshardingshperedemo/namedescriptionshardingshperedemo/descriptionpropertiesjava.version17/java.versionmaven.compiler.source${java.version}/maven.compiler.sourceshardingsphere.version5.5.0/shardingsphere.versionspring.boot.version3.3.3/spring.boot.version!-- DB 相关 --mysql.version8.0.33/mysql.versiondruid.version1.2.23/druid.versionmybatis-plus.version3.5.7/mybatis-plus.versionmybatis-plus-generator3.5.9/mybatis-plus-generator/propertiesdependenciesdependencygroupIdorg.springframework.boot/groupIdartifactIdspring-boot-starter/artifactId/dependencydependencygroupIdorg.springframework/groupIdartifactIdspring-beans/artifactIdversion6.1.14/version/dependencydependencygroupIdorg.springframework/groupIdartifactIdspring-context/artifactIdversion6.1.14/version/dependencydependencygroupIdorg.springframework/groupIdartifactIdspring-expression/artifactIdversion6.1.14/version/dependencydependencygroupIdorg.springframework/groupIdartifactIdspring-core/artifactIdversion6.1.14/version/dependencydependencygroupIdorg.projectlombok/groupIdartifactIdlombok/artifactIdoptionaltrue/optional/dependencydependencygroupIdmysql/groupIdartifactIdmysql-connector-java/artifactIdversion${mysql.version}/version/dependencydependencygroupIdcom.alibaba/groupIdartifactIddruid-spring-boot-3-starter/artifactIdversion${druid.version}/version/dependencydependencygroupIdcom.baomidou/groupIdartifactIdmybatis-plus-spring-boot3-starter/artifactIdversion${mybatis-plus.version}/version/dependencydependencygroupIdcom.github.yulichang/groupIdartifactIdmybatis-plus-join-boot-starter/artifactIdversion1.5.2/version/dependencydependencygroupIdcom.baomidou/groupIdartifactIdmybatis-plus-generator/artifactIdversion${mybatis-plus-generator}/version/dependencydependencygroupIdorg.springframework/groupIdartifactIdspring-jdbc/artifactIdversion6.1.14/version/dependencydependencygroupIdorg.apache.shardingsphere/groupIdartifactIdshardingsphere-jdbc/artifactIdversion${shardingsphere.version}/versionexclusionsexclusiongroupIdorg.apache.shardingsphere/groupIdartifactIdshardingsphere-test-util/artifactId/exclusion/exclusions/dependencydependencygroupIdcommons-io/groupIdartifactIdcommons-io/artifactIdversion2.14.0/version/dependencydependencygroupIdcom.google.protobuf/groupIdartifactIdprotobuf-java/artifactIdversion3.25.5/version/dependencydependencygroupIdcom.github.yulichang/groupIdartifactIdmybatis-plus-join/artifactIdversion1.4.13/version/dependency!-- 测试使用--dependencygroupIdorg.springframework.boot/groupIdartifactIdspring-boot-starter-test/artifactIdscopetest/scopeexclusionsexclusiongroupIdorg.junit.vintage/groupIdartifactIdjunit-vintage-engine/artifactId/exclusion/exclusions/dependencydependencygroupIdorg.xmlunit/groupIdartifactIdxmlunit-core/artifactIdversion2.10.0/versionscopetest/scope/dependencydependencygroupIdorg.junit.platform/groupIdartifactIdjunit-platform-launcher/artifactIdscopetest/scope/dependencydependency!-- 用于生成自定义的 Spring ConfigurationProperties 配置类的说明文件 --groupIdorg.springframework.boot/groupIdartifactIdspring-boot-configuration-processor/artifactIdversion${spring.boot.version}/version/dependency!-- swagger--dependencygroupIdcom.github.xiaoymin/groupIdartifactIdknife4j-openapi3-jakarta-spring-boot-starter/artifactIdversion4.5.0/version/dependency!-- https://mvnrepository.com/artifact/io.github.classgraph/classgraph --dependencygroupIdio.github.classgraph/groupIdartifactIdclassgraph/artifactIdversion4.8.112/version/dependencydependencygroupIdorg.springframework.boot/groupIdartifactIdspring-boot-starter-web/artifactId/dependencydependencygroupIdorg.springframework/groupIdartifactIdspring-webmvc/artifactIdversion6.1.13/version/dependencydependencygroupIdorg.apache.tomcat.embed/groupIdartifactIdtomcat-embed-core/artifactIdversion10.1.31/version/dependency!-- https://mvnrepository.com/artifact/cn.hutool/hutool-core --dependencygroupIdcn.hutool/groupIdartifactIdhutool-core/artifactIdversion5.8.25/version/dependency/dependenciesbuildpluginsplugingroupIdorg.springframework.boot/groupIdartifactIdspring-boot-maven-plugin/artifactIdconfigurationexcludesexcludegroupIdorg.projectlombok/groupIdartifactIdlombok/artifactId/exclude/excludes/configuration/plugin/plugins/build/project5.application.yaml配置如下
server:port: 8888spring:application:name: shardingshperedemodatasource:driverClassName: org.apache.shardingsphere.driver.ShardingSphereDriverurl: jdbc:shardingsphere:classpath:shardingsphere-level-table.yaml#在shardingshere的配置参考官网#https://shardingsphere.apache.org/document/5.5.0/cn/user-manual/shardingsphere-jdbc/yaml-config/rules/readwrite-splitting/mybatis-plus:configuration:map-underscore-to-camel-case: true #开启下划线转驼峰mapper-locations: classpath*:/mapper/**/*.xml #Mapper.xml文件地址默认值type-aliases-package: com.tfq.shardingshperedemo.entity #别名扫描包# springdoc-openapi项目配置
springdoc:swagger-ui:#自定义swagger前端请求路径,输入http:localhost:8080/swagger-ui会自动重定向到swagger页面path: /swagger-uitags-sorter: alphaoperations-sorter: alpha#此项配置解决controller的Getmapping的参数对象展开并能传递对象参数的值到后台default-flat-param-object: true # 参见 https://doc.xiaominfo.com/docs/faq/v4/knife4j-parameterobject-flat-param 文档api-docs:path: /v3/api-docs #swagger后端请求地址enabled: true #是否开启文档功能group-configs: #分组配置,可配置多个分组- group: default #分组名称paths-to-match: /** #配置需要匹配的路径packages-to-scan: com.tfq.shardingshperedemo.controller #配置要扫描包的路径,一般配置到启动类所在的包名- group: admin-apipaths-to-match: /**packages-to-scan: com.tfq.shardingshperedemo.controller
6.shardingsphere-level-table.yaml配置如下
#水平分表-数据源配置
dataSources:ds1: #数据源名称-用于水平分表dataSourceClassName: com.alibaba.druid.pool.DruidDataSourcedriverClassName: com.mysql.cj.jdbc.Driverurl: jdbc:mysql://192.168.56.10:3306/course_db?serverTimeznotallowGMT%2B8useSSLfalserewriteBatchedStatementstrueusername: rootpassword: 123456#数据分片规则配置:指定数据库存表course_1表分布情况,表名是什么
rules:- !SINGLE #不分库分表的表在 ShardingSphere 中叫做单表可以使用 LOAD 语句或者 SINGLE 规则配置需要加载的单表。tables:- *.*# 数据分片- !SHARDINGtables:course: #逻辑表名actualDataNodes: ds1.course_${0..1} #由数据源名 表名组成参考 Inline 语法规则tableStrategy: #指定分片策略约定cid的值偶数添加到course_1,cid的值为奇数则添加到course_2standard:shardingColumn: user_idshardingAlgorithmName: t-course-inline # 分片算法名称必须以中划线命名keyGenerateStrategy:column: cidkeyGeneratorName: My-PAYORDER-ID shardingAlgorithms: #分片算法配置t-course-inline: # 分片算法名称必须以中划线命名type: INLINE # 分片算法类型props: #分片算法属性配置algorithm-expression: course_$-{user_id % 2}keyGenerators:My-PAYORDER-ID:type: MY_KEY_GENERATE_ALGORITHMprops:worker:id: 1 # 可选传递给自定义算法的配置
props:max.connections.size.per.query: 10acceptor.size: 200 # The default value is available processors count * 2.executor.size: 200 # Infinite by default.query.with.cipher.column: truesql-show: trueallow.range.query.with.inline.sharding: falsecheck.table.metadata.enabled: false 7.测试代码
ShardingshperedemoApplicationTests.java
package com.tfq.shardingshperedemo;import com.baomidou.mybatisplus.core.conditions.query.LambdaQueryWrapper;
import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
import com.github.yulichang.wrapper.MPJLambdaWrapper;
import com.tfq.shardingshperedemo.dto.UserDto;
import com.tfq.shardingshperedemo.entity.Course;
import com.tfq.shardingshperedemo.entity.Udict;
import com.tfq.shardingshperedemo.entity.User;
import com.tfq.shardingshperedemo.mapper.CourseMapper;
import com.tfq.shardingshperedemo.mapper.UdictMapper;
import com.tfq.shardingshperedemo.mapper.UserMapper;
import org.junit.jupiter.api.Test;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;
import java.util.List;
import java.util.Random;SpringBootTest
class ShardingshperedemoApplicationTests {Autowiredprivate CourseMapper courseMapper;Autowiredprivate UserMapper userMapper;Autowiredprivate UdictMapper udictMapper;/*** 水平分表* 添加课程方法*/Testvoid addCourse() throws InterruptedException {for (int i 0; i 10; i) {Course course new Course();course.setCname(ruby i);course.setCstatus(Normal);course.setUserId(103L i);courseMapper.insert(course);Thread.sleep(1000);}}/*** 水平分表-查询课程的方法*/Testpublic void findCourse() {QueryWrapperCourse wrapper new QueryWrapper();wrapper.eq(cid, 1069938186098049024L);Course course courseMapper.selectOne(wrapper);System.out.println(course);}}8.运行结果
插入数据库数据 查询运行结果
2024-12-30T18:56:13.87908:00 INFO 31644 --- [shardingshperedemo] [ main] ShardingSphere-SQL : Logic SQL: SELECT cid,cname,user_id,cstatus FROM course WHERE (cid ?)
2024-12-30T18:56:13.88008:00 INFO 31644 --- [shardingshperedemo] [ main] ShardingSphere-SQL : Actual SQL: ds1 ::: SELECT cid,cname,user_id,cstatus FROM course_0 WHERE (cid ?) UNION ALL SELECT cid,cname,user_id,cstatus FROM course_1 WHERE (cid ?) ::: [20241230184613690172, 20241230184613690172]
Course(cid20241230184613690172, cnameruby1, userId104, cstatusNormal, usersnull)
若大家还有问题请留言在解答。 项目下载地址https://download.csdn.net/download/developerFBI/90205506
参考文档
FAQ :: ShardingSphere
基础算法 :: ShardingSphere
springboot整合ShardingSphere5.2.1最新版_spi-00001: no implementation class load from spi -CSDN博客
Docker部署Mysql8.0.20并配置主从复制 - C3Stones - 博客园
