Início / dba / Perguntas / 136476
Accepted
Yassine LD
Asked: 2016-04-26 07:23:15 +0800 CST

Como posso remover 'Usando temporário' e acelerar minha consulta

  • 772

Oi, eu estava trabalhando por horas para acelerar esta consulta usando índices, mas é inútil. Estou tentando otimizar eliminando 'Usando temporário' e usando apenas índices. Será que existe outra alternativa para agilizar essa consulta??

consulta :

 SELECT DISTINCT 
    tbl_dv_fg.id,
    IFNULL(
      tbl_dv_fg.nb_action_rea / tbl_dv_fg.nb_action_total,
      0
    ) AS txRea,
    tbl_dv_fg.num_cmde AS numcmde,
    IF(tbl_fgp.NUMCMDE IS NOT NULL, 1, 0) AS isPlurale,
    IF(
      tbl_dv_fg.relance_date = DATE_FORMAT(NOW(), '%Y-%m-%d'),
      0,
      1
    ) AS DateRelanceArrivee,
    DATE_FORMAT(
      IFNULL(
        tbl_fg.szdatevente,
        tbl_fgp.szdatevente
      ),
      '%d/%m/%Y'
    ) AS szdatevente,
    DATE_FORMAT(
      tbl_pilotage.date_mada,
      '%d/%m/%Y'
    ) AS dateMadaPilotage,
    DATE_FORMAT(
      tbl_pilotage.date_mada_eng,
      '%d/%m/%Y'
    ) AS dateMadaEngPilotage,
    DATE_FORMAT(
      tbl_pilotage.date_liv_clt_conv,
      '%d/%m/%Y'
    ) AS dateLivCltConv,
    IFNULL(
      tbl_fg.szdatevente,
      tbl_fgp.szdatevente
    ) AS szdateventeSort,
    tbl_pilotage.date_mada AS dateMadaPilotageSort,
    tbl_pilotage.date_mada_eng AS dateMadaEngPilotageSort,
    tbl_pilotage.date_liv_clt_conv AS dateLivCltConvSort,
    tbl_pilotage.ord_c AS ordC,
    CONCAT(
      tbl_dv_fg.sznomclient,
      ' ',
      tbl_dv_fg.szprenomclient
    ) AS CLIENT,
    CONCAT(
      tbl_user.user_nom,
      ' ',
      tbl_user.user_prenom
    ) AS vendeur,
    DATE_FORMAT(
      tbl_livraison.livraison_date_facture,
      '%d/%m/%Y'
    ) AS dateFacture,
    IFNULL(
      tbl_fg.SZMARQUE,
      tbl_fgp.SZMARQUE
    ) AS SZMARQUE,
    IFNULL(
      tbl_fg.SZMODELE,
      tbl_fgp.SZMODELE
    ) AS SZMODELE 
  FROM
    `tbl_dv_fg` 
    LEFT JOIN `tbl_fg_plural` 
      ON (
        tbl_dv_fg.num_cmde = tbl_fg_plural.num_cmde 
        AND tbl_dv_fg.affaire_id = tbl_fg_plural.affaire_id
      ) 
    LEFT JOIN `tbl_fg` 
      ON (
        tbl_dv_fg.num_cmde = tbl_fg.NUMCMDE 
        AND tbl_dv_fg.affaire_id = tbl_fg.user_rrf
      ) 
    LEFT JOIN `tbl_fg` `tbl_fgp` 
      ON (
        tbl_fg_plural.user_id = tbl_fgp.user_id 
        AND tbl_fg_plural.fg_id = tbl_fgp.id
      ) 
    LEFT JOIN `tbl_pilotage` 
      ON (
        tbl_dv_fg.NUM_CMDE = tbl_pilotage.num_cde_sed 
        AND tbl_dv_fg.AFFAIRE_ID = tbl_pilotage.rrf
      ) 
    LEFT JOIN `tbl_user` 
      ON (tbl_dv_fg.id_user = tbl_user.id) 
    LEFT JOIN `tbl_livraison` 
      ON (
        tbl_pilotage.chassis = tbl_livraison.livraison_vin
      ) 
    LEFT JOIN `ged_tbl_document_dv` `dv_document_1` 
      ON (
        tbl_dv_fg.id = dv_document_1.dv_id
      ) 
    LEFT JOIN `ged_tbl_document_type` `document_type_1` 
      ON (
        dv_document_1.document_type_id = document_type_1.document_type_id
      ) 
    LEFT JOIN `ged_tbl_document_dv` `dv_document_2` 
      ON (
        tbl_dv_fg.id = dv_document_2.dv_id
      ) 
    LEFT JOIN `ged_tbl_document_type` `document_type_2` 
      ON (
        dv_document_2.document_type_id = document_type_2.document_type_id
      ) 
    LEFT JOIN `ged_tbl_document_dv` `dv_document_3` 
      ON (
        tbl_dv_fg.id = dv_document_3.dv_id
      ) 
    LEFT JOIN `ged_tbl_document_type` `document_type_3` 
      ON (
        dv_document_3.document_type_id = document_type_3.document_type_id
      ) 
    LEFT JOIN `ged_tbl_document_dv` `dv_document_4` 
      ON (
        tbl_dv_fg.id = dv_document_4.dv_id
      ) 
    LEFT JOIN `ged_tbl_document_type` `document_type_4` 
      ON (
        dv_document_4.document_type_id = document_type_4.document_type_id
      ) 
  WHERE tbl_dv_fg.affaire_id = '00001154' 
    AND (
      (
        (
          (
            (
              (
                (
                  (
                    (
                      (
                        (
                          (
                            (
                              (
                                (
                                  (
                                    (
                                      (
                                        (
                                          (
                                            (
                                              (
                                                (
                                                  tbl_dv_fg.id_user = '811' 
                                                  OR tbl_dv_fg.id_user = '789'
                                                ) 
                                                OR tbl_dv_fg.id_user = '790'
                                              ) 
                                              OR tbl_dv_fg.id_user = '791'
                                            ) 
                                            OR tbl_dv_fg.id_user = '10654'
                                          ) 
                                          OR tbl_dv_fg.id_user = '10112'
                                        ) 
                                        OR tbl_dv_fg.id_user = '10246'
                                      ) 
                                      OR tbl_dv_fg.id_user = '792'
                                    ) 
                                    OR tbl_dv_fg.id_user = '11381'
                                  ) 
                                  OR tbl_dv_fg.id_user = '10152'
                                ) 
                                OR tbl_dv_fg.id_user = '10133'
                              ) 
                              OR tbl_dv_fg.id_user = '10983'
                            ) 
                            OR tbl_dv_fg.id_user = '10113'
                          ) 
                          OR tbl_dv_fg.id_user = '797'
                        ) 
                        OR tbl_dv_fg.id_user = '807'
                      ) 
                      OR tbl_dv_fg.id_user = '298'
                    ) 
                    OR tbl_dv_fg.id_user = '10245'
                  ) 
                  OR tbl_dv_fg.id_user = '11151'
                ) 
                OR tbl_dv_fg.id_user = '808'
              ) 
              OR tbl_dv_fg.id_user = '809'
            ) 
            OR tbl_dv_fg.id_user = '10763'
          ) 
          OR tbl_dv_fg.id_user = '810'
        ) 
        OR tbl_dv_fg.id_user = '11681'
      ) 
      OR tbl_dv_fg.id_user = '10502'
    ) 
    AND tbl_dv_fg.nb_action_rea != tbl_dv_fg.nb_action_total 
    AND dv_document_1.cond_valide = 1 
    AND dv_document_2.cond_valide = 1 
    AND dv_document_3.cond_valide = 1 
    AND dv_document_4.cond_valide = 1 
    AND (
      (
        (
          document_type_1.document_type_id = 129 
          AND (
            dv_document_1.document_dv_statut = 0 
            OR dv_document_1.document_dv_statut IS NULL
          )
        ) 
        OR (
          document_type_2.document_type_id = 53 
          AND (
            dv_document_2.document_dv_statut = 0 
            OR dv_document_2.document_dv_statut IS NULL
          )
        )
      ) 
      OR (
        (
          document_type_3.document_type_id = 40 
          AND (
            dv_document_3.document_dv_statut = 0 
            OR dv_document_3.document_dv_statut IS NULL
          )
        ) 
        OR (
          document_type_4.document_type_id = 138 
          AND (
            dv_document_4.document_dv_statut = 0 
            OR dv_document_4.document_dv_statut IS NULL
          )
        )
      )
    ) 
    AND tbl_dv_fg.deleted_at IS NULL

