昨天，在使用codeigniter CLI下执行一个400w条的数据导入问题，一直出现内存过高，经过很我的分析我们得出来解决办法，下面大家一起来看看吧。

<?php
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'])
                    ));
                    $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;

本文地址：http://www.phprm.com/frame/60348.html

转载随意，但请附上文章地址:-)