在MySQL中插入新列花费的时间太长 [英] Inserting New Column in MYSQL taking too long

问题描述

我们有一个庞大的数据库，插入新列需要花费太长时间.还是要加快速度吗?

We have a huge database and inserting a new column is taking too long. Anyway to speed up things?

推荐答案

不幸的是，您可能无能为力.插入新列时，MySQL会复制该表并将新数据插入该表中.您可能发现它做起来更快

Unfortunately, there's probably not much you can do. When inserting a new column, MySQL makes a copy of the table and inserts the new data there. You may find it faster to do

CREATE TABLE new_table LIKE old_table;
ALTER TABLE new_table ADD COLUMN (column definition);
INSERT INTO new_table(old columns) SELECT * FROM old_table;
RENAME table old_table TO tmp, new_table TO old_table;
DROP TABLE tmp;

这不是我的经验，但我听说其他人已经取得了成功.您也可以尝试在插入前禁用new_table上的索引，然后稍后重新启用.请注意，在这种情况下，您需要注意不要丢失过渡期间可能插入old_table的任何数据.

This hasn't been my experience, but I've heard others have had success. You could also try disabling indices on new_table before the insert and re-enabling later. Note that in this case, you need to be careful not to lose any data which may be inserted into old_table during the transition.

或者，如果您的担忧在更改过程中影响了用户，请查看 pt-online-schema-change ，它巧妙地使用触发器来执行ALTER TABLE语句，同时使表被修改. (请注意，但这不会加快该过程.)

Alternatively, if your concern is impacting users during the change, check out pt-online-schema-change which makes clever use of triggers to execute ALTER TABLE statements while keeping the table being modified available. (Note that this won't speed up the process however.)

这篇关于在MySQL中插入新列花费的时间太长的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！