MySQL全文索引：从创建、查询到删除的全方位指南

世界哪有真情
413 阅读7分钟

MySQL全文索引

MySQL全文索引是一种特殊类型的索引，专门用于在文本字段中快速搜索关键字。它以一种特殊的方式存储文本信息，以便能够快速地进行搜索，并找到包含特定关键字的文本记录。全文索引支持在CHAR、VARCHAR和TEXT等字段上创建，并且主要适用于MyISAM和InnoDB存储引擎（在MySQL 5.6及之前的版本中，只有MyISAM存储引擎支持全文索引，但从MySQL 5.7开始，InnoDB存储引擎也开始支持全文索引，但需要进行一些额外的配置）。

全文索引提供了多种查询模式，包括自然语言模式、布尔模式和查询扩展模式。自然语言模式可以根据内容的“相关性”进行排序，非常适合搜索关键词不多时的简单查询。布尔模式则适合更复杂的查询条件，比如包含、排除特定词，甚至按优先级排序。查询扩展模式则适用于关键词特别少的查询，它会先查出初步结果，然后分析这些结果中的共性，扩展查询关键词，帮助获取更多相关结果。

MySQL全文索引与LIKE的区别

  1. 性能区别：

    • 在大数据集上，全文搜索通常比LIKE查询更快，因为它使用了专门的索引结构。
    • LIKE查询通常只支持简单的模糊匹配，而全文搜索则提供了更丰富的查询功能，如多个关键词的搜索、自然语言搜索、布尔搜索等。

  2. 用法区别：

    • 全文搜索使用MATCH()和AGAINST()函数进行查询。
    • LIKE查询则使用LIKE关键字和通配符（如%和_）进行匹配。

  3. 索引区别：

    • 全文搜索需要创建FULLTEXT索引。
    • LIKE查询通常不需要索引（尽管在某些情况下，例如使用前缀匹配时，可以创建B-tree索引来优化性能）。

  4. 适用场景：

    • LIKE查询在某些情况下可以满足简单的模糊匹配需求。
    • 但在处理大量文本数据和复杂查询时，全文搜索通常是一个更好的选择。

综上所述，MySQL全文索引在处理大量文本数据和复杂查询时具有显著的性能优势，并且提供了更丰富的查询功能。而LIKE查询则更适用于简单的模糊匹配场景。因此，在选择使用哪种查询方式时，需要根据具体的应用场景和需求进行权衡。

MySQL 全文索引的创建、查询与删除

创建全文索引

在 MySQL 中，全文索引（Full-Text Index）可以用于加速对文本字段的搜索。你可以在创建表时直接添加全文索引，也可以在已有表中添加。

在创建表时添加全文索引：

	CREATE TABLE products (  

	    id INT AUTO_INCREMENT PRIMARY KEY,  

	    name VARCHAR(255),  

	    description TEXT,  

	    FULLTEXT (name, description)  -- 在 name 和 description 列上添加全文索引  

	) ENGINE=InnoDB;  -- 确保使用 InnoDB 或 MyISAM 引擎

在已创建表中添加全文索引：

	ALTER TABLE products ADD FULLTEXT (name, description);

注意事项：

  • MySQL 只支持 InnoDB 和 MyISAM 引擎的全文索引。
  • 全文检索在 MySQL 5.6 以上版本中才在 InnoDB 引擎支持。

使用全文索引进行查询

建好索引后，可以使用 MATCH() AGAINST() 语法来进行查询。这个方法不仅语法简洁，还能根据相关性排序结果。

自然语言模式查询：

	SELECT * FROM products  

	WHERE MATCH(name, description) AGAINST('苹果手机壳' IN NATURAL LANGUAGE MODE);

在这种模式下，MySQL 会自动给每条记录打分，分数越高说明越相关。你可以使用 ORDER BY 按相关性进行排序。

布尔模式搜索：

布尔模式适合更复杂的查询条件，比如包含、排除特定词，甚至按优先级排序。操作符丰富，比如 + 表示必须包含，- 表示排除。

	SELECT * FROM products  

	WHERE MATCH(name, description) AGAINST('+苹果 -手机壳' IN BOOLEAN MODE);

