4. 数据准备环境：SQL环境搭建与表结构设计

模型A/B测试的数据链路从环境搭建开始。本章将指导你完成MySQL与PostgreSQL的本地/容器化部署、数据库与表结构设计，以及数据安全与权限配置。一个规范的环境是后续SQL实操与结果复现的基础。

4.1 数据库选型：MySQL vs PostgreSQL

在A/B测试场景中，MySQL与PostgreSQL各有优势。下表从关键维度对比两者，帮助你根据团队技术栈与数据规模做出选择。

4.2 环境搭建：Docker快速部署

推荐使用Docker进行本地环境搭建，避免污染宿主机，且可一键重置。以下为两种数据库的docker-compose配置。

4.2.1 MySQL 8.0 部署

version: '3.8'
services:
  mysql:
    image: mysql:8.0
    container_name: abtest_mysql
    environment:
      MYSQL_ROOT_PASSWORD: root123
      MYSQL_DATABASE: abtest_db
      MYSQL_USER: ab_user
      MYSQL_PASSWORD: ab_pass
    ports:
      - "3306:3306"
    volumes:
      - ./mysql_data:/var/lib/mysql
      - ./init_sql:/docker-entrypoint-initdb.d
    command: --default-authentication-plugin=mysql_native_password

4.2.2 PostgreSQL 15 部署

version: '3.8'
services:
  postgres:
    image: postgres:15
    container_name: abtest_pg
    environment:
      POSTGRES_DB: abtest_db
      POSTGRES_USER: ab_user
      POSTGRES_PASSWORD: ab_pass
    ports:
      - "5432:5432"
    volumes:
      - ./pg_data:/var/lib/postgresql/data
      - ./init_sql:/docker-entrypoint-initdb.d

4.3 数据库与表结构设计

模型A/B测试的核心数据模型包含三个主题：实验配置、用户分流、指标事件。以下为通用表结构设计，兼容MySQL与PostgreSQL。

4.3.1 实验配置表 (experiments)

