MySQLのGenerated Columnsを活用する

はじめに

MySQLにはGenerated Columnsという便利な機能があります。今回はこちらについて、概要と基本的な使い方を解説します。

アプリケーションから使う場合の例はRailsとLaravelで示しています。

Generated Columnsとは

dev.mysql.com

ドキュメントの日本語訳が微妙だったので英語版を貼りますが、MySQL公式ドキュメントに

Values of a generated column are computed from an expression included in the column definition.

とあるように、Generated Columnsはカラム定義に他のカラムの値を含む式を使える、というものです。

余談ですが公式の使用例は私にはあまりいい例に思えませんね……。

使える式の制約

主なものを公式ドキュメントから抜粋します。例によって機械翻訳が微妙なので拙訳です。

  • リテラル決定論的組み込み関数、演算子を使用できます。
  • ストアドファンクションやユーザー定義関数は使用できません。
  • ストアドプロシージャ、ストアドファンクションのパラメータは使用できません。
  • 変数は使用できません。
  • サブクエリは使用できません。
  • 他のGenerated Columnsの値は、テーブル定義上で自身より先に登場するもののみ使用できます。Base Columns(=Generatedでないカラム)は定義順に関わらず参照できます。
  • AUTO_INCREMENTは使用できません。
  • AUTO_INCREMENTが設定されているカラムは参照できません。

決定論的組み込み関数は、同じデータに対してユーザーやコネクションに依らず同じ結果を返すものを指します。冪等性が必要ということですね。つまり NOW() などは使えません。

VIRTUAL と STORED

Generated ColumnsにはVIRTUALとSTOREDの2つのモードがあります(デフォルトはVIRTUAL)。

VIRTUAL

カラムの値は保存されず、レコードが読み込まれるときに値が計算されます。保存されないので当然このカラムはストレージを消費しません。

secondary index(つまりprimary key以外のindex)を張ることもできます。VIRTUALなGenerated Columnにsecondary indexが張られると、indexには計算結果が保存されます(insertやupdateで再計算されます)。indexが張られている場合、再計算のオーバーヘッドを考慮しても、VIRTUALの方が後述のSTOREDよりもreadのパフォーマンスがよくなることがあるようです。

STORED

カラムの値はinsertやupdateのときに計算され、保存されます。当然ストレージを消費します。

indexを張ることができます(primary keyも使える)。

ユースケース

Generated Columnを使えば

  • 複雑な条件をGenerated Columnに落とし込むことで、クエリを簡略化できる
    • indexを張れるので、当然パフォーマンスもよくなる
  • JSON型のカラムの中身の一部にindexを張れる

といったことが可能です。

たとえば私が実際にGenerated Columnsを採用したのは「日付のカラムがあるテーブルで、月と日だけで検索したい」というケースです。

Generated Columnsを使わない場合、クエリは以下のようになります。

select * from events where month(starts_at) = 4 and dayofmonth(starts_at) = 20;

これは当然フルテーブルスキャンになりますね。

一方、 month(starts_at)dayofmonth(starts_at) をGenerated Columnsとして定義しておけば、クエリは

select * from events where start_month = 4 and start_day = 20;

のようにシンプルになり、indexも張れるのでパフォーマンスも向上します。

定義の方法

書き方の比較のため2つGenerated Columnを作って一方をVIRTUAL、もう一方をSTOREDにします。

SQL

デフォルトはVIRTUALになるので、VIRTUALのときは省略できます。

CREATE TABLE `events` (
  `id` bigint(200) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  `starts_at` datetime NOT NULL,
  `start_month` int(10) unsigned AS (MONTH(`starts_at`)),
  `start_day` int(10) unsigned AS (DAYOFMONTH(`starts_at`)) STORED,
  `created_at` datetime,
  `updated_at` datetime,
  PRIMARY KEY (`id`),
  KEY `start_month_day_index` (`start_month`, `start_day`)
);

Ruby on Rails

