MySQL - 优化查询

Question

我留下的报告以非常慢的速度返回行。我觉得我需要重做，而不需要那么多或任何子查询。但我完全不知道如何尝试这个。

我查看了索引，发现键不够唯一，这会强制执行全表扫描时间。有什么方法可以使用单独的查询从其他表中提取某些信息，将其添加为变量并在主查询中使用它。事实上，这个查询的结果只有几行。

是否有任何提示或技巧，我可以用来优化或纠正此 SQL 语句以加快速度。

(编辑)我为表添加了一些创建代码。

SELECT
     case when (select count(ag.`PKEY`) - count(ag.`ANSWERTIME`)  from acdcallinformation ag
     where (ag.`COMPLETED`) = 1 and answertime is null and time(ag.INSTIME) and DATE_FORMAT(DATEOFCALL,'%Y-%m-%d') >= date(now()) and ag.skillid = acdcallinformation.skillid) is null
        then 0
            else
        (select count(ag.`PKEY`) - count(ag.`ANSWERTIME`)  from acdcallinformation ag where (ag.`COMPLETED`) = 1
        and answertime is null and DATE_FORMAT(DATEOFCALL,'%Y-%m-%d') >= date(now()) and ag.skillid = acdcallinformation.skillid)
     end as LostCalls,
     case when count(acdcallinformation.idleonqueue) is null then 0 else count(acdcallinformation.idleonqueue) end as CountCallsACD,
     case when count(acdcallinformation.`ANSWERTIME`) is null then 0 else count(acdcallinformation.`ANSWERTIME`) end AS acdcallinformation_ANSWERED,
     (select skillinfo.skillname from skillinfo where skillinfo.pkey = acdcallinformation.skillid) AS acdcallinformation_SKILLIDTEXT,

     (select count(pkey) from acdcallinformation age
       where DATE_FORMAT(DATEOFCALL,'%Y-%m-%d') >= date(now()) and age.skillid = acdcallinformation.skillid and   (age.`COMPLETED`) = 0 and answertime is null
        and SKILLID in (select SKILLID
                                from
                            callcenterinformation
                            where time > (now() - INTERVAL 5 SECOND) and callswaiting > 0)) as Waiting,

     -- count(acdcallinformation.`PKEY`) as CallsWaiting,
     acdcallinformation.`DATEOFCALL` AS acdcallinformation_DATEOFCALL,
     acdcallinformation.`FIRSTRINGONQUEUE` AS acdcallinformation_FIRSTRINGONQUEUE,
     case when acdcallinformation.`CONNECTTIME` is null then time('00:00:00') else acdcallinformation.`CONNECTTIME` end AS acdcallinformation_CONNECTTIME,
     acdcallinformation.`CALLSTATEBEFOREIDLE` AS acdcallinformation_CALLSTATEBEFOREIDLE,
     case when acdcallinformation.`AGENTRINGTIME` is null then time('00:00:00') else acdcallinformation.`AGENTRINGTIME` end AS acdcallinformation_AGENTRINGTIME,
     acdcallinformation.`IDLEONQUEUE` AS acdcallinformation_IDLEONQUEUE,
     acdcallinformation.`DDI` AS acdcallinformation_DDI,
     acdcallinformation.`CLIP` AS acdcallinformation_CLIP,
     acdcallinformation.`SKILLID` AS acdcallinformation_SKILLID,
     acdcallinformation.`ACTIONTYPE` AS acdcallinformation_ACTIONTYPE,
     acdcallinformation.`ACTIONDESTINATION` AS acdcallinformation_ACTIONDESTINATION,
     acdcallinformation.`COMPLETED` AS acdcallinformation_COMPLETED,
     acdcallinformation.`HANDLED` AS acdcallinformation_HANDLED,
     acdcallinformation.`CONFIRMED` AS acdcallinformation_CONFIRMED,
    (
        SELECT
         cal.`AGENTSREADY` AS callcenterinformation_AGENTSREADY
    FROM
         `callcenterinformation` cal
    WHERE cal.skillid <> 1 and acdcallinformation.skillid = skillid order by pkey desc limit 1,1) as agentsready
FROM
     `acdcallinformation` acdcallinformation
where DATE_FORMAT(DATEOFCALL,'%Y-%m-%d') >= date(now()- interval 1 day )
group by (select skillinfo.skillname from skillinfo where skillinfo.pkey = acdcallinformation.skillid);


