[MySQL] LIMIT 分页优化

 

背景：LIMIT 0,20 这种分页方式，随着 offset 值的不断增大，当达到百万级时，一条查询就需要1秒以上，这时可以借助索引条件的查询来优化。

SQL：select * from member where status = 1 limit 0,20;  改写  select * from member where id >= 1 and status = 1 limit 20;

代码片段：

/**
 * limit 分页优化
 * @author ercom
 */
$startTime = time();

$condition = [
    ['status',   '=', 1],
    ['platform', '=', 1],
];

$count = Member::query()
    ->where($condition)
    ->count('id');

$pageSize = 20;
$pageNum = ceil($count / $pageSize);

$startMemberId = 1;
for ($i = 1; $i <= $pageNum; $i++) {
    $condition = [
        ['id',       '>=', $startMemberId],
        ['status',   '=', 1],
        ['platform', '=', 1],
    ];

    $results = Member::query()
        ->where($condition)
        ->orderBy('id', 'asc')
        ->limit($pageSize)
        ->get();

    if ($results->isNotEmpty()) {
        $memberArr = $results->toArray();

        dispatch(new TransferMemberJob($memberArr));

        $startMemberId = max(array_column($memberArr, 'id')) + $pageSize;
    } else {
        $startMemberId = $startMemberId + $pageSize;
    }

    $this->info(sprintf('page=%s, startMemberId=%s', $i, $startMemberId));
}

  $endTime = time();

  $seconds = $endTime - $startTime;
  $hours = sprintf('%.2f', $seconds/3600);
  $rps = sprintf('%.2f', $count/$seconds);
  $qps = sprintf('%.2f', $pageNum/$seconds);

$this->info(sprintf('All finished, %s / %s, total %s rows, cost %s hours, %s rows/s, %s query/s', date('Y-m-d H:i:s', $startTime), date('Y-m-d H:i:s', $endTime), $count, $hours, $rps, $qps));

 

Refer：MySQL分页优化方式

Link：https://www.cnblogs.com/farwish/p/10926820.html