You need to enable JavaScript to run this app.
优惠活动
大模型
产品
解决方案
定价
更多

Codeigniter 3海量数据查询优化求助：大账户列表查询超时

avatar
云上老周
2026-7-11
问题

使用Codeigniter 3编写的user_get_datatables函数，用于从80万行的文档数据库中拉取数据，查询依赖字符串数组accountCodesList。当数组较小时查询正常，但部分用户的数组包含8000+账户代码，导致or_where语句过长，出现超时和内存不足错误。尝试过where_in（因长度限制无法使用）、创建临时表、关联用户账户表等方法，均未解决性能问题，寻求优化方案。

原始实现代码 

function user_get_datatables($doctype, $accountCodesList, $role, $length, $start, $draw, $search, $order, $orderColumn, $orderDirection){
    ini_set('memory_limit', '1024M');
    try{
        

        $this->db->select('documents.id','documents.date_of_production','documents.account_code', 
        'documents.name','documents.policy_number','documents.doctype','documents.status','documents.file_link','documents.created_on','documents.updated_on')->from($this->documents_table);

        if($accountCodesList!==false){
            $this->db->group_start();
            foreach($accountCodesList as $item){
                $this->db->or_where('documents.account_code',$item);
            }
            $this->db->group_end();
        }

        if($doctype!==null || sizeof($doctype)>0){
            $this->db->group_start();
            foreach($doctype as $item){
                $this->db->or_where('documents.doctype',$item);
            }
            $this->db->group_end();
        }else{
            $this->db->where('documents.doctype',"false");
        }
        $this->db->where('documents.status',true);

        if($search != ""  && $search != null){
            $this->db->group_start();
            $this->db->or_like('documents.original_file_name', $search);
            $this->db->or_like('documents.account_code', $search);
            $this->db->or_like('documents.policy_number', $search);
            $this->db->group_end(); 
        }
        if($order != null){
            $this->db->order_by($this->user_column_order[$orderColumn], $orderDirection);
        } 
        else if(isset($this->order)){
            $order = $this->order;
            $this->db->order_by(key($order), $order[key($order)]);
        }

        // Apply LIMIT to the query
        if ($length != -1) {
            $this->db->limit($length, $start);
        }
        $query = $this->db->get();
        // echo $this->db->last_query();
        $res = $query->result();

        return array(
            'data' => $res,
            'isSuccessful' => true,
            'message' => "",
            'search' => $search,
            "length" =>  intval($length),
            "draw" => $draw,
            'start' => $start,
        );
    }
    catch(Exception $e){
        return array(
            'data' => array(),
            'isSuccessful' => false,
            'message' => 'An error occurred: ' . $e->getMessage(),
        );

    }
}

尝试过的临时表方案（耗时超3分钟） 

function user_get_datatables($doctype, $accountCodes, $role, $length, $start, $draw, $search, $order, $orderColumn, $orderDirection){
        ini_set('memory_limit', '1024M');
        try{
            $this->db->query("CREATE TEMPORARY TABLE temp_account_codes (account_code VARCHAR(255))");
            
            // Prepare the values for insertion
            $values = array_map(function($code) {
                return "('$code')";
            }, $accountCodes);
            // Insert data into the temporary table using a multi-row insert
            $insertValues = implode(', ', $values);
            $this->db->query("INSERT INTO temp_account_codes (account_code) VALUES $insertValues");

            // Retrieve all rows from the temporary table
            $this->db->select('*')
                ->from('temp_account_codes');
            $tempTableQuery = $this->db->get();
            $tempTableData = $tempTableQuery->result();

            echo json_encode($tempTableData);

            $this->db->select($this->user_column_order)
            ->from($this->documents_table)
            ->join('temp_account_codes', 'documents.account_code = temp_account_codes.account_code', 'inner');


            if($doctype!==null || sizeof($doctype)>0){
                $this->db->group_start();
                foreach($doctype as $item){
                    $this->db->or_where('documents.doctype',$item);
                }
                $this->db->group_end();
            }else{
                $this->db->where('documents.doctype',"false");
            }
            $this->db->where('documents.status',true);
            // $i = 0;

            if($search != ""  && $search != null){
                $this->db->group_start();
                $this->db->or_like('documents.original_file_name', $search);
                $this->db->or_like('documents.account_code', $search);
                $this->db->or_like('documents.policy_number', $search);
                $this->db->group_end(); 
            }
            if($order != null){
                $this->db->order_by($this->user_column_order[$orderColumn], $orderDirection);
            } 
            else if(isset($this->order)){
                $order = $this->order;
                $this->db->order_by(key($order), $order[key($order)]);
            }

            // Apply LIMIT to the query
            if ($length != -1) {
                $this->db->limit($length, $start);
            }
            $query = $this->db->get();
            echo $this->db->last_query();
            $res = $query->result();

            return array(
                'data' => $res,
                'isSuccessful' => true,
                'message' => "",
                'search' => $search,
                "length" =>  intval($length),
                "draw" => $draw,
                'start' => $start,
            );
        }
        catch(Exception $e){
            return array(
                'data' => array(),
                'isSuccessful' => false,
                'message' => 'An error occurred: ' . $e->getMessage(),
            );

        }
    }

