使用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

一般刚开始学SQL的时候，会这样写

代码如下:

SELECT * FROM table ORDER BY id LIMIT 1000, 10

但在数据达到百万级的时候，这样写会慢死

代码如下:

SELECT * FROM table ORDER BY id LIMIT 1000000, 10

也许耗费几十秒

网上很多优化的方法是这样的

代码如下:

SELECT * FROM table WHERE id >= (SELECT id FROM table LIMIT 1000000, 1) LIMIT 10

是的，速度提升到0.x秒了，看样子还行了

可是，还不是完美的！

以下这句才是完美的！

代码如下:

SELECT * FROM table WHERE id BETWEEN 1000000 AND 1000010

比上面那句，还要再快5至10倍

另外，如果需要查询 id 不是连续的一段，最佳的方法就是先找出 id ，然后用 in 查询

代码如下:

SELECT * FROM table WHERE id IN(10000, 100000, 1000000...)

再分享一点

查询字段一较长字符串的时候，表设计时要为该字段多加一个字段,如，存储网址的字段

查询的时候，不要直接查询字符串，效率低下，应该查看该字串的crc32或md5

如何优化Mysql千万级快速分页

Limit 1,111 数据大了确实有些性能上的问题，而通过各种方法给用上where id >= XX,这样用上索引的id号可能速度上快点儿。By:jack

Mysql limit分页慢的解决办法（Mysql limit 优化，百万至千万条记录实现快速分页）

MySql 性能到底能有多高？用了php半年多，真正如此深入地去思考这个问题还是从前天开始。有过痛苦有过绝望，到现在充满信心！MySql 这个数据库绝对是适合dba级的高手去玩的，一般做一点1万篇新闻的小型系统怎么写都可以，用xx框架可以实现快速开发。可是数据量到了10万，百万至千 万，它的性能还能那么高吗？一点小小的失误，可能造成整个系统的改写，甚至更本系统无法正常运行！好了，不那么多废话了。用事实说话，看例子：

数 据表 collect ( id, title ,info ,vtype) 就这4个字段，其中 title 用定长，info 用text, id 是逐渐，vtype是tinyint，vtype是索引。这是一个基本的新闻系统的简单模型。现在往里面填充数据，填充10万篇新闻。

最后collect 为 10万条记录，数据库表占用硬盘1.6G。OK ,看下面这条sql语句：

select id,title from collect limit 1000,10很快；基本上0.01秒就OK，再看下面的

select id,title from collect limit 90000,10从9万条开始分页，结果？

8-9秒完成，my god 哪出问题了？？？？其实要优化这条数据，网上找得到答案。看下面一条语句:

select id from collect order by id limit 90000,10很快，0.04秒就OK。 为什么？因为用了id主键做索引当然快。网上的改法是：

select id,title from collect where id>=(select id from collect order by id limit 90000,1) limit 10

这就是用了id做索引的结果。可是问题复杂那么一点点，就完了。看下面的语句

select id from collect where vtype=1 order by id limit 90000,10很慢，用了8-9秒！

到 了这里我相信很多人会和我一样，有崩溃感觉！vtype 做了索引了啊？怎么会慢呢？vtype做了索引是不错，你直接 select id from collect where vtype=1 limit 1000,10是很快的，基本上0.05秒，可是提高90倍，从9万开始，那就是0.05*90=4.5秒的速度了。和测试结果8-9秒到了一个数量级。从这里开始有人 提出了分表的思路，这个和dis #cuz 论坛是一样的思路。思路如下：

建一个索引表： t (id,title,vtype) 并设置成定长，然后做分页，分页出结果再到 collect 里面去找info 。 是否可行呢？实验下就知道了。

10万条记录到 t(id,title,vtype) 里，数据表大小20M左右。用

select id from t where vtype=1 order by id limit 90000,10很快了。基本上0.1-0.2秒可以跑完。为什么会这样呢？我猜想是因为collect 数据太多，所以分页要跑很长的路。limit 完全和数据表的大小有关的。其实这样做还是全表扫描，只是因为数据量小，只有10万才快。OK， 来个疯狂的实验，加到100万条，测试性能。

