database-optimization-commerce

データベース最適化 — コマース

概要

Eコマースのデータベースには、特有のクエリパターンがあります。カーディナリティの高い商品フィルタリング（カテゴリー＋価格＋属性）、セッション単位のカート検索、書き込みが多い注文作成、そして同時ユーザー数の増加に対応する必要がある読み取り中心の商品カタログ閲覧です。このスキルでは、遅いクエリの特定、商品フィルタリングに有効なインデックスの設計、注文テーブルのパーティショニング、読み取りトラフィックのレプリカへのルーティングについて説明します。

このスキルを使う場面

• インデックスのないフィルターの組み合わせ（カテゴリー＋価格＋ブランド）が原因で商品一覧ページの表示が遅い場合
• 注文挿入の遅延によりチェックアウトのスループットが制限されている場合
• プライマリデータベースの読み取り負荷が原因で書き込み遅延が増加している場合
• スロークエリログに、大きなテーブルに対してシーケンシャルスキャンを実行しているクエリが記録されている場合
• 新しいカスタムEコマースプラットフォームのデータベーススキーマを計画する場合

主な手順

ステップ 1：状況を確認する

データベース最適化は主にセルフホストの環境で適用されます。まず制約を把握してください。

プラットフォーム	データベースの管理権限	最適化する項目
Shopify	なし — Shopify がすべてのインフラを管理します	Liquid テンプレートのレンダリング速度、アプリのパフォーマンス、Search & Discovery アプリによる Shopify 組み込みのクエリ最適化に注力します
WooCommerce	すべて — ホスト上の MySQL/MariaDB を管理します	キャッシュプラグイン（Redis Object Cache、WP Rocket）で WooCommerce のクエリを最適化し、WP Optimize プラグインでデータベースインデックスを追加して、ホスティングの MySQL 設定を調整します
BigCommerce	なし — BigCommerce がすべてのインフラを管理します	テーマのパフォーマンス、画像の最適化、サードパーティアプリのオーバーヘッド削減に注力します
カスタム／ヘッドレス	すべて — PostgreSQL（または MySQL）を管理します	以下の手法をすべて適用します。コード例では PostgreSQL を前提としています

ステップ 2：WooCommerce ですぐにできる改善（管理型 WordPress/WooCommerce）

データベースインデックスを直接変更する前に、WooCommerce に特化した次の最適化を適用してください。

1. Redis Object Cache をインストールする（無料、wordpress.org）：

   • ホストで Redis がサポートされている必要があります（ほとんどの管理型 WordPress ホスト — WP Engine、Kinsta、Cloudways — が対応しています）
   • プラグインをインストールして有効化し、Settings → Redis に移動して Enable Object Cache をクリックします
   • WooCommerce のデータベースクエリがすべてメモリにキャッシュされ、クエリの再実行にかかる時間が大幅に短縮されます

2. WP-Optimize をインストールする（無料、wordpress.org）：

   • WP-Optimize → Database に移動し、Clean database を実行して、孤立した注文メタデータ、期限切れのトランジェント、投稿リビジョンを削除します
   • WooCommerce のストアでは、孤立したメタデータが時間とともに数百万行に膨れ上がるため、定期的なクリーンアップが不可欠です
   • 毎週の自動クリーンアップをスケジュールします

3. WooCommerce HPOS（High-Performance Order Storage）を有効にする：

   • WooCommerce → Settings → Advanced → Features に移動します
   • High-Performance Order Storage を有効にします。これにより、注文が WP の投稿テーブルから、適切なインデックスを備えた専用の注文テーブルに移されます
   • 注文数が 10,000 件を超えるストアでは特に重要です

4. MySQL 8.0 以降を搭載したホストにアップグレードする — 古いバージョンの MySQL には重要なインデックス改善がありません。WP Engine、Kinsta、Cloudways はいずれも MySQL 8.0 以降を実行しています

ステップ 3：カスタムストアフロント向け PostgreSQL 最適化

---

遅いクエリを特定する

-- Enable pg_stat_statements to find the worst offenders
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

-- Top 20 slowest queries by total cumulative time
SELECT
  round(total_exec_time::numeric, 2) AS total_ms,
  round(mean_exec_time::numeric, 2) AS mean_ms,
  calls,
  round((total_exec_time / sum(total_exec_time) OVER()) * 100, 2) AS pct_of_total,
  left(query, 200) AS query
FROM pg_stat_statements
WHERE calls > 100
ORDER BY total_exec_time DESC
LIMIT 20;

-- Diagnose a specific slow query
EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT p.id, p.name, p.price
FROM products p
JOIN product_categories pc ON pc.product_id = p.id
WHERE pc.category_id = 42
  AND p.price BETWEEN 1000 AND 5000
  AND p.status = 'active'
ORDER BY p.created_at DESC
LIMIT 24;
-- Look for "Seq Scan" on large tables — this means a missing index

商品フィルタリング用のインデックスを設計する

-- Partial index on active products only (smaller, faster)
CREATE INDEX CONCURRENTLY idx_products_status
  ON products (status) WHERE status = 'active';

CREATE INDEX CONCURRENTLY idx_products_price
  ON products (price) WHERE status = 'active';

-- Composite index for the most common filter combination
-- INCLUDE adds non-key columns for index-only scans (no table heap access)
CREATE INDEX CONCURRENTLY idx_products_listing
  ON products (status, brand_id, price, created_at DESC)
  INCLUDE (name, slug, thumbnail_url);

