需要 MySQL 查询优化提示

Question

你能帮我解决一个怪物吗？

你觉得这个有什么问题吗？

想达到秒以下的执行时间，有可能吗？

请询问您可能需要了解数据库结构的任何其他数据。欢迎任何提示和技巧!

SELECT 
    ORD_CLI.COD_AGE,
    ORD_CLI_RIGHE.DOC_ID,
    OFF_CLI.off_cli_id,
    ORD_CLI_RIGHE.DOC_RIGA_ID,
    ORD_CLI_RIGHE.COD_ART,
    ART_PESO.PESO_ART,
    ORD_CLI.ANNO_DOC,
    ORD_CLI.NUM_DOC,
    ORD_CLI.SERIE_DOC,
    ORD_CLI.DATA_DOC,
    CF.RAG_SOC_CF,
    AGENTI.NOME_AGE,
    ORD_CLI.COD_CF,
    ORD_CLI.COD_IVA,
    ORD_CLI.COD_DEP,
    ORD_CLI_TOT.IMPONIBILE_V1 AS IMPONIBILE_ORDINE,
    FATT_CLI_TOT.IMPONIBILE_V1 AS IMPONIBILE_FATTURA,
    ORD_CLI_TOT.IVA_V1,
    SUM(ART_PESO.PESO_ART) AS weight,
    SUM(FATT_CLI_RIGHE.QUANT_RIGA) AS quantity,
    SUM(FATT_CLI_RIGHE.QUANT_RIGA*FATT_CLI_RIGHE.PREZZO_LORDO_VU1) AS sell_price,
    SUM(FATT_CLI_RIGHE.QUANT_RIGA*DDT_FOR_RIGHE.PREZZO_LORDO_VU1) AS acqisition_price1,
    SUM(FATT_CLI_RIGHE.QUANT_RIGA*FATT_FOR_RIGHE.PREZZO_LORDO_VU1) AS acqisition_price2,
    SUM(FATT_CLI_RIGHE.QUANT_RIGA*FATT_CLI_RIGHE_PROVV.IMPORTO_PROVV_VU1) AS agent_reward,
    SUM(FATT_CLI_RIGHE.QUANT_RIGA*ART_PESO.PESO_ART * 0.13) AS transport_price,
    SUM(FATT_CLI_RIGHE.QUANT_RIGA*(
          FATT_CLI_RIGHE.PREZZO_LORDO_VU1 
        - COALESCE(DDT_FOR_RIGHE.PREZZO_LORDO_VU1, 0)
        - COALESCE(FATT_FOR_RIGHE.PREZZO_LORDO_VU1, 0)
        - COALESCE(FATT_CLI_RIGHE_PROVV.IMPORTO_PROVV_VU1, 0)
        - COALESCE(ART_PESO.PESO_ART, 0) * 0.13
    )) AS net_earning,
    OFF_CLI.stima_prezzo_acquisto,
    OFF_CLI.stima_prezzo_trasporto,
    OFF_CLI.stima_provvigioni_agenti,
    OFF_CLI.stima_utile

FROM ORD_CLI 

INNER JOIN ORD_CLI_RIGHE         
    ON ORD_CLI_RIGHE.DOC_ID = ORD_CLI.DOC_ID 

LEFT JOIN ORD_CLI_RIGHE_SPEC   
    ON ORD_CLI_RIGHE.DOC_RIGA_ID = ORD_CLI_RIGHE_SPEC.DOC_RIGA_ID

INNER JOIN ART_PESO 
    ON ART_PESO.COD_ART = ORD_CLI_RIGHE.COD_ART

INNER JOIN ORD_CLI_TOT 
    ON ORD_CLI.DOC_ID = ORD_CLI_TOT.DOC_ID

INNER JOIN AGENTI 
    ON AGENTI.COD_AGE = ORD_CLI.COD_AGE

INNER JOIN CF 
    ON CF.COD_CF = ORD_CLI.COD_CF

LEFT JOIN FATT_CLI_RIGHE_SPEC   
    ON ORD_CLI_RIGHE.DOC_RIGA_ID = FATT_CLI_RIGHE_SPEC.ORD_RIGA_ID

LEFT JOIN FATT_CLI_RIGHE        
    ON FATT_CLI_RIGHE.DOC_RIGA_ID = FATT_CLI_RIGHE_SPEC.DOC_RIGA_ID

LEFT JOIN FATT_CLI_TOT
    ON FATT_CLI_RIGHE.DOC_ID = FATT_CLI_TOT.DOC_ID

LEFT JOIN FATT_CLI_RIGHE_PROVV 
    ON FATT_CLI_RIGHE_PROVV.DOC_RIGA_ID = FATT_CLI_RIGHE_SPEC.DOC_RIGA_ID

