高并发场景下一卡通系统数据库架构设计与实践

高并发场景下一卡通系统数据库架构设计与实践 | 极客日志

引言：数字化时代的一卡通系统挑战

在智慧城市和智慧校园建设浪潮中，一卡通系统已从简单的消费支付工具演变为集身份认证、金融服务、门禁管理、数据统计于一体的综合性数字平台。随着用户规模从数万扩展到数千万，日均交易量从几千笔激增至数千万笔，传统的一卡通系统架构面临着前所未有的性能压力。特别是在早晚高峰时段，系统需要支撑每秒数万笔的并发交易，这对底层数据库的高可用性、高并发处理能力和数据一致性提出了极致要求。

本文将以某大型城市交通一卡通系统的国产化改造实践为基础，深入探讨高并发场景下一卡通系统的数据库架构设计、核心功能实现、性能优化策略及安全保障机制，为同类系统的设计与实施提供可参考的技术方案。

一、一卡通系统架构演进与设计原则

1.1 从集中式到分布式架构的演进

传统一卡通系统多采用集中式架构，所有业务逻辑和数据存储集中在单一服务器上。这种架构虽然简单易维护，但随着业务量增长，逐渐暴露出单点故障风险高、扩展性差、性能瓶颈明显等问题。

现代一卡通系统普遍采用三层分布式架构：

接入层：负责终端设备连接与协议转换，支持 TCP/IP、485 串口等多种通信协议
业务逻辑层：采用微服务架构，将用户管理、交易处理、清分结算等功能拆分为独立服务
数据存储层：采用关系型数据库集群，实现读写分离和数据分片

1.2 核心设计原则

高可用性原则：系统需提供 7×24 小时不间断服务，单点故障自动切换时间控制在秒级以内。通过主备集群 + 共享存储架构，实现 RTO（恢复时间目标）小于 30 秒，RPO（恢复点目标）接近于零。

数据一致性原则：金融级交易必须保证 ACID 特性，防止重复扣款、超额消费等问题。采用分布式事务机制，确保跨服务资金操作的一致性。

弹性扩展原则：系统应支持水平扩展，能够根据业务增长动态增加计算和存储资源。通过容器化部署和 Kubernetes 编排，实现服务的自动扩缩容。

安全合规原则：符合国家信息安全等级保护三级标准，实现数据传输加密、存储加密、细粒度访问控制和完整审计日志。

二、数据库选型与架构设计

2.1 数据库技术选型考量

在一卡通系统数据库选型中，需要综合考虑以下因素：

性能要求：早高峰时段需支撑每秒 10 万 + 交易，TPC-C 性能需达到百万级 tpmc 以上。查询响应时间在正常负载下应低于 200ms，高峰期不超过 500ms。

数据规模：系统需存储数千万用户信息、数十亿条交易流水，数据总量可达 TB 级别。需支持高效的数据分区和索引策略。

高可用需求：主备切换时间需控制在 3-5 秒内，确保业务连续性达到 99.999% 的可用性标准。

生态兼容性：需兼容现有应用生态，降低迁移成本。支持 Oracle、MySQL 等主流数据库语法，迁移过程中业务代码改动量应最小化。

自主可控要求：在信创背景下，需选择拥有自主知识产权、通过国家相关安全认证的国产数据库产品。

2.2 集群架构设计实践

基于上述考量，某市一卡通系统采用了以下数据库集群架构：

-- 集群节点配置示例
CREATE CLUSTER card_cluster WITH (
    cluster_type = 'streaming_replication',
    primary_node = 'node1:5432',
    standby_nodes = ARRAY['node2:5432', 'node3:5432', 'node4:5432'],
    sync_standby_names = 'node2,node3',
    application_name = 'card_system'
);

-- 读写分离配置
CREATE PUBLICATION card_publication FOR ALL TABLES;
CREATE SUBSCRIPTION card_subscription 
CONNECTION 'host=node2 port=5432 dbname=carddb user=replicator' 
PUBLICATION card_publication;

主备同步机制：采用物理日志流复制技术，确保主备节点数据实时一致。通过 WAL（Write-Ahead Logging）机制，在事务提交前先将日志写入持久存储，再同步到备节点。

负载均衡策略：写请求定向主节点，读请求根据各备节点的负载状态动态分配。通过代理层（如 HAProxy）实现智能路由，提升整体吞吐能力。

容灾部署模式：采用同城双中心部署，主备节点跨机房分布。通过双网架构设计，网络切换时间压缩至 5 秒内，远超行业标准。

2.3 数据表结构设计

一卡通系统核心数据表设计需遵循规范化原则，同时兼顾查询性能：

-- 用户信息表
CREATE TABLE user_info (
    user_id BIGINT PRIMARY KEY,
    user_name VARCHAR(50) NOT NULL,
    id_card VARCHAR(18) UNIQUE NOT NULL,
    phone VARCHAR(11),
    email VARCHAR(100),
    user_type SMALLINT NOT NULL DEFAULT 1, -- 1:学生 2:教职工 3:其他
    status SMALLINT NOT NULL DEFAULT 1, -- 1:正常 2:挂失 3:冻结 4:注销
    create_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    update_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_phone (phone),
    INDEX idx_id_card (id_card),
    INDEX idx_status (status)
) PARTITION BY RANGE (user_id);

