MySql left join 多表串連查詢最佳化語句

標籤：style   blog   io   color   ar   sp   資料   div   on   

先過濾條件然後再根據表串連 同時在表中建立相關查詢欄位的索引這樣在大資料多表聯集查詢的情況下速度相當快

SELECT M.*,SS.SensorCode,SS.SensorStatus,SS.ManufacturerId,SS.Electricity,SS.Voltage,SS.MinElectricity,SS.MinVoltage,SS.Temperature,SS.StatusUpdteDate,SS.UpdateStatus ,tp.PricingStrategyid,tps.FreeDuration,bat.BerthTypeId FROM(SELECT T.* , BS.ParkStatus,BS.ChangeTime ,CA.CantonName, SE.SectionName FROM(SELECT A.* ,B.BerthId,B.BerthCode,B.BerthAddress,B.BerthStatus,B.LineDirection,B.CantonId,B.SectionId FROM (SELECT AR.AreaId,AR.AreaCode,AR.AreaName FROM   SYS_Area  AS AR  WHERE 1=1   AND AR.AreaCode=‘110‘ ) A LEFT JOIN  SYS_Berth AS B ON B.AreaId=A.AreaId ) TJOIN SYS_BerthStatus AS BS ON T.BerthCode=BS.BerthCode  JOIN SYS_Canton AS CA ON T.CantonId=CA.CantonId  JOIN SYS_Section AS SE ON T.SectionId=SE.SectionId )MLEFT JOIN SYS_Sensor SS ON M.BerthCode=SS.BerthCode LEFT JOIN TRA_PricingBerth AS tp ON tp.BerthCode=M.BerthCode   LEFT JOIN TRA_PricingStrategy  AS tps ON tps.PricingStrategyId=tp.PricingStrategyId LEFT  JOIN SYS_BerthAndType AS bat ON bat.BerthCode=M.BerthCode ORDER BY BerthCode ASC

 

MySql left join 多表串連查詢最佳化語句