我们有以下MySQL表(为了直接进入主题而进行了简化):
我们想在MySQL中进行FULL Outer Join,查询的基本架构如这里所提出的那样。
我们该如何优化查询呢?我们尝试了不同的索引、调整各种参数,但是8个小时后仍然没有运行完毕。
MySQL服务器是一台Percona MySQL 5.5专用服务器,配有2个CPU、2GB内存和SSD硬盘。我们使用了Percona工具优化了服务器的配置。
非常感谢任何帮助。
谢谢。
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
包含用户在网站上执行的操作,大约有2000万行数据,文件大小接近4GB。
compilation_table
汇总了每个月客户和城市的信息。我们用它来实时显示网站的统计数据。
我们每个月只处理一次统计数据(从第一个表到第二个表),并且我们正在尝试优化执行此操作的查询(因为目前我们使用PHP处理所有内容需要很长时间)。
以下是我们想出的查询语句,在使用小数据集时似乎可以胜任工作,但对于完整的数据集处理需要超过6个小时。
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
我们想在MySQL中进行FULL Outer Join,查询的基本架构如这里所提出的那样。
我们该如何优化查询呢?我们尝试了不同的索引、调整各种参数,但是8个小时后仍然没有运行完毕。
MySQL服务器是一台Percona MySQL 5.5专用服务器,配有2个CPU、2GB内存和SSD硬盘。我们使用了Percona工具优化了服务器的配置。
非常感谢任何帮助。
谢谢。