sql左连接速度优化
0
悬赏园豆：100
[已解决问题]
解决于 2022-11-29 17:49
SELECT
e.id AS id,
e.carNo AS carNo,
e.driverTel AS driverTel,
e.entryType AS entryType,
o.type AS outType,
ct.NAME AS carTypeName,
e.tareWgt AS tareWgt,
e.grossWgt AS grossWgt,
o.weight AS weight,
e.remark AS remark,
o.remark AS outRemark,
e.payTime AS updated,
o.created AS outTime,
e.billNo AS entryBillNo,
o.billNo AS outBillNo,
e.cardNo AS cardNo,
e.merchantName AS merchantName,
e.merchantTel AS merchantTel,
o.outCardNo AS outCardNo,
o.outBusinessName AS outBusinessName,
o.outBusinessPhone AS outBusinessPhone,
e.driverName AS driverName,
e.updateBy AS updateBy,
o.createBy AS outCreateBy,
c.carTypeId AS carTypeId,
c.error AS error,
c.remark AS errorRemark,
e.prodWgt AS prodWgt,
ct.tareWeight AS estimateTareWgt,
e.totalAmt AS totalAmt,
e.reallyAmt AS reallyAmt,
e.payedAmt AS payedAmt,
e.prodCode AS prodCode,
e.prodName AS prodName,
e.placeOrigin AS placeOrigin,
e.channel AS channel,
e.mktId AS mktId,
e.created AS created,
e.payType AS payType,
e.payTime AS payTime,
e.procStatus AS procStatus,
e.STATUS AS STATUS,
e.type AS type,
e.source AS source,
e.openInvoiced AS openInvoiced,
e.printed AS printed,
o.entryId AS entryId,
IFNULL( o.alreadyOut, 0 ) AS alreadyOut,
o.channel AS outChannel,
o.placeOrigin AS outPlaceOrigin,
o.refunded AS refunded,
o.refundAmt AS refundAmt,
o.payType AS outPayType,
o.printed AS outPrinted,
o.outRepairFeeAmt AS outRepairFeeAmt,
o.outRepairFeeProdName AS outRepairFeeProdName,
e.reserved1 AS reserved1,
e.reserved2 AS reserved2,
e.reserved3 AS reserved3,
e.reserved4 AS reserved4,
e.reserved5 AS reserved5,
e.reserved6 AS reserved6,
e.reserved7 AS reserved7,
e.reserved8 AS reserved8,
e.reserved9 AS reserved9,
e.reserved10 AS reserved10
FROM
b_entry_bill e
LEFT JOIN b_car c ON c.carno = e.carno
LEFT JOIN b_car_type ct ON e.carTypeId = ct.id
LEFT JOIN b_out_bill o ON o.entryid = e.id
WHERE
e.payTime >= '2021-04-01 16:18:00' AND e.payTime <= '2021-07-30 16:18:00'
and e.status = 2 and e.type = 1
AND '1' = '1'
最佳答案
0
如果这么写呢？
select
T.*, ……
from (
select
e.id , …
from b_entry_bill e
where e.payTime >= '2021-04-01 16:18:00' AND e.payTime <= '2021-07-30 16:18:00' and e.status = 2 and e.type = 1
) T
LEFT JOIN b_car_type ct ON T.carTypeId = ct.id
LEFT JOIN b_out_bill o ON o.entryid = T.id
收获园豆：100
我先研究一下谢谢老铁
@一生的风景: 1.payTime 加索引，status加索引，type加索引，建议status和type建联合索引，where写的时候按索引顺序查。
2.确保c.carno，e.carTypeId，o.entryid有索引，字符类型避免null。
3.按楼上的，先过滤主表后再联表。
其他回答(1)
0
主表加索引，另外看看单独查询主表慢不慢？
主表索引加了
单独查主表的时候用是2秒
@一生的风景: 先过滤主表，再关联
大佬们sql是这样的
– 一生的风景 2年前主表数据有20万条，左连的三个表一个十几万其他的就两就几万，一条sql查询出来大概要20秒左右，需要进行优化操作
– 一生的风景 2年前