浅谈mysql fulltext全文索引优缺点

前言, 为什么会注意到mysql的fulltext?   nima, 还是上次innodb转成tokudb引擎的事,这次alter修改表引擎的时候,提示percona tokudb是不支持fulltext索引的.

报错信息是这样的.

#blog: xiaorui.cc

mysql> alter table weixin_master.page engine=TokuDB, row_format=TOKUDB_LZMA;
ERROR 1214 (HY000): The used table type doesn't support FULLTEXT indexes

FullText 全文索引? 我什么时候使用过这个索引类型了, 来看看他的表结构… 

#blog: xiaorui.cc

mysql> show create table page;

CREATE TABLE `page` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `url` varchar(255) CHARACTER SET utf8 DEFAULT NULL,
  `title` text CHARACTER SET utf8,
  `content` blob,
  `ts` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `url_idx` (`url`) USING HASH,
  KEY `ts_idx` (`ts`) USING BTREE,
  FULLTEXT KEY `title_idx` (`title`) # FULLTEXT KEY 
) ENGINE=InnoDB AUTO_INCREMENT=79808495 DEFAULT CHARSET=utf8mb4;

话说mysql innodb在5.6.4以后就有了fulltext全文索引, 虽然这个表的字段有全文索引,但我这边没用体验过,因为公司用Elasticsearch来做 “内容+标题”的索引。

文章写的不是很严谨,欢迎来喷,另外该文后续有更新的,请到原文地址查看更新。

http://xiaorui.cc/2016/02/03/%E6%B5%85%E8%B0%88mysql-fulltext%E5%85%A8%E6%96%87%E7%B4%A2%E5%BC%95%E7%9A%84%E4%BC%98%E7%BC%BA%E7%82%B9/


Mysql FullText全文索引语法是这样的.

SELECT * FROM articles WHERE match(aname,bname) against(‘+xiaorui -rfyiamcool‘ IN BOOLEAN MODE);

这种语法有3个关键字: + 代表 AND 含有,- 代表 not 不含有, no 代表 OR或 .  


in boolean mode 布尔模式,我推荐大家使用这个模式,往往不加这模式,你会发现啥都搜不到。 Boolean帮你做了一些匹配方面的适配,另外Boolean虽然可以拿到数据,但有些概率在里面的。 

in natural language mode 大小写模式

MySQL的FULLTEXT怎么分词的:


字母、数字、底线的组合视为一个字,不会把底线断字。会被分词的字符有:空白、逗号(,)与点(.),英文一般一个词一个空格,中文就不同了,所以中文需要自己分词了。

#我们先来验证下是否可以匹配到bmw.

mysql> select title from page where match(title) against('bmw') limit 10;
+------------------------------------------------------------------------------------------------+
| title                                                                                          |
+------------------------------------------------------------------------------------------------+
| 【宝诚二手车】BMW X1、BMW 1系、BMW 5系、BMW 5系GT、                                            |
| 【宝诚二手车】BMW 1系、BMW 5系GT、奔驰GLK300、凯迪拉克XTS、BMW5系、君威                        |
| 【宝诚二手车】BMW 1系、BMW 5系GT、奔驰GLK300、凯迪拉克XTS、BMW5系、君威                        |
| 【宝诚二手车】BMW 1系、BMW 5系GT、奔驰GLK300、凯迪拉克XTS、BMW5系、君威                        |
| 全能王者再出击,BMW X5 xDrive28i、BMW X6 xDrive28i即将上市                                     |
| BMW M | 2015 BMW M赛道体验日                                                                   |
| 【荣宝五周年】本周特价车新鲜出炉:BMW 335Li & BMW X3 20i                                   |
| 【宝诚二手车】BMW 1系、BMW 7系、奔驰GLK300、君威、凯迪拉克XTS                                  |
| 【BMW】BMW 3系:缔造传奇 忠于纯粹                                                         |
| BMW X之旅 ▎丝路帝国简史 ——“BMW X之旅”丝路四部曲之一                                       |
+------------------------------------------------------------------------------------------------+
10 rows in set (0.02 sec)

#我们再来搜索下bmw的bm字符, 结果是无法命中结果.

#blog: xiaorui.cc

mysql> select title from page where match(title) against('bm') limit 10;
Empty set (0.00 sec)

#同样的bm查询,如果使用in boolean mode查询的化,会发现速度快,而且可以准确的命中.