CREATE TABLE `callcenterinformation` (
    `INSTIME` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `PKEY` INT(11) NOT NULL,
    `SKILLID` INT(11) NULL DEFAULT '0',
    `DATE` DATE NULL DEFAULT NULL,
    `TIME` TIME NULL DEFAULT NULL,
    `AGENTSLOGGEDIN` INT(11) NULL DEFAULT '0',
    `AGENTSREADY` INT(11) NULL DEFAULT '0',
    `AGENTSRINGING` INT(11) NULL DEFAULT '0',
    `AGENTSCONNECTED` INT(11) NULL DEFAULT '0',
    `AGENTSINPAUSE` INT(11) NULL DEFAULT '0',
    `AGENTSINWRAPUP` INT(11) NULL DEFAULT '0',
    `CALLSWAITING` INT(11) NULL DEFAULT '0',
    `COMPLETED` TINYINT(1) NULL DEFAULT '0',
    `HANDLED` TINYINT(1) NULL DEFAULT '0',
    `CONFIRMED` TINYINT(1) NULL DEFAULT '0',
    PRIMARY KEY (`PKEY`),
    INDEX `DATE` (`DATE`),
    INDEX `TIME` (`TIME`),
    INDEX `SKILLID` (`SKILLID`)
)
COLLATE='latin1_swedish_ci'
ENGINE=InnoDB;


CREATE TABLE `acdcallinformation` (
    `INSTIME` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `PKEY` INT(11) NOT NULL,
    `DATEOFCALL` DATE NULL DEFAULT NULL,
    `FIRSTRINGONQUEUE` TIME NULL DEFAULT NULL,
    `CONNECTTIME` TIME NULL DEFAULT NULL,
    `CALLSTATEBEFOREIDLE` INT(11) NULL DEFAULT '0',
    `AGENTRINGTIME` TIME NULL DEFAULT NULL,
    `ANSWERTIME` TIME NULL DEFAULT NULL,
    `IDLEONQUEUE` TIME NULL DEFAULT NULL,
    `DDI` TEXT NULL,
    `CLIP` TEXT NULL,
    `SKILLID` INT(11) NULL DEFAULT '0',
    `ACTIONTYPE` INT(11) NULL DEFAULT '0',
    `ACTIONDESTINATION` TEXT NULL,
    `COMPLETED` TINYINT(1) NULL DEFAULT '0',
    `HANDLED` TINYINT(1) NULL DEFAULT '0',
    `CONFIRMED` TINYINT(1) NULL DEFAULT '0',
    PRIMARY KEY (`PKEY`),
    INDEX `DATEOFCALL` (`DATEOFCALL`),
    INDEX `IDLEONQUEUE_HANDLED` (`IDLEONQUEUE`, `HANDLED`),
    INDEX `SKILLID` (`SKILLID`)
)
COLLATE='latin1_swedish_ci'
ENGINE=InnoDB;


CREATE TABLE `skillinfo` (
    `INSTIME` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `PKEY` INT(11) NOT NULL,
    `SKILLNAME` TEXT NULL,
    `CLIP` TEXT NULL,
    `WRAPUPTIMELENGTH` INT(11) NULL DEFAULT '0',
    `MAXRINGTIMELENGTH` INT(11) NULL DEFAULT '0',
    `FORCEDTICKET` TINYINT(1) NULL DEFAULT '0',
    `STATEAFTERWRAPUP` INT(11) NULL DEFAULT '0',
    `STATEAFTERUNANSWEREDCALL` INT(11) NULL DEFAULT '0',
    `ACTIONTYPE` INT(11) NULL DEFAULT '0',
    `ACTIONDESTINATION` TEXT NULL,
    `DEFLECTAFTERCOURTESY` TINYINT(1) NULL DEFAULT '0',
    `MAXOVERALLRINGTIMELENGTH` INT(11) NULL DEFAULT '0',
    `AUTOCLIP` TINYINT(1) NULL DEFAULT '0',
    `OUTGOINGSETTINGSACTIVE` TINYINT(1) NULL DEFAULT '0',
    `NUMPLANIDENTIFIER` INT(11) NULL DEFAULT '0',
    `TYPEOFNUMBER` INT(11) NULL DEFAULT '0',
    `CLIR` INT(11) NULL DEFAULT '0',
    `OUTGOINGROUTEID` INT(11) NULL DEFAULT '0',
    `USELASTAGENT` TINYINT(1) NULL DEFAULT '0',
    `CLIPROUTINGACTIVE` TINYINT(1) NULL DEFAULT '0',
    `USETHRESHOLD` TINYINT(1) NULL DEFAULT '0',
    `NORMALLOADTHRESHOLD` INT(11) NULL DEFAULT '0',
    `OVERLOADTHRESHOLD` INT(11) NULL DEFAULT '0',
    `STATEAFTERFORWARD` INT(11) NULL DEFAULT '0',
    `CALLDISTTYPE` INT(11) NULL DEFAULT '0',
    `USERGROUPID` INT(11) NULL DEFAULT '0',
    `EXTERNALCONTROL` TINYINT(1) NULL DEFAULT '0',
    `LASTAGENTLIMIT` INT(11) NULL DEFAULT '0',
    PRIMARY KEY (`PKEY`)
)
COLLATE='latin1_swedish_ci'
ENGINE=InnoDB;

Answer 1

说实话，这个查询有太多“错误”，它不再有趣了 =/

