Django太强大，发现了一个性能优化神器
Django太强大，发现了一个性能优化神器
Django很强大，发现了一个很有用的功能
问题背景：
百万级数据，做前端页面分页，出现打开网页第一次打开很慢，基本要好几秒，第二次打开速度较快，基本是秒开
代码写了3个查询语句：
1、查询表中数据个数，用了count（）方法
2、根据[start:end]进行切片，实现的为limit查询，每次返回15条数据
3、遍历range(start,end),通过列表推导式，根据惟一索引进行查询，也是15条数据
counts = ArticleMmOther.objects.all().count()
#arts = ArticleMmOther.objects.values("id","title")[start:end]
arts = [ArticleMmOther.objects.values("id","title").get(id=i) for i in range(start, end)]
paginator = Paginator(range(counts), 15)我要探索2个问题：
1、这3条查询语句，中间有一个查询比较耗时，我的前端页面在访问页面的时候，要过好几秒才能打开网页，对用户不友好，但是我现在不知道哪个最耗时，如何把最耗时的查询操作找出来？
2、对于每次返回15条数据，我用了两种方式来写，一种是[start:end]切片limit查询,一种是从range(start,end)遍历15次，每次都是根据索引进行惟一查询，比较这两种查询谁更快？
好了，SQL执行时间可以在Navicate中进行查看，不过我这里要获取的是orm查询的执行时间，可以自己在查询执行前和执行后获取时间相减计算执行时间，不过自己计算也比较麻烦了，这最近看相关技术文章发现，django提供了一个能返回orm查询时间的api，我们调用可以直接查看每次查询的耗时,如下方代码所示
from django.db import connection
connection.queries
from django.db import connection
if stype=='b_other':
counts = ArticleMmOther.objects.all().count()
arts = ArticleMmOther.objects.values("id","title")[start:end]
print(connection.queries)
# arts = [ArticleMmOther.objects.values("id","title").get(id=i) for i in range(start, end)]
title = a
print(arts,type(arts))
paginator = Paginator(range(counts), 15)下方是打印的查询报告：
验证一：
count查询：11.340s
切片limit查询：0.009s
[{'sql': 'SELECT @@SQL_AUTO_IS_NULL', 'time': '0.000'},
{'sql': 'SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED',
'time': '0.000'},
# count查询总数量，查询时间11.340s
{'sql': 'SELECT COUNT(*) AS `__count`
FROM `articlemmother`', 'time': '11.340'},
# limit查询15条数据，查询时间0.009s
{'sql': 'SELECT `articlemmother`.`id`, `articlemmother`.`title`
FROM `articlemmother` ORDER BY `articlemmother`.`like` DESC,
`articlemmother`.`read` DESC LIMIT 15 OFFSET 2',
'time': '0.009'}]验证二：
count查询：0.030s
惟一索引id查询：0.000s
[{'time': '0.000', 'sql': 'SELECT @@SQL_AUTO_IS_NULL'}, {'time': '0.000', 'sql': 'SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED'},
# count查询用时0.030s
{'time': '0.030', 'sql': 'SELECT COUNT(*) AS `__count`
FROM `articlemmother`'},
# 15条数据全部通过惟一索引id查询,每条数据都是0.000s
{'time': '0.000', 'sql': 'SELECT VERSION()'},
{'time': '0.000', 'sql': 'SELECT `articlemmother`.`id`, `articlemmother`.`title` FROM `articlemmother` WHERE `articlemmother`.`id` = 1'},
{'time': '0.000', 'sql': 'SELECT `articlemmother`.`id`, `articlemmother`.`title` FROM `articlemmother` WHERE `articlemmother`.`id` = 2'},
{'time': '0.000', 'sql': 'SELECT `articlemmother`.`id`, `articlemmother`.`title` FROM `articlemmother` WHERE `articlemmother`.`id` = 3'},
{'time': '0.000', 'sql': 'SELECT `articlemmother`.`id`, `articlemmother`.`title` FROM `articlemmother` WHERE `articlemmother`.`id` = 4'},
{'time': '0.000', 'sql': 'SELECT `articlemmother`.`id`, `articlemmother`.`title` FROM `articlemmother` WHERE `articlemmother`.`id` = 5'},
{'time': '0.000', 'sql': 'SELECT `articlemmother`.`id`, `articlemmother`.`title` FROM `articlemmother` WHERE `articlemmother`.`id` = 6'},
{'time': '0.000', 'sql': 'SELECT `articlemmother`.`id`, `articlemmother`.`title` FROM `articlemmother` WHERE `articlemmother`.`id` = 7'},
{'time': '0.000', 'sql': 'SELECT `articlemmother`.`id`, `articlemmother`.`title` FROM `articlemmother` WHERE `articlemmother`.`id` = 8'},
{'time': '0.000', 'sql': 'SELECT `articlemmother`.`id`, `articlemmother`.`title` FROM `articlemmother` WHERE `articlemmother`.`id` = 9'},
{'time': '0.000', 'sql': 'SELECT `articlemmother`.`id`, `articlemmother`.`title` FROM `articlemmother` WHERE `articlemmother`.`id` = 10'},
{'time': '0.000', 'sql': 'SELECT `articlemmother`.`id`, `articlemmother`.`title` FROM `articlemmother` WHERE `articlemmother`.`id` = 11'},
{'time': '0.000', 'sql': 'SELECT `articlemmother`.`id`, `articlemmother`.`title` FROM `articlemmother` WHERE `articlemmother`.`id` = 12'},
{'time': '0.000', 'sql': 'SELECT `articlemmother`.`id`, `articlemmother`.`title` FROM `articlemmother` WHERE `articlemmother`.`id` = 13'},
{'time': '0.000', 'sql': 'SELECT `articlemmother`.`id`, `articlemmother`.`title` FROM `articlemmother` WHERE `articlemmother`.`id` = 14'},
{'time': '0.001', 'sql': 'SELECT `articlemmother`.`id`, `articlemmother`.`title` FROM `articlemmother` WHERE `articlemmother`.`id` = 15'}]
[比较分析与结论：
1、验证一 count查询 11.340秒，验证二count查询耗时0.030秒，明显是耗时最长的查询，其实也不难理解，100万条数据查起来，即使只查了数量，也确实能明显感知到有点慢，对于count查询，第一次11.340秒，第二次0.030秒，原因应该是django查询的缓存作用，所以第二次查的时候就快了很多，随着缓存被清除，再去刷新页面还是有很长的等待时间，那么既然这里count查询很耗时，那我干脆就给一个固定值，1000000，反正数据量也是固定的，我直接看下有多少就行了，省的再去查了，完美解决页面响应慢的问题，所有页面目前已优化统统秒开
2、验证一的切片limit查询15条数据：0.009s，验证二的遍历15次惟一索引查询，每个都是0.000s，几乎不耗时，相比之下，还是通过id的惟一索引去查更快些，但也不排除15条数据都通过id去查的时间之和会不会被四舍五入后跟limit查询相差不多
总体来说，通过上面所说的方法，在django性能优化时候，对每个查询的执行时间有个把握，就能更有针对性的优化，而不是去盲目猜测怎么优化，更多django好用的、避免重复造轮子的功能也欢迎留言大家共同学习