使用MySQL的遞延Join連線實現高效分頁 - Aaron

banq 發表於 2022-01-25
MySQL

在 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,第三版 "的書中有概述。(現在也有第四版)。