待办任务加载需要很长时间是什么原因

jishuang

看日志文件中的输出都已经注释了，现在慢就是数据库的慢，数据库慢就需要自己确认索引是否生效，不是看是否有索引，mysql查看索引是否生效可以到网上查找相关的资料


另为平台提供的都有数据库的初始化脚本，确认任务表中的索引跟平台初始化脚本中创建的是否一致
在tools\init\script\mysql\system.sql中

jy2013

jishuang 发表于 2020-10-28 15:02
看日志文件中的输出都已经注释了，现在慢就是数据库的慢，数据库慢就需要自己确认索引是否生效，不是看是否 ...

索引我看过是生效的，现在的问题就奇怪在，我国庆节前是好的，节后出现的这个问题，清理数据会有效果，但是未完成的数据一多，还是会出现卡顿，搞的我现在每周都做定时清理，而且其实他的数据量目前并不大，我有一家客户用的是老版本的（5.2.7）数据量目前比他多也不卡，这个是比较奇怪的点

jishuang

看数据库是不是有什么设置的变动，直接到数据库中执行sql就慢说明就是数据库的问题

jy2013

jishuang 发表于 2020-10-28 18:34
看数据库是不是有什么设置的变动，直接到数据库中执行sql就慢说明就是数据库的问题 ...

今天客户又反应卡了，我用代码 show full processlist; 查了一下 发现很多 状态是 Creating sort index，如图：

