17,377
社区成员
发帖
与我相关
我的任务
分享大表关联查询的效率问题求教
有两个百万级的大表a,b，现在需要关联两个表查询是否存在满足条件的记录，我的sql如下：
select count(a.id) from a,b where a.id=b.id and ...
当满足查询条件的记录很多时，查询效率非常低下，常常需要几十甚至100多秒，请教各位大侠有没有解决此效率问题的好方法
...全文
请发表友善的回复…
发表回复
neucloud 2005-11-22
- 打赏
- 举报
heyixiang(子豚の愛人):
你的QQ我记下了，以后有什么问题肯定还是要向你们请教的，我现在只是菜鸟级的水平，问题多多，呵呵
你的QQ我记下了，以后有什么问题肯定还是要向你们请教的，我现在只是菜鸟级的水平，问题多多，呵呵
heyixiang 2005-11-21
- 打赏
- 举报
结了……
-_!!
刚来的瘾就没了
-_!!
刚来的瘾就没了
neucloud 2005-11-21
- 打赏
- 举报
最终终于用KingSunSha(弱水三千) 的查询方法搞定了，当初之所以效率低可能是因为建了索引之后没有更新oracle的统计信息，现在通过下面的查询
select 1 from dual
where exists (select 1 from a,b where a.id=b.id and ...);
已经使查询速度提高到了毫秒级。
以前对sql编写不是很注意，导致很多查询效率低下，在用户数据量小的情况下暴露不出来，随着数据量的增长，很多看似简单的sql都可能出现很大的效率问题，以后真得好好注意了。
多谢各位的指导！
select 1 from dual
where exists (select 1 from a,b where a.id=b.id and ...);
已经使查询速度提高到了毫秒级。
以前对sql编写不是很注意，导致很多查询效率低下，在用户数据量小的情况下暴露不出来，随着数据量的增长，很多看似简单的sql都可能出现很大的效率问题，以后真得好好注意了。
多谢各位的指导！
风河 2005-11-20
- 打赏
- 举报
关联查询要先对所有需要关联的表进行预算.
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两个的数据量是最小的,那么关联查询的乘积才是最小的.查询速度也是最快的.
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两个的数据量是最小的,那么关联查询的乘积才是最小的.查询速度也是最快的.
heyixiang 2005-11-19
- 打赏
- 举报
KingSunSha(弱水三千) 所说的用exists代替count绝对是必要的。
heyixiang 2005-11-19
- 打赏
- 举报
a表100W，b表400W，
a.id只A表的主建，b.productid是B表的主键，b.id是外部关键字。
不知道楼主的表关系是不是我猜测的这个样子。
如果是这样的话，B表只有productid做索引是正常的。所以需要看你b.productid='ABC'这个条件筛选出来的记录数大概占了多大的百分比，执行计划是否走了索引扫描？你建了索引ORACLE并不一定用了。
另外，A表我不赞成把flag也放到索引中，毕竟flag只有'Y'和'N'两个情况，这样对索引的大小产生影响，索引过大的话扫描话的时间也会增加，索引的合理性也是很重要的。
把你的执行计划贴出来吧，我感觉主要问题出在B表上导致时间过长。
a.id只A表的主建，b.productid是B表的主键，b.id是外部关键字。
不知道楼主的表关系是不是我猜测的这个样子。
如果是这样的话，B表只有productid做索引是正常的。所以需要看你b.productid='ABC'这个条件筛选出来的记录数大概占了多大的百分比，执行计划是否走了索引扫描？你建了索引ORACLE并不一定用了。
另外，A表我不赞成把flag也放到索引中，毕竟flag只有'Y'和'N'两个情况，这样对索引的大小产生影响，索引过大的话扫描话的时间也会增加，索引的合理性也是很重要的。
把你的执行计划贴出来吧，我感觉主要问题出在B表上导致时间过长。
heyixiang 2005-11-19
- 打赏
- 举报
用了order by 开销应该更大才是
baojianjun 2005-11-19
- 打赏
- 举报
呵呵，特殊情況特殊分析
沒有一定得規律，隻有大家的經驗
沒有一定得規律，隻有大家的經驗
常遇道 2005-11-19
- 打赏
- 举报
用order by
的主表ID或者你字段的时间排序试试
可以加快数据量的读取速度时间的优先级最高
的主表ID或者你字段的时间排序试试
可以加快数据量的读取速度时间的优先级最高
lee_billiy 2005-11-18
- 打赏
- 举报
学习...
neucloud 2005-11-18
- 打赏
- 举报
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查询方法后效率确实提高了很多，但还没有到令人满意的程度。
两个表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查询方法后效率确实提高了很多，但还没有到令人满意的程度。
dylwx 2005-11-18
- 打赏
- 举报
高！
KingSunSha 2005-11-18
- 打赏
- 举报
a表有复合索引：(id,dept,flag)
根据你的sql，这个索引被充分利用，非常好
b表索引：(productid)
非常有可能是这个索引降低了你的效率，如果用(id, productid)做索引，当然是最理想的，但即使单单用（id）索引，也应该比(productid)效率高。
100万、400万纪录只能算是很普通的表，我认为你的这句sql改成exists之后如果执行计划正确的话，运行时间应该在秒级，2-3秒钟甚至更短。
根据你的sql，这个索引被充分利用，非常好
b表索引：(productid)
非常有可能是这个索引降低了你的效率，如果用(id, productid)做索引，当然是最理想的，但即使单单用（id）索引，也应该比(productid)效率高。
100万、400万纪录只能算是很普通的表，我认为你的这句sql改成exists之后如果执行计划正确的话，运行时间应该在秒级，2-3秒钟甚至更短。
heyixiang 2005-11-18
- 打赏
- 举报
QQ 2674816
heyixiang 2005-11-18
- 打赏
- 举报
根据你的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语句是不是就是这个？别又删删减减啦，对于语句优化，不怕你写的问题复杂，就怕你写的简单。
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语句是不是就是这个？别又删删减减啦，对于语句优化，不怕你写的问题复杂，就怕你写的简单。
bobfang 2005-11-18
- 打赏
- 举报
请贴出执行那个select count的执行计划。
heyixiang 2005-11-17
- 打赏
- 举报
需要详细的SQL语句才能帮你分析.同时简单描述下你的索引字段以及查询条件,表是否经常需要update/delete?还是仅仅用来select/insert?
KingSunSha(弱水三千) 提出的用exists是可行的方法,但是如果要提高到5秒以内......楼主还是把问题介绍详细些.
KingSunSha(弱水三千) 提出的用exists是可行的方法,但是如果要提高到5秒以内......楼主还是把问题介绍详细些.
KingSunSha 2005-11-17
- 打赏
- 举报
select count()需要对所有满足条件的纪录作计数，所以必须找出所有满足条件的纪录，当oracle cbo计算出全表扫描的消耗少于走index时，执行计划就会走全表扫描。
而exists条件之需要找到第一条满足条件的纪录就返回，而不需要统计所有满足条件的纪录，cbo根据statistics计算出走index消耗更少。
关于全表扫描还是index扫描，取决于通过index访问的纪录数占表中总纪录数的百分比、数据分布、index中列的uniqueness等很多因素，走index并不是总是有益的。某些情况下，走全表扫描效率远远高于index扫描。所以要具体情况具体分析。
你把表结构、index结构、数据分布（analyze的结果）、典型的查型条件等内容贴上来，我们再探讨一下方案。
而exists条件之需要找到第一条满足条件的纪录就返回，而不需要统计所有满足条件的纪录，cbo根据statistics计算出走index消耗更少。
关于全表扫描还是index扫描，取决于通过index访问的纪录数占表中总纪录数的百分比、数据分布、index中列的uniqueness等很多因素，走index并不是总是有益的。某些情况下，走全表扫描效率远远高于index扫描。所以要具体情况具体分析。
你把表结构、index结构、数据分布（analyze的结果）、典型的查型条件等内容贴上来，我们再探讨一下方案。
neucloud 2005-11-17
- 打赏
- 举报
TO KingSunSha(弱水三千) :
最初用“select count(a.id)”查询时a表走全表扫描，但是用了你的方法后便不会全表扫描，能解释一下原因吗？（查询条件都是相同的）
最初用“select count(a.id)”查询时a表走全表扫描，但是用了你的方法后便不会全表扫描，能解释一下原因吗？（查询条件都是相同的）
neucloud 2005-11-17
- 打赏
- 举报
我看过执行计划了，成本主要消耗在HASH JOIN和NESTED LOOPS上
加载更多回复（19）
