MySQL5.6 OnlineDDL对全文索引变更,mysql5.6onlineddlMySQL5.6手册

MySQL5.6 OnlineDDL对全文索引变更,mysql5.6onlineddlMySQL5.6手册

本文是onlineDDL 对Innodb全文索引的变更测试的。MySQL5.6手册(<a href="http://dev.mysql.com/doc/refman/5.6/en/innodb-create-index-overview.html">http://dev.mysql.com/doc/refman/5.6/en/innodb-create-index-overview.html</a>)

中有这样的解释: (除非用户提供FTS_DOC_ID列，否则创建第一个全文索引的过程包含表的复制。后续在该表可以就地(inplace)创建全文索引)。下面对此进行相应的测试。

测试目的有以下几点:

(1):FTS_DOC_ID对全文索引创建的影响
(2):onlineDDL 对全文索引的变更是否并发DML

用例1<!--more-->

mysql> create table full_table(id bigint   auto_increment not null primary key,a text,b text);
Query OK, 0 rows affected (0.09 sec)
mysql> insert into full_table  select NULL,TABLE_CATALOG,COLUMN_COMMENT from big_table where TABLE_CATALOG !='' and COLUMN_COMMENT!='';
Query OK, 29696 rows affected (12.58 sec)
Records: 29696  Duplicates: 0  Warnings: 0
mysql> alter table full_table add fulltext(a,b),algorithm=inplace;
Query OK, 0 rows affected, 1 warning (1.72 sec)
Records: 0  Duplicates: 0  Warnings: 1
mysql> show warnings;
+---------+------+--------------------------------------------------+
| Level   | Code | Message                                          |
+---------+------+--------------------------------------------------+
| Warning |  124 | InnoDB rebuilding table to add column FTS_DOC_ID |
+---------+------+--------------------------------------------------+
1 row in set (0.00 sec)
mysql> show create table full_table \G
*************************** 1. row ***************************
       Table: full_table
Create Table: CREATE TABLE `full_table` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `a` text,
  `b` text,
  PRIMARY KEY (`id`),
  FULLTEXT KEY `idx` (`a`,`b`)
) ENGINE=InnoDB AUTO_INCREMENT=32768 DEFAULT CHARSET=utf8
1 row in set (0.00 sec)
mysql> show profile for query 17;
+--------------------------------+----------+
| Status                         | Duration |
+--------------------------------+----------+
| starting                       | 0.000103 |
| checking permissions           | 0.000008 |
| init                           | 0.000002 |
| Opening tables                 | 0.000056 |
| setup                          | 0.000055 |
| creating table                 | 0.004234 |
| After create                   | 0.000206 |
| System lock                    | 0.000010 |
| preparing for alter table      | 0.116634 |
| altering table                 | 1.610268 |
| committing alter table to stor | 0.161678 |
| end                            | 0.000033 |
| query end                      | 0.000049 |
| closing tables                 | 0.000008 |
| freeing items                  | 0.000038 |
| cleaning up                    | 0.000019 |
+--------------------------------+----------+

用例2（注:在用例1的基础上再创建新的全文索引）

mysql> alter table full_table add fulltext index abfull (a,b),algorithm=inplace;
Query OK, 0 rows affected (0.41 sec)
Records: 0  Duplicates: 0  Warnings: 0
mysql> show profile for query 35;
+--------------------------------+----------+
| Status                         | Duration |
+--------------------------------+----------+
| starting                       | 0.000103 |
| checking permissions           | 0.000004 |
| checking permissions           | 0.000011 |
| init                           | 0.000004 |
| Opening tables                 | 0.000058 |
| setup                          | 0.000043 |
| creating table                 | 0.004368 |
| After create                   | 0.000158 |
| System lock                    | 0.000015 |
| preparing for alter table      | 0.044202 |
| altering table                 | 0.353713 |
| committing alter table to stor | 0.001960 |
| end                            | 0.000032 |
| query end                      | 0.000053 |
| closing tables                 | 0.000013 |
| freeing items                  | 0.000415 |
| cleaning up                    | 0.000069 |
+--------------------------------+----------+

用例3（注:下面测试和用例1的相同步骤，被省略）

mysql> alter table full_table add fulltext(a,b),algorithm=copy;
Query OK, 29696 rows affected (1.43 sec)
Records: 29696  Duplicates: 0  Warnings: 0
mysql> show profile for query 30;
+----------------------+----------+
| Status               | Duration |
+----------------------+----------+
| starting             | 0.000092 |
| checking permissions | 0.000004 |
| checking permissions | 0.000004 |
| init                 | 0.000003 |
| Opening tables       | 0.000052 |
| setup                | 0.000040 |
| creating table       | 0.003111 |
| After create         | 0.000036 |
| System lock          | 0.144197 |
| copy to tmp table    | 1.175779 |
| rename result table  | 0.108490 |
| end                  | 0.000064 |
| query end            | 0.000052 |
| closing tables       | 0.000012 |
| freeing items        | 0.000672 |
| cleaning up          | 0.000043 |
+----------------------+----------+

用例4

