历史数据迁移到分表的。(以前单表几十G的表,需要做优化分表)
背景:
之前项目因为历史原因使用的是mssql,其中有大量的各种log表,需要完整地迁移到mysql的按照日期分表的分表里。由于数据量大和表当初设计的一些缺陷,所以在迁移的时候要考虑查询效率和执行效率问题。
我采用的方式如下:
每一个表一个function去处理.其实写这种导数据的应该对传入参数减少。
我当初写的是开始时间和天数的传入,然后根据时间戳算出连续时间的字符串,用来去生成对应的天表表面中的字符串。
其实后来想想,完全可以传入2个时间段,结束的时间可以不传,默认为执行该脚本当天。
代码如下:
public functionmoveGameClickLogToMySql()
{//根据迁移日期查询数据(from mssql)
$startDate = $this->input->get_post('startTime');if (!$startDay) exit('请输入开始日期');$endDate = $this->input->get_post('endTime');$dateArray = $this->getSqeDate($startDate, $endDate);$upTime = $this->input->get_post('upTime'); //上线时间 ,时间戳
$upDate = date('Ymd', $upTime);//tGameClickLog
set_time_limit(0);$searchDate = array();foreach ($dateArray as $d) {array_push($searchDate, date('Y-m-d H:i:s', strtotime($d)));
}$mysql = $this->load->database('mysqllog', true);foreach ($searchDate as $day) {$startTime = $day;$endTime = date('Y-m-d 23:59:59', strtotime($day) + 1);$sqlStart = "select min(id) as start_id from someClick where dtInsert>='{$startTime}'";//" and dtInsert<= '{$endTime}'";
$sqlEnd = "select min(id) as end_id from tGameClickLog where dtInsert>='{$endTime}'";$startRes = $this->mssql->getOne($sqlStart);$endRes = $this->mssql->getOne($sqlEnd);if ($startRes && $endRes) {$end_id = $endRes['end_id'] - 1;$start_id = $startRes['start_id'];echo "coming in
";$oneDaySql = "select * from tGameClickLog where id >= {$startRes['start_id']} and id <= {$end_id} ";$countSql = "select count(id) as count from someClick where id >= {$startRes['start_id']} and id <= {$end_id}";$count = $this->mssql->getOne($countSql);$countNum = $count['count'];//var_dump($count);die();
// 每10000条查询一次
$times = ceil($countNum / 10000);//插入mysql
$d = date('Ymd', strtotime($day));$tableName = 'game_click_log_' . $d;//先清空原来该分表数据
$where = '';if ($upDate == $d) $where = ' where created <=' . $upTime;$mysql->query('delete from ' . $tableName . $where);for ($i = 1; $i <= $times; $i++) {$insertData = array();//分批查询和插入
$partData = $this->mssql->select($oneDaySql, array(), $i, 10000);foreach ($partData as $p) {//特殊处理原表中的插入时间
$created = (array)$p['dtInsert'];$created = strtotime($created['date']);$insertData[] = array('netbar_id' => $p['iUserID'],
'game_id' => $p['iGameID'],
'click' => $p['iClick'],
//some other colums
);
}$allData = array_chunk($insertData, 1000); //分批插入
$num = count($allData);for ($n = 0; $n < $num; $n++) {$mysql->insert_batch($tableName, $allData[$n]);
}
}
}else{continue;
}var_dump('finish insert ' . $d . " data");
}
}
整个代码中其实大致总结一下步骤:
1.处理开始时间和结束时间,先生成连续时间字符串数组,比如我传入 2015-11-12,第二个参数结束日期不传。
那么通过getSqeDate函数可以生成
array(
'2015-11-12',
'2015-11-13',
'2015-11-14',
.........
)
2.因为考虑到上线后才执行迁移数据的脚本,所以要考虑先清空原来该分表数据。因为后面会走mysql,所以就不去读上线之后mssql的表中数据,防止出现重复冗余数据。
3.由于数据量太大,且mssql的日期是字符串没有索引,所以还不能直接用日期来做查询把某天的数据直接弄出来。曲线救国,先根据开始时间和结束时间获取当天数据中最大的主键id和最小的主键id,然后再根据这两个id作为条件查询出整天的数据。
大家看到这儿是不是就舒了一口气,然并卵。因为数据量较大,比如游戏点击日志,一天的记录就上亿,一次性取出和一次性插入也是难事。所以还得分批取出来,分批插入到mysql.
分批查询关键代码如下:
$count = $this->mssql->getOne($countSql);$countNum = $count['count'];//var_dump($count);die();
// 每10000条查询一次
$times = ceil($countNum / 10000);for ($i = 1; $i <= $times; $i++) {$insertData = array();//分批查询和插入
$partData = $this->mssql->select($oneDaySql, array(), $i, 10000);
// some other codes .......$allData = array_chunk($insertData, 1000); //分批插入
$num = count($allData);for ($n = 0; $n< $num; $n++) {$mysql->insert_batch($tableName , $allData[$n]);
}//other codes
}
每一天的跑完之后会输出提示信息。
其实这里可以做一些日志记录,比如利用error_log,类似:
error_log(date('H:i:s') . " 同步统计数据到mysql游戏点击表gameClick_{$date}_log \t",3, "./application/logs/SyncGameClick.log");
以此作为一些凭证。