数据库|mysql教程
mysql
优化
存储过程
数据库-mysql教程
mysql优化存储过程
照片墙源码免费下载,ubuntu 中启动网卡,tomcat7w设置jvm,nike爬虫代码,php对接app微信支付,seo边悦lzw
BEGIN
DECLARE personPlaneId BIGINT;
收支宝源码,vscode 字体渲染,启动u盘安装Ubuntu系统,tomcat各个目录,SQLite数据在哪里,jquery自动分页插件,Web前端知识框架结构,利用爬虫抓取网店信息,PHP函数 方法,seo技巧推荐,追梦网站管理系统,网页两边的浮动弹窗,dreamweaver html 模板,串口驱动程序下载lzw
DECLARE starttime datetime; #开始时间
DECLARE endtime datetime;#结束时间
DECLARE sumStudyTime INT;#当前学时
DECLARE targetStudyTime INT; #目标学时
DECLARE id1_count BIGINT default 0;#t_stage_info 第一阶段count
DECLARE id2_count BIGINT default 0;#t_stage_info 第二阶段count
DECLARE id3_count BIGINT default 0;#t_stage_info 第三阶段count
DECLARE firstEndTime datetime; #第一阶段结束时间
DECLARE secEndTime datetime; #第二阶段结束时间
DECLARE thirdEndTime datetime; #第三阶段结束时间
DECLARE done INT DEFAULT 0;
html5新闻源码,ubuntu系统画曲线,安装和配置tomcat8,爬虫缓存图片,php定时任务类下载,seo竞价收录lzw
#声明光标 DECLARE cur1 CURSOR FOR SELECT p1.id, p1.start_learn_time start_time, p1.last_date end_time, p1.sum_study_time,p1.target_study_time FROM t_person_plan p1 LEFT JOIN t_person_plan_record p2 ON p1.id = p2.person_plan_id WHERE p1.license_type = 7 AND p1.last_date <= CURRENT_DATE();#t_person_plan_record p2 这个表数据大概500多万条 #当游标到达尾部时，mysql自动设置done=1 declare continue handler for SQLSTATE 2000 SET done = 1; OPEN cur1;REPEAT /* 移动游标并赋值 */fetch cur1 into personPlaneId,startTime,endTime,sumStudyTime,targetStudyTime; if NOT done then #获取第一阶段结束时间 SET firstEndTime = (SELECT add_time FROM t_person_plan_record WHERE person_plan_id=personPlaneId AND chapter_id=29611); #获取第二阶段结束时间 SET secEndTime = (SELECT add_time FROM t_person_plan_record WHERE person_plan_id=personPlaneId AND chapter_id=29625); #获取第三阶段结束时间 SET thirdEndTime = (SELECT add_time FROM t_person_plan_record WHERE person_plan_id=personPlaneId AND chapter_id=29734); #获取主键标识 SELECT count(1) into id1_count from t_stage_info where person_plan_id=personPlaneId AND stage=1; SELECT count(1) into id2_count from t_stage_info where person_plan_id=personPlaneId AND stage=2; SELECT count(1) into id3_count from t_stage_info where person_plan_id=personPlaneId AND stage=3; IF sumStudyTime 12 AND sumStudyTime14 THEN #如果thirdEndTime为空则取最后学习的时间 SET thirdEndTime = CASE WHEN thirdEndTime IS NULL THEN endTime ELSE thirdEndTime END; #更新第一阶段数据 IF id1_count=0 THEN INSERT INTO t_stage_info(person_plan_id,start_time,end_time,sum_study_time,target_study_time,stage) VALUES(personPlaneId,startTime,firstEndTime,sumStudyTime,targetStudyTime,1); ELSE UPDATE t_stage_info SET end_time=firstEndTime,sum_study_time=sumStudyTime WHERE person_plan_id=personPlaneId AND stage=1; END IF; #更新第二阶段数据 IF id2_count=0 THEN INSERT INTO t_stage_info(person_plan_id,start_time,end_time,sum_study_time,target_study_time,stage) VALUES(personPlaneId,firstEndTime,secEndTime,sumStudyTime,targetStudyTime,2); ELSE UPDATE t_stage_info SET end_time=secEndTime,sum_study_time=sumStudyTime WHERE person_plan_id=personPlaneId AND stage=2; END IF; #更新第三阶段数据 IF id3_count=0 THEN INSERT INTO t_stage_info(person_plan_id,start_time,end_time,sum_study_time,target_study_time,stage) VALUES(personPlaneId,secEndTime,thirdEndTime,sumStudyTime,targetStudyTime,3); ELSE UPDATE t_stage_info SET end_time=thirdEndTime,sum_study_time=sumStudyTime WHERE person_plan_id=personPlaneId AND stage=3; END IF; END IF; end if; UNTIL done END REPEAT;close cur1;
END
现在执行了2小时才有了10几万数据，
我在t_person_plan_record 表里对‘person_plan_id ’建了索引，
在表t_stage_info 对person_plan_id ’建立了索引，其他我就不知道怎么优化了，
请问大神，还能怎么优化比较好呢？
