提高django数据库查询的性能

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

我正在使用django / apache / sqlite3,我有一个看起来像这样的django模型:

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

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

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

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

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?

但是,此查询需要大约1秒钟。有没有办法改善这个?我挖了一遍,发现order_by效率很低,所以我希望有一个可行的选择吗?

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.

我想到的一个替代方案,但无法弄清楚如何实现,将是每20分钟运行一次查询并保持缓存,这也是可以接受的,因为数据可能稍微陈旧而没有不良影响。

4 个解决方案

#1

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

Also you can set db_indexes for the appropriate columns

您还可以为相应的列设置db_indexes

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

#2

Johnny Cache! http://packages.python.org/johnny-cache/ It works out-of-the-box, and it works well!

约翰尼卡什! http://packages.python.org/johnny-cache/它开箱即用,效果很好!

#3

You probably need to add some more indexes in your database. Use the django-debug toolbar to get the SQL of the actual query that's being run, and use the EXPLAIN feature to show what indexes it's using. For this particular query, I'd imagine you need to add an index on (sensor, dateTime) - do that directly in the database shell.

您可能需要在数据库中添加更多索引。使用django-debug工具栏获取正在运行的实际查询的SQL,并使用EXPLAIN功能显示它正在使用的索引。对于这个特定的查询,我想你需要在(sensor,dateTime)上添加索引 - 直接在数据库shell中执行。

#4

-1

Well, if you know your entries always have an increasing dateTime (i.e. the dateTime is set when the entry is created and not edited), then you don't have to order by dateTime as they will naturally be in that order in the database.

好吧,如果你知道你的条目总是有一个增加的dateTime(即在创建条目时没有编辑时设置dateTime),那么你不必按dateTime排序,因为它们在数据库中自然会按顺序排列。

#1