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

CodeIgniter下DataTables数据加载与搜索速度优化求助

avatar
AI 小北
2026-7-4
CodeIgniter + DataTables 数据检索优化方案

原Model代码 

public function get_adjustment($group_id, $start, $length, $search, $order_by, $order_dir)
{
    $this->db->select('uni.inv_id, batch.batch_id, uni.item_code, des.design_name, cat.cat_type, supply.item_size, des.gender_status, uni.quantity, batch.b_remain_qty, batch.batch_code');
    $this->db->from('ebs.uni_inventory uni');
    $this->db->join('ebs.uni_batch batch', 'batch.group_id = uni.group_id AND batch.item_code = uni.item_code', 'inner');
    $this->db->join('ebs.uni_stocksupply_v2 supply', 'supply.item_type = uni.item_type AND supply.item_design = uni.item_design AND supply.item_code = uni.item_code AND supply.item_code = batch.item_code', 'inner');
    $this->db->join('ebs.uni_stockdesign_v2 des', 'des.design_type = uni.item_type AND des.design_id = uni.item_design', 'inner');
    $this->db->join('ebs.uni_stockcategory_v2 cat', 'cat.stock_id = uni.item_type', 'inner');
    $this->db->where('uni.status', 'active');
    $this->db->where('uni.group_id', $group_id);
    $this->db->order_by($order_by, $order_dir);
    $this->db->limit($length, $start);

    if (!empty($search)) {
        $this->db->group_start();
        $this->db->like('uni.item_code', $search);
        $this->db->or_like('des.design_name', $search);
        $this->db->or_like('cat.cat_type', $search);
        $this->db->or_like('supply.item_size', $search);
        $this->db->or_like('des.gender_status', $search);
        $this->db->or_like('uni.quantity', $search);
        $this->db->or_like('batch.b_remain_qty', $search);
        $this->db->or_like('batch.batch_code', $search);
        $this->db->group_end();
    }
    $result = $this->db->get();
    return $result->result_array();
}

public function getTotalRecords($group_id, $search)
{
    $this->db->select('*');
    $this->db->from('ebs.uni_inventory uni');
    $this->db->join('ebs.uni_batch batch', 'batch.group_id = uni.group_id AND batch.item_code = uni.item_code', 'inner');
    $this->db->join('ebs.uni_stocksupply_v2 supply', 'supply.item_type = uni.item_type AND supply.item_design = uni.item_design AND supply.item_code = uni.item_code AND supply.item_code = batch.item_code', 'inner');
    $this->db->join('ebs.uni_stockdesign_v2 des', 'des.design_type = uni.item_type AND des.design_id = uni.item_design', 'inner');
    $this->db->join('ebs.uni_stockcategory_v2 cat', 'cat.stock_id = uni.item_type', 'inner');
    $this->db->where('uni.status', 'active');
    $this->db->where('uni.group_id', $group_id);

    // $this->db->where('uni.quantity != batch.b_remain_qty');

    if (!empty($search)) {
        $this->db->group_start();
        $this->db->like('uni.item_code', $search);
        $this->db->or_like('des.design_name', $search);
        $this->db->or_like('cat.cat_type', $search);
        $this->db->or_like('supply.item_size', $search);
        $this->db->or_like('des.gender_status', $search);
        $this->db->or_like('uni.quantity', $search);
        $this->db->or_like('batch.b_remain_qty', $search);
        $this->db->or_like('batch.batch_code', $search);
        $this->db->group_end();
    }
    // return $this->db->get()->row()->total_records;
    $query = $this->db->get();
    return count($query->result_array());
}

原Controller代码 

