使用延遲關聯實現高效分頁

在Web應用中對大型資料集進行分頁看起來是一個簡單的問題，但實際上很難擴充套件。兩種主要的分頁策略是 偏移量/限制數量(offset/limit) 和 遊標(cursors)。

我們先來看看這兩種方法，然後再介紹一種稍微改進的方法，可以讓偏移/限制非常高效。

偏移量/限制數量(offset/limit) 方法是目前最常見的，它的原理是跳過一定數量的記錄（頁數），並將結果限制為一頁。

舉個例子，假設你的應用程式設定為每頁顯示15條記錄。你的SQL語句會像這樣：

-- 第一頁
select * from users order by created_at desc limit 15 offset 0;

-- 第二頁
select * from users order by created_at desc limit 15 offset 15;

-- 第三頁
select * from users order by created_at desc limit 15 offset 30;

這是最常見的，因為它非常簡單明瞭，容易理解，而且幾乎每個框架都支援它。

除了容易實現之外，它還有一個很好的優點，那就是頁面可以直接定位。例如，如果你想直接跳轉到第20頁，你可以做到，因為那個偏移量很容易計算出來。

但是，它也有一個很大的問題，就是資料庫處理偏移量的方法不好。Offset讓資料庫把查詢出來的前N個結果捨棄。不過，資料庫仍然需要從磁碟中提取這些結果。

如果你只捨棄100行，這並不重要，但如果你捨棄100,000行，資料庫就要做很多工作來捨棄結果。

在實際中，就是說第一頁會載入得很快，之後的每一頁都會越來越慢，直到web請求可能會超時的點。

基於遊標的分頁彌補了 偏移量/限制數量(offset/limit) 的一些缺陷，同時也引入了一些自己的問題。

基於遊標的分頁的原理是，儲存關於最後一條展示給使用者的記錄的狀態，然後根據這個狀態來構建下一個查詢。

所以，它不是按順序獲取所有記錄並丟棄前N個記錄，而是隻獲取最後一個位置N之後的記錄。

如果按ID排序，SQL語句可能像這樣：

-- 第1頁
select * from users where id > 0 order by id limit 15;

-- 第2頁（假設第一頁的最大ID是24。）
select * from users where id > 24 order by id limit 15;

-- 第3頁（假設第二頁的最大ID是72。）
select * from users where id > 72 order by id limit 15;

你可能已經看到了好處。因為我們知道向使用者顯示的最後一個ID，所以我們知道下一個頁面將以更高的ID開始。我們甚至不必檢查ID較低的行，因為我們100%確定不需要顯示這些行。

在上面的示例中，我特別展示了ID可能不是連續的，即可能缺少記錄。這使得無法計算某個頁面上會顯示哪些記錄，你必須跟蹤頁面上的最後一條記錄。

與 偏移量/限制數量(offset/limit) 分頁不同，在使用遊標分頁時,頁面不能直接定位，你只能導航到“下一頁”或“上一頁”。

不過，遊標分頁有一個優點就是，在任何數量的頁面上都很快速。它也非常適合無限滾動，因為頁面本來就不需要直接定位。

Laravel 的文件有一些關於 偏移量(offset)和遊標(cursors) 之間權衡取捨的內容：[分頁《Laravel 9 中文文件》)

瞭解了這些之後，讓我們看一看 偏移量/限制數量(offset/limit) 最佳化，它可以使其效能足以在數千頁中使用。

延遲關聯（deferred join）是一種技術，它將對請求的列的訪問推遲到 偏移量 / 限制數量(Offset / Limit) 已經應用之後。

使用這種技術，我們可以建立一個內部查詢，該查詢可以使用特定的索引進行最佳化，以獲得最大的速度，然後將結果連線回同一個表以獲取完整的行。

它看起來像這樣：

select * from contacts          -- 你想要展示給使用者的完整資料。
    inner join (                -- "延遲關聯"。
        select id from contacts -- 使用快速索引進行分頁。
            order by id
            limit 15 offset 150000
    ) as tmp using(id)
order by id                     -- 對單個結果頁進行排序。

根據資料集的不同，效果可能會有很大的不同，但是會讓資料庫儘可能少地檢索資料，來達到使用者的查詢結果。

“昂貴”的select *部分只在匹配內部查詢的15行上執行。選擇的所有資料都已延遲，因此稱為延遲關聯。

這種方法的效能不太可能比傳統的 偏移量/限制數量(Offset / Limit) 更差，儘管這是可能的，所以你一定要對你的資料進行測試！

我們如何將這種技術應用到我們喜歡的 Web 框架，比如 Laravel 和 Rails 呢？

讓我們具體看看 Laravel，因為我不瞭解 Rails。

(這個功能已經有一個包：github.com/hammerstonedev/fast-paginate)

感謝 Laravel 的 macroable 特性，我們可以擴充套件 Eloquent 查詢構造器，新增一個新的方法叫做 fastPaginate。我們將模仿常規的 paginate 的命名，以保持一致性：

