使用UNION ALL的派生表子查询全量取数的SQL优化问题

现象 #

• 用包含UNION ALL的派生表关联uat_portal.jobs与uat_portal.jobs_employees、uat_portal.jobs_equipment时，EXPLAIN显示后两张表被全量读取（分别扫描953371和391702行）
• 单独关联其中一张表时，仅读取符合条件的少量行（扫描1行）
• 带WHERE条件的查询耗时约3秒，关联视图无法加载

已排查操作 #

• 确认关联字段类型一致：jobs.id、jobs_employees.job_id、jobs_equipment.job_id均为int(11)
• 尝试新增bigint类型测试字段（job_id_bigint_test）并关联，未解决问题
• 重建索引、添加/删除外键、调整查询字段及语句，均无效

关联UNION ALL派生表的SQL及执行计划 #

SQL语句 #

SELECT
    `j`.`job_date` AS `VOUCHERDATE`,
    `labor_equipment`.`time_entry` AS `HOURS`
FROM `uat_portal`.`jobs` `j`
    LEFT JOIN(
        SELECT
            `uat_portal`.`jobs_employees`.`job_id`,
            `uat_portal`.`jobs_employees`.`time_entry`
        FROM
            `uat_portal`.`jobs_employees`
        UNION ALL
        SELECT
            `uat_portal`.`jobs_equipment`.`job_id`,
            `uat_portal`.`jobs_equipment`.`time_entry`
        FROM
            `uat_portal`.`jobs_equipment`
    ) `labor_equipment`
    ON
        `j`.`id` = `labor_equipment`.`job_id`

EXPLAIN结果 #

1   PRIMARY j   index   NULL    idx_jobs_job_date   3   NULL    218110  Using index 
1   PRIMARY <derived2>  ref key0    key0    5   uat_portal.j.id 10      
2   DERIVED jobs_employees  index   NULL    job_id_index    4   NULL    953371  Using index 
3   UNION   jobs_equipment  index   NULL    job_id_index    4   NULL    391702  Using index 

单独关联单表的SQL及执行计划 #

关联jobs_employees的SQL #

SELECT
    `j`.`job_date` AS `VOUCHERDATE`,
    `labor_equipment`.`time_entry` AS `HOURS`
FROM `uat_portal`.`jobs` `j`
    LEFT JOIN(
        SELECT
            `uat_portal`.`jobs_employees`.`job_id`,
            `uat_portal`.`jobs_employees`.`time_entry`
        FROM
            `uat_portal`.`jobs_employees`
    ) `labor_equipment`
    ON
        `j`.`id` = `labor_equipment`.`job_id`

对应EXPLAIN结果 #

1   SIMPLE  j   index   NULL    idx_jobs_job_date   3   NULL    218110  Using index 
1   SIMPLE  jobs_equipment  ref job_id_index    job_id_index    4   uat_portal.j.id 1   Using index 

关联jobs_equipment的SQL #

SELECT
    `j`.`job_date` AS `VOURCHERDATE`,
    `labor_equipment`.`time_entry` AS `HOURS`
FROM `uat_portal`.`jobs` `j`
    LEFT JOIN(
        SELECT
            `uat_portal`.`jobs_equipment`.`job_id`,
            `uat_portal`.`jobs_equipment`.`time_entry`
        FROM
            `uat_portal`.`jobs_equipment`
    ) `labor_equipment`
    ON
        `j`.`id` = `labor_equipment`.`job_id`

对应EXPLAIN结果 #

1   SIMPLE  j   index   NULL    idx_jobs_job_date   3   NULL    218110  Using index 
1   SIMPLE  jobs_equipment  ref job_id_index    job_id_index    4   uat_portal.j.id 1   Using index 

涉及表结构 #

jobs表 #

