资讯

历史

科技

环境与自然

成长

游戏

财经

文学与艺术

美食

健康

家居

文化

情感

汽车

三农

军事

旅行

运动

教育

生活

星座命理

数据库设计必修课：搞懂表关系，提升查询效率！

创作时间:

作者:

@小白创作中心

数据库设计必修课：搞懂表关系，提升查询效率！

引用

CSDN

https://m.blog.csdn.net/zgt_certificate/article/details/140939624

关系型数据库中表与表之间的三种基本关系：1:1关系、1:N关系、N:M关系。理解这些关系对于数据库设计、SQL查询优化和分表分库都有重要作用。

表与表之间的三种关系

1:1关系（One-to-One）

每个表中的一条记录对应另一个表中的唯一一条记录。
例子：一个用户表和用户详细信息表，每个用户在两表中只有一条记录。

1:N关系（One-to-Many）

一个表中的一条记录对应另一个表中的多条记录。
例子：一个部门表和员工表，一个部门可以有多个员工。

N:M关系（Many-to-Many）

一个表中的多条记录可以对应另一个表中的多条记录。
例子：学生表和课程表，一个学生可以选多门课程，一门课程也可以有多个学生。

关系判断与SQL优化

在数据库设计中，识别表之间的关系类型对于优化数据库结构和SQL查询至关重要。下面具体分析这些关系及其在SQL优化中的应用。

如何判断表之间的关系

通过对表的关键列进行汇总统计，可以判断两表间的关系类型。文中以

emp

和

dept

表为例，通过统计

deptno

字段的数量来判断关系：

emp

表中的

deptno

统计结果：


DEPTNO  COUNT(*)
----------------
30      6
20      5
10      3

dept

表中的

deptno

统计结果：


DEPTNO  COUNT(*)
----------------
10      1
40      1
30      1
20      1

通过比较发现，

emp

表中

deptno

出现了多次，而

dept

表中每个

deptno

只出现了一次，因此

emp

与

dept

是1:N关系。

示例分析

示例1：1:1关系优化

有两个表

users

和

user_profiles

，其中每个用户在两个表中都有唯一的记录：

users

表：

user_id

username

user_profiles

表：

user_id

profile_picture

原始SQL：


SELECT u.username, p.profile_picture 
FROM users u 
LEFT JOIN user_profiles p 
ON u.user_id = p.user_id;

问题：即使是1:1关系，使用

LEFT JOIN

可能引入不必要的复杂性。

优化建议：

当确定是1:1关系时，可以省略

LEFT JOIN

而直接使用

INNER JOIN

，以确保只有匹配记录才被返回：


SELECT u.username, p.profile_picture 
FROM users u 
INNER JOIN user_profiles p 
ON u.user_id = p.user_id;

示例2：1:N关系优化

在

departments

表和

employees

表中，一个部门可以有多个员工：

departments

表：

dept_id

dept_name

employees

表：

emp_id

dept_id

emp_name

原始SQL：


SELECT d.dept_name, e.emp_name 
FROM departments d 
JOIN employees e 
ON d.dept_id = e.dept_id;

注意：在这种情况下，应该使用合适的索引来优化连接，例如在

employees.dept_id

上创建索引，以提高连接性能。

优化建议：

使用索引和减少不必要的字段，优化查询性能：


CREATE INDEX idx_emp_dept ON employees(dept_id);
SELECT d.dept_name, e.emp_name 
FROM departments d 
JOIN employees e 
ON d.dept_id = e.dept_id;

示例3：N:M关系优化

在学生选课系统中，一个学生可以选多门课，一门课可以被多个学生选修。使用中间表

student_courses

来表示这种关系：

students

表：

student_id

student_name

courses

表：

course_id

course_name

student_courses

表：

student_id

course_id

原始SQL：


SELECT s.student_name, c.course_name 
FROM students s 
JOIN student_courses sc ON s.student_id = sc.student_id 
JOIN courses c ON sc.course_id = c.course_id;

问题：这种关系通常需要进行复杂的多表连接，适当的索引和中间表结构设计可以极大提高查询效率。

优化建议：

在

student_courses

表上创建组合索引，以优化连接操作：


CREATE INDEX idx_sc ON student_courses(student_id, course_id);
SELECT s.student_name, c.course_name 
FROM students s 
JOIN student_courses sc ON s.student_id = sc.student_id 
JOIN courses c ON sc.course_id = c.course_id;