MariaDB 10.5.15版本中二级父子关系表的设计与高效过滤方案咨询

阿华AIGC实验室

2026-4-29

让我们一步步来解决你的问题：

关于表结构设计：无需拆分三张表，优化现有结构更合理

首先，你的场景是明确的二级层级，完全不需要拆分成三张表——反而当前的children数组字段是反范式的设计，在MariaDB里会带来维护和查询的效率问题（比如修改子级列表要更新数组、查询子级时无法利用索引）。

更合理的方案是简化为单表的邻接表模型：

移除children字段，仅保留position_id、example_value和parent字段
parent字段存储父级的position_id，NULL表示该记录是父级或独立记录
父级记录的parent为NULL，且存在子级记录（即有其他记录的parent指向它）
独立记录的parent为NULL，且没有任何子级记录指向它

优化后的表结构示例（用DDL表示）：

CREATE TABLE positions (
    position_id INT PRIMARY KEY,
    example_value INT,
    parent INT NULL,
    FOREIGN KEY (parent) REFERENCES positions(position_id)
);

这种设计既符合关系数据库范式，又能清晰维护你的二级层级关系，后续查询和维护都会更高效。

高效实现关联组过滤的查询方案

你想要的“只要组内任意一条符合条件就保留整个组”的需求，可以通过一次UNION查询完成，不需要多次查询和应用层去重。核心思路是：

先找出所有直接符合过滤条件的记录
找出这些符合条件记录的父级记录（如果有的话）
找出这些符合条件记录的子级记录（如果有的话）
将这三部分结果合并并自动去重

以你给出的example_value > 10过滤条件为例，完整的SQL查询如下：

-- 1. 直接符合条件的记录
SELECT position_id, example_value, parent
FROM positions
WHERE example_value > 10

-- 2. 符合条件记录的父级
UNION
SELECT p.position_id, p.example_value, p.parent
FROM positions p
INNER JOIN positions matched ON p.position_id = matched.parent
WHERE matched.example_value > 10

-- 3. 符合条件记录的子级
UNION
SELECT c.position_id, c.example_value, c.parent
FROM positions c
INNER JOIN positions matched ON c.parent = matched.position_id
WHERE matched.example_value > 10

为什么这个方案高效？

所有逻辑在数据库层面完成，避免了应用层多次查询和数据传输的开销

可以通过添加索引进一步加速：

-- 给parent字段建索引，加速父子关联查询
CREATE INDEX idx_positions_parent ON positions(parent);
-- 给过滤字段example_value建索引，加速条件筛选
CREATE INDEX idx_positions_example_value ON positions(example_value);

有了这两个索引，数据库可以快速定位符合条件的记录，以及对应的父/子记录，查询性能会大幅提升。