CodeIgniter使用order_by后分页查询变慢问题求助

我有一张约40万条数据的production_report表，要实现分页（每页100条）。使用CodeIgniter分页库时，添加order_by("pro_id", "DESC")降序排序后查询速度明显变慢；移除这条语句后页面加载恢复正常，已添加索引但无改善。

查询代码 #

function fetch_data($limit, $start)
{
    $this->db->select('pro_id,pro_date,st_target,st_smv,st_name,st_code,pro_account_level,pro_qty,mod_number,modet_desc,a_name,u_name');
    $this->db->from("production_report");
    $this->db->join('stages','stages.st_id = production_report.pro_stage_id');
    $this->db->join('model_details','model_details.modet_id = production_report.pro_model_id');
    $this->db->join('models','models.mod_id = model_details.modet_model_id');
    $this->db->join('accounts','accounts.a_id = production_report.pro_account_id');
    $this->db->join('users','users.u_id = production_report.pro_u_id');
    $this->db->order_by("pro_id", "DESC");
    $this->db->limit($limit, $start);

    $query = $this->db->get();
    if ($query->num_rows() > 0) {
        foreach ($query->result() as $row) {
            $data[] = $row;
        }

        return $data;
    }
    return false;
}  

索引信息 #

执行计划截图 #

1. 先分页排序，再关联查询
   当前查询是先关联所有表再排序分页，大表关联后的数据量会让排序成本陡增。改成先在production_report表中利用主键索引快速取出分页范围内的pro_id，再用这些pro_id关联其他表获取完整数据：

function fetch_data($limit, $start)
{
    // 第一步：获取分页对应的pro_id集合
    $sub_query = $this->db->select('pro_id')
                          ->from('production_report')
                          ->order_by('pro_id', 'DESC')
                          ->limit($limit, $start)
                          ->get_compiled_select();

    // 第二步：用pro_id关联其他表，仅查询需要的100条数据
    $this->db->select('pro_id,pro_date,st_target,st_smv,st_name,st_code,pro_account_level,pro_qty,mod_number,modet_desc,a_name,u_name');
    $this->db->from("production_report");
    $this->db->join('stages','stages.st_id = production_report.pro_stage_id');
    $this->db->join('model_details','model_details.modet_id = production_report.pro_model_id');
    $this->db->join('models','models.mod_id = model_details.modet_model_id');
    $this->db->join('accounts','accounts.a_id = production_report.pro_account_id');
    $this->db->join('users','users.u_id = production_report.pro_u_id');
    $this->db->where_in('pro_id', $sub_query);
    $this->db->order_by('pro_id', 'DESC');

    $query = $this->db->get();
    return $query->num_rows() > 0 ? $query->result_array() : false;
}

这种方式下排序操作只针对production_report的主键列，利用索引快速定位分页数据，关联其他表时仅处理100条数据，性能会大幅提升。

2. 补全关联表的索引
   确保所有关联表的关联字段都有索引：

• stages.st_id：设为主键或唯一索引
• model_details.modet_id、model_details.modet_model_id：添加BTREE索引
• models.mod_id：设为主键或唯一索引
• accounts.a_id：设为主键或唯一索引
• users.u_id：设为主键或唯一索引
  缺失这些索引会导致关联时全表扫描，直接拖慢查询速度。

3. 保持按需取字段
   你当前已经做到只查询需要的字段，继续保持，减少数据传输和内存占用。

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