mysql - 如何提高 Django 管理搜索相关字段的查询性能 (MySQL)

Question

在 Django 中我有这个:

模型.py

class Book(models.Model):
    isbn = models.CharField(max_length=16, db_index=True)
    title = models.CharField(max_length=255, db_index=True)
    ... other fields ...

class Author(models.Model):
    first_name = models.CharField(max_length=128, db_index=True)
    last_name = models.CharField(max_length=128, db_index=True)
    books = models.ManyToManyField(Book, blank=True)
    ... other fields ...

admin.py

class AuthorAdmin(admin.ModelAdmin):
    search_fields = ('first_name', 'last_name', 'books__isbn', 'books__title')

    ...

我的问题是，当我使用 2 个或更多短期术语从作者管理列表页面进行搜索时，MySQL 开始花费大量时间(对于 3 个术语查询至少需要 8 秒)。我有大约 5000 位作者和 2500 本书。这里的短非常重要。如果我搜索“a b c”，那么 3 个非常短的术语，我就没有足够的耐心等待结果(我至少等了 2 分钟)。相反，如果我搜索“所有蜜蜂线索”，我会在 2 秒内得到结果。所以这个问题看起来确实与相关领域的短期术语有关。

此搜索产生的 SQL 查询有很多 JOIN、LIKE、AND 和 OR 但没有子查询。

我正在使用 MySQL 5.1，但我尝试使用 5.5 但没有成功。

我还尝试将 innodb_buffer_pool_size 增加到一个非常大的值。这没有任何改变。

我现在提高性能的唯一想法是对 isbn 和 title 字段进行非规范化(即将它们直接复制到 Authors 中)，但我必须添加一堆机制使这些字段与 Book 中的真实字段保持同步。

关于如何改进这个查询有什么建议吗？

Answer 1

经过大量调查，我发现问题出在如何为管理搜索字段(在 ChangeList 类中)构建搜索查询。在多词搜索(由空格分隔的单词)中，每个词都通过链接一个新的 filter() 添加到 QuerySet。当 search_fields 中有一个或多个相关字段时，创建的 SQL 查询将有很多 JOIN 一个接一个地链接着许多 JOIN对于每个相关领域(请参阅我的 related question 了解一些示例和更多信息)。这条 JOIN 链在那里，因此每个术语将仅在数据过滤器的子集中由先行术语搜索，并且最重要的是，相关字段只需要一个术语(与需要have ALL terms) 进行匹配。参见 Spanning multi-valued relationships在 Django 文档中获取有关此主题的更多信息。我很确定这是管理员搜索字段大部分时间想要的行为。

此查询(涉及相关字段)的缺点是性能(执行查询的时间)的变化可能非常大。这取决于很多因素:搜索词的数量、搜索的词、字段搜索的种类(VARCHAR 等)、字段搜索的数量、表中的数据、表的大小等。使用正确的组合很容易拥有一个几乎永远需要花费的查询(一个需要超过 10 分钟的查询。对我来说，在这个搜索字段的上下文中是一个需要永远花费的查询)。

之所以会花费这么长时间，是因为数据库需要为每个术语创建一个临时表，并对其进行大部分扫描以搜索下一个术语。所以，这加起来真的很快。

为提高性能可能要做的更改是对同一 filter() 中的所有术语进行 AND 运算。这样，它们将只有一个 JOIN 相关字段(如果是多对多，则为 2 个)，而不是更多。这个查询会快很多并且性能变化非常小。缺点是相关字段必须匹配所有术语，因此，在许多情况下，您可以获得较少的匹配项。

更新

正如 trinchet 所问，这是更改搜索行为所需的内容(针对 Django 1.7)。您需要覆盖您希望进行此类搜索的管理类的 get_search_results()。您需要将基类 (ModelAdmin) 中的所有方法代码复制到您自己的类中。然后你需要改变这些行:

for bit in search_term.split():
    or_queries = [models.Q(**{orm_lookup: bit})
                  for orm_lookup in orm_lookups]
    queryset = queryset.filter(reduce(operator.or_, or_queries))

对此:

and_queries = []
for bit in search_term.split():
    or_queries = [models.Q(**{orm_lookup: bit})
                  for orm_lookup in orm_lookups]
    and_queries.append(Q(reduce(operator.or_, or_queries)))
queryset = queryset.filter(reduce(operator.and_, and_queries))

此代码未经测试。我的原始代码是针对 Django 1.4 的，我只是在此处针对 1.7 对其进行了调整。