go后端实现数据库分表维护

Be the change

已于 2024-04-24 01:38:28 修改

阅读量152

点赞数 1

文章标签：数据库

于 2024-01-25 15:24:55 首次发布

本文链接：https://blog.csdn.net/poiuytrewq213/article/details/135843483

版权

需求：日志表数据过大，查询耗时，需限制表数据，当超过设定数据量就分表，通过下标表明是第几张表，主要逻辑如下：

1、数据库新增一张存储所有日志表，当新增表的时候记录该表表名，并记录存满表数据的时间log_table_control_detail

2、初次/每次入库都调用 DataBatchProcessor 方法，实时查询当前redis里存的哪张表以及表实际数据量；同时更新redis，当建表数量超出限制，删除历史数据表 checkTableLimit 方法

3、分库分表后，查询需要查控制表里所有表的数据 GetData 方法，tableCount 方法计算所要查询的日志表

4、同时项目启动时，需初始化redis

import (
	"context"
	"go.uber.org/zap"
	"gorm.io/gorm"
	"netvine.com/module/global"
	"sort"
	"strconv"
	"strings"
	"time"
)

func InitRedisTableUsingInfo() {
	var tableControlDetail []TableControlDetail
	global.NETVINE_DB.Raw("SELECT * FROM `log_table_control_detail` cd " +
		"INNER JOIN ( SELECT NAME, max( table_suffix_index ) suffixIndex FROM `log_table_control_detail` GROUP BY `name` ) zz ON zz.NAME = cd.NAME AND zz.suffixIndex = cd.table_suffix_index").Scan(&tableControlDetail)
	val := global.NETVINE_REDIS.Keys(context.Background(), "tableUsingInfo:*").Val()
	for _, v := range val {
		global.NETVINE_REDIS.Del(context.Background(), v)
	}
	for _, detail := range tableControlDetail {
		var total int64
		global.NETVINE_DB.Table(detail.TheTableName).Count(&total)
		global.NETVINE_REDIS.Set(context.Background(), TABLE_USING_INFO_MAP_KEY+detail.Name, detail.TheTableName+"#"+strconv.FormatInt(total, 10), 0)
	}
}

var TABLE_USING_INFO_MAP_KEY = "tableUsingInfo:"

type TableControlDetail struct {
	Id               int               //主键ID
	Name             string            //根表名
	TheTableName     string            //当前使用的表名
	TableSuffixIndex int               //表后缀
	CreatedAt        global.FormatTime // 创建时间
	EndTime          global.FormatTime // 表存满时间时间
}

func (w *TableControlDetail) TableName() string {
	return "log_table_control_detail"
}

