写一些脚本的心得总结系列第2篇-----历史数据迁移到分表

历史数据迁移到分表的。（以前单表几十G的表，需要做优化分表）

背景：

之前项目因为历史原因使用的是mssql，其中有大量的各种log表，需要完整地迁移到mysql的按照日期分表的分表里。由于数据量大和表当初设计的一些缺陷，所以在迁移的时候要考虑查询效率和执行效率问题。

我采用的方式如下：

每一个表一个function去处理.其实写这种导数据的应该对传入参数减少。
我当初写的是开始时间和天数的传入，然后根据时间戳算出连续时间的字符串，用来去生成对应的天表表面中的字符串。
其实后来想想，完全可以传入2个时间段，结束的时间可以不传，默认为执行该脚本当天。
代码如下：

public function moveGameClickLogToMySql()
{
    // 根据迁移日期查询数据(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 <br/>";

            $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");

以此作为一些凭证。