mysql online index_mysql5.6 online ddl—索引

尝试对mysiam表(1500万)删除索引失败

#uk表字段类型比较简单，都是int/tinyint/timestamp类型。

CREATE TABLE `uk` (

`id` int(11) NOT NULL AUTO_INCREMENT,

`a` int(11) NOT NULL DEFAULT '0',

`b` int(11) NOT NULL,

`c` int(11) NOT NULL DEFAULT '0',

`d` int(11) NOT NULL DEFAULT '0',

`e` int(11) DEFAULT '0',

`f` tinyint(4) NOT NULL DEFAULT '1',

`l` timestamp NOT NULL DEFAULT ‘0’ ,

PRIMARY KEY (`id`),

UNIQUE KEY `idx_a_b_c` (`a`,`b`,`c`),

KEY `idx_f` (`f`)

) ENGINE=MyISAM AUTO_INCREMENT=15000000 DEFAULT CHARSET=utf8;

#尝试允许并发删除索引

alter table uk drop index idx_a_b_c , ALGORITHM=INPLACE/LOCK=NONE;

ERROR 1845 (0A000): ALGORITHM=INPLACE is not supported for this operation. Try ALGORITHM=COPY.

ERROR 1845 (0A000): LOCK=NONE is not supported for this operation. Try LOCK=SHARED.

#直接对myisam表进行DDL操作

alter table uk drop index idx_a_b_c;

#耗时2 min 43.27 sec(普通磁盘)

alter table uk add index idx_a_b(a,b);

#耗时2 min 43.27 sec(普通磁盘)

alter table uk add index idx_a_b(a,b);

Query OK,  rows affected (2 min 46.98 sec)

#修改引擎为innodb,过程很慢

alter table uk engine=innodb;

21 min 45.81 sec

#尝试删除索引

alter table uk drop index idx_a_b_c, ALGORITHM=INPLACE;

Query OK, 0 rows affected (0.14 sec)

#尝试重建索引

alter table uk add index idx_a_b(a,b), ALGORITHM=INPLACE;

Query OK, 0 rows affected (1 min 58.82 sec)

Records: 0  Duplicates: 0  Warnings: 0

2016-11-29