具有多个联接的Mysql查询性能差

Question

我有一个查询，需要几分钟才能在一个小数据集中执行，问题是什么？以下是解释输出：

id  select_type table           type    possible_keys               key                 key_len   ref             rows  Extra

1   SIMPLE      BT_CALL_CLASS.. const   PRIMARY                     PRIMARY             4         const           1     Using index; Using temporary; Using filesort
1   SIMPLE      BT_FLAGS_FLAGS  const   PRIMARY                     PRIMARY             4         const           1     Using index
1   SIMPLE      BT_DIRECTORY... range   PRIMARY                     PRIMARY             4         NULL            308   Using where; Using index
1   SIMPLE      BT_CALL_FLAGS.. ref     PRIMARY,FKDF68A2ED9F150002  FKDF68A2ED9F150002  4         const           49    Using where; Using index
1   SIMPLE      BT_DEPARTMENT.. index   PRIMARY                     FK1F3A276188DDBD3   5         NULL            27    Using where; Using index; Using join buffer
1   SIMPLE      BT_USERS_USER.. index   PRIMARY                     FK6A68E086C27A155   5         NULL            233   Using where; Using index; Using join buffer
1   SIMPLE      BT_FCT_CALLS..  eq_ref  PRIMARY,FKDF68A2ED4F28..    PRIMARY             8         ...call_id      1     Using where

查询

 desc SELECT DISTINCT 
      BT_FCT_CALLS_FCT_CALLS.start_time AS COL0
     ,BT_FCT_CALLS_FCT_CALLS.calling_number AS COL1
     ,BT_FCT_CALLS_FCT_CALLS.called_number AS COL2
     ,BT_FCT_CALLS_FCT_CALLS.response AS COL3

 FROM departments BT_DEPARTMENTS_DEPARTMENTS_3 LEFT OUTER JOIN 
 ( 
  directory_numbers BT_DIRECTORY_NUMBERS_DIRECTORY_NUMBERS LEFT OUTER JOIN 
  ( 
   users BT_USERS_USERS_2 JOIN 
   ( 
    call_classification_dim BT_CALL_CLASSIFICATION_DIM_CALL_CLASSIFICATION_DIM JOIN 
    ( 
     flags BT_FLAGS_FLAGS JOIN 
     ( 
      fct_calls BT_FCT_CALLS_FCT_CALLS JOIN call_flags BT_CALL_FLAGS_CALL_FLAGS
      ON ( BT_FCT_CALLS_FCT_CALLS.id = BT_CALL_FLAGS_CALL_FLAGS.call_id )
     ) 
     ON ( BT_CALL_FLAGS_CALL_FLAGS.flag = BT_FLAGS_FLAGS.id AND (  BT_FLAGS_FLAGS.id  = 1 )  )
    ) 
    ON ( BT_FCT_CALLS_FCT_CALLS.call_direction_id = BT_CALL_CLASSIFICATION_DIM_CALL_CLASSIFICATION_DIM.id AND (  BT_CALL_CLASSIFICATION_DIM_CALL_CLASSIFICATION_DIM.id  = 2 )  )
   ) 
   ON ( (( BT_FCT_CALLS_FCT_CALLS.on_network_called_user_id  =  BT_USERS_USERS_2.id ) OR ( BT_FCT_CALLS_FCT_CALLS.on_network_calling_user_id  =  BT_USERS_USERS_2.id )) AND TRUE   )
  ) 
  ON ( (( BT_FCT_CALLS_FCT_CALLS.on_network_called_ext_id  =  BT_DIRECTORY_NUMBERS_DIRECTORY_NUMBERS.id ) OR ( BT_FCT_CALLS_FCT_CALLS.on_network_calling_ext_id  =  BT_DIRECTORY_NUMBERS_DIRECTORY_NUMBERS.id )) AND TRUE )
 ) 
 ON ( (( BT_FCT_CALLS_FCT_CALLS.on_network_called_department_id  =  BT_DEPARTMENTS_DEPARTMENTS_3.id ) OR ( BT_FCT_CALLS_FCT_CALLS.on_network_calling_department_id  =  BT_DEPARTMENTS_DEPARTMENTS_3.id )) AND TRUE )

  WHERE 
    (
      (
          NOT( BT_DEPARTMENTS_DEPARTMENTS_3.id  IN ( 1 ) )
      )
  AND (
          NOT( BT_DIRECTORY_NUMBERS_DIRECTORY_NUMBERS.id  IN ( 1 ) )
      )

  AND (
          NOT( BT_USERS_USERS_2.id  IN ( 1 ) )
      )
    )
ORDER BY 
      COL0

Answer 1

正如我在一条评论中所说的，查询是自动生成的，无论如何，在升级到mysql 5.6.14之后，我不能修改它们，而且如果对查询本身没有任何改变，性能就变得正常了(与其他具有类似复杂性的查询相比)！

Answer 2

