本页二维码,扫一扫分享到朋友圈
朋友圈

mysql fulltext全文索引优缺点


阅读371 评论0 赞 58返回首页    go 编程与技术  go mysql


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


报错信息是这样的.



mysql> alter table weixin_master.page engine=TokuDB, row_format=TOKUDB_LZMA;

ERROR 1214 (HY000): The used table type doesn't support FULLTEXT indexes

 


 

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 全文索引? 我什么时候使用过这个索引类型了, 来看看他的表结构… 


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> 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来做 “内容+标题”的索引。




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)

 

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字符, 结果是无法命中结果.



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

Empty set (0.00 sec)



 

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)

 

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模式查询手机



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

+--------------------------------------------------------------------------------------------------------------------------------------+

| title                                                                                                                                |

+--------------------------------------------------------------------------------------------------------------------------------------+

| 【邮乐惠】重要的事情说三遍:手机银行转账免费啦!手机银行转账免费啦!手机银行转账免费啦!                                             |

| 【邮乐惠】重要的事情说三遍:手机银行转账免费啦!手机银行转账免费啦!手机银行转账免费啦!                                             |

| 【手机开户】足不出户,手机开户!                                                                                                     |

+--------------------------------------------------------------------------------------------------------------------------------------+

3 rows in set (0.35 sec)

 


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)


 

#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 -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)


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)


 

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('+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> 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方案.


  上一篇:go 数据库分库分表(sharding)系列(一) 拆分实施策略和示例演示
  下一篇:go like优化


评论


用QQ登录管理/创建网站 用微博登录管理/创建网站   发布于:12-13