加了10倍的数据，马上t表就到了200多M，而且是定长。还是刚才的查询语句，时间是0.1-0.2秒完成！分表性能没问题？错！因为我们的limit还是9万，所以快。给个大的，90万开始

select id from t where vtype=1 order by id limit 900000,10看看结果，时间是1-2秒！

why ?? 分表了时间还是这么长，非常之郁闷！有人说定长会提高limit的性能，开始我也以为，因为一条记录的长度是固定的，mysql 应该可以算出90万的位置才对啊？ 可是我们高估了mysql 的智能，他不是商务数据库，事实证明定长和非定长对limit影响不大？ 怪不得有人说 discuz到了100万条记录就会很慢，我相信这是真的，这个和数据库设计有关！

难道MySQL 无法突破100万的限制吗？？？到了100万的分页就真的到了极限？？？

答案是： NO !!!! 为什么突破不了100万是因为不会设计mysql造成的。下面介绍非分表法，来个疯狂的测试！一张表搞定100万记录，并且10G 数据库，如何快速分页！

好了，我们的测试又回到 collect表，开始测试结论是： 30万数据，用分表法可行，超过30万他的速度会慢到你无法忍受！当然如果用分表+我这种方法，那是绝对完美的。但是用了我这种方法后，不用分表也可以完美解决！

答 案就是：复合索引！ 有一次设计mysql索引的时候，无意中发现索引名字可以任取，可以选择几个字段进来，这有什么用呢？开始的select id from collect order by id limit 90000,10这么快就是因为走了索引，可是如果加了where 就不走索引了。抱着试试看的想法加了 search(vtype,id) 这样的索引。然后测试

select id from collect where vtype=1 limit 90000,10非常快！0.04秒完成！

再测试: select id ,title from collect where vtype=1 limit 90000,10非常遗憾，8-9秒，没走search索引！

再测试：search(id,vtype)，还是select id 这个语句，也非常遗憾，0.5秒。

综上：如果对于有where 条件，又想走索引用limit的，必须设计一个索引，将where 放第一位，limit用到的主键放第2位，而且只能select 主键！

完美解决了分页问题了。可以快速返回id就有希望优化limit ， 按这样的逻辑，百万级的limit 应该在0.0x秒就可以分完。看来mysql 语句的优化和索引是非常重要的！

好了，回到原题，如何将上面的研究成功快速应用于开发呢？如果用复合查询，我的轻量级框架就没得用了。分页字符串还得自己写，那多麻烦？这里再看一个例子，思路就出来了：

select * from collect where id in (9000,12,50,7000)竟然 0秒就可以查完！

mygod ，mysql 的索引竟然对于in语句同样有效！看来网上说in无法用索引是错误的！

有了这个结论，就可以很简单的应用于轻量级框架了：

代码如下：

复制代码代码如下:

$db=dblink()

$db->pagesize=20

$sql=”select id from collect where vtype=$vtype”

$db->execute($sql)

$strpage=$db->strpage()//将分页字符串保存在临时变量，方便输出

while($rs=$db->fetch_array()){

$strid.=$rs['id'].','

}

$strid=substr($strid,0,strlen($strid)-1)//构造出id字符串

$db->pagesize=0//很关键，在不注销类的情况下，将分页清空，这样只需要用一次数据库连接，不需要再开；

$db->execute(“select id,title,url,sTime,gTime,vtype,tag from collect where id in ($strid)”)

<?php while($rs=$db->fetch_array()): ?>

<?php echo $rs['id']?>

<?php echo $rs['url']?>

<?php echo $rs['sTime']?>

<?php echo $rs['gTime']?>

<?php echo $rs['vtype']?>

” target=”_blank”><?php echo $rs['title']?>

<?php echo $rs['tag']?>

<?php endwhile?>

<?php

echo $strpage

---