環境
MySQL 5.7
PHP 7.2
Laravel Framework 5.7
やりたいこと
データの履歴管理はRDBMSの苦手とする領域の一つである。
Elasticsearchのようにより適切な別のソリューションを使うというのも選択肢の一つとしてあるが、
お手軽な方法の一つとして本体テーブルのそっくりさんを用意し、そのテーブルに履歴を貯めておく方法をおすすめしたい。
次のようなテーブルがある場合、
| orders |
|---|
| id |
| customer_id |
| memo |
| applied_at |
| canceled_at |
このようなログテーブルを用意し、
| orders_log |
|---|
| id |
| customer_id |
| memo |
| applied_at |
| canceled_at |
| log_id |
| log_time |
| ins_flag |
本体テーブル更新が走るたびに、ログテーブルに更新時点でのデータを溜め込むようにする。
実現方法
トリガーを用いて実現する
まず次のコマンドで本体そっくりさんのログテーブルを生成する。
CREATE TABLE orders_log AS select * from orders;
そしてログテーブルに解析用のカラムを追加する。
（必要なければ追加しなくてもOK）
ALTER TABLE orders_log ADD column (log_id bigint(20) unsigned NOT NULL AUTO_INCREMENT PRIMARY KEY, log_time datetime, ins_flag tinyint);
さらにデータ追加、更新時にログテーブルへのデータ追加処理が走るようトリガーを設定する。
CREATE TRIGGER orders_insert_trigger AFTER INSERT ON orders FOR EACH ROW insert into orders_log ( id,memo,customer_id,canceled_at,applied_at,log_time,ins_flag ) VALUES (NEW.id,NEW.memo,NEW.customer_id,NEW.canceled_at,NEW.applied_at,now(),1);
CREATE TRIGGER orders_update_trigger AFTER UPDATE ON orders FOR EACH ROW insert into orders_log ( id,memo,customer_id,canceled_at,applied_at,log_time,ins_flag ) VALUES (NEW.id,NEW.memo,NEW.customer_id,NEW.canceled_at,NEW.applied_at,now(),0);
以上の操作の結果、ordersに対してがデータ追加、更新が走るたびに、orders_logにログレコードが追加されるようになる。
ポイントとして、ログテーブル生成に CREATE TABLE XXX LIKE YYYでなく、CREATE TABLE XX AS select * from YYYを使う点が挙げられる。
前者のコマンドでログテーブルを作成すると、本体に設定されたインデックスも一緒に付いてきてしまい、パフォーマンスが悪化したり、一意制約違反により意図せぬエラーに遭遇する可能性があるので注意されたい（もちろん運用上必要な場合は、ログテーブルにもインデックスを付けるべきである。）
このやり方のメリット
1.本体のRDBMS以外の別サービスを導入する必要がなくお手軽である。
2.本体データとログデータの整合性がRDBMSレイヤーで保証される。
3.Redshift等のRDBMSベースの解析サービスへの連携が容易。データサイズと気合い次第では本体のRDBMS上で直接データ解析することも十分に可能。
個人的には、2のデータ整合性のメリットが絶大であると考えている。
（ここが崩れると復旧がすさまじくめんどくさいので）
Laravel Commandでログテーブルを簡単生成
ログテーブルとトリガーを簡単に作成するLaravel Commandスクリプトを用意したので、よろしければ参考にしてください。
<?php
namespace App\Console\Commands;
use Illuminate\Console\Command;
use Illuminate\Support\Facades\DB;
class CreateLogTriggerCommand extends Command
{
/**
* The name and signature of the console command.
*
* @var string
*/
protected $signature = 'fn:create_trigger {table_name} {log_time_column_name=utc_log_time} {ins_flag_column_name=ins_flag}';
/**
* The console command description.
*
* @var string
*/
protected $description = 'Prepare SQL to create table and trigger for logs';
/**
* Execute the console command.
*
* @return mixed
*/
public function handle()
{
$table_name = $this->argument("table_name");
$log_time_column_name = $this->argument("log_time_column_name");
$ins_flag_column_name = $this->argument("ins_flag_column_name");
$column_array = DB::table('information_schema.columns')->where('TABLE_NAME', $table_name)->pluck("COLUMN_NAME");
$log_table_name = $table_name . "_log";
echo "CREATE TABLE $log_table_name AS select * from $table_name;" .PHP_EOL;
echo "ALTER TABLE $log_table_name ADD column (log_id bigint(20) unsigned NOT NULL AUTO_INCREMENT PRIMARY KEY, $log_time_column_name datetime, $ins_flag_column_name tinyint);".PHP_EOL;
$columns = "";
$values = "";
foreach ($column_array as $column) {
$columns .= $column . ",";
$values .= "NEW." . $column . ",";
}
$columns = $columns . $log_time_column_name;
$values = $values . "now()";
echo "CREATE TRIGGER {$table_name}_insert_trigger AFTER INSERT ON $table_name FOR EACH ROW insert into $log_table_name ( $columns,$ins_flag_column_name ) VALUES ($values,1);".PHP_EOL;
echo "CREATE TRIGGER {$table_name}_update_trigger AFTER UPDATE ON $table_name FOR EACH ROW insert into $log_table_name ( $columns,$ins_flag_column_name ) VALUES ($values,0);".PHP_EOL;
}
}