MySQL 5.7: 新增的online DDL操作

(这是个汇总文章,后续有新的Online DDL操作 会不断更新…)

1. 支持ALTER TABLE t1 RENAME INDEX idx1 to idx2;

(Server层)语法支持的worklog: http://dev.mysql.com/worklog/task/?id=6555
(Innodb层)Online DDL的worklog: http://dev.mysql.com/worklog/task/?id=6752

新增了语法支持RENAME INDEX操作:

mysql> create table t1 (a int, b int, index x1(a));
Query OK, 0 rows affected (0.00 sec)

mysql> alter table t1 rename index x1 to x2;
Query OK, 0 rows affected (0.00 sec)
Records: 0 Duplicates: 0 Warnings: 0

主要包含三个过程:
1. Renaming in InnoDB data dictionary (SYS_INDEXES)


2. Renaming in InnoDB data dictionary cache (the dict_table_t/dict_index_t
objects)
3. Renaming in InnoDB persistent stats storage

ref patch: http://bazaar.launchpad.net/~mysql/mysql-server/5.7/revision/5116

WL#6752, WL#6555

2. 支持在线增加VARCHAR列的长度 (反之,如果缩小列长度,则只支持copy ALGORITHM)
worklog: http://dev.mysql.com/worklog/task/?id=6554
目前只支持在255字节内的长度使用IN-PLACE算法,原因是超过255字节,innodb层存储数据表示列长度的部分就得多扩展一个字节。
不支持IN-PLACE 减小字段长度:
在满足上述情况下,直接更新Innodb 系统表SYS_COLUMNS即可。

mysql> create table t1 (a varchar(100));
Query OK, 0 rows affected (0.00 sec)

mysql> alter table t1 ALGORITHM=INPLACE, CHANGE COLUMN a a varchar(5);
ERROR 1846 (0A000): ALGORITHM=INPLACE is not supported. Reason: Cannot change column type INPLACE. Try ALGORITHM=COPY.

mysql> alter table t1 ALGORITHM=INPLACE, CHANGE COLUMN a a varchar(200);
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0

mysql> alter table t1 ALGORITHM=INPLACE, CHANGE COLUMN a a varchar(256);
ERROR 1846 (0A000): ALGORITHM=INPLACE is not supported. Reason: Cannot change column type INPLACE. Try ALGORITHM=COPY.

ref patch:http://bazaar.launchpad.net/~mysql/mysql-server/5.7/revision/5107.1.230

WL#6554

原创文章,转载请注明: 转载自Simple Life

本文链接地址: MySQL 5.7: 新增的online DDL操作

Post Footer automatically generated by wp-posturl plugin for wordpress.


Comments

Leave a Reply

Your email address will not be published. Name and email are required


Current month ye@r day *