I want to realize some statistics of data, such as user points, number of posts, number of comments, etc., total number of posts in the section, total comments; numbers and the like.
Example: User posts increase points, update the statistics of the number of posts, change the statistics of posts in the same section, or there may be other statistical changes.
In this case, should we directly use the data table count() statistics where needed, or add various statistical fields to the table, and complete it through transactions when triggered?
I personally prefer the latter, but I think it is a bit unreasonable to use transactions to ensure the consistency of statistics? ?
But then again, if transactions are not used, some errors will be caused by statistical errors in some places, similar to paging faults?
Do you have any good ideas or suggestions? Please give me some advice!
![[Web front-end] Node.js quick start](https://img.php.cn/upload/course/000/000/067/662b5d34ba7c0227.png)
It is best not to use count. When the amount of data is large, the database cannot handle this kind of query, especially this kind of query to users.
Regular count and then put it in the cache
I am not a master, but if I do it, I will put this kind of data in redis.
[Background]
1 This problem is caused by the database performance not being able to meet the existing business scenario
2 According to the business scenario you provided, the data is allowed to have a certain error
【Program Structure】
The existing program structure is operated by directly calling the database, but the performance of the database cannot meet the requirements. At this time, we cannot rely solely on the database, so we need to add a layer of [counter] between the databases
Before and after program adjustment, we need to add [aspect] in the three links of [add], [deletion] and [modify] of the database, which is the afterSave event, which can also be understood as a hook.
Redis is usually used for [counter], because it can Persistence
【Note required】
1 Because the db operation may be normal, but the redis operation fails, there may be data errors
2 Since there may be errors, an error correction mechanism is needed. This mechanism can reduce the pressure on the server. In small cases, use mysql's count or sum to update
3 or use another [transaction mechanism] to ensure the atomicity of DB and Redis operations.
InnoDB’s count needs to scan all data rows in real time, which is slow
Myisam should not be used very much
Recommendation
数据表添加统计字段Statistics are processed using back-end services. For example, the back-end is connected to a slave. It will not affect your business
The first person does not recommend using the count() form for data accumulation, and should prefer the cumulative form.
Because every time new data is added, count() must be used to count, which is very overwhelming for the database.
So it is recommended to do this:
1. Use nosql to retain all statistical data
2. When adding new data, trigger a module to accumulate the corresponding data and overwrite the original data.
3. In order to ensure that nosql down problems occur, it is recommended to save corresponding statistical data in the database and write a logic module to save nosql data to the database regularly.
4. Of course, you can use count regularly (this can be done when the server is idle) and recheck whether the statistics are correct