mysql 全文本索引详解数据库

简介

全文索引主要用于关键词查询，就像“百度查询java”一样，java就是这个关键词，全文索引可以用在char、varchar、text字段上。
innodb 从5.6.4开始支持全文索引，之前版本都不支持。
myisam一直支持全文索引。

用法

创建

create fulltext index idx_name on table_name(column_name1,[column_name2,column_name3])

查询

select * from table_name where match(fulltext_col_name) against('key_word')

举例

CREATE TABLE `test_fulltext` ( 
  `id` int(11) NOT NULL AUTO_INCREMENT, 
  `description` text, 
  PRIMARY KEY (`id`), 
  FULLTEXT KEY `idx_desc` (`description`) 
) ENGINE=MyISAM

说明

match函数返回浮点数，表示相关度，返回的结果集相关度由高到低。相关度与关键词的次数、位置都有关系。如上例china在第一条中位置是第一个，第二条就相对靠后，所以，第一条的相关度是0.36大于第二条的0.35。

全文索引返回的结果集是按照相关度排列的，这一点区别like、正则表达式；like、正则表达式按顺序返回，没有相关度的概念。

查询扩展

查询扩展会放宽结果集，查询会执行两遍。第一次，根据关键词获取匹配行，从匹配行获取有用的关键字，第二次根据匹配行的关键词执行查询。如下图，我们可以看到，相比上次查询多了第三条而且相关度是0，这说明它跟关键词没有关系，但是它跟第二条的one-month关联，所以被查询出来。

查询扩展对照咱们的应用场景，可以类比，百度 linux时，在侧边栏出现bsd、李纳斯、gnu类似。

布尔搜索

上述查询还不能满足某些查询，比如查询，包含某些关键词但不包括某些词、有某词的记录出现在前边、有某词的在后边，这些就用到了布尔搜索。
比如，下例只搜索含有china，不能含有sweeping的行

布尔搜索的全部操作符可以通过变量,ft_boolean_syntax查看

• +必须含有
• -必须不含有
• >包含，增加权重
• <包含，减少权重
• *通配符，任意个字符，跟正则表达式一样
• “”定义短语，如下，第一个是搜索含有hello world短语的行，第二个搜索含有hello或者world的行
  
  • select * from test_fulltext where match(description) against('"hello world"')
  • select * from test_fulltext where match(description) against('hello world')

全文索引的原理

将索引列拼成一个字符串，建立索引。索引是双b-tree（平衡树）结构，索引词是一棵树，文档指针一棵树。类似聚簇索引和二级索引，聚簇索引相当于文档指针树，二级指针相当于关键词索引树。
关键词索引有些规则，太常用的词、太短的词、在停用词表中收录的词都不予生成索引。

注意事项

• 短词不予索引，短词的定义由系统中ft_min_word_len决定，一般来说是4。少于4个的单词不予索引。
• 搜索的结果受系统变量影响，如
  
  • ft_max_word_len：单词最大长度
  • ft_min_word_len：单词最小长度
  • ft_stopword_file：停用词文件
  • 
• 索引列文本值太短不予索引，查询的结果集中不会返回。
• 索引词的行数超过50%，不予返回，在布尔模式下失效。
• 如果使用了全文本索引，其它索引都不会使用。

可能遇到的问题

全文索引暂时不支持中文，需要sphinx、lucene等插件配合。