Skip to main content
 首页 » 编程设计

python之Django:随机查询速度慢之尽管进行了优化

2024年12月31日32zhoujg

我构建了一个 API，它应该从一个大型查询集中返回 10 个随机选择的结果。

我有以下4个模型:

class ScrapingOperation(models.Model): 
    completed = models.BooleanField(default=False) 
    (...) 
 
    indexes = [ 
        models.Index(fields=['completed'], name='completed_idx'), 
        models.Index(fields=['trusted'], name='trusted_idx'), 
    ] 
 
    @property 
    def ads(self): 
        """returns all ads linked to the searches of this operation""" 
        return Ad.objects.filter(searches__in=self.searches.all()) 
 
 
class Search(models.Model): 
    completed = models.BooleanField(default=False) 
    scraping_operation = models.ForeignKey( 
            ScrapingOperation, 
            on_delete=models.CASCADE, 
            related_name='searches' 
    ) 
    (...) 
 
 
class Ad(models.Model): 
    searches = models.ManyToManyField('scraper.Search', related_name='ads') 
    (...) 
 
 
class Label(models.Model): 
     value = models.Integerfield() 
     linked_ad = models.OneToOneField( 
         Ad, on_delete=models.CASCADE, related_name='labels' 
     )

数据库目前有 400.000 + Ad 对象，但平均 ScrapingOperation 有 14000 个 Ad 对象链接到它。我希望 API 从这些 +/- 14000 中返回 10 个随机结果，这些结果还没有链接的 Label 对象(每个操作最多只有几百个)

因此必须从包含 14.000 个对象的查询中返回 10 个随机结果。

较早的版本只能返回 1 个结果，但使用了慢得多的 sort_by('?') 方法。当我不得不扩大它以返回随机的 10 个 Ad 对象时，我使用了一种部分基于 this stackoverflow answer 的新方法。

下面是选择(并返回)10 个随机对象的代码:

# Get all ads linked to the last completed operation 
last_op_ads = ScrapingOperation.objects.filter(completed=True).last().ads 
 
# Get all ads that don't have an label yet 
random_ads = last_op_ads.filter(labels__isnull=True) 
 
# Get list ids of all potential ads 
id_list = random_ads.values_list('id', flat=True) 
id_list = list(id_list) 
 
# Select a random sample of 10, get objects with PK matches 
samples = rd.sample(id_list, min(len(id_list), 10)) 
selected_samples = random_ads.filter(id__in=samples) 
 
return selected_samples

但是，尽管我进行了优化，但此查询需要 10 多秒才能完成，从而创建了一个非常慢的 API。

这种长时间延迟是否只是随机查询所固有的？ (如果是这样，其他程序员如何处理这个限制？)或者我的代码中是否存在我遗漏的错误/效率低下？

编辑:根据响应，我在下面包含了原始 sql 查询 (注意:这些在我的本地环境中运行，其中仅包含我的生产环境包含的数据的 5%)

