关于php处理Mysql特大数据表的解决方案

Question

1.目前有一张商品表，由于商品表数据超过250W 条，而且根据需求还会继续激增，所以考虑进行分表设计。
2.由于分表采用主键字段哈希分表,故需要将现有的250W 条数据进行hash处理分别插入到分出的10张表中，但是由于数据量太大，使用foreach来进行批量插入的时候每次都会超时。
3.哪位大神有使用Php处理大数据的经验，这种情况应该怎么处理。以下是我处理的代码：

   for($i=0;$i<250;$i++){
        $offset = $i*10000;
        $where = array('where' => ' id>0',
            'limit' => '10000',
            'offset'=>$offset,
            'fields'=>['goods_id','goods_name','goods_price']);
        $res = $goodsModel->select($where );
        foreach($res as $val){
            //根据主键id进行hash获取表名
            $tab_name = 'goods_'. getStringHash($val['goods_id']);
            $data[$tab_name][] = $val;

        }

        foreach($data as $key=>$val){
            $sql = "insert into {$key} ";
            $sql_val_str = '';
            foreach($val as $v){
                $sql_key = array_keys($v);
                $sql_val = array_values($v);
                $sql_val_str .= '(';
                foreach ($sql_val as $item) {
                    $sql_val_str .= "'".$item."'";
                    $sql_val_str .= ',';
                }
                $sql_val_str = substr($sql_val_str, 0, -1);
                $sql_val_str .='),';
            }
            $sql_key_str = '('.implode(',',$sql_key).')';
            $sql_val_str = substr($sql_val_str, 0, -1);
            $sql .= $sql_key_str . 'values' . $sql_val_str;

            $goodsModel->exec_sql($sql);
        }
    }
    
    goods ：id  商品码      商品编号 商品价格
            1  H235KHAK    123456    123465
    主要结构就是这样的，其中商品码是不允许重复的，而且是10位数字和字母结合的随意字符
    分表后是根据商品码进行hash计算，获取hash值（0-9） 最后实现的结果是：goods_0,goods_1,goods_2....goods_9 将数据分布插入到这些表中
    
    hash算法是在网上找的一个：
    
    function getStringHash($string, $tab_count=10)
{/*{{{*/
$unsign = sprintf('%u', crc32($string));
if ($unsign > 2147483647)  // sprintf u for 64 & 32 bit
{
    $unsign -= 4294967296;
}
return abs($unsign) % $tab_count;

}

Answer 1

因为代码里面$data数组随着循环次数增加，保存了库中所有的记录， 所以这不是set_time_limit和memory_limit太小的问题，需要优化代码， 可以在第二个foreach后面增加unset($data)释放内存。 然后这个查询子句limit的offset会随着循环次数增加，变得很大，后面的SQL查询肯定会很慢， 如果主键id是连续的，可以考虑使用where id in (....)来获取数据。 每次返回10000条记录占用内存也很大，$goodsModel可以使用yield来降低内存使用。 另外，还有更简单的 分表方式，直接在数据库中写SQL就行。 CREATE TABLE goods_1 CREATE TABLE goods_2 ... CREATE TABLE goods_10 .. INSERT INTO goods_1 SELECT * FROM goods WHERE id MOD 250000 = 0 INSERT INTO goods_2 SELECT * FROM goods WHERE id MOD 250000 = 1 ... INSERT INTO goods_10 SELECT * FROM goods WHERE id MOD 250000 = 9 分表以后之前的代码也许都需要改，这点也是需要考虑的，如果改代码代价很大，可以实施分区(PARTITION)策略。

Answer 2

你这个其实直接用mysql来做可能要快太多，你这个用的hash处理的，你直接可以 Insert into Table2(field1,field2,...) select value1,value2,... from Table1 后续按照你hash的加个条件。简直不要太快。 以下为答案补充 其实，我这里不是很清楚你这个hash是怎么样的一个算法，但是，我就假设你现在是基于这个商品ID来处理数据的；那么假设你的，如果是自定义的hash的话，就还需要使用mysql的存储过程了。以下我以一个测试表俩举例子 CREATE TABLE `test` ( `id` int(11) NOT NULL AUTO_INCREMENT, `value` int(11) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8; # 以上test表假设是原表，然后新创建0~9十个表，那么以下10条sql可以直接把原表数据快速拆分放到新的10个表 INSERT INTO test_0(value) SELECT `value` FROM test WHERE id % 10 = 0; INSERT INTO test_1(value) SELECT `value` FROM test WHERE id % 10 = 1; INSERT INTO test_2(value) SELECT `value` FROM test WHERE id % 10 = 2; INSERT INTO test_3(value) SELECT `value` FROM test WHERE id % 10 = 3; INSERT INTO test_4(value) SELECT `value` FROM test WHERE id % 10 = 4; INSERT INTO test_5(value) SELECT `value` FROM test WHERE id % 10 = 5; INSERT INTO test_6(value) SELECT `value` FROM test WHERE id % 10 = 6; INSERT INTO test_7(value) SELECT `value` FROM test WHERE id % 10 = 7; INSERT INTO test_8(value) SELECT `value` FROM test WHERE id % 10 = 8; INSERT INTO test_9(value) SELECT `value` FROM test WHERE id % 10 = 9; 我好像没看到你的hash是怎么实现的，如果知道的话，我也可以做个测试的来模拟，如果是hash商品编码的话，需要用到存储过程，相对来说又会复杂了一些。

Answer 3

有过类似经验，你可以了解一下 swoole 异步任务，你250万的数据，根据ID区间来划分，开启25个task进程,投递25个task任务，每个进程也才10W的数据，非常快就能执行完成,并且在cli命令行环境也不存在超时的问题。

Answer 4

我的想法是新产生的数据就按你分库分表规则处理，已有数据能不动就不动了。如果已产生的数据一定要拆分，对PHP单独处理数据而言这个数据量太大了，用PHP做多层嵌套循环，效率低，易超时。。。看能不能考虑用python来实现数据处理。个人想法，不足之处请谅解。