CREATE TABLE `jobs` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `id_bigint_test` bigint(20) unsigned NOT NULL,
  `parent_job` int(11) NOT NULL,
  `workorder_id` int(11) DEFAULT NULL,
  `wo_day_id` int(11) NOT NULL,
  `quote_id` int(11) NOT NULL,
  `job_type` varchar(100) NOT NULL,
  `job_name` varchar(255) NOT NULL,
  `job_number` varchar(25) NOT NULL,
  `job_date` date NOT NULL,
  `job_color` varchar(10) NOT NULL,
  `onsite_time` time NOT NULL,
  `sales_person` varchar(25) NOT NULL,
  `badging_needed` tinyint(1) NOT NULL,
  `badging_completed` tinyint(1) NOT NULL,
  `notes` text NOT NULL,
  `location` varchar(25) NOT NULL,
  `added_on` datetime NOT NULL,
  `added_by` varchar(25) NOT NULL,
  `updated_on` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  `updated_by` varchar(25) NOT NULL,
  `removed` tinyint(1) NOT NULL,
  `removed_on` datetime NOT NULL,
  `removed_by` varchar(25) NOT NULL,
  `required_crew_size` varchar(255) DEFAULT NULL,
  `min_skill_level` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_jobs_job_date` (`job_date`),
  KEY `idx_jobs_job_number` (`job_number`),
  KEY `indx_jobs_parent_job` (`parent_job`),
  KEY `id_bigint_test` (`id_bigint_test`)
) ENGINE=InnoDB AUTO_INCREMENT=209896 DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci

jobs_equipment表 #

CREATE TABLE `jobs_equipment` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `rate_card_owned_equipment_id` int(11) NOT NULL,
  `job_id` int(11) NOT NULL,
  `job_id_bigint_test` bigint(20) unsigned NOT NULL,
  `equipment_id` int(11) NOT NULL,
  `owned_equipment_id` varchar(255) DEFAULT NULL,
  `start_time` varchar(25) NOT NULL,
  `end_time` varchar(25) NOT NULL,
  `override_time` tinyint(1) NOT NULL,
  `time_entry` float(8,2) NOT NULL,
  `billable` tinyint(1) NOT NULL,
  `removed` tinyint(1) NOT NULL,
  `removed_by` varchar(25) NOT NULL,
  `removed_on` datetime NOT NULL,
  `added_on` datetime NOT NULL,
  `added_by` varchar(25) NOT NULL,
  `updated_on` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  `updated_by` varchar(25) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `job_id_index` (`job_id`),
  KEY `job_id_bigint_index` (`job_id_bigint_test`)
) ENGINE=InnoDB AUTO_INCREMENT=392211 DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci

jobs_employees表 #

CREATE TABLE `jobs_employees` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `rate_card_labor_id` int(11) NOT NULL,
  `job_id` int(11) NOT NULL,
  `job_id_bigint_test` bigint(20) unsigned NOT NULL,
  `employee_id` int(11) NOT NULL,
  `title` varchar(255) NOT NULL,
  `labor_id` varchar(255) DEFAULT NULL,
  `category` varchar(50) NOT NULL,
  `start_time` varchar(25) NOT NULL,
  `end_time` varchar(25) NOT NULL,
  `override_time` tinyint(1) NOT NULL,
  `truck` varchar(100) NOT NULL,
  `trailer` varchar(100) NOT NULL,
  `job_action` varchar(100) NOT NULL,
  `time_entry` float(8,2) NOT NULL,
  `billable` tinyint(1) NOT NULL,
  `incl_break` tinyint(1) NOT NULL,
  `added_on` datetime NOT NULL,
  `added_by` varchar(25) NOT NULL,
  `updated_on` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  `updated_by` varchar(25) NOT NULL,
  `removed` tinyint(1) NOT NULL,
  `removed_on` datetime NOT NULL,
  `removed_by` varchar(25) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `job_id_index` (`job_id`),
  KEY `job_id_bigint_index` (`job_id_bigint_test`)
) ENGINE=InnoDB AUTO_INCREMENT=1018745 DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci

更新补充（9/15） #

补充了视图使用的实际SELECT示例（原内容未提供具体示例）

解决方案 #

1. 拆分UNION ALL为两次独立LEFT JOIN #

将派生表的UNION ALL拆分为两次LEFT JOIN再合并结果，让MySQL分别利用两张表的索引：

SELECT
    j.job_date AS VOUCHERDATE,
    je.time_entry AS HOURS
FROM uat_portal.jobs j
LEFT JOIN uat_portal.jobs_employees je ON j.id = je.job_id
UNION ALL
SELECT
    j.job_date AS VOUCHERDATE,
    jeq.time_entry AS HOURS
FROM uat_portal.jobs j
LEFT JOIN uat_portal.jobs_equipment jeq ON j.id = jeq.job_id;

这种写法让每个JOIN都能使用job_id_index索引，避免全表扫描。

2. 给派生表显式指定索引（MySQL 8.0+） #

如果使用MySQL 8.0及以上版本，可在派生表关联时强制使用索引：

SELECT
    j.job_date AS VOUCHERDATE,
    labor_equipment.time_entry AS HOURS
FROM uat_portal.jobs j
LEFT JOIN (
    SELECT job_id, time_entry FROM uat_portal.jobs_employees
    UNION ALL
    SELECT job_id, time_entry FROM uat_portal.jobs_equipment
) labor_equipment FORCE INDEX (key0)
ON j.id = labor_equipment.job_id;

或使用WITH子句（MySQL 8.0.19+）定义派生表，帮助优化器识别索引：

WITH labor_equipment AS (
    SELECT job_id, time_entry FROM uat_portal.jobs_employees
    UNION ALL
    SELECT job_id, time_entry FROM uat_portal.jobs_equipment
) SELECT
    j.job_date AS VOUCHERDATE,
    le.time_entry AS HOURS
FROM uat_portal.jobs j
LEFT JOIN labor_equipment le ON j.id = le.job_id;

3. 强制指定驱动表顺序 #

用STRAIGHT_JOIN强制MySQL以jobs为驱动表，使用嵌套循环JOIN：

SELECT STRAIGHT_JOIN
    j.job_date AS VOUCHERDATE,
    labor_equipment.time_entry AS HOURS
FROM uat_portal.jobs j
LEFT JOIN (
    SELECT job_id, time_entry FROM uat_portal.jobs_employees
    UNION ALL
    SELECT job_id, time_entry FROM uat_portal.jobs_equipment
) labor_equipment ON j.id = labor_equipment.job_id;

4. 临时关闭派生表合并优化 #

调整优化器参数，禁止派生表合并：

SET optimizer_switch='derived_merge=off';

执行查询后观察性能，若有效可考虑在会话或全局级别配置，但需注意对其他查询的影响。

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