首页 新闻 搜索 专区 学院

sql左连接速度优化

0
悬赏园豆:100 [待解决问题]

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'

sql
一生的风景的主页 一生的风景 | 初学一级 | 园豆:80
提问于:2021-07-30 16:48

大佬们sql是这样的

一生的风景 4个月前

主表数据有20万条,左连的三个表一个十几万其他的就两就几万,一条sql查询出来大概要20秒左右,需要进行优化操作

一生的风景 4个月前
< >
分享
所有回答(2)
0

主表加索引,另外看看单独查询主表慢不慢?

kaixin0497 | 园豆:11 (初学一级) | 2021-07-30 17:30

主表索引加了

支持(0) 反对(0) 一生的风景 | 园豆:80 (初学一级) | 2021-07-30 17:32

单独查主表的时候用是2秒

支持(0) 反对(0) 一生的风景 | 园豆:80 (初学一级) | 2021-07-30 17:33

@一生的风景: 先过滤主表,再关联

支持(0) 反对(0) kaixin0497 | 园豆:11 (初学一级) | 2021-07-30 17:48
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
jzblive | 园豆:257 (菜鸟二级) | 2021-07-30 17:43

我先研究一下谢谢老铁

支持(0) 反对(0) 一生的风景 | 园豆:80 (初学一级) | 2021-07-30 17:49

@一生的风景: 1.payTime 加索引,status加索引,type加索引,建议status和type建联合索引,where写的时候按索引顺序查。

2.确保c.carno,e.carTypeId,o.entryid有索引,字符类型避免null。

3.按楼上的,先过滤主表后再联表。

支持(0) 反对(0) 去海边生活 | 园豆:62 (初学一级) | 2021-08-19 14:33
清除回答草稿
   您需要登录以后才能回答,未注册用户请先注册