Django管理员在mysql中创建了大量重复的查询 [英] Django admin makes a lot of duplicate queries in mysql

查看：144
本文介绍了Django管理员在mysql中创建了大量重复的查询的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！

问题描述

我遇到了一个问题。



当我尝试打开更改页面以查看元素中的参数（如 djangosite.com/admin/djangoapp/someelement/1/更改/ ）它加载非常慢（10-15秒）。



我发现Django正在做很多重复的查询： / p>

更新：
我认为我在admin.py中犯了错误。

  inlines = [PhoneInline，FlatInline，NeedInline]

如果我删除一切以上的一切都很好。



mysql日志

  2161查询集autocommit = 0 
 2161查询SELECT`ha_phone`.`id`，`ha_phone`.`phone_number`，`ha_phone`.`phone_owner_id` FROM`ha_phone` WHERE`ha_phone`.`id` = 262 
 2161查询SELECT`django_content_type`.`id`，`django_content_type`.`app_label`，`django_content_type`.`model` FROM`django_content_type` WHERE（`django_content_type`.`model` ='phone'AND`django_content_type` .`app_label` = 'MYAPP'）
 2161查询提交
 2161查询集autocommit = 1 
 2161查询SELECT`ha_owner`.`id`，`ha_owner`.`owner_pub_date`，`ha_owner`.`owner_name ```ha_owner`.`owner_verify`，`ha_owner`.`owner_company_id` FROM`ha_owner` WHERE`ha_owner`.`id` = 236 
 2161查询SELECT`ha_company`.`id`，`ha_company`。 `company_name` FROM`ha_company` WHERE`ha_company`.`id` = 1 
 2161查询SELECT`ha_owner`.`id`，`ha_owner`.`owner_pub_date`，`ha_owner`.`owner_name`，`ha_owner `.`owner_verify`，`ha_owner`.`owner_company_id` FROM`ha_owner` 
 2161查询SELECT`ha_company`.`id`，`ha_company`.`company_name` FROM`ha_company` WHERE`ha_company`.`id `= 1 
 2161查询SELECT`ha_company`.`id`，`ha_company`.`company_name` FROM`ha_company` WHERE`ha_company`.`id` = 1 
 2161查询SELECT`ha_company`。 `id`，`ha_company`.`company_name` FROM`ha_company` WHERE`ha_company`.`id` = 1 
 2161查询SELECT` ha_company`.`id`，`ha_company`.`company_name` FROM`ha_company` WHERE`ha_company`.`id` = 1 
 2161查询SELECT`ha_company`.`id`，`ha_company`.`company_name` FROM`ha_company` WHERE`ha_company`.`id` = 1 
 2161查询SELECT`ha_company`.`id`，`ha_company`.`company_name` FROM`ha_company` WHERE`ha_company`.`id` = 1 
 2161查询SELECT`ha_company`.`id`，`ha_company`.`company_name` FROM`ha_company` WHERE`ha_company`.`id` = 1 
 2161查询SELECT`ha_company`.`id` ，`ha_company`.`company_name` FROM`ha_company` WHERE`ha_company`.`id` = 1 
 2161查询SELECT`ha_company`.`id`，`ha_company`.`company_name` FROM`ha_company` WHERE` ha_company`.`id` = 1 
 
 ...

模型。 py 

  class House（models.Model）：
 house_number = models.CharField（max_length = 10，verbose_name = Номердома）
 house_floors = models.PositiveSmallIntegerField（verbose_name =Чис оэтажей，null = True）
 house_walls = models.ForeignKey（Walls，verbose_name =Материалстен，null = True）
 house_street = models.ForeignKey（Street，verbose_name =Улица）
 house_metro = models.ForeignKey（Metro，verbose_name =СтанцияМетро，null = True）
 house_block = models.ForeignKey（Block，verbose_name =Микрорайон，null = True）
 
 
 class Meta：
 verbose_name ='Дом'
 verbose_name_plural ='Дома'
 ordering = ['house_street'，'house_number'] 
 
 
 def __unicode __（self）：
 return unicode（self.house_street.street_name）++ unicode（self.house_number）
 
 class Flat（models.Model）：
 flat_number = models.CharField（max_length = 7，verbose_name =Номерквартиры，null = True）
 flat_rooms = models.PositiveSmallIntegerField（verbose_name =Числокомнат）
 flat_total_sq = mod 
 flat_life_sq = models.PositiveSmallIntegerField（verbose_name =Жилаяплощадь）
 flat_kitchen_sq = models.PositiveSmallIntegerField（verbose_name =Кухоннаяплощадь）
 flat_floors = models.PositiveSmallIntegerField（verbose_name =Этаж）
 flat_house = models.ForeignKey（House，verbose_name =Дом）
 flat_owner = models.ForeignKey（Owner，verbose_name =Владелец）
 flat_comment = models.TextField（verbose_name =Комметарий，null = True）
 flat_price = models.PositiveIntegerField（verbose_name ='Ценаквартиры'）
 ... 
类所有者（模型。模型）：
 owner_pub_date = models.DateField（default = None，null = True，verbose_name =Датапубликации）
 owner_name = models.CharField（max_length = 150，verbose_name =ФИОвладельца）
 owner_verify = models.BooleanF ield（verbose_name =Пользовательверифициров​​ан？）
 owner_company = models.ForeignKey（Company，verbose_name =Компания）
 
 class Meta：
 verbose_name ='Владельца'
 verbose_name_plural ='Владельцы'
 
 def __unicode __（self）：
 return unicode（self.id）+| + self.owner_name ++ unicode（self.owner_company）
 
 
 class Phone（models.Model）：
 phone_number = models.CharField（max_length = 25，verbose_name =Номертелефона）
 phone_owner = models.ForeignKey（Owner，verbose_name =Владелецтелефона）
 
 class Meta：
 verbose_name ='Телефон'
 verbose_name_plural ='Телефоны'
 
 def __unicode __（self）：
 return unicode（self.phone_owner）++ self.phone_number

admin.py 

  ... 
 class PhoneInline（admin.StackedInline）：
 model = Phone 
 extra = 2 
 
 class FlatInline（admin.TabularInline）：
 model = Flat 
 extra = 0 
 
 class NeedInline（admin.TabularInline）：
 model =需要
 extra = 1 
 
 OwnerAdmin（admin.ModelAdmin）：
 field = ['owner_name'] 
 inlines = [PhoneInline，FlatInline，NeedInline] 
 
 admin.site.register（Owner，OwnerAdmin）

我该怎么办？
非常感谢。



更新
@Alasdair，谢谢。与ha_company的查询减少。但是我和ha_street有一个重复的。看起来像这样：

  2601查询SELECT`ha_company`.`id`，`ha_company`.`company_name` FROM`ha_company` 
 2601查询SELECT`ha_house`.`id`，`ha_house`.`house_number`，`ha_house`.`house_floors`，`ha_house`.`house_walls_id`，`ha_house`.`house_street_id`，`ha_house` .`house_metro_id`，`ha_house`.`house_block_id`，'ha_house`.`house_age`，`ha_house`.`house_flat`，`ha_house`.`house_quality` FROM`ha_house` INNER JOIN`ha_street` ON（`ha_house` .`house_street_id` =`ha_street`.`id`）ORDER BY`ha_street`.`street_name` ASC，`ha_house`.`house_number` ASC 
 2601查询SELECT`ha_street`.`id`，`ha_street` .`street_name` FROM`ha_street` WHERE`ha_street`.`id` = 74 
 2601查询SELECT`ha_street`.`id`，`ha_street`.`street_name` FROM`ha_street` WHERE`ha_street`.` id` = 74 
 2601查询SELECT`ha_street`.`id`，`ha_street`.`street_name` FROM`ha_street` WHERE`ha_street`.`id` = 36 
 2601查询SELECT`ha_street` `````` ha_street`.`street_name` FROM`ha_street` WHERE`ha_street`.`id` = 582 
 2601查询SELECT`ha_street`.`id`，`ha_street`.`street_name` FROM`ha_street` WHERE`ha_street` .`id` = 582


解决方案

<$ c $ c> __ unicode __ 的方法包括 unicode（self.phone_owner），其中反过来访问 owner_company 外键。这意味着Django必须在内联中查找每个电话对象的公司。



通过删除内联来解决问题，但如果要保留内联您可以：


  1. 更改的 __ unicode __ 电话或所有者，以便公司不包括

  2. 覆盖 get_queryset 方法，以便您可以使用 select_related 。

      class PhoneInline（admin.StackedInline）：
     model = Phone 
     extra = 2 
     
     def get_queryset（self，request）：
     return super（PhoneInline，self）.get_queryset（request）.select_related（'phone_owner'，'phone_owner__ owner_company'）



I have encountered a problem.

When I try to open the change page in order to see parameters within element (like djangosite.com/admin/djangoapp/someelement/1/change/) it loads very slowly (10-15 seconds).

I found out that Django is making a lot of duplicate queries:

UPDATE: I think I made mistake in admin.py.

   inlines = [PhoneInline,FlatInline,NeedInline]

If i delete line above everything is fine.

mysql log

2161 Query  set autocommit=0
         2161 Query SELECT `ha_phone`.`id`, `ha_phone`.`phone_number`, `ha_phone`.`phone_owner_id` FROM `ha_phone` WHERE `ha_phone`.`id` = 262
         2161 Query SELECT `django_content_type`.`id`, `django_content_type`.`app_label`, `django_content_type`.`model` FROM `django_content_type` WHERE (`django_content_type`.`model` = 'phone' AND `django_content_type`.`app_label` = 'MYAPP')
         2161 Query commit
         2161 Query set autocommit=1
         2161 Query SELECT `ha_owner`.`id`, `ha_owner`.`owner_pub_date`, `ha_owner`.`owner_name`, `ha_owner`.`owner_verify`, `ha_owner`.`owner_company_id` FROM `ha_owner` WHERE `ha_owner`.`id` = 236
         2161 Query SELECT `ha_company`.`id`, `ha_company`.`company_name` FROM `ha_company` WHERE `ha_company`.`id` = 1
         2161 Query SELECT `ha_owner`.`id`, `ha_owner`.`owner_pub_date`, `ha_owner`.`owner_name`, `ha_owner`.`owner_verify`, `ha_owner`.`owner_company_id` FROM `ha_owner`
         2161 Query SELECT `ha_company`.`id`, `ha_company`.`company_name` FROM `ha_company` WHERE `ha_company`.`id` = 1
         2161 Query SELECT `ha_company`.`id`, `ha_company`.`company_name` FROM `ha_company` WHERE `ha_company`.`id` = 1
         2161 Query SELECT `ha_company`.`id`, `ha_company`.`company_name` FROM `ha_company` WHERE `ha_company`.`id` = 1
         2161 Query SELECT `ha_company`.`id`, `ha_company`.`company_name` FROM `ha_company` WHERE `ha_company`.`id` = 1
         2161 Query SELECT `ha_company`.`id`, `ha_company`.`company_name` FROM `ha_company` WHERE `ha_company`.`id` = 1
         2161 Query SELECT `ha_company`.`id`, `ha_company`.`company_name` FROM `ha_company` WHERE `ha_company`.`id` = 1
         2161 Query SELECT `ha_company`.`id`, `ha_company`.`company_name` FROM `ha_company` WHERE `ha_company`.`id` = 1
         2161 Query SELECT `ha_company`.`id`, `ha_company`.`company_name` FROM `ha_company` WHERE `ha_company`.`id` = 1
         2161 Query SELECT `ha_company`.`id`, `ha_company`.`company_name` FROM `ha_company` WHERE `ha_company`.`id` = 1

...

models.py

class House(models.Model):
    house_number = models.CharField(max_length=10, verbose_name="Номер дома")
    house_floors = models.PositiveSmallIntegerField(verbose_name="Число этажей", null=True)
    house_walls = models.ForeignKey(Walls, verbose_name="Материал стен", null=True)
    house_street = models.ForeignKey(Street, verbose_name="Улица")
    house_metro = models.ForeignKey(Metro, verbose_name="Станция Метро", null=True)
    house_block = models.ForeignKey(Block, verbose_name="Микрорайон", null=True)


    class Meta:
        verbose_name = 'Дом'
        verbose_name_plural = 'Дома'
        ordering = ['house_street','house_number']


    def __unicode__(self):
        return unicode(self.house_street.street_name) + " " + unicode(self.house_number)

class Flat(models.Model):
    flat_number = models.CharField(max_length=7, verbose_name="Номер квартиры", null=True)
    flat_rooms = models.PositiveSmallIntegerField(verbose_name="Число комнат")
    flat_total_sq = models.PositiveSmallIntegerField(verbose_name="Общая площадь")
    flat_life_sq = models.PositiveSmallIntegerField(verbose_name="Жилая площадь")
    flat_kitchen_sq = models.PositiveSmallIntegerField(verbose_name="Кухонная площадь")
    flat_floors = models.PositiveSmallIntegerField(verbose_name="Этаж")
    flat_house = models.ForeignKey(House, verbose_name="Дом")
    flat_owner = models.ForeignKey(Owner, verbose_name="Владелец")
    flat_comment = models.TextField(verbose_name="Комметарий", null=True)
    flat_price = models.PositiveIntegerField(verbose_name='Цена квартиры')
...    
class Owner(models.Model):
        owner_pub_date = models.DateField(default=None,null=True,verbose_name="Дата публикации")
        owner_name = models.CharField(max_length=150, verbose_name="ФИО владельца")
        owner_verify = models.BooleanField(verbose_name="Пользователь верифицирован?")
        owner_company = models.ForeignKey(Company, verbose_name="Компания")

        class Meta:
            verbose_name = 'Владельца'
            verbose_name_plural = 'Владельцы'

        def __unicode__(self):
            return unicode(self.id) + " | "+ self.owner_name + " " + unicode(self.owner_company)


    class Phone(models.Model):
        phone_number = models.CharField(max_length=25, verbose_name="Номер телефона")
        phone_owner = models.ForeignKey(Owner, verbose_name="Владелец телефона")

        class Meta:
            verbose_name = 'Телефон'
            verbose_name_plural = 'Телефоны'

        def __unicode__(self):
            return unicode(self.phone_owner) + " " + self.phone_number

admin.py

...        
class PhoneInline(admin.StackedInline):
            model = Phone
            extra = 2

        class FlatInline(admin.TabularInline):
            model = Flat
            extra = 0

        class NeedInline(admin.TabularInline):
            model = Need
            extra = 1

        class OwnerAdmin(admin.ModelAdmin):
            field=['owner_name']
            inlines = [PhoneInline,FlatInline,NeedInline]

    admin.site.register(Owner,OwnerAdmin)

What should I do? Thank you very much.

UPDATE @Alasdair, thank you. Queries with ha_company are reduced. But I have another duplicates with ha_street. Looks like this:

 2601 Query SELECT `ha_company`.`id`, `ha_company`.`company_name` FROM `ha_company`
         2601 Query SELECT `ha_house`.`id`, `ha_house`.`house_number`, `ha_house`.`house_floors`, `ha_house`.`house_walls_id`, `ha_house`.`house_street_id`, `ha_house`.`house_metro_id`, `ha_house`.`house_block_id`, `ha_house`.`house_age`, `ha_house`.`house_flat`, `ha_house`.`house_quality` FROM `ha_house` INNER JOIN `ha_street` ON (`ha_house`.`house_street_id` = `ha_street`.`id`) ORDER BY `ha_street`.`street_name` ASC, `ha_house`.`house_number` ASC
         2601 Query SELECT `ha_street`.`id`, `ha_street`.`street_name` FROM `ha_street` WHERE `ha_street`.`id` = 74
         2601 Query SELECT `ha_street`.`id`, `ha_street`.`street_name` FROM `ha_street` WHERE `ha_street`.`id` = 74
         2601 Query SELECT `ha_street`.`id`, `ha_street`.`street_name` FROM `ha_street` WHERE `ha_street`.`id` = 36
         2601 Query SELECT `ha_street`.`id`, `ha_street`.`street_name` FROM `ha_street` WHERE `ha_street`.`id` = 582
         2601 Query SELECT `ha_street`.`id`, `ha_street`.`street_name` FROM `ha_street` WHERE `ha_street`.`id` = 582

解决方案

The __unicode__ method for the Phone model includes unicode(self.phone_owner), which in turn accesses the owner_company foreign key. That means that Django has to look up the company for every phone object in the inline.

You fixed the problem by removing the inline, but if you want to keep the inline you could either:

  1. Change the __unicode__ method for Phone or Owner so that the company is not included

  2. Override the get_queryset method for your inline, so that you can use select_related.

    class PhoneInline(admin.StackedInline):
    model = Phone
    extra = 2

    def get_queryset(self, request):
        return super(PhoneInline, self).get_queryset(request).select_related('phone_owner', 'phone_owner__ owner_company')

这篇关于Django管理员在mysql中创建了大量重复的查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！

查看全文
相关文章
数据库最新文章
热门教程
热门工具
登录 关闭
扫码关注1秒登录
发送“验证码”获取 | 15天全站免登陆