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

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
发布了21 篇原创文章 · 获赞 10 · 访问量 10万+
展开阅读全文

没有更多推荐了,返回首页

©️2019 CSDN 皮肤主题: 大白 设计师: CSDN官方博客

分享到微信朋友圈

×

扫一扫,手机浏览