mysql inner和left优化_【转】 SQL查询优化 LEFT JOIN和INNER JOIN

1,连接了八个数据库表，而且全部使用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.CategoryId

WHERE 部分查询条件也有所减少，仅保留两个查询条件：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(   PriceD