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

[zhjackey]

有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不是很了解，谢谢大家了。

[benluobo]

第一个问题： 应该是没必要建立function，直接连接是可以得到需要的报销信息，最好在db_reiminfo上的personNo上建立索引 第二个问题： 不需要建立临时表，直接insert into table select 应该会好点

[zhjackey]

引用 1 楼 benluobobo 的回复:

第一个问题： 应该是没必要建立function，直接连接是可以得到需要的报销信息，最好在db_reiminfo上的personNo上建立索引 第二个问题： 不需要建立临时表，直接insert into table select 应该会好点

请问如果不建立function，怎么能得到每项报销信息呢？这条语句要怎么写？

[benluobo]

select a.id,a.personno,a.name,a.dep, sum(case when locate(r_type,'t2+t3') > 0 then amount else 0 end) dache, sum(case when locate(r_type,'t1+t3') > 0 then amount else 0 end) zhusu, sum(case when locate(r_type,'t3') > 0 then amount else 0 end) canyin from db_personinfo a left join db_reiminfo c on a.personNo = c.personNo group by personno

[wwwwb]

这个是要用动态执行的方法吧

[zhjackey]

引用 4 楼 wwwwb 的回复:

这个是要用动态执行的方法吧

您这里的动态执行怎么理解？

[z5wjz]

mysql> select * from db_personalinfo;+------+----------+--------+------+ | id | personno | name | dep | +------+----------+--------+------+ | 1 | 1000 | 张三 | d1 | | 2 | 1001 | 李四 | d2 | | 3 | 1002 | 王五 | d3 | +------+----------+--------+------+ 3 rows in set (0.00 sec) mysql> select * from 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 | +------+----------+--------+--------+---------+ 7 rows in set (0.00 sec) mysql> select * from db_reimtype; +------+-----------+-----------+ | id | reim_name | reim_expr | +------+-----------+-----------+ | 1 | 打车 | t2+t3 | | 2 | 住宿 | t1+t3 | | 3 | 餐饮 | t3 | +------+-----------+-----------+ 3 rows in set (0.00 sec) --------------------------------以下是查询语句，供参考------------------------- mysql> SELECT a.id,a.personno,a.name,a.dep -> ,SUM(CASE WHEN c.reim_name='打车' THEN amount ELSE 0 END) AS '打车' -> ,SUM(CASE WHEN c.reim_name='住宿' THEN amount ELSE 0 END) AS '住宿' -> ,SUM(CASE WHEN c.reim_name='餐饮' THEN amount ELSE 0 END) AS '餐饮' -> FROM db_personalinfo a JOIN db_reiminfo b -> ON a.personno=b.personno -> JOIN db_reimtype c -> ON INSTR(c.reim_expr,b.r_type) -> GROUP BY a.id,a.personno,a.name,a.dep; +------+----------+--------+------+--------+--------+--------+ | id | personno | name | dep | 打车 | 住宿 | 餐饮 | +------+----------+--------+------+--------+--------+--------+ | 1 | 1000 | 张三 | d1 | 310 | 660 | 110 | | 2 | 1001 | 李四 | d2 | 530 | 210 | 210 | +------+----------+--------+------+--------+--------+--------+ 2 rows in set (0.00 sec)

[ACMAIN_CHM]

http://blog.csdn.net/acmain_chm/article/details/4283943 MySQL交叉表 在某些数据库中有交叉表，但在MySQL中却没有这个功能，但网上看到有不少朋友想找出一个解决方法，特发贴集思广义。http://topic.csdn.net/u/20090530/23/0b782674-4b0b-4cf5-bc1a-e8914aaee5ab.html?96198现整理解法如下：数据样本： create table tx( id int primary key, c1 c...

[WWWWA]

引用 5 楼 zhjackey 的回复:

[quote=引用 4 楼 wwwwb 的回复:] 这个是要用动态执行的方法吧

您这里的动态执行怎么理解？[/quote] 字符串累加生成SQL语句，再执行的方法