mysql> create table full_table( FTS_DOC_ID bigint unsigned auto_increment not null primary key,a text,b text);
Query OK, 0 rows affected (0.08 sec)
mysql> insert into full_table (a,b) select TABLE_CATALOG,COLUMN_COMMENT from big_table where TABLE_CATALOG !='' and COLUMN_COMMENT!='';
Query OK, 29696 rows affected (8.98 sec)
Records: 29696  Duplicates: 0  Warnings: 0
mysql> alter table full_table add fulltext index abfull (a,b),algorithm=inplace;
Query OK, 0 rows affected (0.75 sec)
Records: 0  Duplicates: 0  Warnings: 0
mysql> show profile for query 37;
+--------------------------------+----------+
| Status                         | Duration |
+--------------------------------+----------+
| starting                       | 0.000082 |
| checking permissions           | 0.000005 |
| checking permissions           | 0.000009 |
| init                           | 0.000004 |
| Opening tables                 | 0.000045 |
| setup                          | 0.000038 |
| creating table                 | 0.004342 |
| After create                   | 0.000130 |
| System lock                    | 0.000012 |
| preparing for alter table      | 0.127797 |
| altering table                 | 0.515199 |
| committing alter table to stor | 0.093827 |
| end                            | 0.000020 |
| query end                      | 0.000041 |
| closing tables                 | 0.000008 |
| freeing items                  | 0.000350 |
| cleaning up                    | 0.000074 |
+--------------------------------+----------+

小结1:从上面四个测试过程中，可以总结如下几点

(1):用例1的表没有指定FTS_DOC_ID列，在创建第一个全文索引时有一个警告，提示我们表隐含添加FTS_DOC_ID列，这列是系统的，使用show create table是看不到该列。

(2):用例2在用例1基础上再创建其他的全文索引，就不会产生用例1中的警告，因为表已经添加 FTS_DOC_ID列。这与手册说明是一致的。

(3):用例3使用copy机制即拷贝表到临时表(profile明显显示),但与用例1是有区别的，用例1还是就地操作(手册上说包含表的复制，这复制并没有复制到临时表，只是对表重新组织)。

(4):用例4不同于用例1的是明确指定FTS_DOC_ID列，首次给表增加全文索引不会产生任何警告，也就避免表的重新组织。效率也有明显提升。在此有一个小小提示

:FTS_DOC_ID列 位置，数据类型 的设置最好和上面的测试用例一致，否则会在不同阶段出现不同错误（如:Incorrect column name 'FTS_DOC_ID'。

Column 'FTS_DOC_ID' is of wrong type for an InnoDB FULLTEXT index）或者测试结果不一致。

2:全文索引的变更并发测试

2.1:默认情况测试并发

session1 DDL                                  session2 DML                                       session3 SELECT
alter table full_table add fulltext         insert into full_table values(null,'aaaa','bbb');   select count(*) from full_table where b like'%master';
index abfull (a,b);                                            
mysql> show processlist;
+----+------+-----------+------+---------+------+---------------------------------+--------------------------------------------------------+
| Id | User | host      | db   | command | time | State                           | info                                                   |
+----+------+-----------+------+---------+------+---------------------------------+--------------------------------------------------------+
| 24 | root | localhost | test | Query   |    4 | altering table                  | alter table full_table add fulltext index abfull (a,b) |
| 25 | root | localhost | test | Query   |    3 | Waiting for table metadata lock | insert into full_table values(null,'aaaa','bbb')       |
| 26 | root | localhost | test | Query   |    2 | Sending data                    | select count(*) from full_table where b like '%master' |
| 27 | root | localhost | NULL | Query   |    0 | init                            | show processlist                                       |
+----+------+-----------+------+---------+------+---------------------------------+--------------------------------------------------------+
mysql> show processlist;
+----+------+-----------+------+---------+------+---------------------------------+--------------------------------------------------------+
| Id | User | Host      | db   | Command | Time | State                           | Info                                                   |
+----+------+-----------+------+---------+------+---------------------------------+--------------------------------------------------------+
| 24 | root | localhost | test | Query   |   25 | altering table                  | alter table full_table add fulltext index abfull (a,b) |
| 25 | root | localhost | test | Query   |   24 | Waiting for table metadata lock | insert into full_table values(null,'aaaa','bbb')       |
| 26 | root | localhost | test | sleep   |   23 |                                 | NULL                                                   |
| 27 | root | localhost | NULL | Query   |    0 | init                            | show processlist                                       |
+----+------+-----------+------+---------+------+---------------------------------+--------------------------------------------------------+

小结1:online DDL对全文变更在默认情况下是不允许DML并发，但允许执行查询操作。

2.2:online DDL在全文索引变更情况下是否lock=none;

mysql> alter table full_table add fulltext index abfull (a,b),algorithm=inplace,lock=none;
ERROR 1846 (0A000): LOCK=NONE is not supported. Reason: Fulltext index creation requires a lock. Try LOCK=SHARED.

小结2:onlineDDL对全文索引变更不支持LOCK=none级别，锁默认级别为shared ,所以不允许DML并发。

总结：

(1):对于要创建全文索引的表，在设计时，可以考虑明确指定FTS_DOC_ID列。
(2):设计时没有指定FTS_DOC_ID列,在后续过程通过alter table添加全文索引，使用online DDL(inplace)虽然会进行数据重新组织，首次效率差点，但后续还是挺好的。
(3):由于在变更过程中，不允许DML并发，尽量避免在高并发下执行该操作。