[MySQL] LIMIT 分页优化

通过llama.cpp与羊驼聊天的网页界面- 详解 Serge 的启动使用

 

背景: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

posted on 2019-05-26 18:22  ercom  阅读(457)  评论(0编辑  收藏  举报