超5千万行视图多表连接SQL除索引外有哪些高效优化写法？

你当前查询慢的核心原因不是单表数据量大，是写法存在两个致命问题，导致执行引擎无法利用TOP 50提前终止计算，还会产生天量的笛卡尔积冗余数据：

• 关联逻辑错误：你对ITV表写的是LEFT JOIN，但把ITV的三个过滤条件写在了WHERE子句中，数据库执行时会直接把这个外连接转为内连接，平白多做了一层无效的外连接逻辑判断。
• 执行顺序完全低效：你选择先多表关联、再聚合计算，四个大表按物料ID关联时，只要单个物料在任意一个表中有多条匹配记录，就会产生笛卡尔积——比如某个物料在ITV有20条记录、IOH有10条、SOC有10条、POH有10条，关联后单物料就会生成201010*10=20000条重复行，最后再对这些重复行做SUM聚合，等于平白多处理了上万倍的无效数据。这种写法下TOP 50完全起不到提速作用，因为数据库必须算完所有物料的全量聚合结果，才能返回前50行，根本没法提前截断执行。

具体优化写法 #

核心思路是先过滤、先聚合，再关联，把每个大表的计算先压缩到单物料1行的粒度，从根源上消除笛卡尔积。改完后每个关联子查询返回的结果集粒度和ECO表一致（单物料单行），关联过程不会产生任何重复行，整体计算量能降到原写法的1%不到，通常几秒内就能出结果。

SELECT TOP 50
       ECO.ITEMNUMBER,
       ECO.PRODUCTNAME,
       POH.WIPQTY,
       ITV.SOLDQTY,
       IOH.ONHANDQTY,
       SOC.ONORDERQTY
FROM ECO
-- 先对ITV做过滤+聚合，每个物料只返回1行汇总结果
LEFT JOIN (
    SELECT ITEMID, SUM(QTY) AS SOLDQTY
    FROM ITV
    WHERE REFERENCECATEGORY = 'REF0'
      AND INVENTLOCATIONID = 'MAIN'
      AND DATECLOSED > GETDATE() - 365
    GROUP BY ITEMID
) ITV ON ECO.ITEMNUMBER = ITV.ITEMID
-- 剩余三个表同理，先按物料聚合完再关联
LEFT JOIN (
    SELECT ITEMID, SUM(QTY) AS ONHANDQTY
    FROM IOH
    GROUP BY ITEMID
) IOH ON ECO.ITEMNUMBER = IOH.ITEMID
LEFT JOIN (
    SELECT PRODUCTNUMBER, SUM(ORIGINALORDERQTY) AS ONORDERQTY
    FROM SOC
    GROUP BY PRODUCTNUMBER
) SOC ON ECO.ITEMNUMBER = SOC.PRODUCTNUMBER
LEFT JOIN (
    SELECT ITEMNUMBER, SUM(SCHEDULEDQUANTITY) AS WIPQTY
    FROM POH
    GROUP BY ITEMNUMBER
) POH ON ECO.ITEMNUMBER = POH.ITEMNUMBER
WHERE ECO.PRODUCTGROUPID = '1'
-- 注意：如果业务允许，加ORDER BY可以让优化器更精准选择执行计划；没有特殊排序要求可以按ECO主键排序，支持执行引擎算完50条符合条件的结果就直接终止查询
-- ORDER BY ECO.ITEMNUMBER

额外可落地的优化点 #

• 如果你引用的ECO/ITV/IOH/SOC/POH是多层嵌套的业务视图，不要直接套用视图取全量字段，直接抽视图中本次查询用到的基表和字段写逻辑——多层嵌套视图经常会夹带大量本次查询不需要的关联、聚合、过滤逻辑，平白增加30%以上的计算量。
• 确认ITV的业务逻辑：如果你确实只需要返回ITV有符合条件记录的物料，直接把LEFT JOIN ITV改成INNER JOIN ITV，减少外连接的空值判断开销。
• 要是你用的数据库支持APPLY运算符（比如SQL Server、PostgreSQL 12+），可以把关联改成OUTER APPLY的写法，配合ECO表的过滤条件逐行计算匹配的物料汇总值，算够50条结果就直接终止查询，性能还能再提升一截。

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