今天做了一个测试,在表插入数据的时候进行主键的修改,表结构如下:
sbtest1 | CREATE TABLE sbtest1
(
id
int(11) NOT NULL AUTO_INCREMENT,
k
int(11) NOT NULL DEFAULT ‘0’,
c
char(120) NOT NULL DEFAULT ”,
pad
char(60) NOT NULL DEFAULT ”,
PRIMARY KEY (id
)
) ENGINE=InnoDB AUTO_INCREMENT=1026817 DEFAULT CHARSET=utf8
对这个表插入数据,同时删除id上的主键,然后在c列上创建新的主键。
mysql> alter table sbtest1 modify id int default 0,drop primary key;
Query OK, 1069600 rows affected (7.54 sec)
Records: 1069600 Duplicates: 0 Warnings: 0
看到拷贝了表,在继续插入数据,同时在c上创建新的主键。
mysql> alter table sbtest1 add primary key(c);
Query OK, 0 rows affected (17 min 14.94 sec)
Records: 0 Duplicates: 0 Warnings: 0
在添加新的主键的时候,看到耗时非常长
| 19 | root | localhost | baixyu | Query | 452 | altering table | alter table sbtest1 add primary key(c) |
| 21 | root | localhost | performance_schema | Sleep | 12 | | NULL |
| 23 | root | 127.0.0.1:28103 | baixyu | Query | 406 | Waiting for table metadata lock | CREATE INDEX k_1 ON sbtest1(k) |
| 24 | root | localhost | NULL | Query | 0 | starting | show processlist
阻塞了对表上创建索引的操作
-rw-r—–. 1 my8888 mysql 8632 Apr 8 18:06 sbtest1.frm
-rw-r—–. 1 my8888 mysql 1195376640 Apr 8 18:08 sbtest1.ibd
-rw-r—–. 1 my8888 mysql 8632 Apr 8 18:07 #sql-856_13.frm
-rw-r—–. 1 my8888 mysql 1581252608 Apr 8 18:19 #sql-ib1348-2147087465.ibd
看到临时表占用额空间也很大,有源表的1.5倍
另外如何监控alter table的过程,参考https://dev.mysql.com/doc/refman/5.7/en/monitor-alter-table-performance-schema.html