使用perl拆分表并迁移数据

随着业务量的增长，可能需要对表进行拆分来提高性能。
下面这个例子是将www.94ip.com的users表拆分成10个表94ip_user_0-94ip_user_9。
拆分迁移数据程序如下所示：
1.创建94ip_user_0-94ip_user_9表
[codesyntax lang="perl"]
#!/usr/bin/perl
###################################
### author: www.94ip.com ###
### QQ：33790985 ###
### E-mail：wangyun@94ip.com ###
###################################

use DBI;

my $driver="DBI:mysql";
my $from_database="94ip";
my $from_user="root";
my $from_password="123456";
my $from_host="localhost";

$from_dbh=DBI->connect ("$driver:$from_database:$from_host;user=$from_user;password=$from_password") or die "cannot connect: ". DBI->errstr;

for (0..9) {
$sql="CREATE TABLE 94ip_user_$_ (
uid int(10) NOT NULL AUTO_INCREMENT,
email varchar(50) NOT NULL,
passwd varchar(40) NOT NULL,
user_name varchar(20) NOT NULL,
PRIMARY KEY (uid),
UNIQUE KEY email (email),
UNIQUE KEY user_name (user_name)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=COMPACT";
$from_dbh->do($sql);
}
$from_dbh->disconnect();
[/codesyntax]

2.迁移数据
[codesyntax]
#!/usr/bin/perl
###################################
### author: www.94ip.com ###
### QQ：33790985 ###
### E-mail：wangyun@94ip.com ###
###################################

use DBI;

my $driver="DBI:mysql";
my $from_database="94ip";
my $from_user="root";
my $from_password="123456";
my $from_host="localhost";

$from_dbh=DBI->connect ("$driver:$from_database:$from_host;user=$from_user;password=$from_password") or die "cannot connect: ". DBI->errstr;
$from_dbh->do("set names 'utf8'");
$from_dbh->do("set session autocommit=0");
$from_dbh->do("set session unique_checks=0");

$max=600000;
$step=10000;
for ($i=1;$i $step_i=$i+$step;
$sql="select * from 94ip_users where user_id>=$i and user_id $sth=$from_dbh->prepare($sql);
$sth->execute;
@value=();
while ($row=$sth->fetchrow_hashref()) {
$uid=$row->{uid};
$email=$row->{email};
$passwd=$row->{passwd};
$user_name=$row->{user_name};
$key=substr($user_id,-1);
$value[$key].="('$uid','$email','$passwd','$user_name'),";

}
for (0..9) {
chop($value[$_]);
$sql="insert into 94ip_user_$_ (uid,email,passwd,user_name) values" . $value[$_];
$from_dbh->do("$sql");
}
}
$from_dbh->do("set session autocommit=1");
$from_dbh->do("set session unique_checks=1");
$sth->finish();
$from_dbh->disconnect();
[/codesyntax]

3.附录
如果没有那么多数据量，可以随意插入一些数据进行测试。下面提供一个插入数据的脚本。
[codesyntax]
#!/usr/bin/perl
###################################
### author: www.94ip.com ###
### QQ：33790985 ###
### E-mail：wangyun@94ip.com ###
###################################

use DBI;
use Digest::SHA qw(sha1_hex);

my $driver="DBI:mysql";
my $from_database="94ip";
my $from_user="root";
my $from_password="123456";
my $from_host="localhost";

$from_dbh=DBI->connect ("$driver:$from_database:$from_host;user=$from_user;password=$from_password") or die "cannot connect: ". DBI->errstr;
$from_dbh->do("set names 'utf8'");
$from_dbh->do("set session autocommit=0");
$from_dbh->do("set session unique_checks=0");

for (1..100000) {
$insert_sql=$from_dbh->prepare("insert into 94ip_users (email,passwd,user_name) values (?,?,?)");
$email="auto-gre-$_\@94ip.com";
$data="auto-gre-$_";
$passwd=sha1_hex($data);
$user_name="auto-gre-$_";
$insert_sql->execute($email,$passwd,$user_name);
}

$from_dbh->do("set session autocommit=1");
$from_dbh->do("set session unique_checks=1");
$insert_sql->finish();
$from_dbh->disconnect();
[/codesyntax]