基于SUBSTRING_INDEX的单表嵌套GROUP BY SQL性能优化

当前编写的嵌套查询可在样例数据上返回预期输出（O/P），以下是具体的改进思路与性能优化方案。

测试表结构与样例数据 #

CREATE TABLE function_groups (
  id int NOT NULL AUTO_INCREMENT PRIMARY KEY,
  name varchar(255) NOT NULL UNIQUE
);
INSERT INTO function_groups (name) VALUES ('f1.g1.a1');
INSERT INTO function_groups (name) VALUES ('f1.g1.a2');
INSERT INTO function_groups (name) VALUES ('f1.g1.a3');
INSERT INTO function_groups (name) VALUES ('f1.g1.a4');

INSERT INTO function_groups (name) VALUES ('f1.g2.a1');
INSERT INTO function_groups (name) VALUES ('f1.g2.a2');
INSERT INTO function_groups (name) VALUES ('f1.g2.a3');
INSERT INTO function_groups (name) VALUES ('f1.g2.a4');


INSERT INTO function_groups (name) VALUES ('f2.g1.a1');
INSERT INTO function_groups (name) VALUES ('f2.g1.a2');
INSERT INTO function_groups (name) VALUES ('f2.g1.a3');
INSERT INTO function_groups (name) VALUES ('f2.g1.a4');

INSERT INTO function_groups (name) VALUES ('f2.g2.a1');
INSERT INTO function_groups (name) VALUES ('f2.g2.a2');
INSERT INTO function_groups (name) VALUES ('f2.g2.a3');
INSERT INTO function_groups (name) VALUES ('f2.g2.a4');

预期输出 #

id  groups
f1  [{"id": "f1.g1", "actions": [{"id": 1, "name": "f1.g1.a1"}, {"id": 2, "name": "f1.g1.a2"}, {"id": 3, "name": "f1.g1.a3"}, {"id": 4, "name": "f1.g1.a4"}]}, {"id": "f1.g2", "actions": [{"id": 5, "name": "f1.g2.a1"}, {"id": 6, "name": "f1.g2.a2"}, {"id": 7, "name": "f1.g2.a3"}, {"id": 8, "name": "f1.g2.a4"}]}]
f2  [{"id": "f2.g1", "actions": [{"id": 9, "name": "f2.g1.a1"}, {"id": 10, "name": "f2.g1.a2"}, {"id": 11, "name": "f2.g1.a3"}, {"id": 12, "name": "f2.g1.a4"}]}, {"id": "f2.g2", "actions": [{"id": 13, "name": "f2.g2.a1"}, {"id": 14, "name": "f2.g2.a2"}, {"id": 15, "name": "f2.g2.a3"}, {"id": 16, "name": "f2.g2.a4"}]}]

当前待优化SQL #

SELECT 
    SUBSTRING_INDEX(t1.name, '.', 1) AS id,
    (SELECT 
            JSON_ARRAYAGG(JSON_OBJECT('id',
                                t2.id,
                                'actions',
                                (SELECT 
                                        JSON_ARRAYAGG(JSON_OBJECT('id', t3.id, 'name', t3.name))
                                    FROM
                                        function_groups t3
                                    WHERE
                                        t2.id = SUBSTRING_INDEX(t3.name, '.', 2)
                                    GROUP BY t2.id)))
        FROM
            (SELECT 
                SUBSTRING_INDEX(t2.name, '.', 2) AS id
            FROM
                function_groups t2
            GROUP BY SUBSTRING_INDEX(t2.name, '.', 2)) t2
        WHERE
            SUBSTRING_INDEX(t2.id, '.', 1) = SUBSTRING_INDEX(t1.name, '.', 1)
        GROUP BY SUBSTRING_INDEX(t2.id, '.', 1)) AS groups
FROM
    function_groups t1
GROUP BY SUBSTRING_INDEX(t1.name, '.', 1)

• 存在三层嵌套相关子查询，外层查询每返回一行，内层子查询就会重新执行一次，表被反复扫描，数据量上涨后性能会线性下降
• 重复调用SUBSTRING_INDEX做字符串截取，同一个字段的截取逻辑在WHERE、GROUP BY、关联条件中反复执行，浪费大量CPU资源
• 没有预计算分组键，所有分组、过滤逻辑都基于运行时计算的结果，无法利用索引优化

逻辑层优化（不改动表结构，直接改写SQL） #

核心思路是一次性解析所有分组键，从最细粒度逐层向上聚合，消除相关子查询，避免重复计算和重复扫表。

• 支持CTE的MySQL 8.0+版本可使用如下写法，逻辑清晰且执行效率高：

WITH group_parsed AS (
    SELECT 
        id,
        name,
        SUBSTRING_INDEX(name, '.', 1) AS top_id,
        SUBSTRING_INDEX(name, '.', 2) AS sub_id
    FROM function_groups
),
sub_group_agg AS (
    SELECT
        top_id,
        sub_id,
        JSON_ARRAYAGG(JSON_OBJECT('id', id, 'name', name)) AS actions
    FROM group_parsed
    GROUP BY top_id, sub_id
)
SELECT
    top_id AS id,
    JSON_ARRAYAGG(JSON_OBJECT('id', sub_id, 'actions', actions)) AS `groups`
FROM sub_group_agg
GROUP BY top_id;

• 不支持CTE的MySQL 5.x版本可改用嵌套子查询实现，执行逻辑和性能与CTE版本一致：

SELECT
    top_id AS id,
    JSON_ARRAYAGG(JSON_OBJECT('id', sub_id, 'actions', actions)) AS `groups`
FROM (
    SELECT
        top_id,
        sub_id,
        JSON_ARRAYAGG(JSON_OBJECT('id', id, 'name', name)) AS actions
    FROM (
        SELECT
            id,
            name,
            SUBSTRING_INDEX(name, '.', 1) AS top_id,
            SUBSTRING_INDEX(name, '.', 2) AS sub_id
        FROM function_groups
    ) t_parsed
    GROUP BY top_id, sub_id
) t_sub_agg
GROUP BY top_id;

以上改写后的SQL仅需对全表做1-2次扫描，字符串截取操作仅执行一次，无循环嵌套子查询，数据量越大性能优势越明显。

结构层优化（生产大数据量场景推荐） #

如果该表数据量达到十万级以上，推荐加存储生成列+联合索引，从根本上消除运行时字符串截取开销，分组逻辑可直接走索引：

ALTER TABLE function_groups
ADD COLUMN top_id VARCHAR(255) GENERATED ALWAYS AS (SUBSTRING_INDEX(name, '.', 1)) STORED,
ADD COLUMN sub_id VARCHAR(255) GENERATED ALWAYS AS (SUBSTRING_INDEX(name, '.', 2)) STORED,
ADD INDEX idx_top_sub (top_id, sub_id);

加完生成列后，SQL可以直接读取预计算好的top_id和sub_id字段，不需要运行时做字符串截取，分组时可直接走索引，百万级数据下性能比原SQL提升两个数量级以上。

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