关于mysql的DDL操作（转载）（面试题）

最新推荐文章于 2023-12-22 10:04:58 发布

每天五点自然醒

最新推荐文章于 2023-12-22 10:04:58 发布

阅读量421

点赞数

分类专栏： mysql

原文链接：https://www.cnblogs.com/TeyGao/p/9089997.html

版权

mysql 专栏收录该内容

3 篇文章

订阅专栏

== 记录一下社招面试的简单原理问题吧==

转 https://www.cnblogs.com/TeyGao/p/9089997.html

面试题：对于mysql较大表增加字段的过程。

（当时脑子想的满是inplace的过程，从未看过5.5-5.7 mysql的DDL操作原理实际上有变化）

（1）mysql5.5

以前所有DDL操作都使用Copy Table的方式完成，操作过程中原表数据库不允许写入，只能读取

（2）mysql5.5

在MySQL 5.5版本中引入FIC（Fast index creation）特性。

FCI 操作流程：
（1）对表加共享S锁，允许其他会话读操作，但禁止写操作，
（2）根据当前表数据创建索引，
（3）新索引创建完成，解除S锁，允许读写。

FCI 优点：
（1）创建索引不需要拷贝整表数据，创建速度快，
（2）创建索引过程中，可以快速中止。

FCI限制：
（1）FCI特新仅限于复制索引，不试用于聚集索引，（主键不适用）
（2）索引创建期间，表只允许读不允许写。

（3）mysql5.6-mysql5.6.7版本前

DDL操作主要有copy和inplace两种方式，两种方式全程都需要锁表禁止写操作，允许部分时间段的读操作，inplace方式仅支持添加和删除索引两种方式。

copy方式：

(1)新建带索引的临时表
(2)锁原表，禁止DML，允许查询
(3)将原表数据拷贝到临时表(无排序，一行一行拷贝)
(4)进行rename，升级字典锁，禁止读写
(5)完成创建索引操作

inplace方式：

(1)新建索引的数据字典
(2)锁表，禁止DML，允许查询
(3)读取聚集索引，构造新的索引项，排序并插入新索引
(4)等待打开当前表的所有只读事务提交
(5)创建索引结束

（4）

在MySQL 5.6.7版本后，引入了row_log来记录DDL期间写操作所产生的日志，因此除DDL操作开始和结束的两小段时间需要对表持EXCLUSIVE-MDL锁（原数据锁）禁止读写外，其余DDL操作阶段允许其他回话对表进行读写，因此可算作ONLINE DDL。

对于ONLINE DDL操作，同样包含copy和inplace方式，而对于inplace方式，又可以细分为rebuild方式和no-rebuild方式，rebuild方式指需要重新组织记录的操作如添加删除列或交换列顺序等操作，而no-rebuild方式指不会导致记录格式发生变化的操作如删除和添加索引。

ONLINE DDL可分为三个阶段操作：

Prepare阶段：
1.创建新的临时frm文件
2.持有EXCLUSIVE-MDL锁，禁止读写
3.根据alter类型，确定执行方式(copy,online-rebuild,online-norebuild)
4.更新数据字典的内存对象
5.分配row_log对象记录增量
6.生成新的临时ibd文件

ddl执行阶段：
1.降级EXCLUSIVE-MDL锁，允许读写
2.扫描old_table的聚集索引每一条记录rec
3.遍历新表的聚集索引和二级索引，逐一处理
4.根据rec构造对应的索引项
6.将构造索引项插入sort_buffer块
6.将sort_buffer块插入新的索引
7.处理ddl执行过程中产生的增量(仅rebuild类型需要)

commit阶段
1.升级到EXCLUSIVE-MDL锁，禁止读写
2.重做最后row_log中最后一部分增量
3.更新innodb的数据字典表
4.提交事务(刷事务的redo日志)
5.修改统计信息
6.rename临时idb文件，frm文件
7.变更完成  

Online DDL期间产生Row Log会按照Block来存放和处理，回放Row Log时按照Block来处理，一个Block回放完后处理下一个Block，只有到达最后一个Block时才会锁表，保证最后一个Block完成后新数据和老数据保持一致，因此Online DDL期间产生大量Row Log不会导致表被长时间锁定。

仅需要修改元数据的DDL操作：

（1）设置列默认值
（2）设置自增列的自增值
（3）删除索引

可以采用Online no-rebuild方式的DDL操作：

（1）添加索引

可以采用Online rebuild方式的DDL操作：

（1）添加列
（2）删除列
（3）交换列顺序
（4）修改列NULL-NOTNULL属性
（5）修改表ROW-FORMAT
（6）添加修改主键

只能采用Copy方式的DDL操作：

（1）修改列类型
（2）转换字符集
（3）Optimize table
（4）删除主键

 PS: 从MySQL 5.6.17版本后，Optimize table可以采用Inplace方式操作。