// 典型分页实现
public function list(int $page = 1) 
{
    $perPage = 20;
    $offset = ($page - 1) * $perPage;
    
    return DB::table('orders')
        ->orderBy('id', 'DESC')
        ->offset($offset)
        ->limit($perPage)
        ->get();
}

EXPLAIN SELECT * FROM orders ORDER BY id DESC LIMIT 1000000, 10;

/*
+----+-------------+--------+------------+-------+---------------+---------+---------+------+--------+----------+-------+
| id | select_type | table  | partitions | type  | possible_keys | key     | key_len | ref  | rows   | filtered | Extra |
+----+-------------+--------+------------+-------+---------------+---------+---------+------+--------+----------+-------+
|  1 | SIMPLE      | orders | NULL       | index | NULL          | PRIMARY | 8       | NULL | 1000010|   100.00 | NULL  |
+----+-------------+--------+------------+-------+---------------+---------+---------+------+--------+----------+-------+
*/

Q(cursor) = { x ∈ S | x.id < cursor } \ \ \cap \ \ \text{TOP}_{N}

class PaginationService
{
    public function cursorPaginate(
        string $cursor = null, 
        int $limit = 20
    ): array 
    {
        $query = Order::query()->orderBy('id', 'desc');
        
        // 定位游标锚点
        if ($cursor && $decoded = $this->decodeCursor($cursor)) {
            $query->where('id', '<', $decoded);
        }
        
        $data = $query->limit($limit + 1)->get(); // 多取1条判断终止
        
        $hasNext = $data->count() > $limit;
        $items = $hasNext ? $data->slice(0, $limit) : $data;
        
        // 生成下一游标
        $nextCursor = null;
        if ($hasNext && $last = $items->last()) {
            $nextCursor = $this->encodeCursor($last->id);
        }
        
        return [
            'data' => $items,
            'next_cursor' => $nextCursor,
            'has_more' => $hasNext,
        ];
    }
    
    private function encodeCursor($id): string
    {
        return base64_encode(json_encode(['v' => $id]));
    }
    
    private function decodeCursor($cursor): ?int
    {
        try {
            $data = json_decode(base64_decode($cursor), true);
            return $data['v'] ?? null;
        } catch (\Throwable) {
            return null;
        }
    }
}

public function timeBasedCursorPaginate(
    string $cursor = null,
    int $limit = 20
): array {
    // 解析复合游标 (格式：时间戳_ID)
    [$lastTime, $lastId] = $cursor 
        ? explode('_', base64_decode($cursor)) 
        : [time() + 1, 0]; // 默认取未来时间

    $query = Order::query()
        ->orderBy('created_at', 'desc')
        ->orderBy('id', 'desc');
    
    // 双条件精确定位
    $query->where(function (Builder $q) use ($lastTime, $lastId) {
        $q->where('created_at', '<', $lastTime)
          ->orWhere(function (Builder $inner) use ($lastTime, $lastId) {
              $inner->where('created_at', $lastTime)
                    ->where('id', '<', $lastId);
          });
    });
    
    // ... (获取数据逻辑同基础版)
    
    // 生成新游标
    if ($hasNext && $lastItem = $items->last()) {
        $nextCursor = base64_encode(
            $lastItem->created_at->timestamp . '_' . $lastItem->id
        );
    }
    
    return $nextCursor;
}

use Hyperf\Paginator\CursorPaginator;
use Hyperf\Utils\ApplicationContext;

class OrderController
{
    #[GetMapping("/orders")]
    public function index(Request $request)
    {
        $cursor = $request->input('cursor');
        $perPage = $request->input('per_page', 20);
        
        $paginator = Order::query()
            ->orderBy('created_at', 'desc')
            ->cursorPaginate(
                perPage: $perPage,
                cursor: $cursor ? Cursor::fromEncoded($cursor) : null
            );
        
        return [
            'data' => $paginator->items(),
            'next_cursor' => $paginator->nextCursor()?->encode(),
        ];
    }
}

/* 高效游标分页索引配置 */
ALTER TABLE orders 
    ADD INDEX idx_cursor_optimize (created_at DESC, id DESC);

/* 反例 - 无法优化游标扫描 */
ADD INDEX idx_fail (status, created_at); /* 非前导列 */

class CachedCursorPaginator
{
    public function __construct(
        private CacheInterface $cache,
        private int $ttl = 60
    ) {}
    
    public function paginate(string $cacheKey, Closure $query)
    {
        if ($data = $this->cache->get($cacheKey)) {
            return $data;
        }
        
        $result = $query();
        $this->cache->set($cacheKey, $result, $this->ttl);
        
        return $result;
    }
}

// 使用示例
$paginator = new CachedCursorPaginator(ApplicationContext::getContainer()->get(CacheInterface::class));
$result = $paginator->paginate(
    "user:{$userId}:orders:" . ($cursor ?: 'first'),
    fn() => $this->getOrderService()->cursorPaginate($cursor)
);

// 前端补偿流程图
sequenceDiagram
    participant C as Client
    participant S as Server
    
    C->>S: 获取第一页 (cursor=null)
    S-->>C: 数据 + new_items_count=0
    note right of C: 用户浏览页面期间...
    C->>S: 注册WebSocket监听(user_id)
    S-->>C: 推送新订单通知 (count=3)
    C->>S: 获取下一页 (cursor=last_id)
    S->>S: 检测到新消息计数 >0
    S-->>C: 返回补偿数据结构
    note right of C: 客户端自动合并:<br/>1. 插入新消息到顶部<br/>2. 正常展示当前页

class DataSnapshotService
{
    public function createSnapshot(string $exportId): void
    {
        // 创建临时表
        DB::statement("CREATE TEMPORARY TABLE snapshot_{$exportId} ENGINE=MEMORY 
            AS SELECT * FROM orders WHERE created_at < NOW()");
        
        // 记录快照元数据
        Redis::setex("snapshot:meta:{$exportId}", 3600, json_encode([
            'created_at' => time(),
            'total_rows' => DB::table("snapshot_{$exportId}")->count()
        ]));
    }
    
    public function paginateSnapshot(string $exportId, ?string $cursor, int $limit)
    {
        // 从临时表游标分页
        $table = "snapshot_{$exportId}";
        $query = DB::table($table)->orderBy('id');
        
        if ($cursor) {
            $query->where('id', '>', $cursor);
        }
        
        return $query->limit($limit)->get();
    }
}

| 并发数 | 分页方案   | 第1页 | 第100页 | 第1万页 | 第100万页 |
|--------|------------|-------|--------|---------|----------|
| 100    | OFFSET     | 3200  | 180    | 12      | 0.5      |
| 100    | 游标分页   | 3500  | 3400   | 3380    | 3350     |
| 500    | OFFSET     | 2200  | 崩溃   | 崩溃    | 崩溃     |
| 500    | 游标分页   | 3300  | 3250   | 3200    | 3180     |

+---------------------+---------------+----------------+
| 指标                | OFFSET分页    | 游标分页       |
+---------------------+---------------+----------------+
| CPU峰值利用率       | 95%           | 23%            |
| 内存页错误(/秒)     | 15000         | 450            |
| 网络输出流量(MB/s)  | 280           | 45             |
+---------------------+---------------+----------------+