前言， 为什么会注意到mysql的fulltext？ nima, 还是上次innodb转成tokudb引擎的事，这次alter修改表引擎的时候，提示percona tokudb是不支持fulltext索引的.
报错信息是这样的.
1 2 3 4 | #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 全文索引？ 我什么时候使用过这个索引类型了, 来看看他的表结构…
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 | #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来做 “内容+标题”的索引。
文章写的不是很严谨，欢迎来喷，另外该文后续有更新的，请到原文地址查看更新。
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.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 | 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字符， 结果是无法命中结果.
1 2 3 4 | #blog: xiaorui.cc
mysql> select title from page where match(title) against('bm') limit 10; Empty set (0.00 sec) |
#同样的bm查询，如果使用in boolean mode查询的化，会发现速度快，而且可以准确的命中.
1 2 3 4 5 6 7 8 9 | 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模式查询手机
1 2 3 4 5 6 7 8 9 10 | #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的用法. 但问题是搜索出来的结果有些怪异，但还能接受.
1 2 3 4 5 6 7 8 9 10 | #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的取出来. 看起来这结果也是合理的.
1 2 3 4 5 6 7 8 9 | 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) |
#我们需要注意的是，只要涉及到汉字查询就不行了，另外涉及到分词不合理的情况也是无法命中的.
1 2 3 4 5 6 7 8 9 | 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字符串的.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 | 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字符串.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 | 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 丰田-皇冠 三款三厢豪华车，浔车团等你来 ! ！ | +------------------------------------------------------------------------------------------------------------------+ |
搜索中文的时候最好加上通配符*符号，不然是搜不到汉字.
1 2 3 4 5 6 7 8 9 10 11 12 | 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方案.