Zenn
👻

JSON型データのクエリ効率化にGenerated Columnsが使える

2023/06/30に公開
MySQL
Rails
Ruby
Rails
SQL
tech

Generated Columnsとは

MySQLの公式ドキュメントはこちらです。
MySQL :: MySQL 5.7 Reference Manual :: 13.1.18.7 CREATE TABLE and Generated Columns

直訳すると "生成されたカラム" ですが、その名の通りです。任意の定義式から新しい値を生成して、新しいカラムのように値を扱うことができる機能です。

値の保存方法は STORED と VIRTUAL の2種類から選べます。MySQL公式の説明はこちらです。

VIRTUAL: Column values are not stored, but are evaluated when rows are read, immediately after any BEFORE triggers. A virtual column takes no storage.
InnoDB supports secondary indexes on virtual columns. See Section 13.1.18.8, “Secondary Indexes and Generated Columns”.

STORED: Column values are evaluated and stored when rows are inserted or updated. A stored column does require storage space and can be indexed.

  • VIRTUAL
    • カラムの値は保存されませんが、行が読み込まれるときに値が評価されます (BEFOREトリガーが定義されている場合は、そのトリガー直後に評価されます)
    • ストレージ容量を使いません
    • セカンダリインデックスを追加できます (PRIMARY KEYなどクラスタインデックスは追加不可)
  • STORED
    • 挿入またはアップデート時に、カラムの値が評価されて保存されます
    • ストレージ容量を必要とします
    • インデックスを追加できます (PRIMARY KEYなどクラスタインデックスも追加可能)

JSON型データにGenerated Columnsをどう使えるか

テーブル設計時に、ひとまずデータ型をJSON型で定義してみたものの、

  • 後で思ったよりJSONの中身をクエリで取り出して使う機会が多く、Slow Queryの温床になってしまっている
  • 今から新規Columnを追加しても良いけれど、既存行のデータ修正が面倒くさい

という場面があると思います。そのようなときにGenerated Columnsの利用を検討できます。

  1. 既存のJSON型Columnから、中身を取り出す新規Generated Columnsを定義する
  2. 生成されたGenerated Columnsに適切なインデックスを貼る

とすると、クエリの効率化や高速化を狙うことができます。

JSON型Columnを元にGenerated Columnsを定義する

例えばテーブル users に、次の構造を持つJSON型のColumn data があるとしましょう。

data column
{
  "first_name": "John",
  "last_name": "Smith",
  "age": 27,
  "address": {
    "city": "New York",
    "postalCode": "10021-3100"
  }
}

年齢 age や都市 city でデータを抽出したい！というときに、抽出条件となるJSON内データをGenerated Columnsとしてそれぞれ定義して、インデックスを貼ることが可能です。

"data->>'$.age'" と "data->>'$.address.city'" をGenerated Columnsとして定義したテーブルを、Sequel Aceで見るとContentはこのようになります。

Alt text

Structureはこのようになっています。

Alt text

例えば年齢と都市を条件にユーザーを抽出したい、という次のクエリがあったとします。

SELECT * FROM users WHERE age < 30 AND city = "New York"

インデックスを用いてクエリ実行するために、次のように (age, city) の複合インデックスを追加することができます。

CREATE INDEX index_on_users_age_and_city ON users (age, city)

JSON型のままだとインデックスは貼れなかったので、クエリの効率化/高速化を狙えます。 (実際には EXPLAIN で意図したインデックスが使われているか、などを見てパフォーマンスチューニングを行うことになります)

Ruby on RailsでGenerated Columnsを使う

このセクションでは、Ruby on RailsにおいてGenerated Columnsを利用する方法を記述します。
いずれも前セクションと同様の、テーブル users にあるJSON型のColumn data を例とします。

Active Recordマイグレーション

マイグレーションファイル内で、as: 定義式 によりGenerated Columnsを容易に定義できます。

また stored オプションで値の保存方法を指定できます。 stored: true を指定すると STORED となり、何も指定しない場合のデフォルト値は stored: false つまり VIRTUAL です。

テーブル新規作成時にGenerated Columnsを定義する場合

マイグレーションファイルは以下のように書けます。値の保存方法はage が VIRTUAL、city が STORED となります。

migration_file.rb
class CreateUsers < ActiveRecord::Migration[6.1]
  def change
    create_table :users do |t|
      t.json "data"

      t.unsigned_integer :age, as: "data->>'$.age'"
      t.string :city, as: "data->>'$.address.city'", stored: true
    end
  end
end

rails db:migrate をコマンド実行することで発行されるSQLはこちらです。

CREATE TABLE `users` (
  `id` bigint NOT NULL AUTO_INCREMENT PRIMARY KEY,
  `data` json,
  `age` int unsigned AS (data->>'$.age'),
  `city` varchar(255) AS (data->>'$.address.city') STORED
);

既存テーブルにGenerated Columnsを追加する場合

マイグレーションファイルは以下のように書けます。値の保存方法はage が VIRTUAL、city が STORED となります。

migration_file.rb
class AddGeneratedColumnsToUsers < ActiveRecord::Migration[6.1]
  def change
    add_column :users, :age, :unsigned_integer, as: "data->>'$.age'"
    add_column :users, :city, :string, as: "data->>'$.address.city'", stored: true, after: :age
  end
end

rails db:migrate をコマンド実行することで発行されるSQLはこちらです。

ALTER TABLE `users` ADD `age` int unsigned AS (data->>'$.age');
ALTER TABLE `users` ADD `city` varchar(255) AS (data->>'$.address.city') STORED AFTER `age`;

Generated Columnsの値取得

Active RecordモデルのGenerated Columnsは、通常のColumnと同様に値取得が . でできます。

JSON型Column内から値を取得する場合は次のように書きますが、

User.first.data["address"]["city"]
=> "New York"

Generated Columnsであれば次のように書けます。簡潔にコーディングできて良いですね！

User.first.city
=> "New York"

Discussion