// DataBatchProcessor 该方法只能串行执行
func DataBatchProcessor(batch []interface{}, logTableName string, dataToRaw func([]interface{}) interface{}) (err error) {
	//将batch需控制在tableMaxSize下
	if len(batch) == 0 {
		return nil
	}
	global.NETVINE_LOG.Info("分表插入开始执行,切片大小：", zap.Any(logTableName, len(batch)))
	var dataSize int        //当前将要操作表的数据总量
	var theTableName string //当前将要操作表的数据表名称

	//redis 查询表当前操作数据
	logUsingInfoStr := global.NETVINE_REDIS.Get(context.Background(), TABLE_USING_INFO_MAP_KEY+logTableName).Val()
	global.NETVINE_LOG.Info("当前redis键值：", zap.Any(logTableName, logUsingInfoStr))
	if len(logUsingInfoStr) == 0 {
		theTableName = logTableName + "_1"
		dataSize = 0
	} else {
		logUsingInfoArray := strings.Split(logUsingInfoStr, "#")
		theTableName = logUsingInfoArray[0]
		size, _ := strconv.Atoi(logUsingInfoArray[1])
		dataSize = size
	}

	// 获取该预计插入的日志表总数据量 table_size_db + batch
	var dataTotalSize = dataSize + len(batch)

	// 查询操作表表是否已有该表记录
	var tableControlDetail TableControlDetail
	global.NETVINE_DB.Model(&TableControlDetail{}).Where("the_table_name", theTableName).Last(&tableControlDetail)
	global.NETVINE_LOG.Info("当前记录表信息：", zap.Any(logTableName, tableControlDetail))
	// 第一次记录日志，新增路由表信息，记录开始时间
	if dataSize == 0 {
		if tableControlDetail == (TableControlDetail{}) {
			// 新增
			tableControlDetail.Name = logTableName
			tableControlDetail.TheTableName = theTableName
			split := strings.Split(theTableName, "_")
			tableSuffixStr := split[len(split)-1]
			tableControlDetail.TableSuffixIndex, _ = strconv.Atoi(tableSuffixStr)
			global.NETVINE_DB.Model(&TableControlDetail{}).Create(&tableControlDetail)
		}
	}

	if dataTotalSize < global.NETVINE_CONFIG.TableClear.TableLimit {
		global.NETVINE_LOG.Info("满足不用建表条件，总量：", zap.Any(logTableName, dataTotalSize))
		//数据落库
		err = global.NETVINE_DB.Table(theTableName).Create(dataToRaw(batch)).Error
		if err == nil {
			//redis更新表状态
			global.NETVINE_REDIS.Set(context.Background(), TABLE_USING_INFO_MAP_KEY+logTableName, theTableName+"#"+strconv.Itoa(dataTotalSize), 0)
			global.NETVINE_LOG.Info("更新redis键值：", zap.Any(logTableName, theTableName+"#"+strconv.Itoa(dataTotalSize)))
		}
		return
	}

	// 日志表已写满
	if dataTotalSize >= global.NETVINE_CONFIG.TableClear.TableLimit {
		global.NETVINE_LOG.Error("当前数据操作需建表：", zap.Any(logTableName, dataTotalSize))
		oldDataSize := len(batch) - (dataTotalSize - global.NETVINE_CONFIG.TableClear.TableLimit)

		//旧表数据落库
		oldData := batch[:oldDataSize]
		err = global.NETVINE_DB.Table(theTableName).Create(dataToRaw(oldData)).Error
		if err != nil {
			return err
		}
		// 更新路由表状态和结束时间
		global.NETVINE_DB.Model(&TableControlDetail{}).Where("the_table_name", theTableName).Updates(map[string]interface{}{"end_time": time.Now().Format("2006-01-02 15:04:05")})
		global.NETVINE_LOG.Error("旧表数据操作完成：", zap.Any(logTableName, dataTotalSize))
		// 查询当前操作到哪些日志表了，日志表+1

		newTheTableName := logTableName + "_" + strconv.Itoa(tableControlDetail.TableSuffixIndex+1)
		global.NETVINE_LOG.Error("开始创建新表：", zap.Any(logTableName, newTheTableName))
		// 数据库新增表
		var result string
		global.NETVINE_DB.Raw("CREATE TABLE " + newTheTableName + " LIKE " + tableControlDetail.TheTableName).Scan(&result)
		global.NETVINE_LOG.Error("建表结束：", zap.Any(logTableName, result))
		var id int
		global.NETVINE_DB.Table(tableControlDetail.TheTableName).Select("id").Order("id desc").Take(&id)
		if id/(global.NETVINE_CONFIG.TableClear.TableLimit*global.NETVINE_CONFIG.TableClear.TableSize) == 0 {
			id = id + 1
		} else {
			id = 1
		}
		global.NETVINE_DB.Raw("ALTER TABLE " + newTheTableName + " AUTO_INCREMENT = " + strconv.Itoa(id) + ";").Scan(&result)
		global.NETVINE_LOG.Error("主键设置结束：", zap.Any(logTableName, result))
		//新表数据落库
		newData := batch[oldDataSize:]
		if len(newData) > 0 {
			err = global.NETVINE_DB.Table(newTheTableName).Create(dataToRaw(newData)).Error
			if err != nil {
				return err
			}
		}
		// 新增
		var tableControlDetailNew TableControlDetail
		tableControlDetailNew.Name = logTableName
		tableControlDetailNew.TheTableName = newTheTableName
		tableControlDetailNew.TableSuffixIndex = tableControlDetail.TableSuffixIndex + 1
		global.NETVINE_DB.Model(&TableControlDetail{}).Create(&tableControlDetailNew)

		//更新redis当前操作库
		global.NETVINE_REDIS.Set(context.Background(), TABLE_USING_INFO_MAP_KEY+logTableName, newTheTableName+"#"+strconv.Itoa(len(newData)), 0)
		global.NETVINE_LOG.Error("新表数据插入完成：", zap.Any(logTableName, newTheTableName+"#"+strconv.Itoa(len(newData))))
		//check建表数量是否超出限制，若超出将历史表删除
		go checkTableLimit(logTableName)
	}

	return
}

