SQL優化（3）（延續前一節）

RomanceSky發表於2018-05-30
SQL優化

前因後果

框架：Django

資料庫:Postgresql

問題

我們遍歷查詢結果的時候就會查詢很多次資料庫，能不能只查詢一次，把User和Group的資訊也查出來呢？

修改一個 settings.py 讓Django列印出在資料庫中執行的語句

settings.py 尾部加上

LOGGING = {
    'version': 1,
    'disable_existing_loggers': False,
    'handlers': {
        'console': {
            'class': 'logging.StreamHandler',
        },
    },
    'loggers': {
        'django.db.backends': {
            'handlers': ['console'],
            'level': 'DEBUG' if DEBUG else 'INFO',
        },
    },
複製程式碼

果

使用prefetch_related('User模型中的外來鍵，如groups')

views.py
class UserViewSet(viewsets.ModelViewSet):
    authentication_classes = (
        TokenAuthentication,
    )
    permission_classes = (AllowAny,)
    queryset = User.objects.all().order_by('-date_joined').prefetch_related('groups')
    serializer_class = UserSerializer

    def create(self, request, *args, **kwargs):
        serializer = self.get_serializer(data=request.data)
        serializer.is_valid(raise_exception=True)
        user = serializer.save()
        auth_token = AuthToken.objects.create(user)
        # print(auth_token)
        return Response(
            {
                "email": user.email,
                "token": auth_token,
                "id": user.id,
                #"key": auth_token.key,
            }
        )

models.py


class MyUsers(models.Model):
    #REQUIRED_FIELDS = ('name',)
    #USERNAME_FIELD = ('name')
    groups = models.ForeignKey(
        'Group',
        null=False,
        blank=False,
        related_name='myusers',
        on_delete=models.CASCADE
    )
   省略 ....
    
    
    
class Group(models.Model):
    name = models.CharField(
            null=False,
            blank=False,
            max_length=125,
    )

    url = models.URLField(
            null=False,
            blank=True,
            max_length=125,
    )
    
複製程式碼

查詢結果

優化前

(0.014) SELECT "auth_user"."id", "auth_user"."password", "auth_user"."last_login", "auth_user"."is_superuser", "auth_user"."username", "auth_user"."first_name", "auth_user"."last_name", "auth_user"."email", "auth_user"."is_staff", "auth_user"."is_active", "auth_user"."date_joined" FROM "auth_user" ORDER BY "auth_user"."date_joined" DESC; args=()
(0.023) SELECT "auth_group"."id", "auth_group"."name" FROM "auth_group" INNER JOIN "auth_user_groups" ON ("auth_group"."id" = "auth_user_groups"."group_id") WHERE "auth_user_groups"."user_id" = 24; args=(24,)
(0.001) SELECT "auth_group"."id", "auth_group"."name" FROM "auth_group" INNER JOIN "auth_user_groups" ON ("auth_group"."id" = "auth_user_groups"."group_id") WHERE "auth_user_groups"."user_id" = 23; args=(23,)
(0.001) SELECT "auth_group"."id", "auth_group"."name" FROM "auth_group" INNER JOIN "auth_user_groups" ON ("auth_group"."id" = "auth_user_groups"."group_id") WHERE "auth_user_groups"."user_id" = 22; args=(22,)
(0.001) SELECT "auth_group"."id", "auth_group"."name" FROM "auth_group" INNER JOIN "auth_user_groups" ON ("auth_group"."id" = "auth_user_groups"."group_id") WHERE "auth_user_groups"."user_id" = 21; args=(21,)
(0.001) SELECT "auth_group"."id", "auth_group"."name" FROM "auth_group" INNER JOIN "auth_user_groups" ON ("auth_group"."id" = "auth_user_groups"."group_id") WHERE "auth_user_groups"."user_id" = 20; args=(20,)
......

優化後

(0.003) SELECT "auth_user"."id", "auth_user"."password", "auth_user"."last_login", "auth_user"."is_superuser", "auth_user"."username", "auth_user"."first_name", "auth_user"."last_name", "auth_user"."email", "auth_user"."is_staff", "auth_user"."is_active", "auth_user"."date_joined" FROM "auth_user" ORDER BY "auth_user"."date_joined" DESC; args=()
(0.002) SELECT ("auth_user_groups"."user_id") AS "_prefetch_related_val_user_id", "auth_group"."id", "auth_group"."name" FROM "auth_group" INNER JOIN "auth_user_groups" ON ("auth_group"."id" = "auth_user_groups"."group_id") WHERE "auth_user_groups"."user_id" IN (1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24); args=(1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24)
[30/May/2018 10:31:35] "GET /users/ HTTP/1.1" 200 2182
(0.002) SELECT "auth_group"."id", "auth_group"."name" FROM "auth_group"; args=()
[30/May/2018 10:31:51] "GET /groups/ HTTP/1.1" 200 228
複製程式碼

參考

https://code.ziqiangxuetang.com/django/django-queryset-advance.html

相關文章