SQL查询速度优化
1、使⽤left join⽐直接使⽤where速度快
参考：
多表使⽤left join只是把主表⾥的所有数据查询出来，其他表只查询表中的符合条件的某⼀条记录，所以速度⾮常快；⽽多表使⽤where内联，是把所有表的数据全查出来，然后进⾏⽐对，所以速度⾮常慢。
多表left join使⽤left join要注意确定哪⼀张表是主表，如果⽆法确定主表，则选择哪张表查询的字段最多，就把哪张表作为主表。
⽰例如下：
使⽤left join，同样的数据量，时间不到1秒钟！
SELECT a.projectno,MAX(a.projectname) projectname,max(a.projectMoney) projectMoney,
max(a.projectLimitYear) projectLimitYear,hcharge) monthcharge,
ame) orgname,max(d.businesstypename) businesstypename,max(e.name) name,
max(b.dicvalue) dicvalue,
min(CONVERT(varchar(100),DATEADD("DAY",jbl.DelayDays,jbl.ReportTime),23)) as period,
max(f.fiveleveltype) fiveleveltype,max(a.ProjectInfoId) ProjectInfoId,
max(g.FlowRunId) FlowRunId
FROM
(select ProjectInfoId,ProjectNo,Status,OrgId,TypeID,UserAId,IsDelete,ProjectName,ProjectMoney,ProjectLimitYear,MonthCharge from jt_biz_projectinfo) a LEFT JOIN
(select b.* from (select MAX(id) id, ProjectNo from JT_Biz_Lecture group by ProjectNo) a,
JT_Biz_Lecture b where a.id=b.id) f
on a.projectno=f.projectno
LEFT JOIN
(select DicId,IsDelete,DicValue,DicCode from jt_Base_Dictionary) b
ON a.status=b.dicId
LEFT JOIN
(select ProjectNo,DelayDays,ReportTime from Jt_Biz_LectureTemp) jbl
ON a.projectno=jbl.projectno
LEFT JOIN
(select IsDelete,OrgName,OrgId from jt_base_org) c
id
LEFT JOIN
(select ID,BusinessTypeName from d_businesstype) d
peid=d.id
LEFT JOIN
(select UserInfoId,IsDelete,Name,UserName from jt_base_userInfo) e
ON a.useraid=e.userinfoid
LEFT JOIN
(select UserId,PostId,OrgIds from JT_Base_Post_UserOrg) j
ON j.UserId=e.userinfoid
LEFT JOIN
(select PostId from JT_Base_Post) k
ON j.PostId=k.PostId
LEFT JOIN
(select FlowRunId,ProjectId from jt_flow_run) g
ON a.ProjectInfoId=g.ProjectId
LEFT JOIN
(select FlowRunId from JT_Flow_Run_Prcs) h
ON g.FlowRunId = h.FlowRunId
LEFT JOIN
(select OrgId,OrgName from JT_Base_Org) l
ON a.OrgId=l.OrgId
WHERE a.status in(82,83,84) and a.isdelete=0
and b.isdelete=0 and c.isdelete=0 and e.isdelete=0
and (l.OrgId in(null) or e.UserName='chenqf') group by a.ProjectNo;
不使⽤left join，同样数据量，时间⼤概在50秒的样⼦！
select a.projectno,max(a.projectname) projectname,max(a.projectMoney) projectMoney,
max(a.projectLimitYear) projectLimitYear,hcharge) monthcharge,
ame) orgname,max(d.businesstypename) businesstypename,max(e.name) name,
max(b.dicvalue) dicvalue,
min(CONVERT(varchar(100),DATEADD("DAY",jbl.DelayDays,jbl.ReportTime),23)) as period,
max(f.fiveleveltype) fiveleveltype,max(a.ProjectInfoId) ProjectInfoId,
max(g.FlowRunId) FlowRunId
from (select DicId,IsDelete,DicValue,DicCode from jt_Base_Dictionary) b,
(select IsDelete,OrgName,OrgId from jt_base_org) c,
(select UserInfoId,IsDelete,Name,UserName from jt_base_userInfo) e,
(select ID,BusinessTypeName from d_businesstype) d,
(select FlowRunId,ProjectId from jt_flow_run) g,
(select FlowRunId from JT_Flow_Run_Prcs) h,
(select UserId,PostId,OrgIds from JT_Base_Post_UserOrg) j,
(select PostId from JT_Base_Post) k,
(select OrgId,OrgName from JT_Base_Org) l,
(select ProjectNo,DelayDays,ReportTime from Jt_Biz_LectureTemp) jbl,
(select ProjectInfoId,ProjectNo,Status,OrgId,TypeID,UserAId,IsDelete,ProjectName,ProjectMoney,ProjectLimitYear,MonthCharge from jt_biz_projectinfo) a left join
(select b.* from (select MAX(id) id, ProjectNo from JT_Biz_Lecture group by ProjectNo) a,
JT_Biz_Lecture b where a.id=b.id) f
on a.projectno=f.projectno where a.status=b.dicId and a.status in(82,83,84)
and a.projectno=jbl.projectno id peid=d.id
and a.useraid=e.userinfoid and j.UserId=e.userinfoid and a.isdelete=0
and b.isdelete=0 and c.isdelete=0 and e.isdelete=0 and g.FlowRunId = h.FlowRunId
and (l.OrgId in(null) or e.UserName='chenqf')
group by a.ProjectNo;
2、LEFT JOIN关联表中ON,WHERE后⾯跟条件的区别
写SQL本想通过 A left join B on and 后⾯的条件来使查出的两条记录变成⼀条，奈何发现还是有两条。
后来发现  on and 不会过滤结果记录条数，只会根据and后的条件是否显⽰ B表的记录，A表的记录⼀定会显⽰。
不管and 后⾯的是A.id=1还是B.id=1，都显⽰出A表中所有的记录，并关联显⽰B中对应A表中id为1的记录或者B表中id为1的记录。
运⾏sql : select * from student s left join class c on s.classId=c.id order by s.id
运⾏sql : select * from student s left join class c on s.classId=c.id and s.name="张三" order by s.id
运⾏sql : select * from student s left join class c on s.classId=c.id and c.name="三年级三班" order by s.id
通过查询相关资料了解到：
数据库在通过连接两张或多张表来返回记录时，都会⽣成⼀张中间的临时表，然后再将这张临时表返回给⽤户。
在使⽤left jion时，on和where条件的区别如下：
1、 on条件是在⽣成临时表时使⽤的条件，它不管on中的条件是否为真，都会返回左边表中的记录。
2、where条件是在临时表⽣成好后，再对临时表进⾏过滤的条件。这时已经没有left join的含义（必须返回左边表的记录）了，条件不为真的就全部过滤掉。
假设有两张表：
表1：tab1
表2：tab2
两条SQL:
1、select * form tab1 left join tab2 on (tab1.size = tab2.size) where tab2.name=’AAA’
2、select * form tab1 left join tab2 on (tab1.size = tab2.size and tab2.name=’AAA’)
其实以上结果的关键原因就是left join,right join,full join的特殊性，不管on上的条件是否为真都会返回left或right表中的记录，full则具有left和right的特性的并集。 ⽽inner jion没这个特殊性，则条件放在on中和where中，返回的结果集是相同的。
3、Where条件中使⽤主表条件过滤速度较快
SELECT
*
FROM
USER_BORROW T1  -- 数据量2~3万条
LEFT JOIN USER T3 ON T3.OID_USER_ID = T1.OID_USER_ID
LEFT JOIN USER_DETAIL T4 ON T4.OID_USER_ID = T1.OID_USER_ID
LEFT JOIN USER_ARCHIVES T5 ON T5.OID_ARCHIVE_ID = T1.OID_ARCHIVE_ID    -- USER_ARCHIVES数据量2~3万条
LEFT JOIN PRODUCT_MST T6 ON T6.OID_PROD_ID = T5.OID_PROD_ID
LEFT JOIN TENANT T7 ON T7.OID_TENANT_ID = T5.OID_TENANT_ID
LEFT JOIN TENANT T8 ON T8.OID_TENANT_ID = T5.OID_TENANT_XS_ID
LEFT JOIN (
SELECT
OID_BORROW_ID,
SUM(REPAY_AMOUNT_TOTAL) AS FEE_AMOUNT
FROM
USER_POUNDAGE_REPAY
WHERE
REPAY_FLG = '0'
GROUP BY
OID_BORROW_ID
) T9 ON T1.BORROW_ID = T9.OID_BORROW_ID
WHERE
1 = 1
-- 此处使⽤T1.OID_TENANT_ID进⾏数据过滤查询速度很快，
-- 但是使⽤T5.OID_TENANT_ID进⾏过滤数据查询速度极慢，不确定是否跟T1为主表有关系
AND T1.OID_TENANT_ID IN (
SELECT
OT.OID_TENANT_ID
FROM
DEPARTMENT_USER DU
INNER JOIN OPERATOR_TENANT OT ON OT.OID_OPERATOR_USER_ID = DU.OID_ADMIN_USER_ID
WHERE
DU.DEL_FLG = '0'
AND DU.OID_DEPARTMENT_ID = (
SELECT
DU1.OID_DEPARTMENT_ID
FROM
DEPARTMENT_USER DU1
WHERE
DU1.OID_ADMIN_USER_ID = ?
AND DU1.DEL_FLG = '0'
)
)

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。