mysql 锁24小时_MySQL中Alter table 不长时间锁表的情况汇总。

查看原文：http://www.tanbo.name/html/99669.html

前言：

MySQL

的大表运维总是令人头疼的一件事，特别是大表表结构的修改尤为困难。

首先，alter table 的process不可被kill ，

一旦执行就不可回退。

其次，大多数的alter table操作都会涉及 lock --- copy to

new table --- rename --- unlock的过程，锁表时间会很长。

本文不是讨论如何进行大表表结构变更， 而是汇总一些不涉及copy to new

table这一步的alter table情况。

这些情况下，mysql会直接修改frm文件，而lock的时间也仅是秒级的。

MySQL 5.0 系列  (即5.0.x 版本)

如果你是这个版本，很遗憾所有alter table 操作都会进行temp table

copy。以下是摘自官方文档的解释：

If you use any option to ALTER

TABLE other

than RENAME, MySQL always creates a

temporary table, even if the data wouldn't strictly

need to be copied (such as when you change the name of a

column).

MySQL 5.1 系列  (即5.1.x 版本)

以下操作不会有copy temp table操作，即锁表时间较短。

1. ALTER

TABLE tbl_name RENAME

TO new_tbl_name

2. 不涉及数据修改的操作

2.1 列改名 (除了innodb)

2.2 修改默认值 (注意：必须使用 modify

，而不能使用change)

2.3 增加ENUM的枚举定义

(注意：仅当新增枚举在当前允许最大值內，例：1B 可存8个枚举，2B可存128个枚举)

3. 通过add

partition 添加分区

4.

重命名索引

5. 添加删除索引 (仅

innodb plugin支持)

详见官方文档说明：

For ALTER TABLE tbl_name RENAME

TO new_tbl_name without

any other options, MySQL simply renames any files that correspond

to the table tbl_name without

making a copy. (You can also use the RENAME TABLEstatement to rename

tables. See Section 13.1.33,

“RENAME

TABLE Syntax”.) Any

privileges granted specifically for the renamed table are not

migrated to the new name. They must be changed manually.

Alterations that modify only table

metadata and not table data can be made immediately by altering the

table's.frm file and not

touching table contents. The following changes are fast alterations

that can be made this way:

Renaming a column, except for

the InnoDB storage

engine.

Changing the default value of a column

(except for NDB tables;

see

Changing the definition of

an ENUM or SET column by

adding new enumeration or set members to

the end of

the list of valid member values, as long as the storage side of the

data type does not change. For example, adding a member to

a SET column that

has 8 members changes the required storage per value from 1 byte to

2 bytes; this will require a table copy. Adding members in the

middle of the list causes renumbering of existing members, which

requires a table copy.

ALTER

TABLE ... ADD PARTITION creates no

temporary table except when used with NDB tables. ADD or DROPoperations

for RANGE or LIST partitions

are immediate operations or nearly

so. ADD or COALESCE operations

forHASH or KEY partitions

copy data between changed partitions;

unless LINEAR

HASH or LINEAR KEY was

used, this is much the same as creating a new table (although the

operation is done partition by

partition). REORGANIZEoperations copy only

changed partitions and do not touch unchanged ones.

Renaming an index, except

for

Adding or dropping an index,

for InnoDB (if InnoDB

Plugin is used)

and