小记， django select 字段中含有连表查询 分页优化

[wuwukai007]

• 例如 查询每本书的借阅数量，在 select 中加入连表

select id,(select count(*) from book where book.user_id= user.id) as count,name from user;

• 在 django 分页最后会变成

  select count(*) from (
  select id,(select count(*) from book where book.user_id= user.id) as count,name from user) 
  

• 优化,在 restframework 中分页, 去掉 select 字段 ，只保留 filter 条件

  from rest_framework.pagination import PageNumberPagination
  from django.core.paginator import Paginator
  from django.utils.functional import cached_property
  
  class Pagination(Paginator):
  
      @cached_property
      def count(self):
          """Return the total number of objects, across all pages."""
          c = getattr(self.object_list, 'count', None)
          if callable(c) and not inspect.isbuiltin(c) and method_has_no_args(c):
              return self.object_list.values('id').count()
          return len(self.object_list)
  
  
  class CustomPagination(PageNumberPagination):
      django_paginator_class = TicketPagination
  
  
  

• 接口中使用

  class API(ModelViewSet):
    pagination_class = CustomPagination
  

[pppobear]

LIMIT/OFFSET 性能太差了：查询耗时会随着页数增长。假如每页 10 条数据，要查询第 10 页的数据。数据库查询需要先跳过 90 条，再查询 10 条数据。

建议改用 CursorPagination