首先将WHERE子句更改为：

 WHERE 
 BT_DEPARTMENTS_DEPARTMENTS_3.id  <> 1 AND 
 BT_DIRECTORY_NUMBERS_DIRECTORY_NUMBERS.id  <> 1 AND 
 BT_USERS_USERS_2.id <> 1

它比使用NOT IN(..)更快。

删除不需要的AND TRUE。

Answer 3

首先，让我们从非常糟糕的查询流程开始。通过简化与直接关系的关系，你的所有嵌套可以帮助清理一些事情。第二，在树下显示事物是相关的关系。你跳得到处都是。我还缩短了“别名”的可读性。最后，您不需要所有的表，因为您没有从所有的表中获得值，这使得许多事情变得无用。无论如何，这里是您的原始查询的清理版本。如您所见，表A链接到B的链接到C链接到D等的地方要容易得多。

SELECT DISTINCT 
      calls.start_time AS COL0,
      calls.calling_number AS COL1,
      calls.called_number AS COL2,
      calls.response AS COL3
   FROM 
      fct_calls calls 
         JOIN departments dpt3 
            ON calls.on_network_called_department_id = dpt3.id
            OR calls.on_network_calling_department_id = dpt3.id
         JOIN users u2 
            ON calls.on_network_called_user_id = u2.id
            OR calls.on_network_calling_user_id = u2.id
         JOIN directory_numbers dirNums 
            ON calls.on_network_called_ext_id = dirNums.id 
            OR calls.on_network_calling_ext_id = dirNums.id 
         JOIN call_classification_dim classDim 
            ON calls.call_direction_id = classDim.id 
            AND classDim.id = 2
         JOIN call_flags callFlags
            ON calls.id = callFlags.call_id
            JOIN flags f 
               ON callFlags.flag = f.id 
               AND f.id = 1
  WHERE
         dpt3.id <> 1
     AND dirNums.id <> 1
     AND  u2.id = 1
  ORDER BY 
     COL0

现在，这是我要做的事。同样，很多东西甚至没有被使用，比如调用标志和目录号。从所有具有您感兴趣的用户ID的呼叫开始，通过呼叫或呼叫位置。我会在calls表( on_network_called_user_id，on_network_calling_user_id )上有一个索引。我假设其他表在它们各自的主"ID“列上有索引。

SELECT DISTINCT 
      calls.start_time AS COL0,
      calls.calling_number AS COL1,
      calls.called_number AS COL2,
      calls.response AS COL3
   FROM 
      fct_calls calls 
         JOIN departments dpt3 
            ON calls.on_network_called_department_id = dpt3.id
            OR calls.on_network_calling_department_id = dpt3.id
         JOIN directory_numbers dirNums 
            ON calls.on_network_called_ext_id = dirNums.id 
            OR calls.on_network_calling_ext_id = dirNums.id 
  WHERE
     (    calls.on_network_called_user_id = 1
       OR calls.on_network_calling_user_id = 1 )
     AND dpt3.id <> 1
     AND dirNums.id <> 1
  ORDER BY 
     COL0

更进一步，您可以通过执行UNION来获得更好的性能，因为考虑到调用或被调用，您可以尝试以下操作。因此，您正在寻找任何调用，其中进行调用的人是用户ID 1，而调用的部门不是1，目录分机不是1.或者，调用用户ID 1，但忽略dept 1和扩展名1中的用户ID 1。

我将在CALL表上为这个调用对象设置两个索引，用于调用谁，以及该调用来自何处(on_network_called_user_id、on_network_calling_department_id、on_network_calling_ext_id )--相反.谁在打电话，他们打的是哪个部门/分机(on_network_calling_user_id，on_network_called_department_id，on_network_called_ext_id)

SELECT
      calls.start_time AS COL0,
      calls.calling_number AS COL1,
      calls.called_number AS COL2,
      calls.response AS COL3
   FROM 
      fct_calls calls 
         LEFT JOIN departments dpt3 
            OR calls.on_network_calling_department_id = dpt3.id
         LEFT JOIN directory_numbers dirNums 
            OR calls.on_network_calling_ext_id = dirNums.id 
   WHERE
          calls.on_network_called_user_id = 1
      AND calls.on_network_calling_department_id <> 1
      AND calls.on_network_calling_ext_id <> 1
   ORDER BY 
      COL0
UNION
SELECT
      calls.start_time AS COL0,
      calls.calling_number AS COL1,
      calls.called_number AS COL2,
      calls.response AS COL3
   FROM 
      fct_calls calls 
         LEFT JOIN departments dpt3 
            OR calls.on_network_called_department_id = dpt3.id
         LEFT JOIN directory_numbers dirNums 
            OR calls.on_network_called_ext_id = dirNums.id 
   WHERE
          calls.on_network_calling_user_id = 1
      AND calls.on_network_called_department_id <> 1
      AND calls.on_network_called_ext_id <> 1