基于动物序列范围关联房间与动物表的性能优化问询

现有三类数据表：

1. Table A：记录房间对应的动物范围，每个条目对应某GroupType下的起始与终止动物

2. Table B：记录每个动物在对应Type下的序列（同Type内序列从1开始）

3. Rooms表：存储房间详细信息

需求是找出每个房间对应的、处于其所有Start-End序列范围内的动物，期望输出如下：

当前通过CTE关联的方式实现了需求，但在1万+房间、34万+动物的真实数据集下性能极差，现有SQL如下：

WITH fullAnimals AS (
    SELECT DISTINCT(RoomId), a.[Animal], ta.[GroupType], a.[sequence] s1, ae.[sequence] s2
    FROM  [TableA] ta
        LEFT JOIN [TableB] a ON a.[Animal] = ta.[StartAnimal] AND a.[Type] = ta.[GroupType]
        LEFT JOIN [TableB] ae ON ae.[Animal] = ta.[EndAnimal] AND ae.[Type] = a.[Type]
)
SELECT DISTINCT(r.Id), Name, b.[Animal], b.[Type]
FROM [TableB] b
    LEFT JOIN fullAnimals ON (b.[Sequence] >= s1 AND b.[Sequence] <= s2)
    INNER JOIN [Rooms] r ON (r.[Id] = fullAnimals.[RoomId]) --this is a third table that has more data from the rooms
WHERE b.[Type] = fullAnimals.[GroupType]

性能问题分析 #

现有SQL存在几个核心瓶颈：

• 不必要的LEFT JOIN：TableA中的起止动物必然在TableB中存在（否则范围无意义），LEFT JOIN会引入无效NULL值，增加计算开销。
• DISTINCT滥用：fullAnimals中的DISTINCT完全多余，TableA与TableB的关联是一一对应关系，不会产生重复行；最终SELECT的DISTINCT是关联逻辑缺陷导致的冗余，优化后可移除。
• 无索引支撑的范围关联：序列范围匹配时没有合适索引，导致全表扫描或大量无效行匹配。

优化方案 #

方案1：简化关联逻辑，提前获取序列范围 #

先将TableA与TableB关联得到每个房间条目的起止序列，再直接匹配范围内的动物，最后关联Rooms表：

SELECT 
    r.Id AS RoomId,
    r.Name,
    b.Animal,
    b.Type
FROM 
    TableA ta
-- 关联获取起始动物的序列
INNER JOIN TableB start_b 
    ON start_b.Animal = ta.StartAnimal 
    AND start_b.Type = ta.GroupType
-- 关联获取终止动物的序列
INNER JOIN TableB end_b 
    ON end_b.Animal = ta.EndAnimal 
    AND end_b.Type = ta.GroupType
-- 匹配该Type下序列在范围内的所有动物
INNER JOIN TableB b 
    ON b.Type = ta.GroupType
    AND b.Sequence BETWEEN start_b.Sequence AND end_b.Sequence
-- 关联房间详情
INNER JOIN Rooms r 
    ON r.Id = ta.RoomId

索引优化建议 #

必须创建以下索引以大幅提升查询效率：

• TableB：创建复合覆盖索引，用于快速匹配起止动物和序列范围：

  CREATE UNIQUE NONCLUSTERED INDEX IX_TableB_Type_Animal ON TableB (Type, Animal) INCLUDE (Sequence);
  CREATE NONCLUSTERED INDEX IX_TableB_Type_Sequence ON TableB (Type, Sequence) INCLUDE (Animal);
  

• TableA：创建复合索引，用于快速定位房间对应的动物范围：

  CREATE NONCLUSTERED INDEX IX_TableA_RoomId_GroupType ON TableA (RoomId, GroupType) INCLUDE (StartAnimal, EndAnimal);
  

• Rooms：确保Id列为主键（通常默认已配置，若未配置则执行）：

  ALTER TABLE Rooms ADD PRIMARY KEY (Id);
  

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