mysql 单表500万数据经过处理后新增到新表

Question

1 .原表500万数据;需要从原表中取出字段('name'或者'content') 中的 中文 转换为 拼音全拼 和 拼音缩写 然后插入到新表;
name字段的数据不全是中文;包含(html,其他字符,可能很长,一般都是40个中文以内,有重复的)
2 .方案: 定时任务->获取固定数量的数据->处理完后->批量插入新表
3 .测试数据(10000条记录)的结果: 10000
查询耗时：0.023秒
循环耗时：612.662秒
插入耗时：0.022秒
内存消耗：9.61 M
4 . thinkphp5框架 代码:

{
        set_time_limit(0);
        $t0 = microtime(true);
        $content = Db::name('scene_data')->where(['type'=>0, 'status'=>1])->order('id')->limit(10000)->page($page)->field('content')->select();

        $count = count($content);
        $pinyin = new Pinyin();
        $t1 = microtime(true);
        echo '查询耗时：'.round($t1-$t0,3).'秒<br>';
        $array = null;
        $time = time();
        $type = 0;
        for ($i=0; $i < $count; $i++){
            $arr = [];
            $name = $this->newName($content[$i]['content']);
            if ($name) {
                if (strlen($name) > 120) {
                    continue;
                }

                $arr['name']       = $name;
                $firstWord         = substr($name, 0, 3);
                $arr['first_word'] = $pinyin->abbr($firstWord);
                $arr['short_word'] = $pinyin->abbr($name);
                $arr['pinyin']     = $pinyin->permalink($name);
                
                $array[] = "('".$arr['name']."','".$arr['first_word']."','".$arr['short_word']."','".$arr['pinyin']."','".$type."','".$time."')";
                
            } else {
                continue;
            }
        }
        $array = array_unique($array);
        $array = implode(',', $array);
        //程序运行时间
        $t2 = microtime(true);
        echo '循环耗时：'.round($t2-$t1,3).'秒<br>';

        $sql = "REPLACE INTO `cd_pinyin` ( `name`, `first_word`, `short_word`, `pinyin`, `type` , `create_time`) VALUES " . $array;
        $res = Db::execute($sql);

        $t3 = microtime(true);
        echo '插入耗时：'.round($t3-$t2,3).'秒<br>';
        echo '内存消耗：'.round(memory_get_usage()/1048576,2)." M<br/>";
        return $res ? [RESULT_SUCCESS, '操作成功'] : [RESULT_ERROR, Db::name('pinyin')->getError()];
    }
    public function newName($str)
    {
        if ($str) {
            // 是否存在非中文符号
            $res = preg_match('/[^\x{4e00}-\x{9fa5}]/u', $str);
            if($res) {
                $str = preg_replace('/[^\x{4e00}-\x{9fa5}]/u', '', $str);
            }
   
            return $str;
        }
    }

5 .目前的测试结果来看大概需要83.3小时的时间处理完;请教各位更好的方案;(主要集中在对数据的处理上)

Answer 1

其实你的时间消耗主要在拼音转换。对于thinkphp5，你可以把500万的数据处理分摊到多个队列里进行处理。比如你跑10个处理队列，每个队列处理50万数据，50万数据处理时间按你现有的速度的话就是8个小时。
另外，array_unique不要处理太多数据，去重查询复杂度是O(n^2),可以用关联数组，插入的时候判断key是否存在，查询复杂度是O（1）

Answer 2

1、去重操作能否再循环内进行；
2、那个Pinyin类能否优化，消耗时间都是花费在这里。