MariaDB 10.3关联视图时列别名失效问题求助

问题背景 #

使用MariaDB 10.3版本执行查询时，出现两个核心问题：

• 定义的列别名a和b未生效，查询结果中两个字段均显示为name，导致代码因重复列名报错，尝试单引号、双引号定义别名均无效
• 将ol.name加入GROUP BY（即GROUP BY v.id, ol.name）可解决别名问题，但查询性能从0.06秒骤降至11秒，无法接受

经排查，问题根源在于关联的organisation_localisation_fallback和vacancy_localisation_fallback均为视图。

原查询语句 #

SELECT v.id,
       ol.name AS a,
       vlf.name AS b
FROM vacancy AS v
LEFT OUTER JOIN organisation AS o ON v.organisation_id = o.id
LEFT OUTER JOIN organisation_localisation_fallback AS ol ON o.id = ol.organisation_id and ol.language_id = 14 and ol.country_id = 19
LEFT OUTER JOIN vacancy_localisation_fallback AS vlf ON v.id = vlf.vacancy_id AND vlf.language_id = 14 AND vlf.country_id = 19
GROUP BY v.id;

视图定义 #

vacancy_localisation_fallback视图

ALTER algorithm = undefined definer=`root`@`localhost` sql security definer VIEW `vacancy_localisation_fallback` AS SELECT `v`.`id`              AS `vacancy_id`,
       `clang`.`language_id` AS `language_id`,
       `clang`.`country_id`  AS `country_id`,
       (
                SELECT   `vl`.`NAME`
                FROM     `vacancy_localisation` `vl`
                WHERE    `vl`.`vacancy_id` = `v`.`id`
                ORDER BY
                         CASE
                                  WHEN (
                                                    `vl`.`language_id` = `clang`.`language_id`
                                           AND      `vl`.`country_id` = `clang`.`country_id`) THEN 1
                                  WHEN (
                                                    `vl`.`language_id` = `clang`.`language_id`
                                           AND      `vl`.`country_id` <> `clang`.`country_id`) THEN 2
                                  WHEN `vl`.`language_id` = 15 THEN 3
                                  ELSE 4
                         END limit 1) AS `NAME`,
       (
                SELECT   `vl`.`description`
                FROM     `vacancy_localisation` `vl`
                WHERE    `vl`.`vacancy_id` = `v`.`id`
                ORDER BY
                         CASE
                                  WHEN (
                                                    `vl`.`language_id` = `clang`.`language_id`
                                           AND      `vl`.`country_id` = `clang`.`country_id`) THEN 1
                                  WHEN (
                                                    `vl`.`language_id` = `clang`.`language_id`
                                           AND      `vl`.`country_id` <> `clang`.`country_id`) THEN 2
                                  WHEN `vl`.`language_id` = 15 THEN 3
                                  ELSE 4
                         END limit 1) AS `description`,
       (
                SELECT   `vl`.`offer`
                FROM     `vacancy_localisation` `vl`
                WHERE    `vl`.`vacancy_id` = `v`.`id`
                ORDER BY
                         CASE
                                  WHEN (
                                                    `vl`.`language_id` = `clang`.`language_id`
                                           AND      `vl`.`country_id` = `clang`.`country_id`) THEN 1
                                  WHEN (
                                                    `vl`.`language_id` = `clang`.`language_id`
                                           AND      `vl`.`country_id` <> `clang`.`country_id`) THEN 2
                                  WHEN `vl`.`language_id` = 15 THEN 3
                                  ELSE 4
                         END limit 1) AS `offer`
FROM   (`vacancy` `v`
JOIN   `country_language` `clang`) ;

organisation_localisation_fallback视图

