MySQL5.6主键的在线DDL变更测试

导读：本文主要详细测试online DDL中的删除，添加主键操作。关于MySQL5.6在线DDL的全文信息，请参照：MySQL5.6版本InnoDB存储引擎在线DDL变更的官方信息中文翻译版，
文章地址：http://www.mysqlops.com/2013/03/26/mysql56-innodb-ddl.html

测试目的主要有以下几点：
(1):以alter table的方式新增主键，MySQL需要做什么
(2):ALGORITHM=inplace与ALGORITHM=copy 增删主键的不同之处，和适用于怎么的环境
(3):inplace与copy对于重建主键的效率,考虑哪些元素
(4):online DDL是如何控制DML，查询的并发

用例1：
mysql&gt; CREATE TABLE `tpri` (  `a` int(11) DEFAULT NULL,   `b` int(11) DEFAULT NULL ) ENGINE=InnoDB;
Query OK, 0 rows affected (0.10 sec)
mysql&gt;  insert into tpri values(null,1);
Query OK, 1 row affected (0.01 sec)
mysql&gt; alter table tpriadd primary key(b),algorithm=inplace;
ERROR 1846 (0A000): ALGORITHM=INPLACE is not supported.
Reason: cannot silently convert NULL values, as required in this SQL_MODE. Try ALGORITHM=COPY.

//错误提示：不支持ALGORITHM=INPLACE。因为在这种SQL_MODE 模式下，不能静态转换NULL值，尝试ALGORITHM=COPY