-- 卡片信息表
CREATE TABLE card_info (
    card_id VARCHAR(20) PRIMARY KEY,
    user_id BIGINT NOT NULL,
    card_type SMALLINT NOT NULL DEFAULT 1, -- 1:实体卡 2:虚拟卡
    card_status SMALLINT NOT NULL DEFAULT , 
    balance (, )   ,
    daily_limit (, )  ,
    single_limit (, )  ,
    issue_date  ,
    expire_date ,
    last_used_time ,
     (user_id)  user_info(user_id),
    INDEX idx_user_id (user_id),
    INDEX idx_card_status (card_status),
    INDEX idx_last_used (last_used_time)
);


 transaction_log (
    trans_id BIGSERIAL ,
    card_id () ,
    trans_type  , 
    trans_amount (, ) ,
    before_balance (, ) ,
    after_balance (, ) ,
    terminal_id (),
    merchant_id (),
    location_info JSONB,
    trans_time    ,
    status    , 
    remark (),
     (card_id)  card_info(card_id),
    INDEX idx_card_time (card_id, trans_time),
    INDEX idx_merchant_time (merchant_id, trans_time),
    INDEX idx_trans_type (trans_type, trans_time)
)    (trans_time);


 merchant_info (
    merchant_id () ,
    merchant_name () ,
    merchant_type  , 
    contact_phone (),
    contact_person (),
    settlement_rate (, )  , 
    settlement_account (),
    status    ,
    create_time    ,
    INDEX idx_merchant_type (merchant_type),
    INDEX idx_status (status)
);

三、核心功能模块实现

3.1 用户管理模块

用户管理模块负责用户生命周期的全流程管理，包括用户注册、信息维护、状态变更等操作。

代码案例 1：用户信息增删改查

// UserController.java
@RestController
@RequestMapping("/api/user")
@Slf4j
public class UserController {
    @Autowired
    private UserService userService;

    /**
     * 新增用户
     */
    @PostMapping("/add")
    public Result<UserVO> addUser(@Valid @RequestBody UserDTO userDTO) {
        try {
            UserVO userVO = userService.addUser(userDTO);
            return Result.success(userVO);
        } catch (BusinessException e) {
            log.error("新增用户失败：{}", e.getMessage(), e);
            return Result.error(e.getCode(), e.getMessage());
        }
    }

    /**
     * 删除用户（逻辑删除）
     */
    @PostMapping("/delete/{userId}")
    public Result<Void> deleteUser(@PathVariable Long userId) {
        try {
            userService.deleteUser(userId);
            return Result.success();
        } catch (BusinessException e) {
            log.error("删除用户失败：{}", e.getMessage(), e);
            return Result.error(e.getCode(), e.getMessage());
        }
    }

    /**
     * 更新用户信息
     */
    @PostMapping("/update")
    public Result<UserVO> updateUser(@Valid @RequestBody UserUpdateDTO updateDTO) {
        try {
            UserVO   userService.updateUser(updateDTO);
             Result.success(userVO);
        }  (BusinessException e) {
            log.error(, e.getMessage(), e);
             Result.error(e.getCode(), e.getMessage());
        }
    }

    
    
     Result<UserVO>  {
         {
               userService.queryUser(userId);
             Result.success(userVO);
        }  (BusinessException e) {
            log.error(, e.getMessage(), e);
             Result.error(e.getCode(), e.getMessage());
        }
    }

    
    
     Result<PageResult<UserVO>>  {
            ();
        queryDTO.setPageNum(pageNum);
        queryDTO.setPageSize(pageSize);
        queryDTO.setUserName(userName);
        queryDTO.setUserType(userType);
        queryDTO.setStatus(status);
        PageResult<UserVO> pageResult = userService.listUsers(queryDTO);
         Result.success(pageResult);
    }
}




     {
    
     UserMapper userMapper;
    
     CardMapper cardMapper;
    
     RedisTemplate<String, Object> redisTemplate;

    
    
     UserVO  {
        
           userMapper.selectByIdCard(userDTO.getIdCard());
         (existingUser != ) {
              (ErrorCode.USER_ID_CARD_EXIST);
        }
        
            ();
        BeanUtils.copyProperties(userDTO, userInfo);
        userInfo.setStatus(UserStatus.NORMAL.getCode());
        userInfo.setCreateTime( ());
        userInfo.setUpdateTime( ());
        userMapper.insert(userInfo);
        
           createDefaultCard(userInfo.getUserId());
        cardMapper.insert(cardInfo);
        
        cacheUserInfo(userInfo);
         convertToVO(userInfo);
    }

    
    
       {
           userMapper.selectById(userId);
         (userInfo == ) {
              (ErrorCode.USER_NOT_EXIST);
        }
        
        userInfo.setStatus(UserStatus.DELETED.getCode());
        userInfo.setUpdateTime( ());
        userMapper.updateById(userInfo);
        
        cardMapper.updateStatusByUserId(userId, CardStatus.DELETED.getCode());
        
        clearUserCache(userId);
    }

    
    
     UserVO  {
           userMapper.selectById(updateDTO.getUserId());
         (userInfo == ) {
              (ErrorCode.USER_NOT_EXIST);
        }
        
         (StringUtils.isNotBlank(updateDTO.getPhone())) {
            userInfo.setPhone(updateDTO.getPhone());
        }
         (StringUtils.isNotBlank(updateDTO.getEmail())) {
            userInfo.setEmail(updateDTO.getEmail());
        }
        userInfo.setUpdateTime( ());
        userMapper.updateById(userInfo);
        
        cacheUserInfo(userInfo);
         convertToVO(userInfo);
    }

    
     UserVO  {
        
            + userId;
           (UserVO) redisTemplate.opsForValue().get(cacheKey);
         (cachedUser != ) {
             cachedUser;
        }
        
           userMapper.selectById(userId);
         (userInfo == ) {
              (ErrorCode.USER_NOT_EXIST);
        }
           convertToVO(userInfo);
        
        redisTemplate.opsForValue().set(cacheKey, userVO, , TimeUnit.MINUTES);
         userVO;
    }

    
     PageResult<UserVO>  {
        PageHelper.startPage(queryDTO.getPageNum(), queryDTO.getPageSize());
        List<UserInfo> userList = userMapper.selectByCondition(queryDTO);
        PageInfo<UserInfo> pageInfo =  <>(userList);
        List<UserVO> voList = userList.stream()
                .map(::convertToVO)
                .collect(Collectors.toList());
          <>(voList, pageInfo.getTotal(), pageInfo.getPageNum(), pageInfo.getPageSize());
    }

       {
            + userInfo.getUserId();
           convertToVO(userInfo);
        redisTemplate.opsForValue().set(cacheKey, userVO, , TimeUnit.MINUTES);
    }

       {
            + userId;
        redisTemplate.delete(cacheKey);
    }
}

3.2 交易处理模块

交易处理模块是一卡通系统的核心，负责处理消费、充值、转账等资金操作，必须保证数据的一致性和事务的原子性。

代码案例 2：交易流水增删改查

// TransactionController.java
@RestController
@RequestMapping("/api/transaction")
@Slf4j
public class TransactionController {
    @Autowired
    private TransactionService transactionService;

    /**
     * 消费扣款
     */
    @PostMapping("/consume")
    public Result<TransactionVO> consume(@Valid @RequestBody ConsumeDTO consumeDTO) {
        try {
            TransactionVO transactionVO = transactionService.consume(consumeDTO);
            return Result.success(transactionVO);
        } catch (BusinessException e) {
            log.error("消费扣款失败：{}", e.getMessage(), e);
            return Result.error(e.getCode(), e.getMessage());
        }
    }

    /**
     * 账户充值
     */
    @PostMapping("/recharge")
    public Result<TransactionVO> recharge(@Valid @RequestBody RechargeDTO rechargeDTO) {
        try {
            TransactionVO transactionVO = transactionService.recharge(rechargeDTO);
            return Result.success(transactionVO);
        } catch (BusinessException e) {
            log.error("账户充值失败：{}", e.getMessage(), e);
            return Result.error(e.getCode(), e.getMessage());
        }
    }

    /**
     * 查询交易详情
     */
    @GetMapping("/detail/{transId}")
    public Result<TransactionVO>  {
         {
               transactionService.getDetail(transId);
             Result.success(transactionVO);
        }  (BusinessException e) {
            log.error(, e.getMessage(), e);
             Result.error(e.getCode(), e.getMessage());
        }
    }

    
    
     Result<PageResult<TransactionVO>>  {
            ();
        queryDTO.setPageNum(pageNum);
        queryDTO.setPageSize(pageSize);
        queryDTO.setCardId(cardId);
        queryDTO.setTransType(transType);
         (StringUtils.isNotBlank(startTime)) {
            queryDTO.setStartTime(DateUtil.parse(startTime));
        }
         (StringUtils.isNotBlank(endTime)) {
            queryDTO.setEndTime(DateUtil.parse(endTime));
        }
        PageResult<TransactionVO> pageResult = transactionService.listTransactions(queryDTO);
         Result.success(pageResult);
    }

    
    
     Result<Void>  {
         {
            transactionService.reverseTransaction(transId);
             Result.success();
        }  (BusinessException e) {
            log.error(, e.getMessage(), e);
             Result.error(e.getCode(), e.getMessage());
        }
    }
}




     {
    
     TransactionMapper transactionMapper;
    
     CardMapper cardMapper;
    
     RedisTemplate<String, Object> redisTemplate;
    
     RabbitTemplate rabbitTemplate;

    
    
     TransactionVO  {
        
           cardMapper.selectByCardId(consumeDTO.getCardId());
         (cardInfo == ) {
              (ErrorCode.CARD_NOT_EXIST);
        }
        
         (cardInfo.getCardStatus() != CardStatus.NORMAL.getCode()) {
              (ErrorCode.CARD_STATUS_ABNORMAL);
        }
        
        validateConsumeLimit(cardInfo, consumeDTO.getAmount());
        
            + consumeDTO.getCardId();
           redissonClient.getLock(lockKey);
         {
               lock.tryLock(, , TimeUnit.SECONDS);
             (!locked) {
                  (ErrorCode.SYSTEM_BUSY);
            }
            
               cardInfo.getBalance();
               beforeBalance.subtract(consumeDTO.getAmount());
             (afterBalance.compareTo(BigDecimal.ZERO) < ) {
                  (ErrorCode.INSUFFICIENT_BALANCE);
            }
            cardInfo.setBalance(afterBalance);
            cardInfo.setLastUsedTime( ());
            cardMapper.updateBalance(cardInfo);
            
                ();
            transactionLog.setCardId(consumeDTO.getCardId());
            transactionLog.setTransType(TransType.CONSUME.getCode());
            transactionLog.setTransAmount(consumeDTO.getAmount());
            transactionLog.setBeforeBalance(beforeBalance);
            transactionLog.setAfterBalance(afterBalance);
            transactionLog.setTerminalId(consumeDTO.getTerminalId());
            transactionLog.setMerchantId(consumeDTO.getMerchantId());
            transactionLog.setLocationInfo(consumeDTO.getLocationInfo());
            transactionLog.setTransTime( ());
            transactionLog.setStatus(TransStatus.SUCCESS.getCode());
            transactionMapper.insert(transactionLog);
            
            updateCardCache(cardInfo);
            
            sendTransactionNotification(transactionLog);
             convertToVO(transactionLog);
        }  (InterruptedException e) {
            Thread.currentThread().interrupt();
              (ErrorCode.SYSTEM_ERROR);
        }  {
             (lock.isHeldByCurrentThread()) {
                lock.unlock();
            }
        }
    }

    
    
     TransactionVO  {
        
           cardMapper.selectByCardId(rechargeDTO.getCardId());
         (cardInfo == ) {
              (ErrorCode.CARD_NOT_EXIST);
        }
        
        validateRechargeChannel(rechargeDTO.getChannel());
        
           cardInfo.getBalance();
           beforeBalance.add(rechargeDTO.getAmount());
        cardInfo.setBalance(afterBalance);
        cardMapper.updateBalance(cardInfo);
        
            ();
        transactionLog.setCardId(rechargeDTO.getCardId());
        transactionLog.setTransType(TransType.RECHARGE.getCode());
        transactionLog.setTransAmount(rechargeDTO.getAmount());
        transactionLog.setBeforeBalance(beforeBalance);
        transactionLog.setAfterBalance(afterBalance);
        transactionLog.setTerminalId(rechargeDTO.getTerminalId());
        transactionLog.setChannel(rechargeDTO.getChannel());
        transactionLog.setChannelOrderNo(rechargeDTO.getChannelOrderNo());
        transactionLog.setTransTime( ());
        transactionLog.setStatus(TransStatus.SUCCESS.getCode());
        transactionMapper.insert(transactionLog);
        
        updateCardCache(cardInfo);
        
        sendRechargeNotification(transactionLog);
         convertToVO(transactionLog);
    }

    
     TransactionVO  {
        
            + transId;
           (TransactionVO) redisTemplate.opsForValue().get(cacheKey);
         (cachedTrans != ) {
             cachedTrans;
        }
        
           transactionMapper.selectById(transId);
         (transactionLog == ) {
              (ErrorCode.TRANSACTION_NOT_EXIST);
        }
           convertToVO(transactionLog);
        
        redisTemplate.opsForValue().set(cacheKey, transactionVO, , TimeUnit.MINUTES);
         transactionVO;
    }

    
     PageResult<TransactionVO>  {
        PageHelper.startPage(queryDTO.getPageNum(), queryDTO.getPageSize());
        List<TransactionLog> transactionList = transactionMapper.selectByCondition(queryDTO);
        PageInfo<TransactionLog> pageInfo =  <>(transactionList);
        List<TransactionVO> voList = transactionList.stream()
                .map(::convertToVO)
                .collect(Collectors.toList());
          <>(voList, pageInfo.getTotal(), pageInfo.getPageNum(), pageInfo.getPageSize());
    }

    
    
       {
           transactionMapper.selectById(transId);
         (originalTrans == ) {
              (ErrorCode.TRANSACTION_NOT_EXIST);
        }
        
         (!canReverse(originalTrans)) {
              (ErrorCode.TRANSACTION_CANNOT_REVERSE);
        }
        
           cardMapper.selectByCardId(originalTrans.getCardId());
        
           cardInfo.getBalance();
           currentBalance.add(originalTrans.getTransAmount());
        cardInfo.setBalance(reversedBalance);
        cardMapper.updateBalance(cardInfo);
        
            ();
        reverseLog.setCardId(originalTrans.getCardId());
        reverseLog.setTransType(TransType.REVERSE.getCode());
        reverseLog.setTransAmount(originalTrans.getTransAmount());
        reverseLog.setBeforeBalance(currentBalance);
        reverseLog.setAfterBalance(reversedBalance);
        reverseLog.setRelatedTransId(transId);
        reverseLog.setTransTime( ());
        reverseLog.setStatus(TransStatus.SUCCESS.getCode());
        reverseLog.setRemark( + transId);
        transactionMapper.insert(reverseLog);
        
        originalTrans.setStatus(TransStatus.REVERSED.getCode());
        transactionMapper.updateStatus(originalTrans);
        
        updateCardCache(cardInfo);
        clearTransactionCache(transId);
    }

       {
        
         (cardInfo.getSingleLimit() !=  && amount.compareTo(cardInfo.getSingleLimit()) > ) {
              (ErrorCode.EXCEED_SINGLE_LIMIT);
        }
        
            + cardInfo.getCardId() +  + DateUtil.today();
           (BigDecimal) redisTemplate.opsForValue().get(dailyKey);
         (dailyConsume == ) {
            dailyConsume = BigDecimal.ZERO;
        }
           dailyConsume.add(amount);
         (cardInfo.getDailyLimit() !=  && afterDaily.compareTo(cardInfo.getDailyLimit()) > ) {
              (ErrorCode.EXCEED_DAILY_LIMIT);
        }
    }
}

3.3 清分结算模块

清分结算模块负责处理商户结算、资金对账、差错处理等核心财务功能，对数据准确性和事务一致性要求极高。

代码案例 3：商户结算增删改查

// SettlementController.java
@RestController
@RequestMapping("/api/settlement")
@Slf4j
public class SettlementController {
    @Autowired
    private SettlementService settlementService;

