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

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

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

 

仪表板

 

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

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

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

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

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

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

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

Django Admin 页面中的一个汇总行

请参阅:

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

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

存在的问题

 

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

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

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

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

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

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

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

在内存中进行合计

 

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

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

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

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

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

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

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

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

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

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

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

在数据库中进行合计

 

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

数据库支持:

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

让我们从一些数据开始:

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

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

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

生成汇总行的查询:

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

ROLLUP (汇总)

 

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

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

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

  1. () all

  2. (device, merchant)

  3. (device)

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

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

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

  1. () all

  2. (merchant, device)

  3. (merchant)

Cube

 

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

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

运行结果包括以下分组:

  1. () all

  2. (device, merchant)

  3. (merchant)

  4. (device)

分组集

 

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

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

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

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

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

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

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

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

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

在Django中使用分组集

 

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

 

对查询进行操作

 

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

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

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

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

这看起来像是re的工作。

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

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

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

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

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

结论

 

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

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

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

原创文章,作者:余 倩倩,如若转载,请注明出处:https://www.pmtemple.com/flyingfish/10022/

(0)
打赏 微信扫一扫 微信扫一扫 支付宝扫一扫 支付宝扫一扫
余 倩倩的头像余 倩倩前端大师
上一篇 2019年7月18日 下午11:50
下一篇 2019年7月21日 下午4:00

相关推荐

发表回复

登录后才能评论
微信公众号
微信公众号
edgesensor_high 小程序
小程序
分享本页
返回顶部