SQL语句优化几种情况.docxVIP

SQL查询优化 LEFT JOIN和INNER JOIN1,连接了八个数据库表，而且全部使用LEFT JOIN，如下所示：Resource_Resources ALEFT JOIN Resource_Clients B ON A.ResourceId = B.ResourceId?LEFT JOIN Resource_Files C on B.ClientId=C.ClientId?LEFT JOIN Resource_ClientsModels D ON B.ClientId = D.ClientIdLEFT JOIN Mobile_Models E ON D.ModelId = E.ModelIdLEFT JOIN dbo.Resource_Images F ON A.ResourceId = F.ResourceIdLEFT JOIN dbo.Resource_Tags G ON G.ResourceId = A.ResourceIdLEFT JOIN Website_Tags H ON G.TagId = H.TagIdLEFT JOIN dbo.Resource_Categories I ON A.CategoryId = I.CategoryId WHERE 部分有四个查询条件A.Name LIKEC.Extend LIKED.ModelId =H.Name LIKEI.Code LIKEE.Name LIKE此时的查询比较费力，经历了将近一分钟的漫长查询，通过WEB访问已经超时。只好将部分查询条件去掉。其中A表20000条记录，B表记录数大于A表记录数，C表记录数大于B表记录数，H表记录数较大经过修改后，表连接减少为六个，将部分LEFT JOIN改为INNER JOIN，如下所示：Resource_Resources AINNER JOIN dbo.Resource_Clients B ON A.ResourceId = B.ResourceId?INNER JOIN dbo.Resource_Files C on B.ClientId = C.ClientId?LEFT JOIN dbo.Resource_ClientsModels D ON B.ClientId = D.ResourceClientIdLEFT JOIN dbo.Resource_Tags G ON G.ResourceId = A.ResourceIdINNER JOIN dbo.Website_Tags H ON G.TagId = H.TagIdINNER JOIN dbo.Resource_Categories I ON A.CategoryId = I.CategoryIdWHERE 部分查询条件也有所减少，仅保留两个查询条件：A.Name LIKEH.Name LIKE经过上面的修改后，查询在1秒内完成，基本达到通过WEB访问的要求，没有长时间等待。2,原来的：SELECT?M.clientid,?M.CardFaceID,?N.NormalBanalce,?D.DateWorth,?T.TimesWorth,?B.BookingWorth,B.BookingTimesManyBalanceFROM?(?SELECT??? clientid,CardFaceIDFROM?cimain?) M???Left Join?(SELECT??? clientid,??? sum( case when IfGive=是 then Balance * ItemZkl else Balance end) as NormalBanalce?FROM ccNormal???Group By clientid ) N on M.clientid=N.clientid?Left Join???????????(SELECT??? clientid,??? sum(?? ConsumeBalance * ItemZkl ) as DateWorthFROM ccDate????Group By clientid ) D on M.clientid=D.clientid?Left Join?(SELECT??? clientid,??? sum(?? AveragePrice * TimesBalance * ItemZKL?? ) as TimesWorth??????FROM ccTimes???Group By clientid ) T on M.clientid=T.clientid?Left Join(SELECT??? clientid,??? sum(?? PriceDiscount * TimesBalance?? ) as Booking