Mysql 计数表的设计

    在Mysql中，表的设计是关键的一环，前期的设计失误，给后期带来的代价将是惨重的。在Mysql中，我们经常会用到计数表，特别是在并发的环境中，较差的计数表，可能带来锁的大量争用，从而带来性能问题。比如在web环境中，关于朋友的统计，文件下载量的统计等等都会用到计数表。Mysql High Performance 3rd中关于计数表的设计非常的经典。   比如我们设计如下的一个计数表：   mysql> CREATE TABLE hit_counter (   -> cnt int unsigned not null  -> ) ENGINE=InnoDB;首先我们必须保证我们的是串行的更新这个表，这样在高并发的环境中，我们执行如下操作： mysql> UPDATE hit_counter SET cnt = cnt + 1;这样就会经常出现锁资源争用比较激烈的情况。我们在来看一个好的设计方式: mysql> CREATE TABLE hit_counter (-> slot tinyint unsigned not null primary key,-> cnt int unsigned not null-> ) ENGINE=InnoDB;假设我们有100行，每次更新随机选择其中一行就可以了。 mysql> UPDATE hit_counter SET cnt = cnt + 1 WHERE slot = RAND() * 100;这样我们就把在一行上的竞争，分摊到100行。如果要获得统计结果，我们只用执行如下语句就可以了： mysql> SELECT SUM(cnt) FROM hit_counter;当然对于其他较为复杂的情况我们可以参考High performance Mysql 3rd A common requirement is to start new counters every so often (for example, once aday). If you need to do this, you can change the schema slightly:mysql> CREATE TABLE daily_hit_counter (-> day date not null,-> slot tinyint unsigned not null,-> cnt int unsigned not null,-> primary key(day, slot)-> ) ENGINE=InnoDB;You don’t want to pregenerate rows for this scenario. Instead, you can use ON DUPLICATEKEY UPDATE:mysql> INSERT INTO daily_hit_counter(day, slot, cnt)-> VALUES(CURRENT_DATE, RAND() * 100, 1)-> ON DUPLICATE KEY UPDATE cnt = cnt + 1;If you want to reduce the number of rows to keep the table smaller, you can write aperiodic job that merges all the results into slot 0 and deletes every other slot:mysql> UPDATE daily_hit_counter as c-> INNER JOIN (-> SELECT day, SUM(cnt) AS cnt, MIN(slot) AS mslot-> FROM daily_hit_counter-> GROUP BY day-> ) AS x USING(day)-> SET c.cnt = IF(c.slot = x.mslot, x.cnt, 0),-> c.slot = IF(c.slot = x.mslot, 0, c.slot);mysql> DELETE FROM daily_hit_counter WHERE slot 0 AND cnt = 0;总而言之，第二次将每天的对一条数据的更新，分为N条来处理，最后将数据汇总成需要的数据，从而较少DML带来的lock contention,如果你的表经常需要这样的real-time计数，我想这样的办法还是不错的。对于热点数据的锁资源，分摊到N行来处理，在一些应用环境中确实是个不错的办法，如果你也遇到过这种类似的情况，我想这样分而治之的方法或许是个好的选择！