Design of MySQL counter table

If your app saves counters in a table, you might encounter concurrency problems when you update the counters. Counter tables are very common in Web applications. You can use this table to cache a user's friend book, File download times, and so on. Creating a separate table storage counter is a great way to make the counter table small and fast. Using stand-alone tables can help prevent query cache invalidation. As the following example:

Suppose there is a counter table with only one row of data, which records the number of clicks on the site.

CREATE TABLE Hit_counter (     cnt  intnotnull) ENGINE=InnoDB;

Every click on the site will result in an update to the counter:

UPDATE SET = + 1;

The problem arises, for any transaction that wants to update the line, there is a global mutex on this record. This causes these transactions to be executed serially only. To live with the high ice method to update the performance, we can solve this:

The technique is saved in multiple lines, and each time a random row is selected for updating, the counter table needs to be modified as follows:

CREATE TABLE hit_counter (    tinyintnotnullprimarykey,     intnotnull = InnoDB;

Then add 100 data to this data sheet. Now select a random slot (slot) to update:

UPDATE SET = + 1 where = RAND * ;

To obtain statistical results, make a query on the appliance and Function sum ():

SELECT SUM  from Hit_counter;

But there is a common need to start a new counter every once in a while (such as one per day). To implement this, we continue to modify the counter table:

CREATE TABLE Daily_hit_counter (     Day isn'tnull,     tinyintnot  null,     intnotnull,       Primary day , slot)          ) ENGINE=InnoDB;

In this scenario, you can use the on DUPLICATE KEY update instead of the previous one as before:

INSERT  into  Day , slot, CNT)     Values current_date RAND *  - 1   )    onKEYUPDATE=+1;

If you want to reduce the number of rows in the table to avoid the table becoming too large, you can write a cycle of the tasks performed, merge all the results into slot No. 0, and delete all the other slots:

UPDATEDaily_hit_counter asCINNERJion (Sekect Day,SUM(CNT) asCntMIN(slot) asMslot fromDaily_hit_counterGROUP  by  Day    )  asX USING ( Day )SETC.cnt= IF(C.slot=X.mslot, x.cnt,0), C.slot= IF(C.slot=X.mslot,0, C.slot);

DELETE  from WHERE <> 0  and = 0;

Design of MySQL counter table