Symfony2 + Doctrine + KnpPaginatorBundle 環境で、速度改善しなければいけないときにしたこと


## 要点
大量のデータを[Paginator](https://github.com/KnpLabs/knp-components/blob/master/src/Knp/Component/Pager/Paginator.php)->paginate() しているページのレスポンスがものすごく遅いときに、対策したことを書きます。
結果的に５つの対策を実施しました。

- Paginator::paginateでDQL(SQL)を実行させない
- FORCE INDEXでインデックスを強制的に効かせる
- 不必要に大きいサイズを確保しているカラムはサイズを小さくする
- 最後の1ページでLIMITを最大件数より多く設定するとインデックスが効かないので件数を補正
- はじめに主キーだけを検索して、取得した主キーで明細を取得する


## 環境
実際、対策したプロジェクトの各Bundleのバージョンなどは下記のとおりです。
（全てではありません。）
DBはMySQL 5.5を使用しています。

~~~composer.json
{
	"require": {
		"php": ">=5.3.3",
		"symfony/symfony": "2.3.*",
		"doctrine/orm": ">=2.2.3,<2.4-dev",
		"knplabs/knp-paginator-bundle": "dev-master",
	}
}
~~~

## Paginator::paginateでSQLを実行させない
### 改善する前の状況

~~~SomethingRespository.php
use Doctrine\ORM\EntityRepository;
use Knp\Component\Pager\Paginator;

class SomethingRepository extends EntityRepository {

	public function paginate (Paginator $paginator, $params = array()) {

		$dql = 'SELECT s FROM something s WHERE s.param1 = :param1';
		$parameters = array(':param1' => 'value1');
		
		$em = $this->getEntityManager();
		$query = $em->createQuery($dql);
		$query->setParameters($binds);

		$pagination = $paginator->paginate(
		    $query,
		    $params['page'] ?: 1,
		    100
		);		
		
		return $pagination;
	}
}
~~~

~~~something.html.twig
<div class="navigation pagination-centered">
    {{ knp_pagination_render(pagination) }}
</div>
~~~

Profilerで確認してもらえれば、分かりますが、$paginator->paginateで実行されるSQLは、無駄が多く速度を改善しようと思っても、実装が隠蔽されているので、抜本的な解決は難しいです。
とはいえ、twigでknp_pagination_renderを使っている場合は、paginationオブジェクトを渡さなければいけません。


### 改善した後の状況

~~~SomethingRespository.php
use Doctrine\ORM\EntityRepository;
use Knp\Component\Pager\Paginator;
use Knp\Bundle\PaginatorBundle\Pagination\SlidingPagination;

class SomethingRepository extends EntityRepository {

	public function paginate (Paginator $paginator, $params = array()) {

		$dql = 'SELECT s FROM something s WHERE s.param1 = :param1';
		$parameters = array(':param1' => 'value1');

		$em = $this->getEntityManager();

		// 合計件数を取得
		$countDql = 'SELECT COUNT(s) FROM `something` s';
		$countQuery = $em->createQuery($countDql);
		$count = $countQuery->getSingleScalarResult();

		// 明細を取得
		$query = $em->createQuery($dql);
		$query->setParameters($parameters);

		$query->setHint('knp_paginator.count', $count);
		$page = $params['page'] ?: 1;
		$query->setMaxResults(100);
		$query->setFirstResult((($page - 1) * 100));
		$items = $query->getResult();

		// Paginationオブジェクトをセット
		$pagination = $paginator->paginate(array());
		/* @var $pagination SlidingPagination */
		$pagination->setCurrentPageNumber($page);
		$pagination->setItemNumberPerPage(100);
		$pagination->setTotalItemCount($count);
		$pagination->setItems($items);
		
		return $pagination;
	}
}
~~~

### 改善ポイント
- 合計件数と明細をそれぞれ取得
- $paginator->paginate(array())で、空の$paginationを取得
- 空の$paginationに、setTotalItemCount、setItemsなど必要なデータをセットする

### 結果
クエリ自体の見直しも一部実施し、9秒ほどかかっていた処理が、3秒ほどに改善しました。

### 参考
- [KnpPaginatorBundle/manual_counting.md](https://github.com/KnpLabs/KnpPaginatorBundle/blob/master/Resources/doc/manual_counting.md)


## FORCE INDEXでインデックスを強制的に効かせる

改善したとはいえ、まだ3秒かかるので、もう少し頑張ります。

SQLの実行計画を確認して、インデックスが効いていないことが分かりました。
インデックスを作成しただけでは、インデックスが効かない場合に、FORCE INDEXで使用するインデックスを強制的に指定することができます。

Index Hint Syntax - MySQL
http://dev.mysql.com/doc/refman/5.5/en/index-hints.html

ただ、DoctrineのDQLには、FORCE INDEXはそのまま書けないので、CustomeOutputWalkerを使って、FORCE INDEXを効かせます。
（もう生SQLでいいじゃん、って気もしますが...）

下記のgistを参考に実装していきます。

[USE INDEX / FORCE INDEX in a Doctrine2 DQL query](https://gist.github.com/arnaud-lb/2704404)

### SqlWalkerを継承して、UseIndexWalkerを作成

```My/SomthingBundle/Query/UseIndexWalker.php

namespace My/SomthingBundle/Query;

use Doctrine\ORM\Query\SqlWalker;

class UseIndexWalker extends SqlWalker
{
    const HINT_FORCE_INDEX = 'UseIndexWalker.ForceIndex';
 
    public function walkFromClause($fromClause)
    {
        $result = parent::walkFromClause($fromClause);
 
        if ($index = $this->getQuery()->getHint(self::HINT_FORCE_INDEX)) {
            $result = preg_replace('#(\bFROM\s*\w+\s*\w+)#', '\1 FORCE INDEX (' . $index . ')', $result);
        }
 
        return $result;
    }
}
```

DQLからSQLを生成する際に、walkFromClause関数内で、FROM句の内容を変更することができます。

[参考）SqlWalker.php](
https://github.com/doctrine/doctrine2/blob/master/lib/Doctrine/ORM/Query/SqlWalker.php)

### 強制的に効かせたいインデックスを指定

order byするときに、Using filesortが発生していたので、ORDER BY句で指定しているキーでインデックスを作成し、FORCE INDEXでそれを強制的に効かせます。

[参考）ORDER BY Optimization](
http://dev.mysql.com/doc/refman/5.6/en/order-by-optimization.html)

```
$query->setHint(Query::HINT_CUSTOM_OUTPUT_WALKER, "My\\SomethingBundle\\Query\\UseIndexWalker");
$query->setHint(UseIndexWalker::HINT_FORCE_INDEX, 'index_for_orderby');
```

## 不必要に大きいサイズを確保しているカラムはサイズを小さくする

尚、今回インデックスを新たに作成するにあたり、不必要に大きなサイズを確保しているカラムがあったので、必要十分なサイズに変更しました。
コード値が255バイトも取られていましたが、実際には25バイトで十分でしたので、変更して、インデックスを作成します。

```
`code` varchar(255) NOT NULL DEFAULT ''
```

```SQL
ALTER TABLE somethind 
	MODIFY code VARCHAR(25) NOT NULL DEFAULT '';
CREATE INDEX index_for_orderby ON something (code, ...);
```

## 最後の1ページでLIMITを最大件数より多く設定するとインデックスが効かないので件数を補正

ここまででも最初の状態に比べればだいぶ早くなったのですが、後のほうのページになればなるほど遅くなり、100件ごとのページで最後の1,800ページ目（合計179,980件）では、3秒以上かかる結果となっていました。

ここで、最終ページのクエリをEXPLAINすると、なんと強制的に効かせたはずのインデックスが効いていません。

```SQL
SELECT * FROM something FORCE INDEX (index_for_orderby) 
WHERE ...
ORDER BY ...
LIMIT 
  100 OFFSET 179900
```

```
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	PRIMARY	something	ALL	NULL	NULL	NULL	NULL	179991	Using where; Using filesort
```

ここでかなり時間が取られました。
が、LIMIT句の件数を残り件数にあわせてあげると、インデックスが効くようになることに気付きました。

```SQL
SELECT * FROM something FORCE INDEX (index_for_orderby) 
WHERE ...
ORDER BY ...
LIMIT 
  80 OFFSET 179900
```

```
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	PRIMARY	a0_	index	NULL	index_for_orderby	106	NULL	179980	Using where
```

つまりはコード側で、下記のように対処します。

```PHP
$page = $params['page'] ?: 1;
$offset = (($page - 1) * 100);
$limit = min([100, (int)$count - $offset]);
$query->setMaxResults($limit);
$query->setFirstResult($offset);

```

## はじめに主キーだけを検索して、取得した主キーで明細を取得する

最後の1ページに関しては数100ミリ秒改善したのですが、まだ3秒台だったので、もう一踏ん張りします。
パフォーマンステストしているインフラエンジニアから下記の提案がありました。

### 主キーだけの検索は早い

当たり前な話ではあるのですが、

- SELECT句にて主キーだけを指定すると早くなる
- WHERE句で主キーだけを指定すると早くなる

なので、

- いったん主キーだけを検索し
- その主キーを元に明細を取得する

とクエリを分けてはどうか、という提案です。

```SQL
## 主キーだけ検索
SELECT id FROM something FORCE INDEX (index_for_orderby) 
WHERE ...
ORDER BY ...
LIMIT 
  80 OFFSET 179900

## 主キーを元に明細を取得
SELECT * FROM something
WHERE id IN (...)
```

ちなみに、IN句へのパラメータは以下のようにセットします。

```PHP
$query->setParameter('ids', $ids, Connection::PARAM_STR_ARRAY);
```

ただ、これ、Doctrineのほうでもこういうクエリーを元々作ってくれていた気がします。。
なので、SQLWalkerなど駆使すれば、汎用的な部分の実装を隠蔽したままチューニングできたのかもしれない。。。（未検証）

## 結果とまとめ

最終的なソースは以下のようになりました。

~~~SomethingRespository.php
use Doctrine\ORM\EntityRepository;
use Doctrine\ORM\Query;
use Knp\Component\Pager\Paginator;
use Knp\Bundle\PaginatorBundle\Pagination\SlidingPagination;

class SomethingRepository extends EntityRepository {

    public function paginate (Paginator $paginator, $params = array()) {

        $idsDql = 'SELECT s.id FROM something s WHERE s.param1 = :param1';
        $itemDql = 'SELECT s FROM something s';
        $parameters = array(':param1' => 'value1');

        $em = $this->getEntityManager();

        // 合計件数を取得
        $countDql = 'SELECT COUNT(s) FROM `something` s';
        $countQuery = $em->createQuery($countDql);
        $count = $countQuery->getSingleScalarResult();

        // 主キーを検索
        $idsQuery = $em->createQuery($idsDql);
        $idsQuery->setParameters($parameters);

        $idsQuery->setHint('knp_paginator.count', $count);
        $page = $params['page'] ?: 1;
        $offset = (($page - 1) * 100);
        $limit = min([100, (int)$count - $offset]);
        $idsQuery->setMaxResults($limit);
        $idsQuery->setFirstResult($offset);
        $idsQuery->setHint(Query::HINT_CUSTOM_OUTPUT_WALKER, "My\\SomethingBundle\\Query\\UseIndexWalker");
        $idsQuery->setHint(UseIndexWalker::HINT_FORCE_INDEX, 'index_for_orderby');

        $ids = $idsQuery->getArrayResult();

        $items = array();
        if (!empty($ids)) {
            $itemDql .= ' WHERE s.id IN (:ids)';
            $itemQuery = $em->createQuery($itemDql);
            $itemQuery->setParameter('ids', $ids, Connection::PARAM_STR_ARRAY);
            $items = $itemQuery->getResult();
        }

        //Paginationオブジェクトをセット
        $pagination = $paginator->paginate(array());
        /* @var $pagination SlidingPagination */
        $pagination->setCurrentPageNumber($page);
        $pagination->setItemNumberPerPage(100);
        $pagination->setTotalItemCount($count);
        $pagination->setItems($items);

        return $pagination;
    }
}
~~~

３秒台から2秒台への壁が相当高かったですが、なんとか改善できました。

後半は、KnpPaginatorBundle というより、Doctrine というか MySQL との戦いになってしまいましたが、似たような壁にぶちあたったときに参考にしていただけると幸いです。