数据库查询——性能调优~

时间:2021-10-05 06:03:15
现在项目出现了一个性能瓶颈:

我有A/B/C,3个表

现在通过B查询状态为1的记录,大概4000条。然后遍历这4000条:
    在C表(50万记录)查找id和B表id相同的记录

所以,性能瓶颈就是C表根据id查询。所以需要执行4000次查询。再包括建立连接、断开连接之类的开销。现在完成这个模块大概需要4分钟左右。

我现在想优化的思路是这样:

通过1次查询把C表的信息全部读进内存。但是问题是,如果我没有任何处理就读取,那么返回的是List<Object>对象,我需要完成4000 * 50万遍历才能完成这个任务。这样就更悲剧了。 所以,我想
select * from C where id in (1,2,3,4,5,...4000); 然后把这些结果装到一个
HashMap<B表id, List<Object>>。这样我用到的时候根据id就可以拿。

不知道SimpleJdbcTemplate有这样的函数吗????在线急等~~~

58 个解决方案

#1


纠正一下,应该是
select * from C where id in (1,2,3,...4000);

#2


求指点啊- -

#3


人呢!!~~~~

#4


正确,in(4000个东西),这种查询不算什么

另外不需要这样吧

select xxx from c join b on c.id = b.id where b.state=1

把索引一建就好了

#5


你那sql语句改成这个:
select C.* from C where exists (select 1 from B where C.id=B.id)

#6


如果C表数据不怎么变化的话,可以考虑服务启动时,做个将C表加载到内存的任务,数据结构使用HashMap<B表id, List<Object>>,用的时候直接map.get(B表id)。
C表数据发生变化时,内存里的map对应的id对应的list也相应进行更新。

这要考虑内存,如果C表字段比较多,可能会占用很大内存。如果有条件,可以搞个缓存服务器。

#7


如果要判断B表的状态字的话, 加到条件里面去
select C.* from C where exists (select 1 from B where B.status = 1 and C.id=B.id) 

#8


SimpleJdbcTemplate不是有queryForObject(String sql,RowMapper rowMapper,Ojbect... args)方法吗这个方法就是返回你rowMapper的mapRow方法所返回的对象所以楼主只要利用BeanPropertyRowMapper进行属性和数据库字段的自动映射

Map<Integer,List<C>> map=new HashMap<Integer,List<C>>();
(Map<Integer,List<C>>)simpleJdbcTemplate.queryForObject(sql,new BeanPropertyRowMapper(C.class){
   //这里重写下rowMapper方法把获得的C对象放到map中就是了
   public Object mapRow(ResultSet rs,int rowNum){
     //原来的方法就是会返回c对象了
     C c=(C)super.mapRow(rs,rowNum);
     //接下来只需要把这个c对象放到map中返回map即可
     Integer id=c.getId();
     List<C> list=map.get(id);
     if(list==null){
       list=new ArrayList<C>();
       map.put(list);
     }
     list.add(c);
     return map;
   }
   
},args)

#9


就2个sql的问题,怎么描述的这么复杂。

#10


我给大家说下:

公司的这块是建立索引任务,固定时间久要更新一次。而索引建立的速度会很影响用户体验。所以,必须优化。

我现在通过一次select * from C; 然后通过HashMap建立一次索引,然后再使用,将性能提高了50%。但是,我觉得自己造的*不算好,想问下有没有更稳定、健壮、高效的方法来优化这个模块。

非常感谢。

#11


引用 6 楼 oh_Maxy 的回复:
如果C表数据不怎么变化的话,可以考虑服务启动时,做个将C表加载到内存的任务,数据结构使用HashMap<B表id, List<Object>>,用的时候直接map.get(B表id)。
C表数据发生变化时,内存里的map对应的id对应的list也相应进行更新。

这要考虑内存,如果C表字段比较多,可能会占用很大内存。如果有条件,可以搞个缓存服务器。


