我需要你的帮助来优化查询，我使用mysql mariadb。查询的任务是获取一些数据以使用ui。我设置为空的一些数据，因为例子。查询是:
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添加了新的索引，它被修复了。但是我不知道如何修改前两行。
感谢有些问题!
这是什么??and ca.firstname=ifnull(NULL, ca.firstname)。如果您不需要测试，则构建不含测试的查询。
(2)避免@Variables…SET @hascustomization=NULL那么WHERE @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
)
(等)
(3)当您重新放入分页时，重写查询。在进行分页之前进行所有的join是非常低效的。相反，只需做最小的工作来查找下一个，例如，10个id，然后执行JOIN来获取其余的信息—这次为每个JOIN只查找10个项。
(4)所有这些之后，开始一个新的问题，显示修改后的查询;我将对索引(通常是"复合")提出建议，以进一步提高性能。在这个问题中一定要包括SHOW CREATE TABLE和EXPLAIN SELECT ...。