<?php
//  模仿標準的 `paginate` 命名。
Builder::macro('fastPaginate', function ($perPage = null, $columns = ['*'], $pageName = 'page', $page = null) {
     // 在這裡新增我們的新的分頁邏輯。
});

// 現在你可以在所有的模型查詢上使用它。
Contact::query()->fastPaginate()

我們將盡可能少地做一些自定義工作，並將大部分工作交給Laravel。

下面是我們要做的：

• 將查詢上的select重置為只選擇主鍵
• 將修改後的查詢透過常規的分頁過程執行
• 取得結果中的鍵，並執行第二個查詢來獲取完整的行
• 獲取生成的鍵並執行第二個查詢以獲取完整的行
• 將新記錄與舊分頁器合併

這樣我們就可以得到 Laravel 的 LengthAwarePaginator 和 延遲關聯(deferred joins)的所有優點！

下面是一個基本的示例（注意：這個包更復雜，涵蓋了更多的邊緣情況！）：

<?php
Builder::macro('fastPaginate', function ($perPage = null, $columns = ['*'], $pageName = 'page', $page = null) {
    $model = $this->newModelInstance();
    $key = $model->getKeyName();
    $table = $model->getTable();

    $paginator = $this->clone()
        //我們不需要它們來進行這個查詢，它們將保留在實際獲取記錄的查詢中。
        ->setEagerLoads([])
        //只選擇主鍵，我們將在下面的第二個查詢中獲得完整記錄。
        ->paginate($perPage, ["$table.$key"], $pageName, $page);

    // 直接使用 "raw" 新增我們的值，而不是新增新的繫結。
    // 這基本上是 Laravel 在某些地方使用的 `whereIntegerInRaw`，
    // 但是我們不能保證主鍵是整數，所以我們不能使用它。
    // 我們確定這些值是安全的，因為它們一開始就直接來自資料庫。
    $this->query->wheres[] = [
        'type'   => 'InRaw',
        'column' => "$table.$key",
        // 從 *當前* 頁面上的記錄中獲取鍵值，而不改變它們。
        'values'  => $paginator->getCollection()->map->getRawOriginal($key)->toArray(),
        'boolean' => 'and'
    ];

    // simplePaginate 遞增一來檢視是否有另一頁。我們將減少一來抵消，
    // 因為在我們的情況下這是不必要的。
    $page = $this->simplePaginate($paginator->perPage(), $columns, $pageName, 1);

    // 建立一個新的分頁器，這樣我們就可以放入完整的記錄，
    // 而不是修改後只選擇主鍵的記錄。
    return new LengthAwarePaginator(
        $page->items(),
        $paginator->total(),
        $paginator->perPage(),
        $paginator->currentPage(),
        $paginator->getOptions()
    );
});

Relation::macro('fastPaginate', 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->fastPaginate($perPage, $columns, $pageName, $page), function ($paginator) {
        if ($this instanceof BelongsToMany) {
            $this->hydratePivotRelation($paginator->items());
        }
    });
});

你會注意到，我們實際上沒有在這裡使用 join，而是使用了 where in。這主要是因為 Laravel 的分頁器已經執行了查詢，所以我們可以直接使用返回的鍵。我們不需要再次執行查詢，所以我們不需要。（我們還必須給 Relation 類新增一個 macro，來模仿 Laravel 在底層的工作方式。在這裡閱讀更多內容。）

上面的 Laravel 程式碼可以處理整數和字串主鍵，但它不能處理複合主鍵。這應該是可以的，但我還沒有做到。我在幾個查詢上測試過這個，但肯定有一些邊緣情況我沒有考慮到。請在你們的應用中測試一下，並報告任何問題！

不過我們還沒有完全完成……

使用延遲關聯的主要好處是減少資料庫必須檢索並丟棄的資料量。我們可以透過幫助資料庫獲取需要的資料，而不用獲取底層行，從而進一步實現這一點。

這樣做的方法稱為“覆蓋索引”，它是確保快速 offset / limit 分頁的最終解決方案。

覆蓋索引是一個索引，其中查詢的所有必需欄位都包含在索引本身中。當一個查詢的所有部分都可以被一個索引“覆蓋”時，資料庫不需要讀取行，它可以從索引中獲取所需的一切。

請注意，覆蓋索引不是以任何特殊方式建立的。它僅指單個索引滿足查詢所需的所有條件的情況。一個查詢上的覆蓋索引可能不是另一個查詢的覆蓋索引。

