主页 / dba / 问题 / 136476
Accepted
Yassine LD
Asked: 2016-04-26 07:23:15 +0800 CST

如何删除“使用临时”并加快查询速度

  • 772

嗨，我花了好几个小时来使用索引来加速这个查询，但它没用..我试图通过消除“使用临时”和只使用索引来优化。我想知道是否有另一种方法可以加快此查询的速度？？

询问 ：

 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

解释声明：

      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 个回答

  1. 来自评论：

    DISTINCT非常相似，GROUP BY <all selected columns>并且在连接许多表时可能无法摆脱临时表，因为服务器需要它来检查返回行的唯一性。

    当您需要获得最佳性能时，覆盖索引( Using index) 非常有用。

    我更喜欢IN()在同一列上而不是多个 OR，因为它更具可读性。我之前关于 IN 具有更好性能的说法似乎是错误的。

    将数字与数字列进行比较时不必引用数字，但当列为 (var)char 时必须引用它们才能使索引起作用。

    对于大多数应用程序来说，默认值innodb_buffer_pool_size太小了，如果你在 InnoDB 表中有大部分数据，给它 60-80% 的可用 RAM，当你也有一些 MyISAM 时，给它少一点，并给它一些key_buffer_size- 但只对 myisam 索引，它缓存文件系统缓存中的数据（系统的“空闲”内存）。

    • 3
  2. Best Answer

    您的查询有很多难以优化的问题。

    您LEFT JOIN在几个表中使用INNER JOIN应该足够的地方（例如ged_tbl_document_dv和的所有情况ged_tbl_document_type）。

    您包含了以后从未真正需要的表（例如 的所有情况ged_tbl_document_type），因此删除所有这些表不会影响结果（替换where 子句中document_type_idfromged_tbl_document_type到的所有实例）。ged_tbl_document_dv

    你左连接表ged_tbl_document_dv四次，当一次应该足够内连接时：

    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))

    此查询实际上并不需要DISTINCT，因为它包含一个主键。如果您有任何重复的行，那么您将错误地执行连接，您应该在 SELECT 子句中使用子选择，或者采用不同的策略。不需要的重复项可能来自tbl_pilotage或tbl_fg_plural。

    您连接的表越多（特别是如果您使用任何 OUTER JOIN），那么可能的查询计划就会变得越复杂，您的 RDBMS 就越有可能选择一个效果不佳的计划。如果您使查询尽可能紧凑和简约，查询将更有可能更高效地运行。

    • 2

相关问题