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


在 Web 应用程序中跨大型数据集分页记录似乎是一个简单的问题,但实际上很难扩展。两种主要的分页策略是偏移/限制和游标。
我们将首先看一下这两种方法,然后稍作修改,可以使偏移/限制非常高效。
偏移/限制分页
偏移/限制方法是迄今为止最常见的方法,它通过跳过一定数量的记录(页)并将结果限制为一页来工作。
例如,假设您的应用程序配置为每页显示 15 条记录。您的 SQL 将如下所示:

-- Page 1
select * from users order by created_at desc limit 15 offset 0;
 
-- Page 2
select * from users order by created_at desc limit 15 offset 15;
 
-- Page 3
select * from users order by created_at desc limit 15 offset 30;

这是最常见的,因为它非常简单,易于推理,并且几乎每个框架都支持它。
除了易于实现之外,它还具有页面可直接寻址的优点。例如,如果您想直接导航到第 20 页,您可以这样做,因为该偏移量很容易计算。
但是有一个主要的缺点,它潜伏在数据库处理偏移量的方式中。偏移量告诉数据库放弃从查询中返回的前N个结果。不过数据库仍然要从磁盘上获取这些行。
如果你丢弃的是100条记录,这并不重要,但如果你丢弃的是100,000条记录,数据库就会为了丢弃这些结果而做大量的工作。

在实践中,这意味着第一个页面会快速加载,之后的每一个页面都会变得越来越慢,直到你达到一个点,网络请求可能会直接超时。
 
基于游标的分页
基于游标的分页弥补了偏移/限制的一些不足,同时引入了一些自己的不足。

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

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

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

-- Page 1
select * from users where id > 0 order by id limit 15;
 
-- Page 2 (Assuming the max ID from page one was 24.)
select * from users where id > 24 order by id limit 15;
 
-- Page 3 (Assuming the max ID from page two was 72.)
select * from users where id > 72 order by id limit 15;

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

在上面的例子中,我特别说明了ID可能不是连续的,也就是说,可能有缺失的记录。这使得我们无法计算出哪些记录会出现在某一页面上,你必须跟踪之前那一页面上的最后一条记录是什么。
与偏移/限制分页不同,使用游标分页时,页面不能直接寻址,你只能导航到 "下一页 "或 "上一页"。

不过光标分页的好处是在任何数量的页面上都很迅速。它也很适合无限滚动,在这种情况下,页面首先不需要可以直接寻址。
Laravel文档中有一些关于偏移量和游标之间的权衡的好的背景。
https://laravel.com/docs/paginationcursor-vs-offset-pagination

考虑到所有这些,让我们来看看一个偏移/限制优化,可以使它的性能足以在成千上万的页面上使用。
 
使用递延join的Offset/Limit
递延连接(deferred join )是一种技术,它将对要求的列的访问推迟到应用了偏移量和限制之后。

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

它看起来像这样:

select * from contacts          -- The full data that you want to show your users.
    inner join (                -- The "deferred join."
        select id from contacts -- The pagination using a fast index.
            order by id
            limit 15 offset 150000
    ) as tmp using(id)
order by id                     -- Order the single resulting page as well.


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

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

这种方法不太可能比传统的偏移/限制性能差,尽管它是可能的,所以一定要在你的数据上进行测试!
 
Laravel实现
我们如何把这一点带到我们最喜欢的网络框架,如Laravel和Rails?

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

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

<?php
// Mimic the standard `paginate` signature.
Builder::macro('deferredPaginate', function ($perPage = null, $columns = ['*'], $pageName = 'page', $page = null) {
   
// Add our new pagination logic here.
});
 
// Now you can use it on all your model queries.
Contact::query()->deferredPaginate()

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

  • 重置select查询为仅select主键
  • 通过常规分页过程运行修改后的查询
  • 获取结果主键并运行第二个查询以获取完整行
  • 将新记录与旧分页器结合起来

