MySql online DDL 原理

布满荆棘的人生 2022-08-19 05:54 141阅读 0赞

背景

dba的日常工作肯定有一项是[ddl][]变更，ddl变更会锁表，这个可以说是dba心中永远的痛，特别是执行ddl变更，导致库上大量[线程][Link 1]处于“Waiting for meta data lock”状态的时候。因此[mysql][] 5.6的online ddl特性是dba们最期待的新特性，这个特性解决了执行ddl锁表的问题，保证了在进行表变更时，不会堵塞线上业务读写，保障在变更时，库依然能正常对外提供访问。网上关于online ddl的文章很多，但涉及原理的很少，都是介绍语法之类的，本文将详细介绍online ddl的原理，知其然，更要知其所以然。

ddl实现方式

5.6 online ddl推出以前，执行ddl主要有两种方式copy方式和inplace方式，inplace方式又称为(fast index creation)。相对于copy方式，inplace方式不拷贝数据，因此较快。但是这种方式仅支持添加、删除索引两种方式，而且与copy方式一样需要全程锁表，实用性不是很强。下面以加索引为例，简单介绍这两种方式的实现流程。

copy方式

(1).新建带索引的[临时表][Link 2]

(2).锁原表，禁止[DML][]，允许查询

(3).将原表数据拷贝到临时表(无排序，一行一行拷贝)

(4).进行rename，升级[字典][Link 3]锁，禁止读写

(5).完成创建索引操作

inplace方式

(1).新建索引的[数据字典][Link 4]

(2).锁表，禁止DML，允许查询

(3).读取[聚集索引][Link 5]，构造新的索引项，排序并插入新索引

(4).等待打开当前表的所有只读事务提交

(5).创建索引结束

**online ddl实现**

online方式实质也包含了copy和inplace方式，对于不支持online的ddl操作采用copy方式，比如修改列类型，删除主键等；对于inplace方式，mysql内部以“是否修改记录格式”为基准也分为两类，一类需要重建表(修改记录格式)，比如添加、删除列、修改列默认值等；另外一类是只需要修改表的[元数据][Link 6]，比如添加、删除索引、修改列名等。Mysql将这两类方式分别称为rebuild方式和no-rebuild方式。online ddl主要包括3个阶段，prepare阶段，ddl执行阶段，commit阶段，rebuild方式比no-rebuild方式实质多了一个ddl执行阶段，prepare阶段和commit阶段类似。下面将主要介绍ddl执行过程中三个阶段的流程。

**Prepare阶段：**

创建新的临时frm文件持有EXCLUSIVE-MDL锁，禁止读写根据alter类型，确定执行方式(copy,online-rebuild,online-norebuild)更新数据字典的内存对象分配row\_log对象记录增量生成新的临时ibd文件

**ddl执行阶段：**

降级EXCLUSIVE-MDL锁，允许读写扫描old\_table的聚集索引每一条记录rec [遍历][Link 7] 新表的聚集索引和二级索引，逐一处理根据rec构造对应的索引项将构造索引项插入sort\_buffer块将sort\_buffer块插入新的索引处理ddl执行过程中产生的增量(仅rebuild类型需要)

**commit阶段**

升级到EXCLUSIVE-MDL锁，禁止读写重做最后row\_log中最后一部分增量更新[innodb][]的数据字典表提交事务(刷事务的redo日志)修改统计信息rename临时idb文件，frm文件变更完成

**关键函数堆栈**

拷贝数据

row\_merge\_build\_indexes

row\_merge\_read\_clustered\_index //拷贝全量

遍历老表的聚集索引

row\_build //创建一个row

row\_merge\_buf\_add //将row加入到sort\_buffer

row\_merge\_insert\_index\_tuples //插入到新表(聚集索引+二级索引)

row\_log\_table\_apply //对于rebuild类型，处理增量

row\_log\_table\_apply\_insert //以insert为例

row\_log\_table\_apply\_convert\_mrec //将buf项转为tuple

插入聚集索引 // row\_ins\_clust\_index\_entry\_low

