Djangoのクエリセット評価タイミング実験

Djangoのクエリセット評価タイミング
http://djangoproject.jp/doc/ja/1.0/ref/models/querysets.html

実際にSQL出しながら検証していきます。

**準備**
--
環境:
Python 2.7.10
Django 1.9.7
MySQL 5.6.30

データ:
単純な書籍管理クラスを適当に作成

```
mysql> select * from book_book;
+----+------+-------------+-----+----------------------------+----------------------------+
| id | name | description | tag | created_at                 | updated_at                 |
+----+------+-------------+-----+----------------------------+----------------------------+
|  1 | A    | A's book    | A   | 2016-06-22 01:50:25.895551 | 2016-06-22 01:50:25.895986 |
|  2 | B    | B's book    | B   | 2016-06-22 01:50:36.743094 | 2016-06-22 01:50:36.743139 |
|  3 | C    | C's book    | C   | 2016-06-22 01:50:46.279098 | 2016-06-22 01:50:46.279141 |
|  4 | D    | D's book    | D   | 2016-06-22 01:50:56.831194 | 2016-06-22 01:50:56.831237 |
|  5 | E    | E's book    | E   | 2016-06-22 01:51:06.663500 | 2016-06-22 01:51:06.663562 |
+----+------+-------------+-----+----------------------------+----------------------------+
5 rows in set (0.00 sec)
```

発行SQLの表示:

```settings.py
LOGGING = {
    'version': 1,
    'handlers': {
        'console': {
            'level': 'DEBUG',
            'class': 'logging.StreamHandler',
        },
    },

    'loggers': {
        'django.db.backends': {
            'level': 'DEBUG',
            'handlers': ['console'],
        },
    }
}
```

**実践**
--

まずはgetしてみます。

```
>>> query = Book.objects.get(id=1)
(0.001) SELECT `book_book`.`id`, `book_book`.`name`, `book_book`.`description`, `book_book`.`tag`, `book_book`.`created_at`, `book_book`.`updated_at` FROM `book_book` WHERE `book_book`.`id` = 1; args=(1,)
```
getで取得したらいきなりSQLが発行されました。
これは、

```
>>> type(query)
<class 'book.models.Book'>
```
クエリセットではなくオブジェクトで取得されるからですね

これをfilterに変えると

```
>>> query = Book.objects.filter(id=1)
>>> type(query)
<class 'django.db.models.query.QuerySet'>
```
クエリセットで取得され、この時点ではSQLは発行させません。
これにドキュメントに従って各操作をしていきます。

**イテレーション**

```
>>> for book in query:
...   print book
... 
(0.001) SELECT `book_book`.`id`, `book_book`.`name`, `book_book`.`description`, `book_book`.`tag`, `book_book`.`created_at`, `book_book`.`updated_at` FROM `book_book` WHERE `book_book`.`id` = 1; args=(1,)
Book object
```

**スライス**

```
>>> query[:1]
(0.001) SELECT `book_book`.`id`, `book_book`.`name`, `book_book`.`description`, `book_book`.`tag`, `book_book`.`created_at`, `book_book`.`updated_at` FROM `book_book` WHERE `book_book`.`id` = 1 LIMIT 1; args=(1,)
[<Book: Book object>]
```

**repr**

```
>>> repr(query)
(0.001) SELECT `book_book`.`id`, `book_book`.`name`, `book_book`.`description`, `book_book`.`tag`, `book_book`.`created_at`, `book_book`.`updated_at` FROM `book_book` WHERE `book_book`.`id` = 1 LIMIT 21; args=(1,)
'[<Book: Book object>]'
```

**len**

```
>>> len(query)
(0.004) SELECT `book_book`.`id`, `book_book`.`name`, `book_book`.`description`, `book_book`.`tag`, `book_book`.`created_at`, `book_book`.`updated_at` FROM `book_book` WHERE `book_book`.`id` = 1; args=(1,)
1
```

**list**

```
>>> list(query)
(0.001) SELECT `book_book`.`id`, `book_book`.`name`, `book_book`.`description`, `book_book`.`tag`, `book_book`.`created_at`, `book_book`.`updated_at` FROM `book_book` WHERE `book_book`.`id` = 1; args=(1,)
[<Book: Book object>]
```
ドキュメント通り各操作でクエリセットが評価されSQL発行されました。

**キャッシュ**
--
1回評価されたクエリセットはキャッシュされ、再度評価される操作をしてもSQL発行されません。

