Drupal 10多JOIN动态SQL查询致数据库CPU占满无响应问题排查

问题背景 #

我在Drupal 10项目中编写了如下动态查询代码，用于查询与指定时间区间存在冲突的节点：

$query = $this->entityTypeManager->getStorage('node')->getQuery();
$query->accessCheck(FALSE);
$query->condition('status', 1);
$query->condition('type', $node_bundle); 
$query->condition($taxonomy_field_name, $node_term_id);
$query->condition('nid', $node->id(), '<>');
$orGroup = $query->orConditionGroup();

foreach($recurrences as $recurrence){

    $date_start = $recurrence['value'];
    $date_end = $recurrence['end_value'];

    $andGroup = $query->andConditionGroup();

    $andGroup->condition($smart_date_field_name . '.end_value', $date_start, '>');
    $andGroup->condition($smart_date_field_name . '.value', $date_end, '<');

    $orGroup->condition($andGroup);

}

$query->condition($orGroup);
$nodes_id = $query->execute();

该查询会根据$recurrences的数量生成多个LEFT JOIN关联node__field_reserva_sala_data_i_hora表。执行时数据库CPU被完全占用且无响应，但减少JOIN数量时查询正常，直接在MySQL客户端执行生成的SQL也出现同样问题。

服务器配置 #

• OS: Debian 11
• Web: Apache/2.4.56
• PHP: 8.1.17
• DB版本: 10.5.18-MariaDB-0+deb11u1（也试过MySQL Server）
• DB引擎: InnoDB
• DB大小: 34 MB
• Drupal: 10.0.7

EXPLAIN执行计划 #

id  select_type table   type    possible_keys   key key_len ref rows    Extra
1   SIMPLE  node__field_reserva_sala_sala   ref PRIMARY,field_reserva_sala_sala_target_id   field_reserva_sala_sala_target_id   4   const   8   Using index
1   SIMPLE  base_table  eq_ref  PRIMARY PRIMARY 4   calendari_d10.node__field_reserva_sala_sala.entity_id   1   
1   SIMPLE  node_field_data ref PRIMARY,node__id__default_langcode__langcode,node_field__type__target_id,node__status_type  node__status_type   39  const,const,calendari_d10.node__field_reserva_sala_sala.entity_id   1   Using where; Using index
1   SIMPLE  node__field_reserva_sala_data_i_hora    ref PRIMARY PRIMARY 4   calendari_d10.node__field_reserva_sala_sala.entity_id   1   
1   SIMPLE  node__field_reserva_sala_data_i_hora_2  ref PRIMARY PRIMARY 4   calendari_d10.node__field_reserva_sala_sala.entity_id   1   
1   SIMPLE  node__field_reserva_sala_data_i_hora_3  ref PRIMARY PRIMARY 4   calendari_d10.node__field_reserva_sala_sala.entity_id   1   
1   SIMPLE  node__field_reserva_sala_data_i_hora_4  ref PRIMARY PRIMARY 4   calendari_d10.node__field_reserva_sala_sala.entity_id   1   
1   SIMPLE  node__field_reserva_sala_data_i_hora_5  ref PRIMARY PRIMARY 4   calendari_d10.node__field_reserva_sala_sala.entity_id   1   
1   SIMPLE  node__field_reserva_sala_data_i_hora_6  ref PRIMARY PRIMARY 4   calendari_d10.node__field_reserva_sala_sala.entity_id   1   
1   SIMPLE  node__field_reserva_sala_data_i_hora_7  ref PRIMARY PRIMARY 4   calendari_d10.node__field_reserva_sala_sala.entity_id   1   
1   SIMPLE  node__field_reserva_sala_data_i_hora_8  ref PRIMARY PRIMARY 4   calendari_d10.node__field_reserva_sala_sala.entity_id   1   
1   SIMPLE  node__field_reserva_sala_data_i_hora_9  ref PRIMARY PRIMARY 4   calendari_d10.node__field_reserva_sala_sala.entity_id   1   
1   SIMPLE  node__field_reserva_sala_data_i_hora_10 ref PRIMARY PRIMARY 4   calendari_d10.node__field_reserva_sala_sala.entity_id   1   
1   SIMPLE  node__field_reserva_sala_data_i_hora_11 ref PRIMARY PRIMARY 4   calendari_d10.node__field_reserva_sala_sala.entity_id   1   
1   SIMPLE  node__field_reserva_sala_data_i_hora_12 ref PRIMARY PRIMARY 4   calendari_d10.node__field_reserva_sala_sala.entity_id   1   
1   SIMPLE  node__field_reserva_sala_data_i_hora_13 ref PRIMARY PRIMARY 4   calendari_d10.node__field_reserva_sala_sala.entity_id   1   
1   SIMPLE  node__field_reserva_sala_data_i_hora_14 ref PRIMARY PRIMARY 4   calendari_d10.node__field_reserva_sala_sala.entity_id   1   
1   SIMPLE  node__field_reserva_sala_data_i_hora_15 ref PRIMARY PRIMARY 4   calendari_d10.node__field_reserva_sala_sala.entity_id   1   
1   SIMPLE  node__field_reserva_sala_data_i_hora_16 ref PRIMARY PRIMARY 4   calendari_d10.node__field_reserva_sala_sala.entity_id   1   
1   SIMPLE  node__field_reserva_sala_data_i_hora_17 ref PRIMARY PRIMARY 4   calendari_d10.node__field_reserva_sala_sala.entity_id   1   
1   SIMPLE  node__field_reserva_sala_data_i_hora_18 ref PRIMARY PRIMARY 4   calendari_d10.node__field_reserva_sala_sala.entity_id   1   
1   SIMPLE  node__field_reserva_sala_data_i_hora_19 ref PRIMARY PRIMARY 4   calendari_d10.node__field_reserva_sala_sala.entity_id   1   
1   SIMPLE  node__field_reserva_sala_data_i_hora_20 ref PRIMARY PRIMARY 4   calendari_d10.node__field_reserva_sala_sala.entity_id   1   
1   SIMPLE  node__field_reserva_sala_data_i_hora_21 ref PRIMARY PRIMARY 4   calendari_d10.node__field_reserva_sala_sala.entity_id   1   Using where

