MySQL自连接查询优化：工厂员工班次冲突检测慢查询问题

云上老周

2026-7-30

问题描述

我负责开发工厂员工班次管理应用，需通过SQL查询检测班次冲突：若员工已有08:00-16:00的班次，再分配09:00-17:00的同天班次属于冲突，但16:00-17:00的班次不冲突，需精准判断起止时间，同时需考虑跨天班次。

当前使用的查询语句如下：

SELECT 
  `shifts`.`id` 
FROM 
  `shifts` 
  INNER JOIN `shifts` `shifts_2` ON `shifts_2`.`employee_id` = `shifts`.`employee_id` 
  AND `shifts_2`.`start_at` < '2023-03-01 00:00:00' 
  AND `shifts_2`.`start_at` < `shifts`.`end_at` 
  AND `shifts_2`.`end_at` > '2023-01-31 23:59:00' 
  AND `shifts_2`.`end_at` > `shifts`.`start_at` 
  AND `shifts_2`.`id` != `shifts`.`id` 
WHERE 
  `shifts`.`id` IN (22258796, 22258797);

为便于阅读简化了IN子句中的班次ID数量，但实际场景中该列表是动态的，曾出现包含6000个ID的情况，此时查询会扫描数百万行数据，耗时超10秒。

shifts表结构如下：

CREATE TABLE `shifts` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `employee_id` int(11) NOT NULL,
  `start_at` datetime NOT NULL,
  `end_at` datetime NOT NULL,
  PRIMARY KEY (`id`),
  KEY `index_shifts_on_employee_id` (`employee_id`),
  KEY `index_sm_shifts_on_employee_id_and_start_at_and_end_at` (`employee_id`,`start_at`,`end_at`),
  KEY `index_sm_employee_id_id_start_end` (`employee_id`,`id`,`start_at`,`end_at`),
  CONSTRAINT `fk_03a7d0ca25` FOREIGN KEY (`employee_id`) REFERENCES `employees` (`id`) ON DELETE CASCADE,
) ENGINE=InnoDB AUTO_INCREMENT=32677939 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci

我使用的是MySQL 5.7版本，执行EXPLAIN FORMAT=JSON得到以下结果：

{
  "query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "62.36"
    },
    "nested_loop": [
      {
        "table": {
          "table_name": "shifts",
          "access_type": "range",
          "possible_keys": [
            "PRIMARY",
            "index_shifts_on_employee_id",
            "index_sm_shifts_on_employee_id_and_start_at_and_end_at",
            "index_sm_employee_id_id_start_end"
          ],
          "key": "PRIMARY",
          "used_key_parts": [
            "id"
          ],
          "key_length": "8",
          "rows_examined_per_scan": 2,
          "rows_produced_per_join": 2,
          "filtered": "100.00",
          "cost_info": {
            "read_cost": "2.41",
            "eval_cost": "0.40",
            "prefix_cost": "2.81",
            "data_read_per_join": "2K"
          },
          "used_columns": [
            "id",
            "employee_id",
            "start_at",
            "end_at"
          ],
          "attached_condition": "(`shifts`.`id` in (22258796,22258797))"
        }
      },
      {
        "table": {
          "table_name": "shifts_2",
          "access_type": "ref",
          "possible_keys": [
            "index_shifts_on_employee_id",
            "index_sm_shifts_on_employee_id_and_start_at_and_end_at",
            "index_sm_employee_id_id_start_end"
          ],
          "key": "index_sm_employee_id_id_start_end",
          "used_key_parts": [
            "employee_id"
          ],
          "key_length": "4",
          "ref": [
            "shifts.employee_id"
          ],
          "rows_examined_per_scan": 141,
          "rows_produced_per_join": 3,
          "filtered": "1.11",
          "using_index": true,
          "cost_info": {
            "read_cost": "3.02",
            "eval_cost": "0.63",
            "prefix_cost": "62.36",
            "data_read_per_join": "3K"
          },
          "used_columns": [
            "id",
            "employee_id",
            "start_at",
            "end_at"
          ],
          "attached_condition": "((`shifts_2`.`start_at` < '2023-03-01 00:00:00') and (`shifts_2`.`start_at` < `shifts`.`end_at`) and (`shifts_2`.`end_at` > '2023-01-31 23:59:00') and (`shifts_2`.`end_at` > `shifts`.`start_at`) and (`shifts_2`.`id` <> `shifts`.`id`))"
        }
      }
    ]
  }
}

常规EXPLAIN结果：

+----+-------------+---------------------------+------------+-------+-----------------------------------------------------------------------------------------------------------------------------------------------+--------------------------------------------------------+---------+-----------------------------------------------------------+------+----------+--------------------------+
| id | select_type | table    | partitions | type  | possible_keys                                                                                                                      | key                                                    | key_len | ref                | rows | filtered | Extra                    |
+----+-------------+---------------------------+------------+-------+-----------------------------------------------------------------------------------------------------------------------------------------------+--------------------------------------------------------+---------+-----------------------------------------------------------+------+----------+--------------------------+
|  1 | SIMPLE      | shifts   | NULL       | range | PRIMARY,index_sm_employee_id_id_start_end,index_sm_shifts_on_employee_id_and_start_at_and_end_at,index_shiftsshifts_on_employee_id | PRIMARY                                                | 8       | NULL               |    2 |   100.00 | Using where              |
|  1 | SIMPLE      | shifts_2 | NULL       | ref   | index_sm_employee_id_id_start_end,index_sm_shifts_on_employee_id_and_start_at_and_end_at,index_shiftsshifts_on_employee_id         | index_sm_shifts_on_employee_id_and_start_at_and_end_at | 4       | shifts.employee_id | 8626 |     1.11 | Using where; Using index |
+----+-------------+---------------------------+------------+-------+-----------------------------------------------------------------------------------------------------------------------------------------------+--------------------------------------------------------+---------+-----------------------------------------------------------+------+----------+--------------------------+

