MySQL ALTER TABLE 在非常大的表上 - 运行它安全吗？
我有一个 MySQL 数据库，其中有一个包含 400 万行的 MyISAM 表。我大约每周更新一次此表，添加大约 2000 个新行。更新后，我会像这样更改表：
ALTER TABLE x ORDER BY PK DESC
我按主键字段按降序对表进行排序。这在我的开发机器（具有 3GB 内存的 Windows）上没有给我带来任何问题。我在生产 Linux 服务器上成功尝试了 3 次（具有 512MB RAM - 每次大约 6 分钟内获得结果排序表），最后一次尝试时，我不得不在大约 30 分钟后停止查询并重建来自备份的数据库。
512MB 的服务器可以处理这么大的表上的 alter 语句吗？我读到创建临时表来执行 ALTER TABLE 命令。
问题：这个alter命令可以安全运行吗？预计更改表格的时间应该是多少？
I have a MySQL database with a MyISAM table with 4 million rows. I update this table about once a week with about 2000 new rows. After updating, I then alter the table like this:
ALTER TABLE x ORDER BY PK DESC
I order the table by the primary key field in descending order. This has not given me any problems on my development machine (Windows with 3GB memory). Three times I have tried it successfully on the production Linux server (with 512MB RAM - and achieving the resulted sorted table in about 6 minutes each time), the last time I tried it I had to stop the query after about 30 minutes and rebuild the database from a backup.
Can a 512MB server cope with that alter statement on such a large table? I have read that a temporary table is created to perform the ALTER TABLE command.
Question: Can this alter command be safely run? What should be the expected time for the alteration of the table?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（5）
正如我刚刚读到的，
ALTER TABLE ... ORDER BY ...查询对于提高某些情况下的性能很有用。令我惊讶的是 PK 指数对此没有帮助。但是，从 MySQL 文档来看，似乎InnoDB 确实使用索引。然而，InnoDB 往往比 MyISAM 慢。也就是说，使用 InnoDB，您不需要重新排序表，但您会失去 MyISAM 的惊人速度。它仍然值得一试。从您解释问题的方式来看，似乎加载到内存中的数据太多（也许甚至正在进行交换？）。您可以通过监视内存使用情况轻松检查这一点。很难说，因为我不太了解 MySQL。
另一方面，我认为你的问题出在一个非常不同的地方：你正在使用一台只有 512 Megs RAM 的机器作为数据库服务器，其中一个表包含超过 4Mio 行......并且你正在执行一个非常占用内存的操作对该机器上的整个表进行操作。看来 512Meg 还远远不够。
我在这里看到一个更基本的问题：您正在一个与生产环境非常不同的环境中进行开发（并且很可能也进行测试）。您所解释的问题类型是可以预料的。您的开发机器的内存是生产机器的六倍。我相信我可以有把握地说，处理器也更快。在这种情况下，我建议您创建一个模仿您的生产站点的虚拟机。这样您就可以轻松测试您的项目，而无需中断生产站点。
As I have just read, the
ALTER TABLE ... ORDER BY ...query is useful to improve performance in certain scenarios. I am surprised that the PK Index does not help with this. But, from the MySQL docs, it seems that InnoDB does use the index. However InnoDB tends to be slower as MyISAM. That said, with InnoDB you wouldn't need to re-order the table but you would lose the blazing speed of MyISAM. It still may be worth a shot.The way you explain the problems, it seems that there is too much data loaded into memory (maybe there is even swapping going on?). You could easily check that with monitoring your memory usage. It's hard to say as I do not know MySQL all that well.
On the other hand, I think your problem lies at a very different place: You are using a machine with only 512 Megs of RAM as Database server with a table containing more than 4Mio rows... And you are performing a very memory-heavy operation on the whole table on that machine. It seems that 512Megs will not nearly be enough for that.
A much more fundamental issue I am seeing here: You are doing development (and quite likely testing as well) in an environment that is very different to the production environment. The kind of problem you are explaining is to be expected. Your development machine has six times as much memory as your production machine. I believe I can safely say, that the processor is much faster as well. In that case, I suggest you create a virtual machine mimicking your production site. That way you can easily test your project without disrupting the production site.
你要求它做的是重建整个表及其所有索引；这是一个昂贵的操作，特别是当数据不适合内存时。它会完成，但如果数据不适合内存，它会慢得多，特别是如果你有很多索引。
当选择在生产中运行内存如此小的机器时，我质疑您的判断。无论如何：
您可能还可以进行一些调整来尝试提供帮助；它很大程度上取决于您的架构（特别是索引）。 4M 行并不是很多（对于具有正常内存量的机器）。
What you're asking it to do is rebuild the entire table and all its indexes; this is an expensive operation particularly if the data doesn't fit in ram. It will complete, but it will be vastly slower if the data doesn't fit in ram, particularly if you have lots of indexes.
I question your judgement when choosing to run a machine with such tiny memory in production. Anyway:
There is probably also some tuning you can do to try to help; it largely depends on your schema (indexes in particular). 4M rows is not very many (for a machine with normal amounts of ram).
主键是自增吗？如果是这样，那么执行 ALTER TABLE ... ORDER BY 不会改善任何事情，因为无论如何所有内容都会按顺序插入。
（除非你有很多删除）
is the primary key auto_increment? if so, then doing ALTER TABLE ... ORDER BY isn't going to improve anything since everything will be inserted in order anyway.
(unless you have lots of deletes)
我可能会创建一个按 PK 值排序的视图，这样一来，您就不需要在执行 ALTER 时锁定那个巨大的表。
I'd probably create a View instead which is ordered by the PK value, so that for one thing you don't need to lock up that huge table while the ALTER is being performed.
如果您使用的是 InnoDB，则不必在插入后或查询时显式执行
ORDER BY。根据MySQL 5.0手册，InnoDB已经默认对查询结果进行主键排序：http://dev.mysql.com/doc/refman/5.0/en/alter-table.html#id4052480
MyISAM 表默认按插入顺序返回记录，这可能有效如果您只追加到表中，而不是使用 UPDATE 查询就地修改任何行，也是如此。
If you're using InnoDB, you shouldn't have to explicitly perform the
ORDER BYeither post-insert or at query time. According to the MySQL 5.0 manual, InnoDB already defaults to primary key ordering for query results:http://dev.mysql.com/doc/refman/5.0/en/alter-table.html#id4052480
MyISAM tables return records in insertion order by default, instead, which may work as well if you only ever append to the table, rather than using an
UPDATEquery to modify any rows in-place.