多言語情報をデータベースにどう持つか〜N+1問題と対策〜

この記事は2020年10月28日に行われたさくらの夕べ Tech Night #3 Onlineにおける発表を文章化したものです。

さくらインターネットの江草です。さくらインターネットの技術推進担当の執行役員とCISOをやっております。

この記事では、多言語情報をどう扱うかを一例に、N+1問題というものを紹介し、それにどう対応するかというところを説明したいと思います。今回はDjangoというPythonのフレームワークを例にしていますが、たぶん他のフレームワークでも応用できるだろうと思っています。

データベースを使わない場合
多言語で情報を動的に扱いたい
データベース構造を考える
商品一覧の取得とN+1問題
N+1問題を解決する
認識しておくべき特徴
まとめ

データベースを使わない場合

まず、多言語対応すべき情報が動的なものではなく、ただの定数である場合、つまりデータベースを使わずコード中の文字列を国際化/地域化する場合を考えます。

この場合はほぼ一択で、gettextを使うのが一般的かと思います。

gettextを使うと、ソースコード中のいろんな文字列を、いろんな言語に翻訳して渡すことができます。例えば、コード中に_("Invalidinput")みたいな文字列を書いておくと、それをいろんな言語に翻訳するための翻訳ファイル(*.po)を自動で生成し、その翻訳ファイルを修正し、コンパイルしてバイナリのメッセージカタログファイル (*.mo) にすることで、多言語の翻訳ができます。

参照：gettext - メッセージカタログ

例えばDjangoの場合は、ソースコード中に国際化したい文字列を書いておいて、makemessagesで日本語用の翻訳ファイルを作成し、自分で編集した後、コンパイルするというようなことが下記のコードで実現できます。

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 にコンパイル

多言語で情報を動的に扱いたい

ここからが本題で、情報を動的に扱いたい場合です。例えばECサイトを作っていて、商品情報などをデータベースに載せているとします。日本語のユーザーには日本語で商品名を出したいし、英語のユーザーには英語で商品名を出したいというような、先ほどのgettextで翻訳するという手段が使えない場合を考えてみます。

データベース構造を考える

例として商品情報を扱うデータベース構造を考えると、基本情報を持つテーブル(Product)を用意し、それとは別に、言語別に持たなければいけない情報を書くテーブル(ProductLocalized)を用意します。そして、1個のProductに対してN個のProductLocalizedをつなぐというようにリレーショナルな扱いをすると、データを持つことができます。

ここで、Productテーブルには、id、EANコード、価格など、言語によらず共通の情報を持ちます。一方でProductLocalizedテーブルには、どのプロダクトの情報かというproduct_idをForeign Keyで持っていて、"en-us"や"ja-jp"など言語が何であるかという情報に加えて、商品名や商品説明など、言語ごとに固有の情報をテーブルとして持つのが、もっとも安直に設計できるテーブルかと思います。

Product	ProductLocalized
id EANコード価格 (などなど、言語によらず共通の情報)	id product_id (Foreign Key) language_code (“en-us”, ”ja-jp”, …) 商品名商品説明 (などなど、言語によらず共通の情報)

これを例えばDjangoのフレームワークでModelとしてテーブルの定義をすると、下図のような感じになります。Productはean_codeとpriceを持っていて、ProductLocalizedには、productに対するForeignKeyとlanguage_codeと、nameやdescriptionなどを持つという形です。とある商品について日本語と英語とフランス語の情報を持ちたければ、3行のレコードに分かれる形で実装できるかなと思います。

class Product(models.Model):
    ean_code = models.CharField(
      "EANコード", max_length=20, unique=True
    )
    price = models.IntegerField("金額(JPY)")

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("説明")

SQLにするとこんな感じでテーブルができます。これはSQLiteの場合の例です。

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
);

商品一覧の取得とN+1問題

ここで問題になるのは、商品一覧を取得する場合です。普通にやると、Productを全件取ってきて、各言語の情報を取得し、JSONにして返すという流れになります。

しかし、これは1-Nのリレーションなので、送出されるクエリの量に注意が必要です。適当にやると「N+1問題」と呼ばれる問題を生み出します。

上図の赤枠で囲んだ部分が問題の処理です。商品を取ってきてforで回し、1件ずつ指定された言語の情報を取ってくるというコードがありますが、これをやると、発行されるSQLが下図のような感じになります。

1件目でProduct全件を取ってきて、言語ごとの情報をほとんどSELECTするのですが、これをしてしまうと、例えば100件応答するAPIだとクエリが100回以上発行されるわけです。このような処理を「O(n)」 (オーダーnと読む) と言いますが、商品件数に比例して、だんだん処理が遅くなるという問題があります。

