MySQL alter table 中 RENAME 比 DROP+ADD 更快
我正在执行一些 MySQL 表维护，这意味着删除一些冗余列并添加一些新列。
一些要删除的列与要添加的列具有相同的类型。如果我利用这一点并重用一些现有的列，该过程会更快吗？
我的理由是，更改列名应该是一个简单的表元数据更改，而删除和添加列意味着要么在文件末尾找到空间（碎片数据），要么使用正确的列重建每一行，以便它们处于相同位置放在磁盘上。
有问题的引擎是 MyISAM，我不知道它会如何处理这个问题，所以我想听听以前遇到过同样情况的人的意见！
I'm performing some MySQL table maintenance that will mean removing some redundant columns and adding some new ones.
Some of the columns to drop are of the same type as ones to add. Would the procedure be faster if I took advantage of this and reused some of the existing columns?
My rationale is that changing column names should be a simple table metadata change, whereas removing and adding columns means either finding room at the end of the file (fragmenting data) or rebuilding every row with the correct columns so that they're at the same place on the disk.
The engine in question is MyISAM and I'm not up to scratch on how exactly it'll treat this so I'd like to hear from anyone who has been in the same situation before!
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（4）
除非您有严重性能问题，否则我不会采用重命名方法 - 因为您将留下所有脏数据。
另外，通过删除表，您将导致重新构建所有索引 - 这是一个好主意，每隔一段时间......
马丁
Unless you have a serious issue with performance, I wouldn't take the renaming approach - because of all the dirty data you're going to leave lying around.
Also, by dropping the table, you will cause any indexes to get re-built - which is a good idea every once in a while...
Martin
我会删除这些列。无论哪种方式，你都会遇到碎片。这应该在您的定期维护计划中处理。您可以在进行大量修改操作后加速这些操作。
I would drop the columns. You will have fragmentation either way. That should be handled in your regular maintenance plans. You could accelerate those after a large number of modification operations.
如果您不知道，在Myisam表中，每个ALTER TABLE操作都会复制整个表，因此在服务器需要复制表时该表将被锁定。
If you don't know, in Myisam table, every ALTER TABLE operation will do a copy of entire table, thus the table will be locked for the time your server needs to copy the table.
我使用了相同的逻辑，但被刺痛了，因为即使进行了不需要重写表的更改（即表重命名），MySQL 错误也会导致它认为这是需要重写表的更改。
如果您正在处理的字段是日期、日期时间或时间戳字段，您很可能会遇到这种情况，这意味着您应该假设它必须进行完全重写并以这种方式进行计划。
I've used that same logic, and got stung because even with changes that are supposed to not require rewriting the table (i.e. a table rename), a MySQL bug caused it to think it was a change that required rewriting the table.
If the fields you are dealing with are date, datetime or timestamp fields, you are likely to be hit by this, which means that you should just assume it has to do a full rewrite and plan that way.