使用MySQL的递延Join连接实现高效分页 - Aaron

在 Web 应用程序中跨大型数据集分页记录似乎是一个简单的问题，但实际上很难扩展。两种主要的分页策略是偏移/限制和游标。

我们将首先看一下这两种方法，然后稍作修改，可以使偏移/限制非常高效。

偏移/限制分页

偏移/限制方法是迄今为止最常见的方法，它通过跳过一定数量的记录（页）并将结果限制为一页来工作。

例如，假设您的应用程序配置为每页显示 15 条记录。您的 SQL 将如下所示：

这是最常见的，因为它非常简单，易于推理，并且几乎每个框架都支持它。

除了易于实现之外，它还具有页面可直接寻址的优点。例如，如果您想直接导航到第 20 页，您可以这样做，因为该偏移量很容易计算。

但是有一个主要的缺点，它潜伏在数据库处理偏移量的方式中。偏移量告诉数据库放弃从查询中返回的前N个结果。不过数据库仍然要从磁盘上获取这些行。

如果你丢弃的是100条记录，这并不重要，但如果你丢弃的是100,000条记录，数据库就会为了丢弃这些结果而做大量的工作。

在实践中，这意味着第一个页面会快速加载，之后的每一个页面都会变得越来越慢，直到你达到一个点，网络请求可能会直接超时。

基于游标的分页

基于游标的分页弥补了偏移/限制的一些不足，同时引入了一些自己的不足。

基于游标的分页是通过存储一些关于最后呈现给用户的记录的状态，然后根据这个状态来进行下一次查询。

因此，它不是按顺序获取所有的记录并丢弃前N条，而是只获取最后一个位置N之后的记录。

如果按ID排序，SQL可能看起来像这样。

你可能已经看到了其中的好处。因为我们知道上次向用户展示的ID，我们知道下一个页面将以一个更高的ID开始。我们甚至不需要检查ID较低的行，因为我们百分之百肯定地知道那些行不需要被显示。

在上面的例子中，我特别说明了ID可能不是连续的，也就是说，可能有缺失的记录。这使得我们无法计算出哪些记录会出现在某一页面上，你必须跟踪之前那一页面上的最后一条记录是什么。

与偏移/限制分页不同，使用游标分页时，页面不能直接寻址，你只能导航到 "下一页 "或 "上一页"。

不过光标分页的好处是在任何数量的页面上都很迅速。它也很适合无限滚动，在这种情况下，页面首先不需要可以直接寻址。

Laravel文档中有一些关于偏移量和游标之间的权衡的好的背景。

https://laravel.com/docs/pagination cursor -vs-offset-pagination

考虑到所有这些，让我们来看看一个偏移/限制优化，可以使它的性能足以在成千上万的页面上使用。

使用递延join的Offset/Limit

递延连接（deferred join ）是一种技术，它将对要求的列的访问推迟到应用了偏移量和限制之后。

使用这种技术，我们创建一个内部查询，可以用特定的索引进行优化，以获得最大的速度，然后将结果连接到同一个表，以获取完整的行。

它看起来像这样：

这种方法的好处可以根据你的数据集有很大的不同，但是这种方法允许数据库尽可能少地检查数据，以满足用户的意图。

查询中 "昂贵的 "select *部分只在与内部查询相匹配的15条记录上运行。所有数据的Select都被推迟了，因此被称为推迟join。

这种方法不太可能比传统的偏移/限制性能差，尽管它是可能的，所以一定要在你的数据上进行测试!

Laravel实现

我们如何把这一点带到我们最喜欢的网络框架，如Laravel和Rails？

让我们具体看看Laravel，因为我不知道Rails。

感谢Laravel的macroable特性，我们可以扩展Eloquent Query Builder来添加一个新的方法，叫做deferredPaginate。为了保持一致性，我们将模仿常规分页的签名。

我们将尝试做尽可能少的自定义工作，并将大部分工作留给 Laravel。

这是我们要做的：

这应该为我们提供 LaravelLengthAwarePaginator 和延迟连接的所有好处！

一个Github仓库

递延Join和覆盖索引

还没有完成...

使用递延Join的主要好处是减少了数据库必须检索然后丢弃的数据量。我们可以通过帮助数据库获得它需要的数据而更进一步，而无需获取底层行。

这样做的方法称为“覆盖索引covering index”，它是确保快速偏移/限制分页的最终解决方案。

覆盖索引是一个索引，在这个索引中，查询的所有需要的字段都包含在索引本身中。当一个查询的所有部分都能被一个索引 "覆盖 "时，数据库根本不需要读取该行，它可以从索引中获得它需要的一切。

请注意，覆盖索引并不是以任何特殊方式创建的。它只是指一个索引满足了一个查询所需要的一切的情况。一个查询上的覆盖索引很可能不是另一个查询上的覆盖索引。

在接下来的几个例子中，我们将使用这个基本的表，我把它填满了~1000万条记录。

让我们看一个仅select索引列的简单查询。在这种情况下，我们将从email表中进行select contacts。

在这种情况下，数据库根本不需要读取基础行。在MySQL中，我们可以通过运行一个解释并查看额外的列来验证这一点：

extra: using index告诉我们，MySQL能够只使用索引来满足整个查询，而不看基础行。

如果尝试select name from contacts limit 10， 我们将期望MySQL必须到该行去获取数据，因为名字name没有被索引。这正是发生的情况，由下面的解释显示。

extra不再显示 using index，所以我们没有使用覆盖索引。

假设你每页有15条记录，你的用户想查看第1001页，你的内部查询最终会是这样的。

select id from contacts order by id limit 15 OFFSET 150000

explain结果显示：