{'sql': 'SELECT "scraper_scrapingoperation"."id",  
"scraper_scrapingoperation"."date_started",  
"scraper_scrapingoperation"."date_completed", 
"scraper_scrapingoperation"."completed",  
"scraper_scrapingoperation"."round",  
"scraper_scrapingoperation"."trusted" FROM "scraper_scrapingoperation"  
WHERE "scraper_scrapingoperation"."completed" = true ORDER BY  
"scraper_scrapingoperation"."id" DESC LIMIT 1', 'time': '0.001'} 
 
 
{'sql': 'SELECT "database_ad"."id" FROM "database_ad" INNER JOIN  
"database_ad_searches" ON ("database_ad"."id" =  
"database_ad_searches"."ad_id") LEFT OUTER JOIN "classifier_label" ON 
("database_ad"."id" = "classifier_label"."ad_id") WHERE  
("database_ad_searches"."search_id" IN (SELECT U0."id" FROM  
"scraper_search" U0 WHERE U0."scraping_operation_id" = 6) AND  
"classifier_label"."id" IS NULL)', 'time': '1.677'}

编辑 2:我尝试了另一种方法，使用更深的 select_related 参数

        random_ads = ScrapingOperation.objects.prefetch_related( 
            'searches__ads__labels', 
        ).filter(completed=True).last().ads.exclude( 
            labels__isnull=True 
        ) 
 
        id_list = random_ads.values_list('id', flat=True) 
        id_list = list(id_list) 
 
        samples = rd.sample(id_list, min( 
            len(id_list), 10)) 
 
        selected_samples = random_ads.filter( 
            id__in=samples) 
 
        return selected_samples

产生以下 SQL 查询:

{'time': '0.008', 'sql': 'SELECT "scraper_search"."id",  
"scraper_search"."item_id", "scraper_search"."date_started",  
"scraper_search"."date_completed", "scraper_search"."completed",  
"scraper_search"."round", "scraper_search"."scraping_operation_id",  
"scraper_search"."trusted" FROM "scraper_search" WHERE  
"scraper_search"."scraping_operation_id" IN (6)'} 
 
 
 {'time': '0.113', 'sql': 'SELECT ("database_ad_searches"."search_id") 
 AS "_prefetch_related_val_search_id", "database_ad"."id",  
 "database_ad"."item_id", "database_ad"."item_state",  
 "database_ad"."title", "database_ad"."seller_id",  
 "database_ad"."url", "database_ad"."price",  
 "database_ad"."transaction_type", "database_ad"."transaction_method", 
 "database_ad"."first_seen", "database_ad"."last_seen",  
 "database_ad"."promoted" FROM "database_ad" INNER JOIN  
 "database_ad_searches" ON ("database_ad"."id" =  
 "database_ad_searches"."ad_id") WHERE  
 "database_ad_searches"."search_id" IN (130, 131, 132, 133, 134, 135, 136, 137, 138, 139, 140, 141, 142, 143, 144, 145, 146, 147, 148, 149, 150, 151, 152, 153, 154, 155, 156, 157, 158, 159, 160)'} 
 
 
{'time': '0.041', 'sql': 'SELECT "classifier_label"."id",  
"classifier_label"."set_by_id", "classifier_label"."ad_id",  
"classifier_label"."date", "classifier_label"."phone_type",  
"classifier_label"."seller_type", "classifier_label"."sale_type" FROM 
"classifier_label" WHERE "classifier_label"."ad_id" IN (1, 3, 6, 10, 20, 29, 30, 35, 43, (and MANY more of these numbers) ....'} 
 
 
 
{'time': '1.498', 'sql': 'SELECT "database_ad"."id" FROM "database_ad" 
INNER JOIN "database_ad_searches" ON ("database_ad"."id" = "database_ad_searches"."ad_id") LEFT OUTER JOIN "classifier_label" ON  
("database_ad"."id" = "classifier_label"."ad_id") WHERE  
("database_ad_searches"."search_id" IN (SELECT U0."id" FROM 
"scraper_search" U0 WHERE U0."scraping_operation_id" = 6) AND NOT  
("classifier_label"."id" IS NOT NULL))'}

每个 ScrapingOperation“仅”有 +/- 14000 个链接广告，但生产中的广告总数为 400.000(并且还在增加)。上面的所有代码在我的本地环境(仅包含 5% 的数据)上返回有效结果，但在生产环境中的 API 上返回 502 错误。

请您参考如下方法:

我会尝试首先隔离链接的广告，然后使用按生成的随机列排序从中随机抽取 10 个。我不确定生成的 sql 如何有效。可以肯定的是，我更愿意为任务创建一个存储过程，因为这显然是一个以随机样本结束的数据挖掘操​​作。


  文章来源：
  https://blog.windname.com/post/ajiabj2b1.html
阅读延展