[MySQL] 用Hacking方法快速alter table

博客首页 » MySQL 用Hacking方法快速alter table

发布于 11 Sep 2015 08:45
标签 blog
我们知道MySQL的alter table add column之类的操作,是超级慢,因为MySQL是copy表修改。在percona上,找到一个用Hacking方式快速alter table的方法。

如果是正常的方法,只能用ALTER TABLE…DISABLE KEYS让MySQL停止更新MyISAM表中的非唯一索引。
然后使用ALTER TABLE … ENABLE KEYS重构无效的索引。

这次介绍的Hacking步骤是:
1 用DDL生成一个空的有新结构的新表(如果是我,用create table … like …; 然后 alter table … add column …; 会更舒服些)
2 flush table with read lock
3 在OS上互换.frm文件
4 unlock tables

需要注意的是,这对于带有auto_increment的表是有问题的。另外,在生产环境上用的话,一定要备份DB。

另外,希望MySQL尽快加上非重新构造表完成alter table add/change column的功能。

-- 1.
mysql> CREATE TABLE `huge_table_new` (
->   `id` int(6) NOT NULL,
->   `text` text NOT NULL,
->   PRIMARY KEY  (`id`)
-> ) ENGINE=INNODB;
Query OK, 0 rows affected (0.01 sec)
 
-- 2.
mysql> FLUSH TABLES WITH READ LOCK;
Query OK, 0 rows affected (0.00 sec)
 
-- 3.
mysql> ^Z
[1]+  Stopped                 mysql test
 
# mv huge_table.frm huge_table_old.frm && mv huge_table_new.frm huge_table.frm && mv huge_table_old.frm huge_table_new.frm
 
# fg
mysql test
 
-- 4.
mysql> UNLOCK TABLES;
Query OK, 0 rows affected (0.00 sec)
mysql> SHOW CREATE TABLE `huge_table`\G
*************************** 1. row ***************************
Table: huge_table
Create Table: CREATE TABLE `huge_table` (
  `id` int(6) NOT NULL,
  `text` text NOT NULL,
  PRIMARY KEY  (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)
mysql> INSERT INTO `huge_table` (text) VALUES ('test');
ERROR 1062 (23000): Duplicate entry '0' for key 1

Suppose you want to remove auto_increment from 100G table. No matter if it’s InnoDB or MyISAM, you’d usually ALTER TABLE huge_table CHANGE id id int(6) NOT NULL and then wait hours for table rebuild to complete. If you’re unlucky i.e. you have a lot of indexes and not too much RAM – you could end up waiting days. If you want to make this happen quick – there’s another way. Not documented, but works well with both – InnoDB and MyISAM.

Now that more and more folks hit the InnoDB auto-inc scalability issue with MySQL 5.0 and older versions, employing other techniques to maintain the PK auto incremental becomes more of an issue. One of the steps here is to remove current PK auto_incremental from the table. As a rule of thumb, this usually involves altering huge InnoDB tables and huge tables take time to rebuild.

Disclaimer: try this at your own risk. It worked for me, it may work for you too, but always have a backup before doing that kind of stuff, as it is not the way MySQL would advice you to do it and we can’t guarantee it will work well for you either.

So, in a nutshell, all you have to do is create another table with desired table structure and switch .frm table definition files. For safety, I’d recommend to flush tables with read lock while switching .frm files. When and how it works:

auto_increment (removing). Let’s have a simple table with auto_increment we want to get rid of:

CREATE TABLE `huge_table` (
`id` int(6) NOT NULL auto_increment,
`text` text NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB

To remove auto_increment, we (1) create table with the same layout but without auto_increment, (2) flush tables with read lock, (3) swap .frm files while keeping mysql suspended and (4) unlock the tables afterwards:

— 1.
mysql> CREATE TABLE `huge_table_new` (
-> `id` int(6) NOT NULL,
-> `text` text NOT NULL,
-> PRIMARY KEY (`id`)
-> ) ENGINE=INNODB;
Query OK, 0 rows affected (0.01 sec)

— 2.
mysql> FLUSH TABLES WITH READ LOCK;
Query OK, 0 rows affected (0.00 sec)

— 3.
mysql> ^Z
[1]+ Stopped mysql test

  1. mv huge_table.frm huge_table_old.frm && mv huge_table_new.frm huge_table.frm && mv huge_table_old.frm huge_table_new.frm
  1. fg

mysql test

— 4.
mysql> UNLOCK TABLES;
Query OK, 0 rows affected (0.00 sec)
mysql> SHOW CREATE TABLE ‘huge_table`\G
* 1. row *
Table: huge_table
Create Table: CREATE TABLE `huge_table` (
`id` int(6) NOT NULL,
`text` text NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)
mysql> INSERT INTO `huge_table` (text) VALUES (’test');
ERROR 1062 (23000): Duplicate entry '0' for key 1

Unfortunately, adding auto_increment does not work that way.

Enum values (add and remove). Enumerated values are added and removed the same way that auto_increment is removed. I’ve been a bit surprised, that removing value from enum() works as good as adding it – rows that have incorrect values are just returned as empty. But I suppose this does violate mysql data file structure, so be really careful with that one.

Default values. MySQL rebuilds table even if we only want to change the default value for new records so this may save one from a lot of trouble.

Table comment. I’m pretty sure that would work for changing table comment as well, however – changing a comment with a help of ALTER TABLE does not rebuild the table, so we better use the documented method for that.

What I’m surprised about is that changing a comment does not to require table to be rebuilt, while things like removing auto_increment or changing a default value still do even though this information is stored in table definition file.

If you’ll ever try this, please leave a comment if it did work for you. Maybe you have discovered some new things to alter that way?

https://www.percona.com/blog/2007/10/29/hacking-to-make-alter-table-online-for-certain-changes/


本页面的文字允许在知识共享 署名-相同方式共享 3.0协议和GNU自由文档许可证下修改和再使用,仅有一个特殊要求,请用链接方式注明文章引用出处及作者。请协助维护作者合法权益。


系列文章

文章列表

  • MySQL 用Hacking方法快速alter table

这篇文章对你有帮助吗,投个票吧?

rating: 0+x

留下你的评论

Add a New Comment