优化MySQL查询以避免“使用临时”和“使用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查询中，我将您的连接更改为内部连接。我不认为你需要外部连接。