尝试过的关联表查询方案（大量账户代码时速度慢） 

function user_get_datatables($doctype, $userId, $role, $length, $start, $draw, $search, $order, $orderColumn, $orderDirection){
        ini_set('memory_limit', '1024M');
        try{

            $this->db->select($this->user_column_order)
            ->from($this->documents_table)
            ->join('account_codes', 'documents.account_code = account_codes.account_code', 'inner')
            ->join('user_account_codes', 'account_codes.id = user_account_codes.account_code_id', 'inner');

            $this->db->where('user_account_codes.user_id',$userId);

            if($doctype!==null || sizeof($doctype)>0){
                $this->db->group_start();
                foreach($doctype as $item){
                    $this->db->or_where('documents.doctype',$item);
                }
                $this->db->group_end();
            }else{
                $this->db->where('documents.doctype',"false");
            }
            $this->db->where('documents.status',true);
            // $i = 0;

            if($search != ""  && $search != null){
                $this->db->group_start();
                $this->db->or_like('documents.original_file_name', $search);
                $this->db->or_like('documents.account_code', $search);
                $this->db->or_like('documents.policy_number', $search);
                $this->db->group_end(); 
            }
            if($order != null){
                $this->db->order_by($this->user_column_order[$orderColumn], $orderDirection);
            } 
            else if(isset($this->order)){
                $order = $this->order;
                $this->db->order_by(key($order), $order[key($order)]);
            }

            // Apply LIMIT to the query
            if ($length != -1) {
                $this->db->limit($length, $start);
            }
            $query = $this->db->get();
            //echo $this->db->last_query();
            $res = $query->result();

            return array(
                'data' => $res,
                'isSuccessful' => true,
                'message' => "",
                'search' => $search,
                "length" =>  intval($length),
                "draw" => $draw,
                'start' => $start,
            );
        }
        catch(Exception $e){
            return array(
                'data' => array(),
                'isSuccessful' => false,
                'message' => 'An error occurred: ' . $e->getMessage(),
            );

        }
    }
优化方案

1. 分批次使用WHERE IN查询

把8000+的账户代码分成多个小批次（比如每1000个一组），用WHERE IN结合OR组合条件，避免单条语句过长，同时比大量OR =更高效：

if($accountCodesList!==false){
    $this->db->group_start();
    // 分批次处理数组，每1000个一组
    $chunks = array_chunk($accountCodesList, 1000);
    foreach($chunks as $chunk){
        $this->db->or_where_in('documents.account_code', $chunk);
    }
    $this->db->group_end();
}

2. 优化临时表方案

之前的临时表慢是因为缺少索引，创建临时表时给account_code加主键索引，同时用Codeigniter的批量插入确保安全高效：

// 创建带主键索引的临时表
$this->db->query("CREATE TEMPORARY TABLE temp_account_codes (account_code VARCHAR(255) PRIMARY KEY) ENGINE=InnoDB");

// 用批量插入替代手动拼接SQL
$batchData = array_map(function($code){
    return ['account_code' => $code];
}, $accountCodes);
$this->db->insert_batch('temp_account_codes', $batchData);

