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

执行这个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

执行计划

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

你这都是全表扫描了啊...

这些索引都加上

formson_1354.formmain_id
formmain_1446.field0008
formmain_1447.field0002
formmain_1438.field0013
formmain_1438.field0017

不必要的order 全部删除

    ORDER BY
        a.field0007,
        a.field0017,
        b.field0001

改动的sql如下, 看下耗时, 另外建议在关联键上加索引:

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 ( -- 先过滤数据
            select 
                * 
            from formmain_1438 
             WHERE
                field0010 = '1' 
                AND field0011 = '0' 
                AND field0015 = '1' 
                AND field0016 = '1'
        ) 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

inner join
left join
子查询
IFNULL
滥用情况较多试下优化去掉一些非必要的，如果去不掉只能搞搞索引凑合用吧，join这么多明显数据库表结构也需要优化，可尝试将部分条件字段冗余出来做查询条件减少join次数

表太多，又是全扫描的形式，应该从关联、索引上进行优化，详情可参考该思路【sql优化的N种方法】，链接：https://blog.csdn.net/wang5701071/article/details/108797859

首先可以尝试使用索引来优化查询。为了让查询更快，可以在 formmain_1353、formson_1354 和 formmain_1446 这三张表上创建索引。这些索引可以加速查询中的 INNER JOIN 操作。

此外，可以尝试将 CASE 语句中的 IFNULL() 函数提取出来，因为这样可以使查询更加简洁，并且可以减少查询中的重复计算。例如，可以将：

CASE
        WHEN d.rcrySplitId IS NULL THEN
        '0' ELSE '1' 
    END AS settlementStatus,

改为：

IFNULL(d.rcrySplitId, 0) AS settlementStatus

此外，可以尝试使用 EXPLAIN

兄弟，你这只是样品数据，还是全部数据，如果就几千条，没必要优化，
如果是样品数据，一加索引，二增加运行内存，三增加线程

建议测试mysql数据库8.0.31，查询走hash jion看性能是否改善。