mysql> select title from page where match(title) against('*bm*' in boolean mode) limit 3;
+------------------------------------------------------------------------------------------------+
| title                                                                                          |
+------------------------------------------------------------------------------------------------+
| 【宝诚二手车】BMW X1、BMW 1系、BMW 5系、BMW 5系GT、                                            |
| 【宝诚二手车】BMW 1系、BMW 5系GT、奔驰GLK300、凯迪拉克XTS、BMW5系、君威                        |
| 什么是#BMW延保#,为什么选择#BMW延保#。                                                         |
+------------------------------------------------------------------------------------------------+
3 rows in set (0.02 sec)

# 通过boolean模式查询手机

#blog: xiaorui.cc
mysql> select title from page where match(title) against('*手机*' in boolean mode) limit 3;
+--------------------------------------------------------------------------------------------------------------------------------------+
| title                                                                                                                                |
+--------------------------------------------------------------------------------------------------------------------------------------+
| 【邮乐惠】重要的事情说三遍:手机银行转账免费啦!手机银行转账免费啦!手机银行转账免费啦!                                             |
| 【邮乐惠】重要的事情说三遍:手机银行转账免费啦!手机银行转账免费啦!手机银行转账免费啦!                                             |
| 【手机开户】足不出户,手机开户!                                                                                                     |
+--------------------------------------------------------------------------------------------------------------------------------------+
3 rows in set (0.35 sec)

另外match against也存有and not or的用法.   但问题是搜索出来的结果有些怪异,但还能接受.

#match against 把meizu的过滤出来,我们看到这结果是合理的。 
mysql> select title from page where match(title) against('+meizu' in boolean mode) limit 3;
+-----------------------------------------------------------------------------------------------------+
| title                                                                                               |
+-----------------------------------------------------------------------------------------------------+
| 【MEIZU】 魅蓝Note2(16G)4G全国套餐合约机                                                       |
| 【金色】Meizu/魅族  金色 MX5移动4G联通4G 大屏智能手机 包邮 2070像素 带指纹                          |
| 不忘初心,重回高端?MEIZU 魅族 更换品牌 Logo                                                        |
+-----------------------------------------------------------------------------------------------------+
3 rows in set (0.00 sec)

#把含有meizu,不含有logo的取出来. 看起来这结果也是合理的.

mysql> select title from page where match(title) against('+meizu -logo' in boolean mode) limit 3;
+---------------------------------------------------------------------------------------------------------+
| title                                                                                                   |
+---------------------------------------------------------------------------------------------------------+
| 【MEIZU】 魅蓝Note2(16G)4G全国套餐合约机                                                           |
| 【金色】Meizu/魅族  金色 MX5移动4G联通4G 大屏智能手机 包邮 2070像素 带指纹                              |
| 魅族(MEIZU)是中国智能手机厂商,致力于制造设计优雅、简单易用的智能手机。                                 |
+---------------------------------------------------------------------------------------------------------+
3 rows in set (0.00 sec)

#我们需要注意的是,只要涉及到汉字查询就不行了,另外涉及到分词不合理的情况也是无法命中的. 

mysql> select title from page where match(title) against('+meizu -金色' in boolean mode) limit 3;
+-----------------------------------------------------------------------------------------------------+
| title                                                                                               |
+-----------------------------------------------------------------------------------------------------+
| 【MEIZU】 魅蓝Note2(16G)4G全国套餐合约机                                                       |
| 【金色】Meizu/魅族  金色 MX5移动4G联通4G 大屏智能手机 包邮 2070像素 带指纹                          |
| 不忘初心,重回高端?MEIZU 魅族 更换品牌 Logo                                                        |
+-----------------------------------------------------------------------------------------------------+
3 rows in set (0.00 sec)

#我们继续来看看由于分词情况引起的查询误差,下面的语句是含有meizu,不含有pro的匹配,但是我们看到结果里是有pro字符串的.  

