优化理赔数据同步机制：从4小时延迟降至15分钟

优化理赔数据同步机制：从4小时延迟降至15分钟

1. 分析当前同步瓶颈

首先诊断当前同步延迟原因：

-- 检查主从复制状态（在主库执行）
SHOW MASTER STATUS;
SHOW SLAVE HOSTS;

-- 在从库执行检查复制延迟
SHOW SLAVE STATUS\G
-- 关注以下字段：
-- Seconds_Behind_Master
-- Slave_SQL_Running_State
-- Last_IO_Errno, Last_SQL_Errno

2. 优化复制配置

-- 在主库配置更频繁的binlog刷新（my.cnf中设置）
SET GLOBAL sync_binlog = 1;  -- 每次事务提交都刷binlog
SET GLOBAL innodb_flush_log_at_trx_commit = 1;  -- 确保ACID

-- 优化从库并行复制（MySQL 5.7+）
STOP SLAVE;
SET GLOBAL slave_parallel_workers = 8;  -- 根据CPU核心数调整
SET GLOBAL slave_parallel_type = 'LOGICAL_CLOCK';
START SLAVE;

-- 增大从库relay log大小
SET GLOBAL relay_log_space_limit = 16G;

3. 表结构优化

-- 为理赔主表添加合适索引
ALTER TABLE claim_records ADD INDEX idx_sync_status (sync_status, last_modified_time);

-- 分区表按时间范围
ALTER TABLE claim_details PARTITION BY RANGE (TO_DAYS(claim_time)) (
    PARTITION p_current VALUES LESS THAN (TO_DAYS('2023-07-01')),
    PARTITION p_2023_h2 VALUES LESS THAN (TO_DAYS('2024-01-01')),
    PARTITION p_future VALUES LESS THAN MAXVALUE
);

4. 实现增量同步机制

-- 创建变更捕获表
CREATE TABLE claim_change_log (
    log_id BIGINT AUTO_INCREMENT PRIMARY KEY,
    claim_id BIGINT NOT NULL,
    change_type ENUM('INSERT','UPDATE','DELETE'),
    change_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    processed BOOLEAN DEFAULT FALSE,
    INDEX idx_processed (processed, change_time)
);

-- 创建触发器捕获变更
DELIMITER //
CREATE TRIGGER trg_claim_insert AFTER INSERT ON claim_records
FOR EACH ROW
BEGIN
    INSERT INTO claim_change_log (claim_id, change_type)
    VALUES (NEW.claim_id, 'INSERT');
END //

CREATE TRIGGER trg_claim_update AFTER UPDATE ON claim_records
FOR EACH ROW
BEGIN
    INSERT INTO claim_change_log (claim_id, change_type)
    VALUES (NEW.claim_id, 'UPDATE');
END //
DELIMITER ;

5. 优化批量同步过程

-- 使用游标分批处理（存储过程示例）
DELIMITER //
CREATE PROCEDURE sync_claims_incremental()
BEGIN
    DECLARE done INT DEFAULT FALSE;
    DECLARE batch_size INT DEFAULT 500;
    DECLARE last_id BIGINT DEFAULT 0;
    
    DECLARE cur CURSOR FOR 
        SELECT claim_id FROM claim_change_log 
        WHERE processed = FALSE
        ORDER BY change_time
        LIMIT batch_size;
    
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
    
    OPEN cur;
    read_loop: LOOP
        FETCH cur INTO last_id;
        IF done THEN
            LEAVE read_loop;
        END IF;
        
        -- 同步数据到分析库
        INSERT INTO analytics_db.claim_records
        SELECT * FROM claim_records WHERE claim_id = last_id
        ON DUPLICATE KEY UPDATE 
            status = VALUES(status),
            amount = VALUES(amount),
            -- 其他需要同步的字段...
            last_sync_time = NOW();
        
        -- 标记为已处理
        UPDATE claim_change_log SET processed = TRUE 
        WHERE claim_id = last_id AND processed = FALSE;
    END LOOP;
    CLOSE cur;
END //
DELIMITER ;

-- 创建事件定期执行
CREATE EVENT evt_sync_claims
ON SCHEDULE EVERY 15 MINUTE
DO CALL sync_claims_incremental();

6. 使用GTID增强复制可靠性

-- 启用GTID复制（需在my.cnf中配置后重启）
SET @@GLOBAL.enforce_gtid_consistency = ON;
SET @@GLOBAL.gtid_mode = ON;

-- 配置从库使用GTID
STOP SLAVE;
CHANGE MASTER TO 
    MASTER_AUTO_POSITION = 1;
START SLAVE;

7. 监控同步延迟

-- 创建监控表
CREATE TABLE sync_monitoring (
    monitor_id INT AUTO_INCREMENT PRIMARY KEY,
    check_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    last_sync_id BIGINT,
    records_pending INT,
    max_delay_seconds INT
);

-- 定期记录同步状态
INSERT INTO sync_monitoring (last_sync_id, records_pending, max_delay_seconds)
SELECT 
    MAX(log_id) AS last_sync_id,
    COUNT(*) AS records_pending,
    TIMESTAMPDIFF(SECOND, MIN(change_time), NOW()) AS max_delay_seconds
FROM claim_change_log
WHERE processed = FALSE;

8. 优化网络传输