Upgrade to Pro — share decks privately, control downloads, hide ads and more …

多言語情報をデータベースにどう持つか @さくらの夕べ Tech Night #3 On...

chibiegg
October 28, 2020

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

chibiegg

October 28, 2020
Tweet

More Decks by chibiegg

Other Decks in Programming

Transcript

  1. 2 【所属等】 さくらインターネット株式会社 新卒⼊社 執⾏役員 技術推進統括担当 兼 CISO IzumoBASE株式会社 取締役

    【開発】 • さくらのVPS API/DB/制御システム担当 • sakura.io ハードウエア仕様、ファームウエア開発 システム設計、開発、インフラ設計、構築 • 社内システム/データセンター⾃動化 • さくらのエンジニアリングラボ 【その他】 • CSAJ U22プログラミング・コンテスト審査員 • Home NOC Operatorsʼ Group (AS59105) @chibiegg
  2. 3 【経歴】 • ロボカップジュニア (中学・⾼校) / NHK⼤学ロボコン • ⼤阪⼤学⼯学部電⼦情報⼯学科情報通信⼯学専攻 •

    個⼈事業主 • ⼤阪⼤学⼤学院⼯学研究科中退 • SECCON CTF 2014 国内4位 • ISUCON5/ISUSON7/ISUCON8本戦、ISUCON9問題作成 • ICTSC7 ⼤⼈チーム 【専⾨】 • ソフトウエアエンジニア • NW/SC/DBスペシャリスト • 回路設計/組み込みソフトウエア 【趣味】 旅⾏/温泉/写真/電⼦⼯作/プログラミング/かわいい服 @chibiegg
  3. © SAKURA internet Inc. • gettextを使うのが一般的 1. コード中に文字列を書く ex: _(“Invalid

    input”) 2. ロケールファイル (*.po) を自動生成する 3. ロケールファイルを翻訳する 4. ロケールファイルをコンパイルする (*.mo) もしも、ただの定数なら 動的ではなく、コード中の⽂字列を国際化/地域化する場合
  4. © 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の場合
  5. © SAKURA internet Inc. • 基本情報を扱うテーブルを用意する (Product) • 言語別に持つテーブルを用意する (ProductLocalized)

    • Product 1 – N ProductLocalzied となるようにリレーショナルな扱いをする データベース構造を考える 例として商品情報を扱う
  6. © SAKURA internet Inc. • id • EANコード • 価格

    • (などなど、言語によらず共通の情報) Product
  7. © SAKURA internet Inc. • id • product_id (Foreign Key)

    • language_code (“en-us”, ”ja-jp”, …) • 商品名 • 商品説明 • (などなど、言語毎に固有の情報) ProductLocalized
  8. © SAKURA internet Inc. class Product(models.Model): ean_code = models.CharField( "EANコード",

    max_length=20, unique=True ) price = models.IntegerField("金額(JPY)") (参考) Djangoの場合
  9. © 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("説明")
  10. © 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 );
  11. © 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 }) 商品⼀覧の取得 普通にやるとこうなる
  12. © 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のクエリが発⾏される
  13. © 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) )
  14. © 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を使った改善 いくつかの⾔語コードについて⼀気に取得してしまう どの⾔語コードを使うかはロジックで決定する
  15. © 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) ) になる
  16. © SAKURA internet Inc. メリット • SQL発⾏回数が O(1) になる デメリット

    • Prefetchのためのクエリ条件が⻑くなる • WHERE区でIDを列挙するため、少しパフォーマンスが悪い • フォールバック先の⾔語も取得する場合、クエリの結果が⼤きくなる • ⾔語決定のためCPUを使う 認識しておくべき特徴