mysql - 帮助 MYSQL 查询聚合计数(替代三个子查询)

Question

我正在尝试从我的统计表中输出总的内容 View 并按年分组...我的统计表是 INNODB 并且有 800 万行并且还在增长...

该表本质上是 ID、DATE、MAKE、IP、REFERRER(id、date、make 上的索引)

每个条目都有一个自动递增的 ID，条目日期 YYYY-MM-DD HH:MM:SS，以及产品品牌，如“sony”、“panasonic”等...

我正在尝试进行一个不会杀死我的服务器的查询，该查询汇总每年的总内容浏览量并按从最多浏览到最少浏览的顺序显示它们......(2011 年)以便我可以使用该数据填充 JS 图表，将今年与过去几年进行比较。我可以通过多个查询并在 PHP 中遍历数组来做到这一点，但我认为应该有一种方法可以在一个查询中获得它，但如果我能弄清楚的话见鬼去吧。

有什么想法吗？另外，我是最好进行三个独立的查询并在 PHP 中处理结果，还是可以将其放入一个 MYSQL 效率更高的查询中。

我想看到的输出(虽然我似乎无法做到这一点)很简单

MAKE           2009Total   2010Total 2011Total----           ---------   --------- ---------Panasonic      800         2345      3456Sony           998         5346      2956JVC            1300        1234      1944           

假设我的表中有从 2009 年到现在的数据，我需要我的数组每个品牌包含一行...

如有任何帮助，我们将不胜感激...我很惊讶这样的结果从分析工具返回的速度如此之快，而我的在 4x 四核 XEON RAID mysql 服务器上大约需要 75 秒...此统计表未写入但是每天一次转储前一天的统计信息，所以我不确定为什么我的 9 月 3 日查询如此缓慢......因此我的问题......也许单个查询不会更快？

无论如何，我们将不胜感激，欢迎提出有关从通用 View 统计表中加速统计查询的意见!

Answer 1

我做了一个观察。您的查询是按年份请求的。你应该做两件事:

1. 存储年份
2. 创建更好的索引(产品、年份)

你可以这样做:

CREATE TABLE stats_entry_new LIKE stats_entry;
ALTER TABLE stats_entry_new ADD COLUMN entryyear SMALLINT NOT NULL AFTER date;
ALTER TABLE stats_entry_new ADD INDEX product_year_ndx (product,year);
ALTER TABLE stats_entry_new DISABLE KEYS;
INSERT INTO stats_entry_new
SELECT ID, DATE,YEAR(date),product,IP,REFERRER FROM state_entry;
ALTER TABLE stats_entry_new ENABLE KEYS;
ALTER TABLE stats_entry RENAME stats_entry_old;
ALTER TABLE stats_entry_new RENAME stats_entry;

现在查询看起来像这样:

SELECT A.product,B.cnt "2009Total",C.cnt "2010Total",D.cnt "2011Total"
FROM
(SELECT DISTINCT product FROM stats_entry) A
INNER JOIN 
(SELECT product,COUNT(1) cnt FROM stats_entry WHERE entryyear=2009 GROUP BY product) B
USING (product)
(SELECT product,COUNT(1) cnt FROM stats_entry WHERE entryyear=2010 GROUP BY product) C
USING (product)
(SELECT product,COUNT(1) cnt FROM stats_entry WHERE entryyear=2011 GROUP BY product) D
USING (product);

公平地说，如果你不想在表中添加年份，那么你仍然需要做一个索引

ALTER TABLE stats_entry ADD INDEX product_date_ndx (product,date);

你的查询现在看起来像这样

SELECT A.product,B.cnt "2009Total",C.cnt "2010Total",D.cnt "2011Total"
FROM
(SELECT DISTINCT product FROM stats_entry) A
INNER JOIN 
(SELECT product,COUNT(1) cnt FROM stats_entry
WHERE date >= '2009-01-01 00:00:00'
AND date <= '2009-12-31 23:59:59'
GROUP BY product) B
USING (product)
(SELECT product,COUNT(1) cnt FROM stats_entry
WHERE date >= '2010-01-01 00:00:00'
AND date <= '2010-12-31 23:59:59'
GROUP BY product) C
USING (product)
(SELECT product,COUNT(1) cnt FROM stats_entry
WHERE date >= '2011-01-01 00:00:00'
AND date <= '2011-12-31 23:59:59'
GROUP BY product) D
USING (product);

试一试!!!