关联日志表查询位置层级时性能问题求助

问题背景 #

现有两张业务表：

1. location表（约2万条记录）：字段包含id、Name、is_active、level1、level2、level3、level4
2. log表（约500万条记录）：字段包含id、Name、location_id

需求为仅获取存在日志记录的位置及其完整父层级，但当前使用递归查询（含CTE写法）耗时超10分钟，现有查询语句如下：

SELECT *
FROM   location
START WITH id IN (SELECT DISTINCT location_id FROM log_response)
CONNECT BY PRIOR name
           = name || '>' || COALESCE(PRIOR level4, PRIOR level3, PRIOR level2)
ORDER BY NAME

WITH cte(location_id) AS (
     SELECT DISTINCT location_id FROM log_response
)
SELECT *
FROM   location
START WITH id IN (SELECT location_id FROM cte)
CONNECT BY PRIOR name
           = name || '>' || COALESCE(PRIOR level4, PRIOR level3, PRIOR level2)
ORDER BY NAME

优化方案 #

1. 核心字段加索引，消除全表扫描 #

• 给log表的location_id创建普通索引，加速去重查询：

  CREATE INDEX idx_log_location_id ON log(location_id);
  

• 给location表的id确认主键（未设置则添加），同时针对递归匹配的字段创建组合索引，减少递归时的表扫描：

  CREATE INDEX idx_location_hierarchy ON location(Name, level2, level3, level4);
  

2. 重构递归匹配逻辑，避免字符串运算 #

原CONNECT BY中的字符串拼接（name || '>' || ...）会导致索引失效，且字符串运算性能极低。需基于层级字段直接匹配，示例如下（需根据实际层级关系调整）：

WITH cte(location_id) AS (
    SELECT DISTINCT location_id FROM log
)
SELECT *
FROM   location
START WITH id IN (SELECT location_id FROM cte)
CONNECT BY 
    PRIOR level1 = level1
    AND (PRIOR level2 = level2 OR PRIOR level2 IS NULL)
    AND (PRIOR level3 = level3 OR PRIOR level3 IS NULL)
    AND PRIOR level4 IS NULL
ORDER BY NAME

核心思路是用字段直接对比替代字符串拼接，让索引能生效。

3. 提前过滤无效数据，减少递归范围 #

如果仅需活跃位置，在查询中先过滤is_active=1的记录，减少递归处理的数据集：

WITH cte(location_id) AS (
    SELECT DISTINCT location_id FROM log
)
SELECT l.*
FROM   location l
WHERE l.is_active = 1
START WITH l.id IN (SELECT location_id FROM cte)
CONNECT BY -- 替换为优化后的匹配逻辑
ORDER BY l.NAME

4. 新增父ID字段，简化递归逻辑 #

给location表新增parent_id字段，预计算每个位置的父级ID，让递归逻辑更高效：

-- 初始化parent_id（根据层级规则计算，示例逻辑需适配实际业务）
UPDATE location 
SET parent_id = (
    SELECT id FROM location l2 
    WHERE l2.level1 = level1 
      AND (l2.level2 = level2 OR (l2.level2 IS NULL AND level2 IS NOT NULL))
      AND l2.level3 IS NULL AND level3 IS NOT NULL
);

-- 创建parent_id索引
CREATE INDEX idx_location_parent_id ON location(parent_id);

-- 优化后的查询
WITH cte(location_id) AS (
    SELECT DISTINCT location_id FROM log
),
hierarchy AS (
    SELECT id, Name, level1, level2, level3, level4, parent_id
    FROM location
    WHERE id IN (SELECT location_id FROM cte)
    UNION ALL
    SELECT l.id, l.Name, l.level1, l.level2, l.level3, l.level4, l.parent_id
    FROM location l
    JOIN hierarchy h ON l.id = h.parent_id
)
SELECT DISTINCT * FROM hierarchy
ORDER BY Name;

5. 预计算层级关系，用中间表加速查询 #

如果层级结构变动不频繁，定时预计算所有位置的层级关系到中间表location_hierarchy，查询时直接关联即可：

-- 创建中间表并预填充数据
CREATE TABLE location_hierarchy (child_id INT, parent_id INT);
INSERT INTO location_hierarchy(child_id, parent_id)
-- 子级关联父级
SELECT l1.id, l2.id
FROM location l1
JOIN location l2 ON l2.level1 = l1.level1 
                  AND (l2.level2 = l1.level2 OR (l2.level2 IS NULL AND l1.level2 IS NOT NULL))
                  AND (l2.level3 = l1.level3 OR (l2.level3 IS NULL AND l1.level3 IS NOT NULL))
                  AND l2.level4 IS NULL
UNION ALL
-- 包含位置自身
SELECT id, id FROM location;

-- 给中间表加索引
CREATE INDEX idx_hierarchy_child ON location_hierarchy(child_id);
CREATE INDEX idx_hierarchy_parent ON location_hierarchy(parent_id);

-- 最终查询
WITH cte(location_id) AS (
    SELECT DISTINCT location_id FROM log
)
SELECT DISTINCT l.*
FROM location l
JOIN location_hierarchy h ON l.id = h.parent_id
JOIN cte ON h.child_id = cte.location_id
WHERE l.is_active = 1
ORDER BY l.Name;

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