SQL查询优化外连接和内连接
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;
4,Left.join优化规则的研究.doc:
⼀、概述
  对于left join的优化，是应⽤开发⼈员、数据库内核开发⼈员关注的问题之⼀。
  应⽤开发⼈员关注是因为：并不是每个数据库的内核都⽀持left join的内部转化，这时候需要应⽤开发⼈员进⾏⼿⼯地转化。
  内核开发⼈员关注是因为：并不假定每个应⽤开发⼈员都能够熟练地将left join转化掉。因此数据库有必要对这种情况，进⾏数据库内部的优化。
  我当初对left join进⾏分析归纳，后来阅读mysql时发现 sql_select.cpp⽂件中的simplify_joins()函数的实现⽅法也是这样的，⼤家可以参考该函数。
  ⼆、left join优化规则的研究
  t1 left t2 l1
  对于类似的表达式，在什么样的情况下才可以去掉left join呢?
  我们⾸先创建三张表：
  create table t1(c1 int,c2 int);
  create table t2(d1 int,d2 int);
  create table t3(e1 int,e2 int);
  2.1 优化的基本策略
  对于left join的查询语句，⽐如：
  select * from t1 left join t2 on t1.c1=t2.d2 where condition1 [{and conditonN}];(N的取值为2,3,……) (语句1)
  什么情况下，才能优化为语句：
  select * from t1 inner join t2 on on t1.c1=t2.d2 where condition1 [{and conditonN}]; (语句2)
  备注：语句2等价于语句：
  select * from t1,t2 where t1.c1=t2.d2 and condition1 [{and conditonN}]; (语句3)
  回答：
  只要where中的⾄少有⼀个conditionK(N的取值为 1,2,……)满⾜如下⾮NULL条件，就可以将语句1优化为语句2(语句3)：
  1)conditionK包含t2表的列(任意列)
  2)conditionK的类型只要不为： t2.column is null。
  其它的任何类型都⾏：⽐如t2.d2=t1.c2，再⽐如t2.d2 is not null。
  例1：
  select * from t1 left join t2 on t1.c1=t2.d2 where t2.d1=2; (t2.d1=2满⾜⾮NULL条件，可以优化)
  <==>等价于： select * from t1 inner join t2 on t1.c1=t2.d2 where t2.d1=2;
  <==>等价于: select * from t1,t2 where t1.c1=t2.d2 and t2.d1=2;
  例 2：select * from t1 left join t2 on t1.c1=t2.d2 where t2.d1+1>t1.c1; (t2.d1+1>t1.c1满⾜⾮NULL条件，可以优化)
  <==>等价于： select * from t1 inner join t2 on t1.c1=t2.d2 where t2.d1+1>t1.c1;
  <==>等价于： select * from t1,t2 where t1.c1=t2.d2 and t2.d1+1>t1.c1;
  2.2思路扩展
a left join
b on condition1 {and conditionM}
  left join c on contion2_1 {and contion2_N}
  --优化的思路和上⽂提出的观点完全⼀样。
  例3：
  select * from t1 left join t2 on c1=d1 left join t3 on d2=e1 where e1=1; (e1满⾜⾮NULL条件，可以优化，甚⾄这⾥可以为：
e2 in (select ……))
  <==>等价于：select * from t1 left join t2 on c1=d1 inner join t3 on d2=e1 where e1=1; //inner转换
  <==>等价于：select * from t1 left join t2 on c1=d1,t3 where d2=e1 and e1=1; //等价调整，然后(d2=e1满⾜⾮NULL条件，可以优化)
  <==>等价于：select * from t1 inner join t2 on c1=d1,t3 where d2=e1 and e1=1; //inner转换
  <==>等价于：select * from t1,t2,t3 where c1=d1 and d2=e1 and e1=1;
