id リストの表現を工夫して INSERT 文のサイズを大幅に小さくした話

はじめに

みなさまごきげんよう。マネーフォワードの福岡拠点でクラウド経費およびクラウド債務支払というプロダクトの開発を担当している、野田 (@quanon_jp) と申します。好きなものは村上春樹とヨーヨーです 🪀

今回は SQL に関する少し風変わりな改善について共有します。特定の形式のカラムのサイズが非常に大きくなっていたので、そのサイズを小さくした話です。

なお、本記事のコードはすべて Ruby (Ruby on Rails の環境) です。

経緯

きっかけ

それは晴天の霹靂でした。他のプロダクト (以下 🍊) の開発者から我々のプロダクト (以下 🍇) にある連絡が来たのです。

※ 文章はイメージです。テーブル名、カラム名は実際のものから一部変更してます。

🍊 で使っている共通データベースのリードレプリカが遅延し、パフォーマンス悪化やエラーレートの上昇、コスト増などが発生しています。調査の結果、🍇 の大きな INSERT に遅延の原因がありそうなためご相談です。 今疑っているのは INSERT INTO user_teams (user_id, team_ids, created_at, updated_at) values (...); という SQL です。 この SQL は非常に長く、INSERT 文の文字列のサイズが 10MB を超えている場合があります。さらに、この SQL が 1 つのトランザクションで 10 回以上発行されるといった状況です。つまり、1 回のトランザクションのテキストサイズだけで 100MB を超える事態が発生しています。 リードレプリカの遅延を避けるために、このような SQL が発行されないように対策をお願いします。

つまり「複数のプロダクトで共通で使用しているデータベースで、 🍇 で非常にサイズの大きな SQL を発行していた。その結果、他のプロダクトに迷惑を掛けてしまった」という状況でした。

テーブルについて

🍇 には「チーム」という概念があります。user_teams テーブルは次のような「あるユーザにとって有効なチーム」を表す関連テーブルです。user_id ごとに team_id のリストを持つという構造です。

id	user_id	team_ids
1	1	1,2
1	2	2,3,5

なお、以前は次のように一般的な関連テーブルとして user_id と team_id の組み合わせを 1 : 1 で保持していました。

id	user_id	team_id
1	1	1
2	1	2
3	2	2
4	2	3
5	2	5

しかし、この方法では user や team が多い場合に user_teams テーブルのレコード件数が爆発的に増えてしまうという問題がありました。例えば「1 万件の user がそれぞれ 100 万件の team に属する」という場合、user_teams テーブルのレコードは 1 万 x 100 万 = 100 億件になってしまいます。

そのため、user ごとに team の id リストを持つ構造に変更 (非正規化) しました。これは理想的な構造ではありませんが、要件とパフォーマンスを考慮した上で採用しました。

問題点

team_id リストを持つ現在の構造では、id の数が非常に多い場合に team_ids 列のサイズが大きくなりすぎてしまう問題があります。例えば 1,000,000 から 1,999,999 までの整数の配列を , で区切った文字列のサイズは 7.63 MB にも達します。

ids_str = (1_000_000..1_999_999).map(&:to_s).join(',')
ids_str.first(64)
#=> "1000000,1000001,1000002,1000003,1000004,1000005,1000006,1000007,"
ids_str.bytesize.then { |bytesize| helper.number_to_human_size(bytesize, locale: :en) }
#=> "7.63 MB"

こうなるともちろん、id の数が非常に多い場合に SQL の INSERT 文のサイズも非常に大きくなってしまいます 😢 なんとかして id リストをサイズを小さくしたいです。なお team はまとめて大量に作成するケースがほとんどのため id リストは連番の部分が多いとします。

解決策

サンプルとして連番の部分が多い id リストを用意します。

# 1,000,000 から 1,999,999 までの整数の配列に少し歯抜けを含める。
ids = [*1_000_000..1_999_999].sample(999_996).sort
ids.size #=> 999996
ids # 1000000..1241646, 1241648..1460481, 1460483..1598364, 1598366..1999584, 1999586..1999999 という内容。

これを , で区切った文字列にすると 7.63 MB です。そして、単に gzip 圧縮しても 2.68 MB です。

ids_str = ids.map(&:to_s).join(',')
ids_str.bytesize.then { helper.number_to_human_size(_1, locale: :en) }
#=> "7.63 MB"
ids_str.then { ActiveSupport::Gzip.compress(_1) }.then { Base64.strict_encode64(_1) }.bytesize.then { helper.number_to_human_size(_1, locale: :en) }
#=> "2.68 MB"

ここで

なお team はまとめて大量に作成するケースがほとんどのため id リストは連番の部分が多いとします。

という前提をもとに「1,2,3,5,7,8,9を 1-3,5,7-9 のように範囲表現にする」とサイズを小さくできると考えました。そして以下の変換処理を実装しました。