这里的 +苹果 表示必须包含“苹果”，而 -手机壳 表示排除包含“手机壳”的记录。

查询扩展：

适用于关键词特别少的查询。它会先查出初步结果，然后分析这些结果中的共性，扩展查询关键词，帮助获取更多相关结果。

	SELECT * FROM products  

	WHERE MATCH(name, description) AGAINST('苹果' WITH QUERY EXPANSION);

使用扩展查询时，结果可能会变多，适合需要尽量全面的数据时使用，但也可能会包含一些不太相关的结果。

删除全文索引

有时候，由于数据量变化大或其他原因，可能需要删除全文索引。MySQL 提供了两种方式删除全文索引：

使用 DROP INDEX 删除全文索引：

	DROP INDEX name_description_index ON products;

使用 ALTER TABLE 删除全文索引：

	ALTER TABLE products DROP INDEX name_description_index;

注意事项：

  • 删除全文索引后，MySQL 将不再使用倒排索引，可能会回到全表扫描的时代，效率会变低。
  • 在删除全文索引之前，最好慎重考虑其影响。

扩展

MySQL全文索引与ES（Elasticsearch）搜索引擎的区别

一、存储结构与索引机制

  1. MySQL全文索引

    • MySQL全文索引基于B+树或倒排索引（具体取决于存储引擎，如InnoDB使用倒排索引）。
    • 索引结构相对简单，适用于较小规模的数据集和简单的查询需求。

  2. ES搜索引擎

    • ES使用基于Lucene的倒排索引，这种数据结构专门优化了搜索性能。
    • ES的倒排索引结构更加复杂和高效，适用于大规模数据集和复杂查询需求。

二、搜索效率与性能

  1. MySQL全文索引

    • 在简单查询或单一条件搜索中，MySQL的全文索引可以提供不错的性能。
    • 但在多条件查询或处理大规模数据集时，性能可能会显著下降。

  2. ES搜索引擎

    • ES在处理复杂条件查询时表现更优秀，尤其适合于多条件组合搜索。
    • ES的倒排索引机制使其能够提供更快的搜索速度和更好的扩展性。
    • ES支持实时索引和搜索功能，确保数据写入后立即可进行搜索。

三、适用场景与功能

  1. MySQL全文索引

    • 适用于传统数据库应用，如事务处理、小数据量全文搜索等。
    • 提供了基本的全文搜索功能，但查询语言和功能相对有限。

  2. ES搜索引擎

    • 适用于大数据量、实时搜索、复杂查询场景，如日志分析、全文检索等。
    • 提供了丰富的查询语言和功能，如精确匹配、模糊匹配、短语匹配、聚合等。
    • ES还提供了分布式架构和水平扩展能力，可以轻松地添加节点来提高搜索性能和容量。

四、维护与更新

  1. MySQL全文索引

    • MySQL的全文索引容易维护，与常规的数据库更新操作一致。
    • 但对于非常短的文本字段或频繁更新的数据，全文索引的效果可能不佳。

  2. ES搜索引擎

    • ES的索引更新相对复杂，需要更多的维护操作。
    • 在数据频繁更新的场景下，ES可能需要重新索引，尤其是倒排索引的维护较为复杂和耗时。
    • 但ES提供了自动负载均衡和容错能力，能够保持数据的完整性和服务的可用性。

五、技术栈与集成

  1. MySQL全文索引

    • 如果已有的技术栈主要是PHP、MySQL等，并且对搜索要求不高，可以继续使用MySQL全文索引。

  2. ES搜索引擎

    • 如果技术栈主要是Java、Elasticsearch等，或者需要高性能、高可用性的搜索服务，建议采用ES。
    • ES提供了RESTful API和多种编程语言的客户端，便于开发者进行集成和扩展。

综上所述，MySQL全文索引与ES搜索引擎在存储结构、搜索效率、适用场景、维护与更新以及技术栈与集成等方面都存在显著的区别。在选择使用哪种搜索技术时，需要根据具体的应用场景和需求进行权衡。

avatar
文章
阅读
粉丝