关于#sql优化#的问题，如何解决？

Question

执行这个sql非常耗时，在尽量不改变sql逻辑的情况下，如何优化一下 (才几千条数据，耗时12s多)
该加的索引都加了

SELECT
    c.id AS id,
    c.id AS jsdSplitId,
    IFNULL( d.rcrySplitId, 0 ) AS rcrySplitId,
    IFNULL( a.field0001, '' ) AS orderNo,
    IFNULL( a.field0002, '' ) AS checkNo,
    IFNULL( a.field0003, '' ) AS orderDate,
    IFNULL( c.field0007, '' ) AS orderType,
    IFNULL( a.field0034, '' ) AS receiveTime,
    LEFT ( IFNULL( a.field0034, '' ), 7 ) AS receiveNy,
    IFNULL( b.field0005, '' ) AS ryName,
    IFNULL( b.field0043, '' ) AS ryCode,
    IFNULL( b.field0006, '' ) AS ryType,
    IFNULL( b.field0007, '' ) AS ryLevel,
    IFNULL( b.field0008, 0 ) AS ryPrice,
    IFNULL( c.field0001, '' ) AS ny,
    IFNULL( c.field0004, 0 ) AS jsdRyMonths,
    IFNULL( c.field0005, 0 ) AS jsdRyTotal,
    IFNULL( c.field0002, '' ) AS jsdRyStartDate,
    IFNULL( c.field0003, '' ) AS jsdRyEndDate,
    IFNULL( d.rcRyFinalRyMonths, 0 ) AS rcRyFinalRyMonths,
    IFNULL( d.rcRyTotal, 0 ) AS rcRyTotal,
    IFNULL( d.finalEvaluationCoefficient, 0 ) AS finalEvaluationCoefficient,
    IFNULL( d.rcRyStartDate, '' ) AS rcRyStartDate,
    IFNULL( d.rcRyEndDate, '' ) AS rcRyEndDate,
    IFNULL( d.rcRyTotal, 0 ) - IFNULL( c.field0005, 0 ) AS dValue,
CASE
        
        WHEN d.rcrySplitId IS NULL THEN
        '0' ELSE '1' 
    END AS settlementStatus,
CASE
        
        WHEN e.field0003 IS NULL THEN
        '0' ELSE e.field0003 
    END AS verifyStatus 
FROM
    formmain_1353 a
    INNER JOIN formson_1354 b ON a.id = b.formmain_id
    INNER JOIN formmain_1446 c ON b.id = c.field0008
    LEFT JOIN formmain_1447 e ON c.id = e.field0002
    LEFT JOIN (
    SELECT
        b.id AS rcrySplitId,
        IFNULL( b.field0001, '' ) AS ny,
        IFNULL( a.field0005, '' ) AS ryName,
        IFNULL( a.field0007, '' ) AS ryCode,
        IFNULL( a.field0017, '' ) AS orderNo,
        IFNULL( b.field0007, 0 ) AS rcRyFinalRyMonths,
        IFNULL( b.field0008, 0 ) AS finalEvaluationCoefficient,
        IFNULL( b.field0009, 0 ) AS rcRyTotal,
        IFNULL( b.field0002, '' ) AS rcRyStartDate,
        IFNULL( b.field0003, '' ) AS rcRyEndDate 
    FROM
        formmain_1393 a
        INNER JOIN formmain_1438 b ON a.id = b.field0013 
    WHERE
        b.field0010 = '1' 
        AND b.field0011 = '0' 
        AND b.field0015 = '1' 
        AND b.field0016 = '1' 
    ORDER BY
        a.field0007,
        a.field0017,
        b.field0001
    ) d ON a.field0001 = d.orderNo 
    AND b.field0043 = d.ryCode 
    AND c.field0001 = d.ny 
ORDER BY
    settlementStatus DESC,
    orderNo DESC,
    ny DESC

执行计划

Answer 1

join的表太多，而且还有子查询，建议减少join的表的数量。