首页
学习
活动
专区
圈层
工具
发布
社区首页 >问答首页 >将列添加到大型表的MySQL性能

问将列添加到大型表的MySQL性能
EN

Stack Overflow用户
提问于 2014-07-09 17:44:50
回答 5查看 43K关注 0票数 24

我在Ubuntu13.10上安装了MySQL 5.5.37和带有apt的本地InnoDB。我的机器是i7-3770 +32 on内存+ SSD硬盘驱动器在我的桌面上。对于仅包含150万条记录的表"mytable“,以下DDL查询所需时间超过20分钟(!):

代码语言:javascript
复制
ALTER TABLE mytable ADD some_column CHAR(1) NOT NULL DEFAULT 'N';

有什么办法可以改进吗?我查过了

代码语言:javascript
复制
show processlist;

因为某种原因它在复制我的桌子。这是令人不安的不方便。有办法关掉这个副本吗?是否有其他方法可以提高将列添加到大型表的性能?

除此之外,我的DB相对较小,只有1.3Gb的转储大小。因此,它应该(在理论上) 100%地适合于记忆。

是否有可以帮助的设置?迁移到Precona会对我有什么改变吗?

加:我有

代码语言:javascript
复制
innodb_buffer_pool_size = 134217728
EN

回答 5

Stack Overflow用户

回答已采纳

发布于 2014-07-09 18:45:40

是否有其他方法可以提高将列添加到大型表的性能?

简短回答:不。您可以立即添加ENUM和SET值,还可以添加辅助索引,同时只锁定写操作,但更改表结构总是需要表副本。

很长的答案:你真正的问题不是性能,而是锁定时间。速度慢并不重要,重要的是其他客户端在您的ALTER完成之前不能执行查询。在这种情况下,有一些选择:

  1. 您可以使用Percona工具箱中的pt-在线-模式更改。先备份你的数据!这是最简单的解决方案,但可能不会在所有情况下都有效。
  2. 如果您不使用外键,而且速度慢,因为您有很多索引,那么创建表的副本(包含所需的更改,但不包含辅助索引)、用数据填充表、最后用一个alter创建所有索引可能会更快。
  3. 如果您很容易创建副本,比如托管在Amazon上,您可以创建一个主主副本,在那里运行alter,让它同步,并在完成后切换实例。

更新

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

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

代码语言:javascript
复制
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/

票数 39
EN

Stack Overflow用户

发布于 2017-11-20 16:33:18

MariaDb 10.3、MySQL 8.0和后面的其他MySQL变体都有“即时添加列”特性,大多数列(请参阅docs中的一些约束)可以立即添加,而不需要重新构建表。

票数 6
EN

Stack Overflow用户

发布于 2019-11-07 12:36:57

我知道这是一个很老的问题,但今天我遇到了一个类似的问题。我决定创建一个新表,并在新表中导入旧表。类似于:

代码语言:javascript
复制
CREATE TABLE New_mytable  LIKE mytable ;

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

insert into New_mytable  select * from mytable ;

然后

代码语言:javascript
复制
START TRANSACTION;
insert into New_mytable  select * from mytable where id > (Select max(id) from New_mytable) ;

RENAME TABLE mytable TO Old_mytable;

RENAME TABLE New_mytable TO mytable;
COMMIT;

这并不能使更新过程进行得更快,但它确实将停机时间降到最低。

希望这能有所帮助。

票数 5
EN
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接:

https://stackoverflow.com/questions/24660456

复制
相关文章

相似问题

领券
问题归档专栏文章快讯文章归档关键词归档开发者手册归档开发者手册 Section 归档