问题原因 #

1. 冗余多表JOIN引发计算爆炸
   当前代码循环创建AND条件组并加入OR组的逻辑，会让Drupal为每个时间区间单独生成一次日期表的JOIN。这种完全冗余的写法导致数据库需要处理大量关联操作，OR条件下的多表关联会让优化器无法生成高效执行计划，最终CPU被大量数据匹配计算耗尽。

2. 日期条件未利用有效索引
   从EXPLAIN结果可见，日期表仅通过PRIMARY索引关联entity_id，但日期范围判断（end_value > ?和value < ?）没有用到专门的复合索引。数据库需要在每个JOIN后的结果集中再过滤日期，额外增加了大量计算开销。

3. 查询逻辑设计错误
   核心问题是把“多时间区间的OR判断”错误设计成了“多表JOIN的OR判断”，实际上只需要关联一次日期表，在同一个表上完成所有时间区间的冲突判断即可。

优化方案 #

1. 重构查询逻辑，减少JOIN次数
   修改代码，让所有时间区间的条件都基于同一个日期表关联完成，避免重复JOIN：

   $query = $this->entityTypeManager->getStorage('node')->getQuery();
   $query->accessCheck(FALSE);
   $query->condition('status', 1);
   $query->condition('type', $node_bundle); 
   $query->condition($taxonomy_field_name, $node_term_id);
   $query->condition('nid', $node->id(), '<>');
   
   // 所有时间区间的OR条件都基于同一个日期表
   $dateOrGroup = $query->orConditionGroup();
   foreach($recurrences as $recurrence){
       $date_start = $recurrence['value'];
       $date_end = $recurrence['end_value'];
       
       $andGroup = $query->andConditionGroup();
       $andGroup->condition($smart_date_field_name . '.end_value', $date_start, '>');
       $andGroup->condition($smart_date_field_name . '.value', $date_end, '<');
       
       $dateOrGroup->condition($andGroup);
   }
   
   $query->condition($dateOrGroup);
   $nodes_id = $query->execute();
   

   这样Drupal只会生成一次JOIN，大幅降低数据库关联压力。

2. 创建复合索引优化日期过滤
   为日期字段表创建包含entity_id、value、end_value的复合索引，让数据库同时利用索引完成关联和日期范围过滤：

   CREATE INDEX idx_entity_date_range ON node__field_reserva_sala_data_i_hora (entity_id, value, end_value);
   

3. 验证优化效果
   修改后重新执行EXPLAIN，确认日期表仅被JOIN一次，且日期条件用到了新创建的复合索引。

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