func checkTableLimit(logTableName string) {
	global.NETVINE_LOG.Error("开始检测是否清理历史数据表：" + logTableName)
	var tableControlDetailArray []TableControlDetail
	global.NETVINE_DB.Model(&TableControlDetail{}).Where("name", logTableName).Find(&tableControlDetailArray)
	if len(tableControlDetailArray) > global.NETVINE_CONFIG.TableClear.TableSize {
		for i := 0; i < len(tableControlDetailArray)-global.NETVINE_CONFIG.TableClear.TableSize; i++ {
			err := global.NETVINE_DB.Transaction(func(tx *gorm.DB) error {
				err := tx.Model(&TableControlDetail{}).Delete(&tableControlDetailArray[i]).Error
				if err != nil {
					return err
				}
				global.NETVINE_LOG.Error("清理control表结束：" + tableControlDetailArray[i].TheTableName)
				err = tx.Exec("DROP TABLE " + tableControlDetailArray[i].TheTableName).Error
				if err != nil {
					return err
				}
				global.NETVINE_LOG.Error("drop历史数据表结束：" + tableControlDetailArray[i].TheTableName)
				return nil
			})
			if err == nil {
				global.NETVINE_LOG.Error("清理历史数据表结束：" + tableControlDetailArray[i].TheTableName)
			}
		}

	}
}

//获取数据

type LogDetail struct {
	LogName  string
	LogCount int64
}

const TableNameConstant = "tableName"
const StartConstant = "start"
const EndConstant = "end"

type TableSearchInfo struct {
	LogTableName string
	StartTime    string
	EndTime      string
	Page         int
	PageSize     int
	DbWhere      *gorm.DB
}

func GetData(tableSearchInfo TableSearchInfo) (retArr map[int]map[string]string, totalCount int64) {
	var tableControlDetailArray []TableControlDetail
	db := global.NETVINE_DB.Model(&TableControlDetail{})
	db.Where("name = ?", tableSearchInfo.LogTableName)
	if tableSearchInfo.StartTime != "" {
		db.Where("(created_at >= ? and created_at <= ?) OR (end_time >= ? and end_time <= ?) OR (created_at <= ? and end_time >= ?) OR (end_time is null and created_at <= ?)",
			tableSearchInfo.StartTime, tableSearchInfo.EndTime,
			tableSearchInfo.StartTime, tableSearchInfo.EndTime,
			tableSearchInfo.StartTime, tableSearchInfo.EndTime,
			tableSearchInfo.StartTime)
	}
	db.Find(&tableControlDetailArray)
	mapList := make(map[int]LogDetail)
	for _, tableControl := range tableControlDetailArray {
		var logDetail LogDetail
		var count int64
		global.NETVINE_DB.Table(tableControl.TheTableName).
			Where(tableSearchInfo.DbWhere).Count(&count)
		totalCount += count
		logDetail.LogName = tableControl.TheTableName
		logDetail.LogCount = count
		mapList[tableControl.TableSuffixIndex] = logDetail
	}

	// 查询满足条件的日志表
	pageSize := tableSearchInfo.PageSize
	pageIndex := tableSearchInfo.Page
	retArr = tableCount(mapList, int64(pageSize), int64(pageIndex))
	return
}

// tableCount 计算所要查询的日志表
func tableCount(mapList map[int]LogDetail, pageSize int64, page int64) map[int]map[string]string {
	// map排序
	var keys []int
	for key, _ := range mapList {
		keys = append(keys, key)
	}
	sort.Slice(keys, func(i, j int) bool {
		return keys[i] > keys[j]
	})

	retArr := make(map[int]map[string]string)
	prevNum := (page - 1) * pageSize //前页面总数据
	lastNum := prevNum + pageSize    //需要到的最后那条数据
	var upNum int64 = 0              //以上表占据的数量
	var nowNum int64 = 0
	var nowHave int64 = 0 //已够条数
	i := 0

	for _, key := range keys {
		logDetail := mapList[key]
		logName := logDetail.LogName
		logCount := logDetail.LogCount

		nowNum += logCount //当前总条数
		if nowNum > prevNum {
			//开始数 = 前页面总数据 + 已够数 -上表总数
			start := prevNum + nowHave - upNum
			//已够数量 = 当前总数 - 开始数
			var end int64 = 0
			if nowNum < lastNum {
				nowHave += nowNum - upNum - start
				end = nowNum - upNum - start
			} else {
				end = pageSize - nowHave
			}
			if nowHave > pageSize {
				nowHave = pageSize
			} else {
				nowHave = nowHave
			}
			mapLog := make(map[string]string)
			mapLog[TableNameConstant] = logName
			mapLog[StartConstant] = strconv.FormatInt(start, 10)
			mapLog[EndConstant] = strconv.FormatInt(end, 10)
			retArr[i] = mapLog
			i++
			if nowNum >= lastNum {
				//如果当前计算位置总数 大于或等于 需要到达的位置 则退出循环
				break
			}
		}
		upNum += logCount
	}

	// map按key排序

	return retArr
}

func GetDefaultTableName(tableNamePrefix string) string {
	var tableName string
	global.NETVINE_DB.Model(&TableControlDetail{}).Select("the_table_name").
		Where("name = ?", tableNamePrefix).Last(&tableName)
	if tableName == "" {
		return tableNamePrefix + "_1"
	} else {
		return tableName
	}
}

