MySQL表结构设计的常见坑与解决方案：基于在线考试系统的实战案例

核心实体与关系设计的常见坑及解决办法

坑点一：题干与选项混合在同一表

在早期设计中，很多方案将题干与选项文本直接放在同一张表，导致数据冗余和查询效率低下。对于在线考试系统而言，这会在加载题目集合时产生昂贵的自连接，在高并发场景下尤为明显。解决思路是将题干/题目元数据与选项文本分离，建立独立的 questions 与 options 表，通过外键关联，并对经常筛选的字段建立索引。

分离后的设计让题目管理更清晰：questions 保存题干、题型、难度等元数据，options 保存每道题的选项及是否为正确选项的标记，历史版本信息也可以通过单独字段记录，从而实现题库的可演进性与可回溯性。

-- 示例：分离表结构
CREATE TABLE exams (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  title VARCHAR(200) NOT NULL,
  duration INT NOT NULL,
  start_time DATETIME,
  end_time DATETIME,
  PRIMARY KEY (id)
) ENGINE=InnoDB;

CREATE TABLE questions (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  exam_id BIGINT UNSIGNED NOT NULL,
  type ENUM('single','multiple','fill','essay') NOT NULL,
  content TEXT NOT NULL,
  difficulty TINYINT UNSIGNED,
  version INT NOT NULL DEFAULT 1,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  FOREIGN KEY (exam_id) REFERENCES exams(id) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE options (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  question_id BIGINT UNSIGNED NOT NULL,
  content TEXT NOT NULL,
  is_correct BOOLEAN NOT NULL DEFAULT FALSE,
  PRIMARY KEY (id),
  FOREIGN KEY (question_id) REFERENCES questions(id) ON DELETE CASCADE
) ENGINE=InnoDB;

此外，版本管理也是关键点：当题目需要更新时，需保留历史版本以维护考试的一致性。可以在questions表中引入version字段，必要时将旧题干拷贝到历史表，确保历史考试分支不被后续修改破坏。

坑点二：考试与题目之间的关系冗余

直接将题目ID硬编码到考试定义中，容易导致一题在多份试卷中的冗余以及后续更新困难，且难以统计某次考试的题目集合。应通过一个专门的关联表 exam_questions 来维护考试与题目的关联，并结合版本控制，确保同一题在不同考试中的版本不同步风险可控。

通过关联表，可以灵活地为每次考试挑选合适的题目集，同时保留历史考试的完整性。

CREATE TABLE exam_questions (
  exam_id BIGINT UNSIGNED NOT NULL,
  question_id BIGINT UNSIGNED NOT NULL,
  question_version INT NOT NULL DEFAULT 1,
  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
) ENGINE=InnoDB;

在设计时应明确版本对齐的规则：同一考试的题目版本应与考试的创建时间绑定，后续对题目的版本更新不会影响已存在的考试记录，从而避免考试结果的不可重复性。

索引与查询优化在在线考试场景中的实践

坑点三：不合适的联合索引导致大表扫描

在尝试快速检索某位用户在某次考试中的答题记录时，若仅靠单字段索引，往往需要扫描大量数据，造成查询延迟。解决思路是为高频查询的字段组合建立联合索引，如 (user_id, exam_id, started_at) 或 (exam_id, user_id, started_at)，以支持按用户/考试/时间段的范围检索。

正确选择索引组合还需要考虑写入成本与覆盖率，避免走全表扫描的同时也不过度增加写入时的维护开销。

-- 示例：为 attempts 表建立高频查询的复合索引
CREATE TABLE attempts (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  user_id BIGINT UNSIGNED NOT NULL,
  exam_id BIGINT UNSIGNED NOT NULL,
  started_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  finished_at TIMESTAMP NULL,
  score DECIMAL(5,2) NULL,
  status ENUM('in_progress','finished','cancelled') NOT NULL DEFAULT 'in_progress',
  PRIMARY KEY (id),
  KEY idx_user_exam_started (user_id, exam_id, started_at),
  KEY idx_exam_status (exam_id, status)
) ENGINE=InnoDB;

通过上述联合索引，常见的查询场景（如“某用户在某考试中的最近一次完成记录”）可以显著提升响应速度，提升用户体验。

坑点四：对频繁更新字段的索引维护成本

如果一个字段在写入时频繁更新，相关索引的维护成本会变得线性增长，影响吞吐。应优先对<强>只读或较少变动的字段建立索引，对高更新频率的字段采用较小的静态列或采用覆盖索引的查询方式来降低维护成本。

设计要点包括：明确记录的更新边界、避免在高并发写入的列上堆积大量可索引字段，以及必要时将热字段缓存到应用层或缓存层，降低对数据库的直接查询压力。

数据归档、分区与长期存储的坑及落地方案

坑点五：历史数据膨胀造成性能下降

随着在线考试系统的使用，历史考试记录、答题记录会呈指数级增长，造成表扫描、索引更新成本上升，影响实时性。解决思路是对历史数据进行归档或分区，以减少单表数据量，并在需要时快速切换到归档表。

常见做法包括对 attempts、answers 等高增长表进行分区或定期分表归档，确保最近的查询仍然高效。

-- MySQL 分区示例（按 started_at 的日期分区）
CREATE TABLE attempts (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  user_id BIGINT UNSIGNED NOT NULL,
  exam_id BIGINT UNSIGNED NOT NULL,
  started_at DATETIME NOT NULL,
  finished_at DATETIME NULL,
  score DECIMAL(5,2) NULL,
  PRIMARY KEY (id, started_at)
) ENGINE=InnoDB
PARTITION BY RANGE (TO_DAYS(started_at)) (
  PARTITION p0 VALUES LESS THAN (TO_DAYS('2024-01-01')),
  PARTITION p1 VALUES LESS THAN (TO_DAYS('2025-01-01')),
  PARTITION p2 VALUES LESS THAN (MAXVALUE)
);

此外，归档表（archive_attempts、archive_answers）也常被用于长期历史数据的分离，查询时对最近数据保持高效，而历史数据可用于离线分析。

坑点六：归档与查询的一致性问题

在进行数据归档时，若未同步更新应用层的缓存、统计视图与分析表，容易导致“最近数据不一致”的情况。需要建立明确的归档时序和对外API，确保归档操作的幂等性、并伴随必要的元数据记录（如归档日期、版本号、是否已归档等）。

为保障一致性，通常将归档作为独立的异步任务或定时任务执行，避免影响在线查询路径的实时性。

数据一致性、事务与安全性在考试场景中的要点

坑点七：并发写入评分导致数据不一致

在考试评分与答题记录写入的高并发情景中，若不使用原子性操作，容易导致分数重复计算或答题状态错乱。应通过事务与行级锁来保证关键操作的原子性与可重复性。

示例场景包括在一个事务内完成对 attempts 的分数更新和对 answers 的答题记录写入，确保一个提交周期内的操作要么全部成功，要么全部回滚。

BEGIN;
UPDATE attempts
  SET score = :score
  WHERE id = :attempt_id AND status = 'in_progress';
INSERT INTO answers (attempt_id, question_id, selected_option_id, is_correct)
VALUES (:attempt_id, :question_id, :option_id, :is_correct);
COMMIT;

事务隔离级别的选择也很关键，InnoDB 的 REPEATABLE READ 通常已经足够，但在极端并发场景下可根据实际并发模型微调。

坑点八：外键约束与级联删除的权衡

外键约束能够提升数据完整性，但在大规模写入场景下，级联删除与外键检查会带来额外的锁定与检查成本。常见做法是对高写入表避免强烈的级联删除，转而采用逻辑删除（如新增 deleted_at 字段）或在应用层实现级联逻辑，以减少数据库层面的锁争用。 再决策时要结合业务可回溯性与数据清理流程。

若必须使用外键约束，可以在谨慎的范围内使用 ON DELETE CASCADE，并确保相关表的写入模式有严格的事务控制。

CREATE TABLE answers (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  attempt_id BIGINT UNSIGNED NOT NULL,
  question_id BIGINT UNSIGNED NOT NULL,
  selected_option_id BIGINT UNSIGNED,
  is_correct BOOLEAN NOT NULL,
  PRIMARY KEY (id),
  FOREIGN KEY (attempt_id) REFERENCES attempts(id) ON DELETE CASCADE,
  FOREIGN KEY (question_id) REFERENCES questions(id)
) ENGINE=InnoDB;

在需要保留历史的场景，建议采取软删除策略，并在应用层实现数据清理与归档的分离，以避免全表锁定造成的服务中断。