使用SQL日志中间件进行Django SQL调试-优化Django，第1部分

If you’re into web development or just software engineering in general, you might have crossed paths with Django-REST. Django-REST has become sort of a gold standard for developers who (with reasonable effort) want to build the quintessential web server of the 2010’s: Open Source, REST protocol, MVS design pattern, and on Python, no less.

如果您通常是从事Web开发或只是软件工程，那么使用Django-REST可能会遇到麻烦。 对于(以合理的努力)想要构建2010年代最典型的Web服务器的开发人员来说，Django-REST已经成为一种黄金标准：开源，REST协议，MVS设计模式，并且在Python上也是如此。

While Django does quite a good job of hiding most of the dirty SQL under its veneer of Models and QuerySets, in some situations we want to be able to optimise expensive database queries, whether to reduce RTT or avoid wasting valuable server time. If you’re reading this, I’ll take it that you’re looking to optimise your Django queries already, so at the very least, let’s make sure that your optimisations are verified with measurements.

尽管Django很好地将大多数脏SQL隐藏在其Models和QuerySets单板下，但在某些情况下，我们希望能够优化昂贵的数据库查询，无论是减少RTT还是避免浪费宝贵的服务器时间。 如果您正在阅读本文，我认为您已经在优化Django查询，因此至少，请确保您的优化已通过度量验证。

In this mini-series, I will cover various tips that you can use to help cut a lot of wasted server processing time out of your queries.

在这个迷你系列中，我将介绍各种技巧，您可以使用这些技巧来帮助减少查询中浪费的服务器处理时间。

在开始之前，请先进行一些设置。 (Before we start, just a couple of things about the setup.)

I’m using the following for this series:

我在本系列中使用以下内容：

IDE — PycharmPython 3Latest Django 1. Django 1 support ends in 2020, so it is recommended to use Django 3. Fortunately, most of this series should apply just as well to Django 2 and 3.

IDE — PycharmPython 3最新的Django1。对Django 1的支持将于2020年结束，因此建议使用Django3。幸运的是， 本系列的大多数内容也应同样适用于Django 2和3。

I will also assume you have an intermediate-level knowledge of Django so I can skip some bits of typical Django configuration.

我还将假设您具有Django的中级知识，因此我可以跳过一些典型的Django配置。

With that out of the way, let’s get right into it.

顺便说一句，让我们开始吧。

在本集中：获得一个SQL调试中间件。 (In this episode: Get an SQL debugging middleware.)

If you’re unfamiliar with middleware, basically your requests are filtered down a whole bunch of middleware. Some perform authentication, while others deal with CSRF tokens. For more information, Django has an article all about this.

如果您不熟悉中间件，则基本上可以将您的请求过滤掉一大堆中间件。 一些执行身份验证，而其他一些则处理CSRF令牌。 有关更多信息， Django有一篇有关this的文章 。

Django also allows you to create your own middleware, which we’re going to make full use of here to print out the SQL queries that are executed with every request the server receives.

Django还允许您创建自己的中间件，我们将在这里充分利用该中间件来打印出服务器收到的每个请求所执行SQL查询。

Github user vstoykov posted a middleware snippet which is really useful, and I have used it in my own development (with some modifications) to great effect as well. Upon receiving a request, this middleware basically:

Github用户vstoykov发布了一个非常有用的中间件代码片段，我也将其用于自己的开发中(进行了一些修改)，效果也很好。 收到请求后，该中间件基本上是：

1. Reads all queries made via django.db.connection,
   读取通过django.db.connection进行的所有查询，
2. Prints out the SQL for each query, and importantly, the time taken for each SQL query to execute.
   打印出每个查询SQL，重要的是，打印出每个SQL查询执行所花费的时间。

现在获取一些代码！ (And now for some code!)

And when you have a middleware like this, you can add it to your Django project into your settings.py file like this:

并且当您拥有这样的中间件时，可以将其添加到Django项目的settings.py文件中，如下所示：

import os... Doing your settings file stuff ...
# If you want, you can lock the middleware behind an env var.if os.environ.get('SQL_DEBUG', False):
    MIDDLEWARE += ('sql_middleware.SqlPrintingMiddleware',)
... Continue with your settings file stuff ...

接下来，让我们测试我们的新中间件。 (Next, let’s test our new middleware.)

Go ahead and run a sample Django server instance with a basic Django admin. To be sure, you should be able to see the server logs like so:

继续并使用基本的Django管理员运行示例Django服务器实例。 可以肯定的是，您应该能够看到服务器日志，如下所示：

Django server log

Django服务器日志

Now log in on the Django admin using a web browser and the corresponding server logs should look something like this.

现在，使用Web浏览器登录Django admin，相应的服务器日志应如下所示。

Django server log after logging in on the Admin page

在Admin页面上登录后的Django服务器日志

As you can see, the SQL logs are printed out after each HTTP call, with SQL printed out against the times taken to execute each call.

如您所见，SQL日志在每个HTTP调用之后被打印出来，并且根据执行每个调用所花费的时间来打印SQL。

总结一下... (To wrap up…)

As you can imagine, having a per-view SQL logger is very useful for SQL debugging. We will be using this logger throughout the rest of this series to help us optimise SQL queries in Django, reducing our SQL footprint in various places while using this logger to confirm that our optimisations are actually working as intended.

可以想象，拥有按视图SQL记录器对于SQL调试非常有用。 在本系列文章的其余部分中，我们将使用该记录器来帮助我们优化Django中SQL查询，从而减少我们在各个地方SQL占用量，同时使用该记录器来确认我们的优化实际上正在按预期进行。

But in the meantime, I hope this article will help you greatly. Even as it is, it can provide great insights into the sorts of queries that are time-consuming in your application, allowing you to decide which queries to work on.

但与此同时，我希望本文对您有所帮助。 即使是这样，它也可以为您的应用程序中费时的各种查询提供深刻的见解，使您可以决定要处理的查询。

Edit: Click here to check out Part 2, where we go into our first SQL optimisation in the Django admin!

编辑： 单击此处以检出第2部分 ，我们在Django管理员中进行了第一个SQL优化！

翻译自: https://levelup.gitconnected.com/django-sql-debugging-with-an-sql-log-middleware-optimising-django-part-1-ca3b5c20d892