django分页优化

January 21st, 2015

最近在用rest framework做API的时候，有几个资源集合get特别慢，看了django debug tool的分析，发现最慢的一条sql是用来count有多少个资源，因为查询比较复杂，加上count后，就变得特别慢。

sql语句像这样：

SELECT COUNT(*) FROM `gallery_photo` (....一堆复杂的联接)

实际上，这部分代码要展示的是UGC资源，用户其实不关心图片或者发帖的总数量，当用户打开页面时，只需要获取当前的信息就可以了,比如微博的下拉刷新，其实一次就只需要加载一个屏幕的信息流，至于是否还有新的信息流，用户只要再下拉一次就好了。

基于这些考虑，我决定把这个count去掉。

根据django debug toolbar给出的代码分析，找到对应的rest framework的代码，对于获取资源的list，有两处会计算资源的count。一个是PaginatorSerializer，代码如下：

class PaginationSerializer(BasePaginationSerializer):
    def validate_number(self, number):
        "Validates the given 1-based page number."
        try:
            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')
        if number > self.num_pages: # 获取count
            if number == 1 and self.allow_empty_first_page:
                pass
            else:
                raise EmptyPage('That page contains no results')
        return number

    def _get_count(self):
        "Returns the total number of objects, across all pages."
        if self._count is None:
            try:
                self._count = self.object_list.count()
            except (AttributeError, TypeError):
                # AttributeError if object_list has no count() method.
                # TypeError if object_list.count() requires arguments
                # (i.e. is of type list).
                self._count = len(self.object_list)
        return self._count
    count = property(_get_count)

    def _get_num_pages(self):
        "Returns the total number of pages."
        if self._num_pages is None:
            if self.count == 0 and not self.allow_empty_first_page:
                self._num_pages = 0
            else:
                hits = max(1, self.count - self.orphans)
                self._num_pages = int(ceil(hits / float(self.per_page)))
        return self._num_pages
    num_pages = property(_get_num_pages)

另一个是rest framework的PaginationSerialzier：

class PaginationSerializer(BasePaginationSerializer):
    """
    A default implementation of a pagination serializer.
    """
    count = serializers.Field(source='paginator.count') # 计算count
    next = NextPageField(source='*')
    previous = PreviousPageField(source='*')

针对这两个count查询，我搜了一个endless paginator库，该库提供了一个LazyPaginator，在调用validate_number不会检查是否超过页数是否超过总数。然后自定义了一个PaginatorSerializer，去掉了count字段。代码如下：

# serializer
from rest_framework.pagination import BasePaginationSerializer, NextPageField, \
    PreviousPageField
class NoCountPaginationSerializer(BasePaginationSerializer):
    next = NextPageField(source='*')
    previous = PreviousPageField(source='*')

# views.py
class PhotoViewSet(viewsets.ModelViewSet):
    model = Photo
    permission_classes = (IsAuthenticated, )
    pagination_serializer_class = NoCountPaginationSerializer # 不输出count
    paginator_class = LazyPaginator   # 换成不检查页数是否越界的分页
    serializer_class = PhotoSerializer
    filter_class = PhotoFilter

参考：
http://dancarroll.org/blog/2010/04/using-infinitepaginator/

后端 (3)