बड़ी मात्रा में डेटा के लिए MySQL पूर्ण बाहरी जुड़ें अनुकूलित करें - mysql, sql, बाहरी-शामिल, क्वेरी-प्रदर्शन

हमारे पास निम्नलिखित mysql टेबल हैं (बिंदु पर सीधे जाने के लिए सरलीकृत)

CREATE TABLE `MONTH_RAW_EVENTS` (
`idEvent` int(11) unsigned NOT NULL,
`city` varchar(45) NOT NULL,
`country` varchar(45) NOT NULL,
`ts` datetime NOT NULL,
`idClient` varchar(45) NOT NULL,
`event_category` varchar(45) NOT NULL,
... bunch of other fields
PRIMARY KEY (`idEvent`),
KEY `idx_city` (`city`),
KEY `idx_country` (`country`),
KEY `idClient` (`idClient`),
) ENGINE=InnoDB;

CREATE TABLE `compilation_table` (
`idClient` int(11) unsigned DEFAULT NULL,
`city` varchar(200) DEFAULT NULL,
`month` int(2) DEFAULT NULL,
`year` int(4) DEFAULT NULL,
`events_profile` int(10) unsigned NOT NULL DEFAULT "0",
`events_others` int(10) unsigned NOT NULL DEFAULT "0",
`events_total` int(10) unsigned NOT NULL DEFAULT "0",
KEY `idx_month` (`month`),
KEY `idx_year` (`year`),
KEY `idx_idClient` (`idClient`),
KEY `idx_city` (`city`)
) ENGINE=InnoDB;

MONTH_RAW_EVENTS लगभग 20M पंक्तियों में उपयोगकर्ता एक वेबसाइट में कार्य करता है, इसका आकार लगभग 4GB है

compilation_table प्रत्येक माह में एक सारांश ग्राहक / शहर हैं, हम इसका उपयोग वास्तविक समय में एक वेबसाइट पर आँकड़े प्रदर्शित करने के लिए करते हैं

We process the statistics (from first table to प्रति माह एक बार), और हम "ऐसी कार्रवाई करने वाली क्वेरी को अनुकूलित करने का प्रयास कर रहे हैं (जैसा कि अब तक हम PHP में हर चीज को संसाधित करते हैं जो लूंग लूंग समय लेता है)

यहाँ हम क्वेरी के साथ आए हैं, जो डेटा के छोटे सबसेट का उपयोग करते समय काम करता हुआ प्रतीत होता है, डेटा के पूर्ण सेट के लिए प्रक्रिया में 6hours से अधिक समय लगने वाली समस्या

INSERT INTO compilation_table (idClient,city,month,year,events_profile,events_others)


SELECT  IFNULL(OTHERS.idClient,AP.idClient) as idClient,
IF(IFNULL(OTHERS.city,AP.city)="","Others",IFNULL(OTHERS.city,AP.city)) as city,
01,2014,
IFNULL(AP.cnt,0) as events_profile,
IFNULL(OTHERS.cnt,0) as events_others

FROM
(
SELECT idClient,CONCAT(city,", ",country) as city,count(*) as cnt
FROM `MONTH_RAW_EVENTS` WHERE `ts`>"2014-01-01 00:00:00" AND `ts`<="2014-01-31 23:59:59"
AND `event_category`!="CLIENT PROFILE"
GROUP BY idClient,city
) as OTHERS
LEFT JOIN
(
SELECT idClient,CONCAT(city,", ",country) as city,count(*) as cnt
FROM `MONTH_RAW_EVENTS` WHERE `ts`>"2014-01-01 00:00:00" AND `ts`<="2014-01-31 23:59:59"
AND `event_category`="CLIENT PROFILE"
GROUP BY idClient,city
) as CLIPROFILE
ON CLIPROFILE.city=OTHERS.city and CLIPROFILE.idClient=OTHERS.idClient

UNION

SELECT  IFNULL(OTHERS.idClient,CLIPROFILE.idClient) as idClient,
IF(IFNULL(OTHERS.city,CLIPROFILE.city)="","Others",IFNULL(OTHERS.city,CLIPROFILE.city)) as city,
01,2014,
IFNULL(CLIPROFILE.cnt,0) as events_profile,
IFNULL(OTHERS.cnt,0) as events_others
FROM
(
SELECT idClient,CONCAT(city,", ",country) as city,count(*) as cnt
FROM `MONTH_RAW_EVENTS` WHERE `ts`>"2014-01-01 00:00:00" AND `ts`<="2014-01-31 23:59:59"
AND `event_category`!="CLIENT PROFILE"
GROUP BY idClient,city
) as OTHERS
RIGHT JOIN
(
SELECT idClient,CONCAT(city,", ",country) as city,count(*) as cnt
FROM `MONTH_RAW_EVENTS` WHERE `ts`>"2014-01-01 00:00:00" AND `ts`<="2014-01-31 23:59:59"
AND `event_category`="CLIENT PROFILE"
GROUP BY idClient,city
) as CLIPROFILE
ON CLIPROFILE.city=OTHERS.city and CLIPROFILE.idClient=OTHERS.idClient

हम "क्या करने की कोशिश कर रहे हैं एक पूर्ण बाहरी मैसूर में शामिल है तो क्वेरी के मूल स्कीमा की तरह है: एक यहाँ प्रस्तावित है

हम क्वेरी को कैसे ऑप्टिमाइज़ कर सकते हैं? हम "अलग-अलग अनुक्रमित कोशिश कर रहे हैं, चारों ओर चीजों को स्वाइप कर रहे हैं लेकिन 8 घंटे के बाद भी अभी भी नहीं चल रहा है,

MySQL सर्वर एक Percona MySQL 5.5 2cpu, 2GB RAM और SSD डिस्क के साथ समर्पित मशीन है, हमने पेरकोना टूल्स का उपयोग करके ऐसे सर्वर के कॉन्फ़िगरेशन को अनुकूलित किया,

किसी भी तरह की सहायता की सच में प्रशंसा की जाएगी,

धन्यवाद

उत्तर:

जवाब के लिए 3 № 1

आप एक UNION कर रहे हैं जिसके परिणामस्वरूप DISTINCT प्रोसेसिंग होती है।

यह आम तौर पर एक पूर्ण जॉइन करने के लिए एक लेफ्ट जॉइन के अलावा राइट राइट की नॉन-मैचिंग पंक्तियों को फिर से लिखना बेहतर होता है (यदि यह उचित 1: n जॉइन है)

OTHERS LEFT JOIN CLIPROFILE
ON CLIPROFILE.city=OTHERS.city and CLIPROFILE.idClient=OTHERS.idClient
union all
OTHERS RIGHT JOIN CLIPROFILE
ON CLIPROFILE.city=OTHERS.city and CLIPROFILE.idClient=OTHERS.idClient
WHERE OTHERS.idClient IS NULL

इसके अतिरिक्त आप के परिणामों को अमल में ला सकते हैंउन्हें शामिल होने से पहले अस्थायी तालिकाओं में व्युत्पन्न टेबल्स, इस प्रकार गणना केवल एक बार की जाती है (अगर मुझे पता है कि MySQL का अनुकूलक स्वचालित रूप से ऐसा करने के लिए पर्याप्त स्मार्ट है)।

इसके अलावा यह अलग-अलग स्तंभों के रूप में समूह / शहर और देश से जुड़ने के लिए अधिक कुशल हो सकता है और कर सकता है CONCAT (शहर, ",", देश) शहर के रूप में बाहरी चरण में।


संबंधित सवाल
सबसे लोकप्रिय