现在用的就是这种方法!性能提升了50%。但是时间还是过长。大概需要100s的时间。我想优化到30s左右。因为我们现在服务器分布在3个机房,最初1个机房完成这个模块需要3分钟+、剩下2个因为网络都是10分钟+。现在估计情况是1分半、5分钟+  目标是30s 3分钟。所以各位大拿有什么独门绝技,都秀出来吧!!!

#12


楼主用过4楼和5楼说的多表连接么?如果有建立索引,性能应该不差才对。

Select * 
From c Join b On c.id = b.id 
Where b.state=1

其中,b表的state上要建立索引,c表的id上也要建立索引(不过估计这个字段是主键可以不用索引)。

如果还觉得连接性能不足,Oracle环境下,可以把C表建立为“哈希聚簇表”。

#13


大数据的查询问题很头疼。

#14


引用 5 楼 acefr 的回复:
你那sql语句改成这个:
select C.* from C where exists (select 1 from B where C.id=B.id)
exsits效率高

#15


引用 12 楼 ldh911 的回复:
楼主用过4楼和5楼说的多表连接么?如果有建立索引,性能应该不差才对。

Select * 
From c Join b On c.id = b.id 
Where b.state=1

其中,b表的state上要建立索引,c表的id上也要建立索引(不过估计这个字段是主键可以不用索引)。

如果还觉得连接性能不足,Oracle环境下,可以把C表建立为“哈希聚簇表”。
大牛给力

#16


才50w的数据怎么可能需要四分钟,你有没有建索引,优化sql语句

#17


引用 7 楼 acefr 的回复:
如果要判断B表的状态字的话, 加到条件里面去
select C.* from C where exists (select 1 from B where B.status = 1 and C.id=B.id) 

你这里明显是限制性条件强在子查询,用in的效率会更高啊

#18


求指点,我的耶不行

#19


多表联合查询 索引新建好

#20


引用 12 楼 ldh911 的回复:
楼主用过4楼和5楼说的多表连接么?如果有建立索引,性能应该不差才对。

Select * 
From c Join b On c.id = b.id 
Where b.state=1

其中,b表的state上要建立索引,c表的id上也要建立索引(不过估计这个字段是主键可以不用索引)。

如果还觉得连接性能不足,Oracle环境下,可以把C表建立为“哈希聚簇表”。
  数据库查询——性能调优~

#21


很明显你的表结构有问题吧,还不上百万,就成这样了。

把表结构列出来,让高手给你分析一下。

#22


引用 12 楼 ldh911 的回复:
楼主用过4楼和5楼说的多表连接么?如果有建立索引,性能应该不差才对。

Select * 
From c Join b On c.id = b.id 
Where b.state=1

其中,b表的state上要建立索引,c表的id上也要建立索引(不过估计这个字段是主键可以不用索引)。

如果还觉得连接性能不足,Oracle环境下,可以把C表建立为“哈希聚簇表”。


额。这个我还真不知道。。。数据库方面的知识基础不够,我得赶紧学习一下~

#23


完整的看了一下上面的帖子.
select * from C where id in (1,2,3,...4000);
以及
select C.* from C where exists (select 1 from B where B.status = 1 and C.id=B.id) 

这里边,查询本身,可能费不了太长的时间,
问题的关键是可能有不少于4000条记录,如果是一个id对多条C的记录的话,
如果这个表的字段个数比较多,势必读取数据时,要费不少时间.
既然C表的数据可以缓存(没什么写操作? 对否?),对吧,同时id是整型的话,不妨用TreeSet<int, List<Object>>缓存起来,
这是一颗排好序的树,取某个id对应的value也非常快.

#24


不错........可以试试

#25


引用 23 楼 iihero 的回复:
完整的看了一下上面的帖子.
select * from C where id in (1,2,3,...4000);
以及
select C.* from C where exists (select 1 from B where B.status = 1 and C.id=B.id) 

这里边,查询本身,可能费不了太长的时间,
问题的关键是可能有不少于4000条记录,如果是一个id对多条C的记录的话,
如果这个表的字段个数比较多,势必读取数据时,要费不少时间.
既然C表的数据可以缓存(没什么写操作? 对否?),对吧,同时id是整型的话,不妨用TreeSet<int, List<Object>>缓存起来,
这是一颗排好序的树,取某个id对应的value也非常快.

