SQL查询优化 LEFT JOIN和INNER JOIN

作者：VerySky

推荐：陈敬（Cathy）

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;

4,Left.join优化规则的研究.doc:
    一、概述 
　　对于left join的优化，是应用开发人员、数据库内核开发人员关注的问题之一。
　　应用开发人员关注是因为：并不是每个数据库的内核都支持left join的内部转化，这时候需要应用开发人员进行手工地转化。
　　内核开发人员关注是因为：并不假定每个应用开发人员都能够熟练地将left join转化掉。因此数据库有必要对这种情况，进行数据库内部的优化。
　　我当初对left join进行分析归纳，后来阅读mysql时发现 sql_select.cpp文件中的simplify_joins()函数的实现方法也是这样的，大家可以参考该函数。
　　二、left join优化规则的研究
　　t1 left t2 on t1.col1=t2.col1
　　对于类似的表达式，在什么样的情况下才可以去掉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、在连接操作中使用的列。

　　二、非聚簇索引(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

　　2、 select count(*),sum(pri_surplus1) from p_detail where op_date>’19990101’ and pay_month between‘199908’ and’199912’

　　不建任何索引查询1 1分15秒

　　查询2 1分7秒

　　在op_date上建非聚簇索引查询1 57秒

　　查询2 57秒

　　在op_date上建聚簇索引查询1 <1秒

　　查询2 52秒

　　在pay_month、op_date、pri_surplus1上建索引查询1 34秒

　　查询2 <1秒

　　在op_date、pay_month、pri_surplus1上建索引查询1 <1秒

　　查询2 <1秒

　　从以上查询效果分析，索引的有无，建立方式的不同将会导致不同的查询效果，选择什么样的索引基于用户对数据的查询条件,这些条件体现于 where从句和join表达式中。一般来说建立索引的思路是：

　　(1)、主键时常作为where子句的条件，应在表的主键列上建立聚簇索引，尤其当经常用它作为连接的时候。

　　(2)、有大量重复值且经常有范围查询和排序、分组发生的列，或者非常频繁地被访问的列，可考虑建立聚簇索引。

　　(3)、经常同时存取多列，且每列都含有重复值可考虑建立复合索引来覆盖一个或一组查询，并把查询引用最频繁的列作为前导列，如果可能尽量使关键查询形成覆盖查询。

　　(4)、如果知道索引键的所有值都是唯一的，那么确保把索引定义成唯一索引。

　　(5)、在一个经常做插入操作的表上建索引时，使用fillfactor(填充因子)来减少页分裂，同时提高并发度降低死锁的发生。如果在只读表上建索引，则可以把fillfactor置为100。

　　(6)、在选择索引键时，设法选择那些采用小数据类型的列作为键以使每个索

　　引页能够容纳尽可能多的索引键和指针，通过这种方式，可使一个查询必须遍历的索引页面降到最小。此外，尽可能地使用整数为键值，因为它能够提供比任何数据类型都快的访问速度。

　　五、索引的维护

　　上面讲到,某些不合适的索引影响到SQL Server的性能,随着应用系统的运行,数据不断地发生变化,当数据变化达到某一个程度时将会影响到索引的使用。这时需要用户自己来维护索引。索引的维护包括：

　　1、重建索引

　　随着数据行的插入、删除和数据页的分裂，有些索引页可能只包含几页数据，另外应用在执行大块I/O的时候，重建非聚簇索引可以降低分片，维护大块I/O的效率。重建索引实际上是重新组织B-树空间。在下面情况下需要重建索引：

　　(1)、数据和使用模式大幅度变化。

　　(2)、排序的顺序发生改变。

　　(3)、要进行大量插入操作或已经完成。

　　(4)、使用大块I/O的查询的磁盘读次数比预料的要多。

　　(5)

您正在看的sybase教程是:Sybase SQL Server索引的使用和优化。、由于大量数据修改，使得数据页和索引页没有充分使用而导致空间的使用超出估算。

　　(6)、dbcc检查出索引有问题。

　　当重建聚簇索引时,这张表的所有非聚簇索引将被重建.

　　2、索引统计信息的更新

　　当在一个包含数据的表上创建索引的时候，SQL Server会创建分布数据页来存放有关索引的两种统计信息：分布表和密度表。优化器利用这个页来判断该索引对某个特定查询是否有用。但这个统计信息并不动态地重新计算。这意味着，当表的数据改变之后，统计信息有可能是过时的，从而影响优化器追求最有工作的目标。因此，在下面情况下应该运行update statistics命令：

　　(1)、数据行的插入和删除修改了数据的分布。

　　(2)、对用truncate table删除数据的表上增加数据行。

　　(3)、修改索引列的值。

　　六、结束语

　　实践表明，不恰当的索引不但于事无补，反而会降低系统的执行性能。因为大量的索引在插入、修改和删除操作时比没有索引花费更多的系统时间。例如下面情况下建立的索引是不恰当的：

　　1、在查询中很少或从不引用的列不会受益于索引，因为索引很少或从来不必搜索基于这些列的行。

　　2、只有两个或三个值的列，如男性和女性(是或否)，从不会从索引中得到好处。

　　另外，鉴于索引加快了查询速度，但减慢了数据更新速度的特点。可通过在一个段上建表，而在另一个段上建其非聚簇索引，而这两段分别在单独的物理设备上来改善操作性能。