慢速日志里发现问题
本帖最后由 dowhile 于 2016-10-24 18:31 编辑最近领导反映手机精灵卡,慢,无反应,于是去慢速日志里看看大于3秒的查询有哪些,发现以下几个慢速查询
--------------------------------------------
# Time: 161024 17:03:32
# User@Host: root @Id: 37562
# Query_time: 7.581613Lock_time: 0.000000 Rows_sent: 294885Rows_examined: 965828
SET timestamp=1477299812;
SELECT frp.ID, frp.RUN_ID, frp.TIME_OUT, frp.TIME_OUT_TYPE, frp.TIME_OUT_ATTEND,
frp.USER_ID, frp.PRCS_ID, frp.FLOW_PRCS,frp.TIME_OUT_FLAG,
frp.CREATE_TIME, frp.ID, frp.PRCS_FLAG, frp.PRCS_TIME, frp.DELIVER_TIME,
fr.RUN_NAME, fr.FLOW_IDFROM flow_run_prcs AS frp INNER JOIN flow_run AS fr ON fr.run_id = frp.run_idWHERE frp.time_out_flag = 0 AND frp.time_out != '' AND fr.DEL_FLAG = 0;
这个查询会返回294885行记录,耗时7秒多,每隔一个小时刷一次,不知道通达为什么要刷30万数据.......
-----------------------------------------------
# Time: 161024 17:53:53
# User@Host: root @Id: 2
# Query_time: 11.450421Lock_time: 0.000000 Rows_sent: 0Rows_examined: 1620570
SET timestamp=1477302833;
update USER set ONLINE=ONLINE+300 where UID in (1063,46,4489,9555,1,38,49,4795,9555,187,882,9858,13936,6617,78,4787,14563,233,1483,14392,33,11002,15246,183,1011,14074,4794,165,53,4792,2045,9832,9881,10,12086,5665,4783,4793,9949,4786,11514,9866,14631,1140,4774,2016,4781,4,4515,599,14684,4782,181,2217,321,110,11102,9827,3849,796,223,1060,1757,168,1,14928,14521,14444,6797,13128,71,14500,4775,11250,11805,11439,9071,8882,9858,275,2130,58,945,13262,13961,2298,7359,13331,23,27,77,4515,91,14041,21,2524,100,8962,136,14864,7228,8171,7775,41,27,4790,14483,12,8621,11515);
这个更新,每隔五分钟左右执行一次,也是很耗时,不知道为什么
------------------------------------------------
# Time: 161022 21:51:09
# User@Host: root @Id: 17739
# Query_time: 5.085609Lock_time: 0.000000 Rows_sent: 0Rows_examined: 963261
SET timestamp=1477144269;
SELECT FLOW_RUN.RUN_ID,
FLOW_RUN.WORK_LEVEL,
FLOW_RUN.FLOW_ID,
FLOW_RUN.RUN_NAME,
FLOW_RUN.BEGIN_USER,
FLOW_RUN.END_TIME,
FLOW_RUN_PRCS.ID AS PRCS_KEY_ID,
FLOW_RUN_PRCS.PRCS_ID,
FLOW_RUN_PRCS.FLOW_PRCS,
FLOW_RUN_PRCS.USER_ID,
FLOW_RUN_PRCS.COMMENT,
FLOW_RUN_PRCS.OP_FLAG,
FLOW_RUN_PRCS.PRCS_FLAG,
FLOW_RUN_PRCS.TIME_OUT_FLAG,
FLOW_RUN_PRCS.PRCS_TIME,
FLOW_RUN_PRCS.TIME_OUT,
count(distinct FLOW_RUN.RUN_ID,FLOW_RUN.FLOW_ID,FLOW_RUN_PRCS.PRCS_ID,FLOW_RUN_PRCS.FLOW_PRCS) FROM FLOW_RUN_PRCS INNER JOIN FLOW_RUN ON FLOW_RUN.RUN_ID = FLOW_RUN_PRCS.RUN_ID WHERE( (FLOW_RUN_PRCS.PRCS_FLAG != '5'AND FLOW_RUN_PRCS.CHILD_RUN = '0'ANDFIND_IN_SET('00011721',FLOW_RUN_PRCS.OTHER_USER)AND FLOW_RUN_PRCS.CHILD_RUN = '0')AND(FLOW_RUN.DEL_FLAG = '0'))GROUP BYFLOW_RUN.RUN_ID,FLOW_RUN.FLOW_ID,FLOW_RUN_PRCS.PRCS_ID,FLOW_RUN_PRCS.FLOW_PRCS ORDER BY FLOW_RUN.RUN_ID DESC, FLOW_RUN_PRCS.FLOW_PRCS ASC LIMIT 0, 10;
这个不定时出现,where中使用了find_in_set()函数,好象是没有办法做索引优化的,FLOW_RUN_PRCS的记录差不多一百万了,再过一年半载不知道会不会更慢,汗
---------------------------------------------------------
求解决方案
oa版本已是2016最新版本
页:
[1]