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

历史数据迁移到分表的。(以前单表几十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");

以此作为一些凭证。