哪个 SQL 查询更好，MATCH AGAINST 还是 LIKE?

2021-11-20 00:00:00 sql database search mysql

要在数据库中搜索在任何列foo_desc"和bar_desc"中同时具有关键字foo"和bar"的行，我会执行以下操作:

To search the database for rows that have both keywords "foo" AND "bar" in any of the columns "foo_desc" and "bar_desc", I would do something like:

SELECT * 
FROM t1 
WHERE MATCH (t1.foo_desc, t2.bar_desc) AGAINST ('+foo* +bar*' IN BOOLEAN MODE)

或

SELECT * 
FROM t1 
WHERE (CONCAT(t1.foo_desc, t2.bar_desc) LIKE '%foo%') AND (CONCAT(t1.foo_desc, t2.bar_desc) LIKE '%bar%')

我预计最后一个查询的缺点是性能.

I expect the downside of the last query is performance.

好处是 LIKE 查询找到了xxfoo"，而 MATCH AGAINST 没有找到.

The upside is that the LIKE query finds 'xxfoo' where MATCH AGAINST does not.

哪个是首选或有更好的解决方案?

Which is the preferred one or is there a better solution?

推荐答案

更新

从 MySQL 5.6 及更高版本开始，InnoDB 表支持 Match... Against.

Update

As of MySQL 5.6 and later, InnoDB tables supports Match... Against.

第一个好多更好.在 MyISAM 表上，它将对这些列使用全文索引.另一个将进行全表扫描，对每一行进行连接，然后进行比较.

The first is much better. On MyISAM tables it will use a full text index against those columns. The other will do a full table scan doing a concat on every row and then a comparison.

LIKE 只有在你反对时才有效:

LIKE is only efficient if you're doing it against:

  • 一列(不是函数的结果，除非您的特定数据库供应商支持函数索引——例如 Oracle——并且您正在使用它们)；
  • 列的开头(即 LIKE 'blah%' 而不是 LIKE '%blah%')；和
  • 已编入索引的列.

如果其中任何一个条件不成立，SQL 引擎执行查询的唯一方法就是执行全表扫描.这可以在大约 10-20,000 行下使用.然而，除此之外，它很快就变得无法使用.

If any one of those conditions are not true the only way for the SQL engine to execute the query is by doing a full table scan. This can be usable under about 10-20 thousand rows. Beyond that it quickly becomes unusable however.

注意: MySQL 上 MATCH 的一个问题是它似乎只匹配整个单词，因此搜索 'bla' 不会匹配值为 'blah' 的列，但是搜索bla*"会.

Note: One problem with MATCH on MySQL is that it seems to only match against whole words so a search for 'bla' won't match a column with a value of 'blah', but a search for 'bla*' will.

相关文章