mysql - 优化 MySQL 查询以避免 "Using temporary"和 "Using filesort"

Question

我知道那里有上千个类似的问题，但没有一个像我这样处理复杂的查询(而且我的 MySQL 技能还没有达到真正理解如何调整它们的程度。)

这里是:

explain select
  `ev`.`EventID` AS `EventID`
  ,`ev`.`EventName` AS `EventName`
  ,concat(`ev`.`EventDate`,' ',`ev`.`StartTime`) AS `EventDT`
  ,`ev`.`NumberTicketsAvailable` AS `TotalTickets`
  ,`ev`.`Soldout` AS `Soldout`
  ,count((case when (`ec`.`CartStatus` = 'InCart') then 1 else NULL end)) AS `InCartCount`
  ,count((case when (`ec`.`CartStatus` = 'InPayment') then 1 else NULL end)) AS `InPaymentCount`
  ,count((case when (`ec`.`CartStatus` = 'Paid') then 1 else NULL end)) AS `PaidCount`
  ,count((case when ((`ec`.`CartStatus` = 'Paid') and ((`ec`.`DateRecordModified` + interval 604800 second) > now())) then 1 else NULL end)) AS `PaidOverWeek`
  ,count((case when ((`ec`.`CartStatus` = 'Paid') and ((`ec`.`DateRecordModified` + interval 432000 second) > now())) then 1 else NULL end)) AS `PaidOverFiveDays`
  ,count((case when ((`ec`.`CartStatus` = 'Paid') and ((`ec`.`DateRecordModified` + interval 86400 second) > now())) then 1 else NULL end)) AS `PaidOverDay`
  ,count((case when ((`ec`.`CartStatus` = 'Paid') and ((`ec`.`DateRecordModified` + interval 43200 second) > now())) then 1 else NULL end)) AS `PaidOverHalfDay`
  ,count((case when ((`ec`.`CartStatus` = 'Paid') and ((`ec`.`DateRecordModified` + interval 21600 second) > now())) then 1 else NULL end)) AS `PaidOverQuarterDay`
  ,count((case when ((`ec`.`CartStatus` = 'Paid') and ((`ec`.`DateRecordModified` + interval 10800 second) > now())) then 1 else NULL end)) AS `PaidOverThreeHours`
  ,count((case when ((`ec`.`CartStatus` = 'Paid') and ((`ec`.`DateRecordModified` + interval 3600 second) > now())) then 1 else NULL end)) AS `PaidOverHour`
from (`Events` `ev`
  left join (`Events_EventCart_Rel` `eecr`
    left join `EventCart` `ec`
      on((`eecr`.`EventCartID` = `ec`.`EventCartID`)))
    on((`ev`.`EventID` = `eecr`.`EventID`)))
where (`eecr`.`Active` = 1 AND `eecr`.`Deleted` = 'No')
group by
  `ev`.`EventID`
  ,`ev`.`EventName`
  ,`ev`.`EventDate`
  ,`ev`.`StartTime`
  ,`ev`.`NumberTicketsAvailable`
  ,`ev`.`Soldout`;

结果如下:

+-id-+-select_type-+-table-+--type--+--------possible_keys--------+----key----+-key_len-+----------ref----------+--rows--+---------------------------Extra---------------------------+
|   1| SIMPLE      | eecr  | index  | EventID,EventID_2,EventID_3 | EventID_3 | 10      | {null}                | 17609  | Using where; Using index; Using temporary; Using filesort |
|   1| SIMPLE      | ev    | eq_ref | PRIMARY                     | PRIMARY   | 4       | eecr.EventID          | 1      | Using where                                               |
|   1| SIMPLE      | ec    | eq_ref | PRIMARY                     | PRIMARY   | 4       | eecr.EventCartID      | 1      |                                                           |
+----+-------------+-------+--------+-----------------------------+-----------+---------+-----------------------+--------+-----------------------------------------------------------+

和表定义:

