mysql全文索引

    全文索引可以支持各种字符内容的搜索，也支持自然语言搜索和布尔搜索。当前只有MyISAM引擎支持全文索引。但是MyISAM对全文索引的支持右很多限制，例如表级别锁对性能的影响、数据文件的崩溃、崩溃后的恢复等，这使得MyISAM的全文索引对于很多应用场景并不合适。

    MyISAM的全文索引作用对象是一个“全文集合”，这可能是某个数据表的一列，也可能是多个列。具体的，对数据表的某一条记录，mysql会将需要索引的列全部拼接成一个字符串，然后进行索引。

    MyISAM的全文索引是一类特殊的b-tree索引，共有两层。第一层是所有关键字，然后对于每一个关键字的第二层，包含的是一组相关的“文档指针”。全文索引不会索引文档对象中的所有词语，他会根据如下规则过滤一些词语：

• 停用列表中的词语都不会被索引。默认的停用词根据通用英语的使用来设置，可以使用参数ft_stopword_file指定一组外部文件来使用自定义的停用词。
• 对于长度大于ft_min_word_len的词语和长度小于ft_max_word_len的词语，都不会被索引。

    全文索引并不会存储关键字具体匹配在哪一列，如果需要根据不同的列来进行组合查询，那么不需要针对每一列来建立多个这类索引。

    这意味着不能在match against子句中指定那个列的相关性更重要。通常构建一个网站的搜索引擎是需要这样的功能，例如，你可能希望优先搜索出那些在标题中出现过的文档对象。如果需要这样的功能，则需要编写更复杂的查询语句。

1、自然语言的全文索引

    自然语言所有引擎将计算每一个文档对象和查询的相关度。这里，相关度是基于匹配的关键词个数，以及关键词在文档中出现的次数。在整个索引中出现次数越少的词语，匹配时的相关度就越高。相反，非常常见的单词就不会搜索，即使不在停用词列表中出现，如果一个词语在超过50%的记录中都出现了，那么自然语言搜索将不会搜索这类词语。

    全文索引的语法和普通词查询略有不同。可以根据where子句中的match against来区分查询是否使用全文索引。函数match将返回关键词匹配的相关度，是一个浮点数字。在一个查询中使用两次match函数并不会有额外的消耗，mysql会自动识别并只进行一次搜索。不过，如果将match函数放到order by子句中，mysql将会使用文件排序。

    在match函数中指定的列必须和在全文索引中指定的列完全相同，否则就无法使用全文索引。这是因为全文索引不会记录关键词是来自那一列的。这也意味着无法使用全文索引来查询某个关键词是否在某一列中存在。

2、布尔全文索引

    在布尔搜索中，用户可以在查询中自定义某个被搜索的词语的相关性。布尔搜索通过停用词列表过滤掉那些“噪声”词，除此之外，布尔搜索还要求搜索关键词长度必须大于ft_min_word_len，同时小于ft_max_word_len。搜索返回的结果是未经排序的。例如：select film_id,title from film_text where match(title,description) against('+factory +casualties' in boolean mode);

    只有MyISAM引擎才能使用布尔全文索引，但并不是一定有全文索引才能使用布尔全文搜索。当没有全文索引的时候，mysql就通过全表扫描来实现。所以，你甚至还可以在多表上使用布尔全文索引，例如在一个关联结果上进行。只不过，因为全表扫描，速度可能会很慢。

3、全文索引的限制和替代方案

    mysql的全文索引实现有很多的设计本身带来的限制。例如：mysql全文索引中只有一种判断相关性的方法--词频。索引也不会记录索引词在字符串中的位置，所以位置也就无法用在相关性上。虽然大多数情况下，数据量很小的时候，这些限制不会影响使用，但也可能不是你所想要的。而且mysql的全文索引也没有提供其他可选的相关性排序算法。

    数据量的大小也是一个问题。mysql的全文索引只有全部在内存中的时候，性能才非常好。如果内存无法装载全部索引，那么搜索速度会非常慢。当你使用精确短语搜索时，想要好的性能，数据和索引都需要在内存中。相比于其他的索引类型，当insert、update和delete操作进行时，全文索引的操作代价非常大：

