时间戳

首页 > 解决方案 > MYSQL - 另一个优化 sql

mysql - MYSQL - 另一个优化 sql

问题描述

我已经完成了以下 sql 查询，它从一些表中挑选数据并尝试推断出正确的巧合。如您所见，查询量很大，而且时间还不错（我用 php 代码做了同样的事情，用 15 秒来处理 2000 个数据库结果，现在用 mysql 只需要 0.6 秒）。你能告诉我一些基本的优化指南，一些明显的失败和无意义的部分吗？注意：查询工作正常（我认为现在）。

这是查询：

[查询结果][1]

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 选项的输出：[EXPLAIN option][1]

编辑：-添加解释
选项
-我的客户端（sqlyog）似乎不支持 \G，给我错误 [1]：https ://i.stack.imgur.com/DZQWS.jpg

标签： mysqlsqlquery-optimization

解决方案

这可能是一个错误。您不能以这种方式减去日期；查看DATEDIFF()功能；它可能对你有用：

AND  ABS(DATE_FORMAT(T1.datec, '%Y%m%d') -
         DATE_FORMAT(llx_csvbank.date1, '%Y%m%d'))

我希望datec是数据类型DATEor DATETIMEor TIMESTAMP。在这种情况下，DATE_FORMAT()可能是不必要的。

说起来可能更好AND some_date >= other_date - INTERVAL 2 DAY

其中一些复合索引可能会有所帮助：

facf:  INDEX(rowid,  ref, total_ttc)
llx_paiementfourn:  INDEX(ref,  amount, rowid)
T07:  INDEX(dbpaymentref)
pai2fac:  INDEX(fk_paiementfourn,  fk_facturefourn)
llx_csvbank:  INDEX(import,  date1, id, paymentref, details)
T2:  INDEX(ref, id,  date1, import, paymentref, details)
llx_csvbank:  INDEX(paymentref,  date1, id, import, details)
T03:  INDEX(paymentref, date1, id,  import, details)
T1:  INDEX(ref)
llx_csv2alias:  INDEX(alias,  id, ocurrence)
T05:  INDEX(details,  date1, id, import, manualpayment, dbpaymentref)
T06:  INDEX(ocurrence,  alias)

推荐阅读