# 整数の配列を範囲の表現を使った文字列に変換する。
# id リストの文字列のサイズを縮小するのが目的。
# e.g.
# NumbersEncoder.encode([1, 2, 3, 4, 6, 7, 9, 10, 11, 14, 15, 12])
# #=> "1-4,6,7,9-12,14,15"
# NumbersEncoder.decode('1-4,6,7,9-12,14,15')
# #=> [1, 2, 3, 4, 6, 7, 9, 10, 11, 12, 14, 15]
module NumbersEncoder
  def self.encode(numbers)
    Encode.call(numbers)
  end

  def self.decode(str)
    Decode.call(str)
  end

  class Encode
    def self.call(numbers)
      new(numbers).send(:call)
    end

    def initialize(numbers)
      unless numbers.all? { _1.is_a?(Integer) && _1.positive? }
        raise ArgumentError.new('must be array of unsigned integer')
      end

      @numbers = numbers.sort
    end

    private

    def call
      return '' if @numbers.empty?

      ranges = grouped_numbers.map do |numbers|
        n, *, m = numbers

        case numbers.size
        when 1
          "#{n}"
        when 2
          "#{n},#{m}"
        else # 3..
          "#{n}-#{m}"
        end
      end

      ranges.join(',')
    end

    private

    def grouped_numbers
      # 要素数が 1 の場合は @numbers.each_cons(2) が [] になってしまう。
      return [[@numbers.first]] if @numbers.size == 1

      @numbers.each_cons(2).each_with_object([]) do |(m, n), ranges|
        ranges << [m] if ranges.empty?

        if m + 1 == n
          ranges.last << n
        else
          ranges << [n]
        end
      end
    end
  end

  class Decode
    class DecodeError < StandardError; end

    def self.call(str)
      new(str).send(:call)
    end

    def initialize(str)
      raise ArgumentError.new('invalid format') unless valid_format?(str)

      @str = str
    end

    private

    def call
      return [] if @str.empty?

      ranges.flat_map do |range|
        matched = range.match(/\A(\d+)-(\d+)\z/)

        if matched
          m, n = matched.captures.map(&:to_i)

          raise(DecodeError.new('invalid range')) unless m < n

          Array(m..n)
        else
          range.to_i
        end
      end
    end

    private

    def valid_format?(str)
      return true if str.empty?
      # 正しいフォーマットとは
      # (1) 整数で始まっていること。
      # (2) 整数で終わっていること。
      # (3) n-m or n or 連続しないカンマの繰り返しであること。
      return false unless str.match?(/\A\d/)
      return false unless str.match?(/\d\z/)
      return false unless str.match?(/\A(\d+-\d+|\d|,(?!,))*\z/)

      true
    end

    def ranges
      @str.split(',')
    end
  end
end

この NumbersEncoder.encode で id リストを範囲表現にすることで、文字列のサイズを 7.63 MB から 79 Bytes へと大幅に小さくできました。

ids_str = NumbersEncoder.encode(ids)
#=> "1000000-1241646,1241648-1460481,1460483-1598364,1598366-1999584,1999586-1999999"
ids_str.bytesize.then { helper.number_to_human_size(_1, locale: :en) }
#=> "79 Bytes"

また NumbersEncoder.decode は従来のカンマ区切りと互換性があり、カンマ区切りでも範囲表現でももとの id 配列に戻すことができます。

# 範囲表現の文字列をもとの id 配列に戻す。
ids_str = NumbersEncoder.encode(ids)
ids == NumbersEncoder.decode(ids_str)
#=> true

# カンマ区切りの文字列ももとの id 配列に戻せる。
ids_str = ids.map(&:to_s).join(',')
ids == NumbersEncoder.decode(ids_str)
#=> true

当初は大掛かりな改修が必要かもとドキドキしました。しかし、この NumbersEncoder を使用する方針により、報告から 1 週間程度で改善をリリースできました 🚀

まとめ

• 今回は id リストの表現を工夫することで、文字列のサイズを大幅に小さくすることができた。
  • id の採番の仕様上、連番が多いという性質を踏まえて範囲表現を用いることで実現できた。
  • 改修前と互換性がある。

ちょっとした工夫で問題を解決できるのは楽しいですね 😉

---

マネーフォワード福岡拠点ではエンジニアを募集しています！ https://hrmos.co/pages/moneyforward/jobs?category=1666323298559537153

福岡開発拠点のサイトもありますので、ぜひご覧ください！ https://fukuoka.moneyforward.com/

---

マネーフォワードでは、エンジニアを募集しています。 ご応募お待ちしています。

【会社情報】 ■Wantedly ■株式会社マネーフォワード ■福岡開発拠点 ■関西開発拠点（大阪/京都）

【SNS】 ■マネーフォワード公式note ■Twitter - 【公式】マネーフォワード ■Twitter - Money Forward Developers ■connpass - マネーフォワード ■YouTube - Money Forward Developers