使用Perl Storable 及Hash结构来分析大量数据
2015-04-21 18:07:18 阿炯
接业务部门要求，提取weibo库表中的上一月发表记录在5条(含)以上的用户登录名及最后的别名。但这两个字段在原始表中均为varchar类型且无索引，下面使用了两种方法来处理：利用mysql本身来计算，其二是使用perl及Storable和Hash数据结构的方法计算。

大致环境：
硬件：dell r720 mem:64gb,cpu:40core,hd:600gb sas 15k with raid5
数据库：mysql5.1 myisam merge by month

线上的生产服务器，从库，平均负载：1.2。

方法一：利用mysql的分组计算功能

为了最大限度减少对原始生产表的影响，将所要的字段复制到监时表中。我们从对应的表中取得属于今年3月的数据第一次的id吧，这样效率最高。

select weibo_content_id,origin_author_id,origin_author_name,capture_time from weibo_content_m03 where capture_time>='2015-03-01 00:00:00' order by weibo_content_id asc limit 100;

CREATE TABLE test.wei03 (
  `origin_author_id` varchar(32) DEFAULT NULL,
  `origin_author_name` varchar(128) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=gbk;

就两个字段，加不加索引应该没有多大区别了吧。

insert into test.wei03 select origin_author_id,origin_author_name from weibo_content_m03 where weibo_content_id>=20130816970420965;
mysql> insert into test.wei03 select origin_author_id,origin_author_name from weibo_content_m03 where weibo_content_id>=20130816970420965;
Query OK, 78151834 rows affected (5 min 32.50 sec)
Records: 78151834  Duplicates: 0  Warnings: 0

文件大小为：
-rw-rw---- 1 mysql mysql 8.5K Apr 21 15:48 wei03.frm
-rw-rw---- 1 mysql mysql 2.2G Apr 21 15:53 wei03.MYD
-rw-rw---- 1 mysql mysql 1.0K Apr 21 15:53 wei03.MYI

查询指定条件的数据
select origin_author_id,origin_author_name,count(1) from test.wei03 group by origin_author_id having count(1)>=5;

还是将结果存入文件中吧
select origin_author_id,origin_author_name,count(1) into outfile '/tmp/content.csv' FIELDS TERMINATED BY ',' ENCLOSED BY  '"' ESCAPED BY '\\' LINES TERMINATED BY '\r\n' from test.wei03 group by origin_author_id having count(1)>=5;

会导致从上的复制有比较大的延迟，触发系统复制高延迟报警，系统负载从平均1.2上升到2.7。

Query OK, 3416271 rows affected (14 min 36.19 sec)

在/tmp目录下生成了109M的content.csv，共计3416271行。

方法二：perl的Storable Hash计算

大致过程：从数据库中按固定数量读取记录，分析后存入hash数组中，完成后存为storable类型文件。后在分析该文件，得出结果。

use v5.12;
use DBI;
use Storable;
use DBD::mysql;
use Time::Piece;
use Data::Dumper;
use Cwd qw(abs_path realpath);
use File::Basename qw(dirname);

binmode(STDOUT, ":encoding(utf8)");
my ($stfile,$lastd,$currd,$rs)=('wm03.st');

#get storable variable
if(-e $stfile){
 $lastd=retrieve($stfile);
}

# Init&Conn mysql db
my ($db,$dbhost,$dbuser,$dbpaswd)=('iis_application_data','localhost','root','freeoa');

#mysql obj attr
my %mydbattr=(RaiseError=>1, AutoCommit=>0,mysql_enable_utf8=>1,RaiseError=>1);
my $st=20130816970420965;
#每一次处理100万的数据量
my $offset=1000000;

#create database handle
my $dbh=DBI->connect_cached("DBI:mysql:database=$db;host=$dbhost;mysql_socket=/tmp/mysql.sock",$dbuser,$dbpaswd,\%mydbattr) or die $DBI::errstr;

my $cnt=$dbh->selectrow_hashref(qq{select max(weibo_content_id) as count from weibo_content_m03 where weibo_content_id>=$st});
say "Done to:$cnt->{count}";

while($st<$cnt->{count}){
 my $sth=$dbh->prepare("select weibo_content_id,origin_author_id,origin_author_name from weibo_content_m03 where weibo_content_id>=$st limit $offset;",{Slice => {}});
 $st+=$offset;
 $sth->execute;
 my $tst=time();
 while (my $rec=$sth->fetchrow_hashref){
  my $v=$rec->{origin_author_id};
  my $vn=$rec->{origin_author_name};
  $rs->{$v}->{cnt}++;
  $rs->{$v}->{name}=$vn;
 }
 my $est=time();
 say 'i have do to:'.$st;
 say 'this time it used(s):'.($est-$tst);
 say '-' x 30;
}

store($rs,$stfile);

END {
 $dbh->disconnect;
}

平均每次用时8s，共计用时21分钟，用top指令观察到其所使用的物理内存为10.8GB，负载平均在2.1，对mysql服务器几乎没有影响。
运行结束后，会在目录下生成一个Storable类型文件，大小：926M

现在就是程序来分析那些大小等于5次的数据了。
use v5.12;
use Storable;
use Time::Piece;
use Data::Dumper;

binmode(STDOUT, ":encoding(utf8)");
my ($stfile,$lastd,$currd,$rs)=('wm03.st');

#get storable variable
if(-e $stfile){
 $lastd=retrieve($stfile);
}

foreach my $uid(keys %$lastd){
 say qq["$uid","$lastd->{$uid}->{name}","$lastd->{$uid}->{cnt}"] if($lastd->{$uid}->{cnt}>=5);
}

time perl parsest.pl>pcontent.csv

脚本会将上页的结果文件载入内存，通过top指令可以看到占用全部内存的13%，用时4分钟计算出结果，其本身负载很小，共计3416284行。

两者不知道为什么会有十几条差异呢，应该是分组的字段登录名前或后有空格的关系吧，不过对于三白万的数据也算不了什么。当然，也可以不需要存入Storable文件来保存中间结果，直接分析内存的大hash数组就可以直接得到结果了。