OTOBANK Engineering Blog

オトバンクはコンテンツが大好きなエンジニアを募集しています!

Doctrine2 四方山話 ( Fetch mode, Index by, Criteria について)

はじめまして。4月からオトバンクで働き始めました @kalibora と申します。よろしくお願いします。

さて、オトバンクでは Symfony2 を使っており、ORマッパーにはデフォルトの Doctrine2 を使用しているのですが、 そんな中で私が得た知見をいくつか紹介したいと思います。

Fetch mode の話

Doctrine2 には fetch mode という概念があります。

21. Annotations Reference — Doctrine 2 ORM 2 documentation を見ますと Doctrine2には下記の fetch mode があるようです。

fetch mode 定義できる場所
EAGER @ManyToOne @ManyToMany @OneToOne @OneToMany
LAZY @ManyToOne @ManyToMany @OneToOne @OneToMany
EXTRA_LAZY @ManyToMany @OneToMany

この fetch mode は、あるエンティティをリポジトリから取得した際に、 関連するエンティティも同時に取得するかどうか?という挙動に関わってくるものです。

ちなみに何も明示的に定義しないと、デフォルトは LAZY な挙動になります。 すなわち、関連エンティティは取得しません。関連エンティティが必要になった時に自動でデータベースにクエリが走り、取得されます。

使わない(参照されない)関連エンティティも毎回取得してしまうと無駄なので、これは理にかなった挙動だと思います。

ところが、LAZY だけを使っていると、いわゆる n+1 問題と呼ばれる問題に直面します。

どういうことかというと、最初の1回の SELECT で、あるエンティティAが複数返却されたあと、 そのAをループで回すたびに、Aに関連するエンティティBを取得したとすると、そのループ回数文 SELECT してしまう。 というものです。

これを防ぐためには fetch mode を EAGER にして最初の1回の SELECT でAのみならず、関連するエンティティBも全て取ってくるなどの工夫が必要になってきます。

そして EXTRA_LAZY ですが、これは、関連エンティティが必要になった時でも、 その関連エンティティの総数を取得するだけ(などの特定)の場合は、 全レコードをphp側にオブジェクトとしてロードせず(メモリに載せず)、COUNT クエリを発行するだけにします。

もし、こういったユースケースで単に LAZY を使っていると、 関連エンティティの総数が1000件あった場合、1000件をphp側のメモリにエンティティとしてロードしてから、 phpのレイヤでカウントを取ります。

その1000件を使う(内容を参照する)ならそれでいいですが、そうでない場合は無駄ですよね。

EXTRA_LAZY についての詳しい挙動は Extra Lazy Associations — Doctrine 2 ORM 2 documentation に書いてあります。

コードで挙動を確かめる

さて、ここまでの挙動を実際のコードを使って確かめてみます。 (本節で登場するサンプルコードはすべて、 GitHub - kalibora/doctrine_test に格納されています)

例とするデータモデルは、有名な ドメインロジックとSQL から拝借しました。

f:id:kalibora:20160612161022p:plain

顧客(Customer)が注文(Order)を複数持ち、注文が明細(LineItem)を複数持っているというよくあるものです。 メソッドの意味は以下のとおり。

  • Order::isCuillen() はその注文内で、一度にタリスカーという名前のウィスキーを5000円以上買ったかどうか?を判断するメソッド
  • Customer::getCuillenMonths() は 1度でもその月に、1回の注文でタリスカーを5000円以上買っていれば、その月を返すメソッド

ピンクの注釈内のレコード数は、単に私のテストした環境のDBに入っているレコード数です。

アクセサは省略し、主だった部分のみ記載しますが、それぞれのエンティティの定義は以下の通りです。

Customer

<?php
/**
 * @ORM\Entity(repositoryClass="AppBundle\Entity\CustomerRepository")
 * @ORM\Table(name="customers")
 */
class Customer
{
    /**
     * @ORM\Column(type="integer")
     * @ORM\Id
     * @ORM\GeneratedValue(strategy="AUTO")
     */
    private $id;

    /**
     * @ORM\Column(type="string", length=100)
     */
    private $name;

