mysql - 哪种数据库方案在性能方面更好？

Question

----方案1----

CREATE TABLE college (
id INT AUTO_INCREMENT,
name VARCHAR(250) NOT NULL,
address VARCHAR(250),
PRIMARY KEY (id)
);

CREATE TABLE student (
college INT NOT NULL,
username VARCHAR(50) NOT NULL,
name VARCHAR(100),
FOREIGN KEY (college) REFERENCES college(id),
CONSTRAINT pk PRIMARY KEY (college,username)
);

CREATE TABLE subject (
college INT NOT NULL,
id INT NOT NULL,
name VARCHAR(100),
FOREIGN KEY (college) REFERENCES college(id),
CONSTRAINT pk PRIMARY KEY (college,id)
);

CREATE TABLE marks (
college INT NOT NULL,
student VARCHAR(50) NOT NULL,
subject INT NOT NULL,
marks INT NOT NULL,
// forget about standard for this example
FOREIGN KEY (college) REFERENCES college(id),
FOREIGN KEY (student) REFERENCES student(username),
FOREIGN KEY (subject) REFERENCES subject(id),
CONSTRAINT pk PRIMARY KEY (college,subject,student)
);

----方案2----

CREATE TABLE college (
id INT AUTO_INCREMENT,
name VARCHAR(250) NOT NULL,
address VARCHAR(250),
PRIMARY KEY (id)
);


CREATE TABLE student (
college INT NOT NULL,
id BIGINT NOT NULL AUTO_INCREMENT,
username VARCHAR(50) NOT NULL,
name VARCHAR(100),
FOREIGN KEY (college) REFERENCES college(id),
PRIMARY KEY (id)
);

CREATE TABLE subject (
college INT NOT NULL,
id BIGINT NOT NULL AUTO_INCREMENT,
name VARCHAR(100),
FOREIGN KEY (college) REFERENCES college(id),
PRIMARY KEY (id)
);

CREATE TABLE marks (
student VARCHAR(50) NOT NULL,
subject INT NOT NULL,
id BIGINT NOT NULL AUTO_INCREMENT,
marks INT NOT NULL,
// forget about standard for this example
FOREIGN KEY (student) REFERENCES student(id),
FOREIGN KEY (subject) REFERENCES subject(id),
PRIMARY KEY (id)
);

从上面的数据库方案来看，似乎方案 1 在搜索特定学生的结果时会提供更好的性能，并且在过滤结果时会更快，但感觉它并不是所有规范化的形式。另一方面，Scheme2 看起来完全正常，但可能需要更多的 JOIN 操作来获取某些结果或过滤数据。

请告诉我如果我在这里的方案有误，还请告诉我哪个更好？

Answer 1

我会选择模式 2:在引用表时，使用单个列(模式 1 中的自动递增主键)比使用列组合(模式 1 中的复合主键)更容易。另外，正如 O.Jones 评论的那样，Schema 2 假设同一所大学的两个学生不能有相同的名字，这似乎不明智。

模式 1 还存在其他问题，例如，将分数与学生相关联的外键格式不正确(您需要一个包含大学 ID 而不仅仅是学生姓名的复合外键)。

通过正确定义引用主键的外键，性能不会成为问题；加入在这种情况下表现良好。

但是 Schema 2 中应该修复一个缺陷，即在 marks 表中存储对大学的引用。您不需要这个，因为学生属于大学(学生表中有对大学的引用)。

另外，我不确定一个学科是否应该属于一个学院:难道同一门学科不可能在不同的学院教授吗？

最后，我建议为外键列提供更清晰的名称，例如 student_id 而不是 student，college_id 而不是 大学。