在接下來的幾個示例中，我們將使用這個基本表，我用大約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`)
)

讓我們來看一個只選擇索引列的簡單查詢。在這種情況下，我們將從 contacts 表中選擇 email。

select email from contacts limit 10;

在這種情況下，資料庫根本不需要讀取底層行。在 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": "Using index" 
}

extra: using index 告訴我們，MySQL 能夠只使用索引來滿足整個查詢，而不需要檢視底層行

如果我們嘗試 select name from contacts limit 10，我們預計 MySQL 必須去行中獲取資料，因為 name 沒有索引。這正是發生的情況，如下explain所示：

{
    "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條記錄，你的使用者想要檢視第10001頁，你的內部查詢可能會像這樣：

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能夠僅透過檢視索引來執行這個查詢。它不會簡單地跳過前150,000行，用offset無法避免的，但它不用讀取150,000行。（只有遊標分頁才能讓你完全跳過行。）

即使使用覆蓋索引和延遲關聯，當你到達後面的頁面時，結果也會變慢，儘管與傳統的offset / limit相比應該很小。你可以很輕鬆地使用這些方法深入到數千頁。

覆蓋索引的好處很大程度上取決於你有好的覆蓋索引，所以讓我們談談這個問題。一切都取決於你的資料和使用者的使用模式，但你可以做一些事情來確保查詢的最高命中率。

這裡主要是針對MySQL，因為這是我有經驗的地方。在其他資料庫中，情況可能會不同。

大多數開發者習慣於給單列新增索引，但是你也可以給多列新增索引。事實上，如果你想要為一個耗費資源的分頁查詢建立一個覆蓋索引，你幾乎肯定需要一個多列索引。

當你試圖最佳化一個分頁查詢的索引時，一定要把order by列放在最後。如果你的使用者要按照updated_at排序，那麼這應該是你的複合索引中的最後一列。

檢視以下包含三列的索引：

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

或在Laravel完成：

$table->index(['is_deleted', 'is_archived', 'updated_at'], 'composite');

在MySQL中，複合索引是從左到右訪問的，如果某一列缺失，或者在第一個範圍條件之後，MySQL就會停止使用索引。

MySQL可以在以下情況下使用這個索引：

• 你查詢is_deleted列
• 你查詢is_deleted和is_archived列
• 你查詢is_deleted、is_archived和updated_at列
• 你查詢is_deleted和is_archived列，並按照updated_at排序

如果你跳過了 is_archived, MySQL將無法訪問 updated_at, 並且不得不在沒有這個索引的情況下進行排序，或者乾脆不使用這個索引，所以請務必做好計劃。

在MySQL的InnoDB引擎中，所有的索引都會附加主鍵。這意味著一個在(email)上的索引實際上是一個在(email, id)上的索引，這對於覆蓋索引和延遲關聯非常重要。

查詢select email from contacts order by id完全可以由一個在email上的單一索引覆蓋，因為InnoDB會把id附加到這個索引上！

使用我們之前的複合示例，你可以看到這有什麼好處：

select
  id                   -- 隱式地在索引中
from
  contacts
where
  is_deleted = 0       -- 顯式地在索引中
  and is_archived = 0  -- 顯式地在索引中
order by
  updated_at desc      -- 顯式地在索引中

因為複合索引覆蓋了 is_deleted, is_archived, updated_at, 和 (由於InnoDB的功能) id, 這整個查詢可以僅透過索引完成。

大多數情況下，使用者都是想要查詢“最新”的資料，也就是最近更新或建立的資料，這可以透過updated_at DESC 排序來實現。

如果你知道你的使用者將主要按降序對其結果進行排序，那麼有必要專門建立一個降序索引。

MySQL 8 是第一個支援降序索引的MySQL版本。

如果你在 explain 的 Extra 部分看到了 Backward index scan，你可以配置一個更好的索引。

{
    "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 分頁和延遲關聯 (deferred join) 以及覆蓋索引的方法非完美的解決方案。

單純的延遲關聯可能就能讓你的速度提升不少，但需要更多的思考來設計正確的索引以獲得最大的效益。

有人可能會認為，延遲關聯應該是框架中預設的 offset / limit 方法，而任何時候覆蓋索引命中都只是一個額外的獎勵。我還沒有在足夠多的生產環境中測試過，所以還不能強烈地主張這一點。

最後，在你向我鼓掌和讚揚之前，請理解這並不是一個原創的概念！這個基本思想在一本叫做“高效能MySQL, 第三版”（現在也有第四版） 的書中概述了。

我之前讀過這本書，然後有點忘記了這個小技巧。幾天前，我在幫一個朋友最佳化他們的 Laravel + MySQL 應用時，我們遇到了這個問題，第一頁工作得很好，而第 3231 頁永遠也載入不出來。

我想起了我讀過的某些模糊記憶，於是我回到書中去查詢並想出瞭如何針對他們的資料集在 Laravel 中實現它。

我喜歡閱讀實體技術書籍就是因為這個原因。我可能只是將一些可能有用的部分標記為有趣，不知道什麼時候會真正需要，但是我大致知道某些解決方案存在。然後當我需要使用它時，我可以回頭去查詢！

我強烈推薦那本 MySQL 書籍。

原文連結：aaronfrancis.com/2022/efficient-pa...

本作品採用《CC 協議》，轉載必須註明作者和本文連結