标签 索引 下的文章

由于我刚刚提交了最后一个改进 PostgreSQL 11 哈希索引的补丁,并且大部分哈希索引的改进都致力于预计下周发布的 PostgreSQL 10(LCTT 译注:已发布),因此现在似乎是对过去 18 个月左右所做的工作进行简要回顾的好时机。在版本 10 之前,哈希索引在并发性能方面表现不佳,缺少预写日志记录,因此在宕机或复制时都是不安全的,并且还有其他二等公民。在 PostgreSQL 10 中,这在很大程度上被修复了。

虽然我参与了一些设计,但改进哈希索引的首要功劳来自我的同事 Amit Kapila,他在这个话题下的博客值得一读。哈希索引的问题不仅在于没有人打算写预写日志记录的代码,还在于代码没有以某种方式进行结构化,使其可以添加实际上正常工作的预写日志记录。要拆分一个桶,系统将锁定已有的桶(使用一种十分低效的锁定机制),将半个元组移动到新的桶中,压缩已有的桶,然后松开锁。即使记录了个别更改,在错误的时刻发生崩溃也会使索引处于损坏状态。因此,Aimt 首先做的是重新设计锁定机制。新的机制在某种程度上允许扫描和拆分并行进行,并且允许稍后完成那些因报错或崩溃而被中断的拆分。完成了一系列漏洞的修复和一些重构工作,Aimt 就打了另一个补丁,添加了支持哈希索引的预写日志记录

与此同时,我们发现哈希索引已经错过了许多已应用于 B 树索引多年的相当明显的性能改进。因为哈希索引不支持预写日志记录,以及旧的锁定机制十分笨重,所以没有太多的动机去提升其他的性能。而这意味着如果哈希索引会成为一个非常有用的技术,那么需要做的事只是添加预写日志记录而已。PostgreSQL 索引存取方法的抽象层允许索引保留有关其信息的后端专用缓存,避免了重复查询索引本身来获取相关的元数据。B 树和 SQLite 的索引正在使用这种机制,但哈希索引没有,所以我的同事 Mithun Cy 写了一个补丁来使用此机制缓存哈希索引的元页。同样,B 树索引有一个称为“单页回收”的优化,它巧妙地从索引页移除没用的索引指针,从而防止了大量索引膨胀。我的同事 Ashutosh Sharma 打了一个补丁将这个逻辑移植到哈希索引上,也大大减少了索引的膨胀。最后,B 树索引自 2006 年以来就有了一个功能,可以避免重复锁定和解锁同一个索引页——所有元组都在页中一次性删除,然后一次返回一个。Ashutosh Sharma 也将此逻辑移植到了哈希索引中,但是由于缺少时间,这个优化没有在版本 10 中完成。在这个博客提到的所有内容中,这是唯一一个直到版本 11 才会出现的改进。

关于哈希索引的工作有一个更有趣的地方是,很难确定行为是否真的正确。锁定行为的更改只可能在繁重的并发状态下失败,而预写日志记录中的错误可能仅在崩溃恢复的情况下显示出来。除此之外,在每种情况下,问题可能是微妙的。没有东西崩溃还不够;它们还必须在所有情况下产生正确的答案,并且这似乎很难去验证。为了协助这项工作,我的同事 Kuntal Ghosh 先后跟进了最初由 Heikki Linnakangas 和 Michael Paquier 开始的工作,并且制作了一个 WAL 一致性检查器,它不仅可以作为开发人员测试的专用补丁,还能真正提交到 PostgreSQL。在提交之前,我们对哈希索引的预写日志代码使用此工具进行了广泛的测试,并十分成功地查找到了一些漏洞。这个工具并不仅限于哈希索引,相反:它也可用于其他模块的预写日志记录代码,包括堆,当今的所有 AM 索引,以及一些以后开发的其他东西。事实上,它已经成功地在 BRIN 中找到了一个漏洞

