MySQL检查表是否有一些记录更改 [英] MySQL check if table has some records changed
问题描述
我正在研究一个使用MySQL数据库作为数据存储层的Java应用程序.数据库中没有几个配置表，但是每个表都有成千上万的记录/行.当应用程序启动时，所有这些配置都将缓存/加载到内存中的相应数据结构/bean(JAVA POJO)中.
I am working on an Java application which uses MySQL database as the data storage layer. There are few configuration tables in database, but each table has many thousands of records / rows. These all configuration is cached / loaded in memory in corresponding data structures / beans(JAVA POJO's) when application starts up.
一切都很好，除了每次应用程序启动缓存时，通常需要15到20分钟，因为要缓存的数据很大，而且某些列具有XML字符串，该字符串将被解析然后存储在bean中.
Everything is fine except that every time the application starts the caching takes place and this usually takes 15-20 minutes, as the data to be cached is huge and also some columns have XML string which is parsed and then stored in beans.
那有什么大不了的??
So what's the big deal??
- 当连续两次启动之间没有数据更改时，为什么还要缓存??我可以将所有bean封装在一个通用的Config bean中并对其进行序列化.下次当我发现没有数据更改时，加载这个序列化的对象-当然可以，加载序列化的对象要比数据库命中加bean填充要快得多.
那么我有什么办法可以解决这个问题? 当然是在数据库级别.我将查询应用程序何时启动-自上次启动以来，数据库表中是否有任何更改.如果是，则执行相同的旧无聊缓存过程并存储一些唯一标识符并进行序列化，或者如果最后一个标识符和当前标识符相同，则只需加载序列化的对象.当然，此唯一标识符将是持久性的.
So is there any way I can figure this out? Of course at database level. I would query when the application starts - Was there any change in the database tables since it was last started. If yes do the same old boring caching process and store some unique identifier and serialize, Or if last identifier and current identifier are same just load the serialized object. This unique identifier will of course be persistent.
推荐答案
将类型为timestamp的last_updated列添加到表中.
Add an last_updated column of type timestamp to the table.
当您需要检查表上是否有更改时，只需执行查询:
When you need to check if there are changes on the table simply execute the query:
select max(last_updated) from YOUR_TABLE
如果last_updated是在创建最后一个缓存副本之后，则可以使用与以下查询类似的查询，仅使用自上次创建缓存以来已更改的元素来更新缓存:
If the last_updated is after the time you created the last cache copy you can update the cache with only the elements changed since last creation of the cache with a query similar to this one:
select * from YOUR_TABLE where last_updated > LAST_CACHE_UPDATE
如注释中所述，
可高度推荐在last_updated列上添加索引.使用索引可以以30个步骤检索1.000.000.000条记录中的最大值(而不是注释中提到的1.000.000.000条错误).
As explained in the comments is higly recomandable to add an index on the column last_updated. Using an index give you the possibility to retrieve the maximum value in a table of 1.000.000.000 records in 30 steps (not 1.000.000.000 as wrong mentioned in the comments).
这篇关于MySQL检查表是否有一些记录更改的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
