本文是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&gt; 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&gt; 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&gt; 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&gt; show warnings; +---------+------+--------------------------------------------------+ | Level | Code | Message | +---------+------+--------------------------------------------------+ | Warning | 124 | InnoDB rebuilding table to add column FTS_DOC_ID | +---------+------+--------------------------------------------------+ 1 row in set (0.00 sec) mysql&gt; 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&gt; 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&gt; 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&gt; 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&gt; 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&gt; 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&gt; 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&gt; 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&gt; 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&gt; 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&gt; 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&gt; 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&gt; 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并发,尽量避免在高并发下执行该操作。
暂无评论
写评论