    /**
     * 生成日终结算单
     */
    @PostMapping("/daily/generate")
    public Result<SettlementVO> generateDailySettlement(@RequestParam String settleDate) {
        try {
            SettlementVO settlementVO = settlementService.generateDailySettlement(settleDate);
            return Result.success(settlementVO);
        } catch (BusinessException e) {
            log.error("生成日终结算单失败：{}", e.getMessage(), e);
            return Result.error(e.getCode(), e.getMessage());
        }
    }

    /**
     * 查询结算单详情
     */
    @GetMapping("/detail/{settleId}")
    public Result<SettlementDetailVO> getSettlementDetail(@PathVariable Long settleId) {
        try {
            SettlementDetailVO detailVO = settlementService.getSettlementDetail(settleId);
            return Result.success(detailVO);
        } catch (BusinessException e) {
            log.error("查询结算单详情失败：{}", e.getMessage(), e);
            return Result.error(e.getCode(), e.getMessage());
        }
    }

    /**
     * 确认结算单
     */
    @PostMapping("/confirm/{settleId}")
    public Result<Void> confirmSettlement {
         {
            settlementService.confirmSettlement(settleId);
             Result.success();
        }  (BusinessException e) {
            log.error(, e.getMessage(), e);
             Result.error(e.getCode(), e.getMessage());
        }
    }

    
    
     Result<PageResult<SettlementVO>>  {
            ();
        queryDTO.setPageNum(pageNum);
        queryDTO.setPageSize(pageSize);
        queryDTO.setMerchantId(merchantId);
        queryDTO.setSettleStatus(settleStatus);
        queryDTO.setStartDate(startDate);
        queryDTO.setEndDate(endDate);
        PageResult<SettlementVO> pageResult = settlementService.listSettlements(queryDTO);
         Result.success(pageResult);
    }

    
    
     Result<Void>  {
         {
            settlementService.deleteSettlement(settleId);
             Result.success();
        }  (BusinessException e) {
            log.error(, e.getMessage(), e);
             Result.error(e.getCode(), e.getMessage());
        }
    }
}




     {
    
     SettlementMapper settlementMapper;
    
     SettlementDetailMapper settlementDetailMapper;
    
     TransactionMapper transactionMapper;
    
     MerchantMapper merchantMapper;
    
     RabbitTemplate rabbitTemplate;

    
    
     SettlementVO  {
        
         (!DateUtil.isValidDate(settleDate)) {
              (ErrorCode.INVALID_DATE_FORMAT);
        }
        
           settlementMapper.selectBySettleDate(settleDate);
         (existing != ) {
              (ErrorCode.SETTLEMENT_ALREADY_EXISTS);
        }
        
        List<MerchantInfo> merchantList = merchantMapper.selectAllActive();
        
            ();
        settlement.setSettleDate(settleDate);
        settlement.setSettleStatus(SettleStatus.DRAFT.getCode());
        settlement.setTotalAmount(BigDecimal.ZERO);
        settlement.setTotalFee(BigDecimal.ZERO);
        settlement.setSettleAmount(BigDecimal.ZERO);
        settlement.setCreateTime( ());
        settlementMapper.insert(settlement);
        List<SettlementDetail> detailList =  <>();
           BigDecimal.ZERO;
           BigDecimal.ZERO;
        
         (MerchantInfo merchant : merchantList) {
            
               transactionMapper.selectDailySummary(
                    merchant.getMerchantId(), settleDate);
             (summary ==  || summary.getTotalAmount().compareTo(BigDecimal.ZERO) == ) {
                ;
            }
            
               calculateFee(summary.getTotalAmount(), merchant.getSettlementRate());
               summary.getTotalAmount().subtract(fee);
            
                ();
            detail.setSettleId(settlement.getSettleId());
            detail.setMerchantId(merchant.getMerchantId());
            detail.setMerchantName(merchant.getMerchantName());
            detail.setTransCount(summary.getTransCount());
            detail.setTotalAmount(summary.getTotalAmount());
            detail.setFeeAmount(fee);
            detail.setSettleAmount(settleAmount);
            detail.setSettleStatus(SettleStatus.DRAFT.getCode());
            detail.setCreateTime( ());
            settlementDetailMapper.insert(detail);
            detailList.add(detail);
            
            totalAmount = totalAmount.add(summary.getTotalAmount());
            totalFee = totalFee.add(fee);
        }
        
        settlement.setTotalAmount(totalAmount);
        settlement.setTotalFee(totalFee);
        settlement.setSettleAmount(totalAmount.subtract(totalFee));
        settlement.setDetailCount(detailList.size());
        settlement.setUpdateTime( ());
        settlementMapper.updateById(settlement);
        
        sendSettlementGeneratedNotification(settlement);
         convertToVO(settlement);
    }

    
     SettlementDetailVO  {
        
           settlementMapper.selectById(settleId);
         (settlement == ) {
              (ErrorCode.SETTLEMENT_NOT_EXIST);
        }
        
        List<SettlementDetail> detailList = settlementDetailMapper.selectBySettleId(settleId);
        
            ();
        detailVO.setSettlement(convertToVO(settlement));
        detailVO.setDetails(detailList.stream()
                .map(::convertDetailToVO)
                .collect(Collectors.toList()));
         detailVO;
    }

    
    
       {
           settlementMapper.selectById(settleId);
         (settlement == ) {
              (ErrorCode.SETTLEMENT_NOT_EXIST);
        }
        
         (settlement.getSettleStatus() != SettleStatus.DRAFT.getCode()) {
              (ErrorCode.SETTLEMENT_STATUS_ERROR);
        }
        
        settlement.setSettleStatus(SettleStatus.CONFIRMED.getCode());
        settlement.setConfirmTime( ());
        settlement.setUpdateTime( ());
        settlementMapper.updateById(settlement);
        
        settlementDetailMapper.updateStatusBySettleId(settleId, SettleStatus.CONFIRMED.getCode());
        
        triggerFundTransfer(settlement);
        
        sendSettlementConfirmedNotification(settlement);
    }

    
     PageResult<SettlementVO>  {
        PageHelper.startPage(queryDTO.getPageNum(), queryDTO.getPageSize());
        List<Settlement> settlementList = settlementMapper.selectByCondition(queryDTO);
        PageInfo<Settlement> pageInfo =  <>(settlementList);
        List<SettlementVO> voList = settlementList.stream()
                .map(::convertToVO)
                .collect(Collectors.toList());
          <>(voList, pageInfo.getTotal(), pageInfo.getPageNum(), pageInfo.getPageSize());
    }

    
    
       {
           settlementMapper.selectById(settleId);
         (settlement == ) {
              (ErrorCode.SETTLEMENT_NOT_EXIST);
        }
        
         (settlement.getSettleStatus() != SettleStatus.DRAFT.getCode()) {
              (ErrorCode.SETTLEMENT_CANNOT_DELETE);
        }
        
        settlementDetailMapper.deleteBySettleId(settleId);
        
        settlementMapper.deleteById(settleId);
        
        sendSettlementDeletedNotification(settlement);
    }

     BigDecimal  {
         (rate ==  || rate.compareTo(BigDecimal.ZERO) == ) {
             BigDecimal.ZERO;
        }
        
         amount.multiply(rate).setScale(, RoundingMode.HALF_UP);
    }
}

