需要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 

它指示引擎按照所列的确切顺序运行查询。这将防止它试图使用查找表作为首要考虑，并试图工作反向或结束，以获得数据。有时效果很好，其他时候(在我的经验中很少)，会影响性能。

接下来，由于您正在寻找一个“和OFF_CLI_RIGHE.DOC_RIGA_ID是非空”，我会将您的左联接更改为内部连接。

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 .非空“。

最后，我将有一个索引，它是可以为查询优化的多个部分.

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

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