浅谈MySQL timestamp(3)问题
作者:johnny233 发布时间:2024-01-14 17:47:23
背景
最近在负责开发维护的一款数据平台,有一个功能是把数据从某个源头数据源(如常规的JDBC数据源,MySQL,Oracle等)推到目地数据源(还包括企微,MQ等)。一次推送数据就是一个任务,当然需要记录此次推送任务的执行情况,如任务的开始时间,结束时间,任务名称,任务执行状态,任务日志(失败原因),执行人,执行方式(手动执行还是定时触发)。另外,从来源数据源取数,怎么取数是通过SQL指定的,那我们还可以记录一下SQL查询耗时,以及SQL查询条数。总耗时就是任务执行结束时间减去任务开始时间,总耗时肯定大于SQL查询耗时。
统计SQL查询耗时时,还需要考虑到SQL取数的数据量,假如SQL查询量为1000w,程序不可能一次查询全部数据,然后加工处理并发送到下游目地数据源,故而需要设置分批。假设批次为50w,则SQL查询耗时为20次查询的耗时之和。
扯远一句,项目是接手维护的,一开始的设计开发者是用秒来记录SQL查询耗时,这样一看就不严谨,因为很多SQL查询耗时根本不需要1秒。事实上就算查询耗时超过1s,1.6s和1.7s也是有区别的,故而需要带毫秒来优化记录SQL查询耗时。最后在前端展示时,用小数点来表示毫秒。
很常规,看起来也没有任何问题的数据表设计:
create table execlog (
id bigint(11) auto_increment primary key,
total_sql_time bigint null comment 'SQL执行耗时,单位豪秒',
start_date timestamp default CURRENT_TIMESTAMP not null comment '执行开始时间',
end_date timestamp null comment '执行结束时间',
);
注:total_sql_time
注释单位毫秒,以及程序记录单位是后来优化调整的。
某次任务执行记录截图如下,发现总耗时为0秒,而查询耗时为146毫秒。这显然不符合逻辑。
优化
优化思路也不难,就是任务开始时间需要记录到毫秒级别,改进后的表结构为:
create table execlog (
id bigint(11) auto_increment primary key,
total_sql_time bigint null comment 'SQL执行耗时,单位豪秒',
start_date timestamp(3) default CURRENT_TIMESTAMP(3) not null comment '执行开始时间',
end_date timestamp(3) null comment '执行结束时间',
);
值得注意的是,MySQL直到版本5.6(不太确定)才支持,如何知道自己使用的MySQL Server版本是否支持timestamp(3)
,执行语句即可验证,没有报错并且返回毫秒数表示支持:select now(3);
优化上面截图中的日志记录问题分为两个步骤,即日志记录和日志显示。
日志记录
增加一个取当时时间精确到毫秒的静态方法:
public static final String COMMON_DATE_WITH_MILLI_SECOND = "yyyy-MM-dd HH:mm:ss:sss";
public static String getNowWithMilliSecond() {
SimpleDateFormat sdf = new SimpleDateFormat(Constant.COMMON_DATE_WITH_MILLI_SECOND);
return sdf.format(new Date());
}
表结构如上改进后,发现start_date
记录没有问题,带3位小数点。但end_date
记录不到小数点,即未记录到毫秒,于是怀疑表结构不对。
某次任务执行肯定会有start_date
,故而设置为not null
,但会因发布,或调试中断等各种原因导致记录不到end_date
时间点,因此字段不能设置为not null
。
落不到数据,怀疑需要给一个默认值,于是如下改表结构
create table execlog (
id bigint(11) auto_increment primary key,
start_date timestamp(3) default CURRENT_TIMESTAMP(3) not null comment '执行开始时间',
end_date timestamp(3) default CURRENT_TIMESTAMP(3) null comment '执行结束时间'
);
但是还是记录不到结束时间的毫秒数。
此时只能好好看代码,end_date
是在任务结束(不管是正常结束还是异常结束,异常结束在finally语句块)时updateExecLog
更新:
<update id="updateExecLog" parameterType="com.xy.cloudiview.common.po.ExecLog">
UPDATE execlog t
<set>
<if test="errorLog != null">
t.error_log = #{errorLog},
</if>
t.end_date = now(),
</set>
WHERE t.id = #{id}
</update>
此处已经指定end_date
取数为now()
,肯定不会记录到毫秒数的。需改成t.end_date = now(3),
。
再看另外一个updateExecLog
更新语句:
<update id="updateByPrimaryKeySelective" parameterType="com.xy.cloudiview.common.po.ExecLogWithBlobs">
update execlog
<set>
<if test="endDate != null">
end_date = #{endDate,jdbcType=TIMESTAMP},
</if>
</set>
where id = #{id,jdbcType=BIGINT}
</update>
貌似找到一点问题解决思路,开启MySQL日志打印功能:
mybatis:
configuration:
log-impl: org.apache.ibatis.logging.stdout.StdOutImpl
一个很无厘头的尝试:
<if test="endDate != null"> end_date = #{endDate,jdbcType=TIMESTAMP(3)},</if>
报错:
MyBatisSystemException: nested exception is org.apache.ibatis.builder.BuilderException: Error resolving JdbcType. Cause: java.lang.IllegalArgumentException: No enum constant org.apache.ibatis.type.JdbcType.TIMESTAMP(3)
at java.lang.Enum.valueOf(Enum.java:238)
那就把jdbcType
去掉:
<if test="endDate != null">
end_date = #{endDate},
</if>
打印日志如下:
JDBC Connection [com.mysql.cj.jdbc.ConnectionImpl@7ef24deb] will not be managed by Spring
==> Preparing: update execlog SET end_date = ? where id = ?
==> Parameters: 2022-10-12 17:08:13:013(String), 31542157(Long)
nested exception is com.mysql.cj.jdbc.exceptions.MysqlDataTruncation: Data truncation: Incorrect datetime value: '2022-10-12 16:05:47:047' for column 'end_date' at row 1
也就是说,当MyBatis遇到MySQL timestamp(3)
时,MyBatis上面这种写法支持不了。
于是只能换一种写法:
<update id="updateByPrimaryKeySelective" parameterType="com.xy.cloudiview.common.po.ExecLogWithBlobs">
update execlog
<set>
<if test="modelId != null">
model_id = #{modelId,jdbcType=BIGINT},
</if>
end_date = now(3),
</set>
where id = #{id,jdbcType=BIGINT}
</update>
解决问题。也不需要在Java代码层设置带毫秒数的当前时间。
此时,回头看看第一个截图,里面有一个日志类型的字段,文章开头没有描述。这里解释一下,因为平台有多种类型的任务,涉及多个maven Module模块和多个表,于是有多个execlog的insert和update语句。
再来看一个insertExecLog语句,省略无关字段,这种方法是MyBatis插件generator自动生成的,也没有任何问题,就是看起来非常冗余,两个<trim>
语句块,再加上每个<trim>
语句块里面每个字段都有<if>
语句块条件判断语句:
<insert id="insertSelective" parameterType="com.xy.cloudiview.common.po.ExecLogWithBlobs">
<selectKey resultType="java.lang.Long" keyProperty="id" order="AFTER">
SELECT LAST_INSERT_ID()
</selectKey>
insert into execlog
<trim prefix="(" suffix=")" suffixOverrides=",">
<if test="startDate != null">
start_date,
</if>
</trim>
<trim prefix="values (" suffix=")" suffixOverrides=",">
<if test="startDate != null">
#{startDate,jdbcType=TIMESTAMP},
</if>
</trim>
</insert>
维护项目的一个默认的潜规则,就是除非没有大的问题,尽可能不要大面积改动代码,大面积改动还不如重构,重构的前提是对项目非常了解。
当然这个地方只是一个MyBatis方法而已,还达不到重构那个深度。但是我也是想着尽可能不要改动太多,于是改动如下:
<insert id="insertSelective" parameterType="com.xy.cloudiview.common.po.ExecLogWithBlobs">
<selectKey resultType="java.lang.Long" keyProperty="id" order="AFTER">
SELECT LAST_INSERT_ID()
</selectKey>
insert into execlog
<trim prefix="(" suffix=")" suffixOverrides=",">
start_date,
</trim>
<trim prefix="values (" suffix=")" suffixOverrides=",">
now(),
</trim>
</insert>
报错信息就不贴出来了,原因还是当MyBatis遇到MySQL timestamp(3)
时,MyBatis上面这种写法不支持。
改动方法:
删除start_date
对应的if
判断语句块,让MySQL的default CURRENT_TIMESTAMP(3)
来使其生效
使用如下方法:
<insert id="saveExecLog" parameterType="com.xy.cloudiview.common.po.ExecLogWithBlobs">
INSERT INTO execlog(start_date) VALUES (now(3))
</insert>
日志查询
改进前的执行日志列表页查询语句为:
select
DATE_FORMAT(t.start_date,'%Y-%m-%d %H:%i:%s') as startDate,
TO_SECONDS(t.end_date)-TO_SECONDS(t.start_date) as totalTime
from execlog
改进后的:
select
SUBSTRING(DATE_FORMAT(t.end_date, '%Y-%m-%d %H:%i:%s.%f'), 1, 23) AS endDate,
TIMESTAMPDIFF(MICROSECOND, t.start_date, t.end_date) / (1000 * 1000) AS totalTime
from execlog
最后实现的效果是:
参考
get-milliseconds-with-date-format-in-mysql
https://stackoverflow.com/questions/26299149/timestamp-with-a-millisecond-precision-how-to-save-them-in-mysql
https://stackoverflow.com/questions/20520443/mysql-timestamp-to-default-null-not-current-timestamp
来源:https://blog.csdn.net/lonelymanontheway/article/details/127292085
猜你喜欢
- 什么是MobileNetV2模型MobileNet它哥MobileNetV2也是很不错的呢MobileNet模型是Google针对手机等嵌入
- 本文实例为大家分享了python学生信息管理系统的具体代码,供大家参考,具体内容如下""" 程序名
- 本文介绍如何建立基于Web的日历,同时为不熟悉Active Server Pages(ASP)、SQL和ADO的开发者提供建立Web站点的过
- sys.argv[]是用来获取命令行参数的,sys.argv[0]表示代码本身文件路径,所以参数从1开始,以下两个例子说明:1、使用sys.
- 序言小学妹说要毕业了,学了一学期Python等于没学,现在要做毕设做不出来,让我帮帮她,晚上去她家吃夜宵。当时我心想,这不是分分钟的事情,还
- 概述pydicom是一个常用python DICOM parser。但是,没有提供解析多帧图的示例。本文结合相关函数和DICOM知识做一个简
- 最简单的办法就是直接在php程序代码中加入下面代码:error_reporting(E_ALL^E_NOTICE^E_WARNING);可以
- 先举个例子,分别以不指定编码、指定编码为 utf-8、指定编码为 utf-8-sig 三种方式来做比较,再将写入 csv 文件和 txt 文
- 1.准备工作1.在文件里找到设置2.在项目里找到python解释器,点击右边的加号3.搜素pygame并安装同理下载pgzero安装包2.开
- 本文实例讲述了python通过smpt发送邮件的方法。分享给大家供大家参考。具体实现方法如下:import smtplib, socketf
- 工欲善其事必先利其器,Python开发利器Pycharm常用快捷键以及配置如下,相信有了这些快捷键,你的开发会事半功倍一 常用快捷键编辑类:
- 1 简介孤立森林(isolation Forest)是一种高效的异常检测算法,它和随机森林类似,但每次选择划分属性和划分点(值)时都是随机的
- import socketimport Queueimport threadingdef worker(): &nbs
- 1. 在控制台输入以下命令,检查Jupyter notebook的安装目录jupyter notebook --generate-confi
- 今天开始学习 YUI,加强一下对 JavaScript 的理解。1. 命名空间 YAHOO
- 这里采用PyCharm专业版可以直接创建Django项目,这里面自动部署了Django的基本环境。在创建项目时选择Django项目:第一次建
- 通过将身份认证令牌直接传给 API 服务器,可以避免使用 kubectl 代理,像这样:使用 grep/cut 方式:# 查看所有的集群,因
- 本文实例讲述了Django restframework 框架认证、权限、限流用法。分享给大家供大家参考,具体如下:概述Django Rest
- 针对这种情况,人工智能自动SQL优化工具应运而生。现在我就向大家介绍这样一款工具:SQLTuning for SQL Server。1. S
- Oracle基本PLSQL的使用实例详解PL/SQL 块是在 SQL 语言之上发展起来的一种应用,可以集中的处理各种复杂的 SQL 操 作。