解决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 { //连接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; |
|
