ビットマップインデックスとチェックサム：データ変更検知の軽量化

データベーステーブルのデータ変更を追跡する方法

変更履歴テーブル

概要

変更履歴テーブルを作成し、データ変更のたびにその内容を記録する方法です。

メリット

• シンプルで実装が容易
• 変更内容の詳細な履歴を保持できる

• テーブル構造が複雑になる
• 多くの更新が発生する場合は、パフォーマンスが低下する

例

CREATE TABLE change_history (
  id INT NOT NULL AUTO_INCREMENT,
  table_name VARCHAR(255) NOT NULL,
  column_name VARCHAR(255) NOT NULL,
  old_value TEXT,
  new_value TEXT,
  updated_at TIMESTAMP NOT NULL,
  PRIMARY KEY (id)
);

コード解説

• id: 変更履歴のID
• table_name: 変更されたテーブル名
• column_name: 変更されたカラム名
• old_value: 変更前の値
• updated_at: 変更日時

トリガー

テーブルに対する更新、削除、挿入などの操作が発生するたびに、トリガーと呼ばれるプログラムを実行する方法です。

• リアルタイムで変更を追跡できる
• 複雑な条件に基づいて処理を実行できる

• トリガーの記述が複雑になる
• パフォーマンスへの影響が大きい場合がある

CREATE TRIGGER update_history
ON table_name
AFTER UPDATE
AS
BEGIN
  INSERT INTO change_history (
    table_name,
    column_name,
    old_value,
    new_value,
    updated_at
  )
  VALUES (
    OLD.table_name,
    OLD.column_name,
    OLD.value,
    NEW.value,
    CURRENT_TIMESTAMP
  );
END;

• update_history: トリガーの名前
• AFTER UPDATE: トリガーが実行されるタイミング (更新後)
• OLD: 更新前の値
• NEW: 更新後の値

変更データキャプチャ (CDC)

データベースの変更ログをリアルタイムに取得する方法です。

• 高度なフィルタリング機能が利用できる

• データベースの種類によっては利用できない
• 設定が複雑になる場合がある

ツール例

• SQL Server: Change Data Capture
• Oracle: Oracle Streams
• PostgreSQL: Logical Replication

データベース監査

データベースへのアクセスを監査し、変更内容を記録する方法です。

• セキュリティ上の問題を検知できる
• コンプライアンス要件を満たせる

• 多くのデータが生成される

• SQL Server: Database Audit
• Oracle: Audit Vault
• PostgreSQL: pgaudit

データベーステーブルのデータ変更を追跡するには、さまざまな方法があります。それぞれの方法にはメリットとデメリットがあるので、要件に合わせて最適な方法を選択する必要があります。

変更履歴テーブル

from sqlalchemy import create_engine, Column, Integer, String, DateTime

engine = create_engine("sqlite:///database.sqlite")

# テーブル定義
class ChangeHistory(Base):
  __tablename__ = "change_history"

  id = Column(Integer, primary_key=True, autoincrement=True)
  table_name = Column(String(255), nullable=False)
  column_name = Column(String(255), nullable=False)
  old_value = Column(String, nullable=True)
  new_value = Column(String, nullable=True)
  updated_at = Column(DateTime, nullable=False)


# データ変更の記録
def track_change(table_name, column_name, old_value, new_value):
  session = Session(engine)

  change_history = ChangeHistory(
    table_name=table_name,
    column_name=column_name,
    old_value=old_value,
    new_value=new_value,
    updated_at=datetime.now(),
  )

  session.add(change_history)
  session.commit()


# 変更履歴の取得
def get_change_history(table_name, column_name):
  session = Session(engine)

  return session.query(ChangeHistory) \
    .filter(ChangeHistory.table_name == table_name) \
    .filter(ChangeHistory.column_name == column_name) \
    .all()


# 例
track_change("users", "name", "John Doe", "Jane Doe")

change_history = get_change_history("users", "name")

for history in change_history:
  print(f"変更日時: {history.updated_at}")
  print(f"変更前: {history.old_value}")
  print(f"変更後: {history.new_value}")
  print("---")

トリガー

CREATE TRIGGER update_history
ON users
AFTER UPDATE
AS
BEGIN
  INSERT INTO change_history (
    table_name,
    column_name,
    old_value,
    new_value,
    updated_at
  )
  VALUES (
    OLD.table_name,
    OLD.column_name,
    OLD.name,
    NEW.name,
    CURRENT_TIMESTAMP
  );
END;

• このトリガーは users テーブルの name カラムが更新されたときに実行されます。
• 更新前の名前と更新後の名前を change_history テーブルに記録します。

各ツールの設定方法や使用方法については、公式ドキュメントを参照してください。

データベーステーブルのデータ変更を追跡するその他の方法

ビットマップインデックス

ビットマップインデックスを使用して、特定の値が変更されたかどうかを追跡する方法です。

• 少ないストレージスペースで済む

• 詳細な変更履歴を取得できない
• 特定の値の変更のみを検知できる

CREATE BITMAP INDEX idx_name ON table_name (column_name);

• idx_name: ビットマップインデックスの名前

チェックサム

テーブルデータのチェックサムを計算し、定期的に更新して変更を検知する方法です。

• データの整合性を検証できる

ALTER TABLE table_name
ADD CHECKSUM (column_name);

外部ツール

データ変更監査ツールなどの外部ツールを使用して、変更を追跡する方法です。

• さまざまな機能が利用できる
• 複雑な要件にも対応できる

• コストがかかる場合がある