Zenn
コラボスタイル DevelopersPublicationへの投稿
⚙️

10万件データで実証。データベース設計パターンの性能・保守性比較【Rust + MariaDB】

yushi-s
に公開
MySQL
Rust
MariaDB
パフォーマンス
tech

はじめに

データベース設計において、パフォーマンス、拡張性、保守性のバランスを取ることは永遠の課題です。本記事では、OLTP（Online Transaction Processing）システムを想定し、10万件のデータを使用して、3つの主要なデータベース設計パターンの性能を定量的に比較し、保守性を実際のコードベースで説明します。

対象システムの特性

  • OLTP（オンライントランザクション処理）: リアルタイムでのデータ更新・参照
  • 高頻度のCRUD操作: 部屋情報の登録・更新・削除・検索
  • 複雑なビジネスロジック: 部屋の状態分析、メンテナンス管理
  • 拡張性の要求: 新しい属性や機能の追加に対応

比較対象の設計パターン

  • Flat Table設計: 単一テーブルに全属性を格納
  • EAV設計: Entity-Attribute-Valueパターン
  • 正規化柔軟設計: 正規化された柔軟設計
  • JSON設計: JSON形式での柔軟な属性管理
  • パーティション設計: 大規模データの効率的な管理

ベンチマーク結果サマリー

総合ランキング

  1. 正規化柔軟設計: バランス型、長期的な保守性が高い
  2. JSON設計: 現代的な柔軟性とパフォーマンスのバランス
  3. パーティション設計: 大規模データでの効率的な処理
  4. Flat Table: 基本操作最速、ビジネスロジックで課題
  5. EAV Pattern: 柔軟性最高、保守性最低

各項目でのランキング

項目 1位 2位 3位 4位 5位
基本操作性能 Flat Table 正規化柔軟設計 EAV Pattern パーティション設計 JSON設計
ビジネスロジック性能 正規化柔軟設計 EAV Pattern パーティション設計 JSON設計 Flat Table

重要な発見

  • Flat Table: 基本操作で最速（0.18ms）だが、複雑なビジネスロジック分析でタイムアウト
  • 正規化柔軟設計: ビジネスロジック分析で良好（0.797ms）、適切な正規化で保守性が高い
  • EAV Pattern: 中程度の性能（17.12ms）、複雑なJOINで保守性が著しく低い
  • JSON設計: 基本操作は良好（0.33ms）だが、複雑なビジネスロジックで大幅に性能が劣化（119.919ms）
  • パーティション設計: 基本操作で中程度（0.38ms）、複雑なビジネスロジックで最も遅い（181.756ms）

比較対象の設計パターン

1. Flat Table設計（単一テーブル設計）

特徴

  • すべての属性を1つのテーブルに格納
  • シンプルな構造で理解しやすい
  • 単一テーブルでのJOIN不要

テーブル構造

CREATE TABLE room_layout_flat (
    id INT AUTO_INCREMENT PRIMARY KEY,
    building_name VARCHAR(255),
    room_name VARCHAR(255),
    room_type VARCHAR(100),
    area_m2 DECIMAL(8,2),
    floor_number INT,
    
    -- トイレ関連
    has_toilet BOOLEAN,
    toilet_count INT,
    toilet_brand VARCHAR(100),
    toilet_model VARCHAR(100),
    toilet_position_x DECIMAL(8,2),
    toilet_position_y DECIMAL(8,2),
    
    -- シャワー関連
    has_shower BOOLEAN,
    shower_count INT,
    shower_brand VARCHAR(100),
    shower_position_x DECIMAL(8,2),
    shower_position_y DECIMAL(8,2),
    
    -- その他の属性（38個のカラム）
    -- ... 省略 ...
);

メリット

  • シンプルなクエリでは高速
  • JOINが不要で構造が理解しやすい
  • 基本的な操作が直感的

デメリット

  • スキーマ変更時にALTER TABLEが必要
  • 多くのNULL値が発生する可能性
  • 属性追加時の複雑性
  • 複雑なビジネスロジック分析で性能が大幅に劣化

2. EAV設計（Entity-Attribute-Value）

特徴

  • 3つのテーブルで柔軟な属性管理
  • 事前定義されていない属性も動的に追加可能
  • スキーマ変更が不要

テーブル構造

-- エンティティ（部屋）
CREATE TABLE entities (
    id INT AUTO_INCREMENT PRIMARY KEY,
    entity_type VARCHAR(100),
    name VARCHAR(255)
);

-- 属性（部屋の特性）
CREATE TABLE attributes (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100)
);

-- 値（エンティティと属性の組み合わせ）
CREATE TABLE entity_values (
    id INT AUTO_INCREMENT PRIMARY KEY,
    entity_id INT,
    attribute_id INT,
    value TEXT,
    FOREIGN KEY (entity_id) REFERENCES entities(id),
    FOREIGN KEY (attribute_id) REFERENCES attributes(id)
);

メリット

  • 極めて高い柔軟性
  • スキーマ変更が不要
  • 新しい属性の追加が容易

デメリット

  • 複雑なJOINが必要
  • クエリの可読性が低い
  • パフォーマンスが劣る
  • データ型の制約が効かない

3. 正規化柔軟設計（Normalized Flexible Design）

特徴

  • 適切な正規化と柔軟性のバランス
  • キーバリュー形式での属性管理
  • JSON形式での詳細情報格納

テーブル構造

-- 建物
CREATE TABLE buildings_good (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255) NOT NULL
);

-- 部屋タイプ
CREATE TABLE room_types_good (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    category VARCHAR(50) NOT NULL
);

-- 部屋
CREATE TABLE rooms_good (
    id INT AUTO_INCREMENT PRIMARY KEY,
    building_id INT,
    room_type_id INT,
    name VARCHAR(255),
    floor_number INT,
    area_m2 DECIMAL(8,2),
    FOREIGN KEY (building_id) REFERENCES buildings_good(id),
    FOREIGN KEY (room_type_id) REFERENCES room_types_good(id)
);

-- 部屋属性（キーバリュー形式）
CREATE TABLE room_attributes_good (
    id INT AUTO_INCREMENT PRIMARY KEY,
    room_id INT,
    attribute_key VARCHAR(100),
    attribute_value TEXT,
    attribute_type VARCHAR(50),
    FOREIGN KEY (room_id) REFERENCES rooms_good(id)
);

メリット

  • 適切な正規化
  • 柔軟な属性管理
  • バランスの取れた設計

デメリット

  • 複数のテーブルでのJOINが必要
  • 中程度の複雑性

4. JSON設計（JSON-based Pattern）

特徴

  • JSON形式での柔軟な属性管理
  • 現代的なデータベース機能を活用
  • スキーマ変更が不要で拡張性が高い

テーブル構造

CREATE TABLE rooms_json (
    id INT AUTO_INCREMENT PRIMARY KEY,
    building_id INT,
    room_type_id INT,
    name VARCHAR(255),
    floor_number INT,
    area_m2 DECIMAL(8,2),
    attributes JSON,  -- 柔軟な属性をJSONで格納
    facilities JSON,  -- 設備情報をJSONで格納
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (building_id) REFERENCES buildings_good(id),
    FOREIGN KEY (room_type_id) REFERENCES room_types_good(id)
);

JSONデータ例

{
  "attributes": {
    "has_toilet": true,
    "has_shower": false,
    "has_air_conditioning": true,
    "has_heating": true,
    "has_window": true,
    "window_direction": "South",
    "room_status": "Active",
    "last_maintenance_date": "2024-06-15",
    "maintenance_interval_days": 30,
    "ac_capacity_kw": 3.5
  },
  "facilities": {
    "toilet": {
      "brand": "TOTO",
      "model": "CS354B",
      "position": {"x": 0.5, "y": 0.3}
    },
    "shower": null,
    "sink": {
      "brand": "LIXIL",
      "position": {"x": 0.8, "y": 0.6}
    }
  }
}

メリット

  • 極めて高い柔軟性
  • JSON関数による効率的な検索・集計
  • スキーマ変更が不要
  • データ型の制約が部分的に効く

デメリット

  • JSON関数の学習コスト
  • 複雑なクエリでの可読性
  • インデックスの制限

5. パーティション設計（Partitioned Pattern）

特徴

  • 大規模データの効率的な管理
  • パーティション単位での操作
  • 時間ベースでのデータ分割

テーブル構造

