标签 ORM 下的文章

在使用 SQLAlchemy 时,那些看似很小的选择可能对这种对象关系映射工具包的性能产生重要影响。

 title=

对象关系映射 Object-relational mapping ORM)使应用程序开发人员的工作更轻松,在很大程度是因为它允许你使用你可能知道的语言(例如 Python)与数据库交互,而不是使用原始 SQL 语句查询。SQLAlchemy 是一个 Python ORM 工具包,它提供使用 Python 访问 SQL 数据库的功能。它是一个成熟的 ORM 工具,增加了模型关系、强大的查询构造范式、简单的序列化等优点。然而,它的易用性使得人们很容易忘记其背后发生了什么。使用 SQLAlchemy 时做出的看似很小的选择可能产生非常大的性能影响。

本文解释了开发人员在使用 SQLAlchemy 时遇到的一些最重要的性能问题,以及如何解决这些问题。

只需要计数但检索整个结果集

有时开发人员只需要一个结果计数,但是没有使用数据库计数功能,而是获取了所有结果,然后使用 Python 中的 len 完成计数。

count = len(User.query.filter_by(acct_active=True).all())

相反,使用 SQLAlchemy 的 count 方法将在服务器端执行计数,从而减少发送到客户端的数据。在前面的例子中调用 all() 也会导致模型对象的实例化,如果有很多数据,那么时间代价可能会非常昂贵。

除非还需要做其他的事情,否则只需使用 count 方法:

count = User.query.filter_by(acct_active=True).count()

只需要几列时检索整个模型

在许多情况下,发出查询时只需要几列数据。SQLAlchemy 可以只获取你想要的列,而不是返回整个模型实例。这不仅减少了发送的数据量,还避免了实例化整个对象。使用列数据的元组而不是模型可以快得多。

result = User.query.all()
for user in result:
    print(user.name, user.email)

反之,使用 with_entities 方法只选择所需要的内容:

result = User.query.with_entities(User.name, User.email).all()
for (username, email) in result:
    print(username, email)

每次循环都更新一个对象

避免使用循环来单独更新集合。虽然数据库可以非常快地执行单个更新,但应用程序和数据库服务器之间的往返时间将快速累加。通常,在合理的情况下争取更少的查询。

for user in users_to_update:
  user.acct_active = True
  db.session.add(user)

改用批量更新方法:

query = User.query.filter(user.id.in_([user.id for user in users_to_update]))
query.update({"acct_active": True}, synchronize_session=False)

触发级联删除

ORM 允许在模型关系上进行简单的配置,但是有一些微妙的行为可能会令人吃惊。大多数数据库通过外键和各种级联选项维护关系完整性。SQLAlchemy 允许你使用外键和级联选项定义模型,但是 ORM 具有自己的级联逻辑,可以取代数据库。

考虑以下模型:

class Artist(Base):
    __tablename__ = "artist"

    id = Column(Integer, primary_key=True)
    songs = relationship("Song", cascade="all, delete")


class Song(Base):
    __tablename__ = "song"

    id = Column(Integer, primary_key=True)
    artist_id = Column(Integer, ForeignKey("artist.id", ondelete="CASCADE"))

删除歌手将导致 ORM 在 song 表上发出 delete 查询,从而防止由于外键导致的删除操作。这种行为可能会成为复杂关系和大量记录的瓶颈。

请包含 passive_deletes 选项,以确保让数据库来管理关系。但是,请确保你的数据库具有此功能。例如,SQLite 默认情况下不管理外键。

