MySQL：如何为拥有1亿+行的表建立索引

Question

我发现自己陷入了一个困境。我有一张用于页面访问跟踪的表格，其中包含近1.05亿行数据。(!)它看起来像这样:

CREATE TABLE `media_hits` (
  `id` int(10) unsigned NOT NULL auto_increment,
  `media_code` char(7) NOT NULL,
  `day` date NOT NULL,
  `hits` int(10) unsigned NOT NULL default '0',
  PRIMARY KEY  (`id`),
  UNIQUE KEY `media_code` (`media_code`,`day`)
) ENGINE=InnoDB;

可以想象，对这个表进行任何类型的查询都需要很长时间。一个典型的查询如下：

SELECT DISTINCT(`media_code`), COUNT(*) AS c
FROM `media_hits`
WHERE `day` >= DATE_SUB(NOW(), INTERVAL 1 DAY)
GROUP BY(`media_code`)
ORDER BY c DESC
LIMIT 200;

这个查询需要很长时间。并且对该查询进行解释(EXPLAIN)的结果如下:

           id: 1
  select_type: SIMPLE
        table: media_hits
         type: index
possible_keys: NULL
          key: media_code
      key_len: 10
          ref: NULL
         rows: 104773158
        Extra: Using where; Using index; Using temporary; Using filesort

那太糟糕了。我的问题是：我该怎么办？现在尝试添加正确的索引是不可能的。 ALTER TABLE查询可能需要超过一周才能运行。我试图删除六个月前的行，但24小时后该查询仍在运行。

我需要想办法解决这个问题。唯一想到的事情是创建一个新的具有正确索引的表，并开始在该表中记录点击次数。在后台，我可以使用脚本从旧的media_hits表中缓慢插入记录。有没有人能提供有关如何为此表建立索引以及可能应该为哪些列建立索引的提示？

- mellowsoon

Answer 1

针对这种工作，仅仅进行索引可能不会有太大的帮助。最好考虑一些缓存策略，并使用一些额外的表来存储所需的聚合数据。

例如，针对您上述的查询，您可以添加第二个表 "media_code_per_day"，其中包含3列 "media_code"、"counter" 和 "date"。每次在原始表中插入一行时，也要相应地更新 "media_code_per_day"。然后，您可以在 "media_code_per_day" 上运行一个新的查询，而不是在原始查询上运行。

当然，在您的情况下初始化新表时，您需要通过一次批处理运行来遍历所有现有行，但这只需要一次即可。

- Doc Brown

---

我同意需要一个新系统，并且汇总表会极大地帮助。我想真正的问题是以不用花费数周时间的方式导入旧数据。哈哈，如果那就是必须要做的，那就是必须要做的，但如果有更简单的方法就更好了。 - mellowsoon

Answer 2

对于这个特定的查询，一个(day, media_code)索引会帮助最多。由于有group by，它仍将不得不使用临时表，并且进行文件排序，因为您正在按count(*)排序，但该索引将显着减少它必须扫描的行数。

如果您需要比这更好的性能，则可能必须像@DocBrown建议的那样创建一些聚合表。但是，在进行额外工作之前，我会首先尝试使用索引来查看它是否足够有用。

此外，您可以向删除查询添加限制，以便逐步清除旧行，而无需运行需要花费数天的大型删除。您可以分批删除它们（例如每次10K或100K行），以逐渐减小该表的大小，直到足够小以添加索引。

- nathan

Answer 3

你也可以看一下 Vertica 社区版。像这样简单的东西。

SELECT count(*) FROM event_track;
    count    
------------
1595756573
(1 row)

在一个没有最近提交查询的系统中，6秒钟就能返回结果。是的，这接近16亿行数据，我经常对它进行像你上面提到的那样的查询，并且响应时间非常合理（通常是几秒钟，很少是几分钟）。

好处是，在将mysql中的实时数据导出到一个巨大的csv文件后，使用单个COPY FROM命令将其快速轻松地导入到vertica中。

https://dba.stackexchange.com/a/35614/20451详细介绍了vertica的下载地址。

- Michael Will