The amount of data is relatively large. How to better implement statistical functions?

How to count the total number when the amount of data is relatively large?

For example, if there are 5 million pieces of data in a table, the traditional method is count(), which is very inefficient.

Some people on the Internet said to use mysql triggers to insert statistical results into a result table.

Are there any other methods? For example, is it possible to insert asynchronous execution into a MySQL result table?

Or can the statistical results be put into redis?

There is also talk of using scheduled execution, but our usage scenario requires real-time calculation of the sum.

How to count the total number when the amount of data is relatively large?

For example, if there are 5 million pieces of data in a table, the traditional method is count(), which is very inefficient.

1. Someone on the Internet said to use mysql triggers to insert statistical results into a result table.

Are there any other methods? For example, is it possible to insert asynchronous execution into a MySQL result table?

2. Or can the statistical results be put into redis?

There is also talk of using scheduled execution, but our usage scenario requires real-time calculation of the sum.

3. You can create a new table to save row number information
Table name (primary key) | data amount

If data is inserted during the trigger, the data amount of the corresponding table will be +1, and if data is deleted, it will be decremented by 1.

4. Another option is to only calculate increments of 1 minute each time (according to your business's real-time requirements). The last statistical value plus the increment of 1 minute is the current total. Of course, this depends on whether the old data has been deleted. I think this method will be coupled with inserting statistical results into the table.

I tend to use redis to store, insert+1, delete-1. There is no pressure on the db, and coupling is reduced at the same time. Just use memcache or redis to count.

When you need to check the count, check the memcache first. If it is not found, go to the database count, and then set up the memcache. If you check the memcache next time, you will have it, so you don’t need to count.

The above is a relatively large amount of data. How to better implement statistical functions? For more related content, please pay attention to the PHP Chinese website (www.php.cn)!