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

-- 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;

-- 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;

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.

<?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()

<?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());
        }
    });
});

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 from contacts limit 10;

{
    "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" 
}

{
    "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 
}

{
    "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" 
}

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

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

{
    "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" 
}

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