CREATE TABLE experiments (
    experiment_id   VARCHAR(32) PRIMARY KEY,
    experiment_name VARCHAR(128) NOT NULL,
    description     TEXT,
    status          VARCHAR(16) DEFAULT 'running',  -- running, paused, ended
    start_time      TIMESTAMP NOT NULL,
    end_time        TIMESTAMP,
    created_at      TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

COMMENT ON TABLE experiments IS 'A/B实验配置表';
COMMENT ON COLUMN experiments.status IS '实验状态: running/paused/ended';

4.3.2 实验分组表 (experiment_groups)

CREATE TABLE experiment_groups (
    group_id        VARCHAR(32) PRIMARY KEY,
    experiment_id   VARCHAR(32) NOT NULL REFERENCES experiments(experiment_id),
    group_name      VARCHAR(64) NOT NULL,   -- control / treatment_1 / treatment_2
    group_type      VARCHAR(16) DEFAULT 'treatment',  -- control / treatment
    traffic_ratio   DECIMAL(5,4) NOT NULL,  -- 0.5000 表示50%流量
    created_at      TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

COMMENT ON TABLE experiment_groups IS '实验分组与流量比例';

4.3.3 用户分流表 (user_assignments)

CREATE TABLE user_assignments (
    id              BIGSERIAL PRIMARY KEY,  -- MySQL使用 BIGINT AUTO_INCREMENT
    user_id         VARCHAR(64) NOT NULL,
    experiment_id   VARCHAR(32) NOT NULL REFERENCES experiments(experiment_id),
    group_id        VARCHAR(32) NOT NULL REFERENCES experiment_groups(group_id),
    assigned_at     TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    UNIQUE (user_id, experiment_id)
);

CREATE INDEX idx_user_exp ON user_assignments(user_id, experiment_id);
COMMENT ON TABLE user_assignments IS '用户分流记录，保证每个实验内用户唯一';

4.3.4 事件表 (events)

CREATE TABLE events (
    event_id        BIGSERIAL PRIMARY KEY,
    user_id         VARCHAR(64) NOT NULL,
    experiment_id   VARCHAR(32) NOT NULL,
    group_id        VARCHAR(32) NOT NULL,
    event_name      VARCHAR(64) NOT NULL,   -- page_view, click, conversion, revenue
    event_value     DECIMAL(12,4),          -- 数值型指标，如金额、时长
    event_time      TIMESTAMP NOT NULL,
    properties      JSONB,                  -- 扩展属性，MySQL使用JSON
    created_at      TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX idx_event_time ON events(experiment_id, event_time);
CREATE INDEX idx_event_name ON events(experiment_id, event_name);
COMMENT ON TABLE events IS '用户行为事件表，记录所有埋点数据';

4.4 数据安全与权限管理

A/B测试数据涉及用户ID与行为，必须遵循最小权限原则。以下为权限配置最佳实践。

4.4.1 用户角色划分

4.4.2 MySQL权限示例

-- 创建分析师角色
CREATE USER 'analyst'@'%' IDENTIFIED BY 'analyst_pass';
GRANT SELECT ON abtest_db.experiments TO 'analyst'@'%';
GRANT SELECT ON abtest_db.experiment_groups TO 'analyst'@'%';
GRANT SELECT ON abtest_db.user_assignments TO 'analyst'@'%';
GRANT SELECT ON abtest_db.events TO 'analyst'@'%';
FLUSH PRIVILEGES;

-- 创建数据工程师角色
CREATE USER 'engineer'@'%' IDENTIFIED BY 'engineer_pass';
GRANT SELECT, INSERT, UPDATE ON abtest_db.user_assignments TO 'engineer'@'%';
GRANT SELECT, INSERT ON abtest_db.events TO 'engineer'@'%';
GRANT SELECT ON abtest_db.experiments TO 'engineer'@'%';
FLUSH PRIVILEGES;

4.4.3 PostgreSQL权限示例

-- 创建分析师角色
CREATE ROLE analyst WITH LOGIN PASSWORD 'analyst_pass';
GRANT CONNECT ON DATABASE abtest_db TO analyst;
GRANT USAGE ON SCHEMA public TO analyst;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO analyst;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO analyst;

-- 创建数据工程师角色
CREATE ROLE engineer WITH LOGIN PASSWORD 'engineer_pass';
GRANT CONNECT ON DATABASE abtest_db TO engineer;
GRANT USAGE ON SCHEMA public TO engineer;
GRANT SELECT, INSERT, UPDATE ON user_assignments, events TO engineer;
GRANT SELECT ON experiments, experiment_groups TO engineer;

4.5 环境架构总览

下图展示了从Docker部署到表结构、权限控制的完整数据环境架构。

4.6 初始化数据脚本示例

环境搭建完成后，建议执行以下初始化脚本插入一条实验配置，用于后续SQL实操验证。

-- 插入实验配置
INSERT INTO experiments (experiment_id, experiment_name, description, status, start_time)
VALUES ('exp_001', '首页推荐算法V2', '对比新版推荐算法与旧版在点击率上的差异', 'running', NOW());

-- 插入两个分组：对照组50%，实验组50%
INSERT INTO experiment_groups (group_id, experiment_id, group_name, group_type, traffic_ratio)
VALUES 
('grp_control', 'exp_001', 'control', 'control', 0.5000),
('grp_treatment', 'exp_001', 'treatment_v2', 'treatment', 0.5000);

-- 插入模拟分流数据（10个用户）
INSERT INTO user_assignments (user_id, experiment_id, group_id)
SELECT 
    'user_' || LPAD(CAST(n AS TEXT), 4, '0'),
    'exp_001',
    CASE WHEN n % 2 = 0 THEN 'grp_control' ELSE 'grp_treatment' END
FROM generate_series(1, 10) AS n;