MySQl全文索引

　　通过数值比较、范围过滤等就可以完成绝大多数我们需要的査询了。但是，如果你希望通过关键字的匹配来进行查询过滤，那么就需要基于相似度的查询，而不是原来的精确数值比较。全文索引就是为这种场景设计的。

　　全文索引有着自己独特的语法。没有索引也可以工作，如果有索引效率会更高。用于全文搜索的索引有着独特的结构，帮助这类查询找到匹配某些关键字的记录。

　　你可能没有在意过全文索引，不过至少应该对一种全文索引技术比较熟悉：互联网搜索引擎。虽然这类搜索引擎的索引对象是超大量的数据，并且通常其背后都不是关系型数据库，不过全文索引的基本原理都是一样的。 

　　全文索引可以支持各种字符内容的搜索（包括CHAR、VARCHAR和TEXT类型），也支持自然语言搜索和布尔捜索。在MySQL中全文索引有很多的限制,其实现也很复杂，但是因为它是MySQL内置的功能，而且满足很多基本的搜索需求，所以它的应用仍然非常广泛。这里将介绍如何使用全文索引，以及如何为应用设计更高性能的全文索引。

　　5.5之前，在标准的MySQL中，只有MyISAM引擎支持全文索引。MySQL5.6中，InnoDB已经实验性质地支持全文索引了。除此，还有第三方的存储引擎，如Groonga，也支持全文索引。

　　事实上，MyISAM对全文索引的支持有很多的限制，例如表级别锁对性能的影响、数据文件的崩溃、崩溃后的恢复等，这使得MyISAM的全文索引对于很多应用场景并不合适。所以，多数情况下我们建议使用别的解决方案，例如Sphinx、Lucene、Solr、Groonga、Xapian或者Senna,再或者可以等MySQL5.6版本正式发布后，直接使用InnoDB的全文索引。如果MyISAM的全文索引确实能满足应用的需求，那么可以继续阅读本节。

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

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

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

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

　　这也意味着不能在MATCH AGAINST子句中指定哪个列的相关性更重要。通常构建一个网站的搜索引擎是需要这样的功能，例如，你可能希望优先捜索出那些在标题中出现过的文档对象。如果需要这样的功能，则需要编写更复杂的査询语句。（后面将会为大家展示如何实现。）

 

1.自然语言的全文索引

　　自然语言搜索引擎将计算每一个文档对象和査询的相关度。这里，相关度是基于匹配的关键词个数，以及关键词在文档中出现的次数。在整个索引中出现次数越少的词语，匹配时的相关度就越高。相反，非常常见的单词将不会搜索，即使不在停用词列表中出现，如果一个词语在超过50%的记录中都出现了，那么自然语言搜索将不会搜索这类词语。（在测试使用时的一个常见错误就是，只是用很小的数据集合进行全文索引，所以总是无法返回结果。 原因在于，每个搜索关键词都可能在一半以上的记录里面出现过。）

　　全文索引的语法和普通査询略有不同。可以根据WHERE子句中的MATCH AGAINST来区分査询是否使用全文索引。我们来看一个示例。在标准的数据库Sakila中，数据表film_text在字段title和description上建立了全文索引：

mysql> SHOW INDEX FROM sakila.film_text;
+-----------+-----------------------+-------------+------------+
| Table     | Key_name              | Column_name | Index_type |
+-----------+-----------------------+-------------+------------+
| ...
| film_text | idx_title_description | title       | FULLTEXT   |
| film_text | idx_title_description | description | FULLTEXT   |
+-----------+-----------------------+-------------+------------+

　　下面是一个使用自然语言搜索的査询：

mysql> SELECT film_id, title, RIGHT(description, 25),
    ->    MATCH(title, description) AGAINST('factory casualties') AS relevance
    -> FROM sakila.film_text
    -> WHERE MATCH(title, description) AGAINST('factory casualties');