TreeSet内部好像是红黑树或者平衡树的样子?我暂时使用的是HashMap<String, List<Object>>结构。不过我会尝试一下。感谢:)

#26


引用 25 楼 niushuai666 的回复:
Quote: 引用 23 楼 iihero 的回复:

完整的看了一下上面的帖子.
select * from C where id in (1,2,3,...4000);
以及
select C.* from C where exists (select 1 from B where B.status = 1 and C.id=B.id) 

这里边,查询本身,可能费不了太长的时间,
问题的关键是可能有不少于4000条记录,如果是一个id对多条C的记录的话,
如果这个表的字段个数比较多,势必读取数据时,要费不少时间.
既然C表的数据可以缓存(没什么写操作? 对否?),对吧,同时id是整型的话,不妨用TreeSet<int, List<Object>>缓存起来,
这是一颗排好序的树,取某个id对应的value也非常快.

TreeSet内部好像是红黑树或者平衡树的样子?我暂时使用的是HashMap<String, List<Object>>结构。不过我会尝试一下。感谢:)

我输入错了
应该是TreeMap<....>, 请更正.

#27


个人感觉就在索引上下功夫,你这个数据量根本算不上大

select 列名 from C inner join B on C.id=B.id and B.status=1

第一关联ID上必须建立索引
第二,你select的不一定是全部的列,那就还有优化的可能,复合索引或者是include索引(sqlserver的话)

刚好我也有一个类似你这样的查询

主表8W数据,相当于你的B表,子表50W数据量,相当于你的C表,效率杠杠滴,分页查询,没有任何性能问题

#28




create table A 
(
 id int identity(1,1),
 Status nvarchar(10),
 Column3 varchar(50),
 Column4 varchar(50)
)
insert into A values('1',NEWID(),NEWID())
go 5000

insert into A values('2',NEWID(),NEWID())
go 5000

insert into A values('3',NEWID(),NEWID())
go 5000

insert into A values('4',NEWID(),NEWID())
go 5000

insert into A values('5',NEWID(),NEWID())
go 5000

insert into A values('6',NEWID(),NEWID())
go 5000

