MySQL ALTER TABLE 在一个非常大的表上运行，是否安全？

Question

我有一个拥有400万行记录的MyISAM表格的MySQL数据库。我每周更新这个表格，大约会新增2000行数据。更新后，我会像这样修改这个表：

ALTER TABLE x ORDER BY PK DESC

我按照主键字段降序排序表格。在我的开发机器上（拥有3GB内存的Windows），这没有给我带来任何问题。我已经成功尝试了三次在生产Linux服务器上运行它（具有512MB RAM - 并且每次都在约6分钟内获得了结果排序的表格），但最后一次尝试后，我不得不在大约30分钟后停止查询并从备份重建数据库。

一个512MB的服务器是否能处理如此大的表格的alter语句？我已经阅读到一个临时表将被创建来执行ALTER TABLE命令。

问题：这个alter命令是否可以安全地运行？对于表格的修改应该期望多长时间？

- Timothy Mifsud

---

我认为“非常大的表”可能有些夸张了。4M行并不是一个非常大的表。10亿行可能会更大一些。 - MarkR

Answer 1

我刚刚阅读了ALTER TABLE ... ORDER BY ...查询的内容，它在某些情况下可以提高性能。我很惊讶主键索引对此没有帮助。但是从MySQL文档中可以看出，InnoDB确实使用了该索引。然而，InnoDB倾向于比MyISAM慢。也就是说，使用InnoDB您不需要重新排序表格，但您会失去MyISAM的快速响应速度。这仍然值得一试。

您解释问题的方式似乎是加载了太多数据到内存中（甚至可能发生了交换？）。您可以通过监视内存使用情况轻松检查。由于我不太熟悉MySQL，因此很难说。

另一方面，我认为您的问题在于：您正在使用只有512兆内存的机器作为数据库服务器，其中包含超过4百万行的表格...并且在该机器上对整个表格执行非常耗费内存的操作。512兆内存显然不足以胜任这项任务。

我在这里看到一个更基本的问题：您在与生产环境非常不同的环境中进行开发（很可能也在测试）。您所描述的问题是可以预料的。您的开发机器的内存是生产机器的六倍。我相信处理器速度也更快。在这种情况下，我建议您创建一个虚拟机，模仿生产环境。这样，您就可以轻松地测试项目而不会干扰生产环境。

- exhuma

---

InnoDB的最近改进使其在大多数情况下与MyISAM表现相当。 - Bill Karwin

@Bill：有趣。那么可以说InnoDB确实是首选吗？同样的性能，更多的功能。看了你的个人资料后，我觉得我可以相信你。不过，你有什么证据来支持这一点吗？ - exhuma

Answer 2

你所要求的是重建整个表和所有索引；这是一项昂贵的操作，特别是如果数据不适合内存。它会完成，但如果数据不适合内存，特别是如果你有很多索引，速度会慢得多。

我对你在生产环境中选择运行内存如此小的机器的判断表示怀疑。无论如何：

• 这个ALTER TABLE真的必要吗？你想加速哪个具体的查询，并且你尝试过没有ALTER TABLE吗？
• 你考虑过让你的开发机器更像生产环境吗？我的意思是，使用内存更多的开发机器从来不是一个好主意，使用不同的操作系统也绝对不是。

还有一些调整可能会有帮助；这在很大程度上取决于你的模式（特别是索引）。对于正常内存量的机器来说，4M行并不算太多。

- MarkR

---

嗨马克，谢谢你的回复。内存限制是基于预算的考虑。我想，如果这个网站受欢迎的话，我会升级服务器的规格... 然而，进行ALTER操作的原因是用户可以运行一个查询这个表的存储过程，我希望返回结果按照“最新插入的先返回”的顺序。我可以在查询本身中使用ORDER BY来实现这个目的，但不幸的是这样做非常昂贵并且会大大减慢查询速度...所以，当我更新表时，通常会按照主键降序进行预排序，以避免使用ORDER BY。 - Timothy Mifsud

你应该创建一个适当的索引，这样 ORDER BY 查询就不需要排序。你可以使用 EXPLAIN 来检查（仅当查询不在存储过程中时）。ALTER TABLE ... ORDER BY 不是解决方案，因为它不能保证数据保持有序。 - MarkR

嗨，马克。在这个表上我有8个索引。如果我将 PK 字段（我想按照降序排序）添加到每个这些索引的最右侧部分，那么索引仍然可以用于满足 WHERE 子句，并且即使排序字段不是索引的左前缀（因为我将其添加到每个索引的最右边），它仍然可以用于 ORDER BY 吗？谢谢。 - Timothy Mifsud

很不幸，我已经将ORDER BY字段添加到索引中，但是EXPLAIN仍然显示它正在使用文件排序...我不认为我可以在索引中包含ORDER BY字段，以便WHERE子句也将使用索引...我还尝试过"SELECT * FROM (SELECT...主查询) ORDER BY..."，但是它仍然比没有ORDER BY子句时要慢得多... - Timothy Mifsud

你需要一个包含两列并且顺序正确的索引。发表另一个问题，包含你的架构、查询和解释计划。 - MarkR

Answer 3

主键是自动递增的吗？如果是，那么执行 ALTER TABLE ... ORDER BY 不会改善任何东西，因为所有内容都将按顺序插入。

（除非您有大量删除操作）

- longneck

---

谢谢您的回复。然而，问题在于我想按照主键顺序的相反顺序给出结果... - Timothy Mifsud

如果您的存储过程、查询和服务器设置性能不佳，那么您需要进行优化，而不是尝试 ALTER TABLE 等黑魔法操作，这只是因为 MyISAM 表中存在一些怪异的问题才有效。如果您在对它们进行排序时发现存储过程和查询的性能下降，那么您应该打开一个新的问题，并发布 CREATE TABLE 语句、查询/存储过程和 EXPLAIN 输出。然后我们可以帮助您优化查询或服务器配置。 - longneck

Answer 4

我会创建一个按照 PK 值排序的视图，这样你就不需要在 ALTER 进行时锁定那个巨大的表了。

- Darth Continent

---

谢谢回复...问题是我不介意在更新期间锁定表格，因为它将离线... - Timothy Mifsud

我不相信视图在这里会有帮助。MySQL 有两种策略用于视图解析：MERGE 和 TEMPTABLE。当使用MERGE时，由于它的定义仅与提交的 SELECT 语句合并，所以您不会获得任何好处。正如其名称所示，TEMPTABLE 将创建一个临时表。但从外观上看，创建临时表是原始问题的原因。因此，您不会获得任何好处，只会使维护更加困难。 - exhuma

Answer 5

如果您正在使用InnoDB，无论是在插入后还是在查询时，都不需要显式执行ORDER BY。根据MySQL 5.0手册，InnoDB已经默认为查询结果使用主键排序：

http://dev.mysql.com/doc/refman/5.0/en/alter-table.html#id4052480

默认情况下，MyISAM表按插入顺序返回记录，如果您只是向表中添加数据而不使用UPDATE查询来就地修改任何行，则此方法也可以起作用。

- rcoder