[MySQL 5.6] MySQL 5.6 online ddl 使用、测试及关键函数栈
来源:互联网 发布:淘宝客单纯做3c产品 编辑:程序博客网 时间:2024/05/17 01:19
[MySQL 5.6] MySQL 5.6 online ddl 使用、测试及关键函数栈
前言
CREATE INDEX
,ADD INDEX
Yes*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 INDEX
YesNo*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 INDEX
YesNoYesYes 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_FORMAT
propertyYesYesYesYesAlthough ALGORITHM=INPLACE
is allowed, the data is reorganized substantially, so it is still an expensive operation.ChangeKEY_BLOCK_SIZE
propertyYesYesYesYesAlthough ALGORITHM=INPLACE
is allowed, the data is reorganized substantially, so it is still an expensive operation.Make columnNULL
YesYesYesYesAlthough ALGORITHM=INPLACE
is allowed, the data is reorganized substantially, so it is still an expensive operation.Make column NOT NULL
Yes*YesYesYesWhen SQL_MODE
includesstrict_all_tables
orstrict_all_tables
, the operation fails if the column contains any nulls. Although ALGORITHM=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
. See Example 5.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 TABLE
statement.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
.LOCK=
ALGORITHM=[INPLACE|COPY]
innodb_online_alter_log_max_size 需要注意,它表示在做在线DDL的过程中,并发DML产生的日志最大允许的大小。如果负载很高,这个值应该尽量的调大,否则可能导致DDL失败。
性能损耗
无压力负载测试:
mysql> set session old_alter_table = OFF;
Query OK, 0 rows affected (0.00 sec)
mysql> alter table sbtest1 add key (k);
Query OK, 0 rows affected (10.44 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> alter table sbtest1 drop key k;
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> alter table sbtest1 add key(k), ALGORITHM=COPY;
Query OK, 1000000 rows affected (27.72 sec)
Records: 1000000 Duplicates: 0 Warnings: 0
mysql> alter table sbtest1 drop key k;
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> alter table sbtest1 add key(k), LOCK=SHARED;
Query OK, 0 rows affected (9.89 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> alter table sbtest1 drop key k;
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> alter table sbtest1 add key(k), LOCK=EXCLUSIVE;
Query OK, 0 rows affected (10.07 sec)
Records: 0 Duplicates: 0 Warnings: 0
内部实现
8031 &ha_alter_info);
6391 goto rollback;
6392 }
6422 goto rollback;
6423 }
6449 {
6450 goto rollback;
6451 }
原创文章,转载请注明: 转载自Simple Life
本文链接地址: [MySQL 5.6] MySQL 5.6 online ddl 使用、测试及关键函数栈
- [MySQL 5.6] MySQL 5.6 online ddl 使用、测试及关键函数栈
- [MySQL 5.6] MySQL 5.6 online ddl 使用、测试及关键函数栈
- mysql 5.6 online ddl 测试
- Online DDL in MySQL 5.6
- MySQL 5.6 Online DDL异常分析
- MySql online DDL 原理
- MySQL中的Online DDL
- MySQL--online ddl原理
- mysql online ddl原理
- MySQL online ddl原理
- MySQL online ddl原理
- 关于mysql 5.6 哪些操作可以进行online ddl 详解
- mysql 5.6在线DDL建索引测试
- mysql dml阻塞online ddl
- 浅谈MySQL Online DDL (上)
- 浅谈MySQL Online DDL(中)
- 浅谈MySQL Online DDL(下)
- MYSQL ONLINE DDL--PT-ONLINE-CHANGE-SCHEMA
- 模式识别 - 多视角学习 (Multi-View Learning) 简介
- 如何在maven工程中加载oracle驱动
- Oracle 11g RAC+DG项目实战-视频分享
- strtok()和strtok_r()
- c#的字符解析简单示例
- [MySQL 5.6] MySQL 5.6 online ddl 使用、测试及关键函数栈
- 项目中使用ecTable的代码
- 表格嵌套的边框处理
- CButtonST类简介
- 在IBM AIX p750小机上为Oracle扩展逻辑卷-视频分享
- struts1和struts2的区别详解
- adb shell 读取以及cmd赋值的用法记录
- oracle之修改已经定义好的表、列信息
- RAC生产库现场调整备份策略实战-视频分享