MariaDB多表复杂JOIN查询优化：简化重复UNION语句

技术需求背景 #

数据库环境：MariaDB-10.8
现有4张业务表（意大利语原名附英文注释）：

• Passaggi（steps）：步骤定义表
• Passaggi salvati（saved steps）：已完成步骤记录表
• Procedure salvate（saved procedure）：流程实例表
• Tipi procedura（procedure types）：流程类型表

表结构及初始化数据如下：

Passaggi（steps）表 #

CREATE TABLE `passaggi` (
  `id` int(11) NOT NULL,
  `tipo_procedura_id` int(11) DEFAULT NULL,
  `passaggio` varchar(255) NOT NULL,
  `ordine` int(11) NOT NULL DEFAULT 1
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_unicode_ci;

INSERT INTO `passaggi` (`id`, `tipo_procedura_id`, `passaggio`, `ordine`) VALUES
(1, 6, 'Attivazione', 1),
(2, 4, 'Attivazione', 1),
(3, 4, 'Assegno', 2),
(4, 3, 'Attivazione', 1),
(5, 3, 'Bando', 2),
(6, 3, 'Decreto di nomina della commissione', 3),
(7, 5, 'Attivazione', 1),
(8, 7, 'Attivazione', 1),
(9, 10, 'Attivazione', 1),
(10, 10, 'Avviso selezione', 2),
(11, 10, 'Decreto nomina commissione', 3),
(12, 10, 'Decreto approvazione atti', 4),
(13, 10, 'Decreto conferimento incarico', 5),
(14, 10, 'Inizio attività', 6),
(15, 10, 'Contratto', 7),
(16, 7, 'Secondo', 2);

Passaggi salvati（saved steps）表 #

CREATE TABLE `passaggi_salvati` (
  `id` int(11) NOT NULL,
  `passaggio_id` int(11) NOT NULL,
  `data_documento` datetime NOT NULL,
  `procedura_id` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_unicode_ci;

INSERT INTO `passaggi_salvati` (`id`, `passaggio_id`, `data_documento`, `procedura_id`) VALUES
(5, 8, '2023-04-26 00:00:00', 4),
(6, 8, '2023-04-26 00:00:00', 5),
(7, 16, '2023-04-27 00:00:00', 5);

Procedure salvate（saved procedure）表 #

CREATE TABLE `procedure_salvate` (
  `id` int(11) NOT NULL,
  `tipo_procedura_id` int(11) DEFAULT NULL,
  `numero_procedura` varchar(255) DEFAULT NULL,
  `anno_procedura` int(4) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_unicode_ci;

INSERT INTO `procedure_salvate` (`id`, `tipo_procedura_id`, `numero_procedura`, `anno_procedura`) VALUES
(4, 7, '1', 2023),
(5, 7, '2', 2023);

Tipi procedura（procedure types）表 #

CREATE TABLE `tipi_procedura` (
  `id` int(11) NOT NULL,
  `procedura` varchar(255) DEFAULT NULL,
  `parent_id` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_unicode_ci;

INSERT INTO `tipi_procedura` (`id`, `procedura`, `parent_id`) VALUES
(1, 'Attivazione borse/assegni', NULL),
(2, 'Lettera di incarico per docenza', NULL),
(3, 'Borsa di studio per attività di ricerca', 1),
(4, 'Assegno di ricerca', 1),
(5, 'Corso di perfezionamento', 2),
(6, 'Master', 2),
(7, 'Seminari', 2),
(9, 'Contratti di collaborazione', NULL),
(10, 'Incarico individuale di prestazione professionale', 9);

查询需求 #

编写查询语句，从流程实例表、步骤定义表、流程类型表中获取数据，关联已完成步骤记录表的数据（若存在则显示对应字段，否则显示NULL）。

示例结果 #

• 当筛选procedure_salvate.id = 4时，需返回：

• 当筛选procedure_salvate.id = 5时，需返回：

当前实现方案 #

目前使用包含UNION的复杂重复查询实现需求，语句如下：

SELECT `numero_procedura`,`anno_procedura`,`data_documento`,`pas_sal`.`id` AS `pas_sal.id`,`pas`.`passaggio`,`procedura`,`proc_sal`.`tipo_procedura_id`,`pas`.`id` AS `pas.id`, `ordine`
FROM `html_cf_modulistica_procedure_salvate` AS `proc_sal`
JOIN `html_cf_modulistica_passaggi_salvati` AS `pas_sal` ON `pas_sal`.`procedura_id` = `proc_sal`.`id`
JOIN `html_cf_modulistica_passaggi` AS `pas` ON `pas_sal`.`passaggio_id` = `pas`.`id`
JOIN `html_cf_modulistica_tipi_procedura` AS `proc` ON `proc_sal`.`tipo_procedura_id` = `proc`.`id`
WHERE `proc_sal`.`id` = 5
UNION
SELECT `numero_procedura`,`anno_procedura`,NULL AS `data_documento`,NULL AS `pas_sal.id`,`pas`.`passaggio`,`procedura`,`proc_sal`.`tipo_procedura_id`,`pas`.`id` AS `pas.id`, `ordine`
FROM `html_cf_modulistica_procedure_salvate` AS `proc_sal`
JOIN `html_cf_modulistica_passaggi` AS `pas` ON `proc_sal`.`tipo_procedura_id` = `pas`.`tipo_procedura_id`
JOIN `html_cf_modulistica_tipi_procedura` AS `proc` ON `proc_sal`.`tipo_procedura_id` = `proc`.`id`
WHERE `proc_sal`.`id` = 5 AND pas.id NOT IN(SELECT `passaggio_id` FROM `html_cf_modulistica_passaggi_salvati` WHERE `procedura_id` = 5)
ORDER BY `ordine`

优化目标 #

寻求更简洁、易维护的查询语句替代当前的重复UNION查询。

优化后的查询语句 #

可以使用LEFT JOIN替代UNION，只需要一次主查询即可实现需求，语句如下：

SELECT 
    proc_sal.numero_procedura,
    proc_sal.anno_procedura,
    pas_sal.data_documento,
    pas_sal.id AS `pas_sal.id`,
    pas.passaggio,
    proc.procedura,
    proc_sal.tipo_procedura_id,
    pas.id AS `pas.id`,
    pas.ordine
FROM html_cf_modulistica_procedure_salvate AS proc_sal
JOIN html_cf_modulistica_tipi_procedura AS proc 
    ON proc_sal.tipo_procedura_id = proc.id
JOIN html_cf_modulistica_passaggi AS pas 
    ON proc_sal.tipo_procedura_id = pas.tipo_procedura_id
LEFT JOIN html_cf_modulistica_passaggi_salvati AS pas_sal 
    ON pas_sal.procedura_id = proc_sal.id 
    AND pas_sal.passaggio_id = pas.id
WHERE proc_sal.id = 5 -- 替换为需要筛选的流程实例ID
ORDER BY pas.ordine;

优化说明 #

1. 逻辑简化：通过LEFT JOIN关联已完成步骤表，自动处理“存在则显示，不存在则为NULL”的场景，无需拆分两次查询再用UNION合并。
2. 易维护性：只需要编写一次主查询逻辑，修改筛选条件或字段时只需调整一处，避免了原方案中重复编写相同关联逻辑的问题。
3. 性能更优：减少了子查询和UNION的开销，查询执行计划更高效。

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