MySQL查看table更新时间

Posted on | In | | 浏览 次

起因

为了满足后台数据热加载的需求，需要有一种方式能够将mysql中数据更新及早的通知出来，当时最先想到的方案是使用mysql的trigger实现，具体的实现方式就是每一张表创建三个trigger分别是before insert before update before delete将表名写入一张额外的表中，之后读表里面的时间戳来判定某张表是否有更新，进而触发相应的操作。MySQL同样支持trigger触发命令，但是这个需要额外的模块支持

然而以上方案在使用过程中遇到了如下几个问题：

  1. 最严重的就是trigger本身导致的性能问题，由于triggger是按照行触发的，因此在有更新全表这种操作时会巨慢无比，在某些场景下甚至会时间翻倍，因此专门封装了一个mysql的脚本，在导入数据前drop trigger，再倒入完成后recreate
  2. 其次是另外一个性能问题，trigger的本质是每一行的修改都会触发一条新的sql而当第二个触发的sql因为某种原因阻塞时会导致整个操作的阻塞，并且如果考虑到引擎的话排查起来会更加麻烦（以后会说）
  3. 创建了trigger之后在导入，导出mysql表的时候会影响trigger，例如drop table会同时drop trigger，所以还需要将mysqldump封装一下增加上参数–no-create-info，同时由于创建trigger还涉及到了用户权限的问题还需要增加–skip-triggers否则会将trigger导出并可能在导入时发生错误

由上面的结果可以看出使用trigger并不是最优解，如果mysql是自建的话，那么其实还有个方法就是监控mysql datadir下面的表文件，因为每一张表其实在底层都对应一个文件 ：）然而由于对于mysql服务搭建在云上面的无解

优化

后来无意间注意到了information_schema的TABLES表，注意到其包含一个UPDATE_TIME字段！！于是赶紧验证对一个表分别进行了增、删、改、查，这一列的信息都保持了更新！找到了一个完美的替换方案！

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
select * from `TABLES` limit 1\G
***************************[ 1. row ]***************************
TABLE_CATALOG   | def
TABLE_SCHEMA    | information_schema
TABLE_NAME      | CHARACTER_SETS
TABLE_TYPE      | SYSTEM VIEW
ENGINE          | MEMORY
VERSION         | 10
ROW_FORMAT      | Fixed
TABLE_ROWS      | None
AVG_ROW_LENGTH  | 384
DATA_LENGTH     | 0
MAX_DATA_LENGTH | 16434816
INDEX_LENGTH    | 0
DATA_FREE       | 0
AUTO_INCREMENT  | None
CREATE_TIME     | 2017-08-21 21:17:16
UPDATE_TIME     | None
CHECK_TIME      | None
TABLE_COLLATION | utf8_general_ci
CHECKSUM        | None
CREATE_OPTIONS  | max_rows=43690
TABLE_COMMENT   |

然而实际上有如下两个注意事项：

  1. mysql必须是5.7版本的才会填充这个字段
  2. 在使用过程中发现某些情况下update_time字段是有可能为空的！根据官方文档有如下解释，当mysql server restart的时候这个字段会是null，因此进行了验证，对该表进行了修改，然后这一列值变了，还是可以正常使用的，虚惊一场。

​ UPDATE_TIME displays a timestamp value for the last UPDATE, INSERT, or DELETE performed on InnoDB tables that are not partitioned. For MVCC, the timestamp value reflects the COMMIT time, which is considered the last update time. Timestamps are not persisted when the server is restarted or when the table is evicted from the InnoDB data dictionary cache.

​ The UPDATE_TIME column also shows this information for partitioned InnoDB tables.

转载请注明来源链接 http://just4fun.im/2017/08/21/MySQL查看table更新时间/ 尊重知识,谢谢:)