    /**
     * @ORM\OneToMany(targetEntity="Order", mappedBy="customer")
     */
    private $orders;

    public function getCuillenMonths()
    {
        $months = [];

        foreach ($this->orders as $order) {
            if ($order->isCuillen()) {
                $months[] = $order->getDate()->format('Y-m');
            }
        }

        return array_unique($months);
    }
}

Order

<?php
/**
 * @ORM\Entity(repositoryClass="AppBundle\Entity\OrderRepository")
 * @ORM\Table(name="orders")
 */
class Order
{
    /**
     * @ORM\Column(type="integer")
     * @ORM\Id
     * @ORM\GeneratedValue(strategy="AUTO")
     */
    private $id;

    /**
     * @ORM\Column(type="date")
     */
    private $date;

    /**
     * @ORM\ManyToOne(targetEntity="Customer", inversedBy="orders")
     * @ORM\JoinColumn(name="customer_id", referencedColumnName="id")
     */
    private $customer;

    /**
     * @ORM\OneToMany(targetEntity="LineItem", mappedBy="order")
     */
    private $lineItems;

    public function isCuillen()
    {
        $discountableAmount = 0;

        foreach ($this->lineItems as $lineItem) {
            if ($lineItem->getProduct() === 'Talisker') {
                $discountableAmount += $lineItem->getCost();
            }
        }

        return $discountableAmount >= 5000;
    }
}

LineItem

<?php
/**
 * @ORM\Entity
 * @ORM\Table(name="line_items")
 */
class LineItem
{
    /**
     * @ORM\Column(type="integer")
     * @ORM\Id
     * @ORM\GeneratedValue(strategy="AUTO")
     */
    private $id;

    /**
     * @ORM\Column(type="string", length=100)
     */
    private $product;

    /**
     * @ORM\Column(type="decimal")
     */
    private $cost;

    /**
     * @ORM\ManyToOne(targetEntity="Order", inversedBy="lineItems")
     * @ORM\JoinColumn(name="order_id", referencedColumnName="id")
     */
    private $order;
}

このように定義したデータモデルで、下記のようなユースケースにおいて、それぞれの fetch mode ( EAGER, LAZY, EXTRA_LAZY + DQLを用いて関連エンティティをすべてJOINした場合 )でどのようなパフォーマンスの違いが出るか試してみました。

  1. Customer を取得するのみでなにも操作しない
  2. Customer を取得し、$customer->getCuillenMonths() を実行
  3. Customer を取得し、$customer->getOrders()->count() を実行

1. Customer を取得するのみでなにも操作しない

比較結果は以下のとおり

JOIN EAGER LAZY EXTRA_LAZY
実行時間 [msec] 215 174 10 9
メモリ使用量 [MB] 22 22 10 10
クエリ数 1 2 1 1

発行されたクエリは以下のとおり

-- JOIN
SELECT c0_.id AS id_0, c0_.name AS name_1, o1_.id AS id_2, o1_.date AS date_3, l2_.id AS id_4, l2_.product AS product_5, l2_.cost AS cost_6, o1_.customer_id AS customer_id_7, l2_.order_id AS order_id_8 FROM customers c0_ INNER JOIN orders o1_ ON c0_.id = o1_.customer_id INNER JOIN line_items l2_ ON o1_.id = l2_.order_id WHERE c0_.name = ?

-- EAGER
SELECT t0.id AS id_1, t0.name AS name_2 FROM customers t0 WHERE t0.name = ? LIMIT 1
SELECT t0.id AS id_1, t0.date AS date_2, t0.customer_id AS customer_id_3, t4.id AS id_5, t4.product AS product_6, t4.cost AS cost_7, t4.order_id AS order_id_8 FROM orders t0 LEFT JOIN line_items t4 ON t4.order_id = t0.id WHERE t0.customer_id = ?

-- LAZY
SELECT t0.id AS id_1, t0.name AS name_2 FROM customers t0 WHERE t0.name = ? LIMIT 1

