调度系统：查询地点与工种可用时隙过慢，求MariaDB优化方案

业务背景 #

• 数千个作业地点，每个地点同一时段最多容纳4人
• 存在多种工种（如木工、电工、水管工），每种工种的人员数量有限
• 所有任务需指定1名或多名对应工种人员，在特定地点执行，任务时长各不相同

数据表结构 #

使用tbl_project_planner表存储任务规划信息：

id - INT(11) - 唯一递增ID
projectid - INT(11) - 项目ID
l4_start - DATETIME - 任务开始时间
l4_end - DATETIME - 任务结束时间
locationid - INT(11) - 地点ID
taskid - INT(11) - 任务ID
trade - VARCHAR(50) - 工种类型（如LAB_ELECTRICIAN）
people - INT(11) - 该任务所需该工种的人数

核心需求 #

系统规划下一个任务时，需要找到指定地点的下一个可用时隙，有效时隙需满足两个条件：

1. 时隙时段内该地点的总人数不超过4，避免人员过载
2. 时隙时段内对应工种的可用人员数量足够（统计跨所有地点的该工种使用量）

当前方案及问题 #

目前使用MariaDB序列生成待检查的特定时长时隙集合，关联规划表统计各时隙的地点人数与工种使用量，筛选第一个符合条件的时隙。该方案逻辑可行，但查询速度极慢（约30秒/次），需要优化至亚秒级。

当前查询语句 #

SELECT mystart, myend, SUM(IF(vessellocationid='158533',people,0)) AS peopleinlocation
   , SUM(IF(trade = 'LAB_ELECTRICIAN',people,0)) AS tradecount 
FROM (SELECT  '2024-05-17 07:00:00' + INTERVAL seq-1 MINUTE AS mystart,
         '2024-05-17 07:30:00' + INTERVAL seq-1 MINUTE AS myend 
      FROM seq_1_to_10000_step_5
     ) AS searchrange
 
JOIN tbl_project_planner AS planner
  ON (vessellocationid='158533' OR trade = 'LAB_ELECTRICIAN') 
  AND projectid='21011' 
  AND (
        (planner.l4_start BETWEEN searchrange.mystart AND searchrange.myend
         OR planner.l4_end BETWEEN searchrange.mystart AND searchrange.myend
        )
      OR planner.l4_start <= searchrange.mystart AND planner.l4_end >= searchrange.myend
     )
GROUP BY mystart
HAVING IFNULL(peopleinlocation,0) <=4 
   AND IFNULL(tradecount,0) <= 5 

已做优化及执行计划 #

已为l4_start、l4_end、trade、vessellocationid、projectid单独创建索引，EXPLAIN输出如下：

1   SIMPLE  planner index_merge projectid,vessellocationid,trade,v_t_p,l4_start,l4_end,l4_start and end vessellocationid,projectid,projectid,trade  5,5,5,203   NULL    918 Using union(intersect(vessellocationid,projectid),intersect(projectid,trade)); Using where; Using temporary; Using filesort

1   SIMPLE  seq_1_to_10000_step_5   index   NULL    PRIMARY 8   NULL    2000    Using where; Using index; Using join buffer (flat, BNL join)

恳请各位提供优化建议！

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