Explique a afirmação:

      id  select_type  table            type    possible_keys                                key              key_len  ref                                                          rows  Extra                               
------  -----------  ---------------  ------  -------------------------------------------  ---------------  -------  ---------------------------------------------------------  ------  ------------------------------------
     1  SIMPLE       tbl_dv_fg        range   PRIMARY,tbl_dv_fg_FI_1,affaire_id            tbl_dv_fg_FI_1   4        (NULL)                                                      10426  Using where; Using temporary        
     1  SIMPLE       tbl_fg_plural    eq_ref  numcmde                                      numcmde          178      manperf.tbl_dv_fg.num_cmde,manperf.tbl_dv_fg.affaire_id         1  Using index                         
     1  SIMPLE       tbl_fg           ref     commande,idx_NUMCMDE,idx_numcmde_user_rrf    commande         180      manperf.tbl_dv_fg.affaire_id,manperf.tbl_dv_fg.num_cmde         1                                      
     1  SIMPLE       tbl_fgp          eq_ref  PRIMARY,idx_fg_multi3                        PRIMARY          8        manperf.tbl_fg_plural.user_id,manperf.tbl_fg_plural.fg_id       1                                      
     1  SIMPLE       tbl_pilotage     ref     idx_num_cde_sed,idx_rrf,idx_num_cde_sed_rrf  idx_num_cde_sed  20       manperf.tbl_dv_fg.num_cmde                                      1                                      
     1  SIMPLE       tbl_user         eq_ref  PRIMARY                                      PRIMARY          4        manperf.tbl_dv_fg.id_user                                       1                                      
     1  SIMPLE       tbl_livraison    ref     vin                                          vin              57       manperf.tbl_pilotage.chassis                                    1                                      
     1  SIMPLE       dv_document_1    ref     Unique,idx_statut_dv                         Unique           4        manperf.tbl_dv_fg.id                                           75  Using where; Distinct               
     1  SIMPLE       document_type_1  eq_ref  PRIMARY                                      PRIMARY          4        manperf.dv_document_1.document_type_id                          1  Using index; Distinct               
     1  SIMPLE       dv_document_2    ref     Unique,idx_statut_dv                         Unique           4        manperf.dv_document_1.dv_id                                    75  Using where; Distinct               
     1  SIMPLE       document_type_2  eq_ref  PRIMARY                                      PRIMARY          4        manperf.dv_document_2.document_type_id                          1  Using index; Distinct               
     1  SIMPLE       dv_document_3    ref     Unique,idx_statut_dv                         Unique           4        manperf.dv_document_2.dv_id                                    75  Using where; Distinct               
     1  SIMPLE       document_type_3  eq_ref  PRIMARY                                      PRIMARY          4        manperf.dv_document_3.document_type_id                          1  Using index; Distinct               
     1  SIMPLE       dv_document_4    ref     Unique,idx_statut_dv                         Unique           4        manperf.tbl_dv_fg.id                                           75  Using where; Distinct               
     1  SIMPLE       document_type_4  eq_ref  PRIMARY                                      PRIMARY          4        manperf.dv_document_4.document_type_id                          1  Using where; Using index; Distinct
