MySQL 表结构如何设计，打造可扩展的在线考试系统的数据模型？

本文聚焦 MySQL 表结构设计，打造可扩展的在线考试系统的数据模型，结合高并发下的可扩展性、数据一致性与安全性等关键要点，帮助你把需求落地为高效、易维护的数据库结构。

1.1 可扩展性与水平拆分

目标是 在用户规模和并发请求持续增长时，数据模型仍能保持稳定的性能与一致性。

通过实现水平拆分、分库分表、分区策略，可以将热数据与冷数据分离，降低单表热点锁竞争，并实现更高的并发处理能力。

在设计阶段，需要明确单表容量上限、跨库聚合查询的成本，以及运维复杂度之间的权衡，确保未来的扩容路径清晰可行。

1.2 数据一致性与事务边界

在线考试系统对数据一致性有较高要求，ACID 事务在写入考试成绩、题目分值与答题结果等关键操作时尤为重要。

可以把跨表操作拆分为在同一事务内完成的小型原子操作，必要时引入事件日志来实现可追溯性与 eventual consistency 的辅助机制。

通过明确的事务边界和合理的乐观/悲观锁策略，可以降低锁竞争，同时确保考试过程中的数据不可篡改或丢失。

1.3 安全性与合规性

涉及用户个人信息、考试记录和答题答案等敏感数据时，需要采取加密、访问控制与审计等措施。

使用密码哈希、盐值、以及多因素认证等安全设计，结合日志审计与最小权限原则，能提升系统的长期可用性与合规性。

在数据建模层面，可以把敏感字段分离到独立表或独立数据库，并对高频访问路径实施额外的权限校验与加密解密处理。

2. 在线考试系统核心实体与关系

2.1 用户、角色与认证模型

核心实体包括用户、角色、权限，通过关联表实现多对多关系，以支持灵活的权限分配。

认证信息应分离存储，密码哈希、盐值等敏感字段要单独保护，避免直接在业务表中暴露。

