Laravel/Lumen Eloquent生成SQL在InnoDB中条件增多后无限卡顿

以下是针对该问题的具体排查方向和优化建议：

1. 先确认Eloquent生成的原始SQL #

多对多关联叠加多个过滤条件时，Eloquent可能生成包含多次JOIN的复杂SQL，甚至触发笛卡尔积导致结果集爆炸。你可以通过两种方式获取完整SQL：

• 在查询构建器末尾调用->toSql()直接输出语句；
• 开启Laravel查询日志（DB::enableQueryLog()），执行查询后打印DB::getQueryLog()。
  将生成的SQL拿到MySQL客户端执行，能快速定位是SQL逻辑问题还是数据库资源瓶颈。

2. 排查查询逻辑中的笛卡尔积风险 #

从参数来看，你同时关联了groups1和groups2（推测是同一个groups表的两次关联），再加上多个IN/OR条件，极易导致JOIN后的中间结果集行数呈指数级增长。比如一条qualification关联多个groups1条目，同时关联多个groups2条目，JOIN后会生成两者的组合数，数据量瞬间膨胀。

这种情况下，建议把基于JOIN的关联条件改成EXISTS子查询，避免笛卡尔积：

WHERE EXISTS (
    SELECT 1 FROM qualification_group qg 
    JOIN groups g ON qg.group_id = g.id 
    WHERE qg.qualification_id = qualifications.id 
    AND g.name_pl = 'NAUKI ŚCISŁE I PRZYRODNICZE'
)
AND EXISTS (
    SELECT 1 FROM qualification_group qg 
    JOIN groups g ON qg.group_id = g.id 
    WHERE qg.qualification_id = qualifications.id 
    AND g.name_pl IN ('Geografia', 'geologia', 'geofizyka')
)

EXISTS会直接判断关联条件是否成立，不会生成冗余的组合数据。

3. 检查MySQL资源配置与临时表使用 #

即便索引合理，大结果集也可能耗尽内存触发磁盘临时表，导致性能暴跌：

• 执行SHOW STATUS LIKE 'Created_tmp_disk_tables';，如果数值持续飙升，说明大量用到了磁盘临时表；
• 检查关键MySQL配置：
  • innodb_buffer_pool_size：确保InnoDB缓冲池足够大，能容纳常用数据；
  • join_buffer_size、sort_buffer_size：针对小表JOIN场景可适当调大，但不要超过系统内存上限；
  • max_heap_table_size、tmp_table_size：这两个参数决定内存临时表的上限，超过阈值会自动转成磁盘临时表。

4. 拆分查询逻辑 #

把复杂的单查询拆分为两步执行：

1. 先筛选出符合基础条件（status、category、hobby、expectation、edulvl）的qualification ID列表；
2. 再基于这些ID查询关联的groups和dictionaries数据。
   比如在Laravel中先查ID集合，再用whereIn('id', $ids)关联其他表，能大幅降低JOIN的复杂度。

5. 验证索引的有效性 #

虽然你提到已配置索引，但多条件场景下需要确认复合索引是否覆盖需求：

• groups表的name_pl字段必须有单独索引或包含在复合索引中；
• 中间表qualification_group需要建立双向联合索引：(qualification_id, group_id)和(group_id, qualification_id)，确保关联查询时能快速定位数据；
• 对于qualifications表的过滤字段（status、category等），建议建立复合索引覆盖常用过滤条件，减少全表扫描概率。

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