mysql - 关系表的复合索引

Question

我有下表:

CREATE TABLE `students` (
    `student_id`            int NOT NULL AUTO_INCREMENT,        
    `student_name`          varchar(40) NOT NULL DEFAULT '',
    PRIMARY KEY (`student_id`)
);

CREATE TABLE `courses` (
    `course_id`             int NOT NULL AUTO_INCREMENT,        
    `course_name`           varchar(40) NOT NULL DEFAULT '',
    PRIMARY KEY (`course_id`)
);

CREATE TABLE `students_courses` (
    `id`                    int NOT NULL AUTO_INCREMENT,        
    `student_id`            int NOT NULL DEFAULT '0',
    `course_id`             int NOT NULL DEFAULT '0',
    PRIMARY KEY (`id`)
);

在这里，我使用 students_courses 表来存储学生和类(class)之间的关系。因为一名学生可以注册多门类(class)。

我的疑问是，应该为该表索引什么以及如何索引。

1) 我应该像这样分别索引 student_id 和 course_id 吗:

CREATE TABLE `students_courses` (
    `id`                    int NOT NULL AUTO_INCREMENT,        
    `student_id`            int NOT NULL DEFAULT '0',
    `course_id`             int NOT NULL DEFAULT '0',
    PRIMARY KEY (`id`),
    KEY (`student_id`),
    KEY (`course_id`)
);

2) 或者，为 student_id 和 course_id 创建复合索引

CREATE TABLE `students_courses` (
    `id`                    int NOT NULL AUTO_INCREMENT,        
    `student_id`            int NOT NULL DEFAULT '0',
    `course_id`             int NOT NULL DEFAULT '0',
    PRIMARY KEY (`id`),
    KEY (`student_id`, `course_id`)
);

3) 如果使用复合键，我是否应该删除该 id 主键并使主键复合？

我主要在 JOIN 过程中使用这个关系表。所以我在这里有点困惑。

Answer 1

假设我们坚持使用自动增量 id 列作为主键。现在，我们还需要确保数据一致，即，(student_id, course_id) 值的组合不存在重复行。因此，我们需要在应用程序代码中处理这个问题(每次在插入/更新之前进行选择)，或者我们可以通过在 (student_id ，course_id)。

现在，主键基本上是一个唯一的非空键。如果您查看表定义，就会发现这个新定义的 UNIQUE 约束基本上只是一个主键(因为字段也不为 NULL)。因此，在这种特殊情况下，您实际上并不需要使用代理主键id。

随机 DML(插入/更新/删除)期间的开销差异很小，因为仅使用 UNIQUE 索引时也会有类似的开销。因此，您可以定义一个自然主复合键(student_id, course_id):

-- Drop the id column
ALTER TABLE students_courses DROP COLUMN id;

-- Add the composite Primary Key
ALTER TABLE students_courses ADD PRIMARY(student_id, course_id);

上面还将对(student_id, course_id)的组合强制执行UNIQUE约束。。此外，每行将节省 4 个字节(int 的大小为 4 个字节)。当您有大 table 时，这会很方便。

现在，当从 students 连接到 students_courses 表时，上面的主键将是一个足够的索引。但是，如果您需要从 courses 加入到 students_courses 表，则需要另一个键来实现此目的。因此，您可以在 course_id 上再定义一个键，如下所示:

ALTER TABLE students_courses ADD INDEX (course_id);

此外，您应该定义外键约束以确保数据完整性:

ALTER TABLE students_courses ADD FOREIGN KEY (student_id) 
                             REFERENCES students(student_id);

ALTER TABLE students_courses ADD FOREIGN KEY (course_id) 
                             REFERENCES courses(course_id);