1. I want to count the number of new customers today, the number of new customers yesterday, and the number of follow-up records today. The amount of data is very large. The method used before is plus 1 minus 1, but errors often occur. How to solve this statistical problem? Do I have to use count?
PHP中文网2017-05-16 13:13:10
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.
ringa_lee2017-05-16 13:13:10
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
