如何优化此MySQL查询？它已经有分页，但我没有在这里添加

Question

我需要您的帮助来优化查询，我正在使用mysql mariadb。查询的工作是获取一些使用UI的数据。 我设置为null一些数据，因为示例。查询是：

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

解释成本： 成本

我以黄色标记了问题点。 另外，我为Bundlecode添加了新的索引，它已修复。但是我不知道如何修复前两行。

谢谢

Answer 1

一些问题！

（1）这是什么？ 和ca.firstname = ifnull（null，ca.firstname）。如果您不需要测试，请在没有测试的情况下构建查询。

（2）避免@Variables ... set @hascustomization = null然后其中@hascustomization = ...总是会失败。也就是说，null不等于0或1。

不要试图解决该问题，而是通过即时构造查询来摆脱@Variables。这将[可能大大]有助于优化查询。

更具体地说，要删除

    join  
            (
                SELECT  @categoryid=NULL, @brandid=NULL, @merchantwarehouseid=NULL,
                        @hascustomization=NULL, @iscustomizationrequested=NULL,
                        @hassuborder=NULL

，然后简化

    (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
           )

（etc）

（3）当您重新放上分页时，重写查询。在进行分页之前，要拥有所有的加入效率很低。取而代之的是，要尽量减少努力，以便找到下一个，例如10个ID，然后然后进行连接以获取其余信息 - 这次仅查找每个加入的10个项目。

（4）毕竟，开始一个新问题，显示了修订的查询；我将提出索引（通常为“复合”）的建议，以进一步提高性能。请确保包含在该问题中显示“创建表”和在该问题中说明选择...。