songs = relationship("Song", cascade  all, delete", passive_deletes=True)

当要使用贪婪加载时,应使用延迟加载

延迟加载是 SQLAlchemy 处理关系的默认方法。从上一个例子构建来看,加载一个歌手时不会同时加载他或她的歌曲。这通常是一个好主意,但是如果总是需要加载某些关系,单独的查询可能会造成浪费。

如果允许以延迟方式加载关系,像 Marshmallow 这样流行的序列化框架可以触发级联查询。

有几种方法可以控制此行为。最简单的方法是通过 relationship 函数本身。

songs = relationship("Song", lazy="joined", cascade="all, delete")

这将导致一个左连接被添加到任何歌手的查询中,因此,songs 集合将立即可用。尽管有更多数据返回给客户端,但往返次数可能会少得多。

SQLAlchemy 为无法采用这种综合方法的情况提供了更细粒度的控制,可以使用 joinedload() 函数在每个查询的基础上切换连接的加载。

from sqlalchemy.orm import joinedload

artists = Artist.query.options(joinedload(Artist.songs))
print(artists.songs) # Does not incur a roundtrip to load

使用 ORM 进行批量记录导入

导入成千上万条记录时,构建完整模型实例的开销会成为主要瓶颈。想象一下,从一个文件中加载数千首歌曲记录,其中每首歌曲都先被转换为字典。

for song in songs:
    db.session.add(Song(`song))

相反,绕过 ORM,只使用核心的 SQLAlchemy 参数绑定功能。

batch = []
insert_stmt = Song.__table__.insert()
for song in songs:
    if len(batch) > 1000:
       db.session.execute(insert_stmt, batch)
       batch.clear()
    batch.append(song)
if batch:
    db.session.execute(insert_stmt, batch)

请记住,此方法会自然而然地跳过你可能依赖的任何客户端 ORM 逻辑,例如基于 Python 的列默认值。尽管此方法比将对象加载为完整的模型实例要快,但是你的数据库可能具有更快的批量加载方法。例如,PostgreSQL 的 COPY 命令为加载大量记录提供了最佳性能。

过早调用提交或刷新

在很多情况下,你需要将子记录与其父记录相关联,反之亦然。一种显然的方法是刷新会话,以便为有问题的记录分配一个 ID。

artist = Artist(name="Bob Dylan")
song = Song(title="Mr. Tambourine Man")

db.session.add(artist)
db.session.flush()

song.artist_id = artist.id

对于每个请求,多次提交或刷新通常是不必要的,也是不可取的。数据库刷新涉及强制在数据库服务器上进行磁盘写入,在大多数情况下,客户端将阻塞,直到服务器确认已写入数据为止。

SQLAlchemy 可以在幕后跟踪关系和管理相关键。

artist = Artist(name="Bob Dylan")
song = Song(title="Mr. Tambourine Man")

artist.songs.append(song)

总结

我希望这一系列常见的陷阱可以帮助你避免这些问题,并使你的应用平稳运行。通常,在诊断性能问题时,测量是关键。大多数数据库都提供性能诊断功能,可以帮助你定位问题,例如 PostgreSQL 的 pg_stat_statements 模块。


via: https://opensource.com/article/19/9/common-pitfalls-python

作者:Zach Todd 选题:lujun9972 译者:MjSeven 校对:wxy

本文由 LCTT 原创编译,Linux中国 荣誉推出

学习怎么去使用 Python 的 web 框架中的对象关系映射与你的数据库交互,就像你使用 SQL 一样。

 title=

你可能听说过 Django,它是一个被称为“完美主义者的最后期限” 的 Python web 框架。它是一匹 可爱的小矮马

Django 的一个强大的功能是它的 对象关系映射 Object-Relational Mapper (ORM),它允许你就像使用 SQL 一样去和你的数据库交互。事实上,Django 的 ORM 就是创建 SQL 去查询和操作数据库的一个 Python 式方式,并且获得 Python 风格的结果。 我说的一种方式,但实际上,它是一种非常聪明的工程方法,它利用了 Python 中一些很复杂的部分,而使得开发者更加轻松。

在我们开始去了解 ORM 是怎么工作之前,我们需要一个可以操作的数据库。和任何一个关系型数据库一样,我们需要去定义一堆表和它们的关系(即,它们相互之间联系起来的方式)。让我们使用我们熟悉的东西。比如说,我们需要去建模一个有博客文章和作者的博客。每个作者有一个名字。一位作者可以有很多的博客文章。一篇博客文章可以有很多的作者、标题、内容和发布日期。

在 Django 村里,这个文章和作者的概念可以被称为博客应用。在这个语境中,一个应用是一个自包含一系列描述我们的博客行为和功能的模型和视图的集合。用正确的方式打包,以便于其它的 Django 项目可以使用我们的博客应用。在我们的项目中,博客正是其中的一个应用。比如,我们也可以有一个论坛应用。但是,我们仍然坚持我们的博客应用的原有范围。

这是为这个教程事先准备的 models.py

from django.db import models

class Author(models.Model):
    name = models.CharField(max_length=100)

    def __str__(self):
        return self.name

class Post(models.Model):
    title = models.CharField(max_length=100)
    content = models.TextField()
    published_date = models.DateTimeField(blank=True, null=True)
    author = models.ManyToManyField(Author, related_name="posts")

    def __str__(self):
        return self.title

现在,看上去似乎有点令人恐惧,因此,我们把它分解来看。我们有两个模型:作者(Author)和文章(Post)。它们都有名字(name)或者标题(title)。文章有个放内容的大的文本字段,以及用于发布时间和日期的 DateTimeField。文章也有一个 ManyToManyField,它同时链接到文章和作者。

大多数的教程都是从头开始的,但是,在实践中并不会发生这种情况。实际上,你会得到一堆已存在的代码,就像上面的 model.py 一样,而你必须去搞清楚它们是做什么的。

因此,现在你的任务是去进入到应用程序中去了解它。做到这一点有几种方法,你可以登入到 Django admin,这是一个 Web 后端,它会列出全部的应用和操作它们的方法。我们先退出它,现在我们感兴趣的东西是 ORM。

我们可以在 Django 项目的主目录中运行 python manage.py shell 去访问 ORM。

/srv/web/django/ $ python manage.py shell

Python 3.6.3 (default, Nov  9 2017, 15:58:30)
[GCC 4.2.1 Compatible Apple LLVM 9.0.0 (clang-900.0.38)] on darwin
Type "help", "copyright", "credits" or "license" for more information.
(InteractiveConsole)
>>>

这将带我们进入到交互式控制台。shell 命令 为我们做了很多设置,包括导入我们的设置和配置 Django 环境。虽然我们启动了 shell,但是,在我们导入它之前,我们并不能访问我们的博客模型。

>>> from blog.models import *

它导入了全部的博客模型,因此,我们可以玩我们的博客了。

首先,我们列出所有的作者:

>>> Author.objects.all()

我们将从这个命令取得结果,它是一个 QuerySet,它列出了我们所有的作者对象。它不会充满我们的整个控制台,因为,如果有很多查询结果,Django 将自动截断输出结果。

>>> Author.objects.all()
<QuerySet [<Author: VM (Vicky) Brasseur>, <Author: Rikki Endsley>,
 <Author: Jen Wike Huger>, '...(remaining elements truncated)...']

我们可以使用 get 代替 all 去检索单个作者。但是,我们需要一些更多的信息才能 get 一个单个记录。在关系型数据库中,表有一个主键,它唯一标识了表中的每个记录,但是,作者名并不唯一。许多人都 重名,因此,它不是唯一约束的好选择。解决这个问题的一个方法是使用一个序列(1、2、3 ……)或者一个通用唯一标识符(UUID)作为主键。但是,因为它对人类并不好用,我们可以通过使用 name 来操作我们的作者对象。

>>> Author.objects.get(name="VM (Vicky) Brasseur")
<Author: VM (Vicky) Brasseur>

到现在为止,我们已经有了一个我们可以交互的对象,而不是一个 QuerySet 列表。我们现在可以与这个 Python 对象进行交互了,使用任意一个表列做为属性去查看对象。

>>> vmb = Author.objects.get(name="VM (Vicky) Brasseur")
>>> vmb.name
u'VM (Vicky) Brasseur'

然后,很酷的事件发生了。通常在关系型数据库中,如果我们希望去展示其它表的信息,我们需要去写一个 LEFT JOIN,或者其它的表耦合函数,并确保它们之间有匹配的外键。而 Django 可以为我们做到这些。

在我们的模型中,由于作者写了很多的文章,因此,我们的作者对象可以检索他自己的文章。

>>> vmb.posts.all()
QuerySet[<Post: "7 tips for nailing your job interview">,
 <Post: "5 tips for getting the biggest bang for your cover letter buck">,
 <Post: "Quit making these 10 common resume mistakes">,
 '...(remaining elements truncated)...']

我们可以使用正常的 Python 式的列表操作方式来操作 QuerySets

>>> for post in vmb.posts.all():
...   print(post.title)
...
7 tips for nailing your job interview
5 tips for getting the biggest bang for your cover letter buck
Quit making these 10 common resume mistakes

要实现更复杂的查询,我们可以使用过滤得到我们想要的内容。这有点复杂。在 SQL 中,你可以有一些选项,比如,likecontains 和其它的过滤对象。在 ORM 中这些事情也可以做到。但是,是通过 特别的 方式实现的:是通过使用一个隐式(而不是显式)定义的函数实现的。

如果在我的 Python 脚本中调用了一个函数 do_thing(),我会期望在某个地方有一个匹配的 def do_thing。这是一个显式的函数定义。然而,在 ORM 中,你可以调用一个 不显式定义的 函数。之前,我们使用 name 去匹配一个名字。但是,如果我们想做一个子串搜索,我们可以使用 name__contains

>>> Author.objects.filter(name__contains="Vic")
QuerySet[<Author: VM (Vicky) Brasseur>, <Author: Victor Hugo">]

现在,关于双下划线(__)我有一个小小的提示。这些是 Python 特有的。在 Python 的世界里,你可以看到如 __main__ 或者 __repr__。这些有时被称为 dunder methods,是 “ 双下划线 double underscore ” 的缩写。仅有几个非字母数字的字符可以被用于 Python 中的对象名字;下划线是其中的一个。这些在 ORM 中被用于显式分隔 过滤关键字 filter key name 的各个部分。在底层,字符串用这些下划线分割开,然后这些标记分开处理。name__contains 被替换成 attribute: name, filter: contains。在其它编程语言中,你可以使用箭头代替,比如,在 PHP 中是 name->contains。不要被双下划线吓着你,正好相反,它们是 Python 的好帮手(并且如果你斜着看,你就会发现它看起来像一条小蛇,想去帮你写代码的小蟒蛇)。

ORM 是非常强大并且是 Python 特有的。不过,还记得我在上面提到过的 Django 的管理网站吗?

 title=

Django 的其中一个非常精彩的用户可访问特性是它的管理界面,如果你定义你的模型,你将看到一个非常好用的基于 web 的编辑门户,而且它是免费的。

ORM,有多强大?

 title=

好吧!给你一些代码去创建最初的模型,Django 就变成了一个基于 web 的门户,它是非常强大的,它可以使用我们前面用过的同样的原生函数。默认情况下,这个管理门户只有基本的东西,但这只是在你的模型中添加一些定义去改变外观的问题。例如,在早期的这些 __str__ 方法中,我们使用这些去定义作者对象应该有什么?(在这种情况中,比如,作者的名字),做了一些工作后,你可以创建一个界面,让它看起来像一个内容管理系统,以允许你的用户去编辑他们的内容。(例如,为一个标记为 “已发布” 的文章,增加一些输入框和过滤)。

如果你想去了解更多内容,Django 美女的教程 中关于 the ORM 的节有详细的介绍。在 Django project website 上也有丰富的文档。

(题图 Christian Holmér,Opensource.com 修改. CC BY-SA 4.0


作者简介:

Katie McLaughlin - Katie 在过去的这几年有许多不同的头衔,她以前是使用多种语言的一位软件开发人员,多种操作系统的系统管理员,和多个不同话题的演讲者。当她不改变 “世界” 的时候,她也去享受烹饪、挂毯艺术,和去研究各种应用程序栈怎么去处理 emoji。


via: https://opensource.com/article/17/11/django-orm

作者:Katie McLaughlin 译者:qhwdw 校对:wxy

本文由 LCTT 原创编译,Linux中国 荣誉推出

在编写高效 SQL 时,你可能遇到的最有影响的事情就是索引。但是,一个很重要的事实就是很多 SQL 客户端要求数据库做很多“不必要的强制性工作”

跟我再重复一遍:

不必要的强制性工作

什么是“不必要的强制性工作”?这个意思包括两个方面:

不必要的

假设你的客户端应用程序需要这些信息:

这没什么特别的。我们运行着一个电影数据库(例如 Sakila 数据库),我们想要给用户显示每部电影的名称和评分。

这是能产生上面结果的查询:

SELECT title, rating
FROM film

然而,我们的应用程序(或者我们的 ORM(LCTT 译注: 对象关系映射 Object-Relational Mapping ))运行的查询却是:

SELECT *
FROM film

我们得到什么?猜一下。我们得到很多无用的信息:

甚至一些复杂的 JSON 数据全程在下列环节中加载:

  • 从磁盘
  • 加载到缓存
  • 通过总线
  • 进入客户端内存
  • 然后被丢弃

是的,我们丢弃了其中大部分的信息。检索它所做的工作完全就是不必要的。对吧?没错。

强制性

这是最糟糕的部分。现今随着优化器变得越来越聪明,这些工作对于数据库来说都是强制执行的。数据库没有办法知道客户端应用程序实际上不需要其中 95% 的数据。这只是一个简单的例子。想象一下如果我们连接更多的表...

你想想那会怎样呢?数据库还快吗?让我们来看看一些之前你可能没有想到的地方:

内存消耗

当然,单次执行时间不会变化很大。可能是慢 1.5 倍,但我们可以忍受,是吧?为方便起见,有时候确实如此。但是如果你每次都为了方便而牺牲性能,这事情就大了。我们不说性能问题(单个查询的速度),而是关注在吞吐量上时(系统响应时间),事情就变得困难而难以解决。你就会受阻于规模的扩大。

让我们来看看执行计划,这是 Oracle 的:

--------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes |
--------------------------------------------------
|   0 | SELECT STATEMENT  |      |  1000 |   166K|
|   1 |  TABLE ACCESS FULL| FILM |  1000 |   166K|
--------------------------------------------------

对比一下:

--------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes |
--------------------------------------------------
|   0 | SELECT STATEMENT  |      |  1000 | 20000 |
|   1 |  TABLE ACCESS FULL| FILM |  1000 | 20000 |
--------------------------------------------------

当执行 SELECT * 而不是 SELECT film, rating 的时候,我们在数据库中使用了 8 倍之多的内存。这并不奇怪,对吧?我们早就知道了。在很多我们并不需要其中全部数据的查询中我们都是这样做的。我们为数据库产生了不必要的强制性工作,其后果累加了起来,就是我们使用了多达 8 倍的内存(当然,数值可能有些不同)。

而现在,所有其它的步骤(比如,磁盘 I/O、总线传输、客户端内存消耗)也受到相同的影响,我这里就跳过了。另外,我还想看看...

索引使用

如今大部分数据库都有涵盖索引(LCTT 译注:covering index,包括了你查询所需列、甚至更多列的索引,可以直接从索引中获取所有需要的数据,而无需访问物理表)的概念。涵盖索引并不是特殊的索引。但对于一个特定的查询,它可以“意外地”或人为地转变为一个“特殊索引”。

看看这个查询:

SELECT *
FROM actor
WHERE last_name LIKE 'A%'

执行计划中没有什么特别之处。它只是个简单的查询。索引范围扫描、表访问,就结束了:

-------------------------------------------------------------------
| Id  | Operation                   | Name                | Rows  |
-------------------------------------------------------------------
|   0 | SELECT STATEMENT            |                     |     8 |
|   1 |  TABLE ACCESS BY INDEX ROWID| ACTOR               |     8 |
|*  2 |   INDEX RANGE SCAN          | IDX_ACTOR_LAST_NAME |     8 |
-------------------------------------------------------------------

这是个好计划吗?如果我们只是想要这些,那么它就不是:

当然,我们浪费了内存之类的。再来看看这个查询:

SELECT first_name, last_name 
FROM actor
WHERE last_name LIKE 'A%'

它的计划是:

----------------------------------------------------
| Id  | Operation        | Name            | Rows  |
----------------------------------------------------
|   0 | SELECT STATEMENT |                 |     8 |
|*  1 |  INDEX RANGE SCAN| IDX_ACTOR_NAMES |     8 |
----------------------------------------------------

现在我们可以完全消除表访问,因为有一个索引涵盖了我们查询需要的所有东西……一个涵盖索引。这很重要吗?当然!这种方法可以将你的某些查询加速一个数量级(如果在某个更改后你的索引不再涵盖,可能会降低一个数量级)。

你不能总是从涵盖索引中获利。索引也有它们自己的成本,你不应该添加太多索引,例如像这种情况就是不明智的。让我们来做个测试:

SET SERVEROUTPUT ON
DECLARE
  v_ts TIMESTAMP;
  v_repeat CONSTANT NUMBER := 100000;
BEGIN
  v_ts := SYSTIMESTAMP;

  FOR i IN 1..v_repeat LOOP
    FOR rec IN (
      -- Worst query: Memory overhead AND table access
      SELECT *
      FROM actor
      WHERE last_name LIKE 'A%'
    ) LOOP
      NULL;
    END LOOP;
  END LOOP;

  dbms_output.put_line('Statement 1 : ' || (SYSTIMESTAMP - v_ts));
  v_ts := SYSTIMESTAMP;

  FOR i IN 1..v_repeat LOOP
    FOR rec IN (
      -- Better query: Still table access
      SELECT /*+INDEX(actor(last_name))*/
        first_name, last_name
      FROM actor
      WHERE last_name LIKE 'A%'
    ) LOOP
      NULL;
    END LOOP;
  END LOOP;

  dbms_output.put_line('Statement 2 : ' || (SYSTIMESTAMP - v_ts));
  v_ts := SYSTIMESTAMP;

  FOR i IN 1..v_repeat LOOP
    FOR rec IN (
      -- Best query: Covering index
      SELECT /*+INDEX(actor(last_name, first_name))*/
        first_name, last_name
      FROM actor
      WHERE last_name LIKE 'A%'
    ) LOOP
      NULL;
    END LOOP;
  END LOOP;

  dbms_output.put_line('Statement 3 : ' || (SYSTIMESTAMP - v_ts));
END;
/

结果是:

Statement 1 : +000000000 00:00:02.479000000
Statement 2 : +000000000 00:00:02.261000000
Statement 3 : +000000000 00:00:01.857000000

注意,表 actor 只有 4 列,因此语句 1 和 2 的差别并不是太令人印象深刻,但仍然很重要。还要注意我使用了 Oracle 的提示来强制优化器为查询选择一个或其它索引。在这种情况下语句 3 明显胜利。这是一个好很多的查询,也是一个十分简单的查询。

当我们写 SELECT * 语句时,我们为数据库带来了不必要的强制性工作,这是无法优化的。它不会使用涵盖索引,因为比起它所使用的 LAST_NAME 索引,涵盖索引开销更多一点,不管怎样,它都要访问表以获取无用的 LAST_UPDATE 列。

使用 SELECT * 会变得更糟。考虑一下……

SQL 转换

优化器工作的很好,因为它们转换了你的 SQL 查询(看我最近在 Voxxed Days Zurich 关于这方面的演讲)。例如,其中有一个称为“表连接消除”的转换,它真的很强大。看看这个辅助视图,我们写了这个视图是因为我们非常讨厌总是连接所有这些表:

CREATE VIEW v_customer AS
SELECT
  c.first_name, c.last_name, 
  a.address, ci.city, co.country
FROM customer c
JOIN address a USING (address_id)
JOIN city ci USING (city_id)
JOIN country co USING (country_id)

这个视图仅仅是把 CUSTOMER 和他们不同的 ADDRESS 部分所有“对一”关系连接起来。谢天谢地,它很工整。

现在,使用这个视图一段时间之后,想象我们非常习惯这个视图,我们都忘了所有它底层的表。然后,我们运行了这个查询:

SELECT *
FROM v_customer

我们得到了一个相当令人印象深刻的计划:

----------------------------------------------------------------
| Id  | Operation            | Name     | Rows  | Bytes | Cost |
----------------------------------------------------------------
|   0 | SELECT STATEMENT     |          |   599 | 47920 |   14 |
|*  1 |  HASH JOIN           |          |   599 | 47920 |   14 |
|   2 |   TABLE ACCESS FULL  | COUNTRY  |   109 |  1526 |    2 |
|*  3 |   HASH JOIN          |          |   599 | 39534 |   11 |
|   4 |    TABLE ACCESS FULL | CITY     |   600 | 10800 |    3 |
|*  5 |    HASH JOIN         |          |   599 | 28752 |    8 |
|   6 |     TABLE ACCESS FULL| CUSTOMER |   599 | 11381 |    4 |
|   7 |     TABLE ACCESS FULL| ADDRESS  |   603 | 17487 |    3 |
----------------------------------------------------------------

当然是这样。我们运行了所有这些表连接以及全表扫描,因为这就是我们让数据库去做的:获取所有的数据。

现在,再一次想一下,对于一个特定场景,我们真正想要的是:

是啊,对吧?现在你应该知道我的意图了。但想像一下,我们确实从前面的错误中学到了东西,现在我们实际上运行下面一个比较好的查询:

SELECT first_name, last_name
FROM v_customer

再来看看结果!

------------------------------------------------------------------
| Id  | Operation          | Name        | Rows  | Bytes | Cost  |
------------------------------------------------------------------
|   0 | SELECT STATEMENT   |             |   599 | 16173 |     4 |
|   1 |  NESTED LOOPS      |             |   599 | 16173 |     4 |
|   2 |   TABLE ACCESS FULL| CUSTOMER    |   599 | 11381 |     4 |
|*  3 |   INDEX UNIQUE SCAN| SYS_C007120 |     1 |     8 |     0 |
------------------------------------------------------------------

这是执行计划一个极大的进步。我们的表连接被消除了,因为优化器可以证明它们是不必要的,因此一旦它可以证明这点(而且你不会因使用 select * 而使其成为强制性工作),它就可以移除这些工作并不执行它。为什么会发生这种情况?

每个 CUSTOMER.ADDRESS_ID 外键保证了有且只有一个 ADDRESS.ADDRESS_ID 主键值,因此可以保证 JOIN 操作是对一连接,它不会产生或者删除行。如果我们甚至不选择行或查询行,当然我们就不需要真正地去加载行。可以证实地移除 JOIN 并不会改变查询的结果。

数据库总是会做这些事情。你可以在大部分数据库上尝试它:

-- Oracle
SELECT CASE WHEN EXISTS (
  SELECT 1 / 0 FROM dual
) THEN 1 ELSE 0 END
FROM dual

-- 更合理的 SQL 语句,例如 PostgreSQL
SELECT EXISTS (SELECT 1 / 0)

在这种情况下,当你运行这个查询时你可能预料到会抛出算术异常:

SELECT 1 / 0 FROM dual

产生了:

ORA-01476: divisor is equal to zero

但它并没有发生。优化器(甚至解析器)可以证明 EXISTS (SELECT ..) 谓词内的任何 SELECT 列表达式不会改变查询的结果,因此也就没有必要计算它的值。呵!

同时……

大部分 ORM 最不幸问题就是事实上他们很随意就写出了 SELECT * 查询。事实上,例如 HQL / JPQL,就设置默认使用它。你甚至可以完全抛弃 SELECT 从句,因为毕竟你想要获取所有实体,正如声明的那样,对吧?

例如:

FROM v_customer

例如 Vlad Mihalcea(一个 Hibernate 专家和 Hibernate 开发倡导者)建议你每次确定不想要在获取后进行任何更改时再使用查询。ORM 使解决对象图持久化问题变得简单。注意:持久化。真正修改对象图并持久化修改的想法是固有的。

但如果你不想那样做,为什么要抓取实体呢?为什么不写一个查询?让我们清楚一点:从性能角度,针对你正在解决的用例写一个查询总是会胜过其它选项。你可以不会在意,因为你的数据集很小,没关系。可以。但最终,你需要扩展并重新设计你的应用程序以便在强制实体图遍历之上支持查询语言,就会变得很困难。你也需要做其它事情。

计算出现次数

资源浪费最严重的情况是在只是想要检验存在性时运行 COUNT(*) 查询。例如:

这个用户有没有订单?

我们会运行:

SELECT count(*)
FROM orders
WHERE user_id = :user_id

很简单。如果 COUNT = 0:没有订单。否则:是的,有订单。

性能可能不会很差,因为我们可能有一个 ORDERS.USER_ID 列上的索引。但是和下面的这个相比你认为上面的性能是怎样呢:

-- Oracle
SELECT CASE WHEN EXISTS (
  SELECT *
  FROM orders
  WHERE user_id = :user_id
) THEN 1 ELSE 0 END
FROM dual

-- 更合理的 SQL 语句,例如 PostgreSQL
SELECT EXISTS (
  SELECT *
  FROM orders
  WHERE user_id = :user_id
)

它不需要火箭科学家来确定,一旦它找到一个,实际存在谓词就可以马上停止寻找额外的行。因此,如果答案是“没有订单”,速度将会是差不多。但如果结果是“是的,有订单”,那么结果在我们不计算具体次数的情况下就会大幅加快。

因为我们不在乎具体的次数。我们告诉数据库去计算它(不必要的),而数据库也不知道我们会丢弃所有大于 1 的结果(强制性)。

当然,如果你在 JPA 支持的集合上调用 list.size() 做同样的事情,情况会变得更糟!

近期我有关于该情况的博客以及在不同数据库上的测试。去看看吧。

总结

这篇文章的立场很“明显”。别让数据库做不必要的强制性工作

不必要,因为对于你给定的需求,你知道一些特定的工作不需要完成。但是,你告诉数据库去做。

强制性,因为数据库无法证明它是不必要的。这些信息只包含在客户端中,对于服务器来说无法访问。因此,数据库需要去做。

这篇文章大部分在介绍 SELECT *,因为这是一个很简单的目标。但是这并不仅限于数据库。这关系到客户端要求服务器完成不必要的强制性工作的任何分布式算法。你的 AngularJS 应用程序平均有多少个 N+1 问题,UI 在服务结果 A 上循环,多次调用服务 B,而不是把所有对 B 的调用打包为一个调用?这是一个复发的模式。

解决方法总是相同。你给执行你命令的实体越多信息,(理论上)它能更快执行这样的命令。每次都写一个好的查询。你的整个系统都会为此感谢你的。

如果你喜欢这篇文章...

再看看近期我在 Voxxed Days Zurich 的演讲,其中我展示了一些在数据处理算法上为什么 SQL 总是会胜过 Java 的双曲线例子。

(题图:Pixabay, CC0)


via: https://blog.jooq.org/2017/03/08/many-sql-performance-problems-stem-from-unnecessary-mandatory-work

作者:jooq 译者:ictlyh 校对:wxy

本文由 LCTT 原创编译,Linux中国 荣誉推出