sharding-jdbc原创
前言
We are going to investigate how to use Shardingsphere to do DB seperation.
一.集成方式 common jar [java]形式: 优点: 可拔插 即配好对应的配置文件 我们就可以自由选择 数据源是否分库 缺点: 和spring boot 的有适配的问题 需要调试 yaml[spring boot] 形式: 优点：spring boot支持 [虽然也存在和postgre适配的问题] 缺点: 需要所有开发人员和环境配置两套数据源 二.分片策略及表数据域的划分
分片键：在所有和contract相关的表中添加contract_tag 字段
AFC/LC （1/0）
分片策略:
StandardShardingStrategy 由于实际业务需求 故此我们将只配置数据源分片策略 而不配表分片原则
分片算法:
inline/preciseShardingAlgorithm
数据节点:
绑定表:
广播表:
单表:
三.开发阶段集成测试 Module name contract相关操作 DML/DDL Execute Type SQL TYPE
check result
CODE/SQL
Comment
Auth service N - - -
Calculation Service
Y READ Repository HQL
contractRepository.findAllByContractNumberIn(contractNumbers)
invalid report
查询的是分库数据：contract_number = 'FG3WXYZ'
Y CREATE Repository HQL
contractRepository.save(contract)
only for test 可以删除
Y READ JDBC SQL SELECT C.contract_number, C.contract_id,C.customer_number,C.contract_financial_product_type, C.contract_activation_date,C.contract_ending_date,C.contract_term, C.contract_installment_amount,C.contract_status,C.contract_live_status, C.customer_name,C.customer_mobile_number,C.asset_model, C.asset_model_year,C.dealer_number,C.contract_terms_remaining, C.contract_outstanding_principal_amount,C.contract_salesperson, C.contract_termination_date,C.product_type, o., l., o.mileage AS offer_mileage, o.asset_model AS offer_asset_model, o.contract_number AS offer_contract_number, o.dealer_number AS offer_dealer_number, l.used_car_value as lead_used_car_value, o.used_car_value as offer_used_car_value, k.contract_number as knock_out_contract_number from retention.contract c left join (select * from lead) l on c.contract_number = l.contract_number left join offer o on l.default_offer_id = o.offer_id left join knock_out_contract k on c.contract_number = k.contract_number where c.contract_activation_date > ? and (l.status is null or l.status not in ('excludeLeadStatus') contract reader left join / from where Y READ JDBC SQL select c.contract_number, c.contract_id, c.customer_number, c.contract_financial_product_type, c.contract_activation_date, c.contract_ending_date, c.contract_term, c.contract_installment_amount, c.contract_status, c.contract_live_status, c.customer_name, c.customer_mobile_number, c.asset_model, c.asset_model_year, c.dealer_number, c.contract_terms_remaining, c.contract_outstanding_principal_amount, c.contract_salesperson, c.contract_termination_date, c.product_type, o., l., o.mileage as offer_mileage, o.asset_model as offer_asset_model, o.contract_number as offer_contract_number, o.dealer_number as offer_dealer_number, l.used_car_value as lead_used_car_value, o.used_car_value as offer_used_car_value, k.contract_number as knock_out_contract_number from retention.contract c left join (select * from lead) l on c.contract_number = l.contract_number left join offer o on l.default_offer_id = o.offer_id left join knock_out_contract k on c.contract_number = k.contract_number
contract reader
left join / from where
Y READ JDBC SQL select c.contract_number, c.contract_id, c.customer_number, c.contract_financial_product_type, c.contract_activation_date, c.contract_ending_date, c.contract_term, c.contract_installment_amount, c.contract_status, c.contract_live_status, c.customer_name, c.customer_mobile_number, c.asset_model, c.asset_model_year, c.dealer_number, c.contract_terms_remaining, c.contract_outstanding_principal_amount, c.contract_salesperson, c.contract_termination_date, c.product_type, o., l., o.mileage as offer_mileage, o.asset_model as offer_asset_model, o.contract_number as offer_contract_number, o.dealer_number as offer_dealer_number, l.used_car_value as lead_used_car_value, o.used_car_value as offer_used_car_value, k.contract_number as knock_out_contract_number from (select * from retention.contract c1 where not EXISTS( select c2.* from contract c2 left join lead l on l.contract_number=c2.contract_number left join otr_lead_map olm on olm.insight_lead_id = l.lead_id left join otr_activity_history oah on oah.contract_number=l.contract_number WHERE ((olm.listed_time is not null and oah.created_time>olm.listed_time and oah.activity_type not in (' ') ) or olm.listed_time is null) and c2.contract_number=c1.contract_number) and c1.contract_number in (SELECT oah.contract_number FROM (SELECT contract_number,activity_type FROM (SELECT oah.* FROM retention.LEAD l INNER JOIN retention.otr_lead_map olm ON l.lead_id = olm.insight_lead_id INNER JOIN retention.otr_activity_history oah ON l.contract_number = oah.contract_number WHERE olm.received_time IS NOT NULL AND olm.received_time < oah.created_time ) t GROUP BY contract_number,activity_type) oah where oah.activity_type='' or oah.activity_type='' GROUP BY oah.contract_number HAVING count(oah.contract_number)=2 ))c left join (select * from lead) l on c.contract_number = l.contract_number left join offer o on l.default_offer_id = o.offer_id left join knock_out_contract k on c.contract_number = k.contract_number left join contract_label cl on c.contract_number = cl.contract_number where c.contract_activation_date > ? and l.status = 'status' and cl.label='labels';
contract reader
left join / from where / sub query
Y READ JDBC SQL select c.contract_number, c.contract_id, c.customer_number, c.contract_financial_product_type, c.contract_activation_date, c.contract_ending_date, c.contract_term, c.contract_installment_amount, c.contract_status, c.contract_live_status, c.customer_name, c.customer_mobile_number, c.asset_model, c.asset_model_year, c.dealer_number, c.contract_terms_remaining, c.contract_outstanding_principal_amount, c.contract_salesperson, c.contract_termination_date, c.product_type, o., l., o.mileage as offer_mileage, o.asset_model as offer_asset_model, o.contract_number as offer_contract_number, o.dealer_number as offer_dealer_number, l.used_car_value as lead_used_car_value, o.used_car_value as offer_used_car_value, k.contract_number as knock_out_contract_number from (select * from retention.contract c1 where not EXISTS( select c2.* from contract c2 left join lead l on l.contract_number=c2.contract_number left join otr_lead_map olm on olm.insight_lead_id = l.lead_id left join otr_activity_history oah on oah.contract_number=l.contract_number WHERE ((olm.listed_time is not null and oah.created_time>olm.listed_time and oah.activity_type not in (' ') ) or olm.listed_time is null) and c2.contract_number=c1.contract_number)
contract reader
left join / from where / sub query
Common Y READ entity HQL @OneToOne(fetch = FetchType.LAZY) @JoinColumn(name = "contractNumber", referencedColumnName="contractNumber", insertable = false, updatable = false) @NotFound(action = NotFoundAction.IGNORE) private Contract contract; Lead READ entity HQL @OneToOne(fetch = FetchType.LAZY) @JoinColumn(name = "contractNumber", referencedColumnName="contractNumber", insertable = false, updatable = false) @NotFound(action = NotFoundAction.IGNORE) private Contract contract; OBKLead DDL flyway SQL SCRIPT ALTER/DROP/PROCEDURE SQL SCRIPT
- WEAK REFRENCE java bean' field
class OBJ{
private String contractNumber;
}
弱相关
# Communication N - - -
# Configuration N - - -
Contract connector Y READ Repository SQL
sharding proxy-4.1.1
OK
SELECT COUNT
( C.contract_number ) FROM contract C INNER JOIN special_mark_history s ON s.contract_number = C.contract_number INNER JOIN contract_label cl ON cl.contract_number = C.contract_number WHERE C.contract_number = 'param' AND s.special_mark_type = 'param' AND cl.label = 'param'
BR/ET tag
from/ inner join /where
需要为聚合函数添加别名
CREATE Repository HQL targetContractRepository.saveAll(entityList) 从AFC/LC[sql/server]导数据到insight READ Repository SQL INSERT INTO application_lead_map ( application_number, dealer_number, customer_number, lead_id, contract_number, submission_date ) SELECT A .application_number, l.dealer_number, A.customer_number, l.lead_id, l.contract_number, A.submission_date FROM otr_lead_map olm INNER JOIN LEAD l ON olm.insight_lead_id = l.lead_id INNER JOIN application A ON A.dealer_number = l.dealer_number AND A.customer_number = l.customer_number INNER JOIN contract C ON l.contract_number = C.contract_number AND C.contract_activation_date < A.submission_date LEFT JOIN application_lead_map alm ON alm.lead_id = l.lead_id AND alm.application_number = A.application_number WHERE olm.listed_time IS NOT NULL AND alm.lead_id IS NULL AND alm.application_number IS NULL AND olm.listed_time < A.submission_date RETURNING AND A.submission_date >= 'param' AND A.submission_date < 'param' RETURNING contract_number AS contractNumber, submission_date AS submissionDate, ( SELECT ID FROM lead_status WHERE code = 'APPLICATION' ) AS statusId 插入ApplicationRepository READ Repository SQL
sharding proxy-4.1.1
OK
SELECT
alm.application_number AS applicationNumber, alm.contract_number AS contractNumber, to_timestamp( to_char( C.contract_activation_date, 'yyyy-MM-dd' ), 'yyyy-MM-dd' ) AS submissionDate, ( SELECT ID FROM lead_status WHERE code = 'ACTIVATED' ) AS statusId FROM contract C INNER JOIN otr_active_dealer ocd ON C.dealer_number = ocd.dealer_number INNER JOIN application_lead_map alm ON C.application_number = alm.application_number AND C.dealer_number = alm.dealer_number AND C.contract_number <> alm.contract_number WHERE 1 = 1 AND C.contract_activation_date >= 'param' AND C.contract_activation_date < : endDate AND C.application_number IS NOT NULL AND alm.contract_number IS NOT NULL; TargetContractRepository READ Repository SQL
sharding proxy-4.1.1
OK
select * from contract c where c.application_number in (:applicationNumbers)
TargetContractRepository in
# Contract Knockout Connector N - - -
# Css connector N - - -
Dmp connector Y dataSource
sharding proxy-4.1.1
OK
SELECT C
# .contract_number contract_id, C.customer_name, C.customer_number, C.customer_date_of_birth, C.customer_gender, C.customer_mobile_number, C.dealer_name, C.dealer_number, C.dealer_gssn_id, C.customer_industry_sector, C.customer_address, C.customer_region, C.asset_vin, C.contract_live_status, C.customer_id_type FROM contract C WHERE C.dmp_modified_date <= ' dbFormatDate ' ORDER BY C.contract_number ASC; 读取DB信息到数据库 from/where/order by Job scheduler - - - -
Lead-service
Y READ Repository HQL
select
new com.daimler.retention.dto.FlatUniversalStatisticsDTO(contract.dealerNumber, count(distinct knockOutContract.pk.contractNumber)) from KnockOutContract knockOutContract inner join Contract contract on knockOutContract.pk.contractNumber = contract.contractNumber group by contract.dealerNumber ContractKnockOutRepository inner join / group by READ Repository HQL select new com.daimler.retention.dto.FlatUniversalStatisticsDTO(contract.dealerNumber, count(distinct contractLabel.contractLabelPrimaryKey.contractNumber)) from ContractLabel contractLabel inner join Contract contract on contractLabel.contractLabelPrimaryKey.contractNumber = contract.contractNumber where contractLabel.contractLabelPrimaryKey.label in (:labels) group by contract.dealerNumber
ContractLabelRepository
inner join / group by
READ Repository HQL
@Query(value =
"select distinct asset_model as assetModel,\n" +
" asset_model_year as assetModelYear,\n" +
" asset_vehicle_cost_amount as assetVehicleCostAmount,\n" +
" asset_brand as assetBrand\n" +
"from retention.contract", nativeQuery = true)
List
List
@Query(value =
" select new com.daimler.retention.dto.ContractDTO(c, cl.comment, cl.contractLabelPrimaryKey.label) " +
" from Contract c left join ContractLabel cl " +
" on c.contractNumber = cl.contractLabelPrimaryKey.contractNumber " +
" and cl.contractLabelPrimaryKey.label in ('GREEN_LIST' , 'CUSTOMER_INTENTION') " +
" where c.contractNumber = :contractNumber" +
" order by cl.modifiedTime desc "
)
List
boolean existsByContractNumber(@Param("contractNumber") String contractNumber);
Contract findFirstByCustomerNumberOrderByContractActivationDateDesc(String customerNumber);
Set
ContractRepository exist/order by/ in
READ Repository HQL SELECT NEW com.daimler.retention.dto.SendCampaignLeadDTO ( cl.ID, l.assigneeId, l.dealerNumber, l.leadId, cl.parity, cl.equity, l.status, cl.contractNumber, o,C ) FROM CampaignLead cl LEFT JOIN LEAD l ON l.leadId = cl.leadId INNER JOIN Offer o ON o.offerId = cl.offerId LEFT JOIN Contract C ON C.contractNumber = cl.contractNumber WHERE cl.campaignId = : campaignId AND cl.status = : campaignStatus AND o.offerType IN : offerTypes ") LeadRepository left join READ Repository HQL
return (Specification<Lead>) (root, criteriaQuery, criteriaBuilder) -> {
if (contractStatus == null || "".equals(contractStatus)) { return null; } Join<Lead, Contract> contractJoin = root.join("contract"); if ("Live".equals(contractStatus)) { return criteriaBuilder.equal(contractJoin.get("contractLiveStatus"), ContractLiveStatus.ALIVE.getValue()); } else if ("Matured".equals(contractStatus)) { return criteriaBuilder.equal(contractJoin.get("contractLiveStatus"), ContractLiveStatus.NOT_ALIVE.getValue()); } else { return null; } }; LeadRepository 通过root.join("contract")的形式
specification join / >= / <= / join
return (Specification<Lead>) (root, criteriaQuery, criteriaBuilder) -> {
if (null == anniversary) { return null; } Date lastMoment = instance.getTime(); Join<Lead, Contract> contractJoin = root.join("contract"); Predicate and = criteriaBuilder.and(criteriaBuilder.greaterThanOrEqualTo(contractJoin.get("contractActivationDate"), from), criteriaBuilder.lessThanOrEqualTo(contractJoin.get("contractActivationDate"), to)); Predicate or = criteriaBuilder.or(criteriaBuilder.lessThan(contractJoin.get("contractEndingDate"), firstMoment), criteriaBuilder.greaterThan(contractJoin.get("contractEndingDate"), lastMoment)); return criteriaBuilder.and(and, or); };
return (Specification<Lead>) (root, criteriaQuery, criteriaBuilder) -> {
if (list == null) { return null; } Join<Lead, Contract> contractJoin = root.join("contract"); return criteriaBuilder.and(contractJoin.get(field).in(list)); };
return (Specification<Lead>) (root, criteriaQuery, criteriaBuilder) -> {
if (null == from && null == to) {
return null;
}
Join<Lead, Contract> contractJoin = root.join("contract");
Contract contract = new Contract();
String typeName = null;
try {
Field declaredField = contract.getClass().getDeclaredField(field);
typeName = declaredField.getGenericType().getTypeName();
} catch (NoSuchFieldException e) {
log.error(e.toString());
}
List
return (Specification<Lead>) (root, criteriaQuery, criteriaBuilder) -> {
if (contractEndDateFrom == null) { return null; } Join<Lead, Contract> contractJoin = root.join("contract"); return criteriaBuilder.greaterThanOrEqualTo(contractJoin.get("contractEndingDate"), contractEndDateFrom); };
return (Specification<Lead>) (root, criteriaQuery, criteriaBuilder) -> {
if (contractEndDateTo == null) { return null; } Join<Lead, Contract> contractJoin = root.join("contract"); return criteriaBuilder.lessThanOrEqualTo(contractJoin.get("contractEndingDate"), contractEndDateTo); };
READ Repository
sharding proxy-4.1.1
OK
SELECT app.application_number AS applicationNumber, app.submission_date AS submissionDate, ol.application_number AS leadId, app.customer_number AS customerNumber, C.contract_number AS contractNumber FROM otr_lead_map olm INNER JOIN oap_lead ol ON olm.insight_lead_id = ol.application_number INNER JOIN application app ON ol.id_card_number = app.customer_number INNER JOIN contract C ON C.customer_number = ol.id_card_number AND C.contract_activation_date < app.submission_date LEFT JOIN application_oap_lead_map alm ON alm.lead_id = ol.application_number AND alm.application_number = app.application_number WHERE
olm.listed_time IS NOT NULL AND alm.lead_id IS NULL AND alm.application_number IS NULL AND olm.listed_time < app.submission_date AND app.submission_date >= : START AND app.submission_date < : END AND app.customer_role_type IN ( 'Borrower', 'Co-Borrower', 'Guarantor' );
OapLeadRepository
READ Repository
sharding proxy-4.1.1
OK
SELECT
alm.application_number AS applicationNumber, alm.contract_number AS contractNumber, alm.lead_id AS leadId, C.contract_activation_date AS submissionDate FROM contract C INNER JOIN application_oap_lead_map alm ON C.application_number = alm.application_number AND C.contract_number <> alm.contract_number WHERE 1 = 1 AND C.application_number IS NOT NULL AND alm.contract_number IS NOT NULL AND C.contract_activation_date >= : START AND C.contract_activation_date < : END OapLeadRepository is not null READ Repository
SELECT NEW
com.daimler.retention.projection.ApplicationOBKDataTrackProj ( A.applicationPK.applicationNumber, ol.dealerNumber, A.applicationPK.customerNumber, ol.obkLeadId, ol.contractNumber, A.submissionDate ) FROM OTRLeadMap olm INNER JOIN OBKLead ol ON olm.insightLeadId = ol.obkLeadId INNER JOIN Application A ON A.applicationPK.dealerNumber = ol.dealerNumber AND A.applicationPK.customerNumber = ol.customerNumber INNER JOIN Contract C ON ol.contractNumber = C.contractNumber AND C.contractActivationDate < A.submissionDate LEFT JOIN ApplicationOBKLeadMap alm ON alm.applicationOBKLeadMapPrimaryKey.leadId = ol.obkLeadId AND alm.applicationOBKLeadMapPrimaryKey.applicationNumber = A.applicationPK.applicationNumber WHERE olm.listedTime IS NOT NULL AND alm.applicationOBKLeadMapPrimaryKey.leadId IS NULL AND alm.applicationOBKLeadMapPrimaryKey.applicationNumber IS NULL AND olm.listedTime < A.submissionDate AND A.customerRoleType IN ( 'Borrower', 'Co-Borrower', 'Guarantor' ) AND A.submissionDate >= : START AND A.submissionDate < : END OBKLeadRepository READ Repository
SELECT NEW
com.daimler.retention.projection.ApplicationOBKDataTrackProj ( A.applicationPK.applicationNumber, ol.dealerNumber, A.applicationPK.customerNumber, ol.obkLeadId, ol.contractNumber, A.submissionDate ) FROM OTRLeadMap olm INNER JOIN OBKLead ol ON olm.insightLeadId = ol.obkLeadId INNER JOIN Application A ON A.applicationPK.dealerNumber = ol.dealerNumber AND A.applicationPK.customerNumber = ol.customerNumber INNER JOIN Contract C ON ol.contractNumber = C.contractNumber AND C.contractActivationDate < A.submissionDate LEFT JOIN ApplicationOBKLeadMap alm ON alm.applicationOBKLeadMapPrimaryKey.leadId = ol.obkLeadId AND alm.applicationOBKLeadMapPrimaryKey.applicationNumber = A.applicationPK.applicationNumber WHERE olm.listedTime IS NOT NULL AND alm.applicationOBKLeadMapPrimaryKey.leadId IS NULL AND alm.applicationOBKLeadMapPrimaryKey.applicationNumber IS NULL AND olm.listedTime < A.submissionDate AND A.customerRoleType IN ( 'Borrower', 'Co-Borrower', 'Guarantor' ) AND A.submissionDate >= : START AND A.submissionDate < : END OBKLeadRepository READ Repository
SELECT NEW
# com.daimler.retention.dto.OfferSnapshotByLpDTO ( os, l, C ) FROM OfferSnapshot os INNER JOIN LEAD l ON l.leadId = os.leadId INNER JOIN Contract C ON C.contractNumber = l.contractNumber WHERE os.ID = : offerSnapshotId OfferSnapshotRepository Master-data-service - - - -
# Master-data-table-connector - - - -
# Otr-connector - - - -
# Rv-connector - - - -
# Rv-service - - - -
# Upselling-serivce - - - -
分布式事务方案选取 四.数据迁移方案和开发生产并行平滑过渡测试方案 五.后续运维方案 六.问题记录 问题记录 解决方案 可行性 common jar 集成会报UnsupportSQLException [postgre driver没有实现JDBC某个规范函数] 换数据源[ Hikari -> druid ] TODO common jar 集成会报UnsupportSQLException [postgre driver没有实现JDBC某个规范函数] 更换为yaml形式 Y sharding jdbc 不支持 schema的概念 当我们用一个数据库实例运行两个schema 如 retention/retention_auth 并且在项目中如此配置数据源 url: jdbc:postgresql://localhost:5432/retention_otr_dev?currentSchema=retention 会报找不到 retention_auth下面的表
解决方案
url:jdbc:postgresql://localhost:5432/retention_otr_dev
TODO
sharding jdbc 不支持 schema的概念 当我们用一个数据库实例运行两个schema 如 retention/retention_auth 并且在项目中如此配置数据源 url: jdbc:postgresql://localhost:5432/retention_otr_dev?currentSchema=retention 会报找不到 retention_auth下面的表 将retention_auth分到另一个库 Y 即便使用sharding-jdbc-spring-boot-starter 也会出现 不兼容的问题 Caused by: java.sql.SQLFeatureNotSupportedException: isValid 这将导致spring-boot-actuator db健康检测失效导致我们k8s对应服务节点频繁重启 关闭DB检查机制 手动打补丁 TODO
sharding-jdbc不支持跨表查询[4.1.1] 具体表现为
db0 db1 sql result
lead contract 存在lead contract_number 123456 不存在 contract_number is 123456
contract 存在 contract_number is 123456 select l from Lead l join Contract c on l.contractNumber = c.contractNumber where c.contractNumber = 123456 查不到该条数据
全分表
db0 db1 sql result
lead contract 存在lead contract_number 123 不存在456 存在lead contract_number 123 不存在456
lead contract 存在lead contract_number 456不存在123 存在lead contract_number 456 不存在123
select count(l.leadId) from Lead l join Contract c on l.contractNumber = c.contractNumber where c.contractNumber in (123,456)
2
待全量测试 初步可行
sharding-jdbc不支持跨表查询[4.1.1] 使用beta版本 TODO
结论
使用sharding jdbc 进行分库分表 对我们已经维护了很久的系统来说 从人力成本和时间成本上来说的得不偿失 故我们选取了另一种方案