摘要:
MySQL5.6 针对 MySQL5.5 各个方面又提升了很多,特别在性能和一些新参数上面,现在看看大致提升了哪些方面(后续不定时更新)。
一:性能、功能上的提升。
① 在线DDL即 online DDL,日常的增删字段和索引都不会出现问题,但还是有很多操作不支持完全的在线DDL,包括增加一个全文索引,修改列的数据类型,删除一个主键,修改表的字符集等,其中主键可以通过自己指定的方式进行操作,操作方式有2种:algorithm=inplace|copy 。也可以看官方的例子
dba@192.168.200.59 : dchat_main 05:44:54>alter table messages_tt add primary key(m_id),algorithm=inplace;
Query OK,0 rows affected (26.59sec)
Records:0 Duplicates: 0 Warnings: 0dba@192.168.200.59 : dchat_main 05:45:53>alter table messages_tt drop primary key;
Query OK,684076 rows affected (16.46sec)
Records:684076 Duplicates: 0 Warnings: 0dba@192.168.200.59 : dchat_main 05:46:20>alter table messages_tt add primary key(m_id),algorithm=copy;Query OK,684076 rows affected (17.54sec)
Records:684076 Duplicates: 0 Warnings: 0dba@192.168.200.59 : dchat_main 05:46:48>alter table messages_tt drop primary key;
Query OK,684076 rows affected (16.73sec)
Records:684076 Duplicates: 0 Warnings: 0dba@192.168.200.59 : dchat_main 05:48:19>alter table messages_tt add primary key(m_id);默认,和第一次inplace效果一样
Query OK,0 rows affected (26.31sec)
Records:0 Duplicates: 0 Warnings: 0
下面是MySQL官方的测试例子:
mysql> CREATE TABLE add_pk_via_inplace (c1 INT, c2 VARCHAR(10), c3 DATETIME);
Query OK,0 rows affected (0.02sec)
mysql> INSERT INTO add_pk_via_inplace VALUES (1,'a','2014-11-03 11:01:37'),(NULL,NULL,NULL);
Query OK,2 rows affected (0.00sec)
Records:2 Duplicates: 0 Warnings: 0mysql> SELECT * FROMadd_pk_via_inplace;+------+------+---------------------+
| c1 | c2 | c3 |
+------+------+---------------------+
| 1 | a | 2014-11-03 11:01:37 |
| NULL | NULL | NULL |
+------+------+---------------------+
2 rows in set (0.00sec)
mysql> SET sql_mode = '';
Query OK,0 rows affected (0.00sec)
mysql> ALTER TABLE add_pk_via_inplace ADD PRIMARY KEY (c1,c2,c3), ALGORITHM=INPLACE;
ERROR1846 (0A000): ALGORITHM=INPLACE is not supported. Reason: cannot silently convert NULL
values, as required in this SQL_MODE. Try ALGORITHM=COPY.
mysql> SET sql_mode ='strict_trans_tables';
Query OK,0 rows affected (0.00sec)
mysql> ALTER TABLE add_pk_via_inplace ADD PRIMARY KEY (c1,c2,c3), ALGORITHM=INPLACE;
ERROR1138 (22004): Invalid use of NULLvalue
mysql> DELETE FROM add_pk_via_inplace WHERE c1 IS NULL OR c2 IS NULL OR c3 IS NULL;
Query OK,1 row affected (0.01sec)
mysql> SELECT * FROMadd_pk_via_inplace;+------+------+---------------------+
| c1 | c2 | c3 |
+------+------+---------------------+
| 1 | a | 2014-11-03 11:01:37 |
+------+------+---------------------+
1 row in set (0.00sec)
mysql> ALTER TABLE add_pk_via_inplace ADD PRIMARY KEY (c1,c2,c3), ALGORITHM=INPLACE;
Query OK,0 rows affected (0.09sec)
Records:0 Duplicates: 0 Warnings: 0
View Code
从自己的例子看出,在处理主键时,没有指定ALGORITHM子句,MySQL会自动选择任意算法(如果都支持,则ALGORITHM=INPLACE优先)。所以在执行的时候可以不用显性的加上"algorithm"。
在官方的例子看出,对于主键列为null时,alter table … add primary key 只在sql_mode 包含srict_trans_table 或strict_all_tables标志下才支持ALGORITHM=INPLACE,否则,强制指定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,会复制表,并且把null 改成not null。
当主键列为not null的时候则一切正常。
注意:采用algorithm=inplace新建主键,虽然避免表的复制,但数据需要重新进行重组的。ALGORITHM=copy则把表拷贝到临时表,然后把临时表的数据插入到新表。
对于alter table … drop primary key 只能采用ALGORITHM=copy,如果强制采用ALGORITHM=inplace会出现错误。
总之,在DDL发起和完成之前(altering table 阶段),允许