关于ALTER TABLE ADD COLUMN的性能问题

Question

我有一个大的MySQL InnoDB表，有500万行。我需要在表格中添加一列，该列的默认值为int。

这样做的最佳方法是什么？正常的alter table命令似乎花费了很多时间。有没有更好的方法来做到这一点？基本上我想知道是否有更快的方式或有效的方式来做到这一点。

如果表中有外键引用，除了alter table之外，还有其他办法可以做到这一点吗？

任何帮助表示赞赏。

Answer 1

我不会说这是一种更好的方式，但是...您可以为新数据创建一个单独的表并将其设置为与现有表的外键关系。这将是“快速”，但如果数据真的属于主表，并且每个（或大多数）现有记录都有一个值，那么您应该修改表并添加它。

Answer 2

假设表是这样的：

CREATE TABLE mytable 
(
    id INT NOT NULL AUTO_INCREMENT, 
    name VARCHAR(25), 
    PRIMARY KEY (id), 
    KEY name (name) 
); 

，并要与

ALTER TABLE mytable ADD COLUMN age INT NOT NULL DEFAULT 0; 

添加年龄栏，您可以分阶段如下执行ALTER TABLE：

CREATE TABLE mytablenew LIKE mytable; 
ALTER TABLE mytablenew ADD COLUMN age INT NOT NULL DEFAULT 0; 
INSERT INTO mytablenew SELECT id,name FROM mytable; 
ALTER TABLE mytable RENAME mytableold; 
ALTER TABLE mytablenew RENAME mytable; 
DROP TABLE mytableold; 

如果mytable使用MyISAM存储引擎并且具有非唯一的inde xes，再加两条线

CREATE TABLE mytablenew LIKE mytable; 
ALTER TABLE mytablenew ADD COLUMN address VARCHAR(50); 
ALTER TABLE mytablenew DISABLE KEYS; 
INSERT INTO mytablenew SELECT id,name FROM mytable; 
ALTER TABLE mytable RENAME mytableold; 
ALTER TABLE mytablenew RENAME mytable; 
DROP TABLE mytableold; 
ALTER TABLE mytable ENABLE KEYS; 

这会让你看到每个阶段需要多少秒钟。从这里，你可以决定一个简单的ALTER TABLE是否更好。

如果有外键引用，这种技术会变得有点血腥。

你的步骤将是

• SET UNIQUE_CHECKS = 0;
• SET FOREIGN_KEY_CHECKS = 0;
• 下降mytable外键引用。
• 在阶段执行ALTER TABLE
• 在mytable中创建外键引用。
• SET UNIQUE_CHECKS = 1;
• SET FOREIGN_KEY_CHECKS = 1;

试试看！