提高django数据库查询的性能 [英] Improving performance of django DB query

问题描述

我使用django / apache / sqlite3，我有一个django模型，如下所示：

I'm using django/apache/sqlite3 and I have a django model that looks like this:

class Temp_entry(models.Model):
    dateTime = models.IntegerField() #datetime
    sensor = models.IntegerField()   # id of sensor
    temp = models.IntegerField()     # temp as temp in Kelvin * 100

我正在尝试将最后300个Temp_entry项目放置到图表中。我这样做：

I'm trying to get the last 300 Temp_entry items to place into a graph. I do that this way:

revOutsideTempHistory = Temp_entry.objects.filter(sensor=49).order_by('dateTime').reverse()[:300]

但是，此查询需要约1秒。有没有办法改善这个？我已经挖了，发现order_by是可怕的低效率，所以我希望有一个可行的替代方案。

However, this query takes ~1 second. Is there a way to improve this? I've dug around and found that order_by is horrible inefficient, so I'm hoping that there is a viable alternative?

我想到的一个替代方案，但不能找出如何实现，将每20分钟运行一次查询并保持缓存，这也是可以接受的，因为数据可能稍微过时，没有不良影响。

An alternative I thought of, but can't figure out how to implement, would be to run the query every 20 minutes and keep it cached, that would be acceptable too, as the data can be slightly stale with no ill effects.

推荐答案

如果缓存是可以接受的，它始终应该被使用。如下：

If caching is acceptable it always should be used. Something like:

from django.core.cache import cache

cached = cache.get('temp_entries')
if cached:
    result = cached 
else:
    result = Temp_entry.objects.filter(sensor=49).order_by('dateTime').reverse().values_list()[:300]
    cache.set('temp_entries', result, 60*20)  # 20 min

此外，您还可以设置 db_indexes 对于相应的列

Also you can set db_indexes for the appropriate columns

class Temp_entry(models.Model):
    dateTime = models.IntegerField(db_index=True) #datetime
    sensor = models.IntegerField(db_index=True)   # id of sensor
    temp = models.IntegerField()     # temp as temp in Kelvin * 100

这篇关于提高django数据库查询的性能的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！