插入二级索引 // row\_ins\_sec\_index\_entry\_low

修改表数据字典

commit\_try\_norebuild，commit\_try\_rebuild

**常见的ddl操作**

<table style="font-size:undefined; border-collapse:collapse; margin-top:10px; margin-bottom:10px; font-family:微软雅黑; width:597px"> 
 <tbody style="background-color:inherit"> 
  <tr style="background-color:inherit"> 
   <td style="word-break:break-all; border:1px solid rgb(153,153,153); padding:5px 16px 5px 12px; min-height:25px; min-width:25px; height:25px; background-color:inherit; width:169px"> 
    <div style="min-width:2px; background-color:inherit"> 
     <span style="background-color:inherit"><span style="font-family:微软雅黑; font-size:14px">类型</span><br style="background-color:inherit"> </span> 
    </div> </td> 
   <td style="word-break:break-all; border:1px solid rgb(153,153,153); padding:5px 16px 5px 12px; min-height:25px; min-width:25px; height:25px; background-color:inherit; width:86px"> 
    <div style="min-width:2px; background-color:inherit"> 
     <span style="background-color:inherit"><span style="font-family:微软雅黑; font-size:14px">并发DML</span><br style="background-color:inherit"> </span> 
    </div> </td> 
   <td style="word-break:break-all; border:1px solid rgb(153,153,153); padding:5px 16px 5px 12px; min-height:25px; min-width:25px; height:25px; background-color:inherit; width:136px"> 
    <div style="min-width:2px; background-color:inherit"> 
     <span style="background-color:inherit"><span style="font-family:微软雅黑; font-size:14px">算法</span><br style="background-color:inherit"> </span> 
    </div> </td> 
   <td style="word-break:break-all; border:1px solid rgb(153,153,153); padding:5px 16px 5px 12px; min-height:25px; min-width:25px; height:25px; background-color:inherit; width:122px"> 
    <div style="min-width:2px; background-color:inherit"> 
     <span style="font-family:微软雅黑; font-size:14px"><span style="background-color:inherit">备注</span></span> 
     <br style="background-color:inherit"> 
    </div> </td> 
  </tr> 
  <tr style="background-color:inherit"> 
   <td style="word-break:break-all; border:1px solid rgb(153,153,153); padding:5px 16px 5px 12px; min-height:25px; min-width:25px; height:25px; background-color:inherit; width:169px"> 
    <div style="min-width:2px; background-color:inherit"> 
     <span style="font-family:微软雅黑; font-size:14px">添加/删除索引</span> 
     <br style="background-color:inherit"> 
    </div> </td> 
   <td style="word-break:break-all; border:1px solid rgb(153,153,153); padding:5px 16px 5px 12px; min-height:25px; min-width:25px; height:25px; background-color:inherit; width:86px"> 
    <div style="min-width:2px; background-color:inherit"> 
     <span style="font-family:微软雅黑; font-size:14px">Yes</span> 
     <br style="background-color:inherit"> 
    </div> </td> 
   <td style="word-break:break-all; border:1px solid rgb(153,153,153); padding:5px 16px 5px 12px; min-height:25px; min-width:25px; height:25px; background-color:inherit; width:136px"> 
    <div style="min-width:2px; background-color:inherit"> 
     <span style="font-family:微软雅黑; font-size:14px">Online(no-rebuild)</span> 
     <br style="background-color:inherit"> 
    </div> </td> 
   <td style="word-break:break-all; border:1px solid rgb(153,153,153); padding:5px 16px 5px 12px; min-height:25px; min-width:25px; height:25px; background-color:inherit; width:122px"> 
    <div style="min-width:2px; background-color:inherit"> 
     <span style="font-family:微软雅黑; font-size:14px">全文索引不支持</span> 
     <br style="background-color:inherit"> 
    </div> </td> 
  </tr> 
  <tr style="background-color:inherit"> 
   <td style="word-break:break-all; border:1px solid rgb(153,153,153); padding:5px 16px 5px 12px; min-height:25px; min-width:25px; height:25px; background-color:inherit; width:169px"> 
    <div style="min-width:2px; background-color:inherit"> 
     <p style="margin-top:5px; margin-bottom:5px; font-family:微软雅黑; font-size:14px">修改default值</p> 
     <p style="margin-top:5px; margin-bottom:5px; font-family:微软雅黑; font-size:14px">修改列名</p> 
     <p style="margin-top:5px; margin-bottom:5px; font-family:微软雅黑; font-size:14px">修改自增列值<span style="background-color:inherit">&nbsp;</span></p> 
    </div> </td> 
   <td style="word-break:break-all; border:1px solid rgb(153,153,153); padding:5px 16px 5px 12px; min-height:25px; min-width:25px; height:25px; background-color:inherit; width:86px"> 
    <div style="min-width:2px; background-color:inherit"> 
     <span style="font-family:微软雅黑; font-size:14px">Yes</span> 
     <br style="background-color:inherit"> 
    </div> </td> 
   <td style="word-break:break-all; border:1px solid rgb(153,153,153); padding:5px 16px 5px 12px; min-height:25px; min-width:25px; height:25px; background-color:inherit; width:136px"> 
    <div style="min-width:2px; background-color:inherit"> 
     <span style="font-family:微软雅黑; font-size:14px">Nothing</span> 
     <br style="background-color:inherit"> 
    </div> </td> 
   <td style="word-break:break-all; border:1px solid rgb(153,153,153); padding:5px 16px 5px 12px; min-height:25px; min-width:25px; height:25px; background-color:inherit; width:122px"> 
    <div style="min-width:2px; background-color:inherit"> 
     <span style="font-family:微软雅黑; font-size:14px">仅需要修改元数据</span> 
     <br style="background-color:inherit"> 
    </div> </td> 
  </tr> 
  <tr style="background-color:inherit"> 
   <td style="word-break:break-all; border:1px solid rgb(153,153,153); padding:5px 16px 5px 12px; min-height:25px; min-width:25px; height:25px; background-color:inherit; width:169px"> 
    <div style="min-width:2px; background-color:inherit"> 
     <p style="margin-top:5px; margin-bottom:5px; font-family:微软雅黑; font-size:14px">添加/删除列</p> 
     <p style="margin-top:5px; margin-bottom:5px; font-family:微软雅黑; font-size:14px">交换列顺序</p> 
     <p style="margin-top:5px; margin-bottom:5px; font-family:微软雅黑; font-size:14px">修改NULL/NOT NULL</p> 
     <p style="margin-top:5px; margin-bottom:5px; font-family:微软雅黑; font-size:14px">修改ROW-FORMAT</p> 
     <p style="margin-top:5px; margin-bottom:5px; font-family:微软雅黑; font-size:14px">添加/修改主键</p> 
    </div> </td> 
   <td style="word-break:break-all; border:1px solid rgb(153,153,153); padding:5px 16px 5px 12px; min-height:25px; min-width:25px; height:25px; background-color:inherit; width:86px"> 
    <div style="min-width:2px; background-color:inherit"> 
     <span style="font-family:微软雅黑; font-size:14px">Yes</span> 
     <br style="background-color:inherit"> 
    </div> </td> 
   <td style="word-break:break-all; border:1px solid rgb(153,153,153); padding:5px 16px 5px 12px; min-height:25px; min-width:25px; height:25px; background-color:inherit; width:136px"> 
    <div style="min-width:2px; background-color:inherit"> 
     <span style="font-family:微软雅黑; font-size:14px">Online(rebuild)</span> 
     <br style="background-color:inherit"> 
    </div> </td> 
   <td style="word-break:break-all; border:1px solid rgb(153,153,153); padding:5px 16px 5px 12px; min-height:25px; min-width:25px; height:25px; background-color:inherit; width:122px"> 
    <div style="min-width:2px; background-color:inherit"> 
     <span style="font-family:微软雅黑; font-size:14px">由于记录格式改变，需要重建表</span> 
     <br style="background-color:inherit"> 
    </div> </td> 
  </tr> 
  <tr style="background-color:inherit"> 
   <td style="word-break:break-all; border:1px solid rgb(153,153,153); padding:5px 16px 5px 12px; min-height:25px; min-width:25px; height:86px; background-color:inherit; width:169px"> 
    <div style="min-width:2px; background-color:inherit"> 
     <p style="margin-top:5px; margin-bottom:5px; font-family:微软雅黑; font-size:14px">修改列类型</p> 
     <p style="margin-top:5px; margin-bottom:5px; font-family:微软雅黑; font-size:14px">Optimize table</p> 
     <p style="margin-top:5px; margin-bottom:5px; font-family:微软雅黑; font-size:14px">转换字符集</p> 
    </div> </td> 
   <td style="word-break:break-all; border:1px solid rgb(153,153,153); padding:5px 16px 5px 12px; min-height:25px; min-width:25px; height:86px; background-color:inherit; width:86px"> 
    <div style="min-width:2px; background-color:inherit"> 
     <span style="font-family:微软雅黑; font-size:14px">No</span> 
     <br style="background-color:inherit"> 
    </div> </td> 
   <td style="word-break:break-all; border:1px solid rgb(153,153,153); padding:5px 16px 5px 12px; min-height:25px; min-width:25px; height:86px; background-color:inherit; width:136px"> 
    <div style="min-width:2px; background-color:inherit"> 
     <span style="font-family:微软雅黑; font-size:14px">Copy</span> 
     <br style="background-color:inherit"> 
    </div> </td> 
   <td style="word-break:break-all; border:1px solid rgb(153,153,153); padding:5px 16px 5px 12px; min-height:25px; min-width:25px; height:86px; background-color:inherit; width:122px"> 
    <div style="min-width:2px; background-color:inherit"> 
     <span style="font-family:微软雅黑; font-size:14px">需要锁表，不支持online</span> 
     <br style="background-color:inherit"> 
    </div> </td> 
  </tr> 
 </tbody> 
