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更具可读性，特别是如果结果是子查询的话。
• AFAIK COUNT(*)将始终返回0，即使什么都没有找到。因此，不需要在其周围编写IFNULL()。
• COUNT(field)只计算该字段的非空记录，但如果没有发现，它将返回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不同的时候是真的吗？

​

• 我很惊讶这个查询实际上是编译的，就像您在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 ;