这个 MySQL 查询是否有优化的可能？

Question

我可以帮助您翻译这段文字，它与IT技术有关。您想知道如何优化查询数百万行的表，是否可以通过添加索引或其他方式来优化查询。

表结构：

CREATE TABLE `aggregate_data` (
  `impressions` int(10) unsigned NOT NULL,
  `clicks` int(10) unsigned NOT NULL,
  `leads` int(10) unsigned NOT NULL,
  `date` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
  `country` varchar(2) COLLATE utf8_bin NOT NULL,
  `campaign_id` int(10) unsigned NOT NULL,
  `payout` decimal(12,6) NOT NULL,
  `revenue` decimal(12,6) NOT NULL,
  `creative_id` int(10) unsigned NOT NULL DEFAULT '0',
  `advertiser_id` int(11) unsigned NOT NULL DEFAULT '0',
  `offer_id` int(11) unsigned NOT NULL DEFAULT '0',
  `affiliate_id` int(11) unsigned NOT NULL DEFAULT '0',
  PRIMARY KEY (`country`,`campaign_id`,`date`),
  KEY `date_added` (`date`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

SQL查询：

SELECT 
    DATE_FORMAT(`date`, "%Y-%m-01 00:00:00") AS `date`, 
    offer_id,
    country,
    @sum_impressions := SUM(impressions), 
    @sum_clicks := SUM(clicks), 
    @sum_leads := SUM(leads), 
    @sum_payout := SUM(payout), 
    @sum_revenue := SUM(revenue)
FROM aggregate_data
WHERE `date` >= '2012-12-00 00:00:00'
GROUP BY country, offer_id, MONTH(`date`), YEAR(`date`)

每当我执行解释时，它总是告诉我它使用了表中的所有行。

+----+-------------+----------------+------+---------------+------+---------+------+--------+----------------------------------------------+
| id | select_type | table          | type | possible_keys | key  | key_len | ref  | rows   | Extra                                        |
+----+-------------+----------------+------+---------------+------+---------+------+--------+----------------------------------------------+
|  1 | SIMPLE      | aggregate_data | ALL  | date_added    | NULL | NULL    | NULL | 809715 | Using where; Using temporary; Using filesort |
+----+-------------+----------------+------+---------------+------+---------+------+--------+----------------------------------------------+

由于 WHERE 子句，它使用了 "Using where"。由于 GROUP BY，它使用了 "Using temporary" 并且也因为 GROUP BY 而使用了 filesort（我想是这样）。

现在，我应该添加哪些其他索引或其他内容来优化此查询。随着行数增加，运行时间需要几秒钟。

像 "@sum_impressions" 这样的变量被使用是因为此 SELECT 语句是 "INSERT INTO ... ON DUPLICATE KEY UPDATE" 语句的一部分。

- gprime

---

似乎索引的希望在于日期列。 - Randy

Answer 1

嗯，这是一个可能会让 where 子句上的索引变得更糟糕的案例。索引可能会让情况变得更糟，因为查询将扫描索引并读取原始数据，但顺序混乱。如果数据大于内存，则可能会发生不必要的缓存未命中。

一个解决方案是按日期分区您的数据。

一个想法是在日期、国家和 offer_id 上建立索引（一个索引，三个部分）。

我不确定这个方案是否可行。它可以解决 where 的问题，但只能解决一半的group by 问题。

如果年份和月份是单独的列，并且查询看起来像：

WHERE year >= 2012 and
GROUP BY country, offer_id, month, year

然后，对于(year, month, country, offer_id)的索引可以满足where和group by只使用索引。我不确定在混合不同粒度的日期时会发生什么。这导致了按日期分区（可能是按月份级别），然后对计数、offer_id和日期进行索引。 （一些数据库实际上支持功能索引，您可以在索引中使用year(date)。）

- Gordon Linoff

---

三个都要单独建立索引吗？ - gprime

Answer 2

有几种方法可行。

• 您可以使用RANGEpartitioning按年份划分表格。

• 您可以运行批处理并存储每个月的总数，然后在totals表上运行查询。从其名称来看，aggregate_data似乎已经是批量生成的，因此这可能不会太困难或昂贵。

• 您可以尝试在date，country，offer_id上建立索引，但我认为如果您在date，country，offer_id，impressions，clicks，leads，payout，revenue上建立索引，您可能会获得更好的结果（以及浪费更多的磁盘空间以及INSERT时间）（这样，查询所需的所有数据都已经在索引中；不需要访问数据表。当然，这是有代价的-INSERT性能）。通过将date拆分为year和month，您可以在选择性能方面实现更好的结果。

如果我有你的问题，我会测试超级索引的性能（但要对整个应用程序进行彻底检查；不同的部分可能会受到不同的性能影响）；然后我会尝试使用批处理解决方案，即使这意味着拥有两个表并处理同步。

- LSerni

Answer 3

仅看表结构：

• 3部分主键使该表因InnoDB聚簇索引的方式而变得低效，而“id自增非空主键”可以帮助解决问题。
• 然后，添加“唯一（country，campaign_id，date）”约束以保持唯一性。
• 此外，为group by添加索引：“key（country，offer_id）”，可能还要将月/年添加到该索引中。

- TrippyD