mysql inner和left优化_SQL查询优化 LEFT JOIN和INNER JOIN

1,

连接了八个数据库表，而且全部使用LEFT JOIN，如下所示：

Resource_Resources ALEFT JOIN Resource_Clients B ON A.ResourceId =B.ResourceIdLEFT JOIN Resource_Files C on B.ClientId=C.ClientIdLEFT 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 LIKE

C.Extend LIKE

D.ModelId =

H.Name LIKE

I.Code LIKE

E.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.ResourceIdINNER JOIN dbo.Resource_Files C on B.ClientId =C.ClientIdLEFT 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 LIKE

H.Name LIKE

经过上面的修改后，查询在1秒内完成，基本达到通过WEB访问的要求，没有长时间等待。

2,

原来的：

SELECTM.clientid,

M.CardFaceID,

N.NormalBanalce,

D.DateWorth,

T.TimesWorth,

B.BookingWorth,

B.BookingTimesManyBalanceFROM(SELECTclientid,CardFaceIDFROMcimain

) MLeft Join(SELECTclientid,sum( case when IfGive='是' then Balance * ItemZkl else Balance end) asNormalBanalceFROMccNormalGroup By clientid ) N on M.clientid=N.clientidLeft Join(SELECTclientid,sum( ConsumeBalance * ItemZkl ) asDateWorthFROMccDateGroup By clientid ) D on M.clientid=D.clientidLeft Join(SELECTclientid,sum( AveragePrice * TimesBalance * ItemZKL ) asTimesWorthFROMccTimesGroup By clientid ) T on M.clientid=T.clientidLeft Join(SELECTclientid,sum( PriceDiscount * TimesBalance ) asB