首页 > 资讯 > 数据库 >浅谈MySQLtimestamp(3)问题

104

分享到

浅谈MySQLtimestamp(3)问题

MySQL timestamp(3) 2023-01-05 12:01:31 104人浏览八月长安

摘要

目录背景优化日志记录日志查询参考背景最近在负责开发维护的一款数据平台，有一个功能是把数据从某个源头数据源（如常规的JDBC数据源，Mysql，oracle等）推到目地数据源（还包括

背景

最近在负责开发维护的一款数据平台，有一个功能是把数据从某个源头数据源（如常规的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

到此这篇关于浅谈MySQL timestamp(3)问题的文章就介绍到这了,更多相关MySQL timestamp(3)内容请搜索编程网以前的文章或继续浏览下面的相关文章希望大家以后多多支持编程网！

您可能感兴趣的文档:

--结束END--

本文标题: 浅谈MySQLtimestamp(3)问题

本文链接: https://lsjlt.com/news/176812.html(转载时请注明来源链接)

有问题或投稿请发送至: 邮箱/279061341@qq.com QQ/279061341

回答

如何调试操作系统的错误？
操作系统

2023-11-15发布

回答

操作系统中的I/O系统是如何实现的？
操作系统

2023-11-15发布

回答

如何实现操作系统的内存管理？
操作系统

2023-11-15发布

回答

什么是虚拟内存，它对操作系统有什么影响？
操作系统

2023-11-15发布

回答

ASP中的MVC架构和WebForms架构有什么区别和使用场景？
ASP.NET

2023-11-15发布

回答

ASP中的数据验证和数据校验有什么不同？
ASP.NET

2023-11-15发布

回答

ASP中的ADO对象和DAO对象有什么区别和使用方法？
ASP.NET

2023-11-15发布

回答

Node.js中的包管理器NPM是什么？如何使用它进行依赖管理？
node.js

2023-11-15发布

回答

Vue.js中的动态组件是什么？如何使用它来动态渲染组件？
VUE

2023-11-15发布

回答

如何使用Vue.js实现懒加载和预加载？
VUE

2023-11-15发布

浅谈MySQLtimestamp(3)问题

目录

背景

优化

日志记录

日志查询

参考

浅谈MySQLtimestamp(3)问题

浅谈MySQL timestamp(3)问题

浅谈location.search与location.hash的问题

浅谈Python 中的复数问题

浅谈NodeJS中require路径问题

浅谈java中String相关问题

浅谈foreach写失效的问题

浅谈Nodejs中的作用域问题

浅谈golang 中time.After释放的问题

浅谈Angular的12个经典问题

浅谈springcloud gateway 连接保活问题

浅谈父子组件传值问题

浅谈编码,解码,乱码的问题

浅谈MySQL分页Limit的性能问题

浅谈Golang 嵌套 interface 的赋值问题

浅谈HashMap在高并发下的问题

浅谈MySQL中不等号索引问题

浅谈Springmvc中的页面跳转问题

关于 React 中 useEffect 使用问题浅谈

浅谈Python中的函数传递问题

关于SQL建表语句使用详解

HBase在大数据审计与合规性追踪中的应用

MySQL与HBase在大数据金融分析中的性能与可扩展性对比

HBase的Region Server之间的网络通信优化

HBase在大数据监控与告警系统中的实时数据处理能力

MySQL与HBase在大数据安全策略中的实现与对比

HBase的分布式事务处理在复杂业务场景中的应用

MySQL与HBase在混合存储架构中的整合策略

HBase如何支持高效的二级索引查询

MySQL与HBase在物联网数据收集与处理中的协作模式