Zenn
💽

MySQLのARCHIVEストレージエンジンをRailsで使用する

2024/11/10に公開
MySQL
Rails
tech

この記事について

MySQLのARCHIVEストレージエンジンを使って、レコードの保存に挑戦します。

https://dev.mysql.com/doc/refman/8.0/ja/archive-storage-engine.html

この記事では、modelのコールバックやMySQLのトリガーによるArchive用レコードの作成方法と、ストレージエンジンの違いについて簡単にまとめます。

例題

UserモデルとArchivedUserモデルを作成し、UserモデルはInnoDB、ArchivedUserモデルはARCHIVEストレージエンジンで作成したテーブルに保存します。

検証環境

docker-composeでRails/MySQL環境を構築しています。

  • Ruby on Rails: 7.2.2
  • Ruby: 3.3.5
  • MySQL: 8.0
  • Docker/docker-compose
  • colima: 0.7.6

modelの作成

Userモデル・ArchivedUserモデルにはそれぞれnameとemailを持たせています。

# bundle exec rails g model user name:string email:string
      invoke  active_record
      create    db/migrate/20241103095659_create_users.rb
      create    app/models/user.rb
# bundle exec rails g model archived_user name:string email:string
      invoke  active_record
      create    db/migrate/20241103100449_create_archived_users.rb
      create    app/models/archived_user.rb

migrationファイル

usersテーブルのmigrationファイルは自動生成そのままにします。

db/migrate/20241103095659_create_users.rb
class CreateUsers < ActiveRecord::Migration[7.2]
  def change
    create_table :users do |t|
      t.string :name
      t.string :email

      t.timestamps
    end
  end
end

archived_usersテーブルには、options: 'ENGINE=ARCHIVE'を指定します。

db/migrate/20241103100449_create_archived_users.rb
class CreateArchivedUsers < ActiveRecord::Migration[7.2]
  def change
    create_table :archived_users, options: 'ENGINE=ARCHIVE' do |t|
      t.string :name
      t.string :email

      t.timestamps
    end
  end
end

migrate実行

# bundle exec rails db:migrate
== 20241103095659 CreateUsers: migrating ======================================
-- create_table(:users)
   -> 0.0315s
== 20241103095659 CreateUsers: migrated (0.0316s) =============================

== 20241103100449 CreateArchivedUsers: migrating ==============================
-- create_table(:archived_users, {:options=>"ENGINE=ARCHIVE"})
   -> 0.0152s
== 20241103100449 CreateArchivedUsers: migrated (0.0153s) =====================

MySQLでストレージエンジンを確認する

usersテーブルはInnoDBストレージエンジン、archived_usersテーブルはARCHIVEストレージエンジンを使用していることがわかります。

mysql> SELECT TABLE_NAME, ENGINE FROM information_schema.TABLES WHERE TABLE_NAME LIKE '%users';
+----------------+--------------------+
| TABLE_NAME     | ENGINE             |
+----------------+--------------------+
| users          | PERFORMANCE_SCHEMA |
| users          | InnoDB             |
| archived_users | ARCHIVE            |
+----------------+--------------------+
3 rows in set (0.01 sec)

Userモデルにコールバックを設定する

https://railsguides.jp/active_record_callbacks.html#オブジェクトの破棄

before_destroyコールバックで、archiveするようにしてみます。

app/models/user.rb
class User < ApplicationRecord
  before_destroy :archive!
  
  private

  def archive!
    ArchivedUser.create!(name: name, email: email)
  end
end

Userを生成・削除する

生成

rails console上でUserを作成します。ここでは、Faker gemを使用してnameとemailを設定しています。

# bundle exec rails c
Loading development environment (Rails 7.2.2)
app(dev)> User.create!(name: Faker::Name.name, email: Faker::Internet.email)
  TRANSACTION (0.2ms)  BEGIN
  User Create (4.3ms)  INSERT INTO `users` (`name`, `email`, `created_at`, `updated_at`) VALUES ('Damon Buckridge', 'lupe.jacobs@sporer.example', '2024-11-03 10:48:16.863843', '2024-11-03 10:48:16.863843')
  TRANSACTION (3.2ms)  COMMIT
=> 
#<User:0x0000ffff93136af8
 id: 1,
 name: "Damon Buckridge",
 email: "[FILTERED]",
 created_at: "2024-11-03 10:48:16.863843000 +0000",
 updated_at: "2024-11-03 10:48:16.863843000 +0000">

削除

rails console上で先ほど生成したUserを削除します。

app(dev)> User.first.destroy!
  User Load (0.7ms)  SELECT `users`.* FROM `users` ORDER BY `users`.`id` ASC LIMIT 1
  TRANSACTION (2.0ms)  BEGIN
  ArchivedUser Create (1.9ms)  INSERT INTO `archived_users` (`name`, `email`, `created_at`, `updated_at`) VALUES ('Damon Buckridge', 'lupe.jacobs@sporer.example', '2024-11-03 10:48:28.826647', '2024-11-03 10:48:28.826647')
  User Destroy (1.2ms)  DELETE FROM `users` WHERE `users`.`id` = 1
  TRANSACTION (3.5ms)  COMMIT
=> 
#<User:0x0000ffff93d1b698
 id: 1,
 name: "Damon Buckridge",
 email: "[FILTERED]",
 created_at: "2024-11-03 10:48:16.863843000 +0000",
 updated_at: "2024-11-03 10:48:16.863843000 +0000">

ArchivedUserを確認する

ArchivedUser.firstを確認してみると、先ほど削除したUserがArchivedUserとして保存されています。

app(dev)> ArchivedUser.first
  ArchivedUser Load (7.1ms)  SELECT `archived_users`.* FROM `archived_users` ORDER BY `archived_users`.`id` ASC LIMIT 1
=> 
#<ArchivedUser:0x0000ffff91b6eec8
 id: 1,
 name: "Damon Buckridge",
 email: "[FILTERED]",
 created_at: "2024-11-03 10:48:28.826647000 +0000",
 updated_at: "2024-11-03 10:48:28.826647000 +0000">

MySQLのトリガーでArchiveする

MySQLのトリガーで、usersレコード削除にarchiveする方式にも挑戦してみました。

https://dev.mysql.com/doc/refman/8.0/ja/triggers.html

db/migrate/20241103161050_create_trigger_delete_user.rb
class CreateTriggerDeleteUser < ActiveRecord::Migration[7.2]
  def change
    reversible do |direction|
      direction.up do
        execute <<-SQL
          CREATE TRIGGER delete_user_trigger
          AFTER DELETE ON users
          FOR EACH ROW
          BEGIN
            INSERT INTO archived_users (name, email, created_at, updated_at)
            VALUES (OLD.name, OLD.email, OLD.created_at, OLD.updated_at);
          END;
        SQL
      end

      direction.down do
        execute <<-SQL
          DROP TRIGGER delete_user_trigger IF EXISTS delete_user_trigger;
        SQL
      end
    end
  end
end

# bundle exec rails db:migrate
== 20241103161050 CreateTriggerDeleteUser: migrating ==========================
-- execute("          CREATE TRIGGER delete_user_trigger\n          AFTER DELETE ON users\n          FOR EACH ROW\n          BEGIN\n            INSERT INTO archived_users (name, email, created_at, updated_at)\n            VALUES (OLD.name, OLD.email, OLD.created_at, OLD.updated_at);\n          END;\n")
   -> 0.0165s
== 20241103161050 CreateTriggerDeleteUser: migrated (0.0167s) =================

modelのコールバックでデータの移動が発生しないように、before_destroyをコメントアウトしておきます。

app/models/user.rb
@@ -1,5 +1,5 @@
 class User < ApplicationRecord
-  before_destroy :archive!
+  # before_destroy :archive!
   
   private

rails consoleから、Userを作成します。

rails console
app(dev)> User.create!(name: Faker::Name.name, email: Faker::Internet.email)
  TRANSACTION (0.2ms)  BEGIN
  User Create (5.2ms)  INSERT INTO `users` (`name`, `email`, `created_at`, `updated_at`) VALUES ('Erica Brekke', 'elliott.kuhlman@hackett.test', '2024-11-03 16:25:42.051505', '2024-11-03 16:25:42.051505')
  TRANSACTION (4.5ms)  COMMIT
=> 
#<User:0x0000ffff8e67faf0
 id: 2,
 name: "Erica Brekke",
 email: "[FILTERED]",
 created_at: "2024-11-03 16:25:42.051505000 +0000",
 updated_at: "2024-11-03 16:25:42.051505000 +0000">

作成したUserを削除します。before_destroyコールバックでArchivedUserを作成していたときには、ArchivedUser Createのログが出力されていましたが、MySQLのトリガーのみ設定してコールバックは無効化しているため、Rails側で処理が発生せずrails consoleにはログが出力されませんでした。

rails console
app(dev)> User.first.destroy!
  User Load (0.6ms)  SELECT `users`.* FROM `users` ORDER BY `users`.`id` ASC LIMIT 1
  TRANSACTION (1.7ms)  BEGIN
  User Destroy (5.9ms)  DELETE FROM `users` WHERE `users`.`id` = 2
  TRANSACTION (2.4ms)  COMMIT
=> 
#<User:0x0000ffff8d13fa00
 id: 2,
 name: "Erica Brekke",
 email: "[FILTERED]",
 created_at: "2024-11-03 16:25:42.051505000 +0000",
 updated_at: "2024-11-03 16:25:42.051505000 +0000">

ArchivedUser.lastを確認してみると、削除したUserが格納されていました。

rails console
app(dev)> ArchivedUser.last
  ArchivedUser Load (3.4ms)  SELECT `archived_users`.* FROM `archived_users` ORDER BY `archived_users`.`id` DESC LIMIT 1
=> 
#<ArchivedUser:0x0000ffff8d0f7548
 id: 2,
 name: "Erica Brekke",
 email: "[FILTERED]",
 created_at: "2024-11-03 16:25:42.051505000 +0000",
 updated_at: "2024-11-03 16:25:42.051505000 +0000">

InnoDBとARCHIVEの違い

テーブルサイズ

InnoDBとARCHIVEエンジンでどれぐらいテーブルサイズが違うのか気になったので調査してみました。rails console上で500_000.times { User.create!(name: Faker::Name.name, email: Faker::Internet.email) }を実行して、50万件のレコードを作成します。

MySQL上でレコード数やテーブルについて確認してみます。

mysql> SELECT COUNT(*) FROM users;
+----------+
| COUNT(*) |
+----------+
|   500000 |
+----------+
1 row in set (0.02 sec)

mysql> ANALYZE TABLE users;
+-----------------------+---------+----------+----------+
| Table                 | Op      | Msg_type | Msg_text |
+-----------------------+---------+----------+----------+
| app_development.users | analyze | status   | OK       |
+-----------------------+---------+----------+----------+
1 row in set (0.00 sec)

mysql> SELECT table_name, engine, table_rows, data_length, index_length, data_length + index_length as total_size, round(((data_length + index_length) / 1024 / 1024), 2) as size_in_mb FROM      information_schema.tables WHERE table_schema = 'app_development' AND table_name = 'users';
+------------+--------+------------+-------------+--------------+------------+------------+
| TABLE_NAME | ENGINE | TABLE_ROWS | DATA_LENGTH | INDEX_LENGTH | total_size | size_in_mb |
+------------+--------+------------+-------------+--------------+------------+------------+
| users      | InnoDB |     498069 |    47792128 |            0 |   47792128 |      45.58 |
+------------+--------+------------+-------------+--------------+------------+------------+
1 row in set (0.00 sec)

