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.
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