public function get_inventory_data()
{
    $group_id   = $this->input->post('group_id');
    $start      = $this->input->post('start');
    $length     = $this->input->post('length');
    $search     = $this->input->post('search')['value'];
    // $column     = $this->input->post('order')[0]['column'];
    // $order_dir  = $this->input->post('order')[0]['dir'];

    // $columns = ['inv_id', 'batch_id', 'item_code', 'design_name', 'cat_type', 'item_size', 'gender_status', 'quantity', 'b_remain_qty'];
    // $order_by = $columns[$column];
    $order_by   = $this->input->post('columns')[$this->input->post('order')[0]['column']]['data'];
    $order_dir  = $this->input->post('order')[0]['dir'];

    $columns = ['inv_id', 'batch_id', 'item_code', 'design_name', 'cat_type', 'item_size', 'gender_status', 'quantity', 'b_remain_qty'];

    if (empty($order_by) || !in_array($order_by, $columns)) {
        $order_by = $columns[0];
    }

    $data = $this->AdjustmentModel->get_adjustment($group_id, $start, $length, $search, $order_by, $order_dir);

    $totalRecords = $this->AdjustmentModel->getTotalRecords($group_id, $search);

    $data_output = array(
        'draw' => intval($this->input->post('draw')),
        'recordsTotal' => intval($totalRecords),
        'recordsFiltered' => intval($totalRecords),
        'data' => $data,
    );

    echo json_encode($data_output);
}

优化方案

1. 数据库层面优化

1.1 添加针对性索引

针对查询中频繁使用的过滤、关联、排序字段创建复合/单独索引，直接降低数据库查询耗时：

  • uni_inventory表：创建复合索引idx_uni_group_status（group_id, status），同时为item_code、inv_id单独加索引
  • uni_batch表：创建复合索引idx_batch_group_item（group_id, item_code），为batch_code单独加索引
  • 关联字段：uni_stocksupply_v2的item_type、item_design、item_code；uni_stockdesign_v2的design_type、design_id；uni_stockcategory_v2的stock_id均需单独加索引
  • 搜索字段：对des.design_name、cat.cat_type添加全文索引（MySQL 5.6+支持），替代低效的like模糊查询

1.2 优化计数查询逻辑

原getTotalRecords查询所有字段再计数，改为直接统计唯一主键数量，避免加载冗余数据：

public function getTotalRecords($group_id, $search)
{
    $this->db->select('COUNT(DISTINCT uni.inv_id) as total');
    $this->db->from('ebs.uni_inventory uni');
    $this->db->join('ebs.uni_batch batch', 'batch.group_id = uni.group_id AND batch.item_code = uni.item_code', 'inner');
    $this->db->join('ebs.uni_stocksupply_v2 supply', 'supply.item_type = uni.item_type AND supply.item_design = uni.item_design AND supply.item_code = uni.item_code AND supply.item_code = batch.item_code', 'inner');
    $this->db->join('ebs.uni_stockdesign_v2 des', 'des.design_type = uni.item_type AND des.design_id = uni.item_design', 'inner');
    $this->db->join('ebs.uni_stockcategory_v2 cat', 'cat.stock_id = uni.item_type', 'inner');
    $this->db->where('uni.status', 'active');
    $this->db->where('uni.group_id', $group_id);

    if (!empty($search)) {
        $this->db->group_start();
        $this->db->like('uni.item_code', $search);
        $this->db->or_like('des.design_name', $search);
        $this->db->or_like('cat.cat_type', $search);
        $this->db->or_like('supply.item_size', $search);
        $this->db->or_like('des.gender_status', $search);
        $this->db->or_where('CAST(uni.quantity AS CHAR) LIKE', "%{$search}%");
        $this->db->or_where('CAST(batch.b_remain_qty AS CHAR) LIKE', "%{$search}%");
        $this->db->or_like('batch.batch_code', $search);
        $this->db->group_end();
    }
    $query = $this->db->get();
    return (int)$query->row()->total;
}

2. Model代码复用与逻辑优化

2.1 提取公共查询逻辑

将两个方法中重复的关联、过滤、搜索逻辑提取为私有方法，减少冗余，便于后续维护：

