老sql

SELECT DISTINCT
    RES.CREATE_TIME_ AS acflowlast_end_date_time,
    RES.PROC_INST_ID_ AS acflowprocess_instance_id,
    RES.NAME_ AS acflowtask_name,
    f.moduleId AS acflowmodule_id,
    RES.TASK_DEF_KEY_ AS acflowtask_definition_key,
    f.STATUS AS acflowprocess_status,
    f.userId AS acflowuser_id
    ,business_.* 
FROM
    (
    SELECT
        RES_.PROC_INST_ID_ AS PROC_INST_ID_,
        RES_.CREATE_TIME_,
        RES_.NAME_,
        RES_.task_def_key_ 
    FROM
        ACT_RU_TASK RES_
        JOIN flow_instance f ON f.processInstanceId = RES_.PROC_INST_ID_ 
        AND f.moduleId IN ('receipt-and-installation' ) 
    
        
        UNION ALL
        
        
    SELECT
        RES_.PROC_INST_ID_ AS PROC_INST_ID_,
        RES_.END_TIME_ AS CREATE_TIME_,
        '已办结' AS NAME_,
        '' task_def_key_ 
    FROM
        ACT_HI_PROCINST RES_ 
    WHERE
        RES_.END_TIME_ IS NOT NULL 
    ) RES
    JOIN flow_instance f ON f.processInstanceId = RES.PROC_INST_ID_ 
    AND f.STATUS IN ( 1, 2 ) 
    AND f.moduleId IN ('receipt-and-installation' )
    JOIN apply_doc_currency business_ ON f.appId = business_.id
    AND business_.del = '0' 
WHERE
    1 = 1 
ORDER BY
    business_.createtime   DESC
LIMIT 20

速度

排查

经过排查发现order by语句花费的时间较多，去掉order by只需要5秒钟

既然order by排序花了接近30秒，就直接解决order by的问题，不再关注别的方面

explain查看索引情况，发现最后使用了filesort排列

由于排序字段所在的表数据量巨大，并且filesort不会使用任何索引，因此造成了order by巨慢

而这个sql属于监控信息的查询，必须使用时间排序，因此删掉order by是不可能的了

解决

思路是强制使用createtime的索引，而不是使用filesort排序

关于filesort （文件排序），可以参考这篇文章MySQL filesort原理及优化 - 知乎 (zhihu.com)

对order by的字段添加索引

在要强制索引的join语句表后 直接加上FORCE INDEX (索引名称) 来强制使用索引

新sql

SELECT DISTINCT
    RES.CREATE_TIME_ AS acflowlast_end_date_time,
    RES.PROC_INST_ID_ AS acflowprocess_instance_id,
    RES.NAME_ AS acflowtask_name,
    f.moduleId AS acflowmodule_id,
    RES.TASK_DEF_KEY_ AS acflowtask_definition_key,
    f.STATUS AS acflowprocess_status,
    f.userId AS acflowuser_id
    ,business_.* 
FROM
    (
    SELECT
        RES_.PROC_INST_ID_ AS PROC_INST_ID_,
        RES_.CREATE_TIME_,
        RES_.NAME_,
        RES_.task_def_key_ 
    FROM
        ACT_RU_TASK RES_
        JOIN flow_instance f ON f.processInstanceId = RES_.PROC_INST_ID_ 
        AND f.moduleId IN ('receipt-and-installation' ) 
    
        
        UNION ALL
        
        
    SELECT
        RES_.PROC_INST_ID_ AS PROC_INST_ID_,
        RES_.END_TIME_ AS CREATE_TIME_,
        '已办结' AS NAME_,
        '' task_def_key_ 
    FROM
        ACT_HI_PROCINST RES_ 
    WHERE
        RES_.END_TIME_ IS NOT NULL 
    ) RES
    JOIN flow_instance f ON f.processInstanceId = RES.PROC_INST_ID_ 
    AND f.STATUS IN ( 1, 2 ) 
    AND f.moduleId IN ('receipt-and-installation' )
    JOIN apply_doc_currency business_ force index(INDEX_CUR_CREATETIME) ON f.appId = business_.id
    AND business_.del = '0' 
WHERE
    1 = 1 
ORDER BY
    business_.createtime   DESC
LIMIT 20

explain一下可以看见走的已经是强制加上去的索引了，而不是使用filesort

速度