SQL查 询优化 LEFT JOIN和INNER JOIN:
1,
连接了八个数据库表，而且全部使用LEFT JOIN，如下所示：
Resource_Resources A
LEFT 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.ClientId
LEFT JOIN Mobile_Models E ON D.ModelId = E.ModelId
LEFT JOIN dbo.Resource_Images F ON A.ResourceId = F.ResourceId
LEFT JOIN dbo.Resource_Tags G ON G.ResourceId = A.ResourceId
LEFT JOIN Website_Tags H ON G.TagId = H.TagId
LEFT 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 A
INNER 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.ResourceClientId
LEFT JOIN dbo.Resource_Tags G ON G.ResourceId = A.ResourceId
INNER JOIN dbo.Website_Tags H ON G.TagId = H.TagId
INNER JOIN dbo.Resource_Categories I ON A.CategoryId = I.CategoryId

WHERE 部分查询条件也有所减少，仅保留两个查询条件：
A.Name LIKE
H.Name LIKE

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

2,
原来的：

SELECT
 M.clientid,
 M.CardFaceID,
 N.NormalBanalce,
 D.DateWorth,
 T.TimesWorth,
 B.BookingWorth,
 B.BookingTimesManyBalance
FROM 
 (
 SELECT
   clientid,CardFaceID
 FROM 
  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 DateWorth
  FROM 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 BookingWorth,
   sum(TimesBalance) as BookingTimesManyBalance        
  FROM ccBooking  
  Group By  clientid  ) B on M.clientid=B.clientid

优化后：
SELECT
    M.clientid  ,
    M.CardFaceID,
    (SELECT sum(case IfGive when '是' then Balance*ItemZkl else Balance end) FROM ccNormal WHERE clientid=M.clientid) AS NormalBanalce,
    (SELECT sum(ConsumeBalance*ItemZkl) FROM ccDate WHERE clientid=M.clientid) AS DateWorth,
    (SELECT sum(AveragePrice*TimesBalance*ItemZKL) FROM ccTimes WHERE clientid=M.clientid) AS TimesWorth,
    (SELECT sum(PriceDiscount*TimesBalance) FROM ccBooking WHERE clientid=M.clientid) AS BookingWorth,
    (SELECT sum(TimesBalance) FROM ccBooking WHERE clientid=M.clientid) AS BookingTimesManyBalance
FROM 
    cimain M

3,
SELECT 
COUNT(DISTINCT T1.A1) + COUNT(DISTINCT T2.B1) +
COUNT(DISTINCT T3.C1)  FROM T1 
LEFT JOIN T2 on T1.A1 = T2.A1 
LEFT JOIN T3 on  T1.A1 = T3.A1 
LEFT JOIN T4 on  T3.C1 = T4.C1 
GROUP BY T1.A2, T1.A3

优化:
1、因为T1表式主表，所以
【select COUNT(DISTINCT T1.A1) from T1】和你求出的
【COUNT(DISTINCT T1.A1)】值是一样的。
2、而由于T2等是从表并且你使用了 【COUNT(DISTINCT T2.B1)】因此null值会被排除掉，实际上和下面的语句求出的值是一样的
select COUNT(DISTINCT T2.B1) from T1 inner join T2 on T1.A1 = T2.A1;
3、从上面的分 析可以看出你使用【left join】的目的只有一个就是得到【T1】表全部数据的【COUNT(DISTINCT T1.A1)】，所以试试改成下面的sql是否性能能够快些

select cnt1+cnt2+cnt3 from(
(select COUNT(DISTINCT T1.A1) cnt1 from T1 GROUP BY T1.A2, T1.A3)t1,
(select COUNT(DISTINCT T2.B1) cnt2 from T1 inner join T2 on T1.A1 = T2.A1 GROUP BY T1.A2, T1.A3)t2,
(select COUNT(DISTINCT T3.C1) cnt3 from T1 inner join T3 on T1.A1 = T3.A1 inner join T4 on  T3.C1 = T4.C1 GROUP BY T1.A2, T1.A3)t3;