Mysql
MySQL 查詢需要一分鐘才能完成
我正在嘗試從我們龐大的數據庫中提取與網站訪問相關的呼叫…我有可以工作的 SQL，提取正確的結果只需幾分鐘即可完成…
這是SQL
SELECT SourceMediumID, CONCAT(SourceDescription, " / ", MediumDescription), COUNT(DISTINCT first_click_calls.CallID) AS 'First Click Calls', COUNT(DISTINCT first_click_visitors.VisitorID) AS 'First Click Visitors', COUNT(DISTINCT last_click_calls.CallID) AS 'Last Click Calls', COUNT(DISTINCT last_click_visitors.VisitorID) AS 'Last Click Visitors' FROM sourcemediumreference LEFT JOIN sources ON SourceMediumSourceID = SourceID LEFT JOIN mediums ON SourceMediumMediumID = MediumID LEFT JOIN calls last_click_calls ON last_click_calls.CallSourceMediumID = SourceMediumID AND last_click_calls.CallCampaignID = 222 AND last_click_calls.CallDate >= '2015-03-01' AND last_click_calls.CallDate <= '2015-03-31' LEFT JOIN visitorvisits last_click_visits ON last_click_calls.CallVisitID = last_click_visits.VisitID LEFT JOIN visitorvisitors last_click_visitors ON last_click_visits.VlviVisitorID = last_click_visitors.VisitorID LEFT JOIN calls first_click_calls ON first_click_calls.CallFirstSourceMediumID = SourceMediumID AND first_click_calls.CallCampaignID = 222 AND first_click_calls.CallDate >= '2015-03-01' AND first_click_calls.CallDate <= '2015-03-31' LEFT JOIN visitorvisits first_click_visits ON first_click_calls.CallFirstVisitID = first_click_visits.VisitID LEFT JOIN visitorvisitors first_click_visitors ON first_click_visits.VlviVisitorID = first_click_visitors.VisitorID WHERE SourceGenericFlag=1 OR SourceCustomerID=249 GROUP BY SourceMediumID這些是結果：
+--------+-------------------------------------------------+-------------------+----------------------+------------------+---------------------+ | SomeID | SourceMedium | First Click Calls | First Click Visitors | Last Click Calls | Last Click Visitors | +--------+-------------------------------------------------+-------------------+----------------------+------------------+---------------------+ | 1 | google / organic | 175 | 144 | 307 | 100 | | 2 | google / ppc | 0 | 0 | 28 | 0 | | 3 | direct / direct | 206 | 170 | 609 | 217 | | 4 | about / organic and ppc | 0 | 0 | 0 | 0 | | 5 | alltheweb / organic and ppc | 0 | 0 | 0 | 0 | | 6 | alta vista / organic and ppc | 0 | 0 | 0 | 0 | | 8 | aol / organic and ppc | 5 | 4 | 5 | 4 | | 9 | ask / organic and ppc | 3 | 3 | 2 | 2 | | 11 | bing / organic and ppc | 334 | 232 | 577 | 243 | | 12 | lycos / organic and ppc | 0 | 0 | 0 | 0 | | 13 | yahoo / organic | 19 | 18 | 144 | 18 | | 14 | yahoo / ppc | 0 | 0 | 0 | 0 | | 68 | referral / referral | 183 | 160 | 1393 | 186 | | 109 | Source 1 / referral | 66 | 50 | 49 | 40 | | 110 | Source 2 / referral | 0 | 0 | 0 | 0 | | 315 | TEST / referral | 11 | 10 | 12 | 10 | | 324 | Website / direct | 0 | 0 | 0 | 0 | | 325 | test .co.uk / referral | 0 | 0 | 0 | 0 | | 338 | Dealers / referral | 0 | 0 | 0 | 0 | | 339 | omg.com / referral | 0 | 0 | 0 | 0 | | 340 | Heads / referral | 4 | 3 | 2 | 2 | | 352 | Yellow Pages / referral | 0 | 0 | 0 | 0 | | 367 | PPC / ppc | 232 | 191 | 185 | 154 | | 376 | PPC / organic | 1246 | 1003 | 1275 | 1054 | +--------+-------------------------------------------------+-------------------+----------------------+------------------+---------------------+ 24 rows in set (1 min 17.64 sec)當我執行“解釋”時，它告訴我它正在使用這些索引：
table: sources | type: index_merge | index: index_SourceGenericFlag,index_SourceCustomerID table: sourcemediumreference| type: ref | index: index_SourceMediumSourceID table: last_click_calls | type: ref | index: index_CallCampaignID table: first_click_calls | type: ref | index: index_CallCampaignID這是 EXPLAIN 的完整結果：
+----+-------------+-----------------------+-------------+------------------------------------------------------+------------------------------------+---------+--------------------------------------------+-------+------------------------ -----------------------------------------------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-----------------------+-------------+------------------------------------------------------+------------------------------------+---------+--------------------------------------------+-------+------------------------ -----------------------------------------------------------------------+ | 1 | SIMPLE | sources | index_merge | PRIMARY,index_SourceCustomerID,index_SourceGenericFlag | index_SourceGenericFlag,index_SourceCustomerID | 4,4 | NULL | 20 | Using union(index_SourceGen ericFlag,index_SourceCustomerID); Using where; Using temporary; Using filesort | | 1 | SIMPLE | sourcemediumreference | ref | index_SourceMediumSourceID | index_SourceMediumSourceID | 4 | mydbtest.sources.SourceID | 1 | | | 1 | SIMPLE | mediums | eq_ref | PRIMARY | PRIMARY | 4 | mydbtest.sourcemediumreference.SourceMediumMediumID | 1 | | | 1 | SIMPLE | last_click_calls | ref | index_CallDate ,index_CallSourceMediumID,index_CallCampaignID | index_CallCampaignID | 4 | const | 78917 | | | 1 | SIMPLE | last_click_visits | eq_ref | PRIMARY | PRIMARY | 4 | mydbtest.last_click_calls.CallVisitID | 1 | | | 1 | SIMPLE | last_click_visitors | eq_ref | PRIMARY | PRIMARY | 4 | mydbtest.last_click_visits.VlviVisitorID | 1 | Using index | | 1 | SIMPLE | first_click_calls | ref | index_CallDate ,index_CallCampaignID ,index_CallFirstSourceMediumID | index_CallCampaignID | 4 | const | 78917 | | | 1 | SIMPLE | first_click_visits | eq_ref | PRIMARY | PRIMARY | 4 | mydbtest.first_click_calls.CallFirstVisitID | 1 | | | 1 | SIMPLE | first_click_visitors | eq_ref | PRIMARY | PRIMARY | 4 | mydbtest.first_click_visits.VlviVisitorID | 1 | Using index | +----+-------------+-----------------------+-------------+------------------------------------------------------+------------------------------------+---------+--------------------------------------------+-------+------------------------ -----------------------------------------------------------------------+last_click_calls 和 first_click_calls 是呼叫表的別名。我加入了兩次通話，因為它需要在兩個不同的欄位上加入：CallSourceMediumID 和 CallFirstSourceMediumID
LEFT JOIN calls last_click_calls ON last_click_calls.CallSourceMediumID = SourceMediumID LEFT JOIN calls first_click_calls ON first_click_calls.CallFirstSourceMediumID = SourceMediumID以下是 show create table for calls 表的結果：
| calls | CREATE TABLE `calls` ( `CallID` int(11) NOT NULL AUTO_INCREMENT, `CallVisitID` int(11) NOT NULL, `CallCampaignID` int(11) NOT NULL, `CallSourceMediumID` int(11) NOT NULL, `CallDate` date NOT NULL DEFAULT '0000-00-00', `CallFirstSourceMediumID` int(11) NOT NULL, `CallFirstVisitID` int(11) NOT NULL, PRIMARY KEY (`CallID`), KEY `idx_CallDate` (`CallDate`), KEY `idx_CallVisitID` (`CallVisitID`), KEY `idx_CallSourceMediumID` (`CallSourceMediumID`), KEY `idx_CallCampaignID` (`CallCampaignID`), KEY `idx_CallFirstVisitID` (`CallFirstVisitID`), KEY `idx_CallFirstSourceMediumID` (`CallFirstSourceMediumID`) ) ENGINE=MyISAM AUTO_INCREMENT=36783127 DEFAULT CHARSET=latin1 |我正在使用 MySQL 5.1 MyISAM 引擎。
你們能看到我能做些什麼來加快速度嗎？
任何幫助將不勝感激。
首先將這些索引添加到
calls表中：(CallCampaignID, CallSourceMediumID, CallDate) (CallCampaignID, CallFirstSourceMediumID, CallDate)這些應該會使其更快，因為它們允許
calls表的兩個連接檢查更少的行（現在每次約為 70k，這意味著數百萬個組合）。之後檢查性能並添加新的
EXPLAIN. 可能有一些方法可以讓它更快，但可能不會那麼快。
綜合指數。
LEFT JOIN calls last ON last.CallSourceMediumID = SourceMediumID AND last.CallCampaignID = 222 AND last.CallDate >= '2015-03-01' AND last.CallDate <= '2015-03-31'需要
INDEX(CallSourceMediumID, CallCampaignID, CallDate)（前兩列可以按任意順序排列，但日期必須在末尾，因為它是一個範圍。）
建議這種模式：
AND last.CallDate >= '2015-03-01' AND last.CallDate < '2015-03-01' + INTERVAL 1 MONTH其他
JOIN的也一樣calls。保持壓力遷移到 InnoDB 並超越 5.1。5.6 中進行了許多優化改進。而且，您等待升級的時間越長，升級難度就越大。
另一件事要嘗試：
代替
COUNT(DISTINCT x.y) AS z ... LEFT JOIN x ON ...嘗試
( SELECT COUNT(DISTINCT y) FROM x WHERE ... ) AS z在某些情況下（我不知道你的情況），它可以消除對
GROUP BY. 通常GROUP BY是在結果集膨脹後縮小結果集JOINs。這可能是一個巨大的加速。