MySQL5.6 InnoDB中文全文索引测试

来源：互联网发布：中国移动网络优势编辑：程序博客网时间：2024/04/28 15:35

InnoDB引擎对FULLTEXT索引的支持是MySQL5.6新引入的特性，之前只有MyISAM引擎支持FULLTEXT索引。对于FULLTEXT索引的内容可以使用MATCH()…AGAINST语法进行查询。但是无法支持中文全文索引，本文将进行简单测试

中文全文索引测试

表结构如下

CREATE TABLE `tb_customer_3` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`register_name` varchar(100) NOT NULL COMMENT '工商名',
`brand_name` varchar(100) DEFAULT NULL COMMENT '品牌名',
`en_name` varchar(100) DEFAULT NULL COMMENT '英文名',
PRIMARY KEY (`id`),
FULLTEXT KEY `fidx` (`register_name`,`brand_name`,`en_name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
数据如下
mysql> select * from tb_customer_3;
+-------+--------------------------------------------------+------------+---------+
| id | register_name | brand_name | en_name |
+-------+--------------------------------------------------+------------+---------+
| 28152 | 芜湖美的厨卫电器制造有限公司 | NULL | NULL |
| 28153 | 北京凡客尚品电子商务有限公司 | NULL | NULL |
| 28154 | 凡客诚品（北京）科技有限公司 | NULL | NULL |
| 28155 | 瞬联讯通科技（北京）有限公司 | NULL | NULL |
| 28156 | 北京畅捷通讯有限公司 | NULL | NULL |
| 28157 | 北京畅捷通支付技术有限公司 | NULL | NULL |
| 28158 | 畅捷通信息技术股份有限公司 | NULL | NULL |
| 28159 | 北京畅捷科技有限公司 | NULL | NULL |
| 28160 | 中国航天工业科学技术咨询有限公司 | NULL | NULL |
| 28161 | 北京·松下彩色显象管有限公司 | NULL | NULL |
| 28162 | 北京·松下电子部品有限公司 | NULL | NULL |
| 28163 | 北京松下照明光源有限公司 | NULL | NULL |
| 28164 | 松下电气机器（北京）有限公司 | NULL | NULL |
| 28165 | 中新航天科技有限公司 | NULL | NULL |
| 28166 | 北京奔驰汽车有限公司 | NULL | NULL |
| 28167 | 阿莫斯特环保科技（北京）有限公司 | NULL | NULL |
| 28168 | 北京低碳清洁能源研究所 | NULL | NULL |
| 28169 | 北京未来科技城开发建设有限公司 | NULL | NULL |
| 28170 | 北京诺华制药有限公司 | NULL | NULL |
| 28171 | 北京信元电信维护有限责任公司 | NULL | NULL |
+-------+--------------------------------------------------+------------+---------+
显式指定全文检索表源
set global innodb_ft_aux_table="test/tb_customer_3";
通过系统表，就可以查看到底是怎么划分tb_customer_3里的数据
mysql> SELECT * FROM information_schema.INNODB_FT_INDEX_CACHE;
+--------------------------------------------------+--------------+-------------+-----------+--------+----------+
| WORD | FIRST_DOC_ID | LAST_DOC_ID | DOC_COUNT | DOC_ID | POSITION |
+--------------------------------------------------+--------------+-------------+-----------+--------+----------+
| 中国航天工业科学技术咨询有限公司 | 9 | 9 | 1 | 9 | 0 |
| 中新航天科技有限公司 | 14 | 14 | 1 | 14 | 0 |
| 凡客诚品 | 3 | 3 | 1 | 3 | 0 |
| 北京低碳清洁能源研究所 | 17 | 17 | 1 | 17 | 0 |
| 北京信元电信维护有限责任公司 | 20 | 20 | 1 | 20 | 0 |
| 北京凡客尚品电子商务有限公司 | 2 | 2 | 1 | 2 | 0 |
| 北京奔驰汽车有限公司 | 15 | 15 | 1 | 15 | 0 |
| 北京未来科技城开发建设有限公司 | 18 | 18 | 1 | 18 | 0 |
| 北京松下照明光源有限公司 | 12 | 12 | 1 | 12 | 0 |
| 北京畅捷科技有限公司 | 8 | 8 | 1 | 8 | 0 |
| 北京畅捷通支付技术有限公司 | 6 | 6 | 1 | 6 | 0 |
| 北京畅捷通讯有限公司 | 5 | 5 | 1 | 5 | 0 |
| 北京诺华制药有限公司 | 19 | 19 | 1 | 19 | 0 |
| 有限公司 | 4 | 16 | 3 | 4 | 30 |
| 有限公司 | 4 | 16 | 3 | 13 | 30 |
| 有限公司 | 4 | 16 | 3 | 16 | 36 |
| 松下彩色显象管有限公司 | 10 | 10 | 1 | 10 | 8 |
| 松下电子部品有限公司 | 11 | 11 | 1 | 11 | 8 |
| 松下电气机器 | 13 | 13 | 1 | 13 | 0 |
| 畅捷通信息技术股份有限公司 | 7 | 7 | 1 | 7 | 0 |
| 瞬联讯通科技 | 4 | 4 | 1 | 4 | 0 |
| 科技有限公司 | 3 | 3 | 1 | 3 | 24 |
| 芜湖美的厨卫电器制造有限公司 | 1 | 1 | 1 | 1 | 0 |
| 阿莫斯特环保科技 | 16 | 16 | 1 | 16 | 0 |
+--------------------------------------------------+--------------+-------------+-----------+--------+----------+
检索数据
[localhost]root@test05:10:59>select * from tb_customer_3 where match(register_name,brand_name,en_name) against ('*松下*' IN BOOLEAN MODE);
+-------+--------------------------------------------+------------+---------+
| id | register_name | brand_name | en_name |
+-------+--------------------------------------------+------------+---------+
| 28161 | 北京·松下彩色显象管有限公司 | NULL | NULL |
| 28162 | 北京·松下电子部品有限公司 | NULL | NULL |
| 28164 | 松下电气机器（北京）有限公司 | NULL | NULL |
+-------+--------------------------------------------+------------+---------+
[localhost]root@test05:12:43>select * from tb_customer_3 where match(register_name,brand_name,en_name) against ('*奔驰*' IN BOOLEAN MODE);
Empty set (0.00 sec)
说明：
第一个SQL能够检索到数据，是因为分词中包括了松下开头的关键字，第二个SQL无法检索出来数据是因为分词中不包括奔驰开头的关键字。
MySQL 5.6不支持中文全文索引，原因很简单：与英文不同，中文的文字是连着一起写的，中间没有MySQL能找到分词的地方，但是有变通的办法，就是将整句的中文分词，并按urlencode、区位码、base64、拼音等进行编码使之以"字母+数字"的方式存储于数据库中。
或者将MySQL升级至5.7版本，MySQL 5.7开始，MySQL内置了ngram全文检索插件，用来支持中文分词，并且对MyISAM和InnoDB引擎有效。

全文索引和like效率对比(下面是在虚拟机上测试的结果)
tb_customer_1和tb_customer_2索引完全相同，只不过一个是KEY `idx_register` (`register_name`)，一个是FULLTEXT KEY `fidx` (`register_name`)
select sql_no_cache count(*) from tb_customer_1 where register_name like '%中国航天工业科学技术咨询有限公司%';
select sql_no_cache count(*) from tb_customer_2 where match(register_name) against ('*中国航天工业科学技术咨询有限公司*' IN BOOLEAN MODE);
第一个SQL查询多次，分别耗费0.09、0.07、0.10、0.12秒
第二个SQL查询多次，分别耗费0.00、0.00、0.00、0.00秒

全文索引和like效率对比(线上测试结果)
select sql_no_cache count(*) from tb_customer t0 where (t0.register_name like '%北京互联企信信息技术有限公司南宁分公司%' or t0.brand_name like '%北京互联企信信息技术有限公司南宁分公司%' or t0.en_name like '%北京互联企信信息技术有限公司南宁分公司%' );
多次查询测试结果：0.18、0.34、0.35、0.34
添加全文索引
alter table tb_customer add fulltext fidx (register_name,brand_name,en_name);
select sql_no_cache count(*) from tb_customer t0 where match(t0.register_name,t0.brand_name,t0.en_name) against ('*北京互联企信信息技术有限公司南宁分公司*');
多次查询测试结果：0.00、0.00、0.01、0.00

注意事项
<1>MATCH()函数中的列必须与FULLTEXT索引中的列相同。如MATCH(register_name,brand_name,en_name)与FULLTEXT(register_name,brand_name,en_name)。若要单独搜索某列，如register_name列，则需另外单独为该列建全文索引FULLTEXT(register_name)，然后用MATCH(register_name)搜索。
<2>innodb全文索引最小分词长度由参数innodb_ft_min_token_size控制，一般修改为1。最大分词长度由innodb_ft_max_token_size控制，一般不需要修改
innodb_ft_min_token_size最小值默认是3，也就是说，mysql fulltext只会使用3个字或者3个字以上的关键字进行建立索引操作。
<3>创建全文索引时会加共享锁，不支持并发DML，即会锁表
<4>目前MySQL支持在CHAR、VARCHAR、TEXT类型的列上定义全文索引
<5>默认全文索引不区分大小写，若要分大小写，column的character set要从utf8修改成utf8_bin
<6>全文索引并不是能搜索出字符串里面的任意字符，只能搜索出有ft_boolean_syntax变量的片段开头的字符串。如ba，无法通过a进行搜索，但是b,a却可以通过a进行搜索;b,abc可以通过布尔模式下的a*进行搜索，也可以通过布尔模式下的b*进行搜索，但是无法通过布尔模式下的c*进行搜索。
<7>MySQL5.6不支持中文全文索引，原因很简单：与英文不同，中文的文字是连着一起写的，中间没有MySQL能找到分词的地方，但是有变通的办法，就是将整句的中文分词，并按urlencode、区位码、base64、拼音等进行编码使之以"字母+数字"的方式存储于数据库中。
从MySQL5.7开始，MySQL内置了ngram全文检索插件，用来支持中文分词，并且对MyISAM和InnoDB引擎有效。

参考文章

http://www.actionsky.com/docs/archives/163
http://imysqldba.blog.51cto.com/1222376/d-2
http://blog.csdn.net/zyz511919766/article/details/12780173

0 0