php - MySQL 连接查询优化

Question

我有两个表，我需要对其执行左外连接以获得我想要的结果集。我现在的查询可以工作，但处理时间太长。有什么建议吗？

查询:

SELECT Date_format(a.call_date, '%Y-%m-%d') Call_Date,
   Date_format(a.call_date, '%H:%i:%s') Call_Time,
   a.lead_id,
   customer_number,
   status,
   a.call_type,
   agent,
   skill,
   campaign,
   disposition,
   hangup,
   a.uniqueid,
   time_to_answer,
   talk_time,
   hold_sec,
   wrapup_sec,
   Date_format(start_time, '%H:%i:%s')  Start_Time,
   Date_format(end_time, '%H:%i:%s')    End_Time,
   Ifnull(a.transfered, b.transfered)   AS transfer,
   comments,
   location,
   duration,
   handling_time,
   number_dialed                        AS DID
FROM   cdr_temp a
   LEFT OUTER JOIN (SELECT USER,
                           Substring(number_dialed, 18, 11) AS transfered,
                           uniqueid
                    FROM   transfertable)
                   b
                ON a.uniqueid = b.uniqueid
WHERE  a.call_date BETWEEN '2019-01-01 00:00:00' AND '2019-03-23 00:00:00'
GROUP  BY a.lead_id,
      b.uniqueid

表:cdr_temp

可转让

索引:

uniqueid and call_date for transfertable
uniqueid and lead_id for cdr_temp

解释查询

id  select_type     table   type    possible_keys   key     key_len     ref     rows    Extra   
1   SIMPLE  a   ALL     NULL    NULL    NULL    NULL    1333    Using where; Using temporary; Using filesort
1   SIMPLE  transfertable   ref     uniqueid    uniqueid    22  test.a.uniqueid     1   

PS:我需要在 GROUP BY 上连接技能和 number_dialed。我尝试过使用 GROUP_CONCAT 但它不起作用，我也不知道为什么。

Answer 1

我真的不明白transfertable上的子查询的意义。但是，仔细观察该子查询，我可以看到您正在那里调用 SUBSTRING 。这几乎肯定意味着 MySQL 将无法使用任何索引来改进该连接。这里的一种方法是创建一个临时表，其中包含 number_dialed 的子字符串作为真正的列。

CREATE TEMPORARY TABLE transfertable_temp (
    INDEX idx (uniqueid, transfered)
)
SELECT uniqueid, SUBSTRING(number_dialed, 18, 11) AS transfered
FROM transfertable;

如果 MySQL 选择使用该临时表(代替 MySQL 尚不支持的物化 View )，则连接到该临时表应该会提高连接的性能。请注意，我还在索引中包含子字符串编号，以覆盖正在使用的其他列。

这是您更新后的查询:

SELECT
    DATE_FORMAT(a.call_date, '%Y-%m-%d') Call_Date,
    DATE_FORMAT(a.call_date, '%H:%i:%s') Call_Time,
    a.lead_id,
    customer_number,
    status,
    a.call_type,
    agent,
    skill,
    campaign,
    disposition,
    hangup,
    a.uniqueid,
    time_to_answer,
    talk_time,
    hold_sec,
    wrapup_sec,
    Date_format(start_time, '%H:%i:%s') Start_Time,
    Date_format(end_time, '%H:%i:%s') End_Time,
    IFNULL(a.transfered, b.transfered) transfer,
    comments,
    location,
    duration,
    handling_time,
    b.number_dialed DID
 FROM cdr_temp a
 LEFT JOIN transfertable_temp b
    ON a.uniqueid = b.uniqueid
WHERE
    a.call_date BETWEEN '2019-01-01 00:00:00' AND '2019-03-23 00:00:00';

注意:在当前查询中使用 GROUP BY 没有任何意义，特别是因为您甚至没有调用任何聚合函数。如果您认为需要聚合，请编辑您的问题并告诉我们原因。