LoginSignup
34
38

More than 5 years have passed since last update.

@imunew(Motohiro Imura)

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

Last updated at Posted at 2014-10-16

要点

大量のデータをPaginator->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秒ほどに改善しました。

参考

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を作成

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

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

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

参考）ORDER BY Optimization

$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など駆使すれば、汎用的な部分の実装を隠蔽したままチューニングできたのかもしれない。。。（未検証）

結果とまとめ

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

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 との戦いになってしまいましたが、似たような壁にぶちあたったときに参考にしていただけると幸いです。

34
38
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
Sign upLogin
34
38