解决Codeigniter在CLI下执行占用内存过大问题

作者：袖梨 2022-06-25
 代码如下 复制代码
    public function import_users()
    {
       
        $members = $user = $user_contact = $user_ext = $user_last_active = $user_tag = array();
        $mid = 10000;
       
      
        $page_size = 3000;
        foreach(array(1,2,3,4,5) as $i)
        {
           
            //连接db
            $this->crm_db = $this->get_crm_db_handle();
            $this->db = get_db('default');
           
            $follow_table = 'follows_' . $i;
           
            $total = $this->db->count_all_results($follow_table);
            $page_total = ceil($total/$page_size);
           
            for($page = 1; $page < $page_total; $page++)
            {
                //连接db
                $this->crm_db = $this->get_crm_db_handle();
                $this->db = get_db('default');
 
                //获取会员
                $members = $this->db->select('*')
                                    ->from($follow_table)
                                    ->limit($page_size,($page-1)*$page_size)
                                    ->order_by('follow_id', 'asc')
                                    ->get()
                                    ->result_array();
               
              
                //开始导入
                foreach($members as $m)
                {
                    //导入到user表
                    if(empty($m['mid']))
                    {
                        $user = array(
                            'mid' => $mid,
                            'uid' =>$m['uid'],
                            'is_member' => 0,
                            'identify' => $m['telephone'],
                            'name' => $m['name'],
                            'nick' => $m['mark_name'],
                            'gender' => $m['sex'],
                            'update_time' => date('Y-m-d H:i:s', $m['create_time']),
                            'status' => 0
                        );
 
                        $this->crm_db->insert('user', $user);
 
                        //导入到user_contact表
                        if($m['qq'] OR $m['email'] OR $m['telephone'])
                        {
                            if($m['qq']) //2
                            {
                                $user_contact[] = array('uid'=>$m['uid'],'mid'=>$mid,'contact_id'=>2,'value'=>$m['qq']);
                            }
                            if($m['email']) //3
                            {
                                $user_contact[] =array('uid'=>$m['uid'],'mid'=>$mid,'contact_id'=>3,'value'=>$m['email']);
                            }
                            if($m['telephone']) //1
                            {
                                $user_contact[] = array('uid'=>$m['uid'],'mid'=>$mid,'contact_id'=>1,'value'=>$m['telephone']);
                            }
                            if($user_contact)
                            {
                                //$this->crm_db->insert_batch('user_contact', $user_contact);
                                foreach($user_contact as $uc)
                                {
                                    $this->crm_db->insert('user_contact', $uc);
                                }
                            }
                        }
 
                        //导入到user_ext表
                        if($m['avatar'] OR $m['city'] OR $m['province'] OR $m['country'])
                        {
                            $user_ext = array('uid'=>$m['uid'],'mid'=>$mid,'avatar'=>$m['avatar'], 'country'=>$m['country'],'province'=>$m['province'],'city'=>$m['city']);
                            $this->crm_db->insert('user_ext', $user_ext);
                        }
 
                        //导入到user_last_active表
                        if($m['last_talktime'] AND $m['talk_cnt'])
                        {
                            $user_last_active = array('uid'=>$m['uid'],'mid'=>$mid,'last_active_time'=>date('Y-m-d H:i:s',$m['last_talktime']), 'active_count'=>$m['talk_cnt']);
                            $this->crm_db->insert('user_last_active', $user_last_active);
                        }
 
                        //导入到user_tag表
                        $user_tags = $this->db->select('uid, gid as tag_id')->where(array('uid'=>$m['uid'], 'follow_id'=>$m['follow_id']))->get('fcate')->result_array();
                        if($user_tags)
                        {
                            foreach ($user_tags as $ut)
                            {
                                $ut['mid'] = $mid;
                                $this->crm_db->insert('user_tag', $ut);
                            }
                        }
 
                        //导入到platform表
                        $this->crm_db->insert('platform', array('uid'=>$m['uid'],'mid'=>$mid,'platform'=>$m['platform'],'platform_user_id'=>$m['unique'],'create_time'=>date('Y-m-d H:i:s', $m['create_time'])));
 
  www.111com.net
                        $mid++;
                        $user = $user_contact = $user_ext = $user_last_active = $user_tag = array();
                    }
                  
                }
               
                //关闭数据库
                $this->crm_db->close();
                $this->db->close();
                usleep(100000);
            }
        }
 
    }

CI db会将所有的查询sql和和sql执行时间保存下来，为了debug。关键是这个$save_queries为TRUE，在config中并没有关闭开关。
再看看我上面的执行脚本，400万的数据，每条记录会执行7条sql，也就是说，如果要执行完这个脚本，2800万条sql记录会被保存在内存中，MB，10G内存都不够。
解决办法：
当你执行大数量的db操作时，记得：

 代码如下 复制代码

$this->db->save_queries = FALSE;

相关文章

精彩推荐