-- EXTRA_LAZY
SELECT t0.id AS id_1, t0.name AS name_2 FROM customers t0 WHERE t0.name = ? LIMIT 1

当たり前ですが、 LAZY, EXTRA_LAZY が無駄がないので早いです。

2. Customer を取得し、$customer->getCuillenMonths() を実行

比較結果は以下のとおり

JOIN EAGER LAZY EXTRA_LAZY
実行時間 [msec] 219 185 413 405
メモリ使用量 [MB] 22 22 20 20
クエリ数 1 2 1098 1098

発行されたクエリは以下のとおり

-- JOIN
SELECT c0_.id AS id_0, c0_.name AS name_1, o1_.id AS id_2, o1_.date AS date_3, l2_.id AS id_4, l2_.product AS product_5, l2_.cost AS cost_6, o1_.customer_id AS customer_id_7, l2_.order_id AS order_id_8 FROM customers c0_ INNER JOIN orders o1_ ON c0_.id = o1_.customer_id INNER JOIN line_items l2_ ON o1_.id = l2_.order_id WHERE c0_.name = ?

-- EAGER
SELECT t0.id AS id_1, t0.name AS name_2 FROM customers t0 WHERE t0.name = ? LIMIT 1
SELECT t0.id AS id_1, t0.date AS date_2, t0.customer_id AS customer_id_3, t4.id AS id_5, t4.product AS product_6, t4.cost AS cost_7, t4.order_id AS order_id_8 FROM orders t0 LEFT JOIN line_items t4 ON t4.order_id = t0.id WHERE t0.customer_id = ?

-- LAZY
SELECT t0.id AS id_1, t0.name AS name_2 FROM customers t0 WHERE t0.name = ? LIMIT 1
SELECT t0.id AS id_1, t0.date AS date_2, t0.customer_id AS customer_id_3 FROM orders t0 WHERE t0.customer_id = ?
SELECT t0.id AS id_1, t0.product AS product_2, t0.cost AS cost_3, t0.order_id AS order_id_4 FROM line_items t0 WHERE t0.order_id = ?
SELECT t0.id AS id_1, t0.product AS product_2, t0.cost AS cost_3, t0.order_id AS order_id_4 FROM line_items t0 WHERE t0.order_id = ?
SELECT t0.id AS id_1, t0.product AS product_2, t0.cost AS cost_3, t0.order_id AS order_id_4 FROM line_items t0 WHERE t0.order_id = ?
-- 以下同じものが延々続く・・・

-- EXTRA_LAZY
SELECT t0.id AS id_1, t0.name AS name_2 FROM customers t0 WHERE t0.name = ? LIMIT 1
SELECT t0.id AS id_1, t0.date AS date_2, t0.customer_id AS customer_id_3 FROM orders t0 WHERE t0.customer_id = ?
SELECT t0.id AS id_1, t0.product AS product_2, t0.cost AS cost_3, t0.order_id AS order_id_4 FROM line_items t0 WHERE t0.order_id = ?
SELECT t0.id AS id_1, t0.product AS product_2, t0.cost AS cost_3, t0.order_id AS order_id_4 FROM line_items t0 WHERE t0.order_id = ?
SELECT t0.id AS id_1, t0.product AS product_2, t0.cost AS cost_3, t0.order_id AS order_id_4 FROM line_items t0 WHERE t0.order_id = ?
-- 以下同じものが延々続く・・・

LAZY, EXTRA_LAZYJOIN, EAGER と比較して約2倍の遅さ。クエリ数は n+1問題が発生しているので大量。

3. Customer を取得し、$customer->getOrders()->count() を実行

比較結果は以下のとおり

JOIN EAGER LAZY EXTRA_LAZY
実行時間 [msec] 217 172 42 14
メモリ使用量 [MB] 22 22 14 10
クエリ数 1 2 2 2

発行されたクエリは以下のとおり

-- JOIN
SELECT c0_.id AS id_0, c0_.name AS name_1, o1_.id AS id_2, o1_.date AS date_3, l2_.id AS id_4, l2_.product AS product_5, l2_.cost AS cost_6, o1_.customer_id AS customer_id_7, l2_.order_id AS order_id_8 FROM customers c0_ INNER JOIN orders o1_ ON c0_.id = o1_.customer_id INNER JOIN line_items l2_ ON o1_.id = l2_.order_id WHERE c0_.name = ?