</table>

官网支持列表

**Table 14.6 Summary of Online Status for DDL Operations**

<table>         
 <thead> 
  <tr> 
   <th align="left">Operation</th> 
   <th align="center">In-Place?</th> 
   <th align="center">Copies Table?</th> 
   <th align="center">Allows Concurrent DML?</th> 
   <th align="center">Allows Concurrent Query?</th> 
   <th align="left">Notes</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td align="left"><a title="13.1.13 CREATE INDEX Syntax" href="http://dev.mysql.com/doc/refman/5.6/en/create-index.html" rel="nofollow"><code>CREATE INDEX</code></a>, <a title="13.1.7 ALTER TABLE Syntax" href="http://dev.mysql.com/doc/refman/5.6/en/alter-table.html" rel="nofollow"> <code>ADD INDEX</code></a></td> 
   <td align="center">Yes*</td> 
   <td align="center">No*</td> 
   <td align="center">Yes</td> 
   <td align="center">Yes</td> 
   <td align="left">Some restrictions for <code>FULLTEXT</code> index; see next row.</td> 
  </tr> 
  <tr> 
   <td align="left"><a title="13.1.7 ALTER TABLE Syntax" href="http://dev.mysql.com/doc/refman/5.6/en/alter-table.html" rel="nofollow"><code>ADD FULLTEXT INDEX</code></a></td> 
   <td align="center">Yes</td> 
   <td align="center">No*</td> 
   <td align="center">No</td> 
   <td align="center">Yes</td> 
   <td align="left">Creating the first <code>FULLTEXT</code> index for a table involves a table copy, unless there is a user-supplied <code>FTS_DOC_ID</code> column. Subsequent <code> FULLTEXT</code> indexes on the same table can be created in-place.</td> 
  </tr> 
  <tr> 
   <td align="left"><a title="13.1.24 DROP INDEX Syntax" href="http://dev.mysql.com/doc/refman/5.6/en/drop-index.html" rel="nofollow"><code>DROP INDEX</code></a></td> 
   <td align="center">Yes</td> 
   <td align="center">No</td> 
   <td align="center">Yes</td> 
   <td align="center">Yes</td> 
   <td align="left">Modifies <code>.frm</code> file only, not the data file.</td> 
  </tr> 
  <tr> 
   <td align="left"><a title="13.7.2.4 OPTIMIZE TABLE Syntax" href="http://dev.mysql.com/doc/refman/5.6/en/optimize-table.html" rel="nofollow"><code>OPTIMIZE TABLE</code></a></td> 
   <td align="center">Yes</td> 
   <td align="center">Yes</td> 
   <td align="center">Yes</td> 
   <td align="center">Yes</td> 
   <td align="left">Uses <code>ALGORITHM=INPLACE</code> as of MySQL 5.6.17. <code>ALGORITHM=COPY</code> is used if <code>old_alter_table=1</code> or <a title="4.3.1 mysqld — The MySQL Server" href="http://dev.mysql.com/doc/refman/5.6/en/mysqld.html" rel="nofollow"> <span><strong>mysqld</strong></span></a> <code>--skip-new</code> option is enabled. <code>OPTIMIZE TABLE</code> using online DDL (<code>ALGORITHM=INPLACE</code>) is not supported for tables with FULLTEXT indexes.</td> 
  </tr> 
  <tr> 
   <td align="left">Set default value for a column</td> 
   <td align="center">Yes</td> 
   <td align="center">No</td> 
   <td align="center">Yes</td> 
   <td align="center">Yes</td> 
   <td align="left">Modifies <code>.frm</code> file only, not the data file.</td> 
  </tr> 
  <tr> 
   <td align="left">Change <a title="auto-increment" href="http://dev.mysql.com/doc/refman/5.6/en/glossary.html#glos_auto_increment" rel="nofollow"> auto-increment</a> value for a column</td> 
   <td align="center">Yes</td> 
   <td align="center">No</td> 
   <td align="center">Yes</td> 
   <td align="center">Yes</td> 
   <td align="left">Modifies a value stored in memory, not the data file.</td> 
  </tr> 
  <tr> 
   <td align="left">Add a <a title="FOREIGN KEY constraint" href="http://dev.mysql.com/doc/refman/5.6/en/glossary.html#glos_foreign_key_constraint" rel="nofollow"> foreign key constraint</a></td> 
   <td align="center">Yes*</td> 
   <td align="center">No*</td> 
   <td align="center">Yes</td> 
   <td align="center">Yes</td> 
   <td align="left">To avoid copying the table, disable <a href="http://dev.mysql.com/doc/refman/5.6/en/server-system-variables.html#sysvar_foreign_key_checks" rel="nofollow"> <code>foreign_key_checks</code></a> during constraint creation.</td> 
  </tr> 
  <tr> 
   <td align="left">Drop a <a title="FOREIGN KEY constraint" href="http://dev.mysql.com/doc/refman/5.6/en/glossary.html#glos_foreign_key_constraint" rel="nofollow"> foreign key constraint</a></td> 
   <td align="center">Yes</td> 
   <td align="center">No</td> 
   <td align="center">Yes</td> 
   <td align="center">Yes</td> 
   <td align="left">The <a href="http://dev.mysql.com/doc/refman/5.6/en/server-system-variables.html#sysvar_foreign_key_checks" rel="nofollow"> <code>foreign_key_checks</code></a> option can be enabled or disabled.</td> 
  </tr> 
  <tr> 
   <td align="left">Rename a column</td> 
   <td align="center">Yes*</td> 
   <td align="center">No*</td> 
   <td align="center">Yes*</td> 
   <td align="center">Yes</td> 
   <td align="left">To allow concurrent DML, keep the same data type and only change the column name.</td> 
  </tr> 
  <tr> 
   <td align="left">Add a column</td> 
   <td align="center">Yes*</td> 
   <td align="center">Yes*</td> 
   <td align="center">Yes*</td> 
   <td align="center">Yes</td> 
   <td align="left">Concurrent DML is not allowed when adding an <a title="auto-increment" href="http://dev.mysql.com/doc/refman/5.6/en/glossary.html#glos_auto_increment" rel="nofollow"> auto-increment</a> column. Although <code>ALGORITHM=INPLACE</code> is allowed, the data is reorganized substantially, so it is still an expensive operation.</td> 
  </tr> 
  <tr> 
   <td align="left">Drop a column</td> 
   <td align="center">Yes</td> 
   <td align="center">Yes*</td> 
   <td align="center">Yes</td> 
   <td align="center">Yes</td> 
   <td align="left">Although <code>ALGORITHM=INPLACE</code> is allowed, the data is reorganized substantially, so it is still an expensive operation.</td> 
  </tr> 
  <tr> 
   <td align="left">Reorder columns</td> 
   <td align="center">Yes</td> 
   <td align="center">Yes</td> 
   <td align="center">Yes</td> 
   <td align="center">Yes</td> 
   <td align="left">Although <code>ALGORITHM=INPLACE</code> is allowed, the data is reorganized substantially, so it is still an expensive operation.</td> 
  </tr> 
  <tr> 
   <td align="left">Change <code>ROW_FORMAT</code> property</td> 
   <td align="center">Yes</td> 
   <td align="center">Yes</td> 
   <td align="center">Yes</td> 
   <td align="center">Yes</td> 
   <td align="left">Although <code>ALGORITHM=INPLACE</code> is allowed, the data is reorganized substantially, so it is still an expensive operation.</td> 
  </tr> 
  <tr> 
   <td align="left">Change <code>KEY_BLOCK_SIZE</code> property</td> 
   <td align="center">Yes</td> 
   <td align="center">Yes</td> 
   <td align="center">Yes</td> 
   <td align="center">Yes</td> 
   <td align="left">Although <code>ALGORITHM=INPLACE</code> is allowed, the data is reorganized substantially, so it is still an expensive operation.</td> 
  </tr> 
  <tr> 
   <td align="left">Make column <code>NULL</code></td> 
   <td align="center">Yes</td> 
   <td align="center">Yes</td> 
   <td align="center">Yes</td> 
   <td align="center">Yes</td> 
   <td align="left">Although <code>ALGORITHM=INPLACE</code> is allowed, the data is reorganized substantially, so it is still an expensive operation.</td> 
  </tr> 
  <tr> 
   <td align="left">Make column <code>NOT NULL</code></td> 
   <td align="center">Yes*</td> 
   <td align="center">Yes</td> 
   <td align="center">Yes</td> 
   <td align="center">Yes</td> 
   <td align="left"><code>STRICT_ALL_TABLES</code> or <code> STRICT_TRANS_TABLES</code> <a href="http://dev.mysql.com/doc/refman/5.6/en/server-system-variables.html#sysvar_sql_mode" rel="nofollow"> <code>SQL_MODE</code></a> is required for the operation to succeed. The operation fails if the column contains NULL values. As of 5.6.7, the server prohibits changes to foreign key columns that have the potential to cause loss of referential integrity. For more information, see <a title="13.1.7 ALTER TABLE Syntax" href="http://dev.mysql.com/doc/refman/5.6/en/alter-table.html" rel="nofollow"> Section 13.1.7, “ALTER TABLE Syntax”</a>. Although <code>ALGORITHM=INPLACE</code> is allowed, the data is reorganized substantially, so it is still an expensive operation.</td> 
  </tr> 
  <tr> 
   <td align="left">Change data type of column</td> 
   <td align="center">No</td> 
   <td align="center">Yes</td> 
   <td align="center">No</td> 
   <td align="center">Yes</td> 
   <td align="left">&nbsp;</td> 
  </tr> 
  <tr> 
   <td align="left">Add <a title="primary key" href="http://dev.mysql.com/doc/refman/5.6/en/glossary.html#glos_primary_key" rel="nofollow"> primary key</a></td> 
   <td align="center">Yes*</td> 
   <td align="center">Yes</td> 
   <td align="center">Yes</td> 
   <td align="center">Yes</td> 
   <td align="left">Although <code>ALGORITHM=INPLACE</code> is allowed, the data is reorganized substantially, so it is still an expensive operation. <code>ALGORITHM=INPLACE</code> is not allowed under certain conditions if columns have to be converted to <code>NOT NULL</code>. See <a title="Example 14.9 Creating and Dropping the Primary Key" href="http://dev.mysql.com/doc/refman/5.6/en/innodb-create-index-examples.html#online-ddl-ex-primary-key" rel="nofollow"> Example 14.9, “Creating and Dropping the Primary Key”</a>.</td> 
  </tr> 
  <tr> 
   <td align="left">Drop <a title="primary key" href="http://dev.mysql.com/doc/refman/5.6/en/glossary.html#glos_primary_key" rel="nofollow"> primary key</a> and add another</td> 
   <td align="center">Yes</td> 
   <td align="center">Yes</td> 
   <td align="center">Yes</td> 
   <td align="center">Yes</td> 
   <td align="left"><code>ALGORITHM=INPLACE</code> is only allowed when you add a new primary key in the same <a title="13.1.7 ALTER TABLE Syntax" href="http://dev.mysql.com/doc/refman/5.6/en/alter-table.html" rel="nofollow"> <code>ALTER TABLE</code></a>; the data is reorganized substantially, so it is still an expensive operation.</td> 
  </tr> 
  <tr> 
   <td align="left">Drop <a title="primary key" href="http://dev.mysql.com/doc/refman/5.6/en/glossary.html#glos_primary_key" rel="nofollow"> primary key</a></td> 
   <td align="center">No</td> 
   <td align="center">Yes</td> 
   <td align="center">No</td> 
   <td align="center">Yes</td> 
   <td align="left">Restrictions apply when you drop a primary key primary key without adding a new one in the same <code>ALTER TABLE</code> statement.</td> 
  </tr> 
  <tr> 
   <td align="left">Convert character set</td> 
   <td align="center">No</td> 
   <td align="center">Yes</td> 
   <td align="center">No</td> 
   <td align="center">Yes</td> 
   <td align="left">Rebuilds the table if the new character encoding is different.</td> 
  </tr> 
  <tr> 
   <td align="left">Specify character set</td> 
   <td align="center">No</td> 
   <td align="center">Yes</td> 
   <td align="center">No</td> 
   <td align="center">Yes</td> 
   <td align="left">Rebuilds the table if the new character encoding is different.</td> 
  </tr> 
  <tr> 
   <td align="left">Rebuild with <code>FORCE</code> option</td> 
   <td align="center">Yes</td> 
   <td align="center">Yes</td> 
   <td align="center">Yes</td> 
   <td align="center">Yes</td> 
   <td align="left">Uses <code>ALGORITHM=INPLACE</code> as of MySQL 5.6.17. <code>ALGORITHM=COPY</code> is used if <code>old_alter_table=1</code> or <a title="4.3.1 mysqld — The MySQL Server" href="http://dev.mysql.com/doc/refman/5.6/en/mysqld.html" rel="nofollow"> <span><strong>mysqld</strong></span></a> <code>--skip-new</code> option is enabled. Table rebuild using online DDL (<code>ALGORITHM=INPLACE</code>) is not supported for tables with FULLTEXT indexes.</td> 
  </tr> 
  <tr> 
   <td align="left">Rebuild with <span>“<span>null</span>”</span> <code>ALTER TABLE ... ENGINE=INNODB</code></td> 
   <td align="center">Yes</td> 
   <td align="center">Yes</td> 
   <td align="center">Yes</td> 
   <td align="center">Yes</td> 
   <td align="left">Uses <code>ALGORITHM=INPLACE</code> as of MySQL 5.6.17. <code>ALGORITHM=COPY</code> is used if <code>old_alter_table=1</code> or <a title="4.3.1 mysqld — The MySQL Server" href="http://dev.mysql.com/doc/refman/5.6/en/mysqld.html" rel="nofollow"> <span><strong>mysqld</strong></span></a> <code>--skip-new</code> option is enabled. Table rebuild using online DDL (<code>ALGORITHM=INPLACE</code>) is not supported for tables with FULLTEXT indexes.</td> 
  </tr> 
  <tr> 
   <td align="left">Set table-level <a title="persistent statistics" href="http://dev.mysql.com/doc/refman/5.6/en/glossary.html#glos_persistent_statistics" rel="nofollow"> persistent statistics</a> options (<code>STATS_PERSISTENT</code>, <code>STATS_AUTO_RECALC</code> <code>STATS_SAMPLE_PAGES</code>)</td> 
   <td align="center">Yes</td> 
   <td align="center">No</td> 
   <td align="center">Yes</td> 
   <td align="center">Yes</td> 
   <td align="left">Modifies <code>.frm</code> file only, not the data file.</td> 
  </tr> 
 </tbody> 
