mysql 存储过程执行ddl 关于mysql的DDL操作（转载）（面试题）

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

转

面试题：对于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的聚集索引每一条记录rec3.遍历新表的聚集索引和二级索引，逐一处理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方式操作。