-- EAGER
SELECT t0.id AS id_1, t0.name AS name_2 FROM customers t0 WHERE t0.name = ? LIMIT 1
SELECT t0.id AS id_1, t0.date AS date_2, t0.customer_id AS customer_id_3, t4.id AS id_5, t4.product AS product_6, t4.cost AS cost_7, t4.order_id AS order_id_8 FROM orders t0 LEFT JOIN line_items t4 ON t4.order_id = t0.id WHERE t0.customer_id = ?

-- LAZY
SELECT t0.id AS id_1, t0.name AS name_2 FROM customers t0 WHERE t0.name = ? LIMIT 1
SELECT t0.id AS id_1, t0.date AS date_2, t0.customer_id AS customer_id_3 FROM orders t0 WHERE t0.customer_id = ?

-- EXTRA_LAZY
SELECT t0.id AS id_1, t0.name AS name_2 FROM customers t0 WHERE t0.name = ? LIMIT 1
SELECT COUNT(*) FROM orders t0 WHERE t0.customer_id = ?

JOIN, EAGER が無駄なので遅いのはその通りだが、EXTRA_LAZYLAZY と比較しても無駄がないので、早くなっているのが分かる。 COUNT クエリが発行されているのが確認できる。

結論

特に目新しさのない結論ですが、私的には下記のように考えています。

  • 基本的に、デフォルトの LAZY を使う(何も明示的に定義しない)
  • コレクションで、カウントを取るユースケースがあるものは EXTRA_LAZY を明示的に定義しておく
  • ループでぶん回すユースケースでは、DQLを用いて最初の1回で関連エンティティを全て JOIN してしまう
    • 実行時に動的に fetch mode を EAGER に変更する方法が公式に見当たらないので

LAZY されない話

さてさて、前節では、デフォルトの fetch mode は LAZY であると書きましたが、そうならないケースもあるようです。

具体的には OneToOne リレーションの inverse side (外部キーを持ってないテーブルの方)がそうです。

どういうことか、ちょっと試してみましょう。

まず、国(Country)と首都(CapitalCity)というOneToOneのエンティティを用意します。

Country

<?php
/**
 * @ORM\Entity(repositoryClass="AppBundle\Entity\CountryRepository")
 * @ORM\Table(name="countries")
 */
class Country
{
    /**
     * @ORM\Column(type="integer")
     * @ORM\Id
     * @ORM\GeneratedValue(strategy="AUTO")
     */
    private $id;

    /**
     * @ORM\Column(type="string", length=100)
     */
    private $name;

    /**
     * @ORM\OneToOne(targetEntity="CapitalCity", mappedBy="country")
     */
    private $capitalCity;

CapitalCity

<?php
/**
 * @ORM\Entity(repositoryClass="AppBundle\Entity\CapitalCityRepository")
 * @ORM\Table(name="capital_cities")
 */
class CapitalCity
{
    /**
     * @ORM\Column(type="integer")
     * @ORM\Id
     * @ORM\GeneratedValue(strategy="AUTO")
     */
    private $id;

    /**
     * @ORM\Column(type="string", length=100)
     */
    private $name;