用例2：
mysql> CREATE TABLE `tpri2` ( `a` int(11) DEFAULT NULL, `b` int(11) DEFAULT NULL ) ENGINE=InnoDB;
Query OK, 0 rows affected (0.10 sec)
mysql> insert into tpri2 values(null,1);
Query OK, 1 row affected (0.01 sec)
mysql> alter table tpri2 add primary key(b),algorithm=copy;
Query OK, 2 rows affected, 1 warning (0.20 sec)
Records: 2 Duplicates: 0 Warnings: 1
mysql> show create table tpri2 \G
*************************** 1. row ***************************
Table: tpri2
Create Table: CREATE TABLE `tpri2` (
`a` int(11) DEFAULT NULL,
`b` int(11) NOT NULL DEFAULT ’0′,
PRIMARY KEY (`b`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.02 sec)

用例3：
mysql> CREATE TABLE `tpri3` ( `a` int(11) DEFAULT NULL, `b` int(11) DEFAULT NULL ) ENGINE=InnoDB;
Query OK, 0 rows affected (0.10 sec)
mysql> show create table tpri3 \G
*************************** 1. row ***************************
Table: tpri3
Create Table: CREATE TABLE `tpri3` (
`a` int(11) DEFAULT NULL,
`b` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)
mysql> insert into tpri3 values(null,1);
Query OK, 1 row affected (0.02 sec)
mysql> alter table tpri3 add primary key(b);
Query OK, 1 row affected (0.19 sec)
Records: 1 Duplicates: 0 Warnings: 0
mysql> show create table tpri3 \G
*************************** 1. row ***************************
Table: tpri3
Create Table: CREATE TABLE `tpri` (
`a` int(11) DEFAULT NULL,
`b` int(11) NOT NULL DEFAULT ’0′,
PRIMARY KEY (`b`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.01 sec)

用例4：
mysql> set sql_mode=’strict_trans_tables’;
Query OK, 0 rows affected (0.00 sec)
mysql> CREATE TABLE `tpri4` ( `a` int(11) DEFAULT NULL, `b` int(11) DEFAULT NULL ) ENGINE=InnoDB;
Query OK, 0 rows affected (0.10 sec)
mysql> alter table tpri4 add primary key(b),algorithm=inplace;
Query OK, 0 rows affected (0.12 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> show create table tpri4 \G
*************************** 1. row ***************************
Table: tpri4
Create Table: CREATE TABLE `tpri4` (
`a` int(11) DEFAULT NULL,
`b` int(11) NOT NULL DEFAULT ’0′,
PRIMARY KEY (`b`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

用例5：
mysql> CREATE TABLE `tpri5` ( `a` int(11) DEFAULT NULL, `b` int(11) default NULL ) ENGINE=InnoDB;
Query OK, 0 rows affected (0.04 sec)
mysql> insert into tpri5 values(null,1);
Query OK, 1 row affected (0.01 sec)
mysql> alter table tpri5 add primary key(b);
Query OK, 0 rows affected (0.08 sec)
Records: 0 Duplicates: 0 Warnings: 0

用例6：
mysql> CREATE TABLE `tpri6` ( `a` int(11) DEFAULT NULL, `b` int(11) default NULL ) ENGINE=InnoDB;
Query OK, 0 rows affected (0.05 sec)
mysql> insert into tpri5 values (null,1),(1,null),(2,null);
Query OK, 3 rows affected (0.01 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> alter table tpri5 add primary key(b),ALGORITHM=COPY;
ERROR 1265 (01000): Data truncated for column ‘b’ at row 2
mysql> alter table tpri5 add primary key(b),ALGORITHM=INPLACE;
ERROR 1138 (22004): Invalid use of NULL value

小结1:
当重建主键时，MySQL需要做额外的工作，检查表中的主键列是否有重复值，和null值，并且检查列定义时是否允许为NULL；

如上面测试用例中的b default null 在添加主键过程中，转换为 not null default 0(数值型默认为0，字符和BLOB 默认为空字符).

从测试用例1，2,4中可以发现：对于主键列为null时，alter table …. add primary key 只在sql_mode 包含srict_trans_table
或strict_all_tables标志下才支持ALGORITHM=INPLACE，否则,强制指定ALGORITHM=INPLACE，会出现测试用例1的错误信息。

从测试用例3,5可知，没有指定ALGORITHM子句时，MySQL会自动选择允许的算法（如果都支持，则ALGORITHM=INPLACE优先），
测试用例6表明，主键列有NULL值，不管以什么算法去重建主键，都是会失败的。

2:alter table …. add primary key 下的ALGORITHM=INPLACE一定比ALGORITHM=copy快吗？
mysql> show create table big_table \G
*************************** 1. row ***************************
Table: big_table
Create Table: CREATE TABLE `big_table` (
`TABLE_CATALOG` varchar(512) NOT NULL DEFAULT ”,
`TABLE_SCHEMA` varchar(64) NOT NULL DEFAULT ”,
`TABLE_NAME` varchar(64) NOT NULL DEFAULT ”,
`COLUMN_NAME` varchar(64) NOT NULL DEFAULT ”,
`ORDINAL_POSITION` bigint(21) unsigned NOT NULL DEFAULT ’0′,
`COLUMN_DEFAULT` longtext,
`IS_NULLABLE` varchar(3) NOT NULL DEFAULT ”,
`DATA_TYPE` varchar(64) NOT NULL DEFAULT ”,
`CHARACTER_MAXIMUM_LENGTH` bigint(21) unsigned DEFAULT NULL,
`CHARACTER_OCTET_LENGTH` bigint(21) unsigned DEFAULT NULL,
`NUMERIC_PRECISION` bigint(21) unsigned DEFAULT NULL,
`NUMERIC_SCALE` bigint(21) unsigned DEFAULT NULL,
`DATETIME_PRECISION` bigint(21) unsigned DEFAULT NULL,
`CHARACTER_SET_NAME` varchar(32) DEFAULT NULL,
`COLLATION_NAME` varchar(32) DEFAULT NULL,
`COLUMN_TYPE` longtext NOT NULL,
`COLUMN_KEY` varchar(3) NOT NULL DEFAULT ”,
`EXTRA` varchar(30) DEFAULT NULL,
`PRIVILEGES` varchar(80) NOT NULL DEFAULT ”,
`COLUMN_COMMENT` varchar(1024) NOT NULL DEFAULT ”,
`id` int(11) NOT NULL DEFAULT ’0′,
`id2` int(11) NOT NULL DEFAULT ’0′,
PRIMARY KEY (`id2`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 KEY_BLOCK_SIZE=8
1 row in set (0.03 sec)
mysql> select count(*) from big_table ;
+———-+
| count(*) |
+———-+
| 1750016 |
+———-+
1 row in set (4.90 sec)

第一组测试
1:mysql> alter table big_table add column id int not null auto_increment primary key,ALGORITHM=copy;
Query OK, 1750016 rows affected, 2 warnings (48.99 sec)
Records: 1750016 Duplicates: 0 Warnings: 2

2:mysql> alter table big_table add column id int not null auto_increment primary key,ALGORITHM=INPLACE;
Query OK, 0 rows affected, 2 warnings (2 min 16.79 sec)
Records: 0 Duplicates: 0 Warnings: 2

3:mysql> alter table big_table add primary key(id),ALGORITHM=INPLACE;
Query OK, 0 rows affected, 2 warnings (2 min 12.44 sec)
Records: 0 Duplicates: 0 Warnings: 2

4:mysql> alter table big_table add primary key(id),ALGORITHM=copy;
Query OK, 1750016 rows affected, 2 warnings (47.69 sec)
Records: 1750016 Duplicates: 0 Warnings: 2

5:mysql> alter table big_table drop primary key ,add primary key(id2),ALGORITHM=copy;
Query OK, 1750016 rows affected, 2 warnings (48.51 sec)
Records: 1750016 Duplicates: 0 Warnings: 2

6:mysql> alter table big_table drop primary key ,add primary key(id),ALGORITHM=inplace;
Query OK, 0 rows affected, 2 warnings (2 min 19.99 sec)
Records: 0 Duplicates: 0 Warnings: 2

第二组测试
mysql> alter table big_table modify id int(11) not null default 0;
Query OK, 1750016 rows affected, 2 warnings (46.60 sec)
Records: 1750016 Duplicates: 0 Warnings: 2

7:mysql> alter table big_table drop primary key,ALGORITHM=INPLACE;
ERROR 1846 (0A000): ALGORITHM=INPLACE is not supported. Reason: Dropping a primary key is not allowed without also adding a new primary key. Try ALGORITHM=COPY.
8:mysql> alter table big_table drop primary key,ALGORITHM=copy;
Query OK, 1750016 rows affected, 2 warnings (50.19 sec)
Records: 1750016 Duplicates: 0 Warnings: 2

从第一组测试中的alter table….add …primary key 的各种形式表明:选用ALGORITHM=INPLACE的执行效率明显比ALGORITHM=copy的效率慢
（大家如果做实验时一定要注意数据量的多少，数据量小的话则测试结果可能会刚好相反的）。是什么原因呢？我认为可以从数据存储来分析，
InnoDB的数据存储以主键聚集来组织的。

采用algorithm=inplace新建主键，虽然避免表的复制，但数据需要重新进行重组的。ALGORITHM=copy把最初表拷贝
到临时表，然后把临时表的数据插入到新表。具体情况需要分析这两种算法的源代码。对于alter table … drop primary key 之后没有添加新的主键，
那么只能采用ALGORITHM=copy,如果强制采用ALGORITHM=inplace会出现7中错误。

建议：
(1):在创建表时，指定主键，避免使用alter table 创建主键重建表
(2):对大数据重建主键建议采用 ALGORITHM=copy，数据量小可以采用默认情况。

mysql>commit; altering

uery OK, 0 rows affected, 2 warnings (3 min 22.00 sec)
小结3：
从线程列表中，可以知道事务发起顺序是session1发起DML操作,session2发起查询，session3最后发起online DDL，
DDL一直等待在DDL发起之前的其他的事务的提交和回滚，然后才执行DDL操作。在这种情况下，online DDL操作是允许进行DML,查询并发。
有一个疑惑：在DDL之后（即在DDL进行时）发起的事务，是否允许？见如下测试（session1，session2，session3 执行与上面相同的操作）。

小结4：
已经可以回答上面的疑惑了，在DDL发起和完成之前，允许在这期间DML，查询（注意这些事务都没有明确指定锁级别）的并发。但DDL在完成时，
还是需要获取表的元数据锁。因为online DDL在 preparing for alter table，committing alter table to stor这两个阶段需要获取表的排斥锁
（这期间是短暂），在altering table 阶段允许其他事务并发的。（online DDL 的操作步骤可以通过打开profiling，来查看，这里就不列出来）。

为了方便让大家更好发现online DDL和以前机制（ALGORITHM=copy）的DDL的区别，见下面的测试：

小结5：ALGORITHM=copy的机制，是不允许DML并发的，但允许查询。

小结6：lock=shared，不允许DML并发，但允许查询执行。

觉得文章有用？立即：和朋友一起 共学习共进步！

OurMySQL

MySQL5.6主键的在线DDL变更测试

猜您喜欢

文章评论： “MySQL5.6主键的在线DDL变更测试”

发表评论取消回复

猜您喜欢

文章评论： “MySQL5.6主键的在线DDL变更测试”

发表评论 取消回复

发表评论取消回复