一些想法:

• IFNULL()比 CASE WHEN <field> IS NULL THEN constant ELSE <field> END 更具可读性，特别是如果结果是子查询。
• 据我所知COUNT(*)即使什么也没找到，也总是返回 0。因此，无需在其周围编写 IFNULL()
• COUNT(field)只计算该字段的非 NULL 记录，但同样，如果没有找到任何内容，它将返回 0，因此不需要 IFNULL() 围绕它
• 您应该自学如何 JOIN表，因为它比到处使用相关子查询更好。
• 我对 mysql 不太了解，但在我看来，你通过在字段周围放置强制转换和函数来破坏你的性能，否则这些字段似乎有一个有用的索引。我非常确定，由于这些结构，引擎根本无法使用所述索引，从而导致性能下降。例如。我会尝试重写
  • AND DATE_FORMAT(DATEOFCALL,'%Y-%m-%d') >= date(now())变成像AND DATEOFCALL >= CUR_DATE()这样的东西，毕竟两边都是日期(=数字)
  • DATE_FORMAT(DATEOFCALL,'%Y-%m-%d') >= date(now()- interval 1 day)进入DATEOFCALL >= date(now()- interval 1 day)出于同样的原因
  • 我也不知道什么 time(ag.INSTIME)应该做 ？!？!每当时间不同于 00:00:00 时都是这样吗？
• 我非常惊讶这个查询实际上完全像你看起来的那样编译GROUP BY就在 skillname ，而且还从表中获取相当多的其他字段(例如 idleonqueue )。从 MSSQL 背景来看，这不应该工作。我猜 mysql 是不同的，尽管我确实想知道结果会是什么样。

无论如何，尝试将上述一些内容应用于您的查询，我最终得到以下结果。我怀疑它会“快得多”；它可能只是一点点，但我认为这是你进一步清理它的使命中向前迈出的一步......

祝你好运!

SELECT (SELECT COUNT(ag.`PKEY`) - COUNT(ag.`ANSWERTIME`)  
          FROM acdcallinformation ag
         WHERE ag.`COMPLETED` = 1 
           AND answertime is null                     
           AND time(ag.INSTIME) 
           AND ag.DATEOFCALL >= CURDATE()             
           AND ag.skillid = info.skillid) AS LostCalls,
       COUNT(info.idleonqueue) AS CountCallsACD,
       COUNT(info.`ANSWERTIME`) AS acdcallinformation_ANSWERED,
       skillinfo.skillname AS acdcallinformation_SKILLIDTEXT,
       (SELECT COUNT(pkey) 
         FROM acdcallinformation age
        WHERE age.DATEOFCALL >= CURDATE() 
          AND age.skillid = info.skillid 
          AND age.`COMPLETED` = 0 
          AND age.answertime is null
          AND age.SKILLID IN (SELECT SKILLID
                                FROM callcenterinformation cci
                               WHERE cci.time > (now() - INTERVAL 5 SECOND) 
                                 AND cci.callswaiting > 0)) AS Waiting,
       -- count(info.`PKEY`) AS CallsWaiting,
       info.`DATEOFCALL` AS acdcallinformation_DATEOFCALL,
       info.`FIRSTRINGONQUEUE` AS acdcallinformation_FIRSTRINGONQUEUE,
       IFNULL(info.`CONNECTTIME`, time('00:00:00')) AS acdcallinformation_CONNECTTIME,
       info.`CALLSTATEBEFOREIDLE` AS acdcallinformation_CALLSTATEBEFOREIDLE,
       IFNULL(info.`AGENTRINGTIME`, time('00:00:00')) AS acdcallinformation_AGENTRINGTIME,
       info.`IDLEONQUEUE` AS acdcallinformation_IDLEONQUEUE,
       info.`DDI` AS acdcallinformation_DDI,
       info.`CLIP` AS acdcallinformation_CLIP,
       info.`SKILLID` AS acdcallinformation_SKILLID,
       info.`ACTIONTYPE` AS acdcallinformation_ACTIONTYPE,
       info.`ACTIONDESTINATION` AS acdcallinformation_ACTIONDESTINATION,
       info.`COMPLETED` AS acdcallinformation_COMPLETED,
       info.`HANDLED` AS acdcallinformation_HANDLED,
       info.`CONFIRMED` AS acdcallinformation_CONFIRMED,
       (SELECT cal.`AGENTSREADY` AS callcenterinformation_AGENTSREADY
          FROM `callcenterinformation` cal
         WHERE cal.skillid <> 1 
           AND cal.skillid = info.skillid 
         ORDER BY pkey DESC LIMIT 1,1) AS agentsready
  FROM `acdcallinformation` info
  JOIN `skillinfo`
    ON skillinfo.pkey = info.skillid
 WHERE info.DATEOFCALL >= (date(now()- interval 1 day ))
 GROUP BY skillinfo.skillname ;