mysql optimization

2 respostas

  1. Dos comentários:

    DISTINCTé muito semelhante GROUP BY <all selected columns>e livrar-se da tabela temporária pode ser impossível ao unir muitas tabelas, pois o servidor precisa dela para verificar a exclusividade das linhas retornadas.

    Os índices de cobertura ( Using index) são bastante úteis quando você precisa obter o melhor desempenho.

    Eu prefiro IN()em vez de vários ORs na mesma coluna, pois é mais legível. Minha afirmação anterior sobre IN ter melhor desempenho parece estar errada.

    Você não precisa colocar os números entre aspas ao compará-los com uma coluna numérica, mas deve citá-los quando a coluna for (var)char para que os índices funcionem.

    O padrão innodb_buffer_pool_sizeé muito pequeno para a maioria dos aplicativos, forneça 60-80% da RAM disponível se você tiver mais dados nas tabelas InnoDB, um pouco menos quando você tiver algum MyISAM também e forneça um pouco para o key_buffer_size- mas apenas para índices myisam, ele armazena em cache dados no cache do sistema de arquivos (memória "livre" do seu sistema).

    • 3
  2. Best Answer

    Sua consulta tem muitos problemas que dificultam a otimização.

    Você usa LEFT JOINem várias tabelas onde INNER JOINdeveria ser suficiente (por exemplo, todos os casos de ged_tbl_document_dve ged_tbl_document_type).

    Você inclui tabelas que nunca serão realmente necessárias posteriormente (por exemplo, todos os casos de ged_tbl_document_type), portanto, remover todas elas não afetaria o resultado (substitua todas as instâncias de document_type_idde ged_tbl_document_typepara ged_tbl_document_dvna cláusula where).

    Você saiu da tabela ged_tbl_document_dvquatro vezes, quando uma deve ser suficiente com a junção interna:

    JOIN ged_tbl_document_dv doc ON (
  tbl_dv_fg.id = doc.dv_id AND 
  doc.cond_valide = 1 AND
  NULLIF(doc.document_dv_statut, 0) IS NULL AND
  doc.document_type_id IN (40, 53, 129, 138))

    Esta consulta realmente não requer DISTINCT, porque inclui uma chave primária. Se você tiver alguma linha duplicada, estará executando uma junção incorretamente e deverá usar uma subseleção na cláusula SELECT ou adotar uma estratégia diferente. As duplicatas indesejadas provavelmente vêm de tbl_pilotageou tbl_fg_plural.

    Quanto mais tabelas você juntar (especialmente se você usar qualquer OUTER JOIN), então mais complexos os possíveis planos de consulta se tornarão, e maior será a probabilidade de seu RDBMS escolher um que não funcione muito bem. Se você mantiver suas consultas o mais compactas e minimalistas possível, é mais provável que elas sejam executadas com mais eficiência.

    • 2

relate perguntas