LEFT JOIN FATT_CLI_RIGHE_LOTTI 
    ON FATT_CLI_RIGHE_LOTTI.DOC_RIGA_ID = FATT_CLI_RIGHE_SPEC.DOC_RIGA_ID

LEFT JOIN  DDT_FOR_RIGHE_LOTTI  
    ON DDT_FOR_RIGHE_LOTTI.COD_LOT = FATT_CLI_RIGHE_LOTTI.COD_LOT

LEFT JOIN  DDT_FOR_RIGHE         
    ON DDT_FOR_RIGHE.DOC_RIGA_ID = DDT_FOR_RIGHE_LOTTI.DOC_RIGA_ID

LEFT JOIN FATT_FOR_RIGHE         
    ON FATT_FOR_RIGHE.DOC_RIGA_ID = FATT_CLI_RIGHE_LOTTI.COD_LOT

LEFT JOIN OFF_CLI_RIGHE
    ON OFF_CLI_RIGHE.DOC_RIGA_ID = ORD_CLI_RIGHE_SPEC.OFF_RIGA_ID

LEFT JOIN OFF_CLI
    ON OFF_CLI.DOC_ID = OFF_CLI_RIGHE.DOC_ID

WHERE 
    ORD_CLI.COD_BUSN_UN='P' 
AND OFF_CLI_RIGHE.DOC_RIGA_ID IS NOT NULL

AND ORD_CLI.DATA_DOC >= '2012-11-29'
AND ORD_CLI.DATA_DOC <= '2013-02-28'   
GROUP BY ORD_CLI.DOC_ID 
ORDER BY   ORD_CLI.DATA_DOC 
DESC LIMIT 30 OFFSET 0

执行时间

Showing rows 0 - 29 ( 30 total, Query took 6.3458 sec)

查询的解释

+----+-------------+----------------------+--------+-----------------------------------------------------------------------------+----------------------------------+---------+--------------------------------------------+------+----------+----------------------------------------------+
| id | select_type | table                | type   | possible_keys                                                               | key                              | key_len | ref                                        | rows | filtered | Extra                                        |
+----+-------------+----------------------+--------+-----------------------------------------------------------------------------+----------------------------------+---------+--------------------------------------------+------+----------+----------------------------------------------+
|  1 | SIMPLE      | ORD_CLI              | range  | PRIMARY,ORD_CLI_DATA_DOC,ORD_CLI_COD_CF,ORD_CLI_COD_BUSN_UN,ORD_CLI_COD_AGE | ORD_CLI_DATA_DOC                 | 4       | NULL                            | 3728 |   100.00 | Using where; Using temporary; Using filesort |
|  1 | SIMPLE      | AGENTI               | eq_ref | PRIMARY                                                                     | PRIMARY                          | 38      | ORD_CLI.COD_AGE                 |    1 |   100.00 | Using where                                  |
|  1 | SIMPLE      | CF                   | eq_ref | PRIMARY                                                                     | PRIMARY                          | 38      | ORD_CLI.COD_CF                  |    1 |   100.00 |                                              |
|  1 | SIMPLE      | ORD_CLI_TOT          | eq_ref | PRIMARY                                                                     | PRIMARY                          | 62      | ORD_CLI.DOC_ID                  |    1 |   100.00 |                                              |
|  1 | SIMPLE      | ORD_CLI_RIGHE        | ref    | PRIMARY,ORD_CLI_RIGHE_DOC_ID,ORD_CLI_RIGHE_COD_ART                          | ORD_CLI_RIGHE_DOC_ID             | 62      | ORD_CLI_TOT.DOC_ID              |    2 |   100.00 | Using where                                  |
|  1 | SIMPLE      | ART_PESO             | eq_ref | PRIMARY                                                                     | PRIMARY                          | 92      | ORD_CLI_RIGHE.COD_ART           |    1 |   100.00 |                                              |
|  1 | SIMPLE      | ORD_CLI_RIGHE_SPEC   | eq_ref | PRIMARY,ORD_CLI_RIGHE_SPEC_OFF_RIGA_ID                                      | PRIMARY                          | 92      | ORD_CLI_RIGHE.DOC_RIGA_ID       |    1 |   100.00 | Using where                                  |
|  1 | SIMPLE      | OFF_CLI_RIGHE        | ref    | DOC_RIGA_ID                                                                 | DOC_RIGA_ID                      | 92      | ORD_CLI_RIGHE_SPEC.OFF_RIGA_ID  |    1 |   100.00 | Using where                                  |
|  1 | SIMPLE      | OFF_CLI              | ref    | DOC_ID                                                                      | DOC_ID                           | 63      | OFF_CLI_RIGHE.DOC_ID              |    1 |   100.00 |                                              |
|  1 | SIMPLE      | FATT_CLI_RIGHE_SPEC  | ref    | FATT_CLI_RIGHE_SPEC_ORD_RIGA_ID                                             | FATT_CLI_RIGHE_SPEC_ORD_RIGA_ID  | 93      | ORD_CLI_RIGHE.DOC_RIGA_ID       |    1 |   100.00 | Using index                                  |
|  1 | SIMPLE      | FATT_CLI_RIGHE       | eq_ref | PRIMARY                                                                     | PRIMARY                          | 92      | FATT_CLI_RIGHE_SPEC.DOC_RIGA_ID |    1 |   100.00 |                                              |
|  1 | SIMPLE      | FATT_CLI_TOT         | eq_ref | PRIMARY                                                                     | PRIMARY                          | 62      | FATT_CLI_RIGHE.DOC_ID           |    1 |   100.00 |                                              |
|  1 | SIMPLE      | FATT_CLI_RIGHE_PROVV | ref    | FATT_CLI_RIGHE_PROVV_DOC_RIGA_ID                                            | FATT_CLI_RIGHE_PROVV_DOC_RIGA_ID | 92      | FATT_CLI_RIGHE_SPEC.DOC_RIGA_ID |    1 |   100.00 |                                              |
|  1 | SIMPLE      | FATT_CLI_RIGHE_LOTTI | ref    | FATT_CLI_RIGHE_LOTTI_DOC_RIGA_ID                                            | FATT_CLI_RIGHE_LOTTI_DOC_RIGA_ID | 92      | FATT_CLI_RIGHE_SPEC.DOC_RIGA_ID |    1 |   100.00 |                                              |
|  1 | SIMPLE      | DDT_FOR_RIGHE_LOTTI  | ref    | DDT_FOR_RIGHE_LOTTI_COD_LOT                                                 | DDT_FOR_RIGHE_LOTTI_COD_LOT      | 92      | FATT_CLI_RIGHE_LOTTI.COD_LOT    |    1 |   100.00 |                                              |
|  1 | SIMPLE      | DDT_FOR_RIGHE        | eq_ref | PRIMARY                                                                     | PRIMARY                          | 92      | DDT_FOR_RIGHE_LOTTI.DOC_RIGA_ID |    1 |   100.00 |                                              |
|  1 | SIMPLE      | FATT_FOR_RIGHE       | eq_ref | PRIMARY                                                                     | PRIMARY                          | 92      | FATT_CLI_RIGHE_LOTTI.COD_LOT    |    1 |   100.00 |                                              |
+----+-------------+----------------------+--------+-----------------------------------------------------------------------------+----------------------------------+---------+--------------------------------------------+------+----------+----------------------------------------------+

