网站开发者模式怎么打开,app开发公司图片,wordpress wpadmin,兰州市住房城乡建设局网站需求#xff1a; 分库#xff1a;按业务线business_id将不同业务线的订单存储在不同的数据库上#xff1b; 分表#xff1a;按user_id字段将不同用户的订单存储在不同的表上#xff0c;为方便直接用非分片字段order_id查询#xff0c;可使用基因法#xff1b; 读写分离 分库按业务线business_id将不同业务线的订单存储在不同的数据库上 分表按user_id字段将不同用户的订单存储在不同的表上为方便直接用非分片字段order_id查询可使用基因法 读写分离为缓解主库的压力读操作访问从库 库表SQL -- 主库
CREATE DATABASE database_103;
CREATE DATABASE database_112;-- 从库
CREATE DATABASE database_slave_103;
CREATE DATABASE database_slave_112;--每个库上分别建立如下表
CREATE TABLE t_order_0 (id bigint(20) NOT NULL,order_id bigint(20) NOT NULL,user_id bigint(20) NOT NULL,business_id bigint(20) NOT NULL
) ENGINEInnoDB DEFAULT CHARSETutf8;CREATE TABLE t_order_1 (id bigint(20) NOT NULL,order_id bigint(20) NOT NULL,user_id bigint(20) NOT NULL,business_id bigint(20) NOT NULL
) ENGINEInnoDB DEFAULT CHARSETutf8;CREATE TABLE t_order_2 (id bigint(20) NOT NULL,order_id bigint(20) NOT NULL,user_id bigint(20) NOT NULL,business_id bigint(20) NOT NULL
) ENGINEInnoDB DEFAULT CHARSETutf8;
INSERT INTO t_order VALUES (1,1,112);CREATE TABLE t_order_3 (id bigint(20) NOT NULL,order_id bigint(20) NOT NULL,user_id bigint(20) NOT NULL,business_id bigint(20) NOT NULL
) ENGINEInnoDB DEFAULT CHARSETutf8; pom.xml !-- sharding-jdbc --dependencygroupIdcom.dangdang/groupIdartifactIdsharding-jdbc-core/artifactIdversion1.4.2/version/dependencydependencygroupIdcom.dangdang/groupIdartifactIdsharding-jdbc-config-spring/artifactIdversion1.4.0/version/dependency spring-database.xml ?xml version1.0 encodingUTF-8?
beans xmlnshttp://www.springframework.org/schema/beansxmlns:xsihttp://www.w3.org/2001/XMLSchema-instancexmlns:txhttp://www.springframework.org/schema/txxmlns:rdbhttp://www.dangdang.com/schema/ddframe/rdbxsi:schemaLocationhttp://www.springframework.org/schema/beanshttp://www.springframework.org/schema/beans/spring-beans.xsdhttp://www.springframework.org/schema/txhttp://www.springframework.org/schema/tx/spring-tx.xsdhttp://www.dangdang.com/schema/ddframe/rdbhttp://www.dangdang.com/schema/ddframe/rdb/rdb.xsdbean iddatabase_112 classcom.alibaba.druid.pool.DruidDataSource init-methodinitdestroy-methodcloseproperty namedriverClassName valuecom.mysql.jdbc.Driver/property nameurl value${jdbc.url.112}/propertyproperty nameusername value${jdbc.username.112}/propertyproperty namepassword value${jdbc.password.112}/propertyproperty namemaxActive value100/property nameinitialSize value50/property namemaxWait value60000/property nameminIdle value5//beanbean iddatabase_slave_112 classcom.alibaba.druid.pool.DruidDataSource init-methodinitdestroy-methodcloseproperty namedriverClassName valuecom.mysql.jdbc.Driver/property nameurl value${jdbc.url.slave.112}/propertyproperty nameusername value${jdbc.username.slave.112}/propertyproperty namepassword value${jdbc.password.slave.112}/propertyproperty namemaxActive value100/property nameinitialSize value50/property namemaxWait value60000/property nameminIdle value5//beanbean iddatabase_103 classcom.alibaba.druid.pool.DruidDataSource init-methodinitdestroy-methodcloseproperty namedriverClassName valuecom.mysql.jdbc.Driver/property nameurl value${jdbc.url.103}/propertyproperty nameusername value${jdbc.username.103}/propertyproperty namepassword value${jdbc.password.103}/propertyproperty namemaxActive value100/property nameinitialSize value50/property namemaxWait value60000/property nameminIdle value5//beanbean iddatabase_slave_103 classcom.alibaba.druid.pool.DruidDataSource init-methodinitdestroy-methodcloseproperty namedriverClassName valuecom.mysql.jdbc.Driver/property nameurl value${jdbc.url.slave.103}/propertyproperty nameusername value${jdbc.username.slave.103}/propertyproperty namepassword value${jdbc.password.slave.103}/propertyproperty namemaxActive value100/property nameinitialSize value50/property namemaxWait value60000/property nameminIdle value5//bean!--mybatis--bean idsqlSessionFactory classorg.mybatis.spring.SqlSessionFactoryBeanproperty namemapperLocations valueclasspath:xmlmapper/*.xml/property namedataSource refshardingDataSource/!-- 配置Mybatis配置文件 --property nameconfigLocation valueclasspath:/mybatis/mybatis-config.xml//beanbean idsqlSession classorg.mybatis.spring.SqlSessionTemplateconstructor-arg index0 refsqlSessionFactory//bean!-- 注解Mapper scanner --bean classorg.mybatis.spring.mapper.MapperScannerConfigurerproperty namebasePackage valuecom.lc.sharding.mybatismapper/property namesqlSessionTemplateBeanName valuesqlSession//bean!-- 事务--bean idtxManagerclassorg.springframework.jdbc.datasource.DataSourceTransactionManagerproperty namedataSource refshardingDataSource//beantx:annotation-driven transaction-managertxManager/!--读写分离--rdb:master-slave-data-source idrbb_112 master-data-source-refdatabase_112slave-data-sources-refdatabase_slave_112/rdb:master-slave-data-source idrbb_103 master-data-source-refdatabase_103slave-data-sources-refdatabase_slave_103/!--分库策略--rdb:strategy iddatabaseShardingStrategy sharding-columnsbusiness_idalgorithm-expressionrbb_${business_id.longValue()}/!--分表策略--rdb:strategy idtableShardingStrategy sharding-columnsuser_id,order_idalgorithm-classcom.lc.sharding.algorithm.MultipleKeysTableShardingAlgorithmImpl/rdb:data-source idshardingDataSourcerdb:sharding-rule data-sourcesrbb_112,rbb_103rdb:table-rulesrdb:table-rule logic-tablet_order actual-tablest_order_${0..3} database-strategydatabaseShardingStrategytable-strategytableShardingStrategy//rdb:table-rules/rdb:sharding-rulerdb:propsprop keymetrics.enabletrue/propprop keysql.showtrue/prop/rdb:props/rdb:data-source
/beans 基因法多列分片 public class MultipleKeysTableShardingAlgorithmImpl implements MultipleKeysTableShardingAlgorithm {public CollectionString doSharding(CollectionString tableNames, CollectionShardingValue? shardingValues) {ListString shardingSuffix new ArrayListString();long partId 0;for (ShardingValue value : shardingValues) {if (value.getColumnName().equals(user_id)) {partId ((Long) value.getValue()) % 4;break;} else if (value.getColumnName().equals(order_id)) {partId ((Long) value.getValue()) % 4;break;}}for (String name : tableNames) {if (name.endsWith(partId )) {shardingSuffix.add(name);return shardingSuffix;}}return shardingSuffix;}
} 什么是基因法分片 在订单数据oid生成时order_id末端加入分片基因让同一个user_id下的所有订单都含有相同基因落在同一个表上。 资料https://mp.weixin.qq.com/s/PCzRAZa9n4aJwHOX-kAhtA 根据user_id生成order_id: public long bulidOrderId(long userId) {//取用户id后4位userId userId 15;//先取60位唯一idlong uniqueId this.nextId();//唯一id左移4位、拼接userId后4位return (uniqueId 4) | userId;} this.nextId();//使用雪花算法生成60位分布式唯一id1位符号位41位时间戳5位workId5位datacenterId6位序列号4位基因片 小结 数据分片 支持分库分表可支持 BETWEENIN等多维度分片也支持多分片键共用支持聚合分组排序分页关联等复杂查询语句分片灵活支持多分片键共用支持inline表达式基于Hint的强制路由支持分布式主键读写分离 支持一主多从的读写分离支持分库分表与读写分离共同使用支持分布式生成全局主键。柔性事务 最大努力到达型事务分布式治理 支持配置中心可动态修改支持客户端熔断和失效转移引用http://shardingsphere.io/转载于:https://www.cnblogs.com/sidesky/p/10669403.html