create table B1
(
 ID int ,
 Column2 varchar(50),
 Column3 varchar(50),
 Column4 varchar(50),
 Column5 varchar(50),
 Column6 varchar(50)



insert into B values (cast(RAND()*100000 as int),NEWID(),NEWID(),NEWID(),NEWID(),NEWID())
go 500000

select RAND()*100000


create index index_id on A(id)


create index index_id on B1(id)

--本机测试,联系的垃圾本本
--A表3W条数据,B表50W,模拟你的查询
--B表ID随机的情况下,有2w多条数据,不到一秒就出来了
--所以还是建议你在数据库级别处理,别拿出来到内存中弄了
select * from B inner join A on B.ID=A.ID and A.Status='1'

#29


直接左联接查询,才50W数据量不大没有问题的.

SELECT B.*,C.* FROM B LEFT JOIN C ON C.BID=B.ID WHERE B.STATE=1;

如果实在慢的话可以考虑在C表中建立BID的索引.建立完成后速度应该在1S左右.
我这MYSQL关联查询300W数据都1S以内(画了分区,每个分区大概50W左右).

#30


你是不是把所有的数据都拿出来了,4000*50万,这个不是小数目,你读取出来,时间是个问题,内存装下这些数据也是要时间的,


请说明你是sql语句执行时间还是你程序读出时间,

#31


该回复于2013-12-09 09:14:10被管理员删除

#32


首先in字符超过7+性能会成几何级数的下降,另外最好以主key操作,用左联接或者exists 关键字来联查操作,写sql的准则是能一句话搞定的不用两句话来写,查询消耗最大的是连接的获取

#33


好东西。学习了。楼主好人 数据库查询——性能调优~

#34


记录下来。 好东西

#35


好东西,下回用的上了

#36


不错的东东,学习了

#37


引用 11 楼 niushuai666 的回复:
Quote: 引用 6 楼 oh_Maxy 的回复:

如果C表数据不怎么变化的话,可以考虑服务启动时,做个将C表加载到内存的任务,数据结构使用HashMap<B表id, List<Object>>,用的时候直接map.get(B表id)。
C表数据发生变化时,内存里的map对应的id对应的list也相应进行更新。

这要考虑内存,如果C表字段比较多,可能会占用很大内存。如果有条件,可以搞个缓存服务器。


现在用的就是这种方法!性能提升了50%。但是时间还是过长。大概需要100s的时间。我想优化到30s左右。因为我们现在服务器分布在3个机房,最初1个机房完成这个模块需要3分钟+、剩下2个因为网络都是10分钟+。现在估计情况是1分半、5分钟+  目标是30s 3分钟。所以各位大拿有什么独门绝技,都秀出来吧!!!

既然有条件搞分布式,应该考虑过缓存服务器吧?redis,把每次查询的数据缓存到缓存服务器中,根据不同表的使用情况,设置个有效时间。这个方案怎么样?

#38


nice 数据库查询——性能调优~

#39


还可以 还可以 哈哈

#40


引用 37 楼 oh_Maxy 的回复:
Quote: 引用 11 楼 niushuai666 的回复:

Quote: 引用 6 楼 oh_Maxy 的回复:

如果C表数据不怎么变化的话,可以考虑服务启动时,做个将C表加载到内存的任务,数据结构使用HashMap<B表id, List<Object>>,用的时候直接map.get(B表id)。
C表数据发生变化时,内存里的map对应的id对应的list也相应进行更新。

这要考虑内存,如果C表字段比较多,可能会占用很大内存。如果有条件,可以搞个缓存服务器。


现在用的就是这种方法!性能提升了50%。但是时间还是过长。大概需要100s的时间。我想优化到30s左右。因为我们现在服务器分布在3个机房,最初1个机房完成这个模块需要3分钟+、剩下2个因为网络都是10分钟+。现在估计情况是1分半、5分钟+  目标是30s 3分钟。所以各位大拿有什么独门绝技,都秀出来吧!!!

既然有条件搞分布式,应该考虑过缓存服务器吧?redis,把每次查询的数据缓存到缓存服务器中,根据不同表的使用情况,设置个有效时间。这个方案怎么样?

果然是大牛,对,我们有几台缓存服务器的。使用的是redis。主要我们的表比较复杂、业务逻辑多,我在mentor指导下优化了数据库查询返回的结构和内存处理的东东,现在一次索引需要20s左右了。

至于优化,是因为这这是我们项目中需要建索引的一部分,现在还是定时任务,未来的业务需求是实时建索引。任重而道远啊。。(我就不说主要是我太菜。。。。)

#41


共同学习啦~

嗯,有索引、主键的时候,会影响插入的操作效率,有些项目因为增删操作非常频繁,就弃用索引了的。

不过不用主键、索引,错失浪费数据库的一大特性啊。

#42


数据库也能这么专业啊 惭愧啊

#43


想学免杀,求高手的指点!

#44


求指点啊- -

#45


索引只要不是太多,性能影响不是太大的,好好使用数据库,这些算法人家都已经写好了,小数据量的话用nestloop join 搞定,数据量大的话用hash join搞定,比你自己造*快多了

#46


SELECT B.COLUMN1,B.COLUMN2....B.COLUMNX,B.ID, TEMP.COLUMN1,TEMP.COLUMN2...TEMP.COLUMNX FROM (SELECT C.COLUMN1 AS TEMP1,C.COLUMN2 AS TEMP2...C.COLUMNX AS TEMPN,C.ID AS TEMPID FROM C) AS TEMP ,B WHERE TEMP.ID = B.ID

#47


碰到效率问题。我一般都会想到空间和时间互换的理论。建立索引、调整数据库配置本质上都采用了这个方式。

如果c表主键是自增长的话,用id匹配是很快的。

我的想法是B表C表不直接关联。用一个tmp表格,这张表格只需要id字段,存储B表的状态有效的4000条id。关联tmp表和c表查询。这样数据量是4000×50万。这个数据量并不大,因为是主键的匹配,速度也很快。如果确实因为数据量大影响了响应时间,B表可以存储200条,然后循环20次。

#48


多写几个SQL语句,测试下选用最快的

#49


多写几个SQL语句,测试下选用最快的

#50


试试5楼的吧 记得加索引

#1


纠正一下,应该是
select * from C where id in (1,2,3,...4000);

#2


求指点啊- -

#3


人呢!!~~~~

#4


正确,in(4000个东西),这种查询不算什么

另外不需要这样吧

select xxx from c join b on c.id = b.id where b.state=1

把索引一建就好了

#5


你那sql语句改成这个:
select C.* from C where exists (select 1 from B where C.id=B.id)

#6


如果C表数据不怎么变化的话,可以考虑服务启动时,做个将C表加载到内存的任务,数据结构使用HashMap<B表id, List<Object>>,用的时候直接map.get(B表id)。
C表数据发生变化时,内存里的map对应的id对应的list也相应进行更新。

这要考虑内存,如果C表字段比较多,可能会占用很大内存。如果有条件,可以搞个缓存服务器。

#7


如果要判断B表的状态字的话, 加到条件里面去
select C.* from C where exists (select 1 from B where B.status = 1 and C.id=B.id) 

#8


SimpleJdbcTemplate不是有queryForObject(String sql,RowMapper rowMapper,Ojbect... args)方法吗这个方法就是返回你rowMapper的mapRow方法所返回的对象所以楼主只要利用BeanPropertyRowMapper进行属性和数据库字段的自动映射

Map<Integer,List<C>> map=new HashMap<Integer,List<C>>();
(Map<Integer,List<C>>)simpleJdbcTemplate.queryForObject(sql,new BeanPropertyRowMapper(C.class){
   //这里重写下rowMapper方法把获得的C对象放到map中就是了
   public Object mapRow(ResultSet rs,int rowNum){
     //原来的方法就是会返回c对象了
     C c=(C)super.mapRow(rs,rowNum);
     //接下来只需要把这个c对象放到map中返回map即可
     Integer id=c.getId();
     List<C> list=map.get(id);
     if(list==null){
       list=new ArrayList<C>();
       map.put(list);
     }
     list.add(c);
     return map;
   }
   
},args)

#9


就2个sql的问题,怎么描述的这么复杂。

#10


我给大家说下:

公司的这块是建立索引任务,固定时间久要更新一次。而索引建立的速度会很影响用户体验。所以,必须优化。

我现在通过一次select * from C; 然后通过HashMap建立一次索引,然后再使用,将性能提高了50%。但是,我觉得自己造的*不算好,想问下有没有更稳定、健壮、高效的方法来优化这个模块。

非常感谢。

#11


引用 6 楼 oh_Maxy 的回复:
如果C表数据不怎么变化的话,可以考虑服务启动时,做个将C表加载到内存的任务,数据结构使用HashMap<B表id, List<Object>>,用的时候直接map.get(B表id)。
C表数据发生变化时,内存里的map对应的id对应的list也相应进行更新。

这要考虑内存,如果C表字段比较多,可能会占用很大内存。如果有条件,可以搞个缓存服务器。


现在用的就是这种方法!性能提升了50%。但是时间还是过长。大概需要100s的时间。我想优化到30s左右。因为我们现在服务器分布在3个机房,最初1个机房完成这个模块需要3分钟+、剩下2个因为网络都是10分钟+。现在估计情况是1分半、5分钟+  目标是30s 3分钟。所以各位大拿有什么独门绝技,都秀出来吧!!!

#12


楼主用过4楼和5楼说的多表连接么?如果有建立索引,性能应该不差才对。

Select * 
From c Join b On c.id = b.id 
Where b.state=1

其中,b表的state上要建立索引,c表的id上也要建立索引(不过估计这个字段是主键可以不用索引)。

如果还觉得连接性能不足,Oracle环境下,可以把C表建立为“哈希聚簇表”。

#13


大数据的查询问题很头疼。

#14


引用 5 楼 acefr 的回复:
你那sql语句改成这个:
select C.* from C where exists (select 1 from B where C.id=B.id)
exsits效率高

#15


引用 12 楼 ldh911 的回复:
楼主用过4楼和5楼说的多表连接么?如果有建立索引,性能应该不差才对。

Select * 
From c Join b On c.id = b.id 
Where b.state=1

其中,b表的state上要建立索引,c表的id上也要建立索引(不过估计这个字段是主键可以不用索引)。

如果还觉得连接性能不足,Oracle环境下,可以把C表建立为“哈希聚簇表”。
大牛给力

#16


才50w的数据怎么可能需要四分钟,你有没有建索引,优化sql语句

#17


引用 7 楼 acefr 的回复:
如果要判断B表的状态字的话, 加到条件里面去
select C.* from C where exists (select 1 from B where B.status = 1 and C.id=B.id) 

你这里明显是限制性条件强在子查询,用in的效率会更高啊

#18


求指点,我的耶不行

#19


多表联合查询 索引新建好

#20


引用 12 楼 ldh911 的回复:
楼主用过4楼和5楼说的多表连接么?如果有建立索引,性能应该不差才对。

Select * 
From c Join b On c.id = b.id 
Where b.state=1

其中,b表的state上要建立索引,c表的id上也要建立索引(不过估计这个字段是主键可以不用索引)。

如果还觉得连接性能不足,Oracle环境下,可以把C表建立为“哈希聚簇表”。
  数据库查询——性能调优~

#21


很明显你的表结构有问题吧,还不上百万,就成这样了。

把表结构列出来,让高手给你分析一下。

#22


引用 12 楼 ldh911 的回复:
楼主用过4楼和5楼说的多表连接么?如果有建立索引,性能应该不差才对。

Select * 
From c Join b On c.id = b.id 
Where b.state=1

其中,b表的state上要建立索引,c表的id上也要建立索引(不过估计这个字段是主键可以不用索引)。

如果还觉得连接性能不足,Oracle环境下,可以把C表建立为“哈希聚簇表”。


额。这个我还真不知道。。。数据库方面的知识基础不够,我得赶紧学习一下~

#23


完整的看了一下上面的帖子.
select * from C where id in (1,2,3,...4000);
以及
select C.* from C where exists (select 1 from B where B.status = 1 and C.id=B.id) 

这里边,查询本身,可能费不了太长的时间,
问题的关键是可能有不少于4000条记录,如果是一个id对多条C的记录的话,
如果这个表的字段个数比较多,势必读取数据时,要费不少时间.
既然C表的数据可以缓存(没什么写操作? 对否?),对吧,同时id是整型的话,不妨用TreeSet<int, List<Object>>缓存起来,
这是一颗排好序的树,取某个id对应的value也非常快.

#24


不错........可以试试

#25


引用 23 楼 iihero 的回复:
完整的看了一下上面的帖子.
select * from C where id in (1,2,3,...4000);
以及
select C.* from C where exists (select 1 from B where B.status = 1 and C.id=B.id) 

这里边,查询本身,可能费不了太长的时间,
问题的关键是可能有不少于4000条记录,如果是一个id对多条C的记录的话,
如果这个表的字段个数比较多,势必读取数据时,要费不少时间.
既然C表的数据可以缓存(没什么写操作? 对否?),对吧,同时id是整型的话,不妨用TreeSet<int, List<Object>>缓存起来,
这是一颗排好序的树,取某个id对应的value也非常快.

TreeSet内部好像是红黑树或者平衡树的样子?我暂时使用的是HashMap<String, List<Object>>结构。不过我会尝试一下。感谢:)