MySQL能够单看索引来执行这个查询。它不会简单地跳过前15万行，在使用offset是没有办法的，但它不需要读取15万行。(只有游标分页可以让你跳过所有的行）。

即使使用覆盖索引和延迟连接，当你到达后面的页面时，结果也会变慢，尽管与传统的偏移/限制相比，它应该是最小的。使用这些方法，你可以轻易地深入到数千页。

更好的覆盖索引

这里的很多好处取决于拥有良好的覆盖索引，所以让我们稍微讨论一下。一切都取决于您的数据和用户的使用模式，但是您可以采取一些措施来确保查询的最高命中率。

这将主要与 MySQL 对话，因为那是我有经验的地方。其他数据库中的情况可能会有所不同。

大多数开发人员习惯于为单列添加索引，但没有什么能阻止您向多列添加索引。事实上，如果您的目标是为昂贵的分页查询创建覆盖索引，您几乎肯定需要一个多列索引。

当你试图为分页优化一个索引时，一定要把按列排序放在最后。如果你的用户要按update_at排序，这应该是你复合索引中的最后一列。

看看下面这个包括三列的索引。

在MySQL中，复合索引是从左到右访问的，如果一个列缺失，或者在第一个范围条件之后，MySQL会停止使用一个索引。

MySQL 将能够在以下场景中使用该索引：

如果你跳过is_archived，MySQL将无法访问update_at，将不得不诉诸于没有该索引的排序，或者根本不使用该索引，所以要确保你有相应的计划。

主键始终存在

在MySQL的InnoDB中，所有的索引都附加了主键。这意味着（email）的索引实际上是（email，id）的索引，当涉及到覆盖索引和延迟连接时，这是相当重要的。

查询select email from contacts order by id完全被email上的一个索引所覆盖，因为InnoDB将id附加到了该索引上。

使用我们上面的综合例子，你可以看到这有什么好处。

因为复合索引涵盖了is_deleted, is_archived, updated_at, 和（通过InnoDB的功能）id，整个查询可以仅由索引来满足。

降序索引

大多数时候，用户都在寻找 "最新的 "项目，即最近更新或创建的项目，这可以通过按update_at DESC排序来满足。

如果你知道你的用户主要是以降序的方式对他们的结果进行排序，那么特别将你的索引设为降序索引可能是有意义的。

MySQL 8是第一个支持降序索引的MySQL版本。

如果你在explain的Extra部分看到向后索引扫描，你也许可以配置一个更好的索引。

前向索引扫描比后向扫描快~15%，所以你要按照你认为你的用户最常使用的顺序添加索引，并为少数使用情况承担惩罚。

太阳底下无新事

这种使用偏移/限制分页与延迟连接和覆盖索引的方法并不是银d。

仅仅是递迟连接就可以让你的速度得到很好的提升，但是需要花一些额外的心思来设计正确的索引以获得最大的好处。

有一种观点认为，递延连接应该是框架中默认的偏移offset/限制limit方法，而任何时候覆盖索引的出现都只是一种奖励。我还没有在足够多的生产环境中测试过，所以还没有强烈主张这样做。

使用MySQL的递延Join连接实现高效分页 - Aaron

这篇文章是一篇小小的总结，如有疏漏请指出，万分感谢。

一个分销系统当中，基本有几个要点，我以商品二级分销模型为例子。

1.销售人员发展客户，自己和上级能够获得奖励。

2.当客户购买产品的时候，销售人员和上级也能获得奖励。

3.发展用户或者销售人员时，需要绑定他们的关系。

4.销售人员能够看自己下级的人员并查看自己的销售业绩。

That's all.

当时刚接触这个需求的时候，由于本来用户表有一个 pid(parent_id) 的字段，来表示自己的上级销售人员。我当时竟然可怕的是用了 ppid，pppid来表示更上级的人员。。。。。。我的天，居然有人会做出这种事。多么可怕的一段黑历史。

目前在维护一个有上千亿行数据的文件系统，分表分8192张。表结构里面有一个字段，专门维护文件夹层级，里面的值遵循一定的数据格式，长得很像linux的文件系统层级结构（例如:/usr/bin/local/），可以说是一模一样。突然，这不就是树结构吗？然后想到了分销系统的树结构

用户表字段设计：user_id，parent_id，tree，level，money

这里涉及到一些家喻户晓的人，分别是，A，B和Cn

他们有相关从属的关系。

用表来表示：

A进了某养生品公司，注册了会员。

于是A成为了这个系统的第一个用户

| user_id| parent_id | tree| level| money|

| ------------- |:-------------:| -----:|

|A|没有爸爸|/|1|0|

这时A走在大街上，看到B，说：“你听说过安利吗? blablabla”

然后B扫码成为了A的下级会员，A获得一块钱的发展会员奖励，并告诉他，你可以告诉你的新朋好友哦，卖出这么好的产品，可以分成哦。

| user_id| parent_id | tree| level| money|

| ------------- |:-------------:| -----:|

|A|没有爸爸|/|1|1|

|B|A|/A/|2|0|

那么B开始努力推广安利这个产品

给认识的熟人（C1，C2，C3）安利了一发

| user_id| parent_id | tree| level| money|

| ------------- |:-------------:| -----:|

|A|没有爸爸|/|1|4(1+3)|

|B|A|/A/|2|3(0+3)|

|C1|B|/A/B/|3|0|

|C2|B|/A/B/|3|0|

|C3|B|/A/B/|3|0|

这个表设计的优点

1.可以通过 tree 很快的找到 C1 用户的所有上级用户进行相关 *** 作

2.可以通过 like tree *** 作，利用索引，查询 A 用户所有的下级进行相关计数 *** 作，可以提高 A 的推广积极性

3.通过用 tree 属性去关联订单表，给上级返利或者查询自己的推广所得，也是很方便。

其实这个分销的树形结构像极了文件系统的标识符，我也是在做相关mysql存储文件关系的业务当中类比出如此高效快速的查询方法。

这个设计模式的缺点也很明显，一旦关系转移(类似文件夹移动)，就会产生大量的tree字段修改。所幸的是：关系转移可以异步处理，但查询关系就必须同步。鉴于这种 *** 作特点，也就可以采取这种模式。

编译借助诸如Apach、Perl、PHP和Python等工具，构建一个MySQL应用时很容易的。然而确保它们运行快速，则需要一点洞察力。本文就是你需要知道的东西。

MySQL对于成为一个非常快速的数据库服务器有着当之无愧的名声，它也非常容易设置和使用。随着它作为网站后端数据库得声望日增，其效果在去年开始有明显提高。但是很多MySQL用户更多地知道如何创建一个数据库并编写对它的查询。就像成千上万的人通过载闲暇时用Linux做实验来学习Unix那样，很多人通过玩MySQL学习关系数据库。这些MySQL新手的大多数既没有关系数据库理论的背景，又没有时间阅读MySQL手册全文。

因此，我们决定研究某些方法，你可以用针对优化性能来调节MySQL。在读完本文后，你将理解一些帮助你设计你的MySQL数据库和查询的技术，值得你的应用很有效率。我们将假定你熟悉MySQL和SQL基础，但不假定你有这两方面的广博知识。

只存储你需要的信息

这听上去是常识，但人们常常采取“厨房下水道”的方式进行数据库设计。他们认为可能项要得每样东西都要存储并设计数据库保存所有者这些数据。你需要对你的需求现实些，并确定取确实需要什么信息。你常常能随意产生一些数据而不把它存在数据库表中。在这种情况下，从一个应用开发者的角度看也有道理这样做。

例如，在线目录的产品表可能包含各种产品的名称、介绍、尺寸、重量和价格。除了价格，你可能想存储每个项目相关的税和运输成本。但实际上不必这样做。首先税和运输成本可以方便地（由你的应用或MySQL）计算出来。其次，如果税和运输成本改变了，你可能必须编写必要的查询更新每个产品记录中的税和运输的费率。

有时人们认为这太难不能在以后往数据库表中加入字段，所以他们感觉不得不定义尽可能多的列。这是明显的概念错误。在MySQL中，你可以用ALTER

TABLE命令方便地修改表定义以适应你改变的需求。

例如，如果你突然认识到你需要给你的产品表增加一个级别列（可能你想允许用户在你的目录中给产品评级），你可以这样做：

ALTER TABLE products ADD rank INTEGER

这给你的产品表增加了一个整数类型的级别列，你能用ALTER

TABLE做什么的完整介绍参见MySQL手册。

只要求你需要的东西--要清晰

就像说“只存储你需要的东西”那样，这可能看来是常识，但这一点常常被忽视，为什么呢？因为在一个应用开发时，需求经常改变，所以很多查询最终看来是这样：

SELECT * FROM sometable

当你不能肯定你将需要哪一列时，要求所有列明显是最省力的事情，然而随着你的表不断增大和修改，这可能变成一个性能问题。最好是在你的最初开发完成后再花些时间并确定你真正从你的查询中需要什么：

SELECT name, rank, description FROM products

　

这带来了一个相关的观点，即代码维护比性能更重要。大多数变成语言（Perl、Python、PHP、Java等）允许通过字段名和数字编号访问一条查询的结果，这意味着你可以访问命名字段或字段0都可以得到相同的数据。

长期看，最好使用列名而不是其编号位置，为什么？因为一个表中或一条查询中地列的相对位置可以改变。它们在表中可能因为重复使用ALTER

TABLE而改变，它们在查询中将因重写了查询而忘记更新应用逻辑来匹配而改变。

当然，你仍然需要小心改变列名！但如果你使用列名而非标号位置，如列名改变，你可以用grep搜索源代码或使用编辑器的搜索能力查找你需要修改的代码。

规范化你的表结构

如果你以前从未听说过“数据规范化”，不要害怕。规范化可能是一个复杂的专题，你可以从只理解最基本的规范化概念中正真正获益。

理解它的最容易的方法是认为你的表是一个电子报表。如果你想以一个报表跟踪你的CD收藏，你可以如图1种那样进行设计：

图1

album track1track2

track10

----- ------------

-------

Billboard Top Hits - 1984 Loverboy Shout St.

Elmo's Fire

(Billy Ocean) (Tears for Fears) (John

Parr)

这看上去很合理。大多数CD只有10首曲子，对否？不尽然。如果你拥有一张有100首曲子的CD且几张超过20首改怎么办。这意味着用这种方法，在极端的情况下，你将需要一个非常宽的表格（或一个超过100个字段的表）来保存所有的数据。

规范化表结构的目标是使“空单元”的数量最少，在上述CD表的情况下，如果你允许CD可能包含100首曲子，你会有很多这样的空单元。不

---