mysql - 使用通配符搜索多对多关系，性能问题

Question

我正在为应用程序构建数据库，并且正在更大数据集上测试性能问题。我生成了大约 250,000 条位置记录。每个位置可以分配给多个类别，一个类别可以分配给多个位置。我的数据集为每个位置分配了 2-4 个类别。

我想允许用户通过使用通配符搜索过滤应允许的类别来搜索位置。所以也许我想将所有类别与其中的“红色”一词匹配。因此，如果我键入红色，现在它会显示类别标题中包含“红色”的所有位置。此外，我想用相同的字符串通配符搜索位置标题。

我编写了一个查询，它有效，但在大型数据集中性能很差。本质上，我使用的是内部查询，如果设置了限制并且我可以快速找到结果(大约 .05 毫秒)，这很好。如果我没有立即找到任何结果，它似乎遍历了整个数据库，查询大约需要 9-10 秒。

这是我的数据库的简化布局:

locations: id | title | address
categories: id | title
locations_categories: id | location_id | category_id

这是我目前正在使用的查询:

SELECT `id`,`title`,`address`
FROM (`locations`)
WHERE title LIKE '%string%'
AND WHERE id IN (
 SELECT location_id 
 FROM locations_categories 
 JOIN categories ON categories.id = locations_categories.category_id 
 WHERE categories.title LIKE '%string%')

Answer 1

首先，你的主查询只是使用了子查询的值，所以可以重写:

 SELECT location_id 
 FROM locations_categories 
 JOIN categories ON categories.id = locations_categories.category_id 
 WHERE categories.title LIKE '%string%'

但我建议将此查询一分为二——JOIN 对于大数据集来说速度很慢。第一个将获得必要的类别 ID(带分页):

SELECT id
FROM categories
WHERE title LIKE '%string%' LIMIT BY <start>, <step>

然后就可以得到locations_categories:

SELECT location_id FROM locations_categories WHERE category_id IN (...)

然后您将使用您获得的位置 ID 来检索相应的记录:

SELECT * FROM locations WHERE id IN (...)

这 3 个查询加起来会比原来的查询快得多。

此外，请确保您的标题列已编入索引——这可能是瓶颈。但是由于您在搜索词的开头有一个通配符，因此您必须使用 FULLTEXT在这里索引。