mysql split data table vertically

During the development process, I encountered such a problem: there is an article table in the database to save the relevant information of the article (such as: title, content, author, creation time, number of collections, etc.), and there is another table. A table is used to save the collection relationship between users and articles (the main fields are: user ID, article ID). When the article is viewed for the first time, the article data is obtained from the database and the data is cached in redis, thus improving performance.
But now because every time an article is collected or uncollected, the number of collections field in the article table will be updated, and the cache of the article will be cleared (the reason why we choose to clear the cache instead of updating the cache is to avoid the generation of dirty data), The next time you access the article, the database will be queried and the data cached, so it will have a certain impact on performance!

So I decided to split the number of collections in the article table to create a new table of collections (the main fields are: primary key id, article ID, number of collections), and cache the number of collections separately when caching article data, so that every time The article table does not need to be updated when collecting or unfavorite articles. Only the article collection count table is updated. After updating the article collection count table, the cache is updated directly (the reason why dirty data is not feared here is because the collection number is not particularly important data, even if there is a certain error. It will not cause any adverse effects).

Using the above solution, two questions arise, and I hope you can answer them:

1. Because the primary key ID and article ID fields in the number of article collections are unique and non-repeating fields, is it possible to omit the primary key ID field and directly use the article ID as the primary key?

2. Because if each time an article is added, an article collection data corresponding to the article ID is not synchronized, then each time the article collection number is updated when an article is collected or uncollected, it will be necessary to first query whether the article collection count table is There is already a collection number corresponding to the article ID. If there is no collection number, add a new article collection number. If there is, directly update the collection number of the article. Therefore, for the sake of simplicity, I decided to synchronize the new article data every time. Add an article collection data corresponding to the article ID. When updating the article collection count in the future, you do not need to first determine whether the data exists, but can directly update the data. In this case, how to avoid adding article data and adding new articles? What happens if there is an anomaly in the collection data, resulting in the success of adding article data but the failure of adding article collection data? I know it can be avoided using transactions, is there an easier way? Can MySQL automatically add a piece of data to another table when adding a piece of data? (Database scum~)

Thanks for the above!

Reply content:

During the development process, I encountered such a problem: there is an article table in the database to save the relevant information of the article (such as: title, content, author, creation time,

number of collections, etc.), and there is another table. A table is used to save the collection relationship between users and articles (the main fields are: user ID, article ID). When the article is viewed for the first time, the article data is obtained from the database and the data is cached in redis, thus improving performance. But now because every time an article is collected or uncollected, the
number of collections field in the article table will be updated, and the cache of the article will be cleared (the reason why we choose to clear the cache instead of updating the cache is to avoid the generation of dirty data), The next time you access the article, the database will be queried and the data cached, so it will have a certain impact on performance!

So I decided to split the number of collections in the article table to create a new table of collections (the main fields are: primary key id, article ID, number of collections), and cache the number of collections separately when caching article data, so that every time The article table does not need to be updated when collecting or unfavorite articles. Only the article collection count table is updated. After updating the article collection count table, the cache is updated directly (the reason why dirty data is not feared here is because the collection number is not particularly important data, even if there is a certain error It will not cause any adverse effects).

Using the above solution, two questions arise, I hope you can answer them:

1. Because the primary key ID and article ID fields in the number of article collections are unique and non-repeating fields, is it possible to omit the primary key ID field and directly use the article ID as the primary key?

2. Because if each time an article is added, an article collection data corresponding to the article ID is not synchronized, then each time the article collection number is updated when an article is collected or uncollected, it will be necessary to first query whether the article collection count table is There is already a collection number corresponding to the article ID. If there is no collection number, add a new article collection number. If there is, directly update the collection number of the article. Therefore, for the sake of simplicity, I decided to synchronize the new article data every time. Add an article collection data corresponding to the article ID. When updating the article collection count in the future, you do not need to first determine whether the data exists, but can directly update the data. In this case, how to avoid adding article data and adding new articles? What happens if there is an anomaly in the collection data, resulting in the success of adding article data but the failure of adding article collection data? I know it can be avoided using transactions, is there an easier way? Can MySQL automatically add a piece of data to another table when adding a piece of data? (Database scum~)

Thanks for the above!

1. Yes, but here you need to do a linkage, that is, when adding or deleting articles, you must also operate the article collection table

2 Use mysql's udf, for details, please see MySQL :: MySQL 5.1 Reference Manual :: 22.3 Adding New Functions to MySQL Then use trigger to call the function after the article table

delete and insert, and write to the article collection number Go to the table. It's roughly like this.