private function _buildBaseQuery($group_id, $search)
{
    $this->db->from('ebs.uni_inventory uni');
    $this->db->join('ebs.uni_batch batch', 'batch.group_id = uni.group_id AND batch.item_code = uni.item_code', 'inner');
    $this->db->join('ebs.uni_stocksupply_v2 supply', 'supply.item_type = uni.item_type AND supply.item_design = uni.item_design AND supply.item_code = uni.item_code AND supply.item_code = batch.item_code', 'inner');
    $this->db->join('ebs.uni_stockdesign_v2 des', 'des.design_type = uni.item_type AND des.design_id = uni.item_design', 'inner');
    $this->db->join('ebs.uni_stockcategory_v2 cat', 'cat.stock_id = uni.item_type', 'inner');
    $this->db->where('uni.status', 'active');
    $this->db->where('uni.group_id', $group_id);

    if (!empty($search)) {
        $this->db->group_start();
        $this->db->like('uni.item_code', $search);
        $this->db->or_like('des.design_name', $search);
        $this->db->or_like('cat.cat_type', $search);
        $this->db->or_like('supply.item_size', $search);
        $this->db->or_like('des.gender_status', $search);
        $this->db->or_where('CAST(uni.quantity AS CHAR) LIKE', "%{$search}%");
        $this->db->or_where('CAST(batch.b_remain_qty AS CHAR) LIKE', "%{$search}%");
        $this->db->or_like('batch.batch_code', $search);
        $this->db->group_end();
    }
}

public function get_adjustment($group_id, $start, $length, $search, $order_by, $order_dir)
{
    $this->db->select('uni.inv_id, batch.batch_id, uni.item_code, des.design_name, cat.cat_type, supply.item_size, des.gender_status, uni.quantity, batch.b_remain_qty, batch.batch_code');
    $this->_buildBaseQuery($group_id, $search);
    $this->db->order_by($order_by, $order_dir);
    $this->db->limit($length, $start);
    $result = $this->db->get();
    return $result->result_array();
}

public function getTotalRecords($group_id, $search)
{
    $this->db->select('COUNT(DISTINCT uni.inv_id) as total');
    $this->_buildBaseQuery($group_id, $search);
    $query = $this->db->get();
    return (int)$query->row()->total;
}

2.2 优化搜索逻辑

  • 对数字类型字段（如uni.quantity、batch.b_remain_qty），避免直接用like，改用CAST转换为字符串后再模糊匹配，或提供精确搜索选项
  • 添加搜索长度限制，比如搜索字符少于2位时不执行模糊查询，减少无效数据库请求

3. Controller参数规范与响应优化

修正recordsTotal和recordsFiltered的取值逻辑，符合DataTables官方规范，同时加强参数验证：

public function get_inventory_data()
{
    $group_id = $this->input->post('group_id', true);
    $start = (int)$this->input->post('start', true) ?: 0;
    $length = (int)$this->input->post('length', true) ?: 10;
    $search = trim($this->input->post('search')['value'] ?? '');
    $draw = (int)$this->input->post('draw', true) ?: 1;

    // 排序参数安全处理
    $order_col = (int)$this->input->post('order')[0]['column'] ?? 0;
    $order_dir = strtolower($this->input->post('order')[0]['dir'] ?? 'asc');
    $order_dir = in_array($order_dir, ['asc', 'desc']) ? $order_dir : 'asc';

    $columns = ['inv_id', 'batch_id', 'item_code', 'design_name', 'cat_type', 'item_size', 'gender_status', 'quantity', 'b_remain_qty'];
    $order_by = $columns[$order_col] ?? $columns[0];

    // 获取数据与计数
    $data = $this->AdjustmentModel->get_adjustment($group_id, $start, $length, $search, $order_by, $order_dir);
    $totalFiltered = $this->AdjustmentModel->getTotalRecords($group_id, $search);
    $totalRecords = $this->AdjustmentModel->getTotalRecords($group_id, '');

    $data_output = array(
        'draw' => $draw,
        'recordsTotal' => $totalRecords,
        'recordsFiltered' => $totalFiltered,
        'data' => $data,
    );

    // 规范JSON响应输出
    $this->output
        ->set_content_type('application/json')
        ->set_output(json_encode($data_output));
}

4. 前端与缓存优化

  • 给DataTables添加搜索防抖，延迟300ms再发送请求，减少频繁查询
  • 开启DataTables的cache: true配置，缓存分页结果
  • 若数据非实时更新，可通过Redis缓存group_id对应的查询结果，有效期设为5-10分钟，大幅降低数据库压力

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

相关产品推荐
方舟 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.04 21:30:53