关于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来实现数据处理。个人想法，不足之处请谅解。