MySQL复杂查询优化指导、逻辑错误及不合理写法排查咨询

我编写了如下MySQL查询语句，用于从多张关联表中提取数据并匹配对应关联关系。目前该查询运行基本符合预期：同等数据规模下（2000条结果）原PHP处理逻辑耗时15s，改用MySQL查询实现后耗时仅0.6s，但查询语句逻辑复杂冗长，需要基础的SQL优化指导，排查其中存在的明显逻辑错误、不合理写法等问题。

查询语句 #

SELECT COUNT(dbpaymentref) AS cnt1, facf.`ref` AS factureref, facf.`total_ttc` AS factureamount, llx_paiementfourn.`amount` AS dbamount, llx_paiementfourn.`amount` AS amount2, date1, id, `import`, manualpayment, dbpaymentref, details, alias
FROM (
  SELECT T05.`date1`, T05.`id`, T05.`import`, T05.`manualpayment`, T05.`dbpaymentref`, T05.`details`, T06.`alias`
    FROM(
      SELECT T3.`date1`, T3.`id`, T3.`import`, T3.`manualpayment` AS manualpayment, 
      T3.`dbpaymentref` AS dbpaymentref, T3.`details` 
      FROM (
        SELECT T2.`date1`, T2.`id`, T2.`import`, T2.`paymentref` AS manualpayment, T2.`ref` AS 
        dbpaymentref, T2.`details` 
        FROM(
          SELECT DATE_FORMAT(llx_csvbank.date1, '%d/%m/%Y')AS date1, llx_csvbank.`id`, 
          llx_csvbank.`import`, llx_csvbank.`paymentref`, T1.`ref`, llx_csvbank.`details` 
          FROM 
            llx_csvbank
            LEFT JOIN llx_paiementfourn AS T1 ON 
              (((
              T1.`amount`=(llx_csvbank.import*-1) 
              AND ABS(DATE_FORMAT(T1.datec, '%Y%m%d')-DATE_FORMAT(llx_csvbank.date1, '%Y%m%d'))<2 
              AND T1.`ref`<>llx_csvbank.`paymentref` 
              AND LENGTH(llx_csvbank.`paymentref`)<2) ) 
              OR T1.`ref`=llx_csvbank.`paymentref` )
          ) AS T2
          LEFT JOIN llx_csvbank ON llx_csvbank.`paymentref`=T2.ref
          WHERE llx_csvbank.`paymentref` IS NULL 
          ORDER BY T2.id
       ) AS T3 
       UNION ALL
       SELECT * 
       FROM (
         SELECT DATE_FORMAT(T03.date1, '%d/%m/%Y')AS date1, T03.`id`, T03.`import`, 
         T03.`paymentref` AS manualpayment, T1.`ref` AS dbpaymentref, T03.`details` 
         FROM 
           llx_csvbank AS T03
         RIGHT JOIN llx_paiementfourn AS T1 ON 
         (
        T1.`ref`=T03.`paymentref` 
         )
         WHERE LENGTH(T03.`paymentref`)>2 AND T03.`date1` IS NOT NULL
         ORDER BY T03.`id`
       ) AS T04
    ) AS T05
    LEFT JOIN 
    (
    SELECT llx_csv2alias.`alias`, llx_csv2alias.`id`, llx_csv2alias.`ocurrence` 
    FROM llx_csv2alias
    GROUP BY llx_csv2alias.`alias`
    ) 
    AS T06 ON 
    (LOCATE(T06.ocurrence, T05.details)>0) 
    ORDER BY T05.`id` , T06.`alias`
    ) AS T07
    LEFT JOIN llx_paiementfourn ON llx_paiementfourn.`ref`=T07.`dbpaymentref`
    LEFT JOIN llx_paiementfourn_facturefourn AS pai2fac ON 
    pai2fac.fk_paiementfourn=llx_paiementfourn.rowid
    LEFT JOIN llx_facture_fourn AS facf ON pai2fac.`fk_facturefourn`=facf.`rowid` 
    GROUP BY id
;

补充说明 #

• 已提供EXPLAIN执行计划内容
• 使用的SQLyog客户端不支持\G参数，执行会报错

• 删除冗余的子查询排序：所有不带LIMIT的子查询内ORDER BY完全无效，只会额外消耗运算资源，包括T2、T3、T04、T05层级的ORDER BY语句可全部删除。
• 修复JOIN条件的索引失效问题：原语句中ABS(DATE_FORMAT(T1.datec, '%Y%m%d')-DATE_FORMAT(llx_csvbank.date1, '%Y%m%d'))<2对日期字段做了函数运算，无法命中索引，可改写为T1.datec BETWEEN DATE_SUB(llx_csvbank.date1, INTERVAL 1 DAY) AND DATE_ADD(llx_csvbank.date1, INTERVAL 1 DAY)，保留字段原生格式即可触发索引。
• 修正GROUP BY逻辑错误：当前语句使用了非标准GROUP BY写法，仅按id分组但SELECT中包含大量非聚合非分组字段，开启ONLY_FULL_GROUP_BY模式后会直接报错，且返回的非分组字段值为随机取数，存在逻辑隐患。需要将所有SELECT中的非聚合字段全部加入GROUP BY列表，或用合适的聚合函数包裹不确定值的字段。另外llx_csv2alias子查询中仅按alias分组但返回id、ocurrence字段，也存在同样的逻辑问题，需要修复。
• 合并冗余逻辑减少表扫描：当前UNION ALL的两个分支实际是按paymentref长度区分的匹配逻辑，可以合并为单查询，避免重复扫描llx_csvbank、llx_paiementfourn表。同时SELECT中重复查询了两次llx_paiementfourn.amount，可直接删除重复字段。
• 添加必要索引：对以下关联字段添加索引可进一步提升性能：
  • llx_paiementfourn：ref、amount、datec
  • llx_csvbank：paymentref、date1、import
  • llx_paiementfourn_facturefourn：fk_paiementfourn、fk_facturefourn
• 优化字符串匹配逻辑：LOCATE(T06.ocurrence, T05.details)>0属于全文模糊匹配，无法命中普通索引，数据量增大后性能会明显下降，可考虑改用全文索引，或提前预处理details字段的alias匹配结果存入表中，避免查询时实时运算。

内容的提问来源于stack exchange，提问作者danirebollo