CREATE TABLE users (
  id BIGINT AUTO_INCREMENT PRIMARY KEY,
  username VARCHAR(64) NOT NULL UNIQUE,
  email VARCHAR(128) NOT NULL UNIQUE,
  password_hash VARCHAR(255) NOT NULL,
  salt VARCHAR(64) NOT NULL,
  status ENUM('active','inactive') NOT NULL DEFAULT 'active',
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

为了实现灵活的权限管理，可以将角色与权限设计为独立的表，并通过关联表实现组合授权。

CREATE TABLE roles (
  id BIGINT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(64) NOT NULL UNIQUE,
  description VARCHAR(255),
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE user_roles (
  user_id BIGINT NOT NULL,
  role_id BIGINT NOT NULL,
  PRIMARY KEY (user_id, role_id),
  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
  FOREIGN KEY (role_id) REFERENCES roles(id) ON DELETE CASCADE
);

2.2 试卷、题目与选项的关系

试卷（exams）承载若干题目，题目分为多种类型如单选、多选、填空、简答，需要通过<桥表实现题目与试卷的多对多关系。

题目的结构设计要支持可扩展的题型，以及分值分配的灵活性。

CREATE TABLE exams (
  id BIGINT AUTO_INCREMENT PRIMARY KEY,
  title VARCHAR(255) NOT NULL,
  duration_minutes INT NOT NULL,
  start_time DATETIME,
  end_time DATETIME,
  course_id BIGINT,
  status ENUM('draft','published','closed') NOT NULL DEFAULT 'draft',
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE questions (
  id BIGINT AUTO_INCREMENT PRIMARY KEY,
  exam_id BIGINT NOT NULL,
  type ENUM('single','multiple','fill','essay') NOT NULL,
  content TEXT NOT NULL,
  difficulty TINYINT UNSIGNED DEFAULT 1,
  points DECIMAL(5,2) NOT NULL DEFAULT 1.00,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (exam_id) REFERENCES exams(id) ON DELETE CASCADE
);

CREATE TABLE options (
  id BIGINT AUTO_INCREMENT PRIMARY KEY,
  question_id BIGINT NOT NULL,
  content VARCHAR(1024) NOT NULL,
  is_correct BOOLEAN NOT NULL DEFAULT FALSE,
  FOREIGN KEY (question_id) REFERENCES questions(id) ON DELETE CASCADE
);

CREATE TABLE exam_questions (
  exam_id BIGINT NOT NULL,
  question_id BIGINT NOT NULL,
  question_order INT NOT NULL,
  points DECIMAL(5,2) NOT NULL DEFAULT 1.00,
  PRIMARY KEY (exam_id, question_id),
  FOREIGN KEY (exam_id) REFERENCES exams(id) ON DELETE CASCADE,
  FOREIGN KEY (question_id) REFERENCES questions(id) ON DELETE CASCADE
);

2.3 考试记录与答题数据

考生对试卷的答题数据需要以逐题记录的方式存储，方便后续评分和分析。

针对不同题型，答案存储格式应该灵活，如文本、JSON 或结构化字段，以便快速统计和复核。

CREATE TABLE user_attempts (
  id BIGINT AUTO_INCREMENT PRIMARY KEY,
  user_id BIGINT NOT NULL,
  exam_id BIGINT NOT NULL,
  started_at DATETIME NOT NULL,
  finished_at DATETIME,
  score DECIMAL(5,2),
  status ENUM('in_progress','completed','abandoned') NOT NULL DEFAULT 'in_progress',
  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
  FOREIGN KEY (exam_id) REFERENCES exams(id) ON DELETE CASCADE,
  KEY idx_user_exam (user_id, exam_id)
);

CREATE TABLE attempt_answers (
  id BIGINT AUTO_INCREMENT PRIMARY KEY,
  attempt_id BIGINT NOT NULL,
  question_id BIGINT NOT NULL,
  answer TEXT,
  is_correct BOOLEAN,
  points_awarded DECIMAL(5,2),
  FOREIGN KEY (attempt_id) REFERENCES user_attempts(id) ON DELETE CASCADE,
  FOREIGN KEY (question_id) REFERENCES questions(id) ON DELETE CASCADE,
  INDEX idx_attempt_question (attempt_id, question_id)
);

3. 面向可扩展性的表结构设计技巧

3.1 规范化与反规范化的平衡

在大规模系统中，规范化有助于数据一致性，但查询复杂度高；适度的反规范化可以提升读取性能，尤其是在考试分数汇总、题目文本缓存等场景。

对考试系统而言，核心数据往往以读取为主，缓存热点字段与冗余字段可以显著提升响应时间，但需通过数据一致性策略进行同步。

3.2 索引与分区策略

MySQL 中，前缀索引、覆盖索引有助于减少回表开销，提升查询吞吐。

对历史数据采用范围分区或哈希分区，结合分区裁剪，可以显著降低大表的扫描成本，提升跨分区聚合的效率。

3.3 归档与冷热数据分离

将老旧的答题记录迁移到归档库，减轻主库写放大压力，是实现长期可用性的有效策略。

通过设定数据保留策略，定期归档与清理，能在不影响现有业务的前提下，确保系统的长期扩展性与可靠性。

4. MySQL 实践代码示例与最佳实践

4.1 关键表的创建与约束

以下示例展示核心表的创建要点，强调外键约束、唯一性、默认值等设计要点，帮助你在实际部署中快速落地。

CREATE INDEX idx_exams_title ON exams (title);
CREATE INDEX idx_user_id ON user_attempts (user_id);

在实际部署中，应结合数据库版本与集群架构，动态调整字符集、存储引擎、分区策略和连接配置，以实现最佳性能与可维护性。

4.2 查询优化示例

对于考试系统，常见查询包括按用户筛选的考试记录、按考试统计答题情况等。通过覆盖索引与分区裁剪，可以显著降低扫描数据量与响应时间。

-- Example: get a user's completed attempts for a specific exam
SELECT ua.id, ua.started_at, ua.finished_at, ua.score
FROM user_attempts AS ua
WHERE ua.user_id = ? AND ua.exam_id = ? AND ua.status = 'completed'
ORDER BY ua.finished_at DESC
LIMIT 20;