Django管理MySQL缓慢INNER JOIN [英] Django admin MySQL slow INNER JOIN

问题描述

我有一个简单的模型，有3个ForeignKey字段。

I have a simple model with 3 ForeignKey fields.

class Car(models.Model):
    wheel = models.ForeignKey('Wheel', related_name='wheels')
    created = models.DateTimeField(auto_now_add=True)
    max_speed = models.PositiveSmallIntegerField(null=True)
    dealer = models.ForeignKey('Dealer')
    category = models.ForeignKey('Category')

对于django管理员中的列表视图，我得到4个查询。其中一个是具有3个INNER JOINS的SELECT。那一个查询是缓慢的方式。用STRAIGHT_JOIN替换INNER JOIN可以解决问题。有没有办法在评估管理员生成的查询之前补丁？

For the list view in the django admin i get 4 queries. One of them is a SELECT with 3 INNER JOINS. That one query is way to slow. Replacing the INNER JOINs with STRAIGHT_JOIN would fix the issue. Is there a way to patch the admin generated query just before it is evaluated?

推荐答案

我已经实现了INNER JOIN的修复对于Django ORM，如果使用INNER JOIN订购，它将使用STRAIGHT_JOIN。我和Django的核心开发人员进行了交谈，我们决定现在做一个单独的后台。所以你可以在这里查看： https://pypi.python.org/pypi/django -mysql-fix

I've implemented a fix for INNER JOIN for Django ORM, it will use STRAIGHT_JOIN in case of ordering with INNER JOINs. I talked to Django core-devs and we decided to do this as a separate backend for now. So you can check it out here: https://pypi.python.org/pypi/django-mysql-fix

然而，还有另外一种解决方法。使用James答案的代码片段，但用以下替换select_related：

However, there is one other workaround. Use a snippet from James's answer, but replace select_related with:

qs = qs.select_related('').prefetch_related('wheel', 'dealer', 'category')

它将取消INNER JOIN并使用4个单独的查询： 1，以 car_id IN（...）获取汽车和3人。

It will cancel INNER JOIN and use 4 separate queries: 1 to fetch cars and 3 others with car_id IN (...).

更新： / strong>
我找到了一个解决方法。一旦您在ForeignKey字段中指定了null = True，Django将使用LEFT OUTER JOIN而不是INNER JOIN。在这种情况下，LEFT OUTER JOIN在没有性能问题的情况下工作，但您可能会遇到我还没有意识到的其他问题。

UPDATE: I've found one more workaround. Once you specify null=True in your ForeignKey field, Django will use LEFT OUTER JOINs instead of INNER JOIN. LEFT OUTER JOIN works without performance issues in this case, but you may face other issues that I'm not aware of yet.

这篇关于Django管理MySQL缓慢INNER JOIN的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！