MySQL Online DDL这个新特性是在MySQL5.6.7开始支持的,更早期版本的MySQL进行DDL对于DBA来说是非常痛苦的。现在主流版本都集中在5.6与5.7,为了更好的理解Online DDL的工作原理与机制,本文就对Online DDL的实现方式进行总结。
本文使用的MySQL版本为官方社区版 5.7.24
。
(root@localhost) [test] > select version();
+------------+
| version() |
+------------+
| 5.7.24-log |
+------------+
1 row in set (0.00 sec)
主要说明
Online DDL这个新特性解决了早期版本MySQL进行DDL操作同时带来锁表的问题,在DDL执行的过程当中依然可以保证读写状态,不影响数据库对外提供服务,大大提高了数据库和表维护的效率。
- 早期实现方式(MySQL5.6.7之前版本)
早期版本MySQL执行DDL语句时主要通过以下方式进行:
COPY方式:
这是InnoDB最早期支持的方式,主要实现步骤:
- 创建与原表结构定义一致的临时表;
- 对原表加锁,不允许执行DML,但允许查询;
- 在临时表上执行DDL语句;
- 逐行拷贝原表数据到临时表;
- 原表与临时表进行RENAME操作,此时会升级原表上的锁,不允许读写,直至完成DDL操作;
INPLACE方式:
INPLACE方式也称为InnoDB fast index creation,是MySQL5.5及之后版本为了提高创建二级索引效率的方式,所以INPLACE方式仅限于二级索引的创建跟删除,关于fast index creation可以参考官方文档:InnoDB fast index creation,主要实现步骤:
- 创建临时的frm文件;
- 对原表加锁,不允许执行DML,但允许查询;
- 根据聚集索引的顺序,构造新的索引项,按照顺序插入新索引页;
- 升级原表上的锁,不允许读写操作;
- 进行RENAME操作,替换原表的frm文件,完成DDL操作。
相对于COPY方式,INPLACE方式在原表上进行,不会生成临时表,也不会拷贝原表数据,减少了很多系统I/O资源占用,但还是无法进行DML操作,也只适用于索引的创建与删除,并不适用于其他类型的DDL语句。
- 当前实现方式(MySQL5.6.7及之后版本)
在MySQL5.6.7及之后版本中推出了新的特性:
Online DDL方式:
Online DDL特性是基于MySQL5.5的InnoDB fast index creation上改进增强的。Online DDL同样包含两种方式:
- COPY方式;
- INPLACE方式。
其中,某些DDL语句不支持Online DDL的就采用COPY方式,支持Online DDL的则采用INPLACE方式,因为Online DDL是对早期INPLACE方式的增加,所以INPLACE方式根据是否涉及到记录格式的修改又分为如下两种情形:
- Rebuilds Table;
- No-Rebuilds Table。
Rebuilds Table操作是因为DDL有涉及到行记录格格式的修改,如字段的增、删、类型修改等;
No-Rebuilds Table则不涉及行记录格式的修改,如索引删除、字段名修改等。
Online DDL选项
ALGORITHM={COPY|INPLACE}
指定DDL执行时对表的操作方式。首选是INPLACE,但并非所有的语句都支持INPLACE,需要根据DDL语句类型决定。- LOCK={NONE|SHARED|DEFAULT|EXCLUSIVE}
指定DDL执行时对表锁定方式。默认情况下MySQL在表执行DDL时会尽量使用最少的锁定,LOCK选项可以为DDL语句指定执行更为严格的锁定方式,一旦指定的锁级别低于DDL语句执行所需的锁级别,则DDL语句会执行失败。- NONE:允许并发查询和DML操作;
- SHARED:允许并发查询,但不允许DML操作;
- DEFAULT:允许尽可能多的并发查询或DML操作(或两者都允许),没指定LOCK选项默认就为DEFAULT;
- EXCLUSIVE:不允许并发查询和DML操作。
Online DDL类型
根据官方文档Online DDL Operations的描述,结合常见的表DDL执行语句,MySQL5.7支持的Online DDL操作类型主要有以下种类:
- 索引操作
- 字段操作
- 组合字段操作
- 主键操作
- 外键操作
- 表操作
- 表分区操作
- 表空间操作
索引操作
索引操作类型如下表所示:
操作(Operation) | 原表操作(In Place) | 重建表操作(Rebuilds Table) | 允许并发DML操作(Permits Concurrent DML) | 仅修改元数据(Only Modifies Metadata) |
---|---|---|---|---|
创建添加二级索引 | YES | NO | YES | NO |
重命名索引 | YES | NO | YES | YES |
删除索引 | YES | NO | YES | YES |
创建全文索引 | YES | NO | NO | NO |
创建空间索引 | YES | NO | NO | NO |
修改索引类别 | YES | NO | YES | YES |
由以上表格可以看出涉及索引的DDL操作都可以使用INPLACE方式来完成,除了创建全文索引与空间索引之外都允许DML操作,并不会锁表。
-- 创建添加二级索引
create index index_name on table_name (column[,column]..);
或
alter table table_name add index index_name (column[,column]..);
-- 删除索引
drop index index_name on table_name;
或
alter table table_name drop index index_name;
-- 重命名索引
alter table table_name rename index old_index_name to new_index_name, algorithm=inplace, lock=none;
-- 创建全文索引
create fulltext index index_name on table_name(column[,column]..);
-- 创建空间索引
create table geom (g geometry not null);
alter table geom add spatial index(g), algorithm=inplace, lock=none;
-- 修改索引类型
alter table table_name drop index index_name, add index index_name(column[,column]..) using btree, algorithm=inplace;
字段操作
字段操作类型如下表所示:
操作(Operation) | 原表操作(In Place) | 重建表操作(Rebuilds Table) | 允许并发DML操作(Permits Concurrent DML) | 仅修改元数据(Only Modifies Metadata) |
---|---|---|---|---|
添加字段 | YES | YES | YES | NO |
删除字段 | YES |
首页 上一页 1 2 3 4 5 下一页 尾页 1/5/5 | |
【大 中 小】【打印】 【繁体】【投稿】【收藏】 【推荐】【举报】【评论】 【关闭】 【返回顶部】 | |
上一篇:[20190219]那个更快(11g).txt | 下一篇:WARNING OGG-01519 |