如何对3张层级关联SQL表执行依赖式UNION查询替代INNER JOIN优化性能
实现方案
可以实现,以下是符合性能要求的UNION实现方案:
前置条件约定(匹配你的业务规则与示例数据)
- 自行车产品匹配规则:
PRODUCT.DATA = 0x5678 - 变速器部件匹配规则:
PART.DATA = 0x8888 - 欧洲位置匹配规则:
LOCATION.DATA = 0xBBBB
最终查询SQL
-- 查询符合条件的自行车产品 SELECT KEY_FIELD AS `KEY`, DATA FROM PRODUCT WHERE DATA = 0x5678 AND PRODUCT_NAME IN ( SELECT DISTINCT PRODUCT_NAME FROM LOCATION WHERE DATA = 0xBBBB AND PART_NAME IN ( SELECT PART_NAME FROM PART WHERE DATA = 0x8888 ) ) UNION ALL -- 查询符合条件的变速器部件 SELECT KEY_FIELD AS `KEY`, DATA FROM PART WHERE DATA = 0x8888 AND (PRODUCT_NAME, PART_NAME) IN ( SELECT DISTINCT PRODUCT_NAME, PART_NAME FROM LOCATION WHERE DATA = 0xBBBB ) AND PRODUCT_NAME IN ( SELECT PRODUCT_NAME FROM PRODUCT WHERE DATA = 0x5678 ) UNION ALL -- 查询符合条件的欧洲位置 SELECT KEY_FIELD AS `KEY`, DATA FROM LOCATION WHERE DATA = 0xBBBB AND (PRODUCT_NAME, PART_NAME) IN ( SELECT PRODUCT_NAME, PART_NAME FROM PART WHERE DATA = 0x8888 ) AND PRODUCT_NAME IN ( SELECT PRODUCT_NAME FROM PRODUCT WHERE DATA = 0x5678 ) -- 按层级排序,和示例输出顺序完全一致 ORDER BY LENGTH(`KEY`), `KEY`;
兼容性适配
如果使用的数据库不支持多列IN语法,可以将多列IN替换为等价的EXISTS写法,性能无明显差异,以部件查询的条件为例:
AND EXISTS ( SELECT 1 FROM LOCATION l WHERE l.DATA = 0xBBBB AND l.PRODUCT_NAME = PART.PRODUCT_NAME AND l.PART_NAME = PART.PART_NAME )
性能优势说明
- 完全没有INNER JOIN操作,不会产生多表关联的中间宽表膨胀,彻底避免2600万行大结果集的问题
- 所有过滤条件直接下推到单表执行,可充分利用各表的主键、持久化计算字段索引,查询效率远高于关联后过滤
- 优先过滤最细粒度的LOCATION表数据,向上层传递的过滤集合极小,子查询开销可以忽略
- 使用UNION ALL而非UNION,三个表的KEY_FIELD长度天然不同不存在重复,无需额外去重开销
- 直接返回统一的KEY+DATA格式,无需应用侧做任何关联解析
代入你提供的测试数据,上述查询返回的结果和你给出的期望输出完全一致。
内容的提问来源于stack exchange,提问作者andrewpw
相关产品推荐
相关产品推荐

