MySQL在大表中添加列的性能

我在本地安装了InnoDB的MySQL 5.5.37和Ubuntu 13.10上的apt-get.我的机器是台式机上的i7-3770 + 32Gb内存+ SSD硬盘.对于仅包含150万条记录的表"mytable",以下DDL查询需要超过20分钟(!):

ALTER TABLE mytable ADD some_column CHAR(1) NOT NULL DEFAULT 'N';

有没有办法改善它？我检查了

show processlist;

它显示它出于某种原因正在复制我的表格.令人不安的是令人不安的.有没有办法关掉这个副本？是否有其他方法可以提高向大表添加列的性能？

除此之外,我的数据库相对较小,只有1.3Gb转储大小.因此它(理论上)应该100%适合记忆.

有设置可以帮助吗？迁移到Precona会改变一切吗？

补充:我有

innodb_buffer_pool_size = 134217728

Pedro Wernec.. 17

是否有其他方法可以提高向大表添加列的性能？

简答:不.您可以立即添加ENUM和SET值,并且可以在仅为写入锁定时添加二级索引,但是更改表结构始终需要表副本.

答案很长:你真正的问题不是真正的表现,而是锁定时间.如果它很慢并不重要,只有在ALTER TABLE完成之后其他客户端才能执行查询.在这种情况下有一些选择:

您可以使用Percona工具包中的pt-online-schema-change.先备份您的数据!这是最简单的解决方案,但可能无法在所有情况下使用.

如果您不使用外键并且因为您有很多索引而速度很慢,那么使用您需要的更改创建表的副本可能会更快,但没有二级索引,用数据填充它,并创建所有索引最后都有一个alter table.

如果你很容易创建副本,就像你在Amazon RDS上托管一样,你可以创建一个主 - 主副本,在那里运行alter table,让它恢复同步,并在完成后切换实例.

UPDATE

正如其他人提到的,MySQL 8.0 INNODB增加了对即时列添加的支持.它不是一个神奇的解决方案,它有局限性和副作用 - 它只能是最后一列,表格不能有全文索引等 - 但在许多情况下应该有所帮助.

您可以指定显式ALGORITHM=INSTANT LOCK=NONE参数,如果无法立即更改模式,MySQL将失败并返回错误,而不是回退到INPLACE或COPY.例:

ALTER TABLE mytable
ADD COLUMN mycolumn varchar(36) DEFAULT NULL,
ALGORITHM=INPLACE, LOCK=NONE;

https://mysqlserverteam.com/mysql-8-0-innodb-now-supports-instant-add-column/