このクエリも1件発行するのにほぼ時間はかかりませんが、100回発行するとさすがに「ちりも積もれば山」となり、「なんだかとても商品の表示が遅いんですけど」みたいな問題を生んでしまいます。こういう問題をN+1問題と呼びます。

N+1問題を解決する

N+1問題を解決するための方法としてよく使われるのは、例えば100件分をまとめて取得するというような方法です。SQLでは、JOINなどを用いて副問合せをすれば簡単に異なるテーブルの情報をまとめて取得したり、条件にマッチするレコードを一気に取ってきたりすることで解決します。Djangoの場合は、select_related() メソッドや、prefetch_related() メソッドを利用することがそれぞれの解決方法に相当します。

下図が改善例です。簡単に説明すると、商品一覧を取ってくるときに、Prefetchという機能を使って、その商品に関連する言語情報を条件に従って一緒にまとめて持ってきてくださいというようなコードを書きます。

ここでは念のため、英語と日本語と、指定された言語の3つをまとめて持ってきてくださいというようなことを書いています。言語情報を取ってきたら、指定された言語があればそれを使う、なければ英語を試してみる、なければ日本語を試してみるというように、どの言語を使うかはPythonのコード中で決定します。出力されるJSONは前の例と同じになっています。

このようなコードを書いて、実際にSQLを見ると、2件のSQLに変わります。1つ目は以前と変わらず商品一覧を全部取ってきます。その全件取ってきたproductに対して、2つ目のSQLで必要な言語情報を持ってきます。

ここではlanguage_codeは"en-us"か"ja"、かつproduct_idが1,2,3のいずれかであるものを全件取ってきてくださいというように、コードは長くなっていますが一発で情報が取れるようなSQLが発行されます。こうすると、商品件数によらず一定のクエリ数で取得することができて、10件だろうが100件だろうが大きなパフォーマンス低下にはならないということが実現できます。このように、件数によらずなんらかの処理量 (今回で言うとクエリ数) が一定な実装を「O(1)」 (オーダー1と読む) と言います

認識しておくべき特徴

こういった実装方法で認識しておくべき特徴があります。まずメリットとしてはSQLの発行回数が O(1) になります。つまり、件数に関わらず同じ回数のSQLが発行されます。これがとても大事なポイントで、体感速度にすごく影響します。

一方、デメリット、のちのち何かの問題が発生するかもしれない、あるいはこれが代わりに払った代償と理解しておくべきところですが、Prefetchのためのクエリ条件が長くなります。

先ほどの例にあった"product_id" IN(1,2,3) のように、全件取ってくるためにクエリが長くなります。WHERE区でIDを列挙しているため、指定されたIDのデータを全部取ってくるという、データベースもあまり得意ではない、少しパフォーマンスが悪い処理をさせているということを理解しておく必要があります。

例えば、先ほどあったようにフォールバック先の言語も取得する場合、つまりフランス語を指定された場合もフランス語がない場合には日本語か英語を使うというようなロジックで書いていた場合、本当はフランス語だけでいいのに、英語と日本語も取っているみたいな感じでクエリの結果が大きくなっているというところもあります。

最終的にどの言語を使うかはCPUを使った処理によって選んでいるため、メモリやCPUを使うというところがデメリットとして挙げられます。でも結果としてはパフォーマンスはかなり上がりますので、やらない手はないかなと思います。

まとめ

今回は多言語情報を持つデータベースを例に、N+1問題の解説をしました。

フレームワークを使っていると、簡単にSQLが発行されて、しかもどこでSQLが発行されているかがコード上からわかりにくいので、そもそもN+1問題が発生していることに気づきづらいというのがあります。レコード数の少ない開発環境では速く動いてしまうので、N+1問題に気づきづらいです。もしかすると、あまりSQLを書かずにコードを書いている人が最近多いかなと思うので、N+1問題を知らない人も意外に多いのではないかと思います。

ですので、自分が作ったアプリケーションがだんだん重くなってきたとか、使ってる量が少ない人は軽いんだけど、いっぱい使ってる人は重いみたいな話を聞いたら、もしかしたらN+1問題が起きているのではないかと考えて、SQLを調査してみてください。フレームワークによってはORM(オブジェクト関係マッピング)を使うことで、SQLを書かなくても、今回のように対処できることもあります。

今回ご紹介したのは明日すぐ使えるテクニックではありませんが、何かデータベースアプリケーションを作っていて、だんだん重くなってきたみたいなときは、こういったことをちょっと気にしてもらったらいいことがあるかも、という話でした。ありがとうございました。