编辑-MySQL。大型 MyISAM 表（4000 万条记录）的索引非常慢且磁盘上的大小很大

Question

该表包含大约 40,000,000 条记录，其中包含：

CREATE TABLE `event` (
  `id` bigint(20) unsigned NOT NULL auto_increment,
  `some_other_id_not_fk` int(10) unsigned default NOT NULL,
  `event_time` datetime NOT NULL,
  `radius` float default NULL,
  `how_heavy` smallint(6) default NULL,
  PRIMARY KEY  (`id`),
  KEY `event_some_other_id_not_fk` (`some_other_id_not_fk`),
  KEY `event_event_time` (`event_time`)
) ENGINE=MyISAM AUTO_INCREMENT=6506226 DEFAULT CHARSET=utf8 

您应该知道 some_other_id_not_fk 列并不大，它只包含 7 个不同的数字。真正的痛苦是 event_time 日期时间列，因为它包含大量不同的日期时间，并且基本上一切都是允许的：重复以及不可预测的大时间间隔，而没有记录来“覆盖”它们。您还应该知道 (some_other_id_not_fk,event_time) 对必须允许有重复项 :( 我知道这会导致更多问题 :(

我有一些经验优化 MySQL 表，但如此巨大的痛苦从未出现在我的视野中：/

“事物”的当前状态是：

• 在 date1 和 date2 之间按 event_time 选择（我需要这样做）是令人满意的快。:)
• 我的插入速度很慢，我的意思是真的很慢！！！超过 30 秒，甚至更糟：临时禁用和启用键的加载数据过程非常慢（几个小时），主要是在启用键操作上。
• 磁盘上索引的大小比数据大小大 7 倍，

到目前为止我已经尝试了几种不同的重新索引组合，但是该数据的大小确实阻止了我对索引和列删除/创建进行实验 ？

请帮助任何人解决这个问题吗？应该使用时间戳而不是日期时间来解决我的问题吗 或者也许我应该为day、year等添加额外的列并为其建立索引？

Answer 1

`id` bigint(20) unsigned NOT NULL auto_increment,

你真的需要一个 BIGINT 吗？你也许可以通过 INT 逃脱惩罚。如果每天 24 小时每秒插入 1,000 行，则需要 136 年才能耗尽无符号 32 位整数中的所有值。

此更改将使您的表大小减少 152.5 MB（4000 万行），并将主键索引大小减少 158.8 MB（4000 万行）。

`some_other_id_not_fk` int(10) unsigned default NOT NULL,

你说这只有 7 个不同的值。那么它必须是 INT 类型吗？你能用 TINYINT 代替吗？这将大大减少索引的大小。

这将使表的大小减少 114.4 MB（包含 4000 万行），并且将 some_other_id_not_fk 索引的大小减少大约相同的大小。

`event_time` datetime NOT NULL,

您需要日期时间吗？ DATETIME 占用 8 个字节，TIMESTAMP 占用 4 个字节。如果您可以使用 TIMESTAMP 那么这将大大减少数据和索引的大小。请注意 TIMESTAMP 字段的限制，例如 Y2K38 以及它们相对于时区和的行为方式复制。

此更改将使您的表大小减少 152.5 MB（4000 万行），并将主键索引大小减少 158.8 MB（4000 万行）。

这三项更改将显着减小数据和索引的大小。

节省的总空间

• 表：152.5 + 152.5 + 114.4 = 419.4 MB
• 索引：158.8 + 158.8 + ~115 = 432.6 MB

总计：852MB

正如其他人所建议的，您甚至可能不需要拥有的所有索引定义的。由于对 some_other_id_not_fk 的选择性如此之低，查询优化器很可能不会使用该索引，而是选择全表扫描。完全删除该索引将为您的索引节省大量空间。

如果您可以提供一些示例查询，我可以进一步帮助您。

另外，您是否在读取负载很重的情况下插入到该表中？请记住，MyISAM 中的 SELECT 会阻止 INSERT。

更新

大多数人建议将 some_other_id_not_fk 字段移至 event_time 索引中，以便新索引位于 (event_time, some_other_id_not_fk) 上。我会推荐同样的内容，但有一个重要的警告。

该索引非常适合仅按 event_time 进行过滤的查询，或者同时按 event_time 和 some_other_id_not_fk 进行过滤的查询。它不会仅用于some_other_id_not_fk上的查询过滤 - 将发生全表扫描。

此外，如果您的查询始终同时对 event_time 和 some_other_id_not_fk 进行过滤，那么不会 使用索引顺序(event_time, some_other_id_not_fk)。相反，您应该使用索引(some_other_id_not_fk, event_time)。

首先拥有选择性最少（最多重复）的字段将允许对索引进行更大的压缩，从而显着减少磁盘上的占用空间。

Answer 2

我认为你对什么是重、什么不是的直觉是倒退的：一个多次重复几个不同选项的索引比一个有很多不同值且每个值很少重复的索引要差。

我的建议：删除 some_other_id_not_fk 上的索引并保留 (some_other_id_not_fk, event_time)。这个复合索引应该是“几乎唯一的”，使得插入开销低得多。如果可能，也删除 event_time 键，除非您的查询使用该字段而不使用 some_other_id_not_fk。

编辑：您说必须按时间间隔选择，然后保留 (event_time, some_other_id_not_fk) 并删除 event_time 和 some_other_id_not_fk。如果您的查询使用 some_other_id_not_fk 而不是 event_time，请同时保留 (event_time, some_other_id_not_fk) 和 (some_other_id_not_fk, event_time)< /代码>。重点是没有任何选项很少的索引。右侧有一个未使用字段的索引是可以的。

Answer 3

我认为您不需要 some_other_id_not_fk 上的索引（正如您所说，只有 7 个不同的值，因此该索引的选择性为 40,000,000/7 ）。您所需要的只是 (event_time + [也许] some_other_id_not_fk) 上的 1 个索引；

Answer 4

我之前也有过类似的情况。我创建了一个具有相同结构的表，我们将其称为存档表。我每天 3:00 将数据从活动表复制到其中，并删除所有原始数据。

图表和其他静态数据是从存档表中选择的，当前事件被记录到活动事件中。

也许这不是最佳实践，但对我来说已经足够了。

按时间分区表：MySQL 5.1 中的日期分区 (Robin Schumacher)

http://dev.mysql.com/tech-resources/articles/mysql_5.1_partitioning_with_dates.html

Answer 5

我已删除所有索引并在 (event_time, some_other_id_not_fk) 上创建索引。我得到以下性能指标：

• 磁盘上的数据大小为 1Gb，磁盘上的索引大小为 1.2Gb。

• 从 event 中删除，其中 event.event_time>STR_TO_DATE('20091201000000','%Y%m%d%H%i %s') 和事件。some_other_id_not_fk=4 |受影响的行：353543
  时间：65.173 秒

• select * from event where event.event_time>STR_TO_DATE('20090401000000','%Y%m% d%H%i%s') 和事件。event_time<=STR_TO_DATE('20090401010000','%Y%m%d%H%i%s' ) 和事件。(22,4,1,3) | 中的some_other_id_not_fk集合中有 916 行，查询时间：0.030 秒

• 索引启用了使用以下格式插入 350,000 条新记录：insert into event VALUES(...),(...),.. 。执行时间约为 30 秒，Yeahaaaaaa :))

