Posted inMySQL

Eコマース向けMySQLスキーマ設計：スケールアップ時にデータベースを「ボトルネック」にさせないために

MySQL tutorial - IT technology blog
MySQL tutorial - IT technology blog

Eコマースのデータベース設計：単にテーブルをいくつか作るだけではない

アプリ開発を始めたばかりの頃、私は販売サイトのDB設計なんて products と orders テーブルがいくつかあれば十分だと思っていました。しかし、現実はもっと過酷です。システムが数千のSKU（在庫最小管理単位）を抱え、色、サイズ、素材といった多様な属性を扱うようになると、初期の小さなミスがパフォーマンス上の大惨事を引き起こします。

設計を誤るとクエリが遅くなり、在庫の不整合や価格履歴の紛失といった致命的なバグが発生します。多くの失敗とバグ修正を経て学んだのは、優れたスキーマとは柔軟性と速度のバランスが取れているべきだということです。目標は、メガセールなどで注文が急増してもシステムがスムーズに動作し続けることです。

正規化 vs 非正規化：どちらを選ぶべきか？

理論的には、データの冗長性を避けるために常に正規化（Normalization）を優先すべきです。しかし、Eコマースにおいては、重い JOIN を減らすために、意図的な非正規化（Denormalization）が鍵となります。

  • 商品とバリエーション： すべてを1つのテーブルに詰め込まないでください. iPhone 15を例にとると、5色と3種類の容量がある場合、15種類のバリエーション（SKU）が生まれます。
  • データのスナップショット： これは非常に重要なポイントです。顧客が注文を確定した瞬間、その時の商品価格を order_items テーブルに保存しなければなりません。商品IDだけを保存していると、翌月に値上げした際、過去の注文履歴の価格まで変わってしまいます。これは非常に初歩的ですが致命的なミスです。
  • 在庫管理： 在庫管理は単なる quantity カラムではありません。「実在庫」と「有効在庫」を明確に分けることで、決済したのに商品がないという事態を防ぐ必要があります。

Thực hành: Cấu trúc Schema tối ưu cho hệ thống lớn

1. 商品とバリエーション（Product & Variants）の扱い

数千の動的な属性を管理するために、私は通常 products と product_variants の間に1対多の関係を持たせます。これにより、共通情報と各SKU固有の識別情報を分離できます。

CREATE TABLE products (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    slug VARCHAR(255) UNIQUE NOT NULL,
    base_price DECIMAL(15, 2) NOT NULL,
    status ENUM('draft', 'published', 'hidden') DEFAULT 'draft',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;

CREATE TABLE product_variants (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    product_id BIGINT UNSIGNED NOT NULL,
    sku VARCHAR(100) UNIQUE NOT NULL,
    price_modifier DECIMAL(15, 2) DEFAULT 0.00,
    attributes JSON, -- { "color": "スペースグレー", "storage": "256GB" } を保存
    INDEX (product_id),
    FOREIGN KEY (product_id) REFERENCES products(id)
) ENGINE=InnoDB;

ヒント： MySQL 8.0の JSON 型は非常に強力です。色やサイズのために20個の中間テーブルを作る代わりに、attributesカラムに直接保存できます。JSON_EXTRACT を使ったり、仮想カラム（Virtual Column）を作成してインデックスを貼ることで、クエリも高速に保てます。

2. Race Conditionを防ぐ在庫管理（Inventory）

注文ボタンが押された瞬間に stock_quantity を直接更新してはいけません。キャンセルや決済失敗が発生した場合、在庫の差し戻しでミスが起きやすくなります。100万件のレコードを扱う実際のプロジェクトでは、この問題を根本的に解決するために「予約（Reserve）」メカニズムを採用しました。

CREATE TABLE inventory (
    variant_id BIGINT UNSIGNED PRIMARY KEY,
    physical_stock INT DEFAULT 0, -- 倉庫内の実在庫
    reserved_stock INT DEFAULT 0, -- 決済待ちの商品
    FOREIGN KEY (variant_id) REFERENCES product_variants(id)
) ENGINE=InnoDB;

販売可能数の計算式は physical_stock - reserved_stock です。注文が入った時は reserved_stock だけを増やします。実際に代金を受け取った、あるいは商品を出荷したタイミングで、両方のカラムを減算します。

3. 注文データの不変性

order_items テーブルは、取引時点の商品情報の「フリーズされたコピー」である必要があります。法的証拠や後日の照合のために、当時の商品名と販売価格の両方を保存しておきましょう。

CREATE TABLE order_items (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    order_id BIGINT UNSIGNED NOT NULL,
    variant_id BIGINT UNSIGNED NOT NULL,
    quantity INT NOT NULL,
    price_at_purchase DECIMAL(15, 2) NOT NULL,
    product_name_snapshot VARCHAR(255),
    FOREIGN KEY (order_id) REFERENCES orders(id)
) ENGINE=InnoDB;

4. 適切なインデックスによるクエリの高速化

大量のデータがあるのにインデックスが不足していると、SELECT 一つでサーバーが「ハング」してしまいます。約50GBの orders テーブルにおいて、複合インデックス（Composite Index）を導入したことで、注文履歴のクエリ時間を2秒から50ms未満に短縮できました。

-- ユーザーIDと作成日時による注文検索の最適化
CREATE INDEX idx_user_id_created_at ON orders(user_id, created_at);

「WHERE や ORDER BY に頻繁に登場するカラムにはインデックスを検討する」というルールを忘れないでください。

おわりに

Eコマースのスキーマ設計に唯一の正解はありません。しかし、「金融に関わるデータは絶対的に正確で、履歴が追える必要がある」ということは常に肝に銘じておいてください。

最初からすべてを複雑にしようとしないでください。まずはProduct、Variant、Orderの関係を適切に処理することに集中しましょう。システムが数百万件の注文に達した時に、パーティショニング（Partitioning）やシャーディング（Sharding）を検討すればよいのです。フラッシュセールの負荷に耐えられる、堅牢なデータベースを構築できることを願っています！

Share:
Tags: