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

主备同步机制：采用物理日志流复制技术，确保主备节点数据实时一致。通过 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 1, -- 1:正常 2:挂失 3:补办中 4:注销
    balance DECIMAL(12, 2) NOT NULL DEFAULT 0.00,
    daily_limit DECIMAL(10, 2) DEFAULT 500.00,
    single_limit DECIMAL(10, 2) DEFAULT 100.00,
    issue_date DATE NOT NULL,
    expire_date DATE,
    last_used_time TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES user_info(user_id),
    INDEX idx_user_id (user_id),
    INDEX idx_card_status (card_status),
    INDEX idx_last_used (last_used_time)
);

-- 交易流水表（按时间分区）
CREATE TABLE transaction_log (
    trans_id BIGSERIAL PRIMARY KEY,
    card_id VARCHAR(20) NOT NULL,
    trans_type SMALLINT NOT NULL, -- 1:消费 2:充值 3:转账 4:退款
    trans_amount DECIMAL(12, 2) NOT NULL,
    before_balance DECIMAL(12, 2) NOT NULL,
    after_balance DECIMAL(12, 2) NOT NULL,
    terminal_id VARCHAR(20),
    merchant_id VARCHAR(20),
    location_info JSONB,
    trans_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    status SMALLINT NOT NULL DEFAULT 1, -- 1:成功 2:失败 3:处理中
    remark VARCHAR(200),
    FOREIGN KEY (card_id) REFERENCES 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)
) PARTITION BY RANGE (trans_time);

-- 商户信息表
CREATE TABLE merchant_info (
    merchant_id VARCHAR(20) PRIMARY KEY,
    merchant_name VARCHAR(100) NOT NULL,
    merchant_type SMALLINT NOT NULL, -- 1:食堂 2:超市 3:图书馆 4:其他
    contact_phone VARCHAR(11),
    contact_person VARCHAR(50),
    settlement_rate DECIMAL(5, 4) DEFAULT 0.0000, -- 结算费率
    settlement_account VARCHAR(30),
    status SMALLINT NOT NULL DEFAULT 1,
    create_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    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 userVO = userService.updateUser(updateDTO);
            return Result.success(userVO);
        } catch (BusinessException e) {
            log.error("更新用户失败：{}", e.getMessage(), e);
            return Result.error(e.getCode(), e.getMessage());
        }
    }

    /**
     * 查询用户信息
     */
    @GetMapping("/query/{userId}")
    public Result<UserVO> queryUser(@PathVariable Long userId) {
        try {
            UserVO userVO = userService.queryUser(userId);
            return Result.success(userVO);
        } catch (BusinessException e) {
            log.error("查询用户失败：{}", e.getMessage(), e);
            return Result.error(e.getCode(), e.getMessage());
        }
    }

    /**
     * 分页查询用户列表
     */
    @GetMapping("/list")
    public Result<PageResult<UserVO>> listUsers(
            @RequestParam(defaultValue = "1") Integer pageNum,
            @RequestParam(defaultValue = "10") Integer pageSize,
            @RequestParam(required = false) String userName,
            @RequestParam(required = false) Integer userType,
            @RequestParam(required = false) Integer status) {
        UserQueryDTO queryDTO = new UserQueryDTO();
        queryDTO.setPageNum(pageNum);
        queryDTO.setPageSize(pageSize);
        queryDTO.setUserName(userName);
        queryDTO.setUserType(userType);
        queryDTO.setStatus(status);
        PageResult<UserVO> pageResult = userService.listUsers(queryDTO);
        return Result.success(pageResult);
    }
}

// UserServiceImpl.java
@Service
@Slf4j
public class UserServiceImpl implements UserService {
    @Autowired
    private UserMapper userMapper;
    @Autowired
    private CardMapper cardMapper;
    @Autowired
    private RedisTemplate<String, Object> redisTemplate;

    @Override
    @Transactional(rollbackFor = Exception.class)
    public UserVO addUser(UserDTO userDTO) {
        // 校验身份证号唯一性
        UserInfo existingUser = userMapper.selectByIdCard(userDTO.getIdCard());
        if (existingUser != null) {
            throw new BusinessException(ErrorCode.USER_ID_CARD_EXIST);
        }
        // 创建用户信息
        UserInfo userInfo = new UserInfo();
        BeanUtils.copyProperties(userDTO, userInfo);
        userInfo.setStatus(UserStatus.NORMAL.getCode());
        userInfo.setCreateTime(new Date());
        userInfo.setUpdateTime(new Date());
        userMapper.insert(userInfo);
        // 创建默认卡片
        CardInfo cardInfo = createDefaultCard(userInfo.getUserId());
        cardMapper.insert(cardInfo);
        // 缓存用户信息
        cacheUserInfo(userInfo);
        return convertToVO(userInfo);
    }

    @Override
    @Transactional(rollbackFor = Exception.class)
    public void deleteUser(Long userId) {
        UserInfo userInfo = userMapper.selectById(userId);
        if (userInfo == null) {
            throw new BusinessException(ErrorCode.USER_NOT_EXIST);
        }
        // 逻辑删除，更新状态为注销
        userInfo.setStatus(UserStatus.DELETED.getCode());
        userInfo.setUpdateTime(new Date());
        userMapper.updateById(userInfo);
        // 同步更新卡片状态
        cardMapper.updateStatusByUserId(userId, CardStatus.DELETED.getCode());
        // 清除缓存
        clearUserCache(userId);
    }

    @Override
    @Transactional(rollbackFor = Exception.class)
    public UserVO updateUser(UserUpdateDTO updateDTO) {
        UserInfo userInfo = userMapper.selectById(updateDTO.getUserId());
        if (userInfo == null) {
            throw new BusinessException(ErrorCode.USER_NOT_EXIST);
        }
        // 更新用户信息
        if (StringUtils.isNotBlank(updateDTO.getPhone())) {
            userInfo.setPhone(updateDTO.getPhone());
        }
        if (StringUtils.isNotBlank(updateDTO.getEmail())) {
            userInfo.setEmail(updateDTO.getEmail());
        }
        userInfo.setUpdateTime(new Date());
        userMapper.updateById(userInfo);
        // 更新缓存
        cacheUserInfo(userInfo);
        return convertToVO(userInfo);
    }

    @Override
    public UserVO queryUser(Long userId) {
        // 先查缓存
        String cacheKey = "user:info:" + userId;
        UserVO cachedUser = (UserVO) redisTemplate.opsForValue().get(cacheKey);
        if (cachedUser != null) {
            return cachedUser;
        }
        // 缓存未命中，查询数据库
        UserInfo userInfo = userMapper.selectById(userId);
        if (userInfo == null) {
            throw new BusinessException(ErrorCode.USER_NOT_EXIST);
        }
        UserVO userVO = convertToVO(userInfo);
        // 写入缓存
        redisTemplate.opsForValue().set(cacheKey, userVO, 30, TimeUnit.MINUTES);
        return userVO;
    }

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

    private void cacheUserInfo(UserInfo userInfo) {
        String cacheKey = "user:info:" + userInfo.getUserId();
        UserVO userVO = convertToVO(userInfo);
        redisTemplate.opsForValue().set(cacheKey, userVO, 30, TimeUnit.MINUTES);
    }

    private void clearUserCache(Long userId) {
        String cacheKey = "user:info:" + 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> getDetail(@PathVariable Long transId) {
        try {
            TransactionVO transactionVO = transactionService.getDetail(transId);
            return Result.success(transactionVO);
        } catch (BusinessException e) {
            log.error("查询交易详情失败：{}", e.getMessage(), e);
            return Result.error(e.getCode(), e.getMessage());
        }
    }

    /**
     * 分页查询交易流水
     */
    @GetMapping("/list")
    public Result<PageResult<TransactionVO>> listTransactions(
            @RequestParam(defaultValue = "1") Integer pageNum,
            @RequestParam(defaultValue = "20") Integer pageSize,
            @RequestParam(required = false) String cardId,
            @RequestParam(required = false) Integer transType,
            @RequestParam(required = false) String startTime,
            @RequestParam(required = false) String endTime) {
        TransactionQueryDTO queryDTO = new TransactionQueryDTO();
        queryDTO.setPageNum(pageNum);
        queryDTO.setPageSize(pageSize);
        queryDTO.setCardId(cardId);
        queryDTO.setTransType(transType);
        if (StringUtils.isNotBlank(startTime)) {
            queryDTO.setStartTime(DateUtil.parse(startTime));
        }
        if (StringUtils.isNotBlank(endTime)) {
            queryDTO.setEndTime(DateUtil.parse(endTime));
        }
        PageResult<TransactionVO> pageResult = transactionService.listTransactions(queryDTO);
        return Result.success(pageResult);
    }

    /**
     * 交易冲正（异常处理）
     */
    @PostMapping("/reverse/{transId}")
    public Result<Void> reverseTransaction(@PathVariable Long transId) {
        try {
            transactionService.reverseTransaction(transId);
            return Result.success();
        } catch (BusinessException e) {
            log.error("交易冲正失败：{}", e.getMessage(), e);
            return Result.error(e.getCode(), e.getMessage());
        }
    }
}

// TransactionServiceImpl.java
@Service
@Slf4j
public class TransactionServiceImpl implements TransactionService {
    @Autowired
    private TransactionMapper transactionMapper;
    @Autowired
    private CardMapper cardMapper;
    @Autowired
    private RedisTemplate<String, Object> redisTemplate;
    @Autowired
    private RabbitTemplate rabbitTemplate;

    @Override
    @Transactional(rollbackFor = Exception.class)
    public TransactionVO consume(ConsumeDTO consumeDTO) {
        // 获取卡片信息
        CardInfo cardInfo = cardMapper.selectByCardId(consumeDTO.getCardId());
        if (cardInfo == null) {
            throw new BusinessException(ErrorCode.CARD_NOT_EXIST);
        }
        // 校验卡片状态
        if (cardInfo.getCardStatus() != CardStatus.NORMAL.getCode()) {
            throw new BusinessException(ErrorCode.CARD_STATUS_ABNORMAL);
        }
        // 校验消费限额
        validateConsumeLimit(cardInfo, consumeDTO.getAmount());
        // 使用分布式锁保证并发安全
        String lockKey = "card:consume:lock:" + consumeDTO.getCardId();
        RLock lock = redissonClient.getLock(lockKey);
        try {
            boolean locked = lock.tryLock(3, 10, TimeUnit.SECONDS);
            if (!locked) {
                throw new BusinessException(ErrorCode.SYSTEM_BUSY);
            }
            // 扣减余额
            BigDecimal beforeBalance = cardInfo.getBalance();
            BigDecimal afterBalance = beforeBalance.subtract(consumeDTO.getAmount());
            if (afterBalance.compareTo(BigDecimal.ZERO) < 0) {
                throw new BusinessException(ErrorCode.INSUFFICIENT_BALANCE);
            }
            cardInfo.setBalance(afterBalance);
            cardInfo.setLastUsedTime(new Date());
            cardMapper.updateBalance(cardInfo);
            // 记录交易流水
            TransactionLog transactionLog = new TransactionLog();
            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(new Date());
            transactionLog.setStatus(TransStatus.SUCCESS.getCode());
            transactionMapper.insert(transactionLog);
            // 更新缓存
            updateCardCache(cardInfo);
            // 发送交易通知（异步）
            sendTransactionNotification(transactionLog);
            return convertToVO(transactionLog);
        } catch (InterruptedException e) {
            Thread.currentThread().interrupt();
            throw new BusinessException(ErrorCode.SYSTEM_ERROR);
        } finally {
            if (lock.isHeldByCurrentThread()) {
                lock.unlock();
            }
        }
    }

    @Override
    @Transactional(rollbackFor = Exception.class)
    public TransactionVO recharge(RechargeDTO rechargeDTO) {
        // 获取卡片信息
        CardInfo cardInfo = cardMapper.selectByCardId(rechargeDTO.getCardId());
        if (cardInfo == null) {
            throw new BusinessException(ErrorCode.CARD_NOT_EXIST);
        }
        // 校验充值渠道
        validateRechargeChannel(rechargeDTO.getChannel());
        // 增加余额
        BigDecimal beforeBalance = cardInfo.getBalance();
        BigDecimal afterBalance = beforeBalance.add(rechargeDTO.getAmount());
        cardInfo.setBalance(afterBalance);
        cardMapper.updateBalance(cardInfo);
        // 记录交易流水
        TransactionLog transactionLog = new TransactionLog();
        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(new Date());
        transactionLog.setStatus(TransStatus.SUCCESS.getCode());
        transactionMapper.insert(transactionLog);
        // 更新缓存
        updateCardCache(cardInfo);
        // 发送充值成功通知
        sendRechargeNotification(transactionLog);
        return convertToVO(transactionLog);
    }

    @Override
    public TransactionVO getDetail(Long transId) {
        // 先查缓存
        String cacheKey = "transaction:detail:" + transId;
        TransactionVO cachedTrans = (TransactionVO) redisTemplate.opsForValue().get(cacheKey);
        if (cachedTrans != null) {
            return cachedTrans;
        }
        // 查询数据库
        TransactionLog transactionLog = transactionMapper.selectById(transId);
        if (transactionLog == null) {
            throw new BusinessException(ErrorCode.TRANSACTION_NOT_EXIST);
        }
        TransactionVO transactionVO = convertToVO(transactionLog);
        // 写入缓存
        redisTemplate.opsForValue().set(cacheKey, transactionVO, 10, TimeUnit.MINUTES);
        return transactionVO;
    }

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

    @Override
    @Transactional(rollbackFor = Exception.class)
    public void reverseTransaction(Long transId) {
        TransactionLog originalTrans = transactionMapper.selectById(transId);
        if (originalTrans == null) {
            throw new BusinessException(ErrorCode.TRANSACTION_NOT_EXIST);
        }
        // 校验是否可冲正
        if (!canReverse(originalTrans)) {
            throw new BusinessException(ErrorCode.TRANSACTION_CANNOT_REVERSE);
        }
        // 获取卡片信息
        CardInfo cardInfo = cardMapper.selectByCardId(originalTrans.getCardId());
        // 恢复余额
        BigDecimal currentBalance = cardInfo.getBalance();
        BigDecimal reversedBalance = currentBalance.add(originalTrans.getTransAmount());
        cardInfo.setBalance(reversedBalance);
        cardMapper.updateBalance(cardInfo);
        // 记录冲正流水
        TransactionLog reverseLog = new TransactionLog();
        reverseLog.setCardId(originalTrans.getCardId());
        reverseLog.setTransType(TransType.REVERSE.getCode());
        reverseLog.setTransAmount(originalTrans.getTransAmount());
        reverseLog.setBeforeBalance(currentBalance);
        reverseLog.setAfterBalance(reversedBalance);
        reverseLog.setRelatedTransId(transId);
        reverseLog.setTransTime(new Date());
        reverseLog.setStatus(TransStatus.SUCCESS.getCode());
        reverseLog.setRemark("冲正交易，原交易 ID：" + transId);
        transactionMapper.insert(reverseLog);
        // 更新原交易状态
        originalTrans.setStatus(TransStatus.REVERSED.getCode());
        transactionMapper.updateStatus(originalTrans);
        // 更新缓存
        updateCardCache(cardInfo);
        clearTransactionCache(transId);
    }