+---------+-----------------------+---------------------------+-----------------+
| film_id | title                 | RIGHT(description, 25)    | relevance       |
+---------+-----------------------+---------------------------+-----------------+
|     831 | SPIRITED CASUALTIES   | a Car in A Baloon Factory | 8.4692449569702 |
|     126 | CASUALTIES ENCINO     | Face a Boy in A Monastery | 5.2615661621094 |
|     193 | CROSSROADS CASUALTIES | a Composer in The Outback | 5.2072987556458 |
|     369 | GOODFELLAS SALUTE     | d Cow in A Baloon Factory | 3.1522686481476 |
|     451 | IGBY MAKER            | a Dog in A Baloon Factory | 3.1522686481476 |

　　MySQL将搜索词语分成两个独立的关键词进行搜索，搜索在title和description字段组成的全文索引上进行。注意，只有一条记录同时包含全部的两个关键词，有三个查询结果只包含关键字“casualties”（这是整个表中仅有的三条包含该关键词的记录），这三个结果都在结果列表的前面。这是因为查询结果是根据与关键词的相似度来进行排序的。

　　提示：和普通査询不同，这类査询自动按照相似度进行排序。在使用全文索引进行排序的时候，MySQL无法再使用索引排序。所以如果不想使用文件排序的话，那么就不要在查询中使用ORDER BY子句。

　　从上面的示例可以看到，函数MATCH()将返回关键词匹配的相关度，是一个浮点数字。你可以根据相关度进行匹配，或者将此直接展现给用户。在一个査询中使用两次MATCH()函数并不会有额外的消耗，MySQL会自动识别并只进行一次搜索。不过，如果你将MATCH()函数放到ORDER BY子句中，MySQL将会使用文件排序。

　　在MATCH()函数中指定的列必须和在全文索引中指定的列完全相同，否则就无法使用全文索引。这是因为全文索引不会记录关键字是来自哪一列的。

　　这也意味着无法使用全文索引来査询某个关键字是否在某一列中存在。这里介绍一个绕过该问题的办法：根据关键词在多个不同列的全文索引上的相关度来算出排名值，然后依此来排序。我们可以在某一列上加上如下索引：

mysql> ALTER TABLE film_text ADD FULLTEXT KEY(title) ;

　　这样，我们可以将title匹配乘以2来提高它的相似度的权重：

mysql> SELECT film_id, RIGHT(description, 25),
    -> ROUND(MATCH(title, description) AGAINST('factory casualties'), 3)
    ->    AS full_rel,
    -> ROUND(MATCH(title) AGAINST('factory casualties'), 3) AS title_rel
    -> FROM sakila.film_text
    -> WHERE MATCH(title, description) AGAINST('factory casualties')
    -> ORDER BY (2 * MATCH(title) AGAINST('factory casualties'))
    ->    + MATCH(title, description) AGAINST('factory casualties') DESC;
+---------+---------------------------+----------+-----------+
| film_id | RIGHT(description, 25)    | full_rel | title_rel |
+---------+-------------- ------------+----------+-----------+
|     831 | a Car in A Baloon Factory |    8.469 |     5.676 |
|     126 | Face a Boy in A Monastery |    5.262 |     5.676 |
|     299 | jack in The Sahara Desert |    3.056 |     6.751 |
|     193 | a Composer in The Outback |    5.207 |     5.676 |
|     369 | d Cow in A Baloon Factory |    3.152 |     0.000 |
|     451 | a Dog in A Baloon Factory |    3.152 |     0.000 |
|     595 | a Cat in A Baloon Factory |    3.152 |     0.000 |
|     649 | nizer in A Baloon Factory |    3.152 |     0.000 |

　　因为上面的査询需要做文件排序，所以这并不是一个髙效的做法。

 

2.布尔全文索引

　　在布尔搜索中，用户可以在査询中自定义某个被搜索的词语的相关性。布尔捜索通过停用词列表过滤掉那些“噪声”词，除此之外，布尔搜索还要求搜索关键词长度必须大于ft_min_word_len，同时小于ft_max_word_len。（事实上，全文索引根本不会对太短或者太长的词语进行索引，但是这里说的不是一回事。一般地，MySQL本身并不会因为搜索关键词过长或过短而忽略这些词语，但是查询优化器的某些部分却可能这样做。）搜索返回的结果是未经排序的。