    /**
     * @ORM\OneToOne(targetEntity="Country", inversedBy="capitalCity")
     * @ORM\JoinColumn(name="country_id", referencedColumnName="id")
     */
    private $country;

これをMySQLのテーブルに落とすと以下のようになります。

mysql> desc countries;
+-------+--------------+------+-----+---------+----------------+
| Field | Type         | Null | Key | Default | Extra          |
+-------+--------------+------+-----+---------+----------------+
| id    | int(11)      | NO   | PRI | NULL    | auto_increment |
| name  | varchar(100) | NO   |     | NULL    |                |
+-------+--------------+------+-----+---------+----------------+
2 rows in set (0.01 sec)

mysql> desc capital_cities;
+------------+--------------+------+-----+---------+----------------+
| Field      | Type         | Null | Key | Default | Extra          |
+------------+--------------+------+-----+---------+----------------+
| id         | int(11)      | NO   | PRI | NULL    | auto_increment |
| country_id | int(11)      | YES  | UNI | NULL    |                |
| name       | varchar(100) | NO   |     | NULL    |                |
+------------+--------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)

そしてテストデータを下記のようにINSERTしました。

mysql> SELECT * FROM countries;
+----+-------+
| id | name  |
+----+-------+
|  1 | Japan |
+----+-------+
1 row in set (0.01 sec)

mysql> SELECT * FROM capital_cities;
+----+------------+-------+
| id | country_id | name  |
+----+------------+-------+
|  1 |          1 | Tokyo |
+----+------------+-------+
1 row in set (0.00 sec)

まず、own side (外部キーを持っている方)である CapitalCity を取得してみます。

<?php
// いろいろと省略
$capitalCityRepository = $doctrine->getRepository('AppBundle:CapitalCity');
$entity = $capitalCityRepository->findOneByName('Tokyo');
dump([
    'entity' => $entity,
    'sqls'   => $this->getExecutedSqls() // 実行されたSQLが取得できるメソッド
]);

この出力は下記のようになりました。

array:2 [
  "entity" => AppBundle\Entity\CapitalCity {#477
    -id: 1
    -name: "Tokyo"
    -country: Proxies\__CG__\AppBundle\Entity\Country {#492
      +__isInitialized__: false
      -id: 1
      -name: null
      -capitalCity: AppBundle\Entity\CapitalCity {#477}
       …2
    }
  }
  "sqls" => array:1 [
    0 => "SELECT t0.id AS id_1, t0.name AS name_2, t0.country_id AS country_id_3 FROM capital_cities t0 WHERE t0.name = ? LIMIT 1"
  ]
]

CapitalCity::$countryProxies\__CG__\AppBundle\Entity\Country という謎クラスになっていることと、 実行されたSQLは capital_cities テーブルのみを SELECT していることが確認できました。

次に inverse side である Country を取得してみます。

<?php
// いろいろと省略
$countryRepository = $doctrine->getRepository('AppBundle:Country');
$entity = $countryRepository->findOneByName('Japan');
dump([
    'entity' => $entity,
    'sqls'   => $this->getExecutedSqls() // 実行されたSQLが取得できるメソッド
]);

この出力は下記のようになりました。

array:2 [
  "entity" => AppBundle\Entity\Country {#477
    -id: 1
    -name: "Japan"
    -capitalCity: AppBundle\Entity\CapitalCity {#481
      -id: 1
      -name: "Tokyo"
      -country: AppBundle\Entity\Country {#477}
    }
  }
  "sqls" => array:1 [
    0 => "SELECT t0.id AS id_1, t0.name AS name_2, t3.id AS id_4, t3.name AS name_5, t3.country_id AS country_id_6 FROM countries t0 LEFT JOIN capital_cities t3 ON t3.country_id = t0.id WHERE t0.name = ? LIMIT 1"
  ]
]

Country::$capitalCity は通常の CapitalCity クラスになっており、 実行されたSQLは countriescapital_cities の両方を JOIN して取得しています。

このように、 OneToOne の inverse side のエンティティを取得すると、必ず関連するエンティティを取得するようです。

この理由ですが、Doctrine2 one-to-one relation auto loads on query - Stack Overflow この辺りを私の拙い英語力で斜め読みしたところ、以下が原因だと思われます。

  • LAZY の挙動は、Doctrine2 が動的に生成する本物のエンティティを継承した Proxy クラスを用いることで可能になっている
    • その Proxy クラスが利用されたタイミングでエンティティ内部の値をDBから取得する実装になっている
  • しかし、関連エンティティが null の場合もありうるので、own side では、外部キーの値が null であれば null。 そうでなければ Proxy クラスをセットすることでこの挙動を可能にしている
  • 一方 inverse side では外部キーが存在しないので、その値を見て関連エンティティの存在を確認することができない
  • よって、必ず関連エンティティのテーブルをSELECTする必要がある
  • 別の話として、~ToMany で定義されるコレクションの場合は、要素が0であっても、1であっても、それ以上であっても、コレクションであることには変わらないので、 PersistentCollection というクラスを用いることで LAZY な挙動を実現している

(この辺りは予想も含まれているので、間違いなどありましたらご指摘いただけるとありがたいです。)

INDEX BY の話

だいぶ長くなってきたので、ここからサラッとした話題を。

リポジトリから複数のエンティティを取得すると、そのエンティティの配列が返却されますが、 通常そのインデックスは0から始まる連番になっています。

しかし、プライマリーキーをキーとした連想配列で返してほしい。などど思った時はないでしょうか? 私はあります。

そういった場合に、再度 foreach でループして連想配列を作りなおさずとも、DQL の INDEX BY という機能が使えます。

先ほどの Order エンティティを id をキーとした連想配列で取得する例は以下のとおりです。

<?php
$orderRepository = $doctrine->getRepository('AppBundle:Order');
$orders = $orderRepository
    ->createQueryBuilder('o', 'o.id') // ここの第2引数がポイント
    ->getQuery()
    ->getResult()
;

ちなみに、これを関連エンティティに定義することもできます。

詳細は Working with Indexed Associations — Doctrine 2 ORM 2 documentation にあります。

Criteria の話

さて、ここで話をまた ↓ の Customer, Order, LineItem のデータに戻します。

f:id:kalibora:20160612161022p:plain

特定の Customer の特定の日の Order をすべて取得したい!という要件があった場合に、どう実装しますか?

普通なら OrderRepository を使って Customer と Date を指定して取得すると思いますが、それだとこの話が終わってしまうので・・

Customer に下記のような実装をしてしまったという前提で進めます。(いい例が思い浮かばなかったのです。。)

<?php
class Customer
{
    // いろいろ省略・・・
    public function getOrdersByDate(\DateTimeInterface $date)
    {
        $orders = [];

        foreach ($this->orders as $order) {
            if ($order->getDate()->format('Ymd') === $date->format('Ymd')) {
                $orders[] = $order;
            }
        }

        return $orders;
    }
}

やってることは単純で、内包しているすべての Order をループしながら、指定日と合うものを集めています。

しかしこれだと Order の数に比例して処理が遅くなっていきますね。

こういう場合は下記のように Criteria を使うと、DB側に処理をもっていけます。

<?php
class Customer
{
    // いろいろ省略・・・
    public function getOrdersByDateWithCriteria(\DateTimeInterface $date)
    {
        $criteria = Criteria::create()
            ->where(Criteria::expr()->eq('date', $date))
        ;

        return $this->orders->matching($criteria);
    }
}

matching でDBにSELECTクエリが走ります。

前者と後者で実際に比較すると下記の様になりました。

  • Criteria 未使用
    • 実行時間: 33 [msec]
    • メモリ使用量: 14 [MB]
    • 発行されたSQL
      • SELECT t0.id AS id_1, t0.name AS name_2 FROM customers t0 WHERE t0.name = ? LIMIT 1"
      • SELECT t0.id AS id_1, t0.date AS date_2, t0.customer_id AS customer_id_3 FROM orders t0 WHERE t0.customer_id = ?
  • Criteria 使用
    • 実行時間: 9 [msec]
    • メモリ使用量: 10 [MB]
    • 発行されたSQL
      • SELECT t0.id AS id_1, t0.name AS name_2 FROM customers t0 WHERE t0.name = ? LIMIT 1
      • SELECT t0.id AS id_1, t0.date AS date_2, t0.customer_id AS customer_id_3 FROM orders t0 WHERE (t0.date = ? AND t0.customer_id = ?)"

はい、ちゃんとDBのレイヤで日付を絞ってますね。

ちなみに、これすでに関連エンティティがphp側にロードされてから実行すると、ちゃんとphpのレイヤで日付を絞ります。

賢い。

まとめ

  • Doctrine2 の日本語情報は少ない
  • オトバンクはエンジニアを募集しています