如何减少Django查询时的数据库命中率

如果您只需要每个用户和设备的日志计数(这是您从发布的代码中获得的),则只需一个查询即可获得:

from django.db.models import Count

logs = (Log.objects
    .filter(created_at__range = (from_date, to_date))
    .values('user', 'device')
    .annotate(log_count=Count('device'))
)

您可以修改查询以包含所需的用户和设备型号的任何属性:

.values('user__last_name', 'device__name')  # etc.

您还可以通过附加 order_by() 最后,为了能够按所需顺序对其进行迭代:

.order_by('user__last_name', '-log_count')

我要做的是创建一个引用 view 在MySQL实例中

视图如下所示:

SELECT 
t1.*,
t2.*,
t3.*
FROM users t1
RIGHT JOIN device t2 (ON t1.id=t2.user_id)
RIGHT JOIN log t3 (ON t3.device_id=t2.id);

class SomeModel(models.Model):
    # all fields from the 3 tables here

    class Meta:
        db_table = 'yourViewNameHere'
        managed = False # this keeps django from creating the table

然后 python manage.py makemigrations python manage.py migrate 像往常一样

现在,要访问所需的数据,可以执行以下操作:

from django.db import connection
sql = "SELECT * FROM your_view WHERE some_date_column > 'foo' AND some_date_column < 'bar' "

with connection.cursor() as cur:

    cur.execute(sql)
    data = cur.fetchall()

print(data)

如果要向原始sql查询传递参数,则应该 像这样传递它们以避免sql注入:

sql = "SELECT * FROM your_view WHERE some_date_column > %s AND some_date_column < %s"

params = ('foo', 'bar')
with connection.cursor() as cur:

    cur.execute(sql, params)
    data = cur.fetchall()