5,
Sybase SQL Server索引的使⽤和优化:
本⽂就SQL Server索引的性能问题进⾏了⼀些分析和实践。
  ⼀、聚簇索引(clustered indexes)的使⽤
  聚簇索引是⼀种对磁盘上实际数据重新组织以按指定的⼀个或多个列的值排序。由于聚簇索引的索引页⾯指针指向数据页⾯，所以使⽤聚簇索引查数据⼏乎总是⽐使⽤⾮聚簇索引快。每张表只能建⼀个聚簇索引，并且建聚簇索引需要⾄少相当该表120%的附加空间，以存放该表的副本和索引中间页。建⽴聚簇索引的思想是：
  1、⼤多数表都应该有聚簇索引或使⽤分区来降低对表尾页的竞争，在⼀个⾼事务的环境中，对最后⼀页的封锁严重影响系统的吞吐量。
  2、在聚簇索引下，数据在物理上按顺序排在数据页上，重复值也排在⼀起，因⽽在那些包含范围检查(between、<、<=、& gt;、>=)或使⽤group by或order by的查询时，⼀旦到具有范围中第⼀个键值的⾏，具有后续索引值的⾏保证物理上毗连在⼀起⽽不必进⼀步搜索，避免了⼤范围扫描，可以⼤⼤提⾼查询速度。
  3、在⼀个频繁发⽣插⼊操作的表上建⽴聚簇索引时，不要建在具有单调上升值的列(如IDENTITY)上，否则会经常引起封锁冲突。
  4、在聚簇索引中不要包含经常修改的列，因为码值修改后，数据⾏必须移动到新的位置。
  5、选择聚簇索引应基于where⼦句和连接操作的类型。聚簇索引的侯选列是：
    1、主键列，该列在where⼦句中使⽤并且插⼊是随机的。
    2、按范围存取的列，如pri_order > 100 and pri_order < 200。
    3、在group by或order by中使⽤的列。
    4、不经常修改的列。
    5、在连接操作中使⽤的列。
sql优化的几种方式  ⼆、⾮聚簇索引(nonclustered indexes)的使⽤
  SQL Server缺省情况下建⽴的索引是⾮聚簇索引，由于⾮聚簇索引不重新组织表中的数据，⽽是对每⼀⾏存储索引列值并⽤⼀个指针指向数据所在的页⾯。换句话说⾮聚簇索引具有在索引结构和数据本⾝之间的⼀个额外级。⼀个表如果没有聚簇索引时，可有250个⾮聚簇索引。每个⾮聚簇索引提供访问数据的不同排序顺序。在建⽴⾮聚簇索引时，要权衡索引对查询速度的加快与降低修改速度之间的利弊。另外，还要考虑这些问题：
  1、索引需要使⽤多少空间。
  2、合适的列是否稳定。
  3、索引键是如何选择的，扫描效果是否更佳。
  4、是否有许多重复值。
  对更新频繁的表来说，表上的⾮聚簇索引⽐聚簇索引和根本没有索引需要更多的额外开销。对移到新页的每⼀⾏⽽⾔，指向该数据的每个⾮聚簇索引的页级⾏也必须更新，有时可能还需要索引页的分理。从⼀个页⾯删除数据的进程也会有类似的开销，另外，删除进程还必须把数据移到页⾯上部，以保证数据的连续性。所以，建⽴⾮聚簇索引要⾮常慎重。⾮聚簇索引常被⽤在以下情况:
  1、某列常⽤于集合函数(如Sum,....)。
  2、某列常⽤于join,order by,group by。
  3、查寻出的数据不超过表中数据量的20%。
  三、覆盖索引(covering indexes)的使⽤
  覆盖索引是指那些索引项中包含查寻所需要的全部信息的⾮聚簇索引，这种索引之所以⽐较快也正是因为索引页中包含了查寻所必须的数据，不需去访问数据页。如果⾮聚簇索引中包含结果数据，那么它的查询速度将快于聚簇索引。
  但是由于覆盖索引的索引项⽐较多，要占⽤⽐较⼤的空间。⽽且update操作会引起索引值改变。所以如果潜在的覆盖查询并不常⽤或不太关键，则覆盖索引的增加反⽽会降低性能。
  四、索引的选择技术
  p_detail是住房公积⾦管理系统中记录个⼈明细的表，有890000⾏，观察在不同索引下的查询运⾏效果，测试在C/S环境下进⾏，客户机是IBM PII350(内存64M)，服务器是DEC Alpha1000A(内存128M)，数据库为SYBASE11.0.3。
  1、 select count(*) from p_detail where op_date>’19990101’ and op_date<’19991231’ and pri_surplus1>300

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。