MySQL子查询比关联查询快_有时候子查询比左连接查询速度快

最新推荐文章于 2024-06-21 10:15:45 发布

姑咕菇

最新推荐文章于 2024-06-21 10:15:45 发布

阅读量2.2k

点赞数 1

文章标签： MySQL子查询比关联查询快

本文链接：https://blog.csdn.net/weixin_33273559/article/details/113285965

版权

最近几天在优化数据库，有些数据表因为建立时候不合理导致查询的时候速度比较慢，比如三个表，三个表中数据最少的都是十万条，这些表在左联或者右联的时候速度可能需要几秒钟，再加上where条件，条件中再加or，这时候速度是非常的慢的，往往需要10秒以上，这时候可以用子查询或者union 或者union all 代替，根据情况而定

比如这个语句用子查询速度就比较快

原来的语句：

select

K.EmployeeNumber,

K.PositionName,

K.Name,

K.SkillWages,

k.AchievementsWages,

K.TotleIncome as Income,

K.TotleCashIncome as CashIncome,

(ISNULL(M0.Val2,0)+ISNULL(M1.Val2,0))TurnoverMoney,

(ISNULL(N0.Val2,0)+ISNULL(N1.Val2,0)+ISNULL(N2.Val2,0)+ISNULL(N3.Val2,0)+ISNULL(N4.Val2,0))LadderMoney