• 索引禁用 - 插入 - 索引启用 - 使用相同格式的 350,000 个新记录：插入 event VALUES(...),(.. .),... |大约40分钟内完成。 :) 看起来像 mysql 默认转储格式，在插入之前禁用索引并在插入之后重新启用它，并不总是有利于性能，特别是当存在大尺寸索引时:)

现在我对此性能感到满意。

昨晚我成功地仅在 (event_time) 创建索引。索引的大小略小于第一个示例。约1.1Gb。与上面列出的相同查询的性能：

• 删除|稍微快一点，大约30秒
• 选择|稍微慢一点，大约0.1秒。

• 我只测试了 350,000 的索引禁用-启用插入。又很慢|约35分钟。

  我拒绝了数据库的这种状态，因为我对选择速度不够满意，这对我来说是优先级N1。

hobodave，我只是好奇，你认为在 (some_other_id_not_fk,event_time) 上建立索引而不是 (event_time,some_other_id_not_fk）真的会发生戏剧性的改变，变得更好吗？我的查询将始终在这两个字段上进行过滤。如果没有通过 some_other_id_not_fk 过滤，我永远不会进行查询。但我可能有一个按 IN(x,y,...) 过滤大多数不同 some_other_id_not_fk 的查询。正如我所说，他们并不多。

我的优先事项是：

1. 选择速度
2. 插入速度
3. 磁盘上的索引大小（因为表将增长几倍）
   ...其他一切

我还想知道为什么 1Gb 数据需要如此巨大的索引大小 1.2Gb？指数仍大于数据。我的逻辑表明，这种日期索引可以在更小的索引中完成？我说得对吗？是否有与索引类型相关的内容（可能是 BTREE）？

谢谢。你们都很棒。我正在关闭线程。