// 关联查询时，临时表的索引会被高效利用
$this->db->join('temp_account_codes', 'documents.account_code = temp_account_codes.account_code', 'inner');

3. 给关联表加复合索引

关联表方案慢的核心原因是缺少合适的索引，给以下字段添加复合索引：

  • user_account_codes(user_id, account_code_id)：加速按用户ID筛选账户代码
  • account_codes(account_code, id)：加速关联documents表的account_code字段
  • documents(account_code, status, doctype)：覆盖查询中的过滤条件和关联字段，避免回表查询

添加后用EXPLAIN查看查询执行计划，确认索引是否被正确调用。

4. 全文索引替代LIKE模糊查询

原代码中的or_like会导致大表全表扫描，给original_file_name、account_code、policy_number创建全文索引，改用match()和against()优化：

if($search != ""  && $search != null){
    $this->db->group_start();
    $this->db->match('original_file_name, account_code, policy_number')
             ->against($search);
    $this->db->group_end(); 
}

注：MySQL默认全文索引仅支持英文，中文需安装ngram分词插件，或改用Elasticsearch等专业搜索引擎。

5. 键集分页替代偏移量分页

原代码的limit($length, $start)在大偏移量时性能极差，改用键集分页（基于上一页最后一条数据的排序字段）：

// 假设排序字段是documents.id，需前端传递上一页的最后一个id值
if($order != null){
    $sortField = $this->user_column_order[$orderColumn];
    $sortDir = $orderDirection;
    $this->db->order_by($sortField, $sortDir);
    
    if($start > 0 && isset($lastSortValue)){
        if($sortDir == 'asc'){
            $this->db->where("$sortField >", $lastSortValue);
        }else{
            $this->db->where("$sortField <", $lastSortValue);
        }
    }
}
$this->db->limit($length);

6. 缓存用户账户代码

如果用户的账户代码不频繁变动，将其缓存到Redis或Codeigniter文件缓存中，减少重复查询：

$cacheKey = 'user_account_codes_' . $userId;
$accountCodesList = $this->cache->get($cacheKey);

if(!$accountCodesList){
    $accountCodesList = $this->get_user_account_codes($userId);
    // 缓存1小时
    $this->cache->save($cacheKey, $accountCodesList, 3600);
}

7. 数据库配置调优

调整MySQL核心参数提升性能：

  • innodb_buffer_pool_size：设置为服务器内存的50%-70%，增强InnoDB缓存能力
  • max_allowed_packet：增大该值（如设置为64M），避免大查询包被拒绝
  • query_cache_size：MySQL 5.7及以下版本可开启查询缓存（注意缓存失效逻辑）

8. 精简查询返回字段

只返回前端需要的字段，减少数据传输和内存占用：

$this->db->select('documents.id, documents.account_code, documents.name, documents.file_link')
         ->from($this->documents_table);

内容的提问来源于stack exchange，提问作者JianYA

相关产品推荐
方舟 Agent Plan

超全模态模型 × Harness 升级，最新支持 Deepseek-V4.1-Flash、GLM-5.3 系列、Doubao-Seedream-5.0-pro、Kimi-K3 (部分)， 限时 9.9 元起

产品介绍相关文档
相关产品推荐
方舟 Agent Plan

超全模态模型 × Harness 升级，最新支持 Deepseek-V4.1-Flash、GLM-5.3 系列、Doubao-Seedream-5.0-pro、Kimi-K3 (部分)， 限时 9.9 元起

产品介绍相关文档
火山引擎 最新活动
全面开放
Doubao-Seedance-2.5全新模型升级

模型再升级,30秒超长叙事, 模态参考扩容

HOT
方舟Codingplan

模型自由，工具不限，最新支持 Deepseek-V4 系列、GLM-5.3 系列

HOT
方舟Agent plan

超全模态模型 × Harness 升级，最新支持 Deepseek-V4.1-Flash、GLM-5.3 系列

HOT
新用户特惠专场

大模型19元起，Al应用9.9元畅享，新人首购爆款尽享优惠

最近更新时间：2026.07.11 12:05:55