Py学习  »  Django

我们如何在Django中使用高级SQL将响应时间减半

Python程序员 • 4 年前 • 333 次点击  


如何在Django中使用分组集(Grouping Set)


最近,我有幸参与了一个对旧仪表板进行优化的项目。我们提出的解决方案需要使用一些Django不支持的高级SQL。在本文中,我将介绍这个问题的解决方案、我们是如何实现它的,并提出一些注意事项。


仪表板


这个仪表板是一个销售模型。它包括一个简单的表,其中包含按商家及其设备分组的指标,以及一个汇总行。


生成该表的代码大致如下:



生成汇总行的代码使用相同的指标,看起来像这样:



我们的admin页面会得到一个漂亮的仪表板,大致如下:


Django Admin 页面中的一个汇总行


请参阅:

有关如何创建上面的仪表板的内容请查看:如何将 Django Admin 转换成一个轻量级的仪表板

(链接地址:https://hakibenita.com/how-to-turn-django-admin-into-a-lightweight-dashboard  )


存在的问题


这个仪表板在大约三年的时间里运行良好。我们得到了良好的响应时间和准确的信息。然而,随着数据的堆积,它的性能已经下降到页面无法使用的程度。


为了分析这个问题,我们检查了SQL,并对其进行计时测试。生成表的查询如下:



最坏的情况下,这个查询需要大约30秒的时间才能完成。

仪表板执行的下一个查询用于生成汇总行:



这个查询花费了大约相同的时间,大约30秒。在最糟糕的情况下,这两个查询总共花费了超过一分钟的时间才完成。


在内存中进行合计


这两个查询处理的是完全相同的数据,唯一的区别是GROUP BY 键。第一个查询在商家和设备级别上生成结果,第二个查询为整个数据集生成相同的合计。


我们首先想到的是通过在内存中合计结果来计算汇总。


第一个指标,total,很容易计算:



第二个指标是平均收费金额。我们不能简单地把每个设备和商家的平均收费金额加起来,我们需要更多的信息。


要计算所有商家和设备的平均收费金额,我们需要将总收费金额除以销售数量。我们已经有了销售数量,所以我们需要为总收费金额添加一个指标:



现在我们已经有了total和total_charged_amount,我们就可以计算avg_charged_amount了:



我们还剩下一个指标,unique_users。这个指标会计数访问每个商家的每个设备的唯一用户数。同一用户可以访问不同商家的多个设备。如果我们对unique_users进行求和,我们就不能得到整个集合的正确指标。


从合计的结果中计算出特值是不可能的,因此解决方案肯定在数据库中。


在数据库中进行合计


大多数SQL实现都提供了几个有用的函数来在不同的级别上合计数据。


数据库支持:

在本文中,我使用PostgreSQL数据库。 在Oracle、MySQL和MSSQL中也有类似的函数。据我所知,SQLite不支持我将要使用的函数。

让我们从一些数据开始:



我们在仪表板中使用的查询会生成以下结果:



生成汇总行的查询:



ROLLUP (汇总)


第一个特殊的GROUP BY表达式是ROLLUP。顾名思义,ROLLUP在最低级别并向上进行合计:



我们按device (设备)和merchant (商家)这两个字段进行分组,得到了三组合计:


  1. () all

  2. (device, merchant)

  3. (device)


ROLLUP会“向上”进行合计,因此字段的顺序非常重要。我们来翻转字段的顺序:



这次我们得到了以下几组:


  1. () all

  2. (merchant, device)

  3. (merchant)


Cube


下一个分组表达式很可能是从OLAP借鉴来的,OLAP中经常提到cube(多维数据集)。CUBE表达式会合计所有可能的组合:



运行结果包括以下分组:


  1. () all

  2. (device, merchant)

  3. (merchant)

  4. (device)


分组集


分组集允许我们提供所需的精确合计分组。例如,要重新创建上面ROLLUP的结果,我们可以提供以下分组集:



GROUPING SETS中括号内的每个字段列表在结果中都是一个组。


CUBE 和ROLLUP都可以使用GROUPING SETS来实现。下表显示了针对ROLLUP和CUBE在两个字段a和b上的等价GROUPING SETS表达式:



在我们最初的查询中,我们有商家和设备级别的指标,并且我们希望得到一个汇总行。使用GROUPING SETS,该查询将看起来像这样:



前6行与原始查询类似。最后一行类似于我们使用的汇总查询的结果。


使用GROUPING SETS,我们可以在一个查询获得所需的结果,而不需要使用两个查询。


在Django中使用分组集


现在我们有了这个查询,我们需要找到一种方法在Django中使用它。不幸的是,Django现在仍然不支持分组集。最重要的是,这个查询由Django Admin生成,它包括来自列表过滤器和日期层次结构中的predicate(断言)。因此,我们不能只使用原始SQL。


我们需要找到一种方法来修改给定的Django QuerySet,并向它添加分组集。


由于Django没有针对分组集的内置支持,所以我们不得不对该查询进行操作。我们需要操作的基查询是Django生成的查询,以及Django Admin添加的任何断言和注释。最后,我们希望像Django一样在数据库中执行这个查询。获取查询Django QuerySet 的一个很不错的特性是它提供了生成的SQL:



这是一个简单的查询,我们可以直接在数据库中执行它吗?



这看起来是我们可以处理的,让我们继续深入研究……


如前所述,QuerySet是由Django Admin生成的,它可能包含列表过滤器和日期层次结构中的断言。让我们尝试在sold_at 日期字段上使用断言来执行一个查询:



看起来Django无法按原样执行该查询。原因是str(qs.query)生成的文本只是查询的一个文本表示。实际上,Django使用了正确的绑定变量(也可以称为替换变量)来避免SQL注入。


大部分Django ORM QuerySet逻辑是由一个名为Query的内部类执行的。该类还没有文档化,了解它的唯一地方是在源代码中。Query的一个很有前途的函数是sql_with_params。让我们在上面的查询中使用它,看看我们能得到什么:



sql_with_params函数会返回一个元组。该元组的第一个参数是SQL查询。第二个是该查询的参数列表。


敏锐的人可能在查询文本中发现了占位符%s:



这个占位符对应于我们在第二个形参中得到的实参。让我们尝试使用占位符和参数来执行该查询:



太棒了!现在我们可以像Django那样来执行一个查询。我们已经准备好操作这个查询了。


对查询进行操作


Django生成的查询包含一个简单的GROUP BY子句:



我们想用下面的group by子句来替换它:



这看起来像是re的工作。


我们希望捕获GROUP BY和ORDER BY之间的分组字段,并使它们成为GROUPING SET表达式中的第一个组。然后,我们想要为汇总添加分组():



现在我们可以使用修改后的查询,并使用参数来执行它:



你瞧……我们现在在一个查询中获得了结果和汇总行。


结论


考虑使用这种方法时要考虑的几个重要问题:


  • 不要做这些!: 这是最糟糕的。这种方法是一个很好的练习,也是研究ORM内部机制的好机会,但是它的实现太脆弱了。当使用一个内部的、未文档化的API时,我们并不能保证它在将来不会意外地改变。说到这里,我们决定在一个内部管理页面中使用这种方法。这是一个非常特殊的场景,涉及一个查询集,它不用于任何面向用户的功能。它帮助我们将页面响应时间精确地减少了一半,并且我们对结果很满意。


  • 确定排序顺序: 当使用GROUPING SETS  (以及ROLLUP或CUBE)时,你在一个查询中混合了多个级别的合计。为了能够以一个可预测的方式获取结果,显式地对结果排序非常重要。例如,在上面的查询中,要确保汇总行是第一行,并添加以下排序顺序 qs.order_by( F('merchant').desc(nulls_last=False) )。


英文原文:https://hakibenita.com/how-to-use-grouping-sets-in-django
译者:一瞬
Python社区是高质量的Python/Django开发社区
本文地址:http://www.python88.com/topic/36153
 
333 次点击