　　当编写一个布尔搜索査询时，可以通过一些前缀修饰符来定制搜索。表7-3列出了最常用的修饰符。

表7-3:布尔全文索引通用修饰符
Example	Meaning
dinosaur	包含“dinosaur”的行rank值更高
~dinosaur	包含“dinosaur”的行rank值更低
+dinosaur	行记录必须包含“dinosaur”
-dinosaur	行记录不可以包含“dinosaur”
dino*	包含以“dino”开头的单词的行rank值更高

　　还可以使用其他的操作，例如使用括号分组。基于此，就可以构造出一些复杂的搜索査询。

　　还是继续用sakila.film_text来举例，现在我们需要搜索既包含词“factory”又包含 “casualties”的记录。在前面，我们已经使用自然语言搜索查询实现找到这两个词中的任何一个的SQL写法。使用布尔捜索査询，我们可以指定返回结果必须同时包含“factory”和“casualties ”：

mysql> SELECT film_id, title, RIGHT(description, 25)
    -> FROM sakila.film_text
    -> WHERE MATCH(title, description)
    ->    AGAINST('+factory +casualties' IN BOOLEAN MODE);
+---------+---------------------+---------------------------+
| film_id | title               | RIGHT(description, 25)    |
+---------+---------------------+---------------------------+
|     831 | SPIRITED CASUALTIES | a Car in A Baloon Factory |
+---------+---------------------+---------------------------+

　　査询中还可以使用括号进行“短语搜索”，让返回结果精确匹配指定的短语：

mysql> SELECT film_id, title, RIGHT(description, 25)
    -> FROM sakila.film_text
    -> WHERE MATCH(title, description)
    ->    AGAINST('"spirited casualties"' IN BOOLEAN MODE);
+---------+---------------------+---------------------------+
| film_id | title               | RIGHT(description, 25)    |
+---------+---------------------+---------------------------+
|     831 | SPIRITED CASUALTIES | a Car in A Baloon Factory |
+---------+---------------------+---------------------------+

　　短语捜索的速度会比较慢。只使用全文索引是无法判断是否精确匹配短语的，通常还需要査询原文确定记录中是否包含完整的短语。由于需要进行回表过滤，所以速度会很慢。

　　要完成上面的査询，MySQL需要先从索引中找出所有同时包含“spirited”和“casualties”的索引条目，然后取出这些记录再判断是否是精确匹配短语。因为这个操作会先从索引中过滤出一些记录，所以通常认为这样做的速度是很快的——比LIKE操作要快很多。事实上，这样做的确很快，但是搜索的关键词不能是太常见的词语。如果搜索的关键词太常见，因为前一步的过滤会返回太多的记录需要判断，因此LIKE操作反而更快。这种情况下LIKE操作是完全的顺序读，相比索引返回值的随机读，会快很多。

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

 

3.MySQL 5.1中全文索引的变化

　　在MySQL5.1开始引入了一些和全文索引相关的改进，包括一些性能上的提升和新增插件式的解析，通过此用户可以自己定制增强搜索功能。例如，插件可以改变索引文本的方式。可以用更灵活的方式进行分词（例如，可以指定C++作为一个单独的词语）、预处理、可以对不同的文档类型进行索引（如PDF),还可以做一些自定义的词干规则。插件还可以直接影响全文搜索的工作方式——例如，直接使用词干进行搜索。

 

4.全文索引的限制和替代方案

　　MySQL的全文索引实现有很多的设计本身带来的限制。在某些场景下这些限制是致命的，不过也有很多办法绕过这些限制。

