Django admin MySQL 慢 INNER JOIN

2022-01-25 00:00:00 django django-admin mysql

我有一个包含 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 admin 中的列表视图，我得到 4 个查询.其中之一是带有 3 个内部联接的 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?

推荐答案

我已经为 Django ORM 的 INNER JOIN 实现了一个修复，它会在使用 INNER JOIN 进行排序的情况下使用 STRAIGHT_JOIN.我与 Django core-devs 进行了交谈，我们决定暂时将其作为一个单独的后端来做.所以你可以在这里查看: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 个用于获取汽车，另外 3 个使用 car_id IN (...).

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

更新:我发现了另一种解决方法.在 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.

相关文章