#26


引用 25 楼 niushuai666 的回复:
Quote: 引用 23 楼 iihero 的回复:

完整的看了一下上面的帖子.
select * from C where id in (1,2,3,...4000);
以及
select C.* from C where exists (select 1 from B where B.status = 1 and C.id=B.id) 

这里边,查询本身,可能费不了太长的时间,
问题的关键是可能有不少于4000条记录,如果是一个id对多条C的记录的话,
如果这个表的字段个数比较多,势必读取数据时,要费不少时间.
既然C表的数据可以缓存(没什么写操作? 对否?),对吧,同时id是整型的话,不妨用TreeSet<int, List<Object>>缓存起来,
这是一颗排好序的树,取某个id对应的value也非常快.

TreeSet内部好像是红黑树或者平衡树的样子?我暂时使用的是HashMap<String, List<Object>>结构。不过我会尝试一下。感谢:)

我输入错了
应该是TreeMap<....>, 请更正.

#27


个人感觉就在索引上下功夫,你这个数据量根本算不上大

select 列名 from C inner join B on C.id=B.id and B.status=1

第一关联ID上必须建立索引
第二,你select的不一定是全部的列,那就还有优化的可能,复合索引或者是include索引(sqlserver的话)

刚好我也有一个类似你这样的查询

主表8W数据,相当于你的B表,子表50W数据量,相当于你的C表,效率杠杠滴,分页查询,没有任何性能问题