おおよそ、45MBあることがわかりました。続いて、50万件のUsersを削除しました。usersテーブルにトリガーを設定してあるので、トリガーによりarchived_usersテーブルに50万レコードが作られます。（2レコード多いのは気にしないでください）

mysql> SELECT COUNT(*) FROM archived_users;
+----------+
| COUNT(*) |
+----------+
|   500002 |
+----------+
1 row in set (0.01 sec)

mysql> ANALYZE TABLE archived_users;
+--------------------------------+---------+----------+----------------------------------------------------------+
| Table                          | Op      | Msg_type | Msg_text                                                 |
+--------------------------------+---------+----------+----------------------------------------------------------+
| app_development.archived_users | analyze | note     | The storage engine for the table doesn't support analyze |
+--------------------------------+---------+----------+----------------------------------------------------------+
1 row in set (0.01 sec)

mysql> SELECT table_name, engine, table_rows, data_length, index_length, data_length + index_length as total_size, round(((data_length + index_length) / 1024 / 1024), 2) as size_in_mb FROM      information_schema.tables WHERE table_schema = 'app_development' AND table_name = 'archived_users';
+----------------+---------+------------+-------------+--------------+------------+------------+
| TABLE_NAME     | ENGINE  | TABLE_ROWS | DATA_LENGTH | INDEX_LENGTH | total_size | size_in_mb |
+----------------+---------+------------+-------------+--------------+------------+------------+
| archived_users | ARCHIVE |     500002 |    14855943 |            0 |   14855943 |      14.17 |
+----------------+---------+------------+-------------+--------------+------------+------------+
1 row in set (0.00 sec)

archived_usersテーブルは約14.17MBのようです。

結果として、同等のレコード数を持っている場合、InnoDBとARCHIVEでは差が発生することがわかりました。

使用可能なクエリ

ARCHIVE エンジンでは、INSERT、REPLACE および SELECT はサポートされますが、DELETE または UPDATE はサポートされません。

ARCHIVEストレージエンジンを使用している場合は、DELETE/UPDATEクエリが実行できないようです。rails console上から ArchivedUser.last.destroy を実行してみると、例外が発生しレコードの削除ができませんでした。

app(dev)> ArchivedUser.last.destroy
  ArchivedUser Load (321.5ms)  SELECT `archived_users`.* FROM `archived_users` ORDER BY `archived_users`.`id` DESC LIMIT 1
  TRANSACTION (0.2ms)  BEGIN
  ArchivedUser Destroy (0.7ms)  DELETE FROM `archived_users` WHERE `archived_users`.`id` = 500002
  TRANSACTION (0.2ms)  ROLLBACK
(app):2:in `<main>': Mysql2::Error: Table storage engine for 'archived_users' doesn't have this option (ActiveRecord::StatementInvalid)
...

UPDATEも同様に、例外が発生して更新ができませんでした。

app(dev)> ArchivedUser.last.update(name: 'hoge')
  ArchivedUser Load (320.9ms)  SELECT `archived_users`.* FROM `archived_users` ORDER BY `archived_users`.`id` DESC LIMIT 1
  TRANSACTION (0.4ms)  BEGIN
  ArchivedUser Update (0.7ms)  UPDATE `archived_users` SET `archived_users`.`name` = 'hoge', `archived_users`.`updated_at` = '2024-11-10 13:24:17.492170' WHERE `archived_users`.`id` = 500002
  TRANSACTION (0.3ms)  ROLLBACK
(app):4:in `<main>': Mysql2::Error: Table storage engine for 'archived_users' doesn't have this option (ActiveRecord::StatementInvalid)

さいごに

普段はInnoDBエンジンでテーブルを作りがちでしたが、初めてARCHIVEDストレージエンジンを使用してのテーブル作成に臨んでみました。触れたり調べたりすることで知ることがたくさんあったので、これからもっと触れて理解を深めていきます。この記事をアップデートしたり、他の記事にまとめ直したりといったことにも挑戦していきます。

Discussion