MariaDB下复杂MySQL多表关联查询性能优化咨询

• 业务场景：为前端UI提供订单列表数据，SQL已实现分页逻辑（示例省略分页部分），测试时将部分筛选参数置为NULL便于复现。目前已为bundlecode字段新增索引解决了对应性能瓶颈，仍有两处核心性能问题未修复。

待优化原始SQL #

select  q.*, 
                (case when  q.numberofcustomizations > 0 then 1 else 0 end) hascustomization, 
                concat_ws(' - ', q.productname, q.variant) productnamewithvariant 
            from ( 
            select ca.lastname, 
                oh.orderid, 
                oh.orderno, 
                oh.datecreated, 
                p.productid, concat_ws(' ', b.name, p.model) as productname, 
                (case when pv.variantid is not null then concat_ws(' / ', pv.type1, pv.type2) end) variant, 
                ifnull(concat(' / ',ml.sku), case when pv.variantid is not null then ifnull(concat(' / ',pv.sku),concat(' / ',p.sku)) else concat(' / ',p.sku) end) as sku2,(case when pv.variantid is not null then ifnull(concat(' / ',pv.barcode),concat(' / ',p.barcode))  else concat(' / ',p.barcode) end) as barcode2, 
                ml.uuid, ml.merchantwarehouseid, 
                p.sku, 
                mo.lineid as merchantorderid,  
                mo.orderlineno,mo.pending,  
                ol.quantity, ol.comment, 
                ifnull((select sum(numberofitemshipped) from merchant_order_shipment mos where mos.merchantorderlineid=mo.lineid),0) as numberofitemshipped,  
                ifnull((select sum(numberofitemshipped) from merchant_order_shipment mos where mos.merchantorderlineid=mo.lineid and mos.`status`='delivered'),0) as numberofitemdelivered,  
                ol.price, 
                ol.bundlecode, 
                concat_ws(' ',bb.name, pb.model) as bundleproductname, 
                mo.statuscode, 
                os.isopenorder, 
                os.name as status,  p.barcode, 
                p.manufactureritemcode, 
                r.fullsizeurl, 
                r.thumbnailsizeurl, 
                sa.postalcode as shipping_postalcode, 
                sa.countrycode as shipping_countrycode, 
                (select count(0) from order_line_property olp where olp.orderid=mo.orderid and olp.lineno=mo.orderlineno) numberofcustomizations, 
                (select count(0) from order_line_property olp where olp.orderid=mo.orderid and olp.lineno=mo.orderlineno and olp.value is null) numberofcustomizationrequests, 
                case when exists(select null from order_incident oi where oi.orderid=mo.orderid and oi.orderlineno=mo.orderlineno) then 1 else 0 end hasincident,  
                (ifnull((select sum(ols.price) from order_line ols where ols.orderid=oh.orderid and ols.feetypecode='shipment'),0) / (select count(distinct pm.merchantid) from order_line olm join product pm on pm.productid=olm.productid where olm.orderid=oh.orderid) ) as shippingfee,  
                (case when olg.giftfrom is not null then 1 else 0 end) hasgiftnote
            from merchant_order mo 
            join merchant_listing ml on ml.merchantlistingid=mo.merchantlistingid 
            join order_header oh on oh.orderid=mo.orderid 
            join address sa on sa.addressid=oh.addressid 
            join order_line ol on ol.orderid=oh.orderid and ol.lineno=mo.orderlineno 
            join order_status os on os.statuscode=mo.statuscode and os.isvalidorder=1 
            join product p on p.productid=ol.productid  
            join customer c on c.customerid=oh.customerid 
            join address ca on ca.addressid=c.addressid 
            left outer join product_variant pv on pv.variantid=ml.variantid  
            left outer join brand b on b.brandid=p.brandid 
            left outer join product_resource pr on pr.productid=p.productid and pr.isdefault=1  
            left outer join resource r on r.resourceid=pr.resourceid  
            left outer join order_line_gift olg on olg.orderid=mo.orderid and olg.lineno=mo.orderlineno  
            left outer join category cat on cat.categoryid=p.categoryid 
            left outer join product pb on pb.bundlecode=ol.bundlecode 
            left outer join brand bb on bb.brandid=pb.brandid 
            join (select @categoryid=NULL, @brandid=NULL, @merchantwarehouseid=NULL, @hascustomization=NULL, @iscustomizationrequested=NULL, @hassuborder=NULL) params on 1=1
            where 
                 mo.statuscode=ifnull(NULL,mo.statuscode) 
                and oh.orderno=ifnull(NULL, oh.orderno) 
                and c.customerno=ifnull(NULL, c.customerno) 
                and ca.firstname=ifnull(NULL, ca.firstname) 
                and ca.lastname=ifnull(NULL, ca.lastname) 
                and ca.email=ifnull(NULL, ca.email) 
                and ml.productid=ifnull(NULL, ml.productid) 
                and (case when @categoryid is null then 1  
                    when cat.categoryid=@categoryid then 1 
                    when cat.overcategoryid=@categoryid then 1 end) 
                and (case when @brandid is null then 1  
                    when p.brandid=@brandid and b.isactive=1 then 1 
                    end) 
                and (case when @merchantwarehouseid is null then 1 
                    when ml.merchantwarehouseid=@merchantwarehouseid then 1 end) 
                and concat(ifnull(oh.originref,'-'),' / ',ifnull(oh.originsource,'-'))=ifnull(NULL,concat(ifnull(oh.originref,'-'),' / ',ifnull(oh.originsource,'-'))) 
                and mo.pending=ifnull(NULL, mo.pending) 
                and oh.datecreated between ifnull(NULL, oh.datecreated) and ifnull(NULL, oh.datecreated) 
            ) q 
            where 
                (case when @hascustomization is null then 1 
                    when q.numberofcustomizations > 0 and @hascustomization = 1 then 1 
                    when q.numberofcustomizations = 0 and @hascustomization = 0 then 1 
                    end)  
                and (case when @iscustomizationrequested is null then 1 
                    when q.numberofcustomizationrequests > 0 and @iscustomizationrequested = 1 then 1 
                    when q.numberofcustomizationrequests = 0 and @iscustomizationrequested = 0 then 1 
                    end)  
            order by 1

