多言語情報をデータベースにどう持つか @さくらの夕べ Tech Night #3 Online / Solve N+1 on Django

Transcript

1. © SAKURA internet Inc. 多⾔語情報をデータベースにどう持つか 2020-10-28@さくらの⼣べ Tech Night #3 Online 執⾏役員 兼 CISO 江草 陽太
2. 2 【所属等】 さくらインターネット株式会社 新卒⼊社 執⾏役員 技術推進統括担当 兼 CISO IzumoBASE株式会社 取締役 【開発】 • さくらのVPS API/DB/制御システム担当 • sakura.io ハードウエア仕様、ファームウエア開発 システム設計、開発、インフラ設計、構築 • 社内システム/データセンター⾃動化 • さくらのエンジニアリングラボ 【その他】 • CSAJ U22プログラミング・コンテスト審査員 • Home NOC Operatorsʼ Group (AS59105) @chibiegg
3. 3 【経歴】 • ロボカップジュニア (中学・⾼校) / NHK⼤学ロボコン • ⼤阪⼤学⼯学部電⼦情報⼯学科情報通信⼯学専攻 • 個⼈事業主 • ⼤阪⼤学⼤学院⼯学研究科中退 • SECCON CTF 2014 国内4位 • ISUCON5/ISUSON7/ISUCON8本戦、ISUCON9問題作成 • ICTSC7 ⼤⼈チーム 【専⾨】 • ソフトウエアエンジニア • NW/SC/DBスペシャリスト • 回路設計/組み込みソフトウエア 【趣味】 旅⾏/温泉/写真/電⼦⼯作/プログラミング/かわいい服 @chibiegg
4. © SAKURA internet Inc. • RDBMSで多言語情報をどう持つかということを例に、 N+1問題を紹介します • 全体的にDjangoを想定しているが、 他のフレームワークでも応用できるはず 今⽇話すこと
5. © SAKURA internet Inc. もしも、ただの定数なら 動的ではなく、コード中の⽂字列を国際化/地域化する場合
6. © SAKURA internet Inc. • gettextを使うのが一般的 1. コード中に文字列を書く ex: _(“Invalid input”) 2. ロケールファイル (*.po) を自動生成する 3. ロケールファイルを翻訳する 4. ロケールファイルをコンパイルする (*.mo) もしも、ただの定数なら 動的ではなく、コード中の⽂字列を国際化/地域化する場合
7. © SAKURA internet Inc. from django.utils.translation import ugettext as _ localized_text = _(“this is a message”) python manage.py makemessages -l ja # *.po を更新 python manage.py compilemessages # *.mo にコンパイル (参考) Djangoの場合
8. © SAKURA internet Inc. 多⾔語で情報を動的に扱いたい 商品情報などを多⾔語で扱う場合など
9. © SAKURA internet Inc. • 基本情報を扱うテーブルを用意する (Product) • 言語別に持つテーブルを用意する (ProductLocalized) • Product 1 – N ProductLocalzied となるようにリレーショナルな扱いをする データベース構造を考える 例として商品情報を扱う
10. © SAKURA internet Inc. • id • EANコード • 価格 • (などなど、言語によらず共通の情報) Product
11. © SAKURA internet Inc. • id • product_id (Foreign Key) • language_code (“en-us”, ”ja-jp”, …) • 商品名 • 商品説明 • (などなど、言語毎に固有の情報) ProductLocalized
12. © SAKURA internet Inc. class Product(models.Model): ean_code = models.CharField( "EANコード", max_length=20, unique=True ) price = models.IntegerField("金額(JPY)") (参考) Djangoの場合
13. © SAKURA internet Inc. class ProductLocalized(models.Model): class Meta: unique_together = (("product", "language_code"), ) LANGUAGE_CODE_CHOICES = ( ("ja", "日本語"), ("en-us", "英語(US)"), ("fr", "フランス語"), ) product = models.ForeignKey("Product", on_delete=models.CASCADE) language_code = models.CharField( "言語コード", max_length=10, choices=LANGUAGE_CODE_CHOICES ) name = models.CharField("商品名", max_length=100) description = models.TextField("説明")
14. © SAKURA internet Inc. CREATE TABLE IF NOT EXISTS "product" ( "id" integer NOT NULL PRIMARY KEY AUTOINCREMENT, "ean_code" varchar(20) NOT NULL UNIQUE, "price" integer NOT NULL ); CREATE TABLE IF NOT EXISTS "productlocalized" ( "id" integer NOT NULL PRIMARY KEY AUTOINCREMENT, "language_code" varchar(10) NOT NULL, "name" varchar(100) NOT NULL, "description" text NOT NULL, "product_id" integer NOT NULL REFERENCES "product" ("id") DEFERRABLE INITIALLY DEFERRED );
15. © SAKURA internet Inc. def product_list(request): language_code = request.GET.get("lang", "ja") ret = [] for product in Product.objects.all(): try: localized = product.localized_set.get( language_code=language_code) except ProductLocalized.DoesNotExist: try: localized = product.localized_set.get( language_code="en-us") except ProductLocalized.DoesNotExist: localized = product.localized_set.get( language_code="ja") ret.append( OrderedDict(( ("id", product.id), ("ean_code", product.ean_code), ("price", product.price), ("language_code", localized.language_code), ("name", localized.name), ("description", localized.description), )) ) return JsonResponse({ "products": ret }) 商品⼀覧の取得 普通にやるとこうなる
16. © SAKURA internet Inc. • 1-N のリレーションなので、クエリに注意 • 適当にやるとN+1問題を⽣み出す 考慮すべき課題
17. © SAKURA internet Inc. def product_list(request): language_code = request.GET.get("lang", "ja") ret = [] for product in Product.objects.all(): try: localized = product.localized_set.get( language_code=language_code) except ProductLocalized.DoesNotExist: try: localized = product.localized_set.get( language_code="en-us") except ProductLocalized.DoesNotExist: localized = product.localized_set.get( language_code="ja") ret.append( OrderedDict(( ("id", product.id), ("ean_code", product.ean_code), ("price", product.price), ("language_code", localized.language_code), ("name", localized.name), ("description", localized.description), )) ) return JsonResponse({ "products": ret }) 商品⼀覧の取得 普通にやるとこうなる 商品の件数だけSQLのクエリが発⾏される
18. © SAKURA internet Inc. SELECT "product"."id", "product"."ean_code", "product"."price" FROM "product"; SELECT "productlocalized"."id", "productlocalized"."product_id", "productlocalized"."language_code", "productlocalized"."name", "productlocalized"."description" FROM "productlocalized" WHERE ("productlocalized"."product_id" = 1 AND "productlocalized"."language_code" = 'en-us'); SELECT "productlocalized"."id", "productlocalized"."product_id", "productlocalized"."language_code", "productlocalized"."name", "productlocalized"."description" FROM "productlocalized" WHERE ("productlocalized"."product_id" = 1 AND "productlocalized"."language_code" = 'en-us'); SELECT "productlocalized"."id", "productlocalized"."product_id", "productlocalized"."language_code", "productlocalized"."name", "productlocalized"."description" FROM "productlocalized" WHERE ("productlocalized"."product_id" = 1 AND "productlocalized"."language_code" = 'ja'); SELECT "productlocalized"."id", "productlocalized"."product_id", "productlocalized"."language_code", "productlocalized"."name", "productlocalized"."description" FROM "productlocalized" WHERE ("productlocalized"."product_id" = 2 AND "productlocalized"."language_code" = 'en-us'); SELECT "productlocalized"."id", "productlocalized"."product_id", "productlocalized"."language_code", "productlocalized"."name", "productlocalized"."description" FROM "productlocalized" WHERE ("productlocalized"."product_id" = 2 AND "productlocalized"."language_code" = 'en-us'); SELECT "productlocalized"."id", "productlocalized"."product_id", "productlocalized"."language_code", "productlocalized"."name", "productlocalized"."description" FROM "productlocalized" WHERE ("productlocalized"."product_id" = 2 AND "productlocalized"."language_code" = 'ja'); SELECT "productlocalized"."id", "productlocalized"."product_id", "productlocalized"."language_code", "productlocalized"."name", "productlocalized"."description" FROM "productlocalized" WHERE ("productlocalized"."product_id" = 3 AND "productlocalized"."language_code" = 'en-us'); SELECT "productlocalized"."id", "productlocalized"."product_id", "productlocalized"."language_code", "productlocalized"."name", "productlocalized"."description" FROM "productlocalized" WHERE ("productlocalized"."product_id" = 3 AND "productlocalized"."language_code" = 'en-us'); SELECT "productlocalized"."id", "productlocalized"."product_id", "productlocalized"."language_code", "productlocalized"."name", "productlocalized"."description" FROM "productlocalized" WHERE ("productlocalized"."product_id" = 3 AND "productlocalized"."language_code" = 'ja'); クエリ発⾏におけるN+1問題 クエリ発⾏数が商品件数に⽐例する ( o(n) )
19. © SAKURA internet Inc. N+1問題を解決する
20. © SAKURA internet Inc. • 必要なレコードをまとめて取得する • 副問合せ (JOIN等)する⽅法と、別に取得する⽅法とある • Djangoの場合 `select_related()` と `prefetch_related()` に相当 今回採⽤した対策
21. © SAKURA internet Inc. def prefetched_product_list(request): language_code = request.GET.get("lang", "ja") ret = [] queryset = Product.objects.all().prefetch_related( Prefetch("localized_set”, queryset=ProductLocalized.objects.filter( language_code__in=("en-us", "ja", language_code) ),to_attr="localized” )) for product in queryset: localized = None for l in (language_code, "en-us", "ja"): for candidate in product.localized: if candidate.language_code == l: localized = candidate break if localized: break ret.append( OrderedDict(( ("id", product.id), ("ean_code", product.ean_code), ("price", product.price), ("language_code", localized.language_code), ("name", localized.name), ("description", localized.description), )) ) return JsonResponse({ "products": ret }) Prefetchを使った改善 いくつかの⾔語コードについて⼀気に取得してしまう どの⾔語コードを使うかはロジックで決定する
22. © SAKURA internet Inc. SELECT "product"."id", "product"."ean_code", "product"."price" FROM "product”; SELECT "productlocalized"."id", "productlocalized"."product_id", "productlocalized"."language_code", "productlocalized"."name", "productlocalized"."description” FROM "productlocalized” WHERE ("productlocalized"."language_code" IN ('en-us', 'ja’) AND "productlocalized"."product_id" IN (1, 2, 3)); Prefetchによるクエリ発⾏ 商品件数によらず、⼀定のクエリ数 ( o(1) ) になる
23. © SAKURA internet Inc. メリット • SQL発⾏回数が O(1) になる デメリット • Prefetchのためのクエリ条件が⻑くなる • WHERE区でIDを列挙するため、少しパフォーマンスが悪い • フォールバック先の⾔語も取得する場合、クエリの結果が⼤きくなる • ⾔語決定のためCPUを使う 認識しておくべき特徴
24. © SAKURA internet Inc. まとめ
25. © SAKURA internet Inc. • フレームワークを使っているとN+1問題に気づきづらい • レコード数の少ない開発環境ではN+1問題に気づきづらい • N+1問題を知らない人も意外と多いのでは • フレームワークによってはORMで対処可能