虽然 WAL 一致性检查是主要的开发者工具——尽管它也适合用户使用,如果怀疑有错误——也可以升级到专为数据库管理人员提供的几种工具。Jesper Pedersen 写了一个补丁来升级 pageinspect contrib 模块来支持哈希索引,Ashutosh Sharma 做了进一步的工作,Peter Eisentraut 提供了测试用例(这是一个很好的办法,因为这些测试用例迅速失败,引发了几轮漏洞修复)。多亏了 Ashutosh Sharma 的工作,pgstattuple contrib 模块也支持哈希索引了

一路走来,也有一些其他性能的改进。我一开始没有意识到的是,当一个哈希索引开始新一轮的桶拆分时,磁盘上的大小会突然加倍,这对于 1MB 的索引来说并不是一个问题,但是如果你碰巧有一个 64GB 的索引,那就有些不幸了。Mithun Cy 通过编写一个补丁,把加倍过程分为四个阶段在某个程度上解决了这一问题,这意味着我们将从 64GB 到 80GB 到 96GB 到 112GB 到 128GB,而不是一次性从 64GB 到 128GB。这个问题可以进一步改进,但需要对磁盘格式进行更深入的重构,并且需要仔细考虑对查找性能的影响。

七月时,一份来自于“AP”测试人员的报告使我们感到需要做进一步的调整。AP 发现,若试图将 20 亿行数据插入到新创建的哈希索引中会导致错误。为了解决这个问题,Amit 修改了拆分桶的代码,使得在每次拆分之后清理旧的桶,大大减少了溢出页的累积。为了得以确保,Aimt 和我也增加了四倍的位图页的最大数量,用于跟踪溢出页分配。

虽然还是有更多的事情要做,但我觉得,我和我的同事们——以及在 PostgreSQL 团队中的其他人的帮助下——已经完成了我们的目标,使哈希索引成为一个一流的功能,而不是被严重忽视的半成品。不过,你或许会问,这个功能可能有哪些应用场景。我在文章开头提到的(以及链接中的)Amit 的博客内容表明,即使是 pgbench 的工作负载,哈希索引页也可能在低级和高级并发方面优于 B 树。然而,从某种意义上说,这确实是最坏的情况。哈希索引的卖点之一是,索引存储的是字段的哈希值,而不是原始值——所以,我希望像 UUID 或者长字符串的宽键将有更大的改进。它们可能会在读取繁重的工作负载时做得更好。我们没有像优化读取那种程度来优化写入,但我鼓励任何对此技术感兴趣的人去尝试并将结果发到邮件列表(或发私人电子邮件),因为对于开发一个功能而言,真正关键的并不是一些开发人员去思考在实验室中会发生什么,而是在实际中发生了什么。

最后,我要感谢 Jeff Janes 和 Jesper Pedersen 为这个项目及其相关所做的宝贵的测试工作。这样一个规模适当的项目并不易得,以及有一群坚持不懈的测试人员,他们勇于打破任何废旧的东西的决心起了莫大的帮助。除了以上提到的人之外,其他人同样在测试,审查以及各种各样的日常帮助方面值得赞扬,其中包括 Andreas Seltenreich,Dilip Kumar,Tushar Ahuja,Alvaro Herrera,Micheal Paquier,Mark Kirkwood,Tom Lane,Kyotaro Horiguchi。谢谢你们,也同样感谢那些本该被提及却被我无意中忽略的所有朋友。


via:https://rhaas.blogspot.jp/2017/09/postgresqls-hash-indexes-are-now-cool.html

作者:Robert Haas 译者:polebug 校对:wxy

