高性能MySql进化论(四):Summary,Cache,Counter表的使用

xsw222

　　在实际的应用中，往往会定期的对一个周期内的系统数据进行统计分析。例如某购物网站定期的统计商品在一个月/年期内的销售情况，如果采用扫描所有相关表的方式在某个时间点进行统计分析， 由于数据量很大，以及表结构的复杂，性能可能会是很大的问题。针对这种情况可以通过使用Summary，Cache，Counter 数据表的方式来提高分析的效率。
　　

Summary
　　可以理解为通过定期执行Group By 语句得到的表。例如系统要求月底的时候需要统计出每个商品的销售情况（销售数量，购买次数，总金额 …）,为了达到这个目的可以创建一个以天为时间单位的Summary表，在每天结束的时候对每种商品进行统计分析，将分析的结果保存到Summary表中(每条记录表示每种商品在当天的销售情况)，在月底需要查询某个商品在这个月的销售情况时，只需要查询Summary表中相关商品的30条记录即可。
　　

Cache
　　可以理解为从主表中抽取一些比较常用的字段组成的一张表，这张表里的内容时间性比较强，可以定期的被清理。例如在银行系统中一般都会有一套复杂的表结构来存储用户的交易流水，这种流水表的字段比较多，数量也非常的大，而且时间性比较强(正常情况下一个用户不会去查询几年前的交易记录)，针对这种情况就可以采用Cache表的机制来提高流水的查询效率。 可以把客户在查询流水的过程中关注度比较高的字段单独的抽取成一张表(例如 时间，金额，交易地点….)，这张表只包含几个月的数据，当用户查询流水时，如果要求查询的交易时间在Cache表的时间区间内，就从这张Cache表进行查询，否则去主表进行查询。
　　

Counter
　　很多web应用中都有计数的功能，例如 博客系统一般都会统计好友的数目，发表的文章数，上传的文件数，以及文章的被浏览数。如果 应用中存在较多的计数器，在高并发时，很容易出现“写”的性能问题，针对这种情况可以采取把Counter的字段作为一张单独表的解决方案。
　　
　　·以博客网站的访问次数为例，最简单的计数器表是只包含单独的计数列

mysql>CREATE TABLE hit_counter (
->  cnt int unsigned not null
-> )ENGINE=InnoDB;
　　每当被访问时cnt就执行一次更新操作
　　

mysql>UPDATE  hit_counter  SET cnt = cnt + 1;
　　·当并发的数量比较大时，由于update是串行的操作，所以性能还是会受到影响，为了提高并发的Performance,
可以采用多条记录的方式来提高并发效率步骤如下
　　(1) 更改表结构

mysql>CREATE TABLE hit_counter (
->  slot tinyint unsigned not null primary key,
->  cnt int unsigned not null
-> )ENGINE=InnoDB;
(2) 执行更新操作时，根据并发的数量，随机的选择一条计数器记录（以100表记录为例)　　

mysql> UPDATE hit_counter SET cnt = cnt + 1WHERE slot = RAND() * 100;(3) 用Sum(cnt)的方式得到总的访问数量　　

mysql>SELECT SUM(cnt) FROM hit_counter;

·一种更为通用的做法是以“天”为单位对cnt进行维护，步骤如下　　(1) 更改表结构

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;
(2) 插入的时候，如果发现当天的某个slot已经存在，则对其cnt进行更行
　　

mysql>INSERT INTO daily_hit_counter(day, slot, cnt)
->  VALUES(CURRENT_DATE, RAND() * 100, 1)
->  ON DUPLICATE KEY UPDATE cnt= cnt + 1;
　　Summary, Cache,Counter表的使用会额外的表空间的开销。对于Summary, Cache表的内容可以使用Period脚本定期的进行清除或者是更新，对于Counter表可以定期的对已有的记录进行汇总，生成汇总记录后，只保留汇总记录。