    private void validateConsumeLimit(CardInfo cardInfo, BigDecimal amount) {
        // 校验单笔限额
        if (cardInfo.getSingleLimit() != null && amount.compareTo(cardInfo.getSingleLimit()) > 0) {
            throw new BusinessException(ErrorCode.EXCEED_SINGLE_LIMIT);
        }
        // 校验当日累计消费（从 Redis 获取）
        String dailyKey = "card:daily:consume:" + cardInfo.getCardId() + ":" + DateUtil.today();
        BigDecimal dailyConsume = (BigDecimal) redisTemplate.opsForValue().get(dailyKey);
        if (dailyConsume == null) {
            dailyConsume = BigDecimal.ZERO;
        }
        BigDecimal afterDaily = dailyConsume.add(amount);
        if (cardInfo.getDailyLimit() != null && afterDaily.compareTo(cardInfo.getDailyLimit()) > 0) {
            throw new BusinessException(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(@PathVariable Long settleId) {
        try {
            settlementService.confirmSettlement(settleId);
            return Result.success();
        } catch (BusinessException e) {
            log.error("确认结算单失败：{}", e.getMessage(), e);
            return Result.error(e.getCode(), e.getMessage());
        }
    }

    /**
     * 分页查询结算单列表
     */
    @GetMapping("/list")
    public Result<PageResult<SettlementVO>> listSettlements(
            @RequestParam(defaultValue = "1") Integer pageNum,
            @RequestParam(defaultValue = "20") Integer pageSize,
            @RequestParam(required = false) String merchantId,
            @RequestParam(required = false) Integer settleStatus,
            @RequestParam(required = false) String startDate,
            @RequestParam(required = false) String endDate) {
        SettlementQueryDTO queryDTO = new SettlementQueryDTO();
        queryDTO.setPageNum(pageNum);
        queryDTO.setPageSize(pageSize);
        queryDTO.setMerchantId(merchantId);
        queryDTO.setSettleStatus(settleStatus);
        queryDTO.setStartDate(startDate);
        queryDTO.setEndDate(endDate);
        PageResult<SettlementVO> pageResult = settlementService.listSettlements(queryDTO);
        return Result.success(pageResult);
    }

    /**
     * 删除结算单（仅限草稿状态）
     */
    @PostMapping("/delete/{settleId}")
    public Result<Void> deleteSettlement(@PathVariable Long settleId) {
        try {
            settlementService.deleteSettlement(settleId);
            return Result.success();
        } catch (BusinessException e) {
            log.error("删除结算单失败：{}", e.getMessage(), e);
            return Result.error(e.getCode(), e.getMessage());
        }
    }
}

// SettlementServiceImpl.java
@Service
@Slf4j
public class SettlementServiceImpl implements SettlementService {
    @Autowired
    private SettlementMapper settlementMapper;
    @Autowired
    private SettlementDetailMapper settlementDetailMapper;
    @Autowired
    private TransactionMapper transactionMapper;
    @Autowired
    private MerchantMapper merchantMapper;
    @Autowired
    private RabbitTemplate rabbitTemplate;

    @Override
    @Transactional(rollbackFor = Exception.class)
    public SettlementVO generateDailySettlement(String settleDate) {
        // 校验日期格式
        if (!DateUtil.isValidDate(settleDate)) {
            throw new BusinessException(ErrorCode.INVALID_DATE_FORMAT);
        }
        // 检查是否已生成过结算单
        Settlement existing = settlementMapper.selectBySettleDate(settleDate);
        if (existing != null) {
            throw new BusinessException(ErrorCode.SETTLEMENT_ALREADY_EXISTS);
        }
        // 获取所有商户
        List<MerchantInfo> merchantList = merchantMapper.selectAllActive();
        // 创建结算主单
        Settlement settlement = new Settlement();
        settlement.setSettleDate(settleDate);
        settlement.setSettleStatus(SettleStatus.DRAFT.getCode());
        settlement.setTotalAmount(BigDecimal.ZERO);
        settlement.setTotalFee(BigDecimal.ZERO);
        settlement.setSettleAmount(BigDecimal.ZERO);
        settlement.setCreateTime(new Date());
        settlementMapper.insert(settlement);
        List<SettlementDetail> detailList = new ArrayList<>();
        BigDecimal totalAmount = BigDecimal.ZERO;
        BigDecimal totalFee = BigDecimal.ZERO;
        // 为每个商户生成结算明细
        for (MerchantInfo merchant : merchantList) {
            // 查询商户当日交易汇总
            TransactionSummary summary = transactionMapper.selectDailySummary(
                    merchant.getMerchantId(), settleDate);
            if (summary == null || summary.getTotalAmount().compareTo(BigDecimal.ZERO) == 0) {
                continue;
            }
            // 计算手续费
            BigDecimal fee = calculateFee(summary.getTotalAmount(), merchant.getSettlementRate());
            BigDecimal settleAmount = summary.getTotalAmount().subtract(fee);
            // 创建结算明细
            SettlementDetail detail = new SettlementDetail();
            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(new Date());
            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(new Date());
        settlementMapper.updateById(settlement);
        // 发送结算单生成通知
        sendSettlementGeneratedNotification(settlement);
        return convertToVO(settlement);
    }

    @Override
    public SettlementDetailVO getSettlementDetail(Long settleId) {
        // 查询结算主单
        Settlement settlement = settlementMapper.selectById(settleId);
        if (settlement == null) {
            throw new BusinessException(ErrorCode.SETTLEMENT_NOT_EXIST);
        }
        // 查询结算明细
        List<SettlementDetail> detailList = settlementDetailMapper.selectBySettleId(settleId);
        // 转换为 VO
        SettlementDetailVO detailVO = new SettlementDetailVO();
        detailVO.setSettlement(convertToVO(settlement));
        detailVO.setDetails(detailList.stream()
                .map(this::convertDetailToVO)
                .collect(Collectors.toList()));
        return detailVO;
    }

    @Override
    @Transactional(rollbackFor = Exception.class)
    public void confirmSettlement(Long settleId) {
        Settlement settlement = settlementMapper.selectById(settleId);
        if (settlement == null) {
            throw new BusinessException(ErrorCode.SETTLEMENT_NOT_EXIST);
        }
        // 校验状态
        if (settlement.getSettleStatus() != SettleStatus.DRAFT.getCode()) {
            throw new BusinessException(ErrorCode.SETTLEMENT_STATUS_ERROR);
        }
        // 更新结算单状态
        settlement.setSettleStatus(SettleStatus.CONFIRMED.getCode());
        settlement.setConfirmTime(new Date());
        settlement.setUpdateTime(new Date());
        settlementMapper.updateById(settlement);
        // 更新明细状态
        settlementDetailMapper.updateStatusBySettleId(settleId, SettleStatus.CONFIRMED.getCode());
        // 触发资金划拨（异步）
        triggerFundTransfer(settlement);
        // 发送结算确认通知
        sendSettlementConfirmedNotification(settlement);
    }

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

    @Override
    @Transactional(rollbackFor = Exception.class)
    public void deleteSettlement(Long settleId) {
        Settlement settlement = settlementMapper.selectById(settleId);
        if (settlement == null) {
            throw new BusinessException(ErrorCode.SETTLEMENT_NOT_EXIST);
        }
        // 仅允许删除草稿状态的结算单
        if (settlement.getSettleStatus() != SettleStatus.DRAFT.getCode()) {
            throw new BusinessException(ErrorCode.SETTLEMENT_CANNOT_DELETE);
        }
        // 删除结算明细
        settlementDetailMapper.deleteBySettleId(settleId);
        // 删除结算主单
        settlementMapper.deleteById(settleId);
        // 发送删除通知
        sendSettlementDeletedNotification(settlement);
    }

    private BigDecimal calculateFee(BigDecimal amount, BigDecimal rate) {
        if (rate == null || rate.compareTo(BigDecimal.ZERO) == 0) {
            return BigDecimal.ZERO;
        }
        // 四舍五入，保留 2 位小数
        return amount.multiply(rate).setScale(2, 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 StringRedisSerializer());
        template.setValueSerializer(new GenericJackson2JsonRedisSerializer());
        template.setHashKeySerializer(new StringRedisSerializer());
        template.setHashValueSerializer(new GenericJackson2JsonRedisSerializer());
        return 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: 1800000

分库分表策略实现：

// 基于用户 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);
        for (String each : availableTargetNames) {
            if (shardNames.contains(each)) {
                result.add(each);
            }
        }
        return result;
    }

    private Set<String> getShardNamesBetween(Date start, Date end) {
        Set<String> shardNames = new HashSet<>();
        Calendar calendar = Calendar.getInstance();
        calendar.setTime(start);
        while (!calendar.getTime().after(end)) {
            int year = calendar.get(Calendar.YEAR);
            int month = calendar.get(Calendar.MONTH) + 1;
            String shardName = String.format("trans_log_%d_%02d", year, month);
            shardNames.add(shardName);
            calendar.add(Calendar.MONTH, 1);
        }
        return 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;
$$ LANGUAGE plpgsql;

-- 创建定时检测任务
CREATE EVENT TRIGGER monitor_db_health ON SCHEDULE EVERY 30 SECOND DO BEGIN
    -- 检查主节点健康状态
    IF NOT check_primary_health() THEN
        PERFORM trigger_failover();
    END IF;
END;

应用层故障转移：

// 数据库连接健康检查
@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(true);
        // 通知所有服务实例
        notifyAllInstances("database_down", System.currentTimeMillis());
        // 记录故障事件
        FaultEvent event = new FaultEvent();
        event.setEventType(FaultType.DATABASE_UNAVAILABLE);
        event.setSeverity(Severity.CRITICAL);
        event.setOccurTime(new Date());
        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 != null) {
                    if (value instanceof NullValue) {
                        return null;
                    }
                    return value;
                }
                // 查询数据库
                value = dbSupplier.get();
                // 写入缓存
                if (value == null) {
                    // 空值缓存，防止缓存穿透
                    redisTemplate.opsForValue().set(key, NullValue.INSTANCE, Duration.ofMinutes(5));
                } else {
                    redisTemplate.opsForValue().set(key, value, ttl);
                }
                return value;
            } else {
                // 未获取到锁，等待后重试或返回默认值
                Thread.sleep(50);
                return queryWithPenetrationProtection(key, clazz, dbSupplier, ttl);
            }
        } catch (InterruptedException e) {
            Thread.currentThread().interrupt();
            throw new BusinessException(ErrorCode.SYSTEM_ERROR);
        } finally {
            if (lock.isHeldByCurrentThread()) {
                lock.unlock();
            }
        }
    }

    /**
     * 批量查询优化
     */
    @Override
    public <T> Map<String, T> batchQuery(List<String> keys, Class<T> clazz, Function<List<String>, Map<String, T>> dbSupplier) {
        if (keys.isEmpty()) {
            return Collections.emptyMap();
        }
        // 批量查询缓存
        List<Object> cachedValues = redisTemplate.opsForValue().multiGet(keys);
        Map<String, T> result = new HashMap<>();
        List<String> missingKeys = new ArrayList<>();
        // 处理缓存结果
        for (int i = 0; i < keys.size(); i++) {
            String key = keys.get(i);
            Object value = cachedValues.get(i);
            if (value == null) {
                missingKeys.add(key);
            } else if (value instanceof NullValue) {
                // 空值标记，不加入结果
                continue;
            } else {
                result.put(key, (T) value);
            }
        }
        // 如果有缺失的 key，查询数据库
        if (!missingKeys.isEmpty()) {
            Map<String, T> dbResult = dbSupplier.apply(missingKeys);
            result.putAll(dbResult);
            // 批量写入缓存
            Map<String, Object> cacheMap = new HashMap<>();
            for (String key : missingKeys) {
                T value = dbResult.get(key);
                if (value == null) {
                    cacheMap.put(key, NullValue.INSTANCE);
                } else {
                    cacheMap.put(key, value);
                }
            }
            if (!cacheMap.isEmpty()) {
                redisTemplate.opsForValue().multiSet(cacheMap);
                // 设置过期时间
                for (String key : cacheMap.keySet()) {
                    if (cacheMap.get(key) instanceof NullValue) {
                        redisTemplate.expire(key, Duration.ofMinutes(5));
                    } else {
                        redisTemplate.expire(key, Duration.ofMinutes(30));
                    }
                }
            }
        }
        return result;
    }
}

六、结语

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

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