本文由[LCTT]([https://github.com/LCTT/TranslateProject)原创编译,[Linux中国](https://linux.cn/)荣誉推出](https://github.com/LCTT/TranslateProject%EF%BC%89%E5%8E%9F%E5%88%9B%E7%BC%96%E8%AF%91%EF%BC%8C%5BLinux%E4%B8%AD%E5%9B%BD%5D%EF%BC%88https://linux.cn/%EF%BC%89%E8%8D%A3%E8%AA%89%E6%8E%A8%E5%87%BA)

在 Citus 公司,为让事情做的更好,我们与客户一起在数据建模、优化查询、和增加 索引上花费了许多时间。我的目标是为客户的需求提供更好的服务,从而创造成功。我们所做的其中一部分工作是持续为你的 Citus 集群保持良好的优化和 高性能;另外一部分是帮你了解关于 Postgres 和 Citus 你所需要知道的一切。毕竟,一个健康和高性能的数据库意味着 app 执行的更快,并且谁不愿意这样呢? 今天,我们简化一些内容,与客户分享一些关于 Postgres 索引的信息。

Postgres 有几种索引类型, 并且每个新版本都似乎增加一些新的索引类型。每个索引类型都是有用的,但是具体使用哪种类型取决于(1)数据类型,有时是(2)表中的底层数据和(3)执行的查找类型。接下来的内容我们将介绍在 Postgres 中你可以使用的索引类型,以及你何时该使用何种索引类型。在开始之前,这里有一个我们将带你亲历的索引类型列表:

  • B-Tree
  • 倒排索引 Generalized Inverted Index (GIN)
  • 倒排搜索树 Generalized Inverted Seach Tree (GiST)
  • 空间分区的 Space partitioned GiST (SP-GiST)
  • 块范围索引 Block Range Index (BRIN)
  • Hash

现在开始介绍索引。

在 Postgres 中,B-Tree 索引是你使用的最普遍的索引

如果你有一个计算机科学的学位,那么 B-Tree 索引可能是你学会的第一个索引。B-tree 索引 会创建一个始终保持自身平衡的一棵树。当它根据索引去查找某个东西时,它会遍历这棵树去找到键,然后返回你要查找的数据。使用索引是大大快于顺序扫描的,因为相对于顺序扫描成千上万的记录,它可以仅需要读几个 (当你仅返回几个记录时)。

如果你运行一个标准的 CREATE INDEX 语句,它将为你创建一个 B-tree 索引。 B-tree 索引在大多数的数据类型上是很有价值的,比如文本、数字和时间戳。如果你刚开始在你的数据库中使用索引,并且不在你的数据库上使用太多的 Postgres 的高级特性,使用标准的 B-Tree 索引可能是你最好的选择。

GIN 索引,用于多值列

倒排索引 Generalized Inverted Index ,一般称为 GIN,大多适用于当单个列中包含多个值的数据类型。

据 Postgres 文档:

“GIN 设计用于处理被索引的条目是复合值的情况,并且由索引处理的查询需要搜索在复合条目中出现的值。例如,这个条目可能是文档,查询可以搜索文档中包含的指定字符。”

包含在这个范围内的最常见的数据类型有:

关于 GIN 索引中最让人满意的一件事是,它们能够理解存储在复合值中的数据。但是,因为一个 GIN 索引需要有每个被添加的单独类型的数据结构的特定知识,因此,GIN 索引并不是支持所有的数据类型。

GiST 索引, 用于有重叠值的行

倒排搜索树 Generalized Inverted Seach Tree (GiST)索引多适用于当你的数据与同一列的其它行数据重叠时。GiST 索引最好的用处是:如果你声明一个几何数据类型,并且你希望知道两个多边型是否包含一些点时。在一种情况中一个特定的点可能被包含在一个盒子中,而与此同时,其它的点仅存在于一个多边形中。使用 GiST 索引的常见数据类型有:

  • 几何类型
  • 需要进行全文搜索的文本类型

GiST 索引在大小上有很多的固定限制,否则,GiST 索引可能会变的特别大。作为其代价,GiST 索引是有损的(不精确的)。

据官方文档:

“GiST 索引是有损的,这意味着索引可能产生虚假匹配,所以需要去检查真实的表行去消除虚假匹配。 (当需要时 PostgreSQL 会自动执行这个动作)”

这并不意味着你会得到一个错误结果,它只是说明了在 Postgres 给你返回数据之前,会做了一个很小的额外工作来过滤这些虚假结果。

特别提示:同一个数据类型上 GIN 和 GiST 索引往往都可以使用。通常一个有很好的性能表现,但会占用很大的磁盘空间,反之亦然。说到 GIN 与 GiST 的比较,并没有某个完美的方案可以适用所有情况,但是,以上规则应用于大部分常见情况。

SP-GiST 索引,用于更大的数据

空间分区 GiST (SP-GiST)索引采用来自 Purdue 研究的空间分区树。 SP-GiST 索引经常用于当你的数据有一个天然的聚集因素,并且不是一个平衡树的时候。 电话号码是一个非常好的例子 (至少 US 的电话号码是)。 它们有如下的格式:

  • 3 位数字的区域号
  • 3 位数字的前缀号 (与以前的电话交换机有关)
  • 4 位的线路号

这意味着第一组前三位处有一个天然的聚集因素,接着是第二组三位,然后的数字才是一个均匀的分布。但是,在电话号码的一些区域号中,存在一个比其它区域号更高的饱合状态。结果可能导致树非常的不平衡。因为前面有一个天然的聚集因素,并且数据不对等分布,像电话号码一样的数据可能会是 SP-GiST 的一个很好的案例。

BRIN 索引, 用于更大的数据

块范围索引(BRIN)专注于一些类似 SP-GiST 的情形,它们最好用在当数据有一些自然排序,并且往往数据量很大时。如果有一个以时间为序的 10 亿条的记录,BRIN 也许就能派上用场。如果你正在查询一组很大的有自然分组的数据,如有几个邮编的数据,BRIN 能帮你确保相近的邮编存储在磁盘上相近的地方。

当你有一个非常大的比如以日期或邮编排序的数据库, BRIN 索引可以让你非常快的跳过或排除一些不需要的数据。此外,与整体数据量大小相比,BRIN 索引相对较小,因此,当你有一个大的数据集时,BRIN 索引就可以表现出较好的性能。

Hash 索引, 总算不怕崩溃了

Hash 索引在 Postgres 中已经存在多年了,但是,在 Postgres 10 发布之前,对它们的使用一直有个巨大的警告,它不是 WAL-logged 的。这意味着如果你的服务器崩溃,并且你无法使用如 wal-g 故障转移到备机或从存档中恢复,那么你将丢失那个索引,直到你重建它。 随着 Postgres 10 发布,它们现在是 WAL-logged 的,因此,你可以再次考虑使用它们 ,但是,真正的问题是,你应该这样做吗?

Hash 索引有时会提供比 B-Tree 索引更快的查找,并且创建也很快。最大的问题是它们被限制仅用于“相等”的比较操作,因此你只能用于精确匹配的查找。这使得 hash 索引的灵活性远不及通常使用的 B-Tree 索引,并且,你不能把它看成是一种替代品,而是一种用于特殊情况的索引。

你该使用哪个?

我们刚才介绍了很多,如果你有点被吓到,也很正常。 如果在你知道这些之前, CREATE INDEX 将始终为你创建使用 B-Tree 的索引,并且有一个好消息是,对于大多数的数据库, Postgres 的性能都很好或非常好。 :) 如果你考虑使用更多的 Postgres 特性,下面是一个当你使用其它 Postgres 索引类型的备忘清单:

  • B-Tree - 适用于大多数的数据类型和查询
  • GIN - 适用于 JSONB/hstore/arrays
  • GiST - 适用于全文搜索和几何数据类型
  • SP-GiST - 适用于有天然的聚集因素但是分布不均匀的大数据集
  • BRIN - 适用于有顺序排列的真正的大数据集
  • Hash - 适用于相等操作,而且,通常情况下 B-Tree 索引仍然是你所需要的。

如果你有关于这篇文章的任何问题或反馈,欢迎加入我们的 slack channel


via: https://www.citusdata.com/blog/2017/10/17/tour-of-postgres-index-types/

作者:Craig Kerstiens 译者: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中国 荣誉推出