from (SELECT

A.EmployeeNumber

,A.Name

,B.PositionName

,A.SkillWages

,A.AchievementsWages

,A.ExtText2

,ISNULL((select SUM(Performance) from t_ShopSerWaiter where [Status]=1 and DeleFlag=1 and UserCode=A.UserCode and SaleType=0 and CreateTime>='{0}' and CreateTime

,ISNULL((select SUM(Performance_Cash) from t_ShopSerWaiter where [Status]=1 and DeleFlag=1 and UserCode=A.UserCode and SaleType=1 and CreateTime>='{0}' and CreateTime

,ISNULL((select SUM(Performance_Cash) from t_ShopSerWaiter where [Status]=1 and DeleFlag=1 and UserCode=A.UserCode and SaleType=4 and CreateTime>='{0}' and CreateTime

,ISNULL((select SUM(Performance_Cash) from t_ShopSerWaiter where [Status]=1 and DeleFlag=1 and UserCode=A.UserCode and SaleType=2 and CreateTime>='{0}' and CreateTime

,ISNULL((select SUM(Performance_Cash) from t_ShopSerWaiter where [Status]=1 and DeleFlag=1 and UserCode=A.UserCode and SaleType=3 and CreateTime>='{0}' and CreateTime

,ISNULL((select SUM(Performance) from t_ShopSerWaiter where [Status]=1 and DeleFlag=1 and UserCode=A.UserCode and CreateTime>='{0}' and CreateTime

,ISNULL((select SUM(Performance_Cash) from t_ShopSerWaiter where [Status]=1 and DeleFlag=1 and UserCode=A.UserCode and CreateTime>='{0}' and CreateTime

,ISNULL((select SUM(ISNULL(Income,0)) from t_ShopSerWaiter where [Status]=1 and DeleFlag=1 and UserCode=A.UserCode and CreateTime>='{0}' and CreateTime

,ISNULL((select SUM(ISNULL(CashIncome,0)) from t_ShopSerWaiter where [Status]=1 and DeleFlag=1 and UserCode=A.UserCode and CreateTime>='{0}' and CreateTime

from

[User] A left join t_UserLevel B on A.ExtText2=B.Id where A.OrgCode='{2}' and A.[Status]!=0 and A.DeleFlag=1 and A.OrgType=6 and A.DeleFlag=1 {4}

left join

(select A.[Type],A.RoleCode,B.BeginVal,B.EndVal,B.Val2 from t_ShopTurnoverPercentageSet A inner join t_ShopTurPerDetailSet B ON A.ID=B.SetCode WHERE A.DeleFlag=1 AND B.DeleFlag=1 AND A.[Type]=57 and a.ShopCode='{3}')M0 ON K.ExtText2=M0.RoleCode AND M0.BeginVal<=K.TotlePerformance AND M0.EndVal>=K.TotlePerformance

left join

(select A.[Type],A.RoleCode,B.BeginVal,B.EndVal,B.Val2 from t_ShopTurnoverPercentageSet A INNER JOIN t_ShopTurPerDetailSet B ON A.ID=B.SetCode WHERE A.DeleFlag=1 AND B.DeleFlag=1 AND A.[Type]=55 and a.ShopCode='{3}')M1 ON K.ExtText2=M1.RoleCode AND M1.BeginVal<=K.TotlePerformance_Cash AND M1.EndVal>=K.TotlePerformance_Cash

left join

(select A.[Type],A.RoleCode,B.BeginVal,B.EndVal,B.Val2 from t_ShopLadderPercentageSet A INNER JOIN t_ShopLaPerDetailSet B ON A.Id=B.SetCode WHERE A.DeleFlag=1 AND B.DeleFlag=1 AND A.[Type]=59 and a.ShopCode='{3}')N0 ON K.ExtText2=N0.RoleCode AND N0.BeginVal<=K.SerPerformance AND N0.EndVal>=K.SerPerformance

left join

(select A.[Type],A.RoleCode,B.BeginVal,B.EndVal,B.Val2 from t_ShopLadderPercentageSet A INNER JOIN t_ShopLaPerDetailSet B ON A.Id=B.SetCode WHERE A.DeleFlag=1 AND B.DeleFlag=1 AND A.[Type]=60 and a.ShopCode='{3}')N1 ON K.ExtText2=N1.RoleCode AND N1.BeginVal<=K.ProPerformance_Cash AND N1.EndVal>=K.ProPerformance_Cash

left join

(select A.[Type],A.RoleCode,B.BeginVal,B.EndVal,B.Val2 from t_ShopLadderPercentageSet A INNER JOIN t_ShopLaPerDetailSet B ON A.Id=B.SetCode WHERE A.DeleFlag=1 AND B.DeleFlag=1 AND A.[Type]=61 and a.ShopCode='{3}')N2 ON K.ExtText2=N2.RoleCode AND N2.BeginVal<=K.OpenPerformance_Cash AND N1.EndVal>=K.OpenPerformance_Cash

left join

(select A.[Type],A.RoleCode,B.BeginVal,B.EndVal,B.Val2 from t_ShopLadderPercentageSet A INNER JOIN t_ShopLaPerDetailSet B ON A.Id=B.SetCode WHERE A.DeleFlag=1 AND B.DeleFlag=1 AND A.[Type]=62 and a.ShopCode='{3}')N3 ON K.ExtText2=N3.RoleCode AND N3.BeginVal<=K.RechargePerformance_Cash AND N3.EndVal>=K.RechargePerformance_Cash

left join

(select A.[Type],A.RoleCode,B.BeginVal,B.EndVal,B.Val2 from t_ShopLadderPercentageSet A INNER JOIN t_ShopLaPerDetailSet B ON A.Id=B.SetCode WHERE A.DeleFlag=1 AND B.DeleFlag=1 AND A.[Type]=63 and a.ShopCode='{3}')N4 ON K.ExtText2=N4.RoleCode AND N4.BeginVal<=K.PlanPerformance_Cash AND N4.EndVal>=K.PlanPerformance_Cash )

修改过的语句

select

K.EmployeeNumber,

K.PositionName,

K.Name,

K.SkillWages,

k.AchievementsWages,

K.TotleIncome as Income,

K.TotleCashIncome as CashIncome,

(ISNULL((select B.Val2 from t_ShopTurnoverPercentageSet A inner join t_ShopTurPerDetailSet B ON A.ID=B.SetCode WHERE A.DeleFlag=1 AND B.DeleFlag=1 AND A.[Type]=57 and a.ShopCode='{3}' AND K.ExtText2=A.RoleCode AND B.BeginVal<=K.TotlePerformance AND B.EndVal>=K.TotlePerformance),0)

ISNULL((select B.Val2 from t_ShopTurnoverPercentageSet A inner join t_ShopTurPerDetailSet B ON A.ID=B.SetCode WHERE A.DeleFlag=1 AND B.DeleFlag=1 AND A.[Type]=55 and a.ShopCode='{3}'and K.ExtText2=A.RoleCode AND B.BeginVal<=K.TotlePerformance_Cash AND B.EndVal>=K.TotlePerformance_Cash),0)

)TurnoverMoney,

(ISNULL((select B.Val2 from t_ShopLadderPercentageSet A INNER JOIN t_ShopLaPerDetailSet B ON A.Id=B.SetCode WHERE A.DeleFlag=1 AND B.DeleFlag=1 AND A.[Type]=59 and a.ShopCode='{3}' AND K.ExtText2=A.RoleCode AND B.BeginVal<=K.SerPerformance AND B.EndVal>=K.SerPerformance),0)

ISNULL((select B.Val2 from t_ShopLadderPercentageSet A INNER JOIN t_ShopLaPerDetailSet B ON A.Id=B.SetCode WHERE A.DeleFlag=1 AND B.DeleFlag=1 AND A.[Type]=60 and a.ShopCode='{3}' AND K.ExtText2=A.RoleCode AND B.BeginVal<=K.ProPerformance_Cash AND B.EndVal>=K.ProPerformance_Cash),0)

ISNULL((select B.Val2 from t_ShopLadderPercentageSet A INNER JOIN t_ShopLaPerDetailSet B ON A.Id=B.SetCode WHERE A.DeleFlag=1 AND B.DeleFlag=1 AND A.[Type]=61 and a.ShopCode='{3}' AND K.ExtText2=A.RoleCode AND B.BeginVal<=K.OpenPerformance_Cash AND B.EndVal>=K.OpenPerformance_Cash),0)

ISNULL((select B.Val2 from t_ShopLadderPercentageSet A INNER JOIN t_ShopLaPerDetailSet B ON A.Id=B.SetCode WHERE A.DeleFlag=1 AND B.DeleFlag=1 AND A.[Type]=62 and a.ShopCode='{3}' AND K.ExtText2=A.RoleCode AND B.BeginVal<=K.RechargePerformance_Cash AND B.EndVal>=K.RechargePerformance_Cash),0)

ISNULL((select B.Val2 from t_ShopLadderPercentageSet A INNER JOIN t_ShopLaPerDetailSet B ON A.Id=B.SetCode WHERE A.DeleFlag=1 AND B.DeleFlag=1 AND A.[Type]=63 and a.ShopCode='{3}' AND K.ExtText2=A.RoleCode AND B.BeginVal<=K.PlanPerformance_Cash AND B.EndVal>=K.PlanPerformance_Cash),0))LadderMoney