python - 如何避免 Django 的分页器的 COUNT 查询呢？

Question

Paginator.page()导致缓存属性 count 的计算，它对数据库执行 COUNT 查询。我的问题是该 COUNT 的平均查询执行时间为 1.06 秒，而主查询的平均时间为 1.04 秒。实际上我有一个重复的查询。有没有办法避免 COUNT 查询？

Answer 1

此问题的常见解决方案是不显示总页数。我实现了一个不支持 count、num_pages 和 page_range 的自定义分页器:

from django.utils.translation import gettext_lazy as _
from django.core.paginator import EmptyPage, PageNotAnInteger


class CountlessPage:

    def __init__(self, object_list, page_number: int, page_size: int):
        self._object_list = object_list
        self._page_number = page_number
        self._page_size = page_size
        self._evaluated = False

    @property
    def number(self):
        return self._page_number

    def has_next(self):
        self._evaluate()
        return self._has_next_page

    def has_previous(self):
        return self._page_number > 1

    def has_other_pages(self):
        return self.has_previous() or self.has_next()

    def next_page_number(self):
        if self.has_next():
            return self._page_number + 1
        else:
            raise EmptyPage(_('There is no next page'))

    def previous_page_number(self):
        if self.has_previous():
            return self._page_number - 1
        else:
            raise EmptyPage(_('There is no previous page'))

    def _evaluate(self):
        if self._evaluated: return
        if not isinstance(self._object_list, list):
            self._object_list = list(self._object_list)
        contents = self._object_list
        self._object_list = self._object_list[:self._page_size]
        if len(contents) > len(self._object_list):
            self._has_next_page = True
        else:
            self._has_next_page = False
        self._evaluated = True

    def __repr__(self):
        return '<Page %s>' % self._page_number

    def __len__(self):
        self._evaluate()
        return len(self._object_list)

    def __iter__(self):
        self._evaluate()
        return iter(self._object_list)

    def __getitem__(self, index):
        if not isinstance(index, (int, slice)):
            raise TypeError
        self._evaluate()
        return self._object_list[index]

class CountlessPaginator:
    """A paginator that does not count the total number of pages.

    To count the total number of pages an additional COUNT query
    is needed, use this paginator when peformance is more important.
    It is still possible to navigate to next, previous and first page.
    It's not possible to navigate to an arbitrary page because we
    don't know the number of pages.
    """

    def __init__(self, object_list, per_page: int) -> None:
        self._object_list = object_list
        self._per_page = per_page

    def validate_number(self, number: int):
        """Validate the given 1-based page number."""
        try:
            if isinstance(number, float) and not number.is_integer():
                raise ValueError
            number = int(number)
        except (TypeError, ValueError):
            raise PageNotAnInteger(_('That page number is not an integer'))
        if number < 1:
            raise EmptyPage(_('That page number is less than 1'))
        return number

    def get_page(self, number: int):
        """
        Return a valid page, even if the page argument isn't a number or isn't
        in range.
        """
        try:
            number = self.validate_number(number)
        except (PageNotAnInteger, EmptyPage):
            number = 1
        return self.page(number)

    def page(self, number: int):
        """Return a Page object for the given 1-based page number."""
        number = self.validate_number(number)
        bottom = (number - 1) * self._per_page
        top = bottom + self._per_page + 1
        return CountlessPage(self._object_list[bottom:top], number, self._per_page)