[Phalcon] 仅检索分页器中当前页面的记录以提高性能

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