快捷導(dǎo)航

Mysql大表數(shù)據(jù)歸檔實現(xiàn)方案

更新時間：2024年11月13日 10:52:45 作者：RobinCode

本文介紹了MySQL大表數(shù)據(jù)歸檔,通過創(chuàng)建歷史訂單表并基于主鍵id進(jìn)行分批處理,避免影響線上業(yè)務(wù)和產(chǎn)生慢SQL,下面就來詳細(xì)的介紹一下,感興趣的可以了解一下

前言

在生產(chǎn)實踐中，你的mysql數(shù)據(jù)庫可能面臨下面這些情況：

不可抗力的因素，數(shù)據(jù)庫所在服務(wù)器被回收，或者服務(wù)器磁盤損壞，數(shù)據(jù)庫必須得遷移？
單點數(shù)據(jù)庫讀寫壓力越來越大，需要擴展一個或多個節(jié)點分?jǐn)傋x寫壓力？
單表數(shù)據(jù)量太大了，需要進(jìn)行水平或垂直拆分怎么搞？
數(shù)據(jù)庫需要從mysql遷移到其他數(shù)據(jù)庫，比如PG，OB…

以上的這些場景，對于不少同學(xué)來講，或多或少的在所處的業(yè)務(wù)中可能會涉及到，沒有碰到還好，一旦發(fā)生了這樣的問題，該如何處理呢？在這里我通過提供一個思路來解決單表數(shù)據(jù)量太大了，進(jìn)行水平拆分，將歷史數(shù)據(jù)歸檔保證熱點數(shù)據(jù)查詢。

歸檔流程示意圖

在這里插入圖片描述

實現(xiàn)步驟

controller 層

@Slf4j
@RestController
@RequestMapping("/backDoor")
public class CleanHistoryDataController {

    @Autowired
    private ICleanHistoryDataService cleanHistoryDataService;

    /**
     * 把指定過期時間的訂單表數(shù)據(jù)遷移到歷史表中
     */
    @PostMapping("/cleanByTableNameAndEndTime")
    public Resp<String> cleanByTableNameAndEndTime(@RequestBody CleanTableReq cleanTableReq) {
        try {
            CleanTableBo cleanTableBo = ObjectUtils.mapValue(cleanTableReq, CleanTableBo.class);
            cleanHistoryDataService.cleanByTableNameAndEndTime(cleanTableBo);
        } catch (Exception e) {
            log.error(cleanTableReq.getTableName() + " 數(shù)據(jù)遷移異常", e);
        }
        return Resp.success("success");
    }
}

Service 層

public interface ICleanHistoryDataService {

    void cleanHistoryTableData();
}

import com.alibaba.fastjson.JSONObject;
import com.photon.union.risk.clean.service.ICleanHistoryDataService;
import com.photon.union.risk.repo.mapper.clean.MasterDbMapper;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.collections4.CollectionUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.util.StopWatch;

import java.util.List;
import java.util.stream.Collectors;


/**
 * @author robin
 */
@Service
@Slf4j
public class CleanHistoryDataService implements ICleanHistoryDataService {
    @Autowired
    private MasterDbMapper masterDbMapper;

    @Override
    public void cleanHistoryTableData() {
        StopWatch stopWatch = new StopWatch();
        stopWatch.start();
        int logInt = 0;
        Long startId = 0L;
        while (true) {
            logInt ++;
            List<JSONObject> hashMapList = masterDbMapper.selectHistoryTableDataIds(startId);
            if (CollectionUtils.isEmpty(hashMapList)){
                break;
            }
            List<Long> allIds = hashMapList.stream().map(o -> o.getLong("id")).collect(Collectors.toList());
            startId = allIds.get(allIds.size()-1);
            if (logInt % 100 == 0 ){
                log.info("id 已經(jīng)處理到-->" + allIds.get(allIds.size()-1));
            }
            try {
                // 往歸檔歷史數(shù)據(jù)表寫入數(shù)據(jù)
                masterDbMapper.insertOldHistoryTableDataBatchByIds(allIds);
                // 把歸檔的數(shù)據(jù)從目前業(yè)務(wù)表中刪除
                masterDbMapper.deleteHistoryTableDataBatchByIds(allIds);
            }catch (Exception e){
                log.error("數(shù)據(jù)遷移異常,ids:{}", allIds, e);
            }
        }
        stopWatch.stop();
        log.info("數(shù)據(jù)遷移到歷史表處理完成時間:{}s", (long)stopWatch.getTotalTimeSeconds());
    }
}

Mapper 層

import com.alibaba.fastjson.JSONObject;
import org.apache.ibatis.annotations.Param;
import org.springframework.stereotype.Repository;

import java.util.List;

/**
 * @author robin
 */
@Repository
public interface MasterDbMapper {

    List<JSONObject> selectHistoryTableDataIds( @Param("id") Long startId);

    void insertOldHistoryTableDataBatchByIds(@Param("ids") List<Long> allIds);

    void deleteHistoryTableDataBatchByIds(@Param("ids") List<Long> allIds);
}

Sql Mapper

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.test.MasterDbMapper">

    <insert id="insertOldHistoryTableDataBatchByIds">
        INSERT IGNORE INTO t_order_old
        SELECT NULL,order_no,created_at
        FROM t_order WHERE id in
        <foreach collection="ids" item="item" open="(" close=")" separator=",">
            #{item}
        </foreach>
    </insert>

    <delete id="deleteHistoryTableDataBatchByIds">
        DELETE FROM t_order WHERE id IN
        <foreach collection="ids" item="item" open="(" close=")" separator=",">
            #{item}
        </foreach>
    </delete>

    <select id="selectHistoryTableDataIds" resultType="com.alibaba.fastjson.JSONObject">
        SELECT id FROM t_order
        WHERE id > #{id} and created_at lt;= DATE_SUB(NOW(), INTERVAL 6 MONTH)
        ORDER BY id limit 1000
    </select>

</mapper>

核心說明

根據(jù) t_order訂單表結(jié)構(gòu)創(chuàng)建 t_order_old歷史訂單表用于歷史數(shù)據(jù)備份存放。
整個流程基于主鍵 id 處理，避免慢 sql 產(chǎn)生，做到不影響當(dāng)前線上業(yè)務(wù)處理。
1000 條記錄一個批次，避免長期搶占鎖資源，同時每個批次執(zhí)行不影響下個批次處理，出現(xiàn)異常后，打印 error 日志再人工跟進(jìn)處理。

總結(jié)

上述方案是處理歷史數(shù)據(jù)歸檔的一種方式，到此這篇關(guān)于Mysql大表數(shù)據(jù)歸檔實現(xiàn)方案的文章就介紹到這了,更多相關(guān)Mysql大表數(shù)據(jù)歸檔內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

您可能感興趣的文章: