行业案例：6 个典型场景的 schema 与配置

电商 / SaaS 多租户 / 游戏 / IoT 时序 / Fintech / 内容站——每个给完整 schema + 调参 + 踩坑

每个案例都做了简化但结构是真实生产级的。配合 InnoDB 内部和优化器阅读效果最佳。

1. 电商

关键挑战

高写并发（下单峰值）
库存扣减必须强一致
订单状态机复杂
历史数据爆炸

核心 schema

CREATE TABLE products (
  id           BIGINT AUTO_INCREMENT PRIMARY KEY,
  sku          VARCHAR(64) NOT NULL UNIQUE,
  name         VARCHAR(255) NOT NULL,
  price_cents  INT NOT NULL,
  stock        INT NOT NULL DEFAULT 0,
  version      INT NOT NULL DEFAULT 0,       -- 乐观锁
  created_at   DATETIME DEFAULT CURRENT_TIMESTAMP,
  updated_at   DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB COMMENT='商品';

CREATE TABLE orders (
  id            BIGINT AUTO_INCREMENT PRIMARY KEY,
  user_id       BIGINT NOT NULL,
  total_cents   INT NOT NULL,
  status        ENUM('pending','paid','shipped','delivered','cancelled','refunded') NOT NULL DEFAULT 'pending',
  paid_at       DATETIME NULL,
  shipped_at    DATETIME NULL,
  created_at    DATETIME DEFAULT CURRENT_TIMESTAMP,
  updated_at    DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  KEY idx_user_created (user_id, created_at DESC),
  KEY idx_status_created (status, created_at)
) ENGINE=InnoDB
  PARTITION BY RANGE (TO_DAYS(created_at)) (
    PARTITION p202601 VALUES LESS THAN (TO_DAYS('2026-02-01')),
    PARTITION p202602 VALUES LESS THAN (TO_DAYS('2026-03-01')),
    PARTITION pmax VALUES LESS THAN MAXVALUE
  );

CREATE TABLE order_items (
  id          BIGINT AUTO_INCREMENT PRIMARY KEY,
  order_id    BIGINT NOT NULL,
  product_id  BIGINT NOT NULL,
  qty         INT NOT NULL,
  price_cents INT NOT NULL,                   -- 下单时快照价
  KEY idx_order (order_id),
  CONSTRAINT fk_oi_order FOREIGN KEY (order_id) REFERENCES orders(id) ON DELETE CASCADE
) ENGINE=InnoDB;

库存扣减（乐观锁）

START TRANSACTION;
SELECT stock, version FROM products WHERE id = 42;  -- 读到 stock=10, version=5
-- 应用层判断 stock >= qty
UPDATE products SET stock = stock - 1, version = version + 1
WHERE id = 42 AND version = 5;
-- 影响行数 = 0 说明被别人改过，回滚重试
COMMIT;

或悲观锁：

START TRANSACTION;
SELECT stock FROM products WHERE id = 42 FOR UPDATE;
UPDATE products SET stock = stock - 1 WHERE id = 42 AND stock >= 1;
COMMIT;

调参

[mariadb]
innodb_buffer_pool_size=8G        # 商品 + 订单热数据
innodb_flush_log_at_trx_commit=1  # 强持久性
sync_binlog=1
innodb_io_capacity=4000           # SSD
innodb_io_capacity_max=8000
max_connections=500
slow_query_log=1
long_query_time=0.5

踩坑

status 用字符串而非 ENUM → 索引失效、可读性差
不分区 → 一年后 orders 表 5 亿行，索引爆
不分离热数据 → 七天前订单 70% 查询不到，浪费 buffer pool
历史订单冷归档建议：MariaDB ColumnStore 引擎做归档表

详见事务、索引。

2. SaaS 多租户

三种 schema 策略

策略	优	劣
A: 全表加 `tenant_id`	资源共享，运维简单	数据隔离弱
B: 每租户独立 schema	隔离强，备份独立	上千租户难扩
C: 每租户独立实例	完全隔离	成本高

99% 场景选 A。MariaDB 在单库千万级行下表现稳定。

Schema 模板

CREATE TABLE workspaces (
  id           BIGINT AUTO_INCREMENT PRIMARY KEY,
  slug         VARCHAR(64) NOT NULL UNIQUE,
  plan         ENUM('free','starter','pro','enterprise') NOT NULL DEFAULT 'free',
  created_at   DATETIME DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE documents (
  id            BIGINT AUTO_INCREMENT PRIMARY KEY,
  workspace_id  BIGINT NOT NULL,
  title         VARCHAR(255),
  content       LONGTEXT,
  deleted_at    DATETIME NULL,
  created_at    DATETIME DEFAULT CURRENT_TIMESTAMP,
  updated_at    DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  KEY idx_ws_updated (workspace_id, updated_at DESC),
  KEY idx_ws_deleted (workspace_id, deleted_at)
) ENGINE=InnoDB;

索引规则

每个查询路径都必须带 workspace_id 作为最左前缀：

-- ✅
SELECT * FROM documents WHERE workspace_id = ? AND id = ?;
SELECT * FROM documents WHERE workspace_id = ? ORDER BY updated_at DESC LIMIT 20;

-- ❌ 没带 workspace_id，全表扫
SELECT * FROM documents WHERE id = ?;

应用层强制 tenant 隔离

// Drizzle 中间件
function tenantScope(workspaceId: bigint) {
  return {
    documents: db.select().from(documents).where(eq(documents.workspaceId, workspaceId)),
    // ...
  };
}

或用 view 包装：

CREATE SQL SECURITY DEFINER VIEW ws_42_documents AS
SELECT * FROM documents WHERE workspace_id = 42;

数据导出 / 删除（GDPR）

-- 导出
SELECT * FROM documents WHERE workspace_id = ?;

-- 软删
UPDATE documents SET deleted_at = NOW() WHERE workspace_id = ?;

-- 永久删（30 天后）
DELETE FROM documents WHERE workspace_id = ? AND deleted_at < NOW() - INTERVAL 30 DAY;

3. 游戏

关键挑战

玩家状态频繁更新
排行榜 / leaderboard
道具背包大对象
跨区合服

玩家状态

CREATE TABLE players (
  id          BIGINT AUTO_INCREMENT PRIMARY KEY,
  account_id  BIGINT NOT NULL,
  server_id   INT NOT NULL,
  level       INT NOT NULL DEFAULT 1,
  exp         BIGINT NOT NULL DEFAULT 0,
  gold        BIGINT NOT NULL DEFAULT 0,
  last_login  DATETIME,
  KEY idx_account (account_id),
  KEY idx_server_level (server_id, level DESC)
) ENGINE=InnoDB;

背包（JSON 列 + 函数索引）

CREATE TABLE player_inventory (
  player_id   BIGINT PRIMARY KEY,
  items       JSON NOT NULL,                 -- {"sword":3, "potion":12, ...}
  updated_at  DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB;

复杂查询用关系表 items (player_id, item_id, qty) 替代 JSON——看读写比例。

排行榜（实时）

SELECT player_id, level, exp,
       RANK() OVER (PARTITION BY server_id ORDER BY level DESC, exp DESC) AS rk
FROM players WHERE server_id = 1
LIMIT 100;

注意：排行榜流量集中，缓存到 Redis 是常见做法。MariaDB 只负责权威数据。

合服

拆库 → 中心库：DTS / Debezium
ID 冲突：所有 ID 分配 server_id 高位（(server_id << 48) | local_id）

4. IoT / 时序数据

挑战

写入量大（每秒万级到百万级）
读以时间范围为主
老数据归档

Schema（分区 + ColumnStore 归档）

CREATE TABLE sensor_readings (
  device_id  BIGINT NOT NULL,
  ts         DATETIME(3) NOT NULL,           -- 毫秒精度
  metric     VARCHAR(32) NOT NULL,
  value      DOUBLE NOT NULL,
  PRIMARY KEY (device_id, ts, metric)
) ENGINE=InnoDB
  PARTITION BY RANGE (TO_DAYS(ts)) (
    PARTITION p20260101 VALUES LESS THAN (TO_DAYS('2026-01-02')),
    PARTITION p20260102 VALUES LESS THAN (TO_DAYS('2026-01-03')),
    -- ... 每天一个分区
    PARTITION pmax VALUES LESS THAN MAXVALUE
  );

写优化

innodb_flush_log_at_trx_commit=2   # 容忍 1 秒数据丢失换吞吐
sync_binlog=0                       # 配合 GTID 应用层重试
innodb_io_capacity=8000
bulk_insert_buffer_size=256M

应用层批量写：

// 每 100 条或 1 秒 flush 一次
const batch: any[] = [];
function record(reading) {
  batch.push(reading);
  if (batch.length >= 100) flush();
}
setInterval(flush, 1000);

归档到 ColumnStore

-- 当 InnoDB 分区超过 30 天，转入 ColumnStore
INSERT INTO sensor_readings_cs SELECT * FROM sensor_readings_2026_04;
ALTER TABLE sensor_readings DROP PARTITION p20260401;

ColumnStore 对聚合查询快 10–100×。

5. Fintech

关键挑战

强一致 + 强持久
审计完整
数据不可篡改

双账本 + 不可变流水

CREATE TABLE accounts (
  id          BIGINT AUTO_INCREMENT PRIMARY KEY,
  owner_id    BIGINT NOT NULL,
  currency    CHAR(3) NOT NULL,
  balance_cents BIGINT NOT NULL DEFAULT 0,
  version     BIGINT NOT NULL DEFAULT 0,
  KEY idx_owner (owner_id)
) ENGINE=InnoDB;

CREATE TABLE journal (
  id           BIGINT AUTO_INCREMENT PRIMARY KEY,
  from_account BIGINT NOT NULL,
  to_account   BIGINT NOT NULL,
  amount_cents BIGINT NOT NULL,
  ref          VARCHAR(64) NOT NULL UNIQUE,    -- 幂等键
  created_at   DATETIME(6) DEFAULT CURRENT_TIMESTAMP(6),
  KEY idx_from_ts (from_account, created_at),
  KEY idx_to_ts (to_account, created_at)
) ENGINE=InnoDB;

转账（强一致）

START TRANSACTION;
INSERT INTO journal (from_account, to_account, amount_cents, ref) VALUES (?,?,?,?);
UPDATE accounts SET balance_cents = balance_cents - ?, version = version + 1
WHERE id = ? AND balance_cents >= ?;
-- 应用层检查 affected_rows = 1
UPDATE accounts SET balance_cents = balance_cents + ?, version = version + 1 WHERE id = ?;
COMMIT;

配置

innodb_flush_log_at_trx_commit=1
sync_binlog=1
binlog_format=ROW
gtid_strict_mode=ON
innodb_strict_mode=ON
sql_mode='STRICT_ALL_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_ZERO_DATE,NO_ZERO_IN_DATE,NO_ENGINE_SUBSTITUTION'

审计

-- 启用 audit plugin
INSTALL SONAME 'server_audit';
SET GLOBAL server_audit_logging=ON;
SET GLOBAL server_audit_events='QUERY_DDL,QUERY_DML,CONNECT';

备份

每小时 PITR + 异地副本，测试 restore 演练每月一次。

6. 内容站（CMS / 博客 / 论坛）

Schema

CREATE TABLE posts (
  id          BIGINT AUTO_INCREMENT PRIMARY KEY,
  author_id   BIGINT NOT NULL,
  slug        VARCHAR(255) NOT NULL UNIQUE,
  title       VARCHAR(255) NOT NULL,
  body        LONGTEXT NOT NULL,
  status      ENUM('draft','published','archived') NOT NULL DEFAULT 'draft',
  views       BIGINT NOT NULL DEFAULT 0,
  published_at DATETIME NULL,
  created_at  DATETIME DEFAULT CURRENT_TIMESTAMP,
  KEY idx_author (author_id),
  KEY idx_status_pub (status, published_at DESC),
  FULLTEXT KEY ft_title_body (title, body) WITH PARSER ngram   -- 中文搜索
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE comments (
  id         BIGINT AUTO_INCREMENT PRIMARY KEY,
  post_id    BIGINT NOT NULL,
  parent_id  BIGINT NULL,                       -- 树状评论
  author_id  BIGINT NOT NULL,
  body       TEXT NOT NULL,
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  KEY idx_post_parent (post_id, parent_id)
) ENGINE=InnoDB;

全文搜索

SELECT id, title FROM posts
WHERE status = 'published'
  AND MATCH(title, body) AGAINST('MariaDB 性能调优' IN NATURAL LANGUAGE MODE)
LIMIT 20;

views 计数（避免锁竞争）

-- ❌ 每次访问 UPDATE 同一行 → 热点行
UPDATE posts SET views = views + 1 WHERE id = ?;

-- ✅ 异步累计：Redis 计数 + 每分钟 flush
-- 或：分散到多行
UPDATE posts SET views = views + 1 WHERE id = ? ORDER BY RAND() LIMIT 1;  -- 不行，得用 shard

更好做法：用向量 + FULLTEXT 混合提升相关度。

选不同行业的共同建议

字符集永远 utf8mb4
金额永远 _cents BIGINT
主键永远 BIGINT AUTO_INCREMENT 或 UUID v7
时间永远 DATETIME DEFAULT CURRENT_TIMESTAMP
每查询路径都要有覆盖索引
每张表写 COMMENT，每列写 COMMENT（详见 LLM 友好 Schema）
慢查询阈值 0.5–1 秒，每天 review
备份真实演练，不只是有

行业案例：6 个典型场景的 schema 与配置

1. 电商

关键挑战

核心 schema

库存扣减（乐观锁）

调参

踩坑

2. SaaS 多租户

三种 schema 策略

Schema 模板

索引规则

应用层强制 tenant 隔离

数据导出 / 删除（GDPR）

3. 游戏

关键挑战

玩家状态

背包（JSON 列 + 函数索引）

排行榜（实时）

合服

4. IoT / 时序数据

挑战

Schema（分区 + ColumnStore 归档）

写优化

归档到 ColumnStore

5. Fintech

关键挑战

双账本 + 不可变流水

转账（强一致）

配置

审计

备份

6. 内容站（CMS / 博客 / 论坛）

Schema

全文搜索

views 计数（避免锁竞争）

选不同行业的共同建议

延伸

本页目录