mysql - Ask a question about database caching in Java

Question

Now I am in charge of a personal writing platform in a school library. The basic business model has been completed, but the project has not yet been optimized in a high-concurrency environment.

Each student can write a book review or take notes after reading a book. Book reviews are public and notes are private. According to the current practice, every student operation accesses the database for query, so there will definitely be a performance bottleneck in the future.

I probably looked at the relevant tutorials on ehcache but there is no good solution. The main reason is that I can't judge whether the cache is invalid. For example, if you put a certain number of notes from high-frequency students into the cache, if you set a specific timeout of 5 minutes, what should you do if a new note is added or modified within this 5 minutes?

For example, corresponding to a sql statement select * from comments condition in mybatis, I can create a memory cache through ehcache, but if comments adds or updates a new record, what should I do? Can gracefully update the cache last added in ehcache.

Can any netizens suggest relevant solutions? thanks :-)

Answer 1

Since it is a cache, it must be tolerant of data effectiveness. Otherwise, if you want to strictly control real-time data, you can only access the database for query.

According to the business scenario described by the subject, the common solution is to maintain the relationship between books and book reviews through external indexes (of course the index is not completely real-time), and then through kv cache (redis, ehcache, map, etc.) to cache the specific content of the book review.
When updating data, usually the database is updated first, and then the cache is updated. The index does not need to be updated.
When data is updated (insert), the corresponding relationship needs to be added to the index based on the update.

For a simple external index, you can add a (book-book review) relational table in the database, build an index, and when querying book reviews, first query (paged query) the relational table, and then query the specific book review information through the book review primary key ( Caching is added to a single book review information), it is recommended to query twice and assemble the data in memory, and do not use join table query (affects database performance). There is no need to create a cache for relational table data. You only need to increase the cache of book review information.