こちらもデフォルトは stored: false なので省略可能です。

class CreateEvents < ActiveRecord::Migration[6.1]
  def change
    create_table :events do |t|
      t.string :name, null: false
      t.datetime :starts_at, null: false
      t.virtual :start_month, type: :integer, as: "MONTH(starts_at)"
      t.virtual :start_day, type: :integer, as: "DAYOFMONTH(starts_at)", stored: true

      t.index [:start_month, :start_day]

      t.timestamps
    end
  end
end

Laravel

こちらは VIRTUAL と STORED でメソッドが違います

<?php

use Illuminate\Database\Migrations\Migration;
use Illuminate\Database\Schema\Blueprint;
use Illuminate\Support\Facades\Schema;

class CreateEventsTable extends Migration
{
    public function up()
    {
        Schema::create('events', function (Blueprint $table) {
            $table->id();
            $table->string('name');
            $table->dateTime('starts_at');
            $table->Integer('start_month')->virtualAs('MONTH(starts_at)');
            $table->integer('start_day')->storedAs('DAYOFMONTH(starts_at)');
            $table->dateTime('created_at')->nullable();
            $table->dateTime('updated_at')->nullable();
            $table->index(['start_month', 'start_day']);
        });
    }

    public function down()
    {
        Schema::dropIfExists('events');
    }
}

実験

上に例示したスキーマでテーブルを作成し、実際に使ってみます。

まずはinsert。

mysql> insert into events (name, starts_at, created_at, updated_at) values ('foo', '2022-04-21 12:00:00', now(), now());
Query OK, 1 row affected (0.03 sec)

mysql> select * from events;
+----+------+---------------------+-------------+-----------+---------------------+---------------------+
| id | name | starts_at           | start_month | start_day | created_at          | updated_at          |
+----+------+---------------------+-------------+-----------+---------------------+---------------------+
|  1 | foo  | 2022-04-21 12:00:00 |           4 |        21 | 2022-04-20 18:42:20 | 2022-04-20 18:42:20 |
+----+------+---------------------+-------------+-----------+---------------------+---------------------+
1 row in set (0.01 sec)

starts_atからstart_monthとstart_dayが計算されていることがわかります。

次はこれをupdateしてみます。

mysql> update events set starts_at = '2022-05-22 12:00:00', updated_at = now() where id = 1;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from events;
+----+------+---------------------+-------------+-----------+---------------------+---------------------+
| id | name | starts_at           | start_month | start_day | created_at          | updated_at          |
+----+------+---------------------+-------------+-----------+---------------------+---------------------+
|  1 | foo  | 2022-05-22 12:00:00 |           5 |        22 | 2022-04-20 18:42:20 | 2022-04-20 18:50:17 |
+----+------+---------------------+-------------+-----------+---------------------+---------------------+
1 row in set (0.01 sec)

ちゃんと更新されていますね。

次にGenerated Columnに値をセットするクエリを実行してみます。

mysql> insert into events (name, starts_at, start_month, created_at, updated_at) values ('foo', '2022-04-21 12:00:00', 6, now(), now());
ERROR 3105 (HY000): The value specified for generated column 'start_month' in table 'events' is not allowed.
mysql> update events set start_month = 6 where id = 1;
ERROR 3105 (HY000): The value specified for generated column 'start_month' in table 'events' is not allowed.

insert、updateともに無事エラーになりました。

最後にGenerated Columnで検索するときのパフォーマンスを確認してみます。

mysql> explain select * from events where start_month = 5 and start_day = 22;
+----+-------------+--------+------------+------+-----------------------+-----------------------+---------+-------------+------+----------+-------+
| id | select_type | table  | partitions | type | possible_keys         | key                   | key_len | ref         | rows | filtered | Extra |
+----+-------------+--------+------------+------+-----------------------+-----------------------+---------+-------------+------+----------+-------+
|  1 | SIMPLE      | events | NULL       | ref  | start_month_day_index | start_month_day_index | 10      | const,const |    1 |   100.00 | NULL  |
+----+-------------+--------+------------+------+-----------------------+-----------------------+---------+-------------+------+----------+-------+
1 row in set, 1 warning (0.01 sec)