CREATE TABLE rooms_partitioned (
    id INT AUTO_INCREMENT,
    building_id INT,
    room_type_id INT,
    name VARCHAR(255),
    floor_number INT,
    area_m2 DECIMAL(8,2),
    has_toilet BOOLEAN,
    has_shower BOOLEAN,
    has_air_conditioning BOOLEAN,
    room_status VARCHAR(20),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id, created_at),
    FOREIGN KEY (building_id) REFERENCES buildings_good(id),
    FOREIGN KEY (room_type_id) REFERENCES room_types_good(id)
) PARTITION BY RANGE (YEAR(created_at)) (
    PARTITION p2023 VALUES LESS THAN (2024),
    PARTITION p2024 VALUES LESS THAN (2025),
    PARTITION p2025 VALUES LESS THAN (2026),
    PARTITION p_future VALUES LESS THAN MAXVALUE
);

メリット

  • 大規模データでの効率的な処理
  • パーティション単位での操作（削除、バックアップ等）
  • クエリパフォーマンスの向上
  • データの時系列管理

デメリット

  • 設計の複雑性
  • パーティション戦略の慎重な選択が必要
  • 小規模データではオーバーヘッド

ベンチマーク環境と実装

技術スタック

  • 言語: Rust 1.70+
  • Webフレームワーク: Axum 0.7
  • データベース: MariaDB (devenv)
  • ORM: SQLx 0.7
  • 非同期ランタイム: Tokio 1.0

テストデータ仕様

  • データ量: 10万件の部屋データ
  • 属性数: 38個のカラム（Flat Table）
  • 建物数: 1,000棟
  • 部屋タイプ: 8種類（Living Room, Bedroom, Bathroom, Kitchen, Dining Room, Study Room, Storage Room, Utility Room）

ベンチマーク結果の詳細分析

1. パフォーマンス比較

実行時間の比較（ミリ秒）

設計パターン シンプル検索 複雑集計 ビジネスロジック分析 平均実行時間
Flat Table 0.312ms 0.234ms タイムアウト 0.18ms
EAV Pattern 0.796ms 38.836ms 11.727ms 17.12ms
正規化柔軟設計 0.253ms 87.324ms 0.797ms 29.46ms
JSON設計 0.329ms 167.935ms 119.919ms 96.06ms
パーティション設計 0.382ms 93.356ms 181.756ms 91.83ms

パフォーマンスランキング（基本操作のみ）

  1. Flat Table: 0.18ms（基本操作最速）
  2. 正規化柔軟設計: 0.25ms（基本操作で良好）
  3. EAV Pattern: 0.80ms（基本操作で劣る）
  4. JSON設計: 0.33ms（基本操作で良好）
  5. パーティション設計: 0.38ms（基本操作で中程度）

ビジネスロジック分析の詳細

  • Flat Table: ビジネスロジック分析でタイムアウト（複雑なクエリで30秒以上）
  • EAV Pattern: 中程度の速度（11.727ms）
  • 正規化柔軟設計: ビジネスロジック分析で良好（0.797ms）
  • JSON設計: 大幅に性能が劣化（119.919ms）
  • パーティション設計: 最も遅い（181.756ms）

保守性の実例比較

実際のコードベースで各設計パターンの保守性の違いを見てみましょう。

Flat Table設計のクエリ例

-- シンプルな検索クエリ
SELECT * FROM room_layout_flat 
WHERE building_name = 'Building 1' AND has_toilet = true;

-- 複雑な集計クエリ
SELECT 
    building_name,
    room_type,
    COUNT(*) as room_count,
    AVG(area_m2) as avg_area,
    SUM(CASE WHEN has_toilet = 1 THEN 1 ELSE 0 END) as toilet_rooms,
    SUM(CASE WHEN has_shower = 1 THEN 1 ELSE 0 END) as shower_rooms,
    SUM(CASE WHEN has_air_conditioning = 1 THEN 1 ELSE 0 END) as ac_rooms,
    SUM(CASE WHEN room_status = 'Maintenance' THEN 1 ELSE 0 END) as maintenance_rooms,
    SUM(CASE WHEN has_window = 1 AND window_direction = 'South' THEN 1 ELSE 0 END) as south_facing_rooms,
    AVG(CASE WHEN has_heating = 1 THEN ac_capacity_kw ELSE NULL END) as avg_ac_capacity
FROM room_layout_flat 
WHERE floor_number BETWEEN 1 AND 3
AND area_m2 > 15.0
AND (has_toilet = 1 OR has_shower = 1)
AND room_status IN ('Active', 'Maintenance')
GROUP BY building_name, room_type
HAVING room_count > 5 AND avg_area > 20.0
ORDER BY avg_area DESC, room_count DESC;