</table>

若干问题

1.如何实现数据完整性

使用online ddl后，用户心中一定有一个疑问，一边做ddl，一边做dml，表中的数据不会乱吗？这里面关键部件是row\_log。row\_log记录了ddl变更过程中新产生的dml操作，并在ddl执行的最后将其应用到新的表中，保证数据完整性。

2.online与数据一致性如何兼得

实际上，online ddl并非整个过程都是online，在prepare阶段和commit阶段都会持有MDL-Exclusive锁，禁止读写；而在整个ddl执行阶段，允许读写。由于prepare和commit阶段相对于ddl执行阶段时间特别短，因此基本可以认为是全程online的。Prepare阶段和commit阶段的禁止读写，主要是为了保证数据一致性。Prepare阶段需要生成row\_log对象和修改内存的字典；Commit阶段，禁止读写后，重做最后一部分增量，然后提交，保证数据一致。

3.如何实现server层和innodb层一致性

在prepare阶段，server层会生成一个临时的frm文件，里面包含了新表的格式；innodb层生成了临时的ibd文件(rebuild方式)；在ddl执行阶段，将数据从原表拷贝到临时ibd文件，并且将row\_log增量应用到临时ibd文件；在commit阶段，innodb层修改表的数据字典，然后提交；最后innodb层和mysql层面分别重命名frm和idb文件。

