mysql - 多个表上的索引性能

Question

我在加快生产查询速度方面遇到了一些麻烦。

我想要执行的查询当前需要 12 秒才能显示结果集，并且它会导致资源受限的生产服务器崩溃。

重点是，我需要获取给定periode(YYYYMM 日期)最后一个的enregistrement 记录。获取这些记录后，我想将 I.sum_field 中给出的字段之一作为 total 字段求和。

当我评论 CASE 部分时，查询大约需要 5 秒(+/- 500 毫秒)。

这是查询:

SELECT 
      I.libelle, 
      E1.periode, 
      E1.created_at, 
      CASE WHEN I.sum_field = 'fat' THEN SUM(E1.Fat)
           WHEN I.sum_field = 'etp' THEN SUM(E1.Etp)
           WHEN I.sum_field = 'nb_ident' THEN COUNT(*)
           WHEN I.sum_field = 'cdi_actif' THEN SUM(E1.cdi_actif)
      END AS total
   FROM 
      indicateur_motif IM
         INNER JOIN indicateur I 
            ON IM.indicateur_id = I.id
         INNER JOIN `position` P 
            ON IM.motif_id = P.id
         INNER JOIN enregistrement E1 
            ON P.id = E1.position_id
            INNER JOIN 
               ( SELECT 
                       MAX(id) AS id, 
                       MAX(created_at) AS created_at
                    FROM 
                       enregistrement
                    WHERE 
                           (etat_mouvement_id IN (1,3,4))
                       AND (periode >= '201410' AND periode <= '201512')
                       AND created_at <= DATE_FORMAT('2015-02-03', '%Y-%m-%d %H:%i:%s')
                    GROUP BY 
                       salarie_id, 
                       periode ) E2 
               ON E1.id = E2.id 
               AND E1.created_at = E2.created_at
   WHERE 
      I.formule_id = 1
   GROUP BY 
      I.id, 
      E1.periode
   ORDER BY 
      I.position, 
      E1.periode

这是解释结果:

id  select_type  table           type    possible_keys                                   key                                             key_len  ref                   rows  Extra                                               
------  -----------  --------------  ------  ----------------------------------------------  ----------------------------------------------  -------  ------------------  ------  ----------------------------------------------------
 1  PRIMARY      I               ALL     PRIMARY                                         (NULL)                                          (NULL)   (NULL)                  21  Using where; Using temporary; Using filesort        
 1  PRIMARY      IM              ref     indicateur_motif_indicateur_id_motif_id_unique  indicateur_motif_indicateur_id_motif_id_unique  4        orhase.I.id              2  Using index                                         
 1  PRIMARY      P               eq_ref  PRIMARY                                         PRIMARY                                         4        orhase.IM.motif_id       1  Using index                                         
 1  PRIMARY      <derived2>      ALL     (NULL)                                          (NULL)                                          (NULL)   (NULL)              165352  Using where; Using join buffer (Block Nested Loop)  
 1  PRIMARY      e1              eq_ref  PRIMARY                                         PRIMARY                                         4        e2.id                    1  Using where                                         
 2  DERIVED      enregistrement  index   sp                                              sp                                              771      (NULL)              165352  Using where                                         

这是结果集的示例:

libelle                                     periode           created_at  total    
------------------------------------------  -------  -------------------  ---------
CDI actifs fin de période                   201410   2014-10-01 00:00:00  4689     
CDI actifs fin de période                   201411   2015-01-29 08:12:03  4674     
CDI actifs fin de période                   201412   2015-01-29 08:12:03  4660     
CDI actifs fin de période                   201501   2015-01-29 08:12:04  4444     
CDI actifs fin de période                   201502   2015-01-29 08:12:04  4222     
CDI actifs fin de période                   201503   2015-01-29 08:12:04  4195     
CDI actifs fin de période                   201504   2015-01-29 08:12:04  4176     
CDI actifs fin de période                   201505   2015-01-29 08:12:04  4155     
CDI actifs fin de période                   201506   2015-01-29 08:12:04  4136     
CDI actifs fin de période                   201507   2015-01-29 08:12:04  4121     
CDI actifs fin de période                   201508   2015-01-29 08:12:04  4080     
CDI actifs fin de période                   201509   2015-01-29 08:12:04  4061     
CDI actifs fin de période                   201510   2015-01-29 08:12:04  4036     
CDI actifs fin de période                   201511   2015-01-29 08:12:04  4001     
CDI actifs fin de période                   201512   2015-01-29 08:12:04  3976     
ETP fin de période CDI stock                201410   2014-10-01 00:00:00  4259.16  
ETP fin de période CDI stock                201411   2015-01-29 08:12:03  4241.91  
ETP fin de période CDI stock                201412   2015-01-29 08:12:03  4222.12  
ETP fin de période CDI stock                201501   2015-01-29 08:12:04  4028.07  

我只是不知道在哪里放置一个新索引来避免这个执行时间...我已经在enregistrement上放置了一个，称为sp:

ALTER TABLE enregistrement ADD INDEX sp(salarie_id, periode);


这个让我的执行时间从 16 秒缩短到 12 秒。有什么想法吗？

谢谢。

Answer 1

不知道这是否有帮助，但是您的案例在做什么...您正在对完全不同的字段进行求和，并将另一个字段计入“总计”。我怀疑您可能实际上希望将它们作为自己的专栏。

但是，话虽如此，您有什么索引...您的解释显示了一些，但如果它们不可用，我会尝试包含以下内容...

table             index 
indicateur        ( formule_id, id, position )
indicateur_motif  ( indicateur_id, motif_id )
`position`        ( id )
enregistrement    ( position_id, id, created_at )  <-- for the JOIN portion
enregistrement    ( etat_mouvement_id, periode, created_at, salarie_id, id )  <-- for sub-select query

此外，从您的连接来看，您并没有真正使用“Position”表中的任何内容。是的，你从主题连接到位置，从位置连接到 enreg，但是因为

IM.motif_id = P.id  and  P.id = E1.position_id

然后就可以直接跳转了

IM.motif_id = E1.position_id

并从查询中删除“位置”表。这是对您开始的内容稍作修改的查询。我删除了位置引用，还更改了内部查询的“分组依据”，以便与 periode 和 salarie_id 列的可用索引相匹配时获得更好的性能。

SELECT 
      I.libelle, 
      E1.periode, 
      E1.created_at, 
      CASE WHEN I.sum_field = 'fat' THEN SUM(E1.Fat)
           WHEN I.sum_field = 'etp' THEN SUM(E1.Etp)
           WHEN I.sum_field = 'nb_ident' THEN COUNT(*)
           WHEN I.sum_field = 'cdi_actif' THEN SUM(E1.cdi_actif)
      END AS total
   FROM 
      indicateur I 
         JOIN indicateur_motif IM
            ON I.id = IM.indicateur_id
            INNER JOIN enregistrement E1 
               ON IM.motif_id = E1.position_id
               INNER JOIN 
                  ( SELECT 
                          MAX(id) AS id, 
                          MAX(created_at) AS created_at
                       FROM 
                          enregistrement
                       WHERE 
                              etat_mouvement_id IN (1,3,4)
                          AND periode >= '201410' 
                          AND periode <= '201512'
                          AND created_at <= '2015-02-03'
                       GROUP BY 
                          periode,
                          salarie_id ) E2 
                  ON E1.id = E2.id 
                  AND E1.created_at = E2.created_at
   WHERE 
      I.formule_id = 1
   GROUP BY 
      I.id, 
      E1.periode
   ORDER BY 
      I.position, 
      E1.periode