一覧SE-09-2 · 保守性と性能の改善

SQLの呼び出し回数とキャッシュの改善

同じ結果を得るためのSQL回数を照合し、検索削減とキャッシュの整合性を別々に確認します。

説明

遅い処理を改善するときは、同じ入力で何を何回実行しているかを先に調べます。一覧を取った後に各行の関連情報を個別検索すると、一覧1回と明細N回の、いわゆるN+1の問合せになります。

実SQLiteに顧客3人と注文9件を用意します。SQLの実行記録とcProfileの関数呼出し回数を使い、個別検索、JOIN、名前のキャッシュを比較します。キャッシュで検索回数が減っても、顧客名の更新後に古い値を返す場合があることも確かめます。

同じ結果を得るためのSQL回数を照合し、検索削減とキャッシュの整合性を別々に確認します。

1同じ顧客を参照する九件を準備

顧客は「あおい」「はる」「みお」の3人です。注文1〜9が、この3人を繰り返し参照します。

python3 query.py init顧客と注文の固定データを作る

顧客3件、注文9件を作りました。以降は同じDBを使い、検索方式だけを変えて処理回数と結果を比較します。

2三件の一覧で四回のSELECT

まず3件だけをN+1方式で読み、実行SQLを表示します。

python3 query.py nplus1 --limit 3一覧と各行の顧客を別々に検索する --trace実行されたSELECTも表示する

注文一覧1回と顧客検索3回でSELECTは4回です。lookup_customer_callsはcProfileが記録した関数呼出し回数です。返る行数と問合せ回数を混同しないようにします。

3九件に増やすと十回になる

一覧の対象を9件へ増やします。

python3 query.py nplus1 --limit 9同じ方式で9件を読む

SELECTは10回、顧客検索関数は9回です。同じ3人を何度も検索していても、注文の行ごとに呼出しているため回数が増えます。

4JOINで同じ結果を一度に取る

注文と顧客を結合して読みます。顧客IDは主キーなので、各注文に対応する顧客は一人です。

python3 query.py join --limit 9結合した結果を一つのSQLで取得する --trace実際のJOIN文を確認する

同じ9件をSELECT1回で取得し、顧客を個別に読む関数は0回です。ここで確認した改善は問合せ回数の削減です。経過時間への影響は、データ量・索引・通信などの条件にも左右されます。

5初回のキャッシュは異なる顧客だけ読む

キャッシュ方式では顧客IDごとに名前を保存します。この演習ではcache.jsonに保持し、次の実行からも参照できます。

python3 query.py cached --limit 9顧客IDごとに名前を再利用する

SELECTは一覧1回と異なる顧客3回の計4回です。同じ人の2件目以降はキャッシュを使っています。

6二回目は顧客検索が不要になる

同じ条件でキャッシュ方式を再実行します。

python3 query.py cached --limit 9保持した名前を再利用して読む

SELECTは一覧の1回だけで、顧客検索関数は0回です。ただし、キャッシュした名前が今のDBと一致しているかは、呼出し回数だけでは分かりません。

7名前を更新すると古い値が残る

顧客2の名前を「はるこ」へ変更し、キャッシュ方式で読みます。

python3 query.py update顧客2の名前をDBで更新する python3 query.py cached --limit 9古いキャッシュが返す結果を確認する

顧客2に対応する注文2・5・8は、まだ「はる」と表示されます。DBの更新だけではcache.jsonの値は変わらないためです。

8DBから読む結果と照合する

JOINで現在の値を読みます。

python3 query.py join --limit 9キャッシュを使わず現在の名前を読む

注文2・5・8は「はるこ」です。回数の少ないキャッシュ方式でも、更新後の正しい値を返せなければ要求を満たしていません。

9該当するキーだけを無効にする

顧客2のキャッシュを取り除き、もう一度読みます。

python3 query.py invalidate顧客2のキャッシュだけを削除する python3 query.py cached --limit 9不足した名前を再取得する

一覧1回と顧客2の再検索1回でSELECTは2回です。「はるこ」に更新され、他の二人のキャッシュは再利用されます。更新に合わせた無効化や期限、許容する古さを設計する必要があります。

まとめ

N+1では一覧1回と明細N回の問合せが発生します。この例ではJOINで10回を1回にできました。

SQL回数と関数呼出し数を同じ入力で計測し、返る行と値が保たれるかも確認します。

キャッシュは検索を減らしますが、更新後の古い値への対処が必要です。回数と正しさを別々に検査します。

操作履歴と解説を振り返れます。

シェル / Python 3

1
python3 query.py init
initialized: customers=3 orders=9
2
python3 query.py nplus1 --limit 3 --trace
rows=[[1, "あおい"], [2, "はる"], [3, "みお"]]
SELECTs=4 lookup_customer_calls=3
SELECT id,customer_id FROM orders ORDER BY id LIMIT 3
SELECT name FROM customers WHERE id=1
SELECT name FROM customers WHERE id=2
SELECT name FROM customers WHERE id=3
3
python3 query.py nplus1 --limit 9
rows=[[1, "あおい"], [2, "はる"], [3, "みお"], [4, "あおい"], [5, "はる"], [6, "みお"], [7, "あおい"], [8, "はる"], [9, "みお"]]
SELECTs=10 lookup_customer_calls=9
4
python3 query.py join --limit 9 --trace
rows=[[1, "あおい"], [2, "はる"], [3, "みお"], [4, "あおい"], [5, "はる"], [6, "みお"], [7, "あおい"], [8, "はる"], [9, "みお"]]
SELECTs=1 lookup_customer_calls=0
SELECT o.id,c.name FROM orders o JOIN customers c ON c.id=o.customer_id ORDER BY o.id LIMIT 9
5
python3 query.py cached --limit 9
rows=[[1, "あおい"], [2, "はる"], [3, "みお"], [4, "あおい"], [5, "はる"], [6, "みお"], [7, "あおい"], [8, "はる"], [9, "みお"]]
SELECTs=4 lookup_customer_calls=3
6
python3 query.py cached --limit 9
rows=[[1, "あおい"], [2, "はる"], [3, "みお"], [4, "あおい"], [5, "はる"], [6, "みお"], [7, "あおい"], [8, "はる"], [9, "みお"]]
SELECTs=1 lookup_customer_calls=0
7
python3 query.py update python3 query.py cached --limit 9
updated customer=2 name=はるこ
rows=[[1, "あおい"], [2, "はる"], [3, "みお"], [4, "あおい"], [5, "はる"], [6, "みお"], [7, "あおい"], [8, "はる"], [9, "みお"]]
SELECTs=1 lookup_customer_calls=0
8
python3 query.py join --limit 9
rows=[[1, "あおい"], [2, "はるこ"], [3, "みお"], [4, "あおい"], [5, "はるこ"], [6, "みお"], [7, "あおい"], [8, "はるこ"], [9, "みお"]]
SELECTs=1 lookup_customer_calls=0
9
python3 query.py invalidate python3 query.py cached --limit 9
invalidated customer=2
rows=[[1, "あおい"], [2, "はるこ"], [3, "みお"], [4, "あおい"], [5, "はるこ"], [6, "みお"], [7, "あおい"], [8, "はるこ"], [9, "みお"]]
SELECTs=2 lookup_customer_calls=1

演習完了