要点
大量のデータをPaginator->paginate() しているページのレスポンスがものすごく遅いときに、対策したことを書きます。
結果的に５つの対策を実施しました。
- Paginator::paginateでDQL(SQL)を実行させない
- FORCE INDEXでインデックスを強制的に効かせる
- 不必要に大きいサイズを確保しているカラムはサイズを小さくする
- 最後の1ページでLIMITを最大件数より多く設定するとインデックスが効かないので件数を補正
- はじめに主キーだけを検索して、取得した主キーで明細を取得する
環境
実際、対策したプロジェクトの各Bundleのバージョンなどは下記のとおりです。
（全てではありません。）
DBはMySQL 5.5を使用しています。
{
"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を実行させない
改善する前の状況
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;
}
}
<div class="navigation pagination-centered">
{{ knp_pagination_render(pagination) }}
</div>
Profilerで確認してもらえれば、分かりますが、$paginator->paginateで実行されるSQLは、無駄が多く速度を改善しようと思っても、実装が隠蔽されているので、抜本的な解決は難しいです。
とはいえ、twigでknp_pagination_renderを使っている場合は、paginationオブジェクトを渡さなければいけません。
改善した後の状況
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秒ほどに改善しました。
参考
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
SqlWalkerを継承して、UseIndexWalkerを作成
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句の内容を変更することができます。
強制的に効かせたいインデックスを指定
order byするときに、Using filesortが発生していたので、ORDER BY句で指定しているキーでインデックスを作成し、FORCE INDEXでそれを強制的に効かせます。
$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 ''
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すると、なんと強制的に効かせたはずのインデックスが効いていません。
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句の件数を残り件数にあわせてあげると、インデックスが効くようになることに気付きました。
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
つまりはコード側で、下記のように対処します。
$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句で主キーだけを指定すると早くなる
なので、
- いったん主キーだけを検索し
- その主キーを元に明細を取得する
とクエリを分けてはどうか、という提案です。
## 主キーだけ検索
SELECT id FROM something FORCE INDEX (index_for_orderby)
WHERE ...
ORDER BY ...
LIMIT
80 OFFSET 179900
## 主キーを元に明細を取得
SELECT * FROM something
WHERE id IN (...)
ちなみに、IN句へのパラメータは以下のようにセットします。
$query->setParameter('ids', $ids, Connection::PARAM_STR_ARRAY);
ただ、これ、Doctrineのほうでもこういうクエリーを元々作ってくれていた気がします。。
なので、SQLWalkerなど駆使すれば、汎用的な部分の実装を隠蔽したままチューニングできたのかもしれない。。。（未検証）
結果とまとめ
最終的なソースは以下のようになりました。
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 との戦いになってしまいましたが、似たような壁にぶちあたったときに参考にしていただけると幸いです。