若通过ID限制shifts_2表，结果似乎更优，但不确定Using join buffer (Block Nested Loop)的影响：

| id | select_type | table    | partitions | type  | possible_keys                                                                                                                | key     | key_len | ref  | rows | filtered | Extra                                              |
+----+-------------+---------------------------+------------+-------+-----------------------------------------------------------------------------------------------------------------------------------------------+---------+---------+------+------+----------+----------------------------------------------------+
|  1 | SIMPLE      | shifts_2 | NULL       | range | PRIMARY,index_shifts_on_employee_id,index_sm_shifts_on_employee_id_and_start_at_and_end_at,index_sm_employee_id_id_start_end | PRIMARY | 8       | NULL |    2 |    11.11 | Using where                                        |
|  1 | SIMPLE      | shifts   | NULL       | range | PRIMARY,index_shifts_on_employee_id,index_sm_shifts_on_employee_id_and_start_at_and_end_at,index_sm_employee_id_id_start_end | PRIMARY | 8       | NULL |    2 |     2.50 | Using where; Using join buffer (Block Nested Loop) |
+----+-------------+---------------------------+------------+-------+-----------------------------------------------------------------------------------------------------------------------------------------------+---------+---------+------+------+----------+----------------------------------------------------+

补充：员工可在同一天有多个班次，08:00-16:00与09:00-17:00算冲突，但与16:00-17:00不算冲突。

我需要识别冲突班次ID供用户删除，请问如何优化该查询以避免扫描大量行？

优化方案

1. 确认冲突判断逻辑的准确性

你的冲突规则是两个时间段存在非端点重叠，当前的shifts_2.start_at < shifts.end_at AND shifts_2.end_at > shifts.start_at已经精准符合需求（比如16:00-17:00和08:00-16:00不会触发该条件），无需调整。如果不需要限定全局时间范围，可以移除shifts_2.start_at < '2023-03-01 00:00:00'和shifts_2.end_at > '2023-01-31 23:59:00'，减少过滤开销。

2. 解决大IN子句的性能瓶颈

当IN子句包含数千个ID时，MySQL会将其转换为大量OR条件，导致索引失效、连接开销剧增。可以通过以下两种方式优化：

方式一：使用临时表存储目标班次ID

将需要检测的班次ID批量插入临时表，利用临时表的主键快速定位数据，避免大IN子句的性能损耗：

-- 创建临时表（会话级，关闭连接后自动销毁）
CREATE TEMPORARY TABLE temp_target_shifts (
    id bigint(20) PRIMARY KEY
);

-- 批量插入目标班次ID
INSERT INTO temp_target_shifts (id) VALUES (22258796), (22258797);

-- 执行冲突检测
SELECT DISTINCT s.id
FROM temp_target_shifts ts
JOIN shifts s ON ts.id = s.id
JOIN shifts s2 ON s.employee_id = s2.employee_id
    AND s2.start_at < s.end_at
    AND s2.end_at > s.start_at
    AND s2.id != s.id
-- 如需时间范围过滤，在此添加
WHERE s2.start_at < '2023-03-01 00:00:00'
  AND s2.end_at > '2023-01-31 23:59:00';

方式二：使用EXISTS子查询替代JOIN

EXISTS子查询会在找到第一个匹配的冲突班次后立即终止扫描，避免生成大量连接结果，性能更优：

SELECT s.id
FROM shifts s
WHERE s.id IN (22258796, 22258797)
AND EXISTS (
    SELECT 1
    FROM shifts s2
    WHERE s2.employee_id = s.employee_id
        AND s2.start_at < s.end_at
        AND s2.end_at > s.start_at
        AND s2.id != s.id
        AND s2.start_at < '2023-03-01 00:00:00'
        AND s2.end_at > '2023-01-31 23:59:00'
);

3. 优化索引，减少扫描行数

当前的索引index_sm_shifts_on_employee_id_and_start_at_and_end_at可以进一步优化，让时间过滤条件更高效：

-- 创建更贴合查询的覆盖索引：先按employee_id分组，再按start_at过滤，最后包含end_at避免回表
ALTER TABLE shifts ADD INDEX idx_employee_start_end (employee_id, start_at, end_at);

该索引会先通过employee_id定位到目标员工的所有班次，再通过start_at < s.end_at过滤掉大部分不相关的班次，最后仅需判断剩余班次的end_at是否满足条件，大幅减少扫描行数。

4. 处理Block Nested Loop的影响

Using join buffer (Block Nested Loop)说明MySQL无法通过索引高效连接表，此时：

确保连接条件employee_id使用了上述优化后的索引
适当调大join_buffer_size配置（建议不超过16M，避免内存压力）
优先使用临时表或EXISTS子查询，减少连接的数据量

5. 去重处理

如果同一个班次匹配到多个冲突班次，使用DISTINCT或GROUP BY去重，避免返回重复的ID，减少结果集大小。

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

MySQL自连接查询优化：工厂员工班次冲突检测慢查询问题

1. 确认冲突判断逻辑的准确性 #

2. 解决大IN子句的性能瓶颈 #

方式一：使用临时表存储目标班次ID #

方式二：使用EXISTS子查询替代JOIN #

3. 优化索引，减少扫描行数 #

4. 处理Block Nested Loop的影响 #

5. 去重处理 #