这应该为我们提供 LaravelLengthAwarePaginator 和延迟连接的所有好处!
<?php
Builder::macro('deferredPaginate', function ($perPage = null, $columns = ['*'], $pageName = 'page', $page = null) {
    $model = $this->newModelInstance();
    $key = $model->getKeyName();
    $table = $model->getTable();
 
    $paginator = $this->clone()
        // We don't need them for this query, they'll remain
       
// on the query that actually gets the records.
        ->setEagerLoads([])
       
// Only select the primary key, we'll get the full
       
// records in a second query below.
        ->paginate($perPage, [
"$table.$key"], $pageName, $page);
 
   
// Add our values in directly using "raw," instead of adding new bindings.
   
// This is basically the `whereIntegerInRaw` that Laravel uses in some
   
// places, but we're not guaranteed the primary keys are integers, so
   
// we can't use that. We're sure that these values are safe because
   
// they came directly from the database in the first place.
    $this->query->wheres[] = [
        'type'   => 'InRaw',
        'column' =>
"$table.$key",
       
// Get the key values from the records on the *current* page, without mutating them.
        'values'  => $paginator->getCollection()->map->getRawOriginal($key)->toArray(),
        'boolean' => 'and'
    ];
 
   
// simplePaginate increments by one to see if there's another page. We'll
   
// decrement to counteract that since it's unnecessary in our situation.
    $page = $this->simplePaginate($paginator->perPage() - 1, $columns, null, 1);
 
   
// Create a new paginator so that we can put our full records in,
   
// not the ones that we modified to select only the primary key.
    return new LengthAwarePaginator(
        $page->items(),
        $paginator->total(),
        $paginator->perPage(),
        $paginator->currentPage(),
        $paginator->getOptions()
    );
});
 
Relation::macro('deferredPaginate', function ($perPage = null, $columns = ['*'], $pageName = 'page', $page = null) {
    if ($this instanceof HasManyThrough || $this instanceof BelongsToMany) {
        $this->query->addSelect($this->shouldSelect($columns));
    }
 
    return tap($this->query->deferredPaginate($perPage, $columns, $pageName, $page), function ($paginator) {
        if ($this instanceof BelongsToMany) {
            $this->hydratePivotRelation($paginator->items());
        }
    });
});

一个Github仓库
 
递延Join和覆盖索引
还没有完成...
使用递延Join的主要好处是减少了数据库必须检索然后丢弃的数据量。我们可以通过帮助数据库获得它需要的数据而更进一步,而无需获取底层行。
这样做的方法称为“覆盖索引covering index”,它是确保快速偏移/限制分页的最终解决方案。
覆盖索引是一个索引,在这个索引中,查询的所有需要的字段都包含在索引本身中。当一个查询的所有部分都能被一个索引 "覆盖 "时,数据库根本不需要读取该行,它可以从索引中获得它需要的一切。

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

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

CREATE TABLE `contacts` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(255) DEFAULT NULL,
  `email` varchar(255) NOT NULL,
  `created_at` timestamp NULL,
  `updated_at` timestamp NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `users_email_unique` (`email`)
)

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

select email from contacts limit 10;

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

{
    "id": 1,
   
"select_type": "SIMPLE",
   
"table": "contacts",
   
"partitions": null,
   
"type": "index",
   
"possible_keys": null,
   
"key": "users_email_unique",
   
"key_len": "1022",
   
"ref": null,
   
"rows": 10690173,
   
"filtered": 100.00,
   
"Extra": "Using index" 
}

extra: using index告诉我们,MySQL能够只使用索引来满足整个查询,而不看基础行。
如果尝试select name from contacts limit 10, 我们将期望MySQL必须到该行去获取数据,因为名字name没有被索引。这正是发生的情况,由下面的解释显示。

{
    "id": 1,
   
"select_type": "SIMPLE",
   
"table": "contacts",
   
"partitions": null,
   
"type": "ALL",
   
"possible_keys": null,
   
"key": null,
   
"key_len": null,
   
"ref": null,
   
"rows": 10690173,
   
"filtered": 100.00,
   
"Extra": null 
}

 extra不再显示 using index,所以我们没有使用覆盖索引。
假设你每页有15条记录,你的用户想查看第1001页,你的内部查询最终会是这样的。
select id from contacts order by id limit 15 OFFSET 150000
explain结果显示:

