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

现在项目出现了一个性能瓶颈：

我有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楼的吧 记得加索引