database-optimization-commerce

数据库优化——电商

概述

电商数据库具有独特的查询模式：高基数商品筛选（分类 + 价格 + 属性）、会话范围内的购物车查询、高写入量的订单创建，以及必须支持并发用户扩展的高读取量商品目录浏览。本技能介绍如何识别慢查询、为商品筛选设计有效索引、对订单表进行分区，以及将读取流量路由到副本。

何时使用此技能

• 商品列表页因未建立索引的筛选组合（分类 + 价格 + 品牌）而运行缓慢时
• 结账吞吐量受订单插入延迟限制时
• 主数据库的读取负载导致写入延迟增加时
• 慢查询日志显示查询正在大型表上执行顺序扫描时
• 为新的自定义电商平台规划数据库架构时

核心说明

第 1 步：确定你的情况

数据库优化主要适用于可自托管的设置。请先了解你的限制：

平台	数据库控制权	优化方向
Shopify	无——Shopify 管理所有基础设施	专注于 Liquid 模板渲染速度、应用性能，以及通过 Search & Discovery 应用使用 Shopify 内置的查询优化
WooCommerce	完全控制——你管理主机上的 MySQL/MariaDB	使用缓存插件（Redis Object Cache、WP Rocket）优化 WooCommerce 查询，通过 WP Optimize 插件添加数据库索引，并配置主机的 MySQL 设置
BigCommerce	无——BigCommerce 管理所有基础设施	专注于主题性能、图像优化，以及减少第三方应用的开销
自定义 / Headless	完全控制——你拥有 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，以移除孤立的订单元数据、过期 transient 和文章修订版本
   • 随着时间推移，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 键的范围查询使用表达式索引
只读副本延迟导致购物车数据过时	将购物车读取路由到主库；只有在可接受轻微数据延迟的情况下，才将目录和订单历史读取路由到只读副本
分区裁剪未生效	确保 WHERE 子句包含分区键（created_at），以便 PostgreSQL 跳过无关分区
第 50 页及之后的分页速度慢	使用基于最后一行值作为游标的键集分页替代 OFFSET

相关技能

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