网站怎么在百度搜不到,电子商务网站开发的过程,网页广告调词平台,WordPress和微信公众号自动回复今日研读了《高性能MySQL》一书中的第 7.10 章节#xff1a;MySQL高级特性之全文索引#xff0c;从中学习到了一些专业技能知识。总结如下。 文章目录 全文索引MyISAM全文索引自然语言的全文索引布尔全文索引全文索引的限制和替代方案全文索引的配置和优化 全文索引
全文索…今日研读了《高性能MySQL》一书中的第 7.10 章节MySQL高级特性之全文索引从中学习到了一些专业技能知识。总结如下。 文章目录 全文索引MyISAM全文索引自然语言的全文索引布尔全文索引全文索引的限制和替代方案全文索引的配置和优化 全文索引
全文索引是为 “通过关键字的匹配来进行相似度的查询过滤” 的场景而设计的。
MyISAM全文索引
MyISAM的全文索引作用对象是一个全文集合这可能是某个数据表的一列也可能是多个列。具体的对数据表的某一条记录MySQL会将需要索引的列全部拼接成一个字符串然后进行索引。
MyISAM对全文索引的支持有很多的限制例如表级别锁对性能的影响、数据文件的崩溃、崩溃后的恢复等这使得MyISAM的全文索引对于很多应用场景并不合适。
MyISAM的全文索引是一类特殊的 B-Tree 索引共有两层。第一层是所有关键字然后对于每一个关键字的第二层包含的是一组相关的文档指针全文索引不会索引对象中的所有词语它会根据如下规则过滤一些词语
停用词列表中的词不会被索引。默认的停用词根据通用英语的使用来设置可以使用参数 ft_stopword_file 指定一组外部文件来使用自定义的停用词。对于长度大于 ft_min_word_len 的词语和长度小于 ft_max_word_len 的词语都不会被索引。
全文索引并不会存储关键字具体匹配在哪一行如果需要根据不同的列来进行组合查询那么不需要根据每一列来建立多个这类索引。这也意味着不能在像 MATCH AGAINST 的子句中指定哪个列的相关性更重要 通常构建一个网站的搜索引擎是需要这样的功能例如用户希望优先搜索出那些在标题中出现过的文档对象。如果需要这样的功能则需要编写更复杂的查询。
自然语言的全文索引
自然语言搜索引擎将计算每一个文档对象和查询的相关度。相关度是基于匹配的关键词个数以及关键词在文档中出现的次数。注意是在整个索引中出现次数越少的词语匹配时的相关度就越高。相反非常长常见的单词将不会被搜索即使不再停用词列表中出现如果一次词语在超过50%的记录中都出现了那么自然语言搜索将不会搜索这类词语。 如果进行全文搜索的数据集过小可能无法返回结果。原因在于每个搜索关键词都可能在一半以上的记录里面出现过。 全文索引的语法和不同查询略有不同。可以根据 WHERE 子句中的 MATCH(columns) AGAINST(keyword) 来区分查询是否使用全文索引示例语句如
SELECT id, title, RIGHT(description, 25), MATCH(title, description) AGAINST(factory casualties) AS relevance
FROM sakila.film_text
WHERE MATCH(title, description) AGAINST(factory casualties);上面的语句中MySQL将搜索词 ‘factory casualties’ 分成两个独立的关键词在 title 和 description 字段组成的全文索引上进行搜索。搜索结果将根据关键词的相似度排序列出和普通查询不同这类查询自动按照相似度进行排序。注意在使用全文索引进行排序的时候MySQL无法在使用索引排序。
函数 MATCH() 将返回关键词匹配的相关度是一个浮点数字。在一个查询中使用两次 MATCH() 函数并不会有额外的消耗MySQL会自动识别并只进行一次搜索。不过如果将 MATCH() 函数放到 ORDER BY 子句中MySQL将会使用文件排序。
在 MATCH() 函数中指定的列必须和在全文索引中指定的列完全相同否则就无法使用全文索引。这是因为全文索引不会记录关键字是来自那一列的这也意味着无法使用全文索引来查询某个关键字是否在某一列中存在。
布尔全文索引
在布尔搜索中用户可以自定义某个被搜索的词语的相关性。布尔搜索通过停用词列表过滤掉哪些噪声词除此之外布尔搜索还要求搜索关键词长度必须大于 ft_min_word_len同时小于 ft_max_word_len。与自然语言的全文索引不同布尔全文索引的搜索结果是未经排序的。
布尔搜索通常一些前缀修饰符来定制搜索
dinosaur: 包含 “dinosaur” 的行 rank 值更高~dinosaur: 包含 “dinosaur” 的行 rank 值更低符号 “~” 通常表示取反dinosaur: 行记录必须包含 “dinosaur”-dinosaur: 行记录必须不包含 “dinosaur”dino*: 包含以 “dino” 开头的单词的行 rank 值更高。
还可以使用其他的操作例如使用括号分组构建一些更复杂的搜索查询。这类似于编程语言中的正则表达式模式。示例语句搜索出既包含词 “factory” 又包含 “casualties” 的记录
SELECTid, title, RIGHT(description, 25)
FROM sakila.film_text
WHERE MATCH(title, description) AGAINST(factory casualties IN BOOLEAN MODE);类似的还可以在查询中使用括号进行短语搜索让返回结果精确匹配指定的短语
SELECT id, title, RIGHT(description, 25)
FROM sakila.film_text
WHERE MATCH(title, description) AGAINST(spirited casualties IN BOOLEAN MODE);短语搜索的速度会比较慢。只使用全文索引是无法判断是否精确匹配短语的通常还需要查询原文确定记录中是否包含完整的短语。由于需要进行会表过滤索引速度会很慢。要完成上面的查询MySQL需要先从索引中找出所有同时包含 “spirited” 和 “casualties” 的索引条目然后取出这些记录再判断是否是精确匹配短语。
全文索引的限制和替代方案
MySQL全文索引中只有一种判断相关性的方法词频。索引也不会记录索引词在字符串中的位置索引位置也有无法用在相关性上。MySQL的全文索引也没有提供其它可选的相关性排序算法。
数据量的大小也是一个问题MySQL的全文索引只有全部在内存中的时候性能才非常好。如果内存无法状态全部索引那么搜索速度可能会非常慢尤其在使用精确短语搜索时。相比其他的索引类型写操作的代价会更大
修改一段文本中的 100 个单词需要 100 次索引操作而不是一次。一般来说列长度并不会太影响其它索引类型但是如果是全文索引三个单词的文本和 10000 个单词的文本性能可能会相差几个数量级。全文索引会有更多的碎片可能需要做更多的 OPTIMIZE TABLE 操作。
全文索引还会影响查询优化器的工作
如果查询中使用了 MATCH AGAINST 子句而对应列上又有可用的全文索引那么MySQL就一定会使用这个全文索引即使有其它的更优的索引可用这里包含连续条件判断。全文索引只能用作全文搜索匹配。其它任何操作如 WHERE 条件比较都必须在MySQL完成全文搜索返回记录后才能进行。全文索引不存储索引列的实际值。也就意味着其不可能用作索引覆盖扫描。除了相关性排序全文索引不能用作其它的排序。如果查询需要做相关性以外的排序操作都需要使用文件排序。
这些限制影响着查询下面给出一个直观的例子字段 content 上有全文索引字段 author 上有普通索引
...
WHERE MATCH(content) AGAINST (High Performance MySQL)
AND author 123;即使这里 author 字段创建了普通索引也依然不会被使用。因为这里使用了 MATCH AGAINST而且恰好上面有全文索引所以MySQL优先选择使用全文索引即先搜索所有的文档查找是否有包含关键词的文档然后返回记录看看作者是否是 123。所以这里也就没有使用 author 字段上的索引。
一个替代方案是将 author 列包含到全文索引中。当然不是直接包含在 author 列的值前面附上一个不常见的前缀然后将这个带前缀的值存放到一个单独的 filters 列中并单独维护该列也与可以使用触发器来做维护工作
...
WHERE MATCH(content, filters)
AGAINST (High Performance MySQL author_id_123 IN BOOLEAN MODE);在这个案例中如果 author 列的选择性非常高那么MySQL能够根据作者信息很快地将需要过滤的文档记录限制在一个很小的范围内这个查询的效率也就会非常好。如果 author 列的选择性很低那么这个替代方案的效率会比前面的那个更糟所以使用的时候要谨慎。
虽然只有MyISAM表支持全文索引但如果仍然希望使用InnoDB或其它引擎可以将原表复制到一个备库再将备库上的表改成MyISAM并建上相应的全文索引。如果不希望在另一个服务器上完成查询还可以对表进行垂直拆分将需要索引的列放到一个单独的MyISAM表中。
全文索引的配置和优化
全文索引的日常维护通常能够大大提升性能。“双B-Tree” 的特殊结构、在某些文档中比其他文档要包含多得多的关键字这都使得全文索引比起普通索引有更多的碎片问题。所以需要经常使用 OPTIMIZE TABLE 来较少碎片。如果应用是 I/O 密集型的那么定期地进行全文索引重建可以让性能提升很多。
如果希望全文索引能够高效地工作还需要保证索引缓存足够大从而保证所有的全文索引都能够缓存在内存中。通常可以为全文索引设置单独的键缓存Key cache保证不会被其他的索引缓存挤出内存。
提供一个好的停用词列表忽略一些太短的单词索引单词的最小长度可以通过参数 ft_min_word_len 配置。修改该参数可以过滤更多的单词让查询速度更快但是也会降低精确度。停用词表和允许最小词长都可以通过减少索引词语来提升全文索引的效率但是同时也会降低搜索的精确度。 这些都需要根据实际的应用场景找到合适的平衡点。如果希望同时获得好的性能和好的搜索质量那么需要自己定制这些参数。一个好的办法是通过日志系统来研究用户的搜索行为看看一些异常的查询包括没有结果返回的查询或者返回过多结果的用户查询。通过这些用户行为和被搜索的内容来判断应该如何调整索引策略。 另外要注意当调整 “允许最小词长” 后需要通过 OPTIMIZE TABLE 来重建索引才会生效。另一个参数 ft_max_word_len 也一样。 当向一个有全文索引的表中导入大量数据的时候最好通过命令 DISABLE KEYS 来禁用全文索引然后在导入结束后使用 ENABLE KEYS 来建立全文索引。因为全文索引的更新时一个消耗很大的操作所以上面的细节会帮你节省大量的时间。另外这样还顺便为全文索引做了一次碎片整理工作。
