使用 jOOQ 的 Seek 方法实现更快的 SQL 分页 已翻译 100%

oschina 投递于 2013/10/29 09:06 (共 7 段, 翻译完成于 10-29)
阅读 4895
收藏 89
9
加载中

Featured article by oschina reproduced with permission by Data Geekery GmbH.
English content copyright © 2013 by Data Geekery GmbH.

上周,我发表了一篇《为什么保留对SQL的控制非常重要》的博客,因为好的SQL有助于降低(数据库)操作的开销。在如今很多方面当中这都是条真理,我们将继续看看用另一种方式来编写编写好的高效SQL:使用“Seek 方法”。

很慢的OFFSET

为了理解Seek方法,首先让我们了解它能解决什么问题:SQL中的OFFSET从句 很慢。而且慢的原因很简单:为了从一个结果集中取出一个偏移量较大的记录,它之前所有数据都要被跳过并计数。不包含offset从句的查询是非常快的(使用MySQL 语法):

SELECT first_name, last_name, score
FROM players
WHERE game_id = 42
ORDER BY score DESC
LIMIT 10;

跳到第10000条记录将会慢的多:

SELECT first_name, last_name, score
FROM players
WHERE game_id = 42
ORDER BY score DESC
LIMIT 10
OFFSET 100000;

尽管(game_id,score)元组已经被索引,为了计数我们已经跳过多少记录,我们实际上不得不遍历整个索引,虽然这个问题可以通过一招——将其填入到派生表中来解决,此外还有一个更快的解决分页的方法:Seek方法。

bigtiger02
bigtiger02
翻译于 2013/10/29 12:33
4

Seek方法

谁最先发明了Seek方法(有人还称为“按键翻页”)不是很清晰,不过一个非常杰出的倡导者是Markus Winand。他在博客日志(还有他编写的书籍)里说明了Seek方法:

http://use-the-index-luke.com/sql/partial-results/fetch-next-page

Seek方法本来就没有跳过OFFSET之前的记录,它跳过的是所有以前获取到的最后一条记录之前的记录。考虑一下谷歌的翻页。从实用角度看的话,你几乎不可能准确地跳过100'000行记录。大多数情况下,你可能需要跳到下一页,然后再下一页,也就是最后一条记录之后的那一页,或者搜索以前获取到的结果。看看下面10个顶级玩家(由名字生成器自动生成的假名字) :

first_name | last_name | score
------------------------------
Mary       | Paige     |  1098
Tracey     | Howard    |  1087
Jasmine    | Butler    |  1053
Zoe        | Piper     |  1002
Leonard    | Peters    |   983
Jonathan   | Hart      |   978
Adam       | Morrison  |   976
Amanda     | Gibson    |   967
Alison     | Wright    |   958
Jack       | Harris    |   949

上面是按分数进行排名的前10个玩家。这可以通过仅仅使用LIMIT 10非常快速的获取到。现在,当要调到下一页的时候,你要么仅仅使用OFFSET子句,要么跳过所有分数高于949的用户:

SELECT first_name, last_name, score
FROM players
WHERE game_id = 42
-- 让我们调用下面"seek断言"
AND score < 949
ORDER BY score DESC
LIMIT 10;

然后,你就会看到下一页的玩家如下:

first_name | last_name | score
------------------------------
William    | Fraser    |   947
Claire     | King      |   945
Jessica    | McDonald  |   932
...        | ...       |   ...
几点人
几点人
翻译于 2013/10/29 12:29
1

注意:前面的查询假设players表里的score是唯一的,当然,这是不可能的。如果William Fraser也是949分,同第一页的最后一个玩家Jack Harris一样,那么他将会"在翻页的时丢失"。因此创建一个不混淆的ORDER BY子句是很重要的,而且可以给“seek断言“添加另外一个唯一字段:

SELECT player_id, first_name, last_name, score
FROM players
WHERE game_id = 42
-- assuming 15 is Jack Harris's player_id
AND (score, player_id) < (949, 15)
ORDER BY score DESC, player_id DESC
LIMIT 10;

现在,"seek断言“取决于ORDER BY子句。下面有多种可能的配置,可供选择的配置如下:

-- "一致型" ASC和DESC对应的是>和<
AND (score, player_id) > (949, 15)
ORDER BY score ASC, player_id ASC

-- "混合型" ASC和DESC使事情稍有些复杂
AND ((score < 949)
  OR (score = 949 AND player_id > 15))
ORDER BY score DESC, player_id ASC

-- 上面处理可能进一步增加了处理的性能
AND (score <= 949)
AND ((score < 949)
  OR (score = 949 AND player_id > 15))
ORDER BY score DESC, player_id ASC
如果ORDER BY子句中的某个或者某些字段为空,那么可能要用上NULLS FIRST和NULLS LAST选项,这将进一步使"seek断言“复杂化。
几点人
几点人
翻译于 2013/10/29 12:41
2