-- GIN index for flexible JSONB attribute filtering
-- Enables: attributes @> '{"color": "blue", "size": "M"}'
CREATE INDEX CONCURRENTLY idx_products_attributes
  ON products USING gin(attributes);

-- ALWAYS index foreign keys (PostgreSQL does NOT do this automatically)
CREATE INDEX CONCURRENTLY idx_product_categories_product_id
  ON product_categories (product_id);
CREATE INDEX CONCURRENTLY idx_order_lines_order_id
  ON order_lines (order_id);

注文テーブルを日付でパーティショニングする

-- Create orders table with range partitioning on created_at
CREATE TABLE orders (
  id          UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  customer_id UUID NOT NULL,
  status      TEXT NOT NULL,
  total_cents INTEGER NOT NULL,
  created_at  TIMESTAMPTZ NOT NULL DEFAULT NOW()
) PARTITION BY RANGE (created_at);

-- Quarterly partitions
CREATE TABLE orders_2025_q1 PARTITION OF orders
  FOR VALUES FROM ('2025-01-01') TO ('2025-04-01');
CREATE TABLE orders_2025_q2 PARTITION OF orders
  FOR VALUES FROM ('2025-04-01') TO ('2025-07-01');
-- (continue for Q3, Q4, 2026...)

-- Indexes on the parent propagate to all partitions
CREATE INDEX CONCURRENTLY ON orders (customer_id, created_at DESC);
CREATE INDEX CONCURRENTLY ON orders (status, created_at DESC);

読み取りをレプリカにルーティングする

// lib/database.js — two connection pools
import { Pool } from 'pg';

const primaryPool = new Pool({ connectionString: process.env.DATABASE_URL, max: 20 });
const replicaPool = new Pool({ connectionString: process.env.DATABASE_REPLICA_URL, max: 50 });

export const db = {
  // Writes and anything requiring freshness — primary
  async write(sql, params = []) {
    const result = await primaryPool.query(sql, params);
    return result.rows;
  },
  // Catalog reads — replica (slight staleness is acceptable)
  async read(sql, params = []) {
    const result = await replicaPool.query(sql, params);
    return result.rows;
  },
  // Transactions — always primary
  async transaction(fn) {
    const client = await primaryPool.connect();
    try {
      await client.query('BEGIN');
      const result = await fn(client);
      await client.query('COMMIT');
      return result;
    } catch (e) {
      await client.query('ROLLBACK');
      throw e;
    } finally {
      client.release();
    }
  },
};

読み取りを正しくルーティングする：

• カタログページ、商品検索、注文履歴 → db.read()（レプリカ）
• カート操作、チェックアウト、在庫数の減算 → db.write() または db.transaction()（プライマリ）

キーセットページネーションを使用する（大規模なカタログでは OFFSET を使わない）

-- OFFSET 10000 reads and discards 10,000 rows — slow at scale
-- Use keyset pagination instead: pass the last row's cursor values

-- First page
SELECT id, name, price, created_at FROM products
WHERE status = 'active'
ORDER BY created_at DESC, id DESC
LIMIT 24;

-- Next page (pass last row's created_at and id as cursor)
SELECT id, name, price, created_at FROM products
WHERE status = 'active'
  AND (created_at, id) < ('2025-03-01T12:00:00Z', 'uuid-of-last-row')
ORDER BY created_at DESC, id DESC
LIMIT 24;

ベストプラクティス

• EXPLAIN (ANALYZE, BUFFERS)を使用してインデックスの使用状況を検証します。EXPLAINだけでは推定値が表示されますが、ANALYZEはクエリを実行して実際の値を表示します。大きなテーブルで「Seq Scan」が発生する場合は、インデックスが不足しています。
• インデックスはCONCURRENTLYを指定して作成します。 CONCURRENTLYを指定しないと、インデックス作成中にテーブルへの書き込みがロックされるため、本番環境では必ず指定してください。
• すべての外部キーにインデックスを作成します。 PostgreSQLは外部キーに自動でインデックスを作成しません。結合テーブルのcustomer_id、order_id、product_idには、明示的にインデックスを作成する必要があります。
• work_memは慎重に設定します。 work_memを増やすとソートは高速になりますが、接続数に応じて必要量が増加します。値を引き上げる前にベンチマークを行ってください。
• 定期的にVACUUM ANALYZEを実行します。 デッドタプルによるテーブルの肥大化は、すべてのクエリを遅くします。cartsやsessionsのように書き込みの多いテーブルでは、autovacuumを積極的に設定してください。

よくある落とし穴

問題	解決策
複数列のフィルターでインデックスが使用されない	複合インデックスでは列の順序が重要です。等価条件の列（status、brand_id）を先に、範囲条件の列（price、created_at）を後に配置します。
JSONB属性のフィルタリングが遅い	@>包含クエリにはattributes列全体にGINインデックスを追加し、特定のJSONキーに対する範囲クエリには式インデックスを使用します。
リードレプリカの遅延によりカートデータが古くなる	カートの読み取りはプライマリにルーティングします。多少古いデータでも問題ないカタログや注文履歴の読み取りのみ、レプリカにルーティングします。
パーティションプルーニングが機能しない	PostgreSQLが該当しないパーティションをスキップできるよう、WHERE句にパーティションキー（created_at）を含めます。
50ページ目以降のページネーションが遅い	OFFSETを、最後の行の値をカーソルとして使うキーセットページネーションに置き換えます。

関連スキル

• @flash-sale-scaling
• @monitoring-alerting-commerce
• @ecommerce-caching
• @load-testing-commerce