参考文档

http://hedengcheng.com/?p=405

http://hedengcheng.com/?p=421

http://hedengcheng.com/?p=148

[ddl]: http://zhannei.baidu.com/cse/search?s=12535204980459304754&entry=1&q=ddl
[Link 1]: http://zhannei.baidu.com/cse/search?s=12535204980459304754&entry=1&q=%E7%BA%BF%E7%A8%8B
[mysql]: http://www.codesec.net/list/8/
[Link 2]: http://zhannei.baidu.com/cse/search?s=12535204980459304754&entry=1&q=%E4%B8%B4%E6%97%B6%E8%A1%A8
[DML]: http://zhannei.baidu.com/cse/search?s=12535204980459304754&entry=1&q=DML
[Link 3]: http://zhannei.baidu.com/cse/search?s=12535204980459304754&entry=1&q=%E5%AD%97%E5%85%B8
[Link 4]: http://zhannei.baidu.com/cse/search?s=12535204980459304754&entry=1&q=%E6%95%B0%E6%8D%AE%E5%AD%97%E5%85%B8
[Link 5]: http://zhannei.baidu.com/cse/search?s=12535204980459304754&entry=1&q=%E8%81%9A%E9%9B%86%E7%B4%A2%E5%BC%95
[Link 6]: http://zhannei.baidu.com/cse/search?s=12535204980459304754&entry=1&q=%E5%85%83%E6%95%B0%E6%8D%AE
[Link 7]: http://zhannei.baidu.com/cse/search?s=12535204980459304754&entry=1&q=%E9%81%8D%E5%8E%86
[innodb]: http://zhannei.baidu.com/cse/search?s=12535204980459304754&entry=1&q=innodb