mysql - 是否可以优化此 mysql 查询？

Question

我有一个表，其中有几百万行，我正在查询该表，想知道我是否可以通过添加索引或其他任何方式来优化查询。

表架构:

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 |
+----+-------------+----------------+------+---------------+------+---------+------+--------+----------------------------------------------+

它使用“Using where”是因为 WHERE 子句，“Using temporary”是因为 group by 和 filesort 因为 group by also(我认为)。

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

之所以使用像“@sum_impressions”这样的变量，是因为这个 SELECT 语句是“INSERT INTO ... ON DUPLICATE KEY UPDATE”语句的一部分。

Answer 1

有几种可能的方法。

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

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

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

如果我有你的问题，我会测试 super 指标的性能(但要对整个应用进行良好的调试；不同的部分可能会受到不同的性能影响)；然后我会尝试使用批处理解决方案，即使这意味着有两个表并处理同步。