这比OFFSET好在哪儿?

Seek方法可以避免耗时的“跳过并计数”操作,代之以在符合“seek断言”的索引上进行简单的范围扫描。因为不管怎样你都要在符合“seek断言”的字段上进行ORDER BY,所以你可能已经选择对这些字段进行了合适的索引。

然而,Seek方法并不能提升第页数的查询效率,获取高页数的数据则有显著的速度提升,这在下面这个良好的基准测试中得到了证明:

经过Markus Winand的允许,在use-the-index-luke.com上得到了再现

更多关于该话题的有趣反馈可以在reddit.com thread上找到,在那上面甚至Tom Kyte自己都添加了许多评论。

lwei
lwei
翻译于 2013/10/29 13:23
2

Seek方法的一个副作用

Seek方法的一个副作用就是,事实上分页将会更加“稳定”。当你打算展示第2页并且同时有一个新的玩家加入到了第一页中,或者某个玩家从第1页中被完全删除,你的第2页上都将展示同样的信息。也就是说,使用Seek方法时,并不保证第2页上的第一条记录是从11开始的。

这可能正是你期望的结果,也许不是。不过,在第10’000页上,这可能并不重要。

lwei
lwei
翻译于 2013/10/29 11:17
1

jOOQ 3.3支持Seek方法

即将到来的jOOQ 3.3(2013年即将到来)将会包括在SQL DSL API级别上对Seek方法的支持。另外jOOQ存在一些限制..和一些不支持,一个“seek predicate”可以通过syntheticSEEKclause来指定(与jOOQ’s syntheticDIVIDE BYclause类似):

DSL.using(configuration)
   .select(PLAYERS.PLAYER_ID,
           PLAYERS.FIRST_NAME,
           PLAYERS.LAST_NAME,
           PLAYERS.SCORE)
   .from(PLAYERS)
   .where(PLAYERS.GAME_ID.eq(42))
   .orderBy(PLAYERS.SCORE.desc(),
            PLAYERS.PLAYER_ID.asc())
   .seek(949, 15) // (!)
   .limit(10)
   .fetch();

而不是简单的“seek句式”查询语句,把上一次的查询结果传递给上一条记录,jOOQ将会看到之前的所有记录包括那些被忽略掉的记录,最后通过ORDER BY 字段进行排序。

与实际呈现出来的SQL,这似乎更具有可读性,因为“seek句式”更靠近ORDER BY 字段。另外,JQQQ使用的是安全的行值类型,在这里可以帮助你在SEEK字段找到正确的阶次/参数数量和数据类型。在上面的示例中,调用下面的方法,将不会经过java编译:

// Not enough arguments in seek()
   .orderBy(PLAYERS.SCORE.desc(),
            PLAYERS.PLAYER_ID.asc())
   .seek(949)

// Wrong argument types in seek()
   .orderBy(PLAYERS.SCORE.desc(),
            PLAYERS.PLAYER_ID.asc())
   .seek(949, "abc")
NCThinker
NCThinker
翻译于 2013/10/29 13:26
3

使用Seek方法开始工作

随着本地API对aSEEKclause的支持,你可以控制你的SQL,同时可以很容易实现高性能的SQL语句查询。早期的开发者已经在使用GitHub上的jOOQ3.3。0开源版本了。

即使你不使用jOOQ,尽量试试Seek方法。你也许在之后就会拥有一个效率更高的应用程序了。

NCThinker
NCThinker
翻译于 2013/10/29 10:06
3
本文中的所有译文仅用于学习和交流目的,转载请务必注明文章译者、出处、和本文链接。
我们的翻译工作遵照 CC 协议,如果我们的工作有侵犯到您的权益,请及时联系我们。
加载中

评论(12)

喵星人123
喵星人123
还有一个问题 ORDER BY后面跟两个字段的话 只有一个字段会起到排序的作用
喵星人123
喵星人123
问题是最后一条记录 怎么保存下来
喵星人123
喵星人123

引用来自“非良”的评论

引用来自“颜磊”的评论

直接limit分页不行吗

这是为大数据准备的

直接用limit在大数据下是不是效率比较低
blackshow
blackshow
player_id 到底是啥啊?主键自增列?
googlespot
googlespot
如果有排序字段近似分布函数也可以做类似优化也是可以的,而且可以随机读写偶,不过要看该排序字段是高基数的还是低基数的了,如果是低基数的那本文也不成立。
wx---每日佳选
wx---每日佳选

引用来自“颜磊”的评论

直接limit分页不行吗

这是为大数据准备的
Pader
Pader
这个太局限了,不能跳转到指定的页数。
磊神Ray
磊神Ray
直接limit分页不行吗
0x0001
0x0001
这种方式就是我现在用的,因为我的工作中几乎没有跳到第几页(手机程序)
小白小霸王
小白小霸王
关键字 “yahoo 分页”
返回顶部
顶部