然后我把SQL 拿出来了发现没有用到索引
SELECT  SA_Task.sID AS SA_Task, SA_Task.SAIACTIVE AS sAIActive, SA_Task.SAIID AS sAIID, SA_Task.SAISTATUSID AS sAIStatusID, SA_Task.SAISTATUSNAME AS sAIStatusName, SA_Task.SACTIVE AS sActive, SA_Task.SACTIVITY AS sActivity, SA_Task.SACTIVITYINTEMPLATE AS sActivityInTemplate, SA_Task.SACTIVITYNAME AS sActivityName, SA_Task.SACTIVITYNAMES AS sActivityNames, SA_Task.SACTUALFINISHTIME AS sActualFinishTime, SA_Task.SACTUALSTARTTIME AS sActualStartTime, SA_Task.SCURL AS sCURL, SA_Task.SCATALOGID AS sCatalogID, SA_Task.SCODE AS sCode, SA_Task.SCONTENT AS sContent, SA_Task.SCREATETIME AS sCreateTime, SA_Task.SCREATORDEPTID AS sCreatorDeptID, SA_Task.SCREATORDEPTNAME AS sCreatorDeptName, SA_Task.SCREATORFID AS sCreatorFID, SA_Task.SCREATORFNAME AS sCreatorFName, SA_Task.SCREATOROGNID AS sCreatorOgnID, SA_Task.SCREATOROGNNAME AS sCreatorOgnName, SA_Task.SCREATORPERSONID AS sCreatorPersonID, SA_Task.SCREATORPERSONNAME AS sCreatorPersonName, SA_Task.SCREATORPOSID AS sCreatorPosID, SA_Task.SCREATORPOSNAME AS sCreatorPosName, SA_Task.SCUSTOMERID AS sCustomerID, SA_Task.SCUSTOMERNAME AS sCustomerName, SA_Task.SDATA1 AS sData1, SA_Task.SDATA2 AS sData2, SA_Task.SDATA3 AS sData3, SA_Task.SDATA4 AS sData4, SA_Task.SDISTRIBUTETIME AS sDistributeTime, SA_Task.SEBFIELD51 AS sEBField51, SA_Task.SEBFIELD52 AS sEBField52, SA_Task.SEBFIELD53 AS sEBField53, SA_Task.SEBFIELD54 AS sEBField54, SA_Task.SEDFIELD21 AS sEDField21, SA_Task.SEDFIELD22 AS sEDField22, SA_Task.SEDFIELD23 AS sEDField23, SA_Task.SEDFIELD24 AS sEDField24, SA_Task.SEIFIELD41 AS sEIField41, SA_Task.SEIFIELD42 AS sEIField42, SA_Task.SEIFIELD43 AS sEIField43, SA_Task.SEIFIELD44 AS sEIField44, SA_Task.SENFIELD11 AS sENField11, SA_Task.SENFIELD12 AS sENField12, SA_Task.SENFIELD13 AS sENField13, SA_Task.SENFIELD14 AS sENField14, SA_Task.SESFIELD01 AS sESField01, SA_Task.SESFIELD02 AS sESField02, SA_Task.SESFIELD03 AS sESField03, SA_Task.SESFIELD04 AS sESField04, SA_Task.SESFIELD05 AS sESField05, SA_Task.SESFIELD06 AS sESField06, SA_Task.SESFIELD07 AS sESField07, SA_Task.SESFIELD08 AS sESField08, SA_Task.SETFIELD31 AS sETField31, SA_Task.SETFIELD32 AS sETField32, SA_Task.SETFIELD33 AS sETField33, SA_Task.SETFIELD34 AS sETField34, SA_Task.SEURL AS sEURL, SA_Task.SEMERGENCYID AS sEmergencyID, SA_Task.SEMERGENCYNAME AS sEmergencyName, SA_Task.SEXECUTEMODE AS sExecuteMode, SA_Task.SEXECUTEMODE2 AS sExecuteMode2, SA_Task.SEXECUTETIME AS sExecuteTime, SA_Task.SEXECUTORDEPTID AS sExecutorDeptID, SA_Task.SEXECUTORDEPTNAME AS sExecutorDeptName, SA_Task.SEXECUTORFID AS sExecutorFID, SA_Task.SEXECUTORFNAME AS sExecutorFName, SA_Task.SEXECUTORNAMES AS sExecutorNames, SA_Task.SEXECUTOROGNID AS sExecutorOgnID, SA_Task.SEXECUTOROGNNAME AS sExecutorOgnName, SA_Task.SEXECUTORPERSONID AS sExecutorPersonID, SA_Task.SEXECUTORPERSONNAME AS sExecutorPersonName, SA_Task.SEXECUTORPOSID AS sExecutorPosID, SA_Task.SEXECUTORPOSNAME AS sExecutorPosName, SA_Task.SEXPECTFINISHTIME AS sExpectFinishTime, SA_Task.SEXPECTSTARTTIME AS sExpectStartTime, SA_Task.SFLOWID AS sFlowID, SA_Task.SFRONTID AS sFrontID, SA_Task.SHINTS AS sHints, SA_Task.SIMPORTANCEID AS sImportanceID, SA_Task.SIMPORTANCENAME AS sImportanceName, SA_Task.SKINDID AS sKindID, SA_Task.SLASTMODIFYTIME AS sLastModifyTime, SA_Task.SLIMITSECOND AS sLimitSecond, SA_Task.SLIMITTIME AS sLimitTime, SA_Task.SLOCK AS sLock, SA_Task.SNAME AS sName, SA_Task.sParentID AS sParent, SA_Task.SPLANID AS sPlanID, SA_Task.SPLANNAME AS sPlanName, SA_Task.SPREEMPTMODE AS sPreemptMode, SA_Task.SPROCESS AS sProcess, SA_Task.SPROCESSNAME AS sProcessName, SA_Task.SPROCESSTEMPLATEID AS sProcessTemplateID, SA_Task.SPROCESSTEMPLATEID2 AS sProcessTemplateID2, SA_Task.SPROCESSTEMPLATEITEMSEQUENCE AS sProcessTemplateItemSequence, SA_Task.SPROJECTID AS sProjectID, SA_Task.SPROJECTNAME AS sProjectName, SA_Task.SREMARK AS sRemark, SA_Task.SREMINDMODE AS sRemindMode, SA_Task.SRESPONSIBLE AS sResponsible, SA_Task.SSEQUENCE AS sSequence, SA_Task.SSHORTCUT AS sShortcut, SA_Task.SSOURCEID AS sSourceID, SA_Task.SSTATUSID AS sStatusID, SA_Task.SSTATUSNAME AS sStatusName, SA_Task.SSUMMARY AS sSummary, SA_Task.STEMPPERMISSIONID AS sTempPermissionID, SA_Task.STYPEID AS sTypeID, SA_Task.STYPENAME AS sTypeName, SA_Task.SVARIABLE AS sVariable, SA_Task.SWARNINGTIME AS sWarningTime, SA_Task.SWITHDRAW AS sWithdraw, SA_Task.VERSION AS version FROM SA_Task SA_Task  WHERE  ( (UPPER(SA_Task.SNAME) LIKE '%102116%') OR (UPPER(SA_Task.STYPENAME) LIKE '%102116%') OR (UPPER(SA_Task.SCREATORPERSONNAME) LIKE '%102116%') OR (UPPER(SA_Task.SEXECUTORPERSONNAME) LIKE '%102116%') OR (UPPER(SA_Task.SSTATUSNAME) LIKE '%102116%') OR (UPPER(DATE_FORMAT(SA_Task.SCREATETIME, '%Y-%m-%d %H:%i:%s')) LIKE '%102116%') OR (UPPER(DATE_FORMAT(SA_Task.SEXECUTETIME, '%Y-%m-%d %H:%i:%s')) LIKE '%102116%') ) AND ( ( '/ORG01.ogn/cGEc64raOROeL2IIp1J.dpt/tYVN4IUxVn5ihqxgWT7@cGEc64raOROeL2IIp1J.psm'  LIKE concat_ws('',SA_Task.SEXECUTORFID, '%')) AND (SA_Task.SEXECUTORFID LIKE '/%') AND ( (SA_Task.SSTATUSID = 'tesReady') OR (SA_Task.SSTATUSID = 'tesExecuting') ) )   ORDER BY sCreateTime DESC

我用explain 执行这句的结果如图：

这是你说的索引失效吗？怎么解决 ？