四、性能优化策略

4.1 数据库层面优化

索引优化策略：

-- 复合索引设计
CREATE INDEX idx_card_trans ON transaction_log(card_id, trans_time DESC);
CREATE INDEX idx_merchant_trans ON transaction_log(merchant_id, trans_time DESC);
-- 函数索引
CREATE INDEX idx_user_name_lower ON user_info(LOWER(user_name));
-- 分区索引
CREATE INDEX idx_trans_time_local ON transaction_log(trans_time) LOCAL;

查询优化技巧：

-- 避免 SELECT *，只查询需要的字段
SELECT user_id, user_name, phone FROM user_info WHERE status = 1;
-- 使用覆盖索引
SELECT card_id FROM transaction_log WHERE trans_time >= '2024-01-01' AND trans_time < '2024-02-01';
-- 分页优化（避免深度分页）
SELECT * FROM transaction_log WHERE trans_id > ? ORDER BY trans_id ASC LIMIT 20;

4.2 缓存策略设计

多级缓存架构：

// 缓存配置类
@Configuration
@EnableCaching
public class CacheConfig {
    @Bean
    public CacheManager cacheManager(RedisConnectionFactory factory) {
        RedisCacheConfiguration config = RedisCacheConfiguration.defaultCacheConfig()
                .entryTtl(Duration.ofMinutes(30))
                .serializeKeysWith(RedisSerializationContext.SerializationPair
                        .fromSerializer(new StringRedisSerializer()))
                .serializeValuesWith(RedisSerializationContext.SerializationPair
                        .fromSerializer(new GenericJackson2JsonRedisSerializer()));
        // 不同业务设置不同的过期时间
        Map<String, RedisCacheConfiguration> cacheConfigs = new HashMap<>();
        cacheConfigs.put("userInfo", config.entryTtl(Duration.ofMinutes(30)));
        cacheConfigs.put("cardInfo", config.entryTtl(Duration.ofMinutes(10)));
        cacheConfigs.put("transaction", config.entryTtl(Duration.ofMinutes(5)));
        cacheConfigs.put("merchant", config.entryTtl(Duration.ofHours(1)));
        return RedisCacheManager.builder(factory)
                .cacheDefaults(config)
                .withInitialCacheConfigurations(cacheConfigs)
                .build();
    }

    @Bean
    public RedisTemplate<String, Object> redisTemplate(RedisConnectionFactory factory) {
        RedisTemplate<String, Object> template = new RedisTemplate<>();
        template.setConnectionFactory(factory);
        template.setKeySerializer(new ());
        template.setValueSerializer( ());
        template.setHashKeySerializer( ());
        template.setHashValueSerializer( ());
         template;
    }
}

缓存穿透、击穿、雪崩防护：

4.3 读写分离与分库分表

为应对海量数据和高并发读写压力，系统在数据库架构层面采用了读写分离和分库分表策略。

读写分离配置：

# application.yml 数据源配置示例
spring:
  datasource:
    dynamic:
      primary: master # 设置默认数据源
      strict: true # 严格匹配数据源，未找到报错
      datasource:
        master:
          url: jdbc:kingbase8://master-host:5432/carddb?currentSchema=public
          username: ${MASTER_DB_USER}
          password: ${MASTER_DB_PASSWORD}
          driver-class-name: com.kingbase8.Driver
        slave1:
          url: jdbc:kingbase8://slave1-host:5432/carddb?currentSchema=public
          username: ${SLAVE_DB_USER}
          password: ${SLAVE_DB_PASSWORD}
          driver-class-name: com.kingbase8.Driver
        slave2:
          url: jdbc:kingbase8://slave2-host:5432/carddb?currentSchema=public
          username: ${SLAVE_DB_USER}
          password: ${SLAVE_DB_PASSWORD}
          driver-class-name: com.kingbase8.Driver
      hikari:
        maximum-pool-size: 20
        minimum-idle: 5
        connection-timeout: 30000
        idle-timeout: 600000
        max-lifetime:

