Perfex CRM会计模块大数据量查询优化请求（附核心代码）

核心问题分析 #

原代码依赖count(*) = 0的子查询判断关联记录是否存在，会触发全表扫描；同时存在SQL注入风险，部分关联操作无实际意义，这些是15万+数据下耗时达5分钟的主要原因。

第一个函数（支付记录统计）优化 #

优化后代码 #

$where_payment = $this->get_where_report_period(db_prefix() . 'invoicepaymentrecords.date');
$this->db->select_sum('amount');

if ($where_payment != '') {
    $this->db->where($where_payment);
}

// 用NOT EXISTS替代count(*)子查询，提升存在性判断效率
$this->db->where("NOT EXISTS (
    SELECT 1 FROM " . db_prefix() . "acc_account_history 
    WHERE rel_id = " . db_prefix() . "invoicepaymentrecords.id 
    AND rel_type = 'payment'
)");
// 改用参数绑定避免SQL注入
$this->db->where('currency', $data_currency);

// 移除无意义的left join（未使用invoices表字段）
$payment = $this->db->get(db_prefix() . 'invoicepaymentrecords')->row();

优化点说明 #

• 替换count(*) = 0为NOT EXISTS：NOT EXISTS找到第一条匹配记录就终止查询，远快于count(*)遍历全表统计
• 移除冗余的left join：原代码关联invoices表但未使用其字段，减少表关联开销
• 改用参数绑定处理currency：避免SQL注入风险，同时让数据库更好地缓存执行计划

第二个函数（发票统计）优化 #

优化后代码 #

$this->db->select_sum('total');

if ($where != '') {
    $this->db->where($where);
}

// 用NOT EXISTS替代count(*)子查询
$this->db->where("NOT EXISTS (
    SELECT 1 FROM " . db_prefix() . "acc_account_history 
    WHERE rel_id = " . db_prefix() . "invoices.id 
    AND rel_type = 'invoice'
)");
// 参数绑定处理currency
$this->db->where('currency', $data_currency);

$invoice = $this->db->get(db_prefix() . 'invoices')->row();

优化点说明 #

• 用NOT EXISTS替代count(*) = 0，大幅提升关联记录存在性判断的效率
• 用参数绑定替换直接字符串拼接currency，规避SQL注入并优化执行计划缓存

数据库索引优化建议 #

为进一步提升查询速度，需添加以下索引：

• 给acc_account_history表添加复合索引：rel_type, rel_id
  • 理由：NOT EXISTS子查询会频繁按rel_type和rel_id过滤，复合索引能直接命中查询条件
• 给invoicepaymentrecords表添加索引：date, currency
• 给invoices表添加索引：currency（若原where包含日期范围，可添加date, currency复合索引）

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