下面是查询执行后show status like 'Handler%'的结果

Handler_commit, 2
Handler_delete, 0
Handler_discover, 0
Handler_prepare, 0
Handler_read_first, 0
Handler_read_key, 421001
Handler_read_last, 0
Handler_read_next, 240344
Handler_read_prev, 0
Handler_read_rnd, 30
Handler_read_rnd_next, 2412
Handler_rollback, 0
Handler_savepoint, 0
Handler_savepoint_rollback, 0
Handler_update, 31846
Handler_write, 2409

数据库结构:https://gist.github.com/moiseevigor/4988fc8868f92643c9fb

编辑 1

创建索引后

ALTER TABLE `TCross5_NP`.`ORD_CLI` 
ADD INDEX `ORD_CLI_MULTI` (`COD_BUSN_UN` ASC, `DATA_DOC` ASC, `DOC_ID` ASC) ;

执行时间下降了 2 倍，但仍达到 ORD_CLI_MULTI 索引

Answer 1

首先，(并且在您似乎正在处理大量“查找”辅助表引用的许多其他类似查询中提供帮助)，将查询开始更改为

SELECT STRAIGHT_JOIN 

它指示引擎按照您列出的确切顺序运行查询。这将防止它尝试使用查找表作为主要考虑因素并尝试使用反向词或绕过以获取数据。有时效果很好，有时(根据我的经验很少见)会影响性能。

接下来，由于您正在寻找“AND OFF_CLI_RIGHE.DOC_RIGA_ID IS NOT NULL”，我会在加入时将您的 LEFT JOIN 更改为 INNER JOIN。

INNER JOIN ORD_CLI_RIGHE_SPEC   
   ON ORD_CLI_RIGHE.DOC_RIGA_ID = ORD_CLI_RIGHE_SPEC.DOC_RIGA_ID

   INNER JOIN OFF_CLI_RIGHE
      ON ORD_CLI_RIGHE_SPEC.OFF_RIGA_ID = OFF_CLI_RIGHE.DOC_RIGA_ID

从而消除 WHERE 子句中的“AND ... is not null”。

最后，我会有一个索引，它包含多个可以优化的部分对于查询...

CREATE index MultipleParts on ORD_CLI  ( COD_BUSN_UN, DATA_DOC, DOC_ID );

多部分索引将有助于查询的 WHERE、GROUP BY 和 ORDER BY。