优化SQL提高left join的查询速度怎么降低到1秒内
1
悬赏园豆：50
[待解决问题]
select *
from (select s.*, rownum as num
from (select
a.ID,
u1.userName as "UID",
u11.userName as Branch,
u2.userName as SagentID,
u3.userName as ThagentID,
u4.userName as FuAgentID,
u5.userName as FiveAgentID,
u6.userName as SixAgentID,
u7.userName as SevenAgentID,
u8.userName as EightAgentID,
u9.userName as NineAgentID,
u10.userName as TenAgentID,
a.TermModel,
a.MachineID,
a.CreateDate,
a.CreateBy,
a.UpdateTime,
a.UpdateBy,
a.StockState,
a.Takemachi,
a.EXt1,
a.EightFlag,
acm.agentcstmanager,
cc.cityname,
a.DEBITCARDRATE,
a.DEBITAMOUNT,
a.CREDITCARDBUSINESSRATE,
a.FROZENMONEY,
a.RETURNMONEY,
0 as Contacts,
q.POLICYFLAG,
null as mercode
FROM AgentStockMachine a
left join userInfo u1
on a."UID" = u1.ID
left join userInfo u2
on a.SagentID = u2.ID
left join userInfo u3
on a.ThagentID = u3.ID
left join userInfo u4
on a.FuAgentID = u4.ID
left join userInfo u5
on a.FiveAgentID = u5.ID
left join userInfo u6
on a.SixAgentID = u6.ID
left join userInfo u7
on a.SevenAgentID = u7.ID
left join userInfo u8
on a.EightAgentID = u8.ID
left join userInfo u9
on a.NineAgentID = u9.ID
left join userInfo u10
on a.TenAgentID = u10.ID
left join userInfo u11
on a.Branch = u11.ID
left join citycode cc
on a.CITYID = cc.ID
left join TBL_POS_TYPE_INFO st
on a.TERMMODEL = st.POS_TYPE
left join agentcustmanager acm
on a.CMID = acm.ID
left join AGENTTERMINAL q
on Upper(a.MachineID) = Upper(q.MachineID)
where 1 = 1
and st.POS_CLASS = 'traditional'
and st.AVAILABLE = '1'
and a.CreateDate between
to_date('2010-08-29 00:00:00', 'yyyy-MM-dd HH24:mi:ss') and
to_date('2020-09-04 23:59:59', 'yyyy-MM-dd HH24:mi:ss')
) s) t
where t.num between 1 and 15
这段语句怎么提高速度，AgentStockMachine 这个表测试环境40万数据，其他的表大概都是1万-2万左右，现在关联后要2秒多，怎么提高到1秒内，越快越好，因为生成线上的数据量是测试线的十倍多。
所有回答(7)
0
都是left join 而且 rownum 最后是为了限制只要前15条？
你直接 先只查 AgentStockMachine 和 TBL_POS_TYPE_INFO组装上查询条件，限制下15条，外面再去left join其他表呢
select * from
(
select * fron AgentStockMachine t1
left join TBL_POS_TYPE_INFO t2 ON t1.TERMMODEL = t2.POS_TYPE
where
limit 15
) t
left join
left join
0
inner join
0
语句没问题。
解决办法 1 所有on 连接字段加索引。（不推荐，大概挺高30%速度，但存储空间也会相应变大）
2 加冗余字段，减少 连表查询，最好的性能是不连表查询（推荐，查询时间提升最明显）
0
建议先把主表数据查出来再去关联从表，即AgentStockMachine表的条件查询和分页条数作为子查询
0
太乱，看不懂，先格式话一下吧
不乱了吧，这么直接了当，这么清晰的SQL，最后的目的就是从一个主表和其关联的子表中取出想要的字段数据，并返回前15条，数据量在千万左右
@拼命撸码的徐哥:
SELECT
*
FROM
(
SELECT
s.*,
rownum AS num
FROM
(
SELECT
a.ID,
u1.userName AS "UID",
u11.userName AS Branch,
u2.userName AS SagentID,
u3.userName AS ThagentID,
u4.userName AS FuAgentID,
u5.userName AS FiveAgentID,
u6.userName AS SixAgentID,
u7.userName AS SevenAgentID,
u8.userName AS EightAgentID,
u9.userName AS NineAgentID,
u10.userName AS TenAgentID,
a.TermModel,
a.MachineID,
a.CreateDate,
a.CreateBy,
a.UpdateTime,
a.UpdateBy,
a.StockState,
a.Takemachi,
a.EXt1,
a.EightFlag,
acm.agentcstmanager,
cc.cityname,
a.DEBITCARDRATE,
a.DEBITAMOUNT,
a.CREDITCARDBUSINESSRATE,
a.FROZENMONEY,
a.RETURNMONEY,
0 AS Contacts,
q.POLICYFLAG,
NULL AS mercode
FROM
AgentStockMachine a
LEFT JOIN userInfo u1 ON a."UID" = u1.ID
LEFT JOIN userInfo u2 ON a.SagentID = u2.ID
LEFT JOIN userInfo u3 ON a.ThagentID = u3.ID
LEFT JOIN userInfo u4 ON a.FuAgentID = u4.ID
LEFT JOIN userInfo u5 ON a.FiveAgentID = u5.ID
LEFT JOIN userInfo u6 ON a.SixAgentID = u6.ID
LEFT JOIN userInfo u7 ON a.SevenAgentID = u7.ID
LEFT JOIN userInfo u8 ON a.EightAgentID = u8.ID
LEFT JOIN userInfo u9 ON a.NineAgentID = u9.ID
LEFT JOIN userInfo u10 ON a.TenAgentID = u10.ID
LEFT JOIN userInfo u11 ON a.Branch = u11.ID
LEFT JOIN citycode cc ON a.CITYID = cc.ID
LEFT JOIN TBL_POS_TYPE_INFO st ON a.TERMMODEL = st.POS_TYPE
LEFT JOIN agentcustmanager acm ON a.CMID = acm.ID
LEFT JOIN AGENTTERMINAL q ON Upper( a.MachineID ) = Upper( q.MachineID )
WHERE
1 = 1
AND st.POS_CLASS = 'traditional'
AND st.AVAILABLE = '1'
AND a.CreateDate BETWEEN to_date ( '2010-08-29 00:00:00', 'yyyy-MM-dd HH24:mi:ss' )
AND to_date ( '2020-09-04 23:59:59', 'yyyy-MM-dd HH24:mi:ss' )
) s
) t
WHERE
t.num BETWEEN 1
AND 15
这才叫不乱。
0
降低在1秒内？数据多了不可能，单从语句上优化达不到的
0
不必要的表连接，多重表连接mysql都是嵌套for循环处理的，连接越多数据冗余越多。一般情况下
（目标数据行）/（查询数据行） 的比值越接近1，说明SQL越好。题主这里的查询数据行太多，所以会影响效率。
优化点一：如此多的表连接，这里完全可以不用表连接，都是以userInfo的ID作为关联，返回数据只有一条，可以试试这样：
优化点二：AgentStockMachine表查询基数太大，先过过滤下AgentStockMachine表，再做处理：
你这个语句，会报错的，SINGLE-ROW SUBQUERY RETURNS MORE THAN ONE ROW
单行子查询返回多行，另外我用的是ORACLE，不是MYSQL哦
