关于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

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

Answer 2

开头加上:

set_time_limit(0);
ini_set('memory_limit', '1024M');

Answer 3

你的方法不对 ：

1.应该新建一个conrtab定时任务,分页每次插入1000条,直至250w 数据全部查完

Answer 4

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

Answer 5

我看了下你的代码，在for循环里面插入数据库，这部能不能批量插入呢？？？
另外250W数据真不是很大

Answer 6

crontab 每一分钟执行一次，执行的php逻辑中增加limit限制。

Answer 7

每次插入的数据减少一点 一次10000条还是太多了

Answer 8

因为代码里面$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 9

你这个其实直接用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 10

由于分表和插入数据实际上是两个行为，拆开来处理就行了。第一步，先确定基于主键的hash后应该分配到的表数据；第二步，根据之前分配好的策略来插入数据（这里数据插入的方法就很多样化了，可以基于回答1的方式，也可以自己写程序处理，另外必须跑在cli环境下）。

Answer 11

我感觉哈，这玩意按照正常思路来做出错，究其原因，无非就是得先把数据从数据库里查出来，然后进行处理，再连接数据库进行插入操作，之后才算完事。个人愚见，可以采用数据库长连接，然后封装成事务，然后重复提交事务。单单只是靠php的话，悬。。。