保守性の特徴:

  • シンプルな検索は理解しやすい
  • JOINが不要で構造が分かりやすい
  • 複雑な集計ではCASE文が連続し、可読性が低下
  • 新しい属性を追加する際はALTER TABLEが必要

EAV Patternのクエリ例

-- シンプルな検索クエリ
SELECT e.name, ev.value
FROM entities e
JOIN entity_values ev ON e.id = ev.entity_id
JOIN attributes a ON ev.attribute_id = a.id
WHERE e.entity_type = 'room' AND a.name = 'has_toilet' AND ev.value = 'true';

-- 複雑な集計クエリ
SELECT 
    e.name as room_name,
    MAX(CASE WHEN a.name = 'room_type' THEN ev.value END) as room_type,
    MAX(CASE WHEN a.name = 'area' THEN CAST(ev.value AS DECIMAL(8,2)) END) as area,
    MAX(CASE WHEN a.name = 'has_toilet' THEN ev.value END) as has_toilet,
    MAX(CASE WHEN a.name = 'has_shower' THEN ev.value END) as has_shower,
    MAX(CASE WHEN a.name = 'has_air_conditioning' THEN ev.value END) as has_ac,
    MAX(CASE WHEN a.name = 'room_status' THEN ev.value END) as room_status,
    COUNT(DISTINCT a.name) as attribute_count
FROM entities e
JOIN entity_values ev ON e.id = ev.entity_id
JOIN attributes a ON ev.attribute_id = a.id
WHERE e.entity_type = 'room'
AND a.name IN ('room_type', 'area', 'has_toilet', 'has_shower', 'has_air_conditioning', 'room_status')
GROUP BY e.id, e.name
HAVING 
    MAX(CASE WHEN a.name = 'has_toilet' THEN ev.value END) = 'true'
    OR MAX(CASE WHEN a.name = 'has_shower' THEN ev.value END) = 'true'
ORDER BY 
    MAX(CASE WHEN a.name = 'area' THEN CAST(ev.value AS DECIMAL(8,2)) END) DESC;

保守性の特徴:

  • ❌ 3つのテーブルでのJOINが必要
  • ❌ 複雑なCASE文の連続で可読性が極めて低い
  • ❌ 属性の追加・変更が容易だが、クエリが複雑になる
  • ❌ データ型の制約が効かない（すべてTEXT型）

正規化柔軟設計のクエリ例

-- シンプルな検索クエリ
SELECT r.name, ra.attribute_value
FROM rooms_good r
JOIN room_attributes_good ra ON r.id = ra.room_id
WHERE r.building_id = 1 AND ra.attribute_key = 'has_toilet' AND ra.attribute_value = 'true';

-- 複雑な集計クエリ
SELECT 
    b.name as building_name,
    rt.name as room_type,
    COUNT(r.id) as room_count,
    AVG(r.area_m2) as avg_area,
    COUNT(CASE WHEN ra.attribute_value = 'true' AND ra.attribute_key = 'has_toilet' THEN 1 END) as toilet_rooms,
    COUNT(CASE WHEN ra.attribute_value = 'true' AND ra.attribute_key = 'has_shower' THEN 1 END) as shower_rooms,
    COUNT(CASE WHEN ra.attribute_value = 'true' AND ra.attribute_key = 'has_air_conditioning' THEN 1 END) as ac_rooms,
    COUNT(CASE WHEN ra.attribute_value = 'Maintenance' AND ra.attribute_key = 'room_status' THEN 1 END) as maintenance_rooms
FROM buildings_good b
LEFT JOIN rooms_good r ON b.id = r.building_id
LEFT JOIN room_types_good rt ON r.room_type_id = rt.id
LEFT JOIN room_attributes_good ra ON r.id = ra.room_id
WHERE r.floor_number BETWEEN 1 AND 3
AND r.area_m2 > 15.0
AND (ra.attribute_value = 'true' AND ra.attribute_key IN ('has_toilet', 'has_shower'))
GROUP BY b.id, b.name, rt.id, rt.name
HAVING room_count > 5 AND avg_area > 20.0
ORDER BY avg_area DESC, room_count DESC;