mysql> select title from page where match(title) against('+meizu -pro' in boolean mode);
+-------------------------------------------------------------------------------------------------------------------------+
| title                                                                                                                   |
+-------------------------------------------------------------------------------------------------------------------------+
| MEIZU/魅族PRO 5全面开启预订,实体专卖店随时为你准备着...                                                                |
| <福利活动>““LOVE CHINA LOVE MEIZU”                                                                                |
| 【手机】魅族手机pro5 MEIZU MX5 工艺                                                                                    |
| Meizu/魅族 MX5移动版八核时尚 智能拍照手机                                                                          |
| MEIZU 魅族 魅蓝note2 两个月体验                                                                                         |
| 关注公众号 冉子屋 Meizu/魅族 MX5智能大屏手机  地址:平遥县顺城路隆兴源6区东6号                                |
| Meizu Pro/魅族 魅蓝2 2G+16G 1300M+500M 超强性价比                                                                           |
| MEIZU PRO5金色版本已经到货,您还在等什么!有它就够了。。。                                                         |
| MEIZU PRO5金色版本已经到货,您还在等什么!有它就够了。。。                                                         |
| MEIZU PRO5金色版本已经到货,您还在等什么!有它就够了!!!                                                     
+-------------------------------------------------------------------------------------------------------------------------+
30 rows in set (0.01 sec)

#我不想要xts,但是返回的结果确含有xts字符串. 

mysql> select title from page where match(title) against('+bmw -XTS') limit 10;
+------------------------------------------------------------------------------------------------------------------+
| title                                                                                                            |
+------------------------------------------------------------------------------------------------------------------+
| 【宝诚二手车】BMW X1、BMW 1系、BMW 5系、BMW 5系GT、                                                              |
| 【每天一辆特价车】 凯迪拉克 XTS                                                                             |
| XTS.一部你能买到最舒适的豪华轿车                                                                                 |
| 2014年9月 凯迪拉克 XTS 2.0T 28T 舒适型 白色 自动档                            |
| 克莱斯勒-300C 凯迪拉克 -XTS 丰田-皇冠 三款三厢豪华车,浔车团极速店等你来 ! !                               |
| 克莱斯勒-300C 凯迪拉克 -XTS 丰田-皇冠 三款三厢豪华车,浔车团玺沣店等你来 ! !                               |
| 克莱斯勒-300C 凯迪拉克 -XTS 丰田-皇冠 三款三厢豪华车,浔车团HAT动力店等你来 ! !                                 |
| 克莱斯勒-300C 凯迪拉克 -XTS 丰田-皇冠 三款三厢豪华车,浔车团酷车汇店等你来 ! !                                  |
| 克莱斯勒-300C 凯迪拉克 -XTS 丰田-皇冠 三款三厢豪华车,浔车团浔阳店等你来 ! !                               |
| 克莱斯勒-300C     凯迪拉克 -XTS      丰田-皇冠      三款三厢豪华车,浔车团等你来 ! !                            |
+------------------------------------------------------------------------------------------------------------------+

搜索中文的时候最好加上通配符*符号,不然是搜不到汉字.

mysql> select title from page where match(title) against('手机' in boolean mode) limit 3;
Empty set (0.00 sec)

mysql>  select title from page where match(title) against('*手机*' in boolean mode) limit 3;
+--------------------------------------------------------------------------------------------------------------------------------------+
| title                                                                                                                                |
+--------------------------------------------------------------------------------------------------------------------------------------+
| 【邮乐惠】重要的事情说三遍:手机银行转账免费啦!手机银行转账免费啦!手机银行转账免费啦!                                             |
| 【邮乐惠】重要的事情说三遍:手机银行转账免费啦!手机银行转账免费啦!手机银行转账免费啦!                                             |
| 【手机开户】足不出户,手机开户!                                                                                                     |
+--------------------------------------------------------------------------------------------------------------------------------------+
3 rows in set (0.35 sec)

最后总结:

Mysql这fulltext性能看起来还行,组合查询的精度有些差,mysql fulltext跟Elasticsearch的索引都有个打分机制,也就是匹配相似度. 经过我的测试发现fulltext在单个词或者字符查询命中率还是可以的。

其实我们这就算大量的使用Elasticsearch做全文索引,分词是使用Elasticsearch最火的ik加自定义的词库包,但还是经常有因为分词问题引起数据无法命中的情况。

现在不知道mysql5.7是否可以自定义中文分词词库,如果不能的话,果断推荐大家使用Sphinx或Lucene方案.


大家觉得文章对你有些作用! 如果想赏钱,可以用微信扫描下面的二维码,感谢!
另外再次标注博客原地址  xiaorui.cc

发表评论

电子邮件地址不会被公开。 必填项已用*标注