分库分表策略实现：

// 基于用户 ID 的分片策略
@Component
public class UserShardingStrategy implements PreciseShardingAlgorithm<Long> {
    @Override
    public String doSharding(Collection<String> availableTargetNames, PreciseShardingValue<Long> shardingValue) {
        Long userId = shardingValue.getValue();
        // 基于用户 ID 取模分片，分为 4 个库
        int shardIndex = Math.abs(userId.hashCode() % 4);
        for (String each : availableTargetNames) {
            if (each.endsWith("_" + shardIndex)) {
                return each;
            }
        }
        throw new IllegalArgumentException("未找到匹配的数据源");
    }
}

// 基于时间范围的分片策略（用于交易流水表）
@Component
public class TimeRangeShardingStrategy implements RangeShardingAlgorithm<Date> {
    @Override
    public Collection<String> doSharding(Collection<String> availableTargetNames, RangeShardingValue<Date> shardingValue) {
        Range<Date> range = shardingValue.getValueRange();
        Date lower = range.lowerEndpoint();
        Date upper = range.upperEndpoint();
        List<String> result = new ArrayList<>();
        Set<String> shardNames = getShardNamesBetween(lower, upper);
         (String each : availableTargetNames) {
             (shardNames.contains(each)) {
                result.add(each);
            }
        }
         result;
    }

     Set<String>  {
        Set<String> shardNames =  <>();
           Calendar.getInstance();
        calendar.setTime(start);
         (!calendar.getTime().after(end)) {
               calendar.get(Calendar.YEAR);
               calendar.get(Calendar.MONTH) + ;
               String.format(, year, month);
            shardNames.add(shardName);
            calendar.add(Calendar.MONTH, );
        }
         shardNames;
    }
}

五、高可用与容灾设计

5.1 多级故障转移机制

数据库层故障转移：

-- 主备切换脚本
CREATE OR REPLACE FUNCTION trigger_failover() RETURNS void AS $$
DECLARE
    current_primary VARCHAR(100);
    new_primary VARCHAR(100);
BEGIN
    -- 检测当前主节点状态
    SELECT node_name INTO current_primary FROM pg_stat_replication WHERE state = 'streaming' LIMIT 1;
    -- 如果主节点不可用，选择新的主节点
    IF current_primary IS NULL THEN
        -- 选择同步状态最好的备节点作为新主
        SELECT node_name INTO new_primary FROM pg_stat_wal_receiver WHERE status = 'streaming' ORDER BY last_msg_send_time DESC LIMIT 1;
        -- 执行主备切换
        PERFORM pg_promote(new_primary);
        -- 更新路由配置
        UPDATE system_config SET config_value = new_primary WHERE config_key = 'primary_db_node';
        -- 记录切换日志
        INSERT INTO failover_log (old_primary, new_primary, failover_time, reason) VALUES (current_primary, new_primary, NOW(), 'primary node failure');
    END IF;
END;
$$  plpgsql;


 EVENT  monitor_db_health  SCHEDULE    DO 
    
    IF  check_primary_health() 
        PERFORM trigger_failover();
     IF;
;

应用层故障转移：

// 数据库连接健康检查
@Component
@Slf4j
public class DatabaseHealthChecker {
    @Autowired
    private DataSource dataSource;
    @Value("${health.check.interval:5000}")
    private long checkInterval;

    @PostConstruct
    public void init() {
        ScheduledExecutorService scheduler = Executors.newScheduledThreadPool(1);
        scheduler.scheduleAtFixedRate(this::checkDatabaseHealth, 0, checkInterval, TimeUnit.MILLISECONDS);
    }

    private void checkDatabaseHealth() {
        try (Connection conn = dataSource.getConnection(); Statement stmt = conn.createStatement()) {
            // 执行简单查询检查连接
            ResultSet rs = stmt.executeQuery("SELECT 1");
            if (rs.next()) {
                HealthMonitor.setDatabaseStatus(HealthStatus.UP);
            }
        } catch (SQLException e) {
            log.error("数据库健康检查失败", e);
            HealthMonitor.setDatabaseStatus(HealthStatus.DOWN);
            // 触发降级逻辑
            triggerDegradation();
        }
    }

    private void triggerDegradation() {
        
        SystemConfig.setReadOnlyMode();
        
        notifyAllInstances(, System.currentTimeMillis());
        
            ();
        event.setEventType(FaultType.DATABASE_UNAVAILABLE);
        event.setSeverity(Severity.CRITICAL);
        event.setOccurTime( ());
        event.setDescription();
        faultEventService.recordEvent(event);
    }
}

5.2 数据同步与一致性保障

跨机房数据同步

-- 配置逻辑复制
-- 发布端配置
CREATE PUBLICATION card_publication FOR ALL TABLES;
-- 订阅端配置
CREATE SUBSCRIPTION beijing_subscription 
CONNECTION 'host=bj-db-host port=5432 dbname=carddb user=replicator password=xxxxxx' 
PUBLICATION card_publication WITH (
    copy_data = true,
    create_slot = true,
    enabled = true,
    slot_name = 'beijing_slot'
);
-- 监控复制延迟
SELECT client_addr, application_name, state, sync_state, 
pg_wal_lsn_diff(pg_current_wal_lsn(), sent_lsn) AS sent_lag, 
pg_wal_lsn_diff(pg_current_wal_lsn(), write_lsn) AS write_lag, 
pg_wal_lsn_diff(pg_current_wal_lsn(), flush_lsn) AS flush_lag, 
pg_wal_lsn_diff(pg_current_wal_lsn(), replay_lsn) AS replay_lag 
FROM pg_stat_replication;