ALTER algorithm = undefined definer=`root`@`localhost` sql security definer VIEW `organisation_localisation_fallback` AS SELECT `o`.`id`              AS `organisation_id`,
       `clang`.`language_id` AS `language_id`,
       `clang`.`country_id`  AS `country_id`,
       (
                SELECT   `ol`.`NAME`
                FROM     `organisation_localisation` `ol`
                WHERE    `ol`.`organisation_id` = `o`.`id`
                ORDER BY
                         CASE
                                  WHEN (
                                                    `ol`.`language_id` = `clang`.`language_id`
                                           AND      `ol`.`country_id` = `clang`.`country_id`) THEN 1
                                  WHEN (
                                                    `ol`.`language_id` = `clang`.`language_id`
                                           AND      `ol`.`country_id` <> `clang`.`country_id`) THEN 2
                                  WHEN `ol`.`language_id` = 15 THEN 3
                                  ELSE 4
                         END limit 1) AS `NAME`,
       (
                SELECT   `ol`.`description`
                FROM     `organisation_localisation` `ol`
                WHERE    `ol`.`organisation_id` = `o`.`id`
                ORDER BY
                         CASE
                                  WHEN (
                                                    `ol`.`language_id` = `clang`.`language_id`
                                           AND      `ol`.`country_id` = `clang`.`country_id`) THEN 1
                                  WHEN (
                                                    `ol`.`language_id` = `clang`.`language_id`
                                           AND      `ol`.`country_id` <> `clang`.`country_id`) THEN 2
                                  WHEN `ol`.`language_id` = 15 THEN 3
                                  ELSE 4
                         END limit 1) AS `description`
FROM   (`organisation` `o`
JOIN   `country_language` `clang`) ;

解决方案 #

1. 关联视图时提前重命名列（推荐，无需修改视图） #

在关联视图的子查询中直接给NAME列指定别名，避免SELECT阶段的别名解析问题，同时保留原查询性能：

SELECT v.id,
       ol.a,
       vlf.b
FROM vacancy AS v
LEFT OUTER JOIN organisation AS o ON v.organisation_id = o.id
LEFT OUTER JOIN (
    SELECT organisation_id, NAME AS a 
    FROM organisation_localisation_fallback 
    WHERE language_id = 14 AND country_id = 19
) AS ol ON o.id = ol.organisation_id
LEFT OUTER JOIN (
    SELECT vacancy_id, NAME AS b 
    FROM vacancy_localisation_fallback 
    WHERE language_id = 14 AND country_id = 19
) AS vlf ON v.id = vlf.vacancy_id
GROUP BY v.id;

2. 修改视图定义，区分列名（一劳永逸） #

如果有权限修改视图，直接把两个视图中的NAME列改成具有区分性的别名：

• 对于organisation_localisation_fallback，将AS NAME``修改为AS organisation_name
• 对于vacancy_localisation_fallback，将AS NAME``修改为AS vacancy_name

修改后原查询可简化为：

SELECT v.id,
       ol.organisation_name AS a,
       vlf.vacancy_name AS b
FROM vacancy AS v
LEFT OUTER JOIN organisation AS o ON v.organisation_id = o.id
LEFT OUTER JOIN organisation_localisation_fallback AS ol ON o.id = ol.organisation_id and ol.language_id = 14 and ol.country_id = 19
LEFT OUTER JOIN vacancy_localisation_fallback AS vlf ON v.id = vlf.vacancy_id AND vlf.language_id = 14 AND vlf.country_id = 19
GROUP BY v.id;

3. 移除无意义的GROUP BY（若适用） #

如果查询中没有使用聚合函数，GROUP BY v.id属于冗余语句（MariaDB在关闭ONLY_FULL_GROUP_BY时允许，但逻辑上无必要），直接移除即可解决别名问题并保留性能：

SELECT v.id,
       ol.name AS a,
       vlf.name AS b
FROM vacancy AS v
LEFT OUTER JOIN organisation AS o ON v.organisation_id = o.id
LEFT OUTER JOIN organisation_localisation_fallback AS ol ON o.id = ol.organisation_id and ol.language_id = 14 and ol.country_id = 19
LEFT OUTER JOIN vacancy_localisation_fallback AS vlf ON v.id = vlf.vacancy_id AND vlf.language_id = 14 AND vlf.country_id = 19;

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