Mysql据库报错[HY000][10000] binlog write threshold(1610612736) exceeded, the transaction has 1610614460 (bytes) binlogs, it is aborted.解决方案;
1.问题原因
max_binlog_cache_size参数指定了单个事务最大允许使用的Binlog,当超出这个值时,会出现当前报错。
binlog_cache_size:为每个session 分配的内存,在事务过程中用来存储二进制日志的缓存,提高记录bin-log的效率。没有什么大事务,dml也不是很频繁的情况下可以设置小一点,如果事务大而且多,dml操作也频繁,则可以适当的调大一点。
max_binlog_cache_size设置的参考标准
Binlog_cache_disk_use表示因为我们binlog_cache_size设计的内存不足导致缓存二进制日志用到了临时文件的次数;Binlog_cache_use 表示用binlog_cache_size缓存的次数,当对应的Binlog_cache_disk_use 值比较大的时候 我们可以考虑适当的调高 binlog_cache_size 对应的值;
2.解决方案
2.1 可以通过调整max_binlog_cache_size的值大小方式,将这个值设置的更大一些;登录对应的数据库通过SET GLOBAL max_binlog_cache_size=[$Size];
2.2 但是实际情况中上述情况并无法一劳永逸,如果后续数据量变得更大,超过了设置的更大的范围同样会出现相同的错误,这是我们不希望出现的,所以有如下情况的可以通过代码去解决这个问题:分批插入或者分批修改,这个解决方法适用于能够有明确区分批次的数据,通过某些标志可以进行区分,比如某一段时间、某一个类型进行分批,把大事物拆分成多个小事务;例如:
UPDATE newbusiness_t t
SET
t.MANAGECOM = CASE
WHEN (t.MANAGECOM IS NULL OR t.MANAGECOM = '') THEN '861100'
WHEN LENGTH(TRIM(t.MANAGECOM)) = 4 THEN CONCAT_WS('', TRIM(t.MANAGECOM), '00')
ELSE t.MANAGECOM
END, // 注意这里使用逗号分隔不同的SET表达式
t.TRANSSALECHNL = CASE
WHEN (t.TRANSSALECHNL IS NULL OR t.TRANSSALECHNL='') THEN '0002'
WHEN LENGTH(TRIM(t.TRANSSALECHNL)) = 1 THEN CONCAT_WS('', '000', TRIM(t.TRANSSALECHNL))
WHEN LENGTH(TRIM(t.TRANSSALECHNL)) = 2 THEN CONCAT_WS('', '00', TRIM(t.TRANSSALECHNL))
WHEN LENGTH(TRIM(t.TRANSSALECHNL)) = 3 THEN CONCAT_WS('', '0', TRIM(t.TRANSSALECHNL))
ELSE t.TRANSSALECHNL
END
WHERE
(((t.signdate BETWEEN #{startDate} AND #{endDate} AND t.cessstart <= #{endDate})
OR (t.cessstart BETWEEN #{startDate} AND #{endDate} AND t.signdate <= #{endDate}))
OR (t.riskcode IN (SELECT code FROM ldcode1 c WHERE c.codetype = 'newProduct' AND c.code1 = #{period})))
这类的sql进行拆分,拆成两个update语句,且拆分之后的update语句可以继续分批,比如我这里由于会携带开始日期和结束日期,所以可以选择按季度或者是按月份,如果实在是数据量特别大的话,可以按照5天-10天之类的去进行更新:
UPDATE newbusiness_t t
SET
t.MANAGECOM = CASE
WHEN (t.MANAGECOM IS NULL or t.MANAGECOM = '') THEN '861100'
WHEN LENGTH(TRIM(t.MANAGECOM)) = 4 THEN CONCAT_WS('', TRIM(t.MANAGECOM), '00')
ELSE t.MANAGECOM
END
WHERE
(((t.signdate BETWEEN '2024-07-01' and '2024-09-30' and t.cessstart <='2024-09-30')
or (t.cessstart between '2024-07-01' and '2024-09-30' and t.signdate<= '2024-09-30'))
or (t.riskcode in (select code from ldcode1 c where c.codetype = 'newProduct' and c.code1 = '2024Q3')));
UPDATE newbusiness_t t
SET
t.TRANSSALECHNL = CASE
WHEN (t.TRANSSALECHNL IS NULL or t.TRANSSALECHNL='') THEN '0002'
WHEN LENGTH(TRIM(t.TRANSSALECHNL)) = 1 THEN CONCAT_WS('', '000', TRIM(t.TRANSSALECHNL))
WHEN LENGTH(TRIM(t.TRANSSALECHNL)) = 2 THEN CONCAT_WS('', '00', TRIM(t.TRANSSALECHNL))
WHEN LENGTH(TRIM(t.TRANSSALECHNL)) = 3 THEN CONCAT_WS('', '0', TRIM(t.TRANSSALECHNL))
ELSE t.TRANSSALECHNL
END
WHERE
(((t.signdate BETWEEN #{startDate} and #{endDate} and t.cessstart <= #{endDate})\n
or (t.cessstart between #{startDate} and #{endDate} and t.signdate<= #{endDate}))\n
or (t.riskcode in (select code from ldcode1 c where c.codetype = 'newProduct' and c.code1 = #{period})))
这里提供一个方法通过年度和季度获取每个月的首日和末尾:
/**
* 获取指定年度和季度每个月的首日和末尾日期
*
* @param year 年度
* @param quarter 季度(1-4)
* @return 当前季度每个月的首日和末尾日期的列表
*/
public static List<MonthRangeDTO> getQuarterlyMonthRanges(int year, int quarter) {
List<MonthRangeDTO> ranges = new ArrayList<>();
if (quarter < 1 || quarter > 4) {
throw new IllegalArgumentException("Quarter must be between 1 and 4");
}
// 计算季度的开始和结束月份
int startMonth = (quarter - 1) * 3 + 1;
int endMonth = quarter * 3;
for (int month = startMonth; month <= endMonth; month++) {
LocalDate firstDay = LocalDate.of(year, month, 1);
LocalDate lastDay = firstDay.withDayOfMonth(firstDay.lengthOfMonth());
MonthRangeDTO monthRangeDTO=new MonthRangeDTO();
monthRangeDTO.setStartDate(firstDay);
monthRangeDTO.setEndDate(lastDay);
ranges.add(monthRangeDTO);
}
return ranges;
}
@Data
public class MonthRangeDTO {
//开始日期
private LocalDate startDate;
//结束日期
private LocalDate endDate;
}
在提供另外一个方法,获获取季度内的每一天:传入开始时间和结束时间,返回每一天的一个结果集合:
public static List<String> getDatesBetween(String startDate, String endDate) {
List<String> dates = new ArrayList<>();
LocalDate start = LocalDate.parse(startDate);
LocalDate end = LocalDate.parse(endDate);
// 确保起始日期小于结束日期
if (start.isAfter(end)) {
LocalDate temp = start;
start = end;
end = temp;
}
// 添加起始日期
dates.add(start.format(DateTimeFormatter.ISO_DATE));
// 循环添加中间的日期
while (start.isBefore(end)) {
start = start.plusDays(1);
dates.add(start.format(DateTimeFormatter.ISO_DATE));
}
return dates;
}
3. 具体分批代码示例
public void updateAccountGetDate(ActualUpdateFieldDto updateFieldDto){
Integer year = Integer.valueOf(updateFieldDto.getPeriod().substring(0,4));
Integer quarter = Integer.valueOf(updateFieldDto.getPeriod().substring(5,6));
List<MonthRangeDTO> monthRangeDTOS = getQuarterlyMonthRanges(year,quarter);
for (MonthRangeDTO monthRangeDTO : monthRangeDTOS) {
String startDate= makeDateToString(monthRangeDTO.getStartDate());
String endDate= makeDateToString(monthRangeDTO.getEndDate());
newBusinessRMapper.updateManageCom(updateFieldDto.getPeriod(),startDate,endDate);
newBusinessRMapper.updateTransSaleChanl(updateFieldDto.getPeriod(),startDate,endDate);
newBusinessRMapper.updateAccountGetDate(updateFieldDto.getPeriod(),startDate,endDate);
}
}