// 缓存服务实现
@Service
public class CacheServiceImpl implements CacheService {
    @Autowired
    private RedisTemplate<String, Object> redisTemplate;
    @Autowired
    private RedissonClient redissonClient;

    /**
     * 防缓存穿透查询
     */
    @Override
    public <T> T queryWithPenetrationProtection(String key, Class<T> clazz, Supplier<T> dbSupplier, Duration ttl) {
        // 先查缓存
        T value = (T) redisTemplate.opsForValue().get(key);
        if (value != null) {
            // 空值标记处理
            if (value instanceof NullValue) {
                return null;
            }
            return value;
        }
        // 使用分布式锁防止缓存击穿
        String lockKey = "cache:lock:" + key;
        RLock lock = redissonClient.getLock(lockKey);
        try {
            // 尝试获取锁，等待 100ms，锁持有时间 5s
            boolean locked = lock.tryLock(100, 5000, TimeUnit.MILLISECONDS);
            if (locked) {
                // 再次检查缓存（双重检查）
                value = (T) redisTemplate.opsForValue().get(key);
                if (value != ) {
                     (value  NullValue) {
                         ;
                    }
                     value;
                }
                
                value = dbSupplier.get();
                
                 (value == ) {
                    
                    redisTemplate.opsForValue().set(key, NullValue.INSTANCE, Duration.ofMinutes());
                }  {
                    redisTemplate.opsForValue().set(key, value, ttl);
                }
                 value;
            }  {
                
                Thread.sleep();
                 queryWithPenetrationProtection(key, clazz, dbSupplier, ttl);
            }
        }  (InterruptedException e) {
            Thread.currentThread().interrupt();
              (ErrorCode.SYSTEM_ERROR);
        }  {
             (lock.isHeldByCurrentThread()) {
                lock.unlock();
            }
        }
    }

    
    
     <T> Map<String, T>  {
         (keys.isEmpty()) {
             Collections.emptyMap();
        }
        
        List<Object> cachedValues = redisTemplate.opsForValue().multiGet(keys);
        Map<String, T> result =  <>();
        List<String> missingKeys =  <>();
        
         (   ; i < keys.size(); i++) {
               keys.get(i);
               cachedValues.get(i);
             (value == ) {
                missingKeys.add(key);
            }   (value  NullValue) {
                
                ;
            }  {
                result.put(key, (T) value);
            }
        }
        
         (!missingKeys.isEmpty()) {
            Map<String, T> dbResult = dbSupplier.apply(missingKeys);
            result.putAll(dbResult);
            
            Map<String, Object> cacheMap =  <>();
             (String key : missingKeys) {
                   dbResult.get(key);
                 (value == ) {
                    cacheMap.put(key, NullValue.INSTANCE);
                }  {
                    cacheMap.put(key, value);
                }
            }
             (!cacheMap.isEmpty()) {
                redisTemplate.opsForValue().multiSet(cacheMap);
                
                 (String key : cacheMap.keySet()) {
                     (cacheMap.get(key)  NullValue) {
                        redisTemplate.expire(key, Duration.ofMinutes());
                    }  {
                        redisTemplate.expire(key, Duration.ofMinutes());
                    }
                }
            }
        }
         result;
    }
}

六、结语

本次一卡通系统国产化改造实践表明，基于国产数据库构建大规模、高并发、高可用的核心业务系统是完全可行的。通过合理的架构设计、精细的性能优化、完善的容灾方案和严谨的迁移策略，不仅实现了技术自主可控，更在性能、稳定性和可扩展性等方面获得了显著提升。

随着数字化转型的深入推进和信息技术应用创新产业的快速发展，国产基础软件将在更多关键业务场景中发挥重要作用。本案例的技术方案和实践经验，可为类似系统的建设和改造提供有价值的参考，共同推动我国数字基础设施的自主创新发展。

高并发场景下一卡通系统数据库架构设计与实践

引言：数字化时代的一卡通系统挑战

一、一卡通系统架构演进与设计原则

1.1 从集中式到分布式架构的演进

1.2 核心设计原则

二、数据库选型与架构设计

2.1 数据库技术选型考量

2.2 集群架构设计实践

2.3 数据表结构设计

三、核心功能模块实现

3.1 用户管理模块

3.2 交易处理模块

3.3 清分结算模块

四、性能优化策略

4.1 数据库层面优化

4.2 缓存策略设计

4.3 读写分离与分库分表

五、高可用与容灾设计

5.1 多级故障转移机制

5.2 数据同步与一致性保障

六、结语

更多推荐文章

相关免费在线工具

高并发场景下一卡通系统数据库架构设计与实践

引言：数字化时代的一卡通系统挑战

一、一卡通系统架构演进与设计原则

1.1 从集中式到分布式架构的演进

1.2 核心设计原则

二、数据库选型与架构设计

2.1 数据库技术选型考量

2.2 集群架构设计实践

2.3 数据表结构设计

三、核心功能模块实现

3.1 用户管理模块

3.2 交易处理模块

3.3 清分结算模块

四、性能优化策略

4.1 数据库层面优化

4.2 缓存策略设计

4.3 读写分离与分库分表

五、高可用与容灾设计

5.1 多级故障转移机制

5.2 数据同步与一致性保障

六、结语

微信扫一扫，关注极客日志

更多推荐文章

相关免费在线工具