扫码关注官方微信

扫码下载APP
返回顶部
首页 > 资讯 > 数据库 >浅谈MySQL timestamp(3)问题
  • 110

0
分享到

浅谈MySQL timestamp(3)问题

MySQLtimestamp(3) 2023-01-05 11:01:13 110人浏览 泡泡鱼
摘要

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

背景

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

本文标题: 浅谈MySQL timestamp(3)问题

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

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

猜你喜欢
  • 浅谈MySQL timestamp(3)问题
    目录背景优化日志记录日志查询参考背景 最近在负责开发维护的一款数据平台，有一个功能是把数据从某个源头数据源（如常规的JDBC数据源，mysql，oracle等）推到目地数据源（还包括企微，MQ等）。一次推送数据就是一个任...
    99+
    2023-01-05
    MySQLtimestamp(3)
  • 浅谈mysql的timestamp存在的时区问题
    目录简介基本概念timestamp与datetime区别为什么网上又说timestamp类型存在时区问题？那为什么网上会说timestamp存在时区问题？serverTimezone的本质将serverTimezone与...
    99+
    2022-07-14
    mysqltimestamp时区问题 mysqltimestamp时区
  • 浅谈MySQLtimestamp(3)问题
    目录背景优化日志记录日志查询参考背景 最近在负责开发维护的一款数据平台，有一个功能是把数据从某个源头数据源（如常规的JDBC数据源，MySQL，Oracle等）推到目地数据源（还包括...
    99+
    2023-01-05
    MySQL timestamp(3)
  • 浅谈MySQL分页Limit的性能问题
    MySQL的分页查询通常通过limit来实现。limit接收1或2个整数型参数，如果是2个参数，第一个是指定第一个返回记录行的偏移量，第二个是返回记录行的最大数目。初始记录行的偏移量是0。为了与Postgr...
    99+
    2024-04-02
  • 浅谈MySQL中不等号索引问题
    目录1.当不等号<>作用在普通索引字段上2.当不等号<>作用在主键索引字段上3.当不等号<>作用在唯一索引字段上最近在使用mysql中的一个小总结。 在MySQL中，不等号<&g...
    99+
    2023-03-20
  • 浅谈Mysql时间的存储 datetime还是时间戳timestamp
    目录简单对比占用空间优缺对比如何存储毫秒或者更高级别的小数？时间戳详解一个方便的用法显示格式（非存储格式）Java可能遇到的坑简单对比 占用空间 mysql 常用的日期时间类型常用的是datetime、timestamp...
    99+
    2022-07-26
    Mysql时间 存储 Mysql  datetime timestamp
  • 浅谈Mysql时间的存储 datetime还是时间戳timestamp
    目录简单对比占用空间优缺对比如何存储毫秒或者更高级别的小数？时间戳详解一个方便的用法显示格式（非存储格式）java可能遇到的坑简单对比 占用空间 MySQL 常用的日期时间类型常用的...
    99+
    2024-04-02
  • 浅谈location.search与location.hash的问题
    location.search和location.hash是JavaScript中URL对象的两个属性，用于获取和设置URL中的查询...
    99+
    2023-08-11
    location.hash
  • 浅谈Python 中的复数问题
    前言 复习试题时，发现一道复数问题 问题 关于 Python 的复数类型，以下选项中描述错误的是 A复数的虚数部分通过后缀“J”或者“j”来表示 B对于复数 z，可以用 z.real 获得它的实数部分 C对于复数 z...
    99+
    2022-06-02
    Python 复数问题
  • 浅谈NodeJS中require路径问题
    项目需要用nodejs，感觉nodejs是前端装逼神器了，是通向全栈工程师的必经之路哇，接下来开始踏上学习nodejs的征程。下面是第一个hello，world的程序。 1、server.js文件，这相当于...
    99+
    2022-06-04
    浅谈 路径 NodeJS
  • 浅谈java中String相关问题
    原文：https://blog.csdn.net/qq_41268447/article/details/96759597    首先我们先说一下java堆内存和栈内存 java中八个基本数据类型就是值类型，存放在栈内存...
    99+
    2023-06-02
  • 浅谈foreach写失效的问题
    Java中的细节一定要清楚，否则非常容易出现问题。例如这个场景：遍历一个集合，对符合某种条件的元素做修改。大家往往会写出如下代码：public class JavaTest{ public static void main(String[]...
    99+
    2023-05-31
    foreach 写失效 fo
  • 浅谈Redis跟MySQL的双写问题解决方案
    目录写在前面三种读写缓存策略Cache-AsidePattern（旁路缓存模式）Read-Through/Write-Through（读写穿透）WriteBehindPattern（...
    99+
    2024-04-02
  • 浅谈Nodejs中的作用域问题
    在JS中有全局作用域和函数作用域，而在Nodejs中也自己的作用域，分为全局作用域（global）和模块作用域。 js作用域： 以前学js的时候我们的全局对象是window，如： var a = 10;...
    99+
    2022-06-04
    浅谈 作用 Nodejs
  • 浅谈golang 中time.After释放的问题
    在谢大群里看到有同学在讨论time.After泄漏的问题，就算时间到了也不会释放，瞬间就惊呆了，忍不住做了试验，结果发现应该没有这么的恐怖的，是有泄漏的风险不过不算是泄漏， 先看AP...
    99+
    2024-04-02
  • 浅谈Angular的12个经典问题
    目录1. 请解释Angular 2应用程序的生命周期hooks是什么？2. 使用Angular 2，和使用Angular 1相比，有什么优势？3. Angular 2中的路由工作原理...
    99+
    2024-04-02
  • 浅谈springcloud gateway 连接保活问题
    项目中使用了springcloud gateway作为网关，上游与负载均衡服务器连接。 近期通过监控系统观察，发现网关与上游负载均衡服务器保持的TCP连接有300+，初步怀疑是调用方...
    99+
    2024-04-02
  • 浅谈父子组件传值问题
    目录一、问题描述二、问题解决一、问题描述 想要搭建一个模型检验的页面，在点击按钮“开始检测”后，后端会获取相应数据、页面跳转并进行渲染。 主要涉及三个页面：i...
    99+
    2023-05-14
    Vue父子组件传值 Vue父子组件 Vue.js父子组件传值
  • 浅谈Linux 二进制包安装MySQL的一些问题
    第一步：安装相关的依赖yum install perl-Data-Dumper 第二步：初始化mysql数据库的内部信息./scripts/mysql_install_db --basedir=/usr/l...
    99+
    2022-06-04
    浅谈 Linux MySQL
  • 浅谈编码,解码,乱码的问题
    在开发的过程中，我们不可避免的会遇到各种各样的编码，解码，或者乱码问题，很多时候，我们可以正常的解决问题，但是说实在的，我们有可能并不清楚问题到底是怎么被解决的，秉承知其然，更要知其所以然的理念，经过一番研...
    99+
    2022-06-04
    乱码 浅谈
软考高级职称资格查询
热门wiki
近期文章
推荐阅读
热门问答
热门标签
编程网，编程工程师的家园，是目前国内优秀的开源技术社区之一，形成了由开源软件库、代码分享、资讯、协作翻译、讨论区和博客等几大频道内容，为IT开发者提供了一个发现、使用、并交流开源技术的平台。

  • 官方手机版

  • 微信公众号

  • 商务合作