时间戳

首页 > 解决方案 > 与多个过滤器结合使用时，Django order_by('-id') 非常慢

mysql - 与多个过滤器结合使用时，Django order_by('-id') 非常慢

问题描述

我有一个带有组外键的模型线。我想在一个组中获取某个 id 下的最后 30 行。为此，我使用以下查询：

Line.objects.filter(group_id=8460, id__lt=10333449).order_by("-id")[:30]

我的问题是这个查询需要 1.5 秒才能执行，而这三个几乎是立即完成的（<50 毫秒）：

对于信息，我总共有 10M+ 行，仅在这个组中就有大约 13k 行。

id__lt使用和时如何使查询更快order_by('-id')？

该模型如下所示：

class Line(TransactionSubject):
    objects = ModelManager()

    group = models.ForeignKey(Group, on_delete=models.CASCADE, related_name="lines")

    user = models.ForeignKey(settings.AUTH_USER_MODEL, on_delete=models.CASCADE, null=True)
    author = models.ForeignKey(Character, on_delete=models.CASCADE, null=True, related_name="lines")
    tone = models.CharField(max_length=50, default="neutral")
    content = models.TextField(blank=True, null=True)
    action = models.TextField(blank=True, null=True)
    is_storytelling = models.BooleanField(default=False)
    is_chat = models.BooleanField(default=False)
    is_description = models.BooleanField(default=False)
    is_comment = models.BooleanField(default=False, db_index=True)

    character_state = models.ForeignKey(CharacterState, null=True, blank=True, on_delete=models.SET_NULL,
                                        related_name="lines")

    created = models.DateTimeField(auto_now_add=True)
    edited = models.DateTimeField(auto_now=True)

    whispered_to = models.ManyToManyField(Character, related_name="whispered_lines", blank=True)

    yet_to_read = models.ManyToManyField(User, related_name="unread_lines", blank=True)

    party = models.ForeignKey(Party, on_delete=models.SET_NULL, null=True, blank=True, related_name="lines")

    language = models.ForeignKey(FictionalLanguage, on_delete=models.SET_NULL, null=True, blank=True,
                                 related_name="lines")

    beginner_speakers = models.ManyToManyField(Character, related_name="lines_beginner", blank=True)
    intermediate_speakers = models.ManyToManyField(Character, related_name="lines_intermediate", blank=True)
    fluent_speakers = models.ManyToManyField(Character, related_name="lines_fluent", blank=True)

    transactions = GenericRelation(CoinTransaction, related_name="transactions")

    tagged_characters = models.ManyToManyField(Character, related_name="lines_tagging", blank=True)
    tagged_users = models.ManyToManyField(settings.AUTH_USER_MODEL, related_name="lines_tagging", blank=True)

查询中的一个explain()给出：

1 SIMPLE playerdata_line None index_merge PRIMARY,playerdata_line_group_id_429944d8,playerdata__group_id_d5c0b7_idx,playerdata__group_id_667c13_idx,group_id_index playerdata_line_group_id_429944d8,PRIMARY 8,4 None 11677 100.0 Using intersect(playerdata_line_group_id_429944d8,PRIMARY); Using where; Using filesort

结果查询是：

(1.339) SELECT `playerdata_line`.`id`, `playerdata_line`.`wood`, `playerdata_line`.`copper`, `playerdata_line`.`silver`, `playerdata_line`.`gold`, `playerdata_line`.`platinum`, `playerdata_line`.`legendary`, `playerdata_line`.`total_value`, `playerdata_line`.`group_id`, `playerdata_line`.`user_id`, `playerdata_line`.`author_id`, `playerdata_line`.`tone`, `playerdata_line`.`content`, `playerdata_line`.`action`, `playerdata_line`.`is_storytelling`, `playerdata_line`.`is_chat`, `playerdata_line`.`is_description`, `playerdata_line`.`is_comment`, `playerdata_line`.`character_state_id`, `playerdata_line`.`created`, `playerdata_line`.`edited`, `playerdata_line`.`party_id`, `playerdata_line`.`language_id` FROM `playerdata_line` WHERE (`playerdata_line`.`group_id` = 8460 AND `playerdata_line`.`id` < 10333449) ORDER BY `playerdata_line`.`id` DESC  LIMIT 30; args=(8460, 10333449)

标签： mysqldjango

解决方案

在通常一起查询的字段上添加索引是加快这些查询的好方法。在您的情况下，索引结束group并且id似乎合适

class Line(TransactionSubject):

    # fields...

    class Meta:
        indexes = [
            models.Index(fields=['group', 'id'], name='group_id_index'),
        ]

推荐阅读