It is best not to perform statistics on large amounts of data directly on the main database - the method of adding 1 and subtracting 1 without locking the table is inaccurate. Locking the table affects performance, and the count performance will also be poor.
It is recommended to separate the statistical function into a subsystem, and the business server notifies the operation of adding/deleting customers to this statistical subsystem through the message queue.
In this subsystem, you can consider using a time series database (such as open source Elasticsearch) for storage, which is very convenient for statistics. Even if you don't need a special time series database and use mysql directly, you can create a separate mysql database, so that the lock table can be increased by 1, subtracted by 1, or counted. It is not the main database anyway.
![[Web front-end] Node.js quick start](https://img.php.cn/upload/course/000/000/067/662b5d34ba7c0227.png)
It is best not to perform statistics on large amounts of data directly on the main database - the method of adding 1 and subtracting 1 without locking the table is inaccurate. Locking the table affects performance, and the count performance will also be poor.
It is recommended to separate the statistical function into a subsystem, and the business server notifies the operation of adding/deleting customers to this statistical subsystem through the message queue.
In this subsystem, you can consider using a time series database (such as open source Elasticsearch) for storage, which is very convenient for statistics. Even if you don't need a special time series database and use mysql directly, you can create a separate mysql database, so that the lock table can be increased by 1, subtracted by 1, or counted. It is not the main database anyway.
If it is an auto-incrementing ID, it can be calculated by subtracting yesterday’s last one from today’s last one.
The statistics table used is plus 1 and minus 1
Use count