{
    "id": 1,
   
"select_type": "SIMPLE",
   
"table": "contacts",
   
"partitions": null,
   
"type": "index",
   
"possible_keys": null,
   
"key": "PRIMARY",
   
"key_len": "8",
   
"ref": null,
   
"rows": 150015,
   
"filtered": 100.00,
   
"Extra": "Using index" 
}

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

即使使用覆盖索引和延迟连接,当你到达后面的页面时,结果也会变慢,尽管与传统的偏移/限制相比,它应该是最小的。使用这些方法,你可以轻易地深入到数千页。
 
更好的覆盖索引
这里的很多好处取决于拥有良好的覆盖索引,所以让我们稍微讨论一下。一切都取决于您的数据和用户的使用模式,但是您可以采取一些措施来确保查询的最高命中率。
这将主要与 MySQL 对话,因为那是我有经验的地方。其他数据库中的情况可能会有所不同。

  • 多列索引

大多数开发人员习惯于为单列添加索引,但没有什么能阻止您向多列添加索引。事实上,如果您的目标是为昂贵的分页查询创建覆盖索引,您几乎肯定需要一个多列索引。
当你试图为分页优化一个索引时,一定要把按列排序放在最后。如果你的用户要按update_at排序,这应该是你复合索引中的最后一列。

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

alter table contacts add index `composite`(`is_deleted`, `is_archived`, `updated_at`);

在MySQL中,复合索引是从左到右访问的,如果一个列缺失,或者在第一个范围条件之后,MySQL会停止使用一个索引。
MySQL 将能够在以下场景中使用该索引:
  • 您的查询对象是is_deleted
  • 您查询的是is_deleted和is_archived。
  • 您可以查询is_deleted和is_archived以及update_at。
  • 你查询is_deleted和is_archived,并按更新日期排序。

如果你跳过is_archived,MySQL将无法访问update_at,将不得不诉诸于没有该索引的排序,或者根本不使用该索引,所以要确保你有相应的计划。
 
主键始终存在
在MySQL的InnoDB中,所有的索引都附加了主键。这意味着(email)的索引实际上是(email,id)的索引,当涉及到覆盖索引和延迟连接时,这是相当重要的。

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

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

select
  id                   -- implicitly in the index
from
  contacts
where
  is_deleted = 0       -- explicitly in the index
  and is_archived = 0  -- explicitly in the index
order by
  updated_at desc      -- explicitly in the index

因为复合索引涵盖了is_deleted, is_archived, updated_at, 和(通过InnoDB的功能)id,整个查询可以仅由索引来满足。
 
 
降序索引
大多数时候,用户都在寻找 "最新的 "项目,即最近更新或创建的项目,这可以通过按update_at DESC排序来满足。

如果你知道你的用户主要是以降序的方式对他们的结果进行排序,那么特别将你的索引设为降序索引可能是有意义的。
MySQL 8是第一个支持降序索引的MySQL版本。
如果你在explain的Extra部分看到向后索引扫描,你也许可以配置一个更好的索引。

{
    "id": 1,
   
"select_type": "SIMPLE",
   
"table": "contacts",
   
"partitions": null,
   
"type": "index",
   
"possible_keys": null,
   
"key": "users_email_unique",
   
"key_len": "1022",
   
"ref": null,
   
"rows": 10690173,
   
"filtered": 100.00,
   
"Extra": "Backward index scan; Using index" 
}

要声明一个索引是降序的, 你可以在你的索引语句中加入DESC. 在Laravel中,你需要使用DB::raw()方法来做这件事。

$table->index(['is_deleted', 'is_archived', DB::raw('`updated_at` DESC')], 'composite');

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

 
太阳底下无新事
这种使用偏移/限制分页与延迟连接和覆盖索引的方法并不是银弹。

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

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

最后,在你用掌声和赞美声向我致意之前,请理解这不是一个原创的概念!基本的想法在一本书中就有概述。基本的想法在一本叫做 "高性能MySQL,第三版 "的书中有概述。(现在也有第四版)。