请教各位朋友一个问题:在mysql里执行一个存储过程,该存储过程做的事情是,循环执行动态sql(where子句和order by是动态的,该sql为多表查询并返回满足条件的前几条记录。用order by 再 limit),执行动态sql后再将查询出满足条件的记录更新都一张表中。该存储过程循环再做删除、插入、查询(含动态sql查询)、更新操作。让我纳闷的地方是:从监控来看,该存储过程刚开始执行时,循环一次大概要执行时间不到1秒,但在执行了一段时间后,内存占用上升,速度也逐渐变慢(但系统总内存占用也就50%左右),到后面大概6、7秒循环一次。于是将该存储过程停止,然后重新执行,则执行速度又开始从快到慢。存储过程的执行并没有累加数据,而动态sql随每次循环,where和order by都不一样,但单独拉出来在执行速度上并没什么差异。所以,每次执行都是在做类似的处理,为什么会出现这种情况?又该如何优化呢?盼了解的人帮我分析下,谢谢!我把InnoDB的引擎改成了MYISAM,并没有实质性的提升。【应用场景】:纯粹mysql数据库端执行,没有外部调用。
解决方案 »
- 简单问题求助
- load的问题
- 进来看看,关于周和月查询sql的写法
- mysql的存储怎么写?帮我看看我这个写的哪里有问题?谢谢
- MySql中如何在本机命令行中模拟另一台机器上的用户登录?
- 几个constraints 的写法
- 如何解决Warning: mysql_connect() [function.mysql-connect]: Can't connect to MySQL server on 'localhost' (10048)
- 使用标准sql语句,查找一个数据库文件中有多少个表的命令是什么?
- Mysq中只有一个表的存储引擎室InnoDB其他的是Mysam,这个表是否能用事务?
- 这种设计一定要多表关联吗?
- MySQL密码忘记了,求改密!
- mysql root 用户不能授权
再补充下哦,是2层循环体,外循环(7万7千次),内循环(1百多次),动态sql在内循环内执行。
IF p_begin_qh = 0 OR p_begin_qh = "" OR ISNULL(p_begin_qh) THEN
SET v_loop_num = v_min_qh;
ELSE
SET v_loop_num = p_begin_qh;
END IF; TRUNCATE TABLE ss_success_list;
SELECT s.sql_content INTO v_loop_part_sql FROM ss_where_condition_sql_result_list s WHERE s.id = v_loop_sql_id; WHILE v_loop_num < v_max_qh DO SELECT MIN(srl.qh) INTO v_next_qh FROM ss_record_list srl WHERE srl.qh > v_loop_num;
SELECT srl.r1,srl.r2,srl.r3,srl.r4,srl.r5,srl.r6,srl.r7,srl.b1
INTO v_loop_next_record_r1,v_loop_next_record_r2,v_loop_next_record_r3,v_loop_next_record_r4,v_loop_next_record_r5,v_loop_next_record_r6,v_loop_next_record_r7,v_loop_next_record_b1
FROM ss_record_list srl WHERE srl.qh = v_next_qh; SET v_flag1_count = 0;
SET v_flag2_count = 0;
TRUNCATE TABLE ss_sm_array_bi; #动态生成v_loop_sql
SET v_loop_sql = CONCAT("INSERT INTO ss_sm_array_bi ","SELECT t_out2.sf_code,t_out2.num FROM (");
SET v_loop_sql = CONCAT(v_loop_sql,"SELECT t_out1.sf_code,t_out1.num FROM (");
SET v_loop_sql = CONCAT(v_loop_sql,"SELECT s.sf_code,s.num FROM ss_sm_list s ");
SET v_loop_sql = CONCAT(v_loop_sql,"LEFT JOIN ss_sf_list_solution_1_1 sf ON s.sf_code = sf.sf_code ");
SET v_loop_sql = CONCAT(v_loop_sql,"LEFT JOIN ss_anquan_qh_sf_list saq ON s.qh = saq.qh AND s.sf_code = saq.sf_code ");
SET v_loop_sql = CONCAT(v_loop_sql,"WHERE s.qh = ",v_loop_num," AND sf.num_type = 2 ");
SET v_loop_sql = CONCAT(v_loop_sql," ",v_loop_part_sql);
SET v_loop_sql = CONCAT(v_loop_sql,") t_out1 LIMIT 0,",v_sm_num,") t_out2;");
#执行动态sql
SET @v_sql=v_loop_sql;
PREPARE stmt FROM @v_sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
#++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ SELECT COUNT(1) INTO v_exist FROM ss_sm_array_bi s
WHERE (s.num = v_loop_next_record_r1 OR s.num = v_loop_next_record_r2 OR s.num = v_loop_next_record_r3 OR s.num = v_loop_next_record_r4
OR s.num = v_loop_next_record_r5 OR s.num = v_loop_next_record_r6 OR s.num = v_loop_next_record_r7 OR s.num = v_loop_next_record_b1);
SELECT COUNT(1) INTO v_real FROM ss_sm_array_bi s; IF v_real >= v_sm_num THEN
IF v_exist <> 0 THEN
INSERT INTO ss_success_list VALUES (v_loop_num,0);
ELSE
INSERT INTO ss_success_list VALUES (v_loop_num,1);
END IF;
END IF; SELECT MIN(srl.qh) INTO v_loop_num FROM ss_record_list srl WHERE srl.qh > v_loop_num; END WHILE;
SELECT COUNT(1) INTO v_flag1_count FROM ss_success_list a WHERE a.flag = 1;
SELECT COUNT(1) INTO v_flag2_count FROM ss_success_list a WHERE a.flag = 0; UPDATE ss_where_condition_sql_result_list SET right_kill_total = v_flag1_count , wrong_kill_total = v_flag2_count
WHERE id = v_loop_sql_id;
SELECT MIN(id) INTO v_loop_sql_id FROM ss_where_condition_sql_result_list s WHERE s.id > v_loop_sql_id;
END WHILE;