#28




create table A 
(
 id int identity(1,1),
 Status nvarchar(10),
 Column3 varchar(50),
 Column4 varchar(50)
)
insert into A values('1',NEWID(),NEWID())
go 5000

insert into A values('2',NEWID(),NEWID())
go 5000

insert into A values('3',NEWID(),NEWID())
go 5000

insert into A values('4',NEWID(),NEWID())
go 5000

insert into A values('5',NEWID(),NEWID())
go 5000

insert into A values('6',NEWID(),NEWID())
go 5000

create table B1
(
 ID int ,
 Column2 varchar(50),
 Column3 varchar(50),
 Column4 varchar(50),
 Column5 varchar(50),
 Column6 varchar(50)



insert into B values (cast(RAND()*100000 as int),NEWID(),NEWID(),NEWID(),NEWID(),NEWID())
go 500000

select RAND()*100000


create index index_id on A(id)


create index index_id on B1(id)

--本机测试,联系的垃圾本本
--A表3W条数据,B表50W,模拟你的查询
--B表ID随机的情况下,有2w多条数据,不到一秒就出来了
--所以还是建议你在数据库级别处理,别拿出来到内存中弄了
select * from B inner join A on B.ID=A.ID and A.Status='1'

#29


直接左联接查询,才50W数据量不大没有问题的.

SELECT B.*,C.* FROM B LEFT JOIN C ON C.BID=B.ID WHERE B.STATE=1;

如果实在慢的话可以考虑在C表中建立BID的索引.建立完成后速度应该在1S左右.
我这MYSQL关联查询300W数据都1S以内(画了分区,每个分区大概50W左右).

#30


你是不是把所有的数据都拿出来了,4000*50万,这个不是小数目,你读取出来,时间是个问题,内存装下这些数据也是要时间的,


请说明你是sql语句执行时间还是你程序读出时间,

#31


该回复于2013-12-09 09:14:10被管理员删除

#32


首先in字符超过7+性能会成几何级数的下降,另外最好以主key操作,用左联接或者exists 关键字来联查操作,写sql的准则是能一句话搞定的不用两句话来写,查询消耗最大的是连接的获取

#33


好东西。学习了。楼主好人 数据库查询——性能调优~

#34


记录下来。 好东西

#35


好东西,下回用的上了

#36


不错的东东,学习了

#37


引用 11 楼 niushuai666 的回复:
Quote: 引用 6 楼 oh_Maxy 的回复:

如果C表数据不怎么变化的话,可以考虑服务启动时,做个将C表加载到内存的任务,数据结构使用HashMap<B表id, List<Object>>,用的时候直接map.get(B表id)。
C表数据发生变化时,内存里的map对应的id对应的list也相应进行更新。

这要考虑内存,如果C表字段比较多,可能会占用很大内存。如果有条件,可以搞个缓存服务器。


现在用的就是这种方法!性能提升了50%。但是时间还是过长。大概需要100s的时间。我想优化到30s左右。因为我们现在服务器分布在3个机房,最初1个机房完成这个模块需要3分钟+、剩下2个因为网络都是10分钟+。现在估计情况是1分半、5分钟+  目标是30s 3分钟。所以各位大拿有什么独门绝技,都秀出来吧!!!

既然有条件搞分布式,应该考虑过缓存服务器吧?redis,把每次查询的数据缓存到缓存服务器中,根据不同表的使用情况,设置个有效时间。这个方案怎么样?

#38


nice 数据库查询——性能调优~

#39


还可以 还可以 哈哈

#40


引用 37 楼 oh_Maxy 的回复:
Quote: 引用 11 楼 niushuai666 的回复:

Quote: 引用 6 楼 oh_Maxy 的回复:

如果C表数据不怎么变化的话,可以考虑服务启动时,做个将C表加载到内存的任务,数据结构使用HashMap<B表id, List<Object>>,用的时候直接map.get(B表id)。
C表数据发生变化时,内存里的map对应的id对应的list也相应进行更新。

这要考虑内存,如果C表字段比较多,可能会占用很大内存。如果有条件,可以搞个缓存服务器。


现在用的就是这种方法!性能提升了50%。但是时间还是过长。大概需要100s的时间。我想优化到30s左右。因为我们现在服务器分布在3个机房,最初1个机房完成这个模块需要3分钟+、剩下2个因为网络都是10分钟+。现在估计情况是1分半、5分钟+  目标是30s 3分钟。所以各位大拿有什么独门绝技,都秀出来吧!!!

既然有条件搞分布式,应该考虑过缓存服务器吧?redis,把每次查询的数据缓存到缓存服务器中,根据不同表的使用情况,设置个有效时间。这个方案怎么样?

果然是大牛,对,我们有几台缓存服务器的。使用的是redis。主要我们的表比较复杂、业务逻辑多,我在mentor指导下优化了数据库查询返回的结构和内存处理的东东,现在一次索引需要20s左右了。

至于优化,是因为这这是我们项目中需要建索引的一部分,现在还是定时任务,未来的业务需求是实时建索引。任重而道远啊。。(我就不说主要是我太菜。。。。)

#41


共同学习啦~

嗯,有索引、主键的时候,会影响插入的操作效率,有些项目因为增删操作非常频繁,就弃用索引了的。

不过不用主键、索引,错失浪费数据库的一大特性啊。

#42


数据库也能这么专业啊 惭愧啊

#43


想学免杀,求高手的指点!

#44


求指点啊- -

#45


索引只要不是太多,性能影响不是太大的,好好使用数据库,这些算法人家都已经写好了,小数据量的话用nestloop join 搞定,数据量大的话用hash join搞定,比你自己造*快多了

#46


SELECT B.COLUMN1,B.COLUMN2....B.COLUMNX,B.ID, TEMP.COLUMN1,TEMP.COLUMN2...TEMP.COLUMNX FROM (SELECT C.COLUMN1 AS TEMP1,C.COLUMN2 AS TEMP2...C.COLUMNX AS TEMPN,C.ID AS TEMPID FROM C) AS TEMP ,B WHERE TEMP.ID = B.ID

#47


碰到效率问题。我一般都会想到空间和时间互换的理论。建立索引、调整数据库配置本质上都采用了这个方式。

如果c表主键是自增长的话,用id匹配是很快的。

我的想法是B表C表不直接关联。用一个tmp表格,这张表格只需要id字段,存储B表的状态有效的4000条id。关联tmp表和c表查询。这样数据量是4000×50万。这个数据量并不大,因为是主键的匹配,速度也很快。如果确实因为数据量大影响了响应时间,B表可以存储200条,然后循环20次。

#48


多写几个SQL语句,测试下选用最快的

#49


多写几个SQL语句,测试下选用最快的

#50


试试5楼的吧 记得加索引