大表关联查询的效率问题求教

[neucloud]

有两个百万级的大表a,b，现在需要关联两个表查询是否存在满足条件的记录，我的sql如下：
select count(a.id) from a,b where a.id=b.id and ...

当满足查询条件的记录很多时，查询效率非常低下，常常需要几十甚至100多秒，请教各位大侠有没有解决此效率问题的好方法

[neucloud]

我看过执行计划了，成本主要消耗在HASH JOIN和NESTED LOOPS上

[neucloud]

TO KingSunSha(弱水三千) :

最初用“select count(a.id)”查询时a表走全表扫描，但是用了你的方法后便不会全表扫描，能解释一下原因吗？（查询条件都是相同的）

[KingSunSha]

select count()需要对所有满足条件的纪录作计数，所以必须找出所有满足条件的纪录，当oracle cbo计算出全表扫描的消耗少于走index时，执行计划就会走全表扫描。

而exists条件之需要找到第一条满足条件的纪录就返回，而不需要统计所有满足条件的纪录，cbo根据statistics计算出走index消耗更少。

关于全表扫描还是index扫描，取决于通过index访问的纪录数占表中总纪录数的百分比、数据分布、index中列的uniqueness等很多因素，走index并不是总是有益的。某些情况下，走全表扫描效率远远高于index扫描。所以要具体情况具体分析。

你把表结构、index结构、数据分布（analyze的结果）、典型的查型条件等内容贴上来，我们再探讨一下方案。

[heyixiang]

需要详细的SQL语句才能帮你分析.同时简单描述下你的索引字段以及查询条件,表是否经常需要update/delete?还是仅仅用来select/insert?

KingSunSha(弱水三千) 提出的用exists是可行的方法,但是如果要提高到5秒以内......楼主还是把问题介绍详细些.

[bobfang]

请贴出执行那个select count的执行计划。

[heyixiang]

根据你的SQL语句

select count(a.id)
from a,b where a.id=b.aid and a.dept = '01' and a.flag = 'Y' and b.productid = 'ABC'

可以看到a表的所有字段都在索引中，那你可以试试索引扫描。
select /*+ index_ffs(a 索引名称)*/ count(a.id)
from a,b where a.id=b.aid and a.dept = '01' and a.flag = 'Y' and b.productid = 'ABC'


你的完整的SQL语句是不是就是这个？别又删删减减啦，对于语句优化，不怕你写的问题复杂，就怕你写的简单。

[heyixiang]

QQ 2674816

[KingSunSha]

a表有复合索引：(id,dept,flag)
根据你的sql，这个索引被充分利用，非常好

b表索引：(productid)
非常有可能是这个索引降低了你的效率，如果用(id, productid)做索引，当然是最理想的，但即使单单用（id）索引，也应该比(productid)效率高。

100万、400万纪录只能算是很普通的表，我认为你的这句sql改成exists之后如果执行计划正确的话，运行时间应该在秒级，2-3秒钟甚至更短。

[dylwx]

高！

[neucloud]

heyixiang(子豚の愛人)，KingSunSha(弱水三千) ：

两个表a,b是主子表关系，其中a有100万行，b有400万行，这两个表主要执行的sql为select,update,insert，几乎没有delete

我的查询语句很简单：

select count(a.id)
from a,b where a.id=b.aid and a.dept = '01' and a.flag = 'Y' and b.productid = 'ABC'

只不过满足查询条件的记录很多，有40万行。
该查询用到的索引如下：

a表有复合索引：(id,dept,flag)，b表索引：(productid)

当然，这两个表还建有其他的索引。


用了exists查询方法后效率确实提高了很多，但还没有到令人满意的程度。

[lee_billiy]

学习...

[常遇道]

用order by
的主表ID或者你字段的时间排序试试
可以加快数据量的读取速度时间的优先级最高

[baojianjun]

呵呵，特殊情況特殊分析

沒有一定得規律，隻有大家的經驗

[heyixiang]

用了order by 开销应该更大才是

[heyixiang]

a表100W，b表400W，

a.id只A表的主建，b.productid是B表的主键，b.id是外部关键字。

不知道楼主的表关系是不是我猜测的这个样子。

如果是这样的话，B表只有productid做索引是正常的。所以需要看你b.productid='ABC'这个条件筛选出来的记录数大概占了多大的百分比，执行计划是否走了索引扫描？你建了索引ORACLE并不一定用了。

另外，A表我不赞成把flag也放到索引中，毕竟flag只有'Y'和'N'两个情况，这样对索引的大小产生影响，索引过大的话扫描话的时间也会增加，索引的合理性也是很重要的。

把你的执行计划贴出来吧，我感觉主要问题出在B表上导致时间过长。

[heyixiang]

KingSunSha(弱水三千) 所说的用exists代替count绝对是必要的。

[风河]

关联查询要先对所有需要关联的表进行预算.

select a.id from a where a.id exists(select b.id from b where b.id exits(....))

最后做where x.id = y.id时,应满足x,y两个的数据量是最小的,那么关联查询的乘积才是最小的.查询速度也是最快的.

[neucloud]

最终终于用KingSunSha(弱水三千) 的查询方法搞定了，当初之所以效率低可能是因为建了索引之后没有更新oracle的统计信息，现在通过下面的查询
select 1 from dual
where exists (select 1 from a,b where a.id=b.id and ...);
已经使查询速度提高到了毫秒级。

以前对sql编写不是很注意，导致很多查询效率低下，在用户数据量小的情况下暴露不出来，随着数据量的增长，很多看似简单的sql都可能出现很大的效率问题，以后真得好好注意了。
多谢各位的指导！

[heyixiang]

结了……


-_!!

刚来的瘾就没了

[neucloud]

heyixiang(子豚の愛人):

你的QQ我记下了，以后有什么问题肯定还是要向你们请教的，我现在只是菜鸟级的水平，问题多多，呵呵