[Phalcon] Retrieve only records of the current page in paginator to improve performance
IPFS
Phalcon 的 model search 配合 paginator 就可以很方便地幫查詢資料結果做分頁,但是如果資料筆數過多,則會有速度過慢的問題。
dev tool 產生的程式碼與問題
原本用 phalcon-devtool 產生的 search 相關 volt 檔案與 controller 檔案大概會長這樣
<div class="row"> <table class="table table-bordered"> <thead> <tr> <th>Field A</th> <th>Field B</th> <th>Field C</th> <th></th> <th></th> </tr> </thead> <tbody> {% if page.items is defined %} {% for modelobjs in page.items %} <tr> <td>{{ modelobjs.fielda}}</td> <td>{{ modelobjs.fieldb}}</td> <td>{{ modelobjs.fieldc}}</td> <td>{{ link_to("modelobjs/edit/"~modelobjs.fielda, "Edit") }}</td> <td>{{ link_to("modelobjs/delete/"~modelobjs.fielda, "Delete", "onclick":"return confirm('Are you sure?')") }}</td> </tr> {% endfor %} {% endif %} </tbody> </table> </div> <div class="row"> <div class="col-sm-1"> <p class="pagination" style="line-height: 1.42857;padding: 6px 12px;"> {{ page.current~"/"~page.total_pages }} </p> </div> <div class="col-sm-8"> <nav> <ul class="pagination"> <li>{{ link_to("modelobjs/search", "First") }}</li> <li>{{ link_to("modelobjs/search?page="~page.before, "Previous") }}</li> <li>{{ link_to("modelobjs/search?page="~page.next, "Next") }}</li> <li>{{ link_to("modelobjs/search?page="~page.last, "Last") }}</li> </ul> </nav> </div> <div class="col-sm-3"> <p class="pagination" style="line-height: 1.42857;padding: 6px 12px;"> Total count: {% if page.items is defined %} {{ page.total_items }} {%endif%} </p> </div> </div>
public function searchAction() { $numberPage = 1; if ($this->request->isPost()) { $query = Criteria::fromInput($this->di, 'Modelobj', $_POST); $this->persistent->parameters = $query->getParams(); } else { $numberPage = $this->request->getQuery("page", "int"); } $parameters = $this->persistent->parameters; if (!is_array($parameters)) { $parameters = []; } $parameters["order"] = "eventtypeid"; $modelobjs = Modelobj::find($parameters); if (count($modelobjs ) == 0) { $this->flash->notice("The search did not find any modelobjs"); $this->dispatcher->forward([ "controller" => "modelobjs", "action" => "index" ]); return; } $paginator = new Paginator([ 'data' => $modelobjs, 'limit'=> 10, 'page' => $numberPage ]); $this->view->page = $paginator->getPaginate(); }
可以發現searchAction()中,因為做了 Model::find($parameter),且 $parameter 沒有限制回傳資料筆數區間,所以如果符合條件的資料有上萬筆,那可能光資料傳送的時間就吃掉許多。
解決方式
解決的方式就是在 $parameter 中加入 offset 與 limit (實際上還可以加入 order 等資料,可以參考 phalcon 官方文件),這樣就可以限縮回傳的資料剛好是頁面顯示需要的筆數。然後透過 Model::count($parameter) 取得筆數,來計算全部頁數以及目前頁數等原先 Paginator 會幫忙計算的東西。
public function searchAction() { $numberPage = 1; if ($this->request->isPost()) { $query = Criteria::fromInput($this->di, 'Modelobj', $_POST); $this->persistent->parameters = $query->getParams(); } else { $numberPage = $this->request->getQuery("page", "int"); } $parameters = $this->persistent->parameters; if (!is_array($parameters)) { $parameters = []; } //加入 order, limit, offset 參數。limit 就等於是每頁資料筆數。 $parameters["order"] = "fielda, fieldb"; $parameters["limit"] = "10"; $parameters["offset"] = max(0,($numberPage - 1)) * 10; $modelobjs = Modelobj::find($parameters); if (count($modelobjs) == 0) { $this->flash->notice("The search did not find any modelobjs"); $this->dispatcher->forward([ "controller" => "modelobjs", "action" => "index" ]); return; } //拿掉 offset, limit, order ,呼叫 count() 來計算全部資料筆數,這個 function call 實際上就是執行 select count(*) unset($parameters["offset"]); unset($parameters["limit"]); unset($parameters["order"]); $modelobj_count = Modelobj::count($parameters); $paginator = new Paginator([ 'data' => $eventlog, 'limit'=> 10, // 'page' => $numberPage //目前在第幾頁就變成要自己計算 ]); $this->view->page = $paginator->getPaginate(); //計算全部頁數,目前在第幾頁等資訊 $total_pages = ceil($modelobj_count/10); $this->view->page->total_pages = $total_pages; $this->view->page->last = $total_pages; $this->view->page->next = min($total_pages, $numberPage + 1); $this->view->page->before = max(1, $numberPage - 1); $this->view->page->total_items = $eventlog_count; $this->view->page->current = max(0, ($numberPage - 1)) + 1; }
Original link: Phanix's Blog
喜欢我的作品吗?别忘了给予支持与赞赏,让我知道在创作的路上有你陪伴,一起延续这份热忱!