首页 新闻 搜索 专区 学院

MySQL 从两表抽取数据插入新表中效率低下,如何优化?

0
悬赏园豆:60 [已解决问题] 解决于 2014-05-07 18:30

有4张表[MySQL数据库],人员基本信息表db_personalinfo,数据结构如下:

id     personno        name   dep   .......

1      1000               张三    d1    .......

2      1001               李四    d2    .......

3      1002               王五    d3    .......

报销信息表db_reiminfo,数据结构如下:

id    personno         r_type    amount    yearmon

1       1000             t1           550   201401

2       1000             t2           200   201401

3       1000             t3           110     201401

4       1001             t2           320     201401

5       1001             t3           210     201401

6       1003             t2           100     201401

7       1004             t1           180     201401

.......

报销类型表db_reimtype,数据结构如下:(一共差不多20条以内)

id     reim_name     reim_expr

1      打车               t2+t3

2      住宿               t1+t3

3      餐饮               t3

......

最后是报销明细表db_person_reiminfo,数据结构如下:

id    personno    name      dep ......     打车    住宿    餐饮   .......

1     1000          张三        d1   ......    310     660    110   .......

2     1001          李四        d2   ......    530     210    210   .......

3     1002          王五        d3   ......    100         0       0   .......

......

数据流程是这样,从第三方数据库中同步人员基本信息和报销信息数据后插入到对应表中,报销类型表是固定的,有了人员基本信息和报销信息后要组合成报销明细表结构并插入到报销明细表中。

我写了个方法来获取指定的报销金额,s_personno--人员编号、s_reim_Name--报销类型名

create function getreimAmount(s_personno varchar(8),s_reim_Name varchar(10))

returns decimal(18,2)

begin

  declare result decimal(18,2);
  set result=(select ifnull(sum(amount),0) from db_reiminfo ri join (

    select reim_expr from db_reimtype where reim_Name=s_reim_Name) rt

    where instr(rt.reim_expr,ri.r_type)>0 and ri.personno=s_personno);
  return result;

end

 

组合数据结构并插入到报销明细表的脚本如下:(id 是自增列)

drop temporary table if exists temptb;
create temporary table temptb select null as id,personno,name,dep,getreimAmount(personno,'打车') as '打车', getreimAmount(personno,'住宿') as '住宿', getreimAmount(personno,'餐饮') as '餐饮', getreimAmount(personno,'其他') as '其他' from db_personalinfo; insert into db_person_reiminfo select * from temptb;

真实情况是人员基本信息有500条,报销类型有20条,报销信息有35000条的样子(所以上面这条插入语句中有20个getreimAmount方法来查询不同的报销种类金额),当我执行插入报销明细的语句时非常卡,在workbench里执行时要差不多140秒,主要是查询占一大部分时间,想请教大家有没有可以优化的地方或者更好的办法?

我对MySQL不是很了解,谢谢大家了。

晖洒自如的主页 晖洒自如 | 初学一级 | 园豆:170
提问于:2014-04-16 10:44
< >
分享
最佳答案
0

查询的时间过长就优化查询,建立相应的索引,查询的SQL语句针对最左前缀设计。explain 每条sql语句。

收获园豆:30
unclerayray | 菜鸟二级 |园豆:232 | 2014-04-22 11:48
其他回答(1)
0

其实不需要这个db_person_reiminfo表, 只需要设计这个为视图就行

当你取得数据后插入上面三个表就行了, 最后设计一个表完全是多余的

收获园豆:30
风浪 | 园豆:2861 (老鸟四级) | 2014-04-16 17:26

db_person_reiminfo 表并不只是为了放数据,因为有要求在某个月的报销数据基础上做计划版本,就是会在某个月的报销数据上进行修改生成另一个版本数据,所以需要这个表的

支持(0) 反对(0) 晖洒自如 | 园豆:170 (初学一级) | 2014-04-17 09:30
清除回答草稿
   您需要登录以后才能回答,未注册用户请先注册