• 修改一段文本中的100个单词，需要100词索引操作，而不是一次；
• 一般来说列长度并不会太影响其他的索引类型，但是如果是全文索引，三个单词的文本和10 000个单词的文本，性能可能会相差几个数量级。
• 全文索引会有更多的碎片，可能需要做更多的optimize table操作。

    全文索引还会影响查询优化器的工作。索引选择、where子句、oeder by都有可能不是按照你所预想的方式工作：

• 如果查询中使用了match against子句，而对应列上又有可用的全文索引，那么mysql就一定会使用这个全文索引。这时，即使其他的索引可以使用，mysql也不会去比较到底哪个索引的性能更好。所以，即使这时有更合适的索引可以使用，mysql仍然会置之不理。
• 全文索引只能用作全文搜索匹配。任何其他操作，如where条件比较，都必须在mysql完成全文搜索返回记录后才能进行。这和其他普通索引不同。
• 全文索引不存储索引列的实际值。也就不可能用作索引覆盖扫描。
• 除了相关性排序，全文索引不能用作其他的排序。如果查询需要做相关性以外的排序操作，都需要使用文件排序。

    全文索引的一个常用技巧是缓存全文索引返回的主键值，这在分页显示的时候经常使用。当应用程序真的需要输出结果时，才通过主键值将所有需要的数据返回。这个查询就可以自由的使用其他索引、或者自由的关联其他表。

    虽然只有MyISAM表支持全文索引。但是如果仍然希望使用InnoDB或其他引擎，可以将原表复制到一个备库，再将备库上的表修改成MyISAM并建上相应的全文索引。如果不希望在一个服务器上完成查询，还可以对表进行垂直拆分，将需要索引的列放到一个单独的MyISAM表中。

    将需要索引的列额外的冗余在另外一个MyISAM表中也是一个方法。通常可以使用触发器来维护这个表的数据。

    因为使用全文索引的时候，通常会返回大量结果并产生大量随机I/O，如果group by一起使用的话，还需要通过临时表或者文件排序进行分组，性能会非常非常糟糕。这类查询通常只是希望查询分组后的前几名结果，所以一个有效的优化方法是对结果进行抽样而不是精确计算。

4、全文索引的优化

    全文索引的日常维护通常能够大大提升性能。“双b-tree”的特殊结构、在某些文档中比其他文档要包含多得多的关键字，这都使的全文索引比起普通索引有更多的碎片问题。所以需要经常使用optimize table来减少碎片。如果应用是i/o密集型的，那么定期的进行全文索引重建可以让性能提升很多。

    如果希望全文索引能够高效的工作，还需要保证索引缓存足够大，从而保证所有的全文索引都能够缓存在内存中。通常，可以为全文索引设置单独的键缓存，保证不会被其他的缓存挤出内存。

    提供一个好的停用词表也很重要。默认的停用词表对常用的英语来说可能还不错，但是如果其他语言或某些专业文档就不合适了。忽略一些太短的单词也可以提升全文索引的效率。索引单词的最小长度可以通过参数ft_min_word_len配置。修改该参数可以过滤更多的单词，让查询速度更快，但是也会降低精确度。

    停用词表和允许最小词长都可以通过减少索引词语来提升全文索引的效率，但是同时也会降低搜索的精确度。这需要根据应用场景找到合适的平衡点。如果希望同时获得好的性能和搜索质量，那么需要自己定制这些参数。一个好的办法是通过日志系统来研究用户的搜索行为，看看一些异常的查询，包括没有结果返回的查询或者返回过多结果的用户查询。通过这些用户行为和搜索的内容来判断应该如何调整索引策略。

    当向一个有全文索引的表中导入大量数据的时候，最好先通过disable keys来禁用全文索引，然后在导入结束后使用Enable keys来建立全文索引。因为全文索引的更新是一个消耗很大的操作，所以上面的细节会帮助你节省大量时间。另外，这样还顺便为全文索引做了一次碎片整理工作。

    如果数据量特别大，需要对数据进行分区，然后将数据分不到不同的节点，在做并行的搜索。这是一个复杂的工作，最好通过一些外部搜索引擎来实现，如Lucene。