SQL查询性能：LIKE和通配符 vs. IN

Question

我想知道这两个查询之间是否有很大的性能差异。

SELECT 
  `items_id`, `sport_id`, `sport`, `title`, `url`, 
  `Select3`, `Select6`, `id`, `data`,`image` as image,
  concat('index.php?option=sports&item=',`items_id`,'&p=C108-M108') as count_url
FROM 
  qy9zh_dataitems
WHERE Select6 LIKE '%sport%'
ORDER BY `Select9` DESC LIMIT 0, 4


SELECT 
  `items_id`, `sport_id`, `sport`, `title`, `url`, 
  `Select3`, `Select6`, `id`, `data`,`image` as image,
  concat('index.php?option=sports&item=',`items_id`,'&p=C108-M108') as count_url
FROM 
  qy9zh_dataitems 
WHERE Select6 in ('sport') 
ORDER BY `Select9` DESC LIMIT 0, 4

查询已经正常工作了，两个都是。只是担心性能问题 :)

编辑：在进行一些测试时发现一个奇怪的问题：

SELECT * FROM qy9zh_dataitems WHERE Select6 in ('kvinna') LIMIT 0, 40000

共有29,051项结果，查询耗时0.2581秒。

SELECT * FROM qy9zh_dataitems WHERE Select6 LIKE ('%kvinna%') LIMIT 0, 40000

总共 29,113 条记录，查询耗时 0.2218 秒

- Mackelito

---

你知道两个查询有不同的结果吗？ - dani herrera

结果并不是问题，只是对性能很好奇 :) - Mackelito

首次查询时，击杀性能问题是一个起始的通配符。 - dani herrera

你在编辑器中得到的结果可能是因为你立即连续运行了它们；缓存会影响结果。只需执行 EXPLAIN PLAN 并查看输出 - LIKE 查询将显示需要进行 TABLE SCAN，这意味着由于起始通配符必须检查每一行。 - Ken White

这两个查询的意思不一样，你应该将 Select6 in ('kvinna') 与 Select6 = 'kvinna' 进行比较。两者都是查找 Select6 列等于 'kvinna' 的行。然而，Select6 LIKE ('%kvinna%') 查找 Select6 包含 'kvinna' 的行，如 'abc kvinna xyz'。 - Olivier Jacot-Descombes

Answer 1

如果在某一列上定义了索引，并且您正在查找该列的开头，例如

name LIKE 'Jo%' 

索引可用于加速查询。但是，如果您要查找单词的任何部分，例如：

name LIKE '%man%' 

索引无法使用。

IN子句通常非常快，但我不知道它是否可以利用索引。

查询速度取决于适当索引的可用性、表的大小、值的分布（许多行具有相同的值，每行具有不同的值）以及查询本身。我认为您的问题无法一般性地回答。进行测试并比较！

- Olivier Jacot-Descombes

Answer 2

这两个查询根本不相同，因此无法进行性能比较。

第二个查询可以使用索引 Select6 来限制行数，使其等于 sport，而第一个查询必须查看每一行，看它是否包含 Select6 内容中的任何部分。你试图比较苹果和橙子。

- Ken White

---

抱歉...我不是后端/数据库专家...只是个简单的前端开发者 ;) 有没有什么办法可以在第一次查询中使用索引？我的问题是第二个查询让我使用通配符 :/ - Mackelito

不，当你在使用通配符开头的LIKE时，不能使用索引。考虑纯文本意义：“给我所有以0个或多个字符开头，然后包含单词'sport'，最后有任何东西（或没有）的行。”由于匹配可以是sport或abracadabara sports are like magic，所以没有办法使用索引来进行搜索。如果你可以将其限制为特定的单词开头（LIKE 'sport%'），那么索引可以在某种程度上帮助（尽管没有使用LIKE的查询仍然可能稍微快一些，因为服务器可以限制行数）。 - Ken White

Answer 3

这些是非常不同的标准。

WHERE Select6 LIKE '%sport%'

我会寻找包含“sport”一词的select6情况。

WHERE Select6 in ('sport') 

我将寻找选择6为单词“sport”的情况。

IN条件允许您指定可能值的列表，并且如果您已经对字段进行了索引，则应该非常快。尤其是在字符串中查找任何位置的单词的like查询可能很难索引。Where select6 like 'sport%'将寻找选择6以单词sport开头的情况，并且可被索引。

- Brian Hoover

Answer 4

正如其他人所发表的，基本索引无法用于带有前导通配符的搜索。如果您认为这些查询将是最常见的，您需要查看MySQL中的全文搜索功能。

全文搜索提供了一种有效的方式来查找基于文本中单个单词的记录。但即使如此，仍然会存在限制（例如，搜索单词“port”不一定会返回类似“sport”的记录）。

MySQL全文搜索

- Gary - Stand with Ukraine