CREATE TABLE IF NOT EXISTS `Events` (
  `EventID` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `EventName` varchar(150) NOT NULL,
  `StartTime` char(8) NOT NULL DEFAULT '00:00:00',
  `EndTime` char(8) NOT NULL DEFAULT '00:00:00',
  `EventDate` varchar(20) NOT NULL,
  `NumberTicketsAvailable` smallint(6) DEFAULT NULL,
  `Soldout` enum('yes','no') DEFAULT 'no',
  #...
  PRIMARY KEY (`EventID`),
  KEY `EndTime` (`EndTime`,`EventDate`),
  KEY `StartTime` (`StartTime`,`EventDate`),
  KEY `EventDate` (`EventDate`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1

CREATE TABLE IF NOT EXISTS `Events_EventCart_Rel` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `EventCartID` int(11) NOT NULL,
  `EventID` int(11) NOT NULL,
  `DateAdded` datetime NOT NULL,
  `PersonID` int(11) NOT NULL,
  `SeatTypeID` int(11) NOT NULL,
  `MealChoiceID` int(11) NOT NULL,
  `Active` tinyint(1) NOT NULL DEFAULT '1',
  `Deleted` enum('Yes','No') NOT NULL DEFAULT 'No',
  `ModifiedByAdmin` enum('Yes','No') NOT NULL DEFAULT 'No',
  PRIMARY KEY (`ID`),
  KEY `EventID` (`EventID`,`PersonID`),
  KEY `EventCartID` (`EventCartID`),
  KEY `EventID_2` (`EventID`),
  KEY `EventID_3` (`EventID`,`EventCartID`,`Active`,`Deleted`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1

CREATE TABLE IF NOT EXISTS `EventCart` (
  `EventCartID` int(11) NOT NULL AUTO_INCREMENT,
  `RegistrantsID` int(11) NOT NULL DEFAULT '0',
  `DateRecordCreated` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `DateRecordModified` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
  `CartStatus` enum('InCart','InPayment','Paid') NOT NULL DEFAULT 'InCart',
  `ModifiedByAdmin` enum('yes','no') NOT NULL DEFAULT 'no',
  PRIMARY KEY (`EventCartID`),
  KEY `rid` (`RegistrantsID`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1

为了避免疑惑，请查看其中的一些专栏 - 是的，其中有很多我没有时间在代码中修复的遗留类型的东西。

Answer 1

我发现至少在 MySQL 中，几乎所有使用 GROUP BY 的查询都会调用一个临时表。这就是您的巨大性能成本所在。尝试使用探查器检查它把时间花在哪里:

编辑:我将以下内容更正为SET PROFILING(不是SET PROFILES):

SET PROFILING = On;
SELECT ...the whole query you want to profile...
SHOW PROFILES;
SHOW PROFILE FOR QUERY 1;

参见 http://dev.mysql.com/doc/refman/5.1/en/show-profiles.html了解更多详情。

要解决此问题，您无能为力。出于性能原因，有时最好消除 GROUP BY 和聚合函数:

select
  `ev`.`EventID` AS `EventID`
  ,`ev`.`EventName` AS `EventName`
  ,concat(`ev`.`EventDate`,' ',`ev`.`StartTime`) AS `EventDT`
  ,`ev`.`NumberTicketsAvailable` AS `TotalTickets`
  ,`ev`.`Soldout` AS `Soldout`
  ,case when (`ec`.`CartStatus` = 'InCart') then 1 else 0 end AS `InCartCounter`
  ,case when (`ec`.`CartStatus` = 'InPayment') then 1 else 0 end AS `InPaymentCounter`
  ,case when (`ec`.`CartStatus` = 'Paid') then 1 else 0 end AS `PaidCounter`
  ,case when ((`ec`.`CartStatus` = 'Paid') and ((`ec`.`DateRecordModified` + interval 604800 second) > now())) then 1 else 0 end AS `PaidOverWeekCounter`
  ,case when ((`ec`.`CartStatus` = 'Paid') and ((`ec`.`DateRecordModified` + interval 432000 second) > now())) then 1 else 0 end AS `PaidOverFiveDaysCounter`
  ,case when ((`ec`.`CartStatus` = 'Paid') and ((`ec`.`DateRecordModified` + interval 86400 second) > now())) then 1 else 0 end AS `PaidOverDayCounter`
  ,case when ((`ec`.`CartStatus` = 'Paid') and ((`ec`.`DateRecordModified` + interval 43200 second) > now())) then 1 else 0 end AS `PaidOverHalfDayCounter`
  ,case when ((`ec`.`CartStatus` = 'Paid') and ((`ec`.`DateRecordModified` + interval 21600 second) > now())) then 1 else 0 end AS `PaidOverQuarterDayCounter`
  ,case when ((`ec`.`CartStatus` = 'Paid') and ((`ec`.`DateRecordModified` + interval 10800 second) > now())) then 1 else 0 end AS `PaidOverThreeHoursCounter`
  ,case when ((`ec`.`CartStatus` = 'Paid') and ((`ec`.`DateRecordModified` + interval 3600 second) > now())) then 1 else 0 end AS `PaidOverHourCounter`
from `Events` `ev`
inner join `Events_EventCart_Rel` `eecr`
  on `ev`.`EventID` = `eecr`.`EventID` 
inner join `EventCart` `ec`
   on `eecr`.`EventCartID` = `ec`.`EventCartID`
where `eecr`.`Active` = 1 and `eecr`.`Deleted` = 'No'

然后在您的应用程序代码中，获取所有 行，并遍历它们，边计算边计算总计数。例如在 PHP 中:

$stmt = $pdo->query($sql);
$events = array();
$counters = array("InCartCounter", "InPaymentCounter", "PaidCounter",
  "PaidOverWeekCounter", "PaidOverFiveDaysCounter", "PaidOverDayCounter",
  "PaidOverHalfDayCounter", "PaidOverQuarterDayCounter", 
  "PaidOverThreeHoursCounter", "PaidOverHourCounter");

while ($row = $stmt->fetch())
{
  if (!isset($events[$row["EventID"]])) {
    $events[$row["EventID"]] = $row;
  } else {
    foreach ($counters as $key) {
      $events[$row["EventID"]][$key] += $row[$key];
    }
  }
}

做 SQL 应该可以更有效地做的事情看起来需要很多代码和麻烦，但是对于 MySQL 和 GROUP BY 写更多应用程序代码通常更好。

PS:在示例 SQL 查询中，我将您的联接更改为内部联接。我认为您不需要外部联接。