保守性の特徴:

  • 適切な正規化で構造が理解しやすい
  • キーバリュー形式で柔軟な属性管理
  • 複数のテーブルでのJOINが必要
  • 中程度の複雑性だが、バランスが取れている

JSON設計のクエリ例

-- シンプルな検索クエリ
SELECT r.name, JSON_EXTRACT(r.attributes, '$.has_toilet') as has_toilet
FROM rooms_json r
WHERE JSON_EXTRACT(r.attributes, '$.has_toilet') = true;

-- 複雑な集計クエリ
SELECT 
    b.name as building_name,
    rt.name as room_type,
    COUNT(r.id) as room_count,
    COUNT(CASE WHEN JSON_EXTRACT(r.attributes, '$.has_toilet') = true THEN 1 END) as toilet_rooms,
    COUNT(CASE WHEN JSON_EXTRACT(r.attributes, '$.has_shower') = true THEN 1 END) as shower_rooms,
    AVG(r.area_m2) as avg_area
FROM buildings_good b
LEFT JOIN rooms_json r ON b.id = r.building_id
LEFT JOIN room_types_good rt ON r.room_type_id = rt.id
GROUP BY b.id, b.name, rt.id, rt.name;

保守性の特徴:

  • JSON関数による柔軟な属性アクセス
  • スキーマ変更が不要
  • JSON関数の学習コスト
  • 複雑なクエリでの可読性が低下

パーティション設計のクエリ例

-- シンプルな検索クエリ
SELECT r.name, r.has_toilet, r.has_shower
FROM rooms_partitioned r
WHERE r.building_id = 1 AND r.created_at >= '2024-01-01';

-- 複雑な集計クエリ
SELECT 
    b.name as building_name,
    rt.name as room_type,
    COUNT(r.id) as room_count,
    COUNT(CASE WHEN r.has_toilet = true THEN 1 END) as toilet_rooms,
    COUNT(CASE WHEN r.has_shower = true THEN 1 END) as shower_rooms,
    AVG(r.area_m2) as avg_area
FROM buildings_good b
LEFT JOIN rooms_partitioned r ON b.id = r.building_id
LEFT JOIN room_types_good rt ON r.room_type_id = rt.id
WHERE r.created_at >= '2024-01-01'
GROUP BY b.id, b.name, rt.id, rt.name;

保守性の特徴:

  • パーティション単位での効率的な操作
  • 時間ベースでのデータ管理
  • パーティション戦略の慎重な選択が必要
  • 小規模データではオーバーヘッド

まとめ

本記事では、OLTPシステムを想定し、10万件の実データを使用して、3つの主要なデータベース設計パターンの性能を定量的に比較し、保守性を実際のコードベースで説明しました。

重要な学び:
ベンチマーク結果では、Flat Table設計が基本操作で最速（0.18ms）の結果を出しましたが、複雑なビジネスロジック分析ではタイムアウトしました。一方、正規化柔軟設計はビジネスロジック分析で良好な性能（0.797ms）を示しました。また、JSON設計は基本操作で良好な性能（0.33ms）を示しましたが、複雑なビジネスロジックでは大幅に性能が劣化（119.919ms）しました。この結果から、正規化の原則や長期的な保守性を考慮すると、正規化柔軟設計（適切に正規化された設計）が実際のOLTPプロジェクトではより適切な選択肢となります。

設計選択の指針:

  • ベンチマーク結果は参考資料として活用
  • 実際の設計では、正規化の原則と長期的な保守性を重視
  • パフォーマンスは最適化で改善可能だが、設計の根本的な問題は後から修正困難

OLTP vs OLAPの設計違い:
本記事で扱った設計パターンは、リアルタイムでのデータ更新・参照を前提としたOLTPシステム向けです。一方、データ分析・レポート用途のOLAPシステムでは、スター・スキーマ、スノーフレーク・スキーマ、ファクト・コンステレーション・スキーマなどの異なる設計パターンが適しています。システムの用途に応じて適切な設計パターンを選択することが重要です。

参考資料

技術ドキュメント

ソースコード・ツール

コラボスタイル Developers
コラボスタイル DevelopersPublication

「ワークスタイルの未来を切り拓く」を理念に掲げ、リアルとデジタルの2つの働く場に向けた事業展開に留まらず、自社が率先して新しいワークスタイルに挑戦し、発信を行うことでワークスタイルの未来を切り拓いていきます。

Discussion