核心性能瓶颈定位 #

• 第一瓶颈：SELECT层关联子查询逐行执行
  原SQL中发货数量、妥投数量、定制项统计、售后标记、订单运费的计算全部使用写在SELECT子句中的关联子查询，这类子查询会对外层结果集的每一行单独执行一次扫描，外层返回数据量越大，性能损耗线性升高。其中运费计算逻辑对同一个订单重复扫描两次order_line和product表，同一订单下有多条商品行时会重复计算多次，存在大量无效IO。
• 第二瓶颈：反索引写法导致全表扫描
  原SQL大量使用col = IFNULL(NULL, col)这类恒真判断、CASE WHEN包裹过滤条件、对字段做拼接函数计算后过滤的写法，会导致MariaDB优化器无法命中字段上的已有索引，只能走全表扫描+内存过滤。额外关联的无意义params参数表，也会在部分场景下干扰优化器选择最优执行计划。
• 额外冗余问题：排序使用order by 1的列序号写法，无法利用排序索引；部分左连接没有做结果集限制，容易产生重复数据，放大中间结果集大小。

针对性优化方案 #

1. 替换逐行子查询为预聚合CTE #

将所有按订单、订单行维度聚合的统计值，提前通过CTE按关联键分组计算完成，再和主查询做左连接，从根源上避免逐行扫描：

WITH mos_agg AS (
    -- 预聚合每个订单行的发货、妥投数量
    SELECT 
        merchantorderlineid,
        SUM(numberofitemshipped) AS numberofitemshipped,
        SUM(CASE WHEN status = 'delivered' THEN numberofitemshipped ELSE 0 END) AS numberofitemdelivered
    FROM merchant_order_shipment
    GROUP BY merchantorderlineid
),
olp_agg AS (
    -- 预聚合每个订单行的定制项统计
    SELECT 
        orderid,
        lineno,
        COUNT(0) AS numberofcustomizations,
        COUNT(CASE WHEN value IS NULL THEN 1 END) AS numberofcustomizationrequests
    FROM order_line_property
    GROUP BY orderid, lineno
),
oi_agg AS (
    -- 预标记存在售后事件的订单行
    SELECT DISTINCT orderid, orderlineno FROM order_incident
),
ship_fee_agg AS (
    -- 预计算每个订单的总运费、分摊商家数，避免重复计算
    SELECT 
        ol.orderid,
        SUM(CASE WHEN ol.feetypecode = 'shipment' THEN ol.price ELSE 0 END) AS total_ship_fee,
        COUNT(DISTINCT p.merchantid) AS merchant_count
    FROM order_line ol
    LEFT JOIN product p ON p.productid = ol.productid
    GROUP BY ol.orderid
)

主查询直接关联上述CTE取数即可，运费直接通过IFNULL(sfa.total_ship_fee / NULLIF(sfa.merchant_count, 0), 0)计算，无需重复扫描表。

2. 重构过滤逻辑释放索引能力 #

• 删除所有col = IFNULL(NULL, col)、oh.datecreated between ifnull(NULL, oh.datecreated) and ifnull(NULL, oh.datecreated)这类恒真无意义判断，改用动态SQL拼接逻辑：参数为NULL时不拼接对应过滤条件，参数有值时才拼接col = ?的等值/范围条件，让优化器可以直接命中字段索引。
• 将所有CASE WHEN包裹的过滤条件改为普通布尔逻辑，例如分类过滤改写为(@categoryid IS NULL OR cat.categoryid = @categoryid OR cat.overcategoryid = @categoryid)，品牌、仓库、定制项的过滤都按该逻辑改写，避免CASE WHEN阻断索引匹配。
• 删除concat(ifnull(oh.originref,'-'),' / ',ifnull(oh.originsource,'-'))的拼接过滤逻辑，改为直接对oh.originref、oh.originsource两个字段单独判断，避免字段上的函数计算导致索引失效。
• 删除无意义的params子查询，直接通过会话变量或预编译传参，减少对优化器的干扰。

3. 精简关联与排序逻辑 #

• 关联product_resource、resource表取默认商品图时，在关联条件中增加LIMIT 1限制，避免一个商品绑定多个默认资源时产生重复行，放大结果集。
• 明确指定排序字段（建议按oh.datecreated DESC, oh.orderid DESC排序），替换原有的order by 1写法，配合排序字段的联合索引避免filesort。
• 当分类、品牌等筛选参数为NULL时，直接跳过对应表的关联，减少JOIN的表数量，降低中间结果集大小。

配套索引优化建议 #

配合SQL改写，补充以下覆盖索引进一步降低查询IO：

• merchant_order_shipment: 建立联合索引(merchantorderlineid, status, numberofitemshipped)，覆盖发货数量聚合查询，无需回表
• order_line_property: 建立联合索引(orderid, lineno, value)，覆盖定制项统计查询
• order_incident: 建立联合索引(orderid, orderlineno)，覆盖售后事件存在性判断
• 主链路表补充联合索引：merchant_order(orderid, merchantlistingid, statuscode, pending, orderlineno)、order_header(orderid, customerid, addressid, datecreated, orderno)、order_line(orderid, lineno, productid, feetype, price)

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