MySQL 全文搜索 (FULLTEXT) vs 合并字段模糊查询 (LIKE) 对比

在 MySQL 中，使用 FULLTEXT 索引通常比将多个字段合并到一个新字段（如 search_text）中进行模糊查询更快，尤其是在数据量较大时。以下是详细对比：

1. FULLTEXT 索引的优势

专门优化：FULLTEXT 索引是为全文搜索设计的，能够高效处理文本匹配。

分词搜索：FULLTEXT 支持分词搜索（基于空格或特定字符），可以快速定位关键词。

性能更好：对于大文本字段或多字段联合搜索，FULLTEXT 的性能通常优于 LIKE 的模糊查询。

支持布尔搜索：可以使用 +、- 等操作符实现更复杂的查询逻辑。

示例：

CREATE FULLTEXT INDEX idx_search ON orders(model_name, order_number, customer_name);
SELECT * FROM orders WHERE MATCH(model_name, order_number, customer_name) AGAINST('keyword');

2. 合并字段到 search_text 的劣势

模糊查询效率低：使用 LIKE ‘%keyword%’ 进行模糊查询时，数据库无法有效利用索引，尤其是 % 在开头时，会导致全表扫描。

数据冗余：需要额外存储 search_text 字段，增加了存储空间。

更新成本高：当任一字段更新时，search_text 也需要同步更新，增加了维护成本。

查询性能差：随着数据量增大，LIKE 查询的性能会显著下降。

示例：

SELECT * FROM orders WHERE search_text LIKE '%keyword%';

3. 性能对比

小数据量：两者性能差异可能不明显。

大数据量：FULLTEXT 索引的性能优势会非常明显，尤其是在多字段联合搜索时。

查询复杂度：FULLTEXT 支持更复杂的查询（如布尔搜索、权重分配），而 LIKE 只能实现简单的模糊匹配。

4. 适用场景

FULLTEXT 索引：

适合需要高效全文搜索的场景。

适合多字段联合搜索。

适合大数据量和高并发查询。

合并字段到 search_text：

适合数据量小、查询简单的场景。

适合无法使用 FULLTEXT 索引的数据库（如某些 NoSQL 数据库）。

5. 总结

FULLTEXT 索引：更快、更高效，适合大多数全文搜索场景。

合并字段到 search_text：简单但性能较差，适合小数据量或简单需求。

如果系统需要高效查询且数据量较大，建议使用 FULLTEXT 索引。如果数据量小且查询简单，可以使用合并字段的方案。