　　例如，MySQL全文索引中只有一种判断相关性的方法：词频。索引也不会记录索引词在字符串中的位置，所以位置也就无法用在相关性上。虽然大多数情况下，尤其是数据量很小的时候，这些限制都不会影响使用，但也可能不是你所想要的。而且MySQL的全文索引也没有提供其他可选的相关性排序算法。（它无法存储基于相对位置的相关性排序数据。）

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

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

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

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

　　看看这些限制如何影响査询语句。来看一个例子，假设有一百万个文档记录，在文档的作者author字段上有一个普通的索引，在文档内容字段content上有全文索引。现在我们要搜索作者是123,文档中又包含特定词语的文档。很多人可能会按照下面的方式来写査询语句：

... WHERE MATCH(content) AGAINST ('High Performance MySQL')
    AND author = 123;

　　而实际上，这样做的效率非常低。因为这里使用了MATCH AGAINST，而且恰好上面有全文索引，所以MySQL优先选择使用全文索引，即先搜索所有的文档，查找是否有包含关键词的文档，然后返回记录看看作者是否是123。所以这里也就没有使用author字段上的索引。

　　一个替代方案是将author列包含到全文索引中。可以在author列的值前面附上一个不常见的前缀，然后将这个带前缀的值存放到一个单独的filters列中，并单独维护该列（也许可以使用触发器来做维护工作）。

　　这样就可以扩展全文索引，使其包含filters列，上面的査询就可以改写为：

... WHERE MATCH(content, filters)
    AGAINST ('High Performance MySQL +author_id_123' IN BOOLEAN MODE);

　　这个案例中，如果author列的选择性非常高，那么MySQL能够根据作者信息很快地将需要过滤的文档记录限制在一个很小的范围内，这个査询的效率也就会非常好。如果author列的选择性很低，那么这个替代方案的效率会比前面那个更糟，所以使用的时候要谨慎。 

　　全文索引有时候还可以实现一些简单的“边框”搜索。例如，希望搜索某个坐标范围时，将坐标按某种方式转换成文本再进行全文索引。假设某条记录的坐标为X=123和Y=456。可以按照这样的方式交错存储坐标:XY142536,然后对此进行全文索引。这时，希望査询某矩形--X取值100至199,Y取值400至499——范围时，可以在査询直接搜索“+XY14*”。这比使用WHERE条件过滤的效率要高很多。

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

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

　　将需要索引的列额外地冗余在另一个MyISAM表中也是一个办法。在测试库中sakila.film_text就是使用这个策略，这里使用触发器来维护这个表的数据。最后，你还可以使用一个包含内置全文索引的引擎，如Lucene或者Sphinx。

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

 

5.全文索引的配置和优化

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

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

　　提供一个好的停用词表也很重要。默认的停用词表对常用英语来说可能还不错，但是如果是其他语言或者某些专业文档就不合适了，例如技术文档。例如，若要索引一批MySQL相关的文档，那么最好将mysql放入停用词表，因为在这类文档中，这个词会出现得非常频繁。

　　忽略一些太短的单词也可以提升全文索引的效率。索引单词的最小长度可以通过参数ft_min_word_len配置。修改该参数可以过滤更多的单词，让查询速度更快，但是也会降低精确度。还需要注意一些特殊的场景，有时确实需要索引某些非常短的词语。例如，对一个电子消费品文档进行索引，除非我们允许对很短的单词进行索引，否则搜索“cd player”可能会返回大量的结果。因为单词“cd”比默认允许的最短长度4还要小，所以这里只会对“Player”进行搜索，而通常搜索“cd player”的客户，其实对MP3或者DVD播放器并不感兴趣。

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

　　提示：需要注意，当调整“允许最小词长”后，需要通过OPTIMIZE TABLE来重建索引才会生效。另一个参数ft_max_word_len和该参数行为类似，它限制了允许索引的最大词长。

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

　　如果数据集特别大，则需要对数据进行手动分区，然后将数据分布到不同的节点，再做并行的搜索。这是一个复杂的工作，最好通过一些外部的搜索引擎来实现，如Lucene或者Sphinx。我们的经验显示这样做性能会有指数级的提升。