注册 登录  
 加关注
   显示下一条  |  关闭
温馨提示!由于新浪微博认证机制调整,您的新浪微博帐号绑定已过期,请重新绑定!立即重新绑定新浪微博》  |  关闭

Inside MySQL

MySQL MariaDB InnoDB InnoSQL

 
 
 

日志

 
 
关于我

MySQL技术内幕系列作者, 网易杭州研究院MySQL技术经理, 擅长于MySQL performance tuning、troubleshooting、systems availability and scalability、capacity planning

网易考拉推荐

Online DDL in MySQL 5.6  

2013-11-06 17:07:34|  分类: 默认分类 |  标签: |举报 |字号 订阅

  下载LOFTER 我的照片书  |

在线DDL操作(Online DDL)是MySQL 5.6中最为另人激动的改进功能。从历史上看,若我没记错MySQL在2007年就完成了在线索引接口的设计。而MySQL NDB Cluster、TokuDB都早在5.1版本中就支持在线索引添加,然而直到MySQL 5.6用户才能享受到该功能(虽然facebook团队有推出OSC),这也是MySQL数据库长期被吐槽的原因之一(特别是Oracle DBAOnline DDL in MySQL 5.6 - insidemysql - Inside MySQL)。


虽然MySQL 5.6支持了在线DDL操作,不过还是有些需要注意的问题,最为重要的就是参数innodb_online_alter_log_max_size的配置。在进行DDL操作时会保存操作时间内产生的日志,这与facebook OSC的临时表,但是其保存在内存中,而该内存的大小由参数innodb_online_alter_log_max_size定义,默认大小为128M。如果产生的日志超出该容量大小,则会抛出类似如下的异常提示:

Error:1799SQLSTATE:HY000(ER_INNODB_ONLINE_LOG_TOO_BIG)

Message: Creating index 'idx_aaa' required more than 'innodb_online_alter_log_max_size' bytes of modification log. Please try again.


根据我的实际经验体会,128M通常在一个活跃的线上环境中是不够的,通常512M是比较推荐的值。好在该参数是动态的,可以基于会话级别进行调整。另外个人的体会是,在进行在线索引添加操作时,数据库性能会有20~30%的下降。


另一个小技巧是使用ALTER TABLE ... LOCK=NONE来强制使用在线DDL操作,若操作不支持,则会抛出异常,如:

mysql> ALTER TABLE customers DROP PRIMARY KEY ,LOCK=NONE;

ERROR 1846 (0A000): LOCK=NONE is not supported. Reason: Dropping a primary key is not allowed without also adding a new primary key. Try LOCK=SHARED.


正如前面所说的,并不是所有的DDL操作都支持在线操作,这里附上MySQL官方文档对于DDL操作的总结(http://dev.mysql.com/doc/refman/5.6/en/innodb-create-index-overview.html):

OperationIn-Place?Copies Table?Allows Concurrent DML?Allows Concurrent Query?Notes
CREATE INDEX,ADD INDEXYes*No*YesYesSome restrictions for FULLTEXT index; see next row. Currently, the operation is not in-place (that is, it copies the table) if the same index being created was also dropped by an earlier clause in the sameALTER TABLE statement.
ADD FULLTEXT INDEXYesNo*NoYesCreating the first FULLTEXT index for a table involves a table copy, unless there is a user-supplied FTS_DOC_ID column. Subsequent FULLTEXT indexes on the same table can be created in-place.
DROP INDEXYesNoYesYes 
Set default value for a columnYesNoYesYesModifies .frm file only, not the data file.
Change auto-increment value for a columnYesNoYesYesModifies a value stored in memory, not the data file.
Add a foreign key constraintYes*No*YesYesTo avoid copying the table, disableforeign_key_checks during constraint creation.
Drop a foreign key constraintYesNoYesYesThe foreign_key_checks option can be enabled or disabled.
Rename a columnYes*No*Yes*YesTo allow concurrent DML, keep the same data type and only change the column name.
Add a columnYesYesYes*YesConcurrent DML is not allowed when adding an auto-increment column. Although ALGORITHM=INPLACE is allowed, the data is reorganized substantially, so it is still an expensive operation.
Drop a columnYesYesYesYesAlthough ALGORITHM=INPLACE is allowed, the data is reorganized substantially, so it is still an expensive operation.
Reorder columnsYesYesYesYesAlthough ALGORITHM=INPLACE is allowed, the data is reorganized substantially, so it is still an expensive operation.
ChangeROW_FORMATpropertyYesYesYesYesAlthough ALGORITHM=INPLACE is allowed, the data is reorganized substantially, so it is still an expensive operation.
ChangeKEY_BLOCK_SIZEpropertyYesYesYesYesAlthough ALGORITHM=INPLACE is allowed, the data is reorganized substantially, so it is still an expensive operation.
Make columnNULLYesYesYesYesAlthough ALGORITHM=INPLACE is allowed, the data is reorganized substantially, so it is still an expensive operation.
Make column NOT NULLYes*YesYesYesWhen SQL_MODE includesstrict_all_tables orstrict_all_tables, the operation fails if the column contains any nulls. AlthoughALGORITHM=INPLACE is allowed, the data is reorganized substantially, so it is still an expensive operation.
Change data type of columnNoYesNoYes 
Add primary keyYes*YesYesYesAlthough ALGORITHM=INPLACE is allowed, the data is reorganized substantially, so it is still an expensive operation. ALGORITHM=INPLACE is not allowed under certain conditions if columns have to be converted to NOT NULL. SeeExample 14.9, “Creating and Dropping the Primary Key”.
Drop primary keyand add anotherYesYesYesYesALGORITHM=INPLACE is only allowed when you add a new primary key in the same ALTER TABLE; the data is reorganized substantially, so it is still an expensive operation.
Drop primary keyNoYesNoYesRestrictions apply when you drop a primary key primary key without adding a new one in the same ALTER TABLEstatement.
Convert character setNoYesNoYesRebuilds the table if the new character encoding is different.
Specify character setNoYesNoYesRebuilds the table if the new character encoding is different.
Rebuild withFORCE optionNoYesNoYesActs like the ALGORITHM=COPY clause or the setting old_alter_table=1.

——EOF——
PS: 欢迎关注InsideMySQL公众帐号
Online DDL in MySQL 5.6 - insidemysql - Inside MySQL
  评论这张
 
阅读(1946)| 评论(2)
推荐 转载

历史上的今天

评论

<#--最新日志,群博日志--> <#--推荐日志--> <#--引用记录--> <#--博主推荐--> <#--随机阅读--> <#--首页推荐--> <#--历史上的今天--> <#--被推荐日志--> <#--上一篇,下一篇--> <#-- 热度 --> <#-- 网易新闻广告 --> <#--右边模块结构--> <#--评论模块结构--> <#--引用模块结构--> <#--博主发起的投票-->
 
 
 
 
 
 
 
 
 
 
 
 
 
 

页脚

网易公司版权所有 ©1997-2017