5、维护：当断电但同时还在插入时，会出现表损害

import (
	"context"
	"encoding/json"
	"errors"
	"fmt"
	"go.uber.org/zap"
	"netvine.com/firewall/server/global"
	"netvine.com/firewall/server/utils/businessTableControl"
	"os/exec"
	"strings"
)

var (
	CrashedError    = errors.New("is marked as crashed and should be repaired")
	CrashedErrorStr = "is marked as crashed and should be repaired"
)

type CheckTable struct {
	Table   string `gorm:"column:Table"`
	Op      string `gorm:"column:Op"`
	MsgType string `gorm:"column:Msg_type"`
	MsgText string `gorm:"column:Msg_text"`
}

func TableCrashedRepair() {
	var err error
	var tableNames []string
	err = global.NETVINE_DB.Table("information_schema.TABLES").Where("table_schema = 'firewall' AND ENGINE = 'MyISAM'").Pluck("table_name", &tableNames).Error
	if err != nil {
		global.NETVINE_LOG.Error("select MyISAM tables error ", zap.Error(err))
		return
	}
	global.NETVINE_LOG.Info("repair start", zap.Any("tableNames", tableNames))
	for _, tableName := range tableNames {
		var arr []CheckTable
		sql := fmt.Sprintf("CHECK TABLE `%s`;", tableName)
		err = global.NETVINE_DB.Raw(sql).Scan(&arr).Error
		var destroyed bool
		for _, v := range arr {
			if v.MsgText != "OK" {
				destroyed = true
				marshal, _ := json.Marshal(arr)
				global.NETVINE_LOG.Error("检测到表损坏：" + v.Table + "----" + string(marshal))
			}
		}
		if err != nil || destroyed {
			RecoverShardingTableByTableName(tableName, tableNames)
		}
	}
}

// RecoverShardingTableByTableName 某一个模块的表损坏了 直接初始化当前模块的表
func RecoverShardingTableByTableName(tableName string, tableNames []string) {
	var err error
	global.NETVINE_LOG.Info("1.crashed table: ", zap.String("tableName", tableName), zap.Error(err))
	tableTempName := tableName[:strings.LastIndex(tableName, "_")]
	var dropTableNames []string
	for _, name := range tableNames {
		if strings.Contains(name, tableTempName) {
			dropTableNames = append(dropTableNames, name)
		}
	}
	for _, name := range dropTableNames {
		err = global.NETVINE_DB.Exec("DROP TABLE IF EXISTS " + name).Error
		if err != nil {
			global.NETVINE_LOG.Error("2.drop crashed table error: ", zap.String("exec drop sql error", name), zap.Error(err))
		} else {
			global.NETVINE_LOG.Error("2.drop crashed table success: ", zap.String("exec drop sql success", name))
		}
	}
	cmdStr := "awk '/CREATE TABLE `" + tableTempName + "_1`" + "/,/;/' ./sql/truncate.sql"
	global.NETVINE_LOG.Info("3.awk command: ", zap.String("tableName", tableName), zap.String("cmdStr", cmdStr))
	cmd := exec.Command("/bin/bash", "-c", cmdStr)
	output, err1 := cmd.CombinedOutput()
	if err1 != nil {
		global.NETVINE_LOG.Error("3.awk create sql error: ", zap.String("tableName", tableName), zap.Error(err1))
	} else {
		global.NETVINE_LOG.Error("3.awk create sql success: ", zap.String("tableName", tableName), zap.String("output", string(output)))
	}
	err = global.NETVINE_DB.Exec(string(output)).Error
	if err != nil {
		global.NETVINE_LOG.Error("4.init table error: ", zap.String("tableName", tableName), zap.Error(err))
	} else {
		global.NETVINE_LOG.Error("4.init table success: ", zap.String("tableName", tableName))
	}
	//data sync: redis-tableUsingInfo-->#0  mysql-log_table_control_detail
	tableInfoKey := businessTableControl.TABLE_USING_INFO_MAP_KEY + tableTempName
	err = global.NETVINE_REDIS.Del(context.Background(), tableInfoKey).Err()
	if err != nil {
		global.NETVINE_LOG.Error("5.redis del hset error: ", zap.String("tableName", tableName), zap.Error(err))
	} else {
		global.NETVINE_LOG.Error("5.redis del hset success: ", zap.String("tableName", tableName))
	}
	affected := global.NETVINE_DB.Delete(&businessTableControl.TableControlDetail{}, "name = ?", tableTempName).RowsAffected
	global.NETVINE_LOG.Error("6.delete table_control_detail success: ", zap.String("tableName", tableName), zap.Int64("affected", affected))
}