关系表的综合索引 [英] Composite index for a relationship table

问题描述

我有下表:

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表存储学生"和课程"之间的关系.因为一个学生可以报读一门以上的课程.

Here, am using the students_courses table to store the relationships between the Students and Courses. Because one Student can enroll to more than one Course.

令人怀疑的是，应该为该表编制什么索引以及如何为该表编制索引.

The doubt am having is, what should be indexed and how for that table.

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

1) Shall I index student_id and course_id separately like this:

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主键并制成PRIMARY KEY复合键?

3) If going with composite key, should I remove that that id primary key and make the PRIMARY KEY composite?

我将主要在JOIN期间使用此关系表.所以这里有点困惑.

I will be using this relationship table during JOIN mainly. So am a bit confused here.

推荐答案

假设我们坚持使用自动增量id"列作为主键.现在，我们还需要确保数据一致，即 ，对于(student_id, course_id)值的组合，没有重复的行.因此，我们将需要在应用程序代码中进行处理(每次插入/更新之前都要进行选择)，或者可以通过在(student_id, course_id)上定义Composite UNIQUE约束来从结构上解决此问题.

Let's say we stick to using the Auto Increment id column as Primary Key. Now, we will also need to ensure that the data is consistent, i.e., there are no duplicate rows for a combination of (student_id, course_id) values. So, we will need to either handle this in application code (do a select every time before insert/update), or we can fix this thing structurally by defining a Composite UNIQUE constraint on (student_id, course_id).

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

Now, a Primary Key is basically a UNIQUE NOT NULL Key. If you look at your table definition, this newly defined UNIQUE constraint is basically a Primary Key only (because the fields are NOT NULL as well). So, in this particular case, you don't really need to use a Surrogate Primary key id.

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

The difference in overheads during random DML (Insert/Update/Delete) will be minimal, as you would also have similar overheads when using a UNIQUE index only. So, you can rather define a Natural Primary Composite Key (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个字节).当您有大桌子时，这将很方便.

Above will also enforce the UNIQUE constraint on the combination of (student_id, course_id). Moreover, you will save 4 bytes per row (size of int is 4 bytes). This will come handly when you would have large tables.

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

Now, while Joining from students to students_courses table, above Primary Key will be a sufficient index. However, if you need to Join from courses to students_courses table, you will need another key for this purpose. So, you can define one more key on course_id as follows:

ALTER TABLE students_courses ADD INDEX (course_id);

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

Moreover, you should define Foreign Key constraints to ensure data integrity:

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);

这篇关于关系表的综合索引的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！