```
>>> query = Book.objects.filter(id=1)
>>> list(query)
(0.001) SELECT `book_book`.`id`, `book_book`.`name`, `book_book`.`description`, `book_book`.`tag`, `book_book`.`created_at`, `book_book`.`updated_at` FROM `book_book` WHERE `book_book`.`id` = 1; args=(1,)
[<Book: Book object>]
>>> list(query)
[<Book: Book object>]
>>> list(query)
[<Book: Book object>]
>>> query[:1]
[<Book: Book object>]
>>> len(query)
1
```

ここでキャッシュされるのはクエリセットであってSQLではないので注意が必要です。

```
>>> # クエリセット評価結果はキャッシュされる
>>> for i in xrange(1, 5):
...   list(query)
... 
[<Book: Book object>]
[<Book: Book object>]
[<Book: Book object>]
[<Book: Book object>]
[<Book: Book object>]
```

```
>>> # SQLの結果自体はキャッシュされない
>>> for i in xrange(1, 5):
...   list(Book.objects.filter(id=1))
... 
(0.001) SELECT `book_book`.`id`, `book_book`.`name`, `book_book`.`description`, `book_book`.`tag`, `book_book`.`created_at`, `book_book`.`updated_at` FROM `book_book` WHERE `book_book`.`id` = 1; args=(1,)
[<Book: Book object>]
(0.002) SELECT `book_book`.`id`, `book_book`.`name`, `book_book`.`description`, `book_book`.`tag`, `book_book`.`created_at`, `book_book`.`updated_at` FROM `book_book` WHERE `book_book`.`id` = 1; args=(1,)
[<Book: Book object>]
(0.001) SELECT `book_book`.`id`, `book_book`.`name`, `book_book`.`description`, `book_book`.`tag`, `book_book`.`created_at`, `book_book`.`updated_at` FROM `book_book` WHERE `book_book`.`id` = 1; args=(1,)
[<Book: Book object>]
(0.002) SELECT `book_book`.`id`, `book_book`.`name`, `book_book`.`description`, `book_book`.`tag`, `book_book`.`created_at`, `book_book`.`updated_at` FROM `book_book` WHERE `book_book`.`id` = 1; args=(1,)
[<Book: Book object>]
(0.001) SELECT `book_book`.`id`, `book_book`.`name`, `book_book`.`description`, `book_book`.`tag`, `book_book`.`created_at`, `book_book`.`updated_at` FROM `book_book` WHERE `book_book`.`id` = 1; args=(1,)
```

また、クエリセットに条件追加したものはキャッシュされません。
(下例はBook.objects.filter(id=1).order_by('id')と等価なので)

```
>>> query.order_by('id')
(0.001) SELECT `book_book`.`id`, `book_book`.`name`, `book_book`.`description`, `book_book`.`tag`, `book_book`.`created_at`, `book_book`.`updated_at` FROM `book_book` WHERE `book_book`.`id` = 1 ORDER BY `book_book`.`id` ASC LIMIT 21; args=(1,)
[<Book: Book object>]
>>> query.order_by('id')
(0.001) SELECT `book_book`.`id`, `book_book`.`name`, `book_book`.`description`, `book_book`.`tag`, `book_book`.`created_at`, `book_book`.`updated_at` FROM `book_book` WHERE `book_book`.`id` = 1 ORDER BY `book_book`.`id` ASC LIMIT 21; args=(1,)
[<Book: Book object>]
```

この場合は次のようにすればキャッシュされます。

```
>>> query2 = query.order_by('id')
>>> list(query2)
(0.001) SELECT `book_book`.`id`, `book_book`.`name`, `book_book`.`description`, `book_book`.`tag`, `book_book`.`created_at`, `book_book`.`updated_at` FROM `book_book` WHERE `book_book`.`id` = 1 ORDER BY `book_book`.`id` ASC; args=(1,)
[<Book: Book object>]
>>> list(query2)
[<Book: Book object>]
```

次のような順番でも発行されるSQLが変わるためそれぞれ個別評価になるようです。

```
>>> query = Book.objects.filter(id=1)
>>> query[:1]
(0.001) SELECT `book_book`.`id`, `book_book`.`name`, `book_book`.`description`, `book_book`.`tag`, `book_book`.`created_at`, `book_book`.`updated_at` FROM `book_book` WHERE `book_book`.`id` = 1 LIMIT 1; args=(1,)
[<Book: Book object>]
>>> list(query)
(0.001) SELECT `book_book`.`id`, `book_book`.`name`, `book_book`.`description`, `book_book`.`tag`, `book_book`.`created_at`, `book_book`.`updated_at` FROM `book_book` WHERE `book_book`.`id` = 1; args=(1,)
[<Book: Book object>]
```
query[:1]を最初にするとBook.objects.filter(id=1)のクエリに対してLimit 1を付加するためです。
ただし、list(query)の後にquery[:1]をするとlist(query)のスライスを取得するためキャッシュが効く模様。