indexが使われていることがわかります。

ちなみに、Generated Columnsが定義されている状態であれば、元の式で検索した場合でもMySQLがよしなにGenerated Columnsのindexで検索してくれます。

mysql> explain select * from events where month(starts_at) = 5 and dayofmonth(starts_at) = 22;
+----+-------------+--------+------------+------+-----------------------+-----------------------+---------+-------------+------+----------+-------+
| id | select_type | table  | partitions | type | possible_keys         | key                   | key_len | ref         | rows | filtered | Extra |
+----+-------------+--------+------------+------+-----------------------+-----------------------+---------+-------------+------+----------+-------+
|  1 | SIMPLE      | events | NULL       | ref  | start_month_day_index | start_month_day_index | 10      | const,const |    1 |   100.00 | NULL  |
+----+-------------+--------+------------+------+-----------------------+-----------------------+---------+-------------+------+----------+-------+
1 row in set, 1 warning (0.01 sec)

Generated Columnを削除してから同じクエリを確認してみると、もちろん下のようにALLで検索されます。

mysql> explain select * from events where month(starts_at) = 5 and dayofmonth(starts_at) = 22;
+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table  | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | events | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    1 |   100.00 | Using where |
+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.01 sec)

使用上の注意

上にmigrationのコードを示したとおり、RailsやLaravelなどのフレームワークでもGenerated Columnsを定義することはできるのですが、ActiveRecordやEloquent Modelのオブジェクトでは特に読み取り専用になっていたりはしませんし、当然値をセットしただけで再計算されたりもしません。

そのため、気をつけて使わないと以下のような問題が起こります。

不正なクエリの発行をフレームワークのレイヤーで抑止できない

Generated Columnに対して直接insertやupdateのクエリを発行するのをフレームワークレベルで抑止することはできません(DBがエラーを吐きます)

Rails

Rails内部でGenerated Columnと通常のカラムを区別していないので、 event.start_month = 5 の時点ではエラーが出ず、save時にMySQLがエラーを吐きます。

# rails c
Running via Spring preloader in process 92
Loading development environment (Rails 6.1.4.6)
irb(main):001:0>
irb(main):002:0> event = Event.new
=> #<Event id: nil, name: nil, starts_at: nil, start_month: nil, start_day: nil, created_at: nil, updated_at: nil>
irb(main):003:0> event.name = "foo"
=> "foo"
irb(main):004:0> event.starts_at = Time.parse("2022-05-22 12:00:00")
=> 2022-05-22 12:00:00 +0000
irb(main):005:0> event.start_month = 5
=> 5
irb(main):006:0> event.save
  TRANSACTION (0.5ms)  BEGIN
  Event Create (1.3ms)  INSERT INTO `events` (`name`, `starts_at`, `start_month`, `created_at`, `updated_at`) VALUES ('foo', '2022-05-22 12:00:00', 5, '2022-04-20 10:42:13.035857', '2022-04-20 10:42:13.035857')
  TRANSACTION (0.5ms)  ROLLBACK
Traceback (most recent call last):
        1: from (irb):6
ActiveRecord::StatementInvalid (Mysql2::Error: The value specified for generated column 'start_month' in table 'events' is not allowed.)

Laravel

※ Laravelの場合、この問題はGenerated Column以外でも普通に発生します。

# php artisan tinker
Psy Shell v0.11.2 (PHP 8.1.4 — cli) by Justin Hileman
>>> $event = new App\Models\Event();
=> App\Models\Event {#4796}
>>> $event->name = 'foo';
=> "foo"
>>> $event->starts_at = '2022-05-22 12:00:00';
=> "2022-05-22 12:00:00"
>>> $event->start_month = 5;
=> 5
>>> $event->save();
Illuminate\Database\QueryException with message 'SQLSTATE[HY000]: General error: 3105 The value specified for generated column 'start_month' in table 'events' is not allowed. (SQL: insert into `events` (`name`, `starts_at`, `start_month`, `updated_at`, `created_at`) values (foo, 2022-05-22 12:00:00, 5, 2022-04-20 19:10:13, 2022-04-20 19:10:13))'

アプリケーションでは自動計算されない

レコードを更新した後、再度selectしない限りGenerated Columnの更新はインスタンスには反映されないので、アプリケーションロジックでGenerated Columnを参照すると更新前の値を参照してしまう可能性があります。

Rails

# rails c
Running via Spring preloader in process 92
Loading development environment (Rails 6.1.4.6)
irb(main):001:0> event = Event.find(1)
=> #<Event id: 1, name: "foo", starts_at: "2022-05-22 21:00:00.000000000 +0900", start_month: 5, start_day: 22, created_at: "2022-04-20 19:44:58.337180000 +0900", updated_at: "2022-04-20 19:44:58.337180000 +0900">
irb(main):002:0> event.starts_at
=> Sun, 22 May 2022 21:00:00.000000000 JST +09:00
irb(main):003:0> event.starts_at = Time.parse("2022-04-23 13:00:00")
=> 2022-04-23 13:00:00 +0000
irb(main):004:0> event.save               # この時点でDBのstart_monthは4に更新されている
=> true
irb(main):005:0> event.start_month        # ActiveRecordインスタンスではstart_monthは5のまま
=> 5
irb(main):006:0> event.starts_at          # もちろん、starts_atは更新されている
=> Sat, 23 Apr 2022 22:00:00.000000000 JST +09:00
irb(main):007:0> event.reload
=> #<Event id: 1, name: "foo", starts_at: "2022-04-23 22:00:00.000000000 +0900", start_month: 4, start_day: 23, created_at: "2022-04-20 19:44:58.337180000 +0900", updated_at: "2022-04-20 19:45:49.199031000 +0900">
irb(main):008:0> event.start_month        # reloadするとModelインスタンスも更新される
=> 4
irb(main):009:0> event.starts_at
=> Sat, 23 Apr 2022 22:00:00.000000000 JST +09:00

Laravel

# php artisan tinker
Psy Shell v0.11.2 (PHP 8.1.4 — cli) by Justin Hileman
>>> $event = App\Models\Event::find(1);
=> App\Models\Event {#4810
     id: 1,
     name: "foo",
     starts_at: "2022-05-22 12:00:00",
     start_month: 5,
     start_day: 22,
     created_at: "2022-04-20 19:18:16",
     updated_at: "2022-04-20 19:18:16",
   }
>>> $event->starts_at = "2022-04-23 13:00:00"
=> "2022-04-23 13:00:00"
>>> $event->save();             # この時点でDBのstart_monthは4に更新されている
=> true
>>> $event->start_month;        # Modelインスタンスではstart_monthは5のまま
=> 5
>>> $event->starts_at;          # もちろん、starts_atは更新されている
=> "2022-04-23 13:00:00"
>>> $event->refresh();
=> App\Models\Event {#4810
     id: 1,
     name: "foo",
     starts_at: "2022-04-23 13:00:00",
     start_month: 4,
     start_day: 23,
     created_at: "2022-04-20 19:18:16",
     updated_at: "2022-04-20 19:19:00",
   }
>>> $event->starts_at;
=> "2022-04-23 13:00:00"
>>> $event->start_month;        # refresh()するとModelインスタンスも更新される
=> 4

おわりに

今回はMySQLのGenerated Columnsについて、基本的な使い方と注意点をまとめました。

使い所は結構限られるとは思いますが、有効に使えればかなり便利なので機会があれば是非使ってみてください。

参考文献

MySQL公式ドキュメント(日本語)

MySQL公式ドキュメント(英語)