MySQL关联brelation与bintersection表的SQL优化简化需求

业务表说明 #

• intersection表：存储路口基础信息
• relation表：存储路口与路侧、路口与路口的关联关系

原SQL问题分析 #

原SQL使用了多层嵌套子查询+IN子查询的写法，会生成多次临时表，大数据量下查询性能较差，逻辑可以做等价简化提升执行效率。

优化后等价SQL #

SELECT 
    r1.source_key AS start_key,
    r2.source_key AS end_key,
    r1.target_key AS roadside_key,
    d.obj_name AS end_name,
    e.obj_name AS start_name
FROM `brelation` r1
INNER JOIN `brelation` r2 
    ON r1.target_key = r2.target_key
    AND r2.target_concept = 'roadside边'
    AND r2.rel_number = 2
INNER JOIN `brelation` r3
    ON r2.source_key = r3.target_key
    AND r3.source_key = 'c1_VbFljpol'
    AND r3.target_concept = 'node'
INNER JOIN bintersection d ON r2.source_key = d.obj_key
INNER JOIN bintersection e ON r1.source_key = e.obj_key
WHERE 
    r1.source_key = 'c1_VbFljpol'
    AND r1.target_concept = 'roadside'
    AND r1.rel_number = 1

优化点说明 #

• 去掉三层冗余嵌套子查询，减少临时表生成开销，方便MySQL优化器生成更优执行计划
• 用INNER JOIN替换IN子查询，避免子查询逐行匹配的性能损耗，数据量越大性能提升越明显
• 所有过滤条件前置到关联条件或WHERE层，减少参与关联的数据量，执行效率更高
• 逻辑和原SQL完全等价，返回结果无差异

结果验证 #

返回结果和原SQL完全一致，示例如下：

start_key   start_name end_key      roadside_key end_name
c1_VbFljpol aaaa       c1_hKwEo6JZ  c3_G2rSzUIK  bbbb
c1_VbFljpol aaaa       c1_gDUWuB4V  c3_YtKWzPy0  cccc
c1_VbFljpol aaaa       c1_uayODvZz  c3_SMz1WGl0  dddd

额外性能优化建议 #

可以给brelation表添加两个联合索引，实现覆盖索引查询无需回表，性能进一步提升：

• 联合索引1：(source_key, target_concept, rel_number, target_key) 覆盖r1、r3表的过滤和关联字段
• 联合索引2：(target_key, target_concept, rel_number, source_key) 覆盖r2表的关联和过滤字段

内容的提问来源于stack exchange，提问作者xin.chen