MySQL—多表操作详解
在 MySQL 中，多表操作通常涉及联接（JOIN）和子查询（Subquery），用于处理来自多个表的数据。
约束分类
约束介绍
约束：用于对数据库表中的数据进行限定，确保数据的正确性、有效性和完整性。
约束的分类
| 约束 | 说明 |
|---|---|
| PRIMARY KEY | 主键约束，唯一标识每行数据 |
| PRIMARY KEY AUTO_INCREMENT | 主键自增，自动为新数据生成唯一标识符 |
| UNIQUE | 唯一约束，保证字段值在表中唯一 |
| NOT NULL | 非空约束，确保字段不能为空 |
| FOREIGN KEY | 外键约束，确保引用其他表中的主键 |
| FOREIGN KEY ON UPDATE CASCADE | 外键级联更新，引用的主键更新时自动更新 |
| FOREIGN KEY ON DELETE CASCADE | 外键级联删除，引用的主键删除时自动删除 |
主键约束
主键约束用于唯一标识表中的每一行数据，并自动包含非空和唯一两个特性。
主键约束特点：
- 主键列中的数据必须唯一，且不能包含空值。
- 一张表只能有一个主键。
- 主键通常用于标识表中的每一条记录。
建表时添加主键约束
CREATE TABLE 表名 (
列名 数据类型 PRIMARY KEY,
列名 数据类型,
...
);
删除主键约束
ALTER TABLE 表名 DROP PRIMARY KEY;
建表后单独添加主键约束
ALTER TABLE 表名 MODIFY 列名 数据类型 PRIMARY KEY;
主键自增
主键自增约束在插入数据时，主键列的值会自动增加，通常用于数值型主键。
主键自增的特点：
- 主键自增列可以为空，但插入新数据时会自动分配一个唯一的值。
- 删除记录不会影响自增序列，下一条记录仍然基于先前的最大值递增。
建表时添加主键自增约束
CREATE TABLE 表名 (
列名 数据类型 PRIMARY KEY AUTO_INCREMENT,
列名 数据类型,
...
);
删除主键自增约束
ALTER TABLE 表名 MODIFY 列名 数据类型;
建表后单独添加主键自增约束
ALTER TABLE 表名 MODIFY 列名 数据类型 AUTO_INCREMENT;
唯一约束
唯一约束确保某列中的数据不会重复。
建表时添加唯一约束
CREATE TABLE 表名 (
列名 数据类型 UNIQUE,
列名 数据类型,
...
);
删除唯一约束
ALTER TABLE 表名 DROP INDEX 列名;
建表后单独添加唯一约束
ALTER TABLE 表名 MODIFY 列名 数据类型 UNIQUE;
非空约束
非空约束确保字段不能为空。
建表时添加非空约束
CREATE TABLE 表名 (
列名 数据类型 NOT NULL,
列名 数据类型,
...
);
删除非空约束
ALTER TABLE 表名 MODIFY 列名 数据类型;
建表后单独添加非空约束
ALTER TABLE 表名 MODIFY 列名 数据类型 NOT NULL;
外键约束
外键约束用于定义表与表之间的关系，确保数据的完整性和一致性。
建表时添加外键约束
CREATE TABLE 表名 (
列名 数据类型 约束,
...
CONSTRAINT 外键名 FOREIGN KEY (本表外键列名) REFERENCES 主表名(主表主键列名)
);
删除外键约束
ALTER TABLE 表名 DROP FOREIGN KEY 外键名;
建表后单独添加外键约束
ALTER TABLE 表名 ADD CONSTRAINT 外键名 FOREIGN KEY (本表外键列名) REFERENCES 主表名(主表主键列名);
外键级联操作
级联操作用于定义当主表中的数据发生变化时从表中数据的响应方式。
- RESTRICT 和 NO ACTION：如果从表有相关记录，则禁止对主表进行删除或更新操作。
- CASCADE：当主表中的数据更新或删除时，从表中对应的数据也将被更新或删除。
- SET NULL：当主表中的数据更新或删除时，从表中的对应字段会被设置为 NULL。
添加级联更新
ALTER TABLE 表名 ADD CONSTRAINT 外键名 FOREIGN KEY (本表外键列名) REFERENCES 主表名(主表主键列名) ON UPDATE [CASCADE | RESTRICT | SET NULL];
添加级联删除
ALTER TABLE 表名 ADD CONSTRAINT 外键名 FOREIGN KEY (本表外键列名) REFERENCES 主表名(主表主键列名) ON DELETE CASCADE;
同时添加级联更新和级联删除
ALTER TABLE 表名 ADD CONSTRAINT 外键名 FOREIGN KEY (本表外键列名) REFERENCES 主表名(主表主键列名) ON UPDATE CASCADE ON DELETE CASCADE;
多表设计
在数据库设计中，多表之间的关联关系通常分为三类：一对一、一对多、多对多。这些关系通过外键约束来实现，确保表与表之间数据的逻辑一致性。
一对一关系
一对一关系是指两个表中的每一行数据在另一个表中只有一条对应记录。常见的例子是人和身份证，每个人对应一个唯一的身份证。
实现原则：
在任意一个表中建立外键，用于关联另一张表的主键，确保两个表的一对一关系。
-- 人表
CREATE TABLE Person (
PersonID INT PRIMARY KEY,
Name VARCHAR(50)
-- 其他字段
);
-- 身份证表
CREATE TABLE IDCard (
IDCardID INT PRIMARY KEY,
PersonID INT,
CardNumber VARCHAR(20),
-- 建立与人表的外键关系，确保数据一致性
FOREIGN KEY (PersonID) REFERENCES Person(PersonID)
);
一对多关系
一对多关系是最常见的关系类型，指一个表中的一条记录可以对应另一个表中的多条记录。例如：用户和订单，一个用户可以有多个订单；商品分类和商品，一个分类下可以有多个商品。
实现原则：
在多的一方表中建立外键，关联到一的一方的主键。即多的一方通过外键约束和一的一方建立联系。
-- 用户表
CREATE TABLE User (
UserID INT PRIMARY KEY, -- 用户ID，主键
Name VARCHAR(50) -- 用户姓名
-- 其他字段
);
-- 订单表
CREATE TABLE Order (
OrderID INT PRIMARY KEY, -- 订单ID，主键
UserID INT, -- 用户ID，与用户表的UserID关联
OrderDate DATE, -- 订单日期
FOREIGN KEY (UserID) REFERENCES User(UserID) -- 外键约束，确保订单表中的UserID引用用户表中的UserID
);
多对多关系
多对多关系通常出现在两个表中都有多个关联记录的场景。例如：学生和课程，一个学生可以选修多门课程，而一门课程可以被多个学生选修。
实现原则：
通过中间表（也叫关联表）来实现。中间表至少包含两个外键，分别关联到两张主表的主键。
-- 学生表
-- 该表存储学生的相关信息
CREATE TABLE Student (
StudentID INT PRIMARY KEY, -- 学生ID，作为主键
Name VARCHAR(50), -- 学生姓名
-- 其他字段
);
-- 课程表
-- 该表存储课程的相关信息
CREATE TABLE Course (
CourseID INT PRIMARY KEY, -- 课程ID，作为主键
CourseName VARCHAR(50), -- 课程名称
-- 其他字段
);
-- 中间表：学生课程关系表
-- 该表用于关联学生与所选课程的关系
CREATE TABLE StudentCourse (
StudentID INT, -- 学生ID，外键关联到学生表
CourseID INT, -- 课程ID，外键关联到课程表
PRIMARY KEY (StudentID, CourseID), -- 联合主键，确保每条记录唯一
FOREIGN KEY (StudentID) REFERENCES Student(StudentID), -- 外键约束
FOREIGN KEY (CourseID) REFERENCES Course(CourseID) -- 外键约束
);
总结
- 一对一：在任意一个表中建立外键，关联另一个表的主键。
- 一对多：在多的一方建立外键，关联一的一方的主键。
- 多对多：通过中间表来记录两张表之间的多对多关系，中间表包含两个外键，分别关联两张表的主键。
连接查询
连接查询是数据库中用于从多个表中获取数据的一种操作。通过连接查询，可以将多个表中的数据根据特定的条件组合在一起，从而获取更全面的信息。常见的连接类型包括内连接（INNER JOIN）、左连接（LEFT JOIN）、右连接（RIGHT JOIN）和全外连接（FULL OUTER JOIN）。
内连接（INNER JOIN）
定义：连接查询的是两张表有交集的部分数据，两张表分为驱动表和被驱动表，如果结果集中的每条记录都是两个表相互匹配的组合，则称这样的结果集为笛卡尔积 。
显式内连接
显式内连接使用 INNER JOIN 关键字，明确指定连接条件。
语法：
SELECT 列名
FROM 表名1
[INNER] JOIN 表名2
ON 条件;隐式内连接
隐式内连接使用 WHERE 子句来指定连接条件，而不是使用 INNER JOIN 关键字。在某些情况下，WHERE 子句和 ON 子句是等价的。
语法：
SELECT 列名
FROM 表名1, 表名2
WHERE 条件;STRAIGHT_JOIN
STRAIGHT_JOIN 与 JOIN 类似，但它强制数据库引擎按照指定的顺序读取表。具体来说，左表始终在右表之前读取。STRAIGHT_JOIN 只适用于内连接。
语法：
SELECT 列名
FROM 表名1 STRAIGHT_JOIN 表名2
ON 条件;总结
- 显式内连接：使用 INNER JOIN 关键字，明确指定连接条件。
- 隐式内连接：使用 WHERE 子句来指定连接条件，而不是使用 INNER JOIN 关键字。
- STRAIGHT_JOIN：强制数据库引擎按照指定的顺序读取表，左表始终在右表之前读取，只适用于内连接。
左连接（LEFT JOIN）
定义：左连接返回左表中的所有行，以及右表中满足连接条件的行。如果右表中没有匹配的行，则返回NULL。
语法：
SELECT 列名
FROM 表1
LEFT JOIN 表2
ON 表1.列名 = 表2.列名;右连接（RIGHT JOIN）
定义：右连接返回右表中的所有行，以及左表中满足连接条件的行。如果左表中没有匹配的行，则返回NULL。
语法：
SELECT 列名
FROM 表1
RIGHT JOIN 表2
ON 表1.列名 = 表2.列名;全外连接（FULL OUTER JOIN）
定义：全外连接返回两个表中的所有行，无论是否满足连接条件。如果某个表中没有匹配的行，则返回NULL。
语法：
SELECT 列名
FROM 表1
FULL OUTER JOIN 表2
ON 表1.列名 = 表2.列名;示例数据库
为了更好地理解连接查询，假设我们有以下两个表：
-- 用户表
CREATE TABLE User (
UserID INT PRIMARY KEY,
Name VARCHAR(50)
);
-- 订单表
CREATE TABLE `Order` (
OrderID INT PRIMARY KEY,
UserID INT,
OrderDate DATE
);
-- 插入数据
INSERT INTO User (UserID, Name) VALUES
(1, 'Alice'),
(2, 'Bob'),
(3, 'Charlie');
INSERT INTO `Order` (OrderID, UserID, OrderDate) VALUES
(101, 1, '2023-01-01'),
(102, 1, '2023-02-01'),
(103, 2, '2023-03-01');查询示例
内连接：
SELECT User.Name, `Order`.OrderDate
FROM User
INNER JOIN `Order`
ON User.UserID = `Order`.UserID;左连接：
SELECT User.Name, `Order`.OrderDate
FROM User
LEFT JOIN `Order`
ON User.UserID = `Order`.UserID;右连接：
SELECT User.Name, `Order`.OrderDate
FROM User
RIGHT JOIN `Order`
ON User.UserID = `Order`.UserID;全外连接：
SELECT User.Name, `Order`.OrderDate
FROM User
FULL OUTER JOIN `Order`
ON User.UserID = `Order`.UserID;关联查询
关联查询是指在查询中使用多个表的数据，通过某种条件将这些表的数据关联起来。关联查询可以分为多种类型，包括自关联查询。
自关联查询是指在同一张表中进行查询，表中的数据之间存在某种关联关系。自关联查询通常用于处理层次结构数据或需要多次查询同一张表的情况。
示例场景：假设有一个员工表 Employee，其中每个员工都有一个 ManagerID，指向另一个员工的 EmployeeID，表示该员工的直接上级。
表结构及插入数据：
CREATE TABLE Employee (
EmployeeID INT PRIMARY KEY,
Name VARCHAR(50),
ManagerID INT,
FOREIGN KEY (ManagerID) REFERENCES Employee(EmployeeID)
);
INSERT INTO Employee (EmployeeID, Name, ManagerID) VALUES
(1, 'Alice', NULL), -- Alice 是 CEO，没有上级
(2, 'Bob', 1), -- Bob 的上级是 Alice
(3, 'Charlie', 1), -- Charlie 的上级是 Alice
(4, 'David', 2), -- David 的上级是 Bob
(5, 'Eve', 2); -- Eve 的上级是 Bob查询每个员工的直接上级：
SELECT
e.Name AS EmployeeName,
m.Name AS ManagerName
FROM
Employee e
LEFT JOIN
Employee m
ON
e.ManagerID = m.EmployeeID;
查询每个员工的直接下级：
SELECT
m.Name AS ManagerName,
e.Name AS EmployeeName
FROM
Employee e
RIGHT JOIN
Employee m
ON
e.ManagerID = m.EmployeeID;
查询每个员工的完整层级路径：
-- 递归查询员工层级结构
WITH RECURSIVE EmployeeHierarchy AS (
-- 初始化递归查询，选取经理层级以上的员工
SELECT
EmployeeID,
Name,
ManagerID,
1 AS Level
FROM
Employee
WHERE
ManagerID IS NULL
UNION ALL
-- 递归部分，构建员工和其经理之间的层级关系
SELECT
e.EmployeeID,
e.Name,
e.ManagerID,
eh.Level + 1
FROM
Employee e
INNER JOIN
EmployeeHierarchy eh
ON
e.ManagerID = eh.EmployeeID
)
-- 最终查询，展示员工、其直接经理和层级信息
SELECT
e.Name AS EmployeeName,
eh.Name AS ManagerName,
eh.Level
FROM
EmployeeHierarchy eh
LEFT JOIN
Employee e
ON
eh.EmployeeID = e.EmployeeID
ORDER BY
eh.Level, e.Name;
连接原理
在数据库中，连接查询的性能优化是一个非常重要的课题。MySQL 提供了多种连接算法来优化查询性能，包括 Index Nested-Loop Join 算法和 Block Nested-Loop Join 算法。理解这些算法的原理和优化方式，对于提高查询效率至关重要。
Index Nested-Loop Join 算法：查询驱动表得到数据集，然后根据数据集中的每一条记录的关联字段再分别到被驱动表中查找匹配（走索引），所以驱动表只需要访问一次，被驱动表要访问多次
MySQL 将查询驱动表后得到的记录成为驱动表的扇出，连接查询的成本：单次访问驱动表的成本 + 扇出值 * 单次访问被驱动表的成本，优化器会选择成本最小的表连接顺序（确定谁是驱动表，谁是被驱动表）生成执行计划，进行连接查询，优化方式：
-
减少驱动表的扇出（让数据量小的表来做驱动表）
-
降低访问被驱动表的成本
说明：STRAIGHT_JOIN 是查一条驱动表，然后根据关联字段去查被驱动表，要访问多次驱动表，所以需要优化为 INL 算法
Block Nested-Loop Join 算法：一种空间换时间的优化方式，基于块的循环连接，执行连接查询前申请一块固定大小的内存作为连接缓冲区 Join Buffer，先把若干条驱动表中的扇出暂存在缓冲区，每一条被驱动表中的记录一次性的与 Buffer 中多条记录进行匹配（扫描全部数据，一条一条的匹配），因为是在内存中完成，所以速度快，并且降低了 I/O 成本
Join Buffer 可以通过参数 join_buffer_size 进行配置，默认大小是 256 KB
在成本分析时，对于很多张表的连接查询，连接顺序有非常多，MySQL 如果挨着进行遍历计算成本，会消耗很多资源
-
提前结束某种连接顺序的成本评估：维护一个全局变量记录当前成本最小的连接方式，如果一种顺序只计算了一部分就已经超过了最小成本，可以提前结束计算
-
系统变量 optimizer_search_depth：如果连接表的个数小于该变量，就继续穷举分析每一种连接数量，反之只对数量与 depth 值相同的表进行分析，该值越大成本分析的越精确
-
系统变量 optimizer_prune_level：控制启发式规则的启用，这些规则就是根据以往经验指定的，不满足规则的连接顺序不分析成本
连接优化
BKA（Batched Key Access）算法
原理：
- BKA 算法是对 Index Nested-Loop Join (NLJ) 算法的优化。
- 在读取被驱动表的记录时使用顺序 IO，Extra 信息中会有 Batched Key Access 信息。
使用 BKA 的表的 JOIN 过程：
- 连接驱动表将满足条件的记录放入 Join Buffer，并将两表连接的字段放入一个 DYNAMIC_ARRAY ranges 中。
- 在进行表的连接过程中，会将 ranges 相关的信息传入 Buffer 中，进行被驱动表主键的查找及排序操作。
- 调用步骤 2 中产生的有序主键，顺序读取被驱动表的数据。
- 当缓冲区的数据被读完后，会重复进行步骤 2、3，直到记录被读取完。
使用 BKA 优化需要设置：
SET optimizer_switch='mrr=on,mrr_cost_based=off,batched_key_access=on';- 前两个参数的作用是启用 Multi-Range Read (MRR)，因为 BKA 算法的优化要依赖于 MRR。
BNL（Block Nested-Loop Join）算法
问题
BNL 算法由于要访问多次被驱动表，会产生两个问题：
冷表数据页移到 LRU 链表头部：
- Join 语句多次扫描一个冷表，并且语句执行时间小于 1 秒，就会在再次扫描冷表时，把冷表的数据页移到 LRU 链表头部，导致热数据被淘汰，影响业务的正常运行。
- 这种情况冷表的数据量要小于整个 Buffer Pool 的 old 区域，能够完全放入 old 区，才会再次被读时加到 young，否则读取下一段时就已经把上一段淘汰。
Buffer Pool 的 young 区域数据页未被合理淘汰：
- Join 语句在循环读磁盘和淘汰内存页，进入 old 区域的数据页很可能在 1 秒之内就被淘汰，就会导致 MySQL 实例的 Buffer Pool 在这段时间内 young 区域的数据页没有被合理地淘汰。
- 大表 Join 操作虽然对 IO 有影响，但是在语句执行结束后对 IO 的影响随之结束。但是对 Buffer Pool 的影响就是持续性的，需要依靠后续的查询请求慢慢恢复内存命中率。
优化
将 BNL 算法转成 BKA 算法，优化方向：
- 在被驱动表上建索引：这样就可以根据索引进行顺序 IO。
- 使用临时表：在临时表上建立索引，将被驱动表和临时表进行连接查询。
驱动表 t1，被驱动表 t2，使用临时表的工作流程：
- 把表 t1 中满足条件的数据放在临时表 tmp_t 中。
- 给临时表 tmp_t 的关联字段加上索引，使用 BKA 算法。
- 让表 t2 和 tmp_t 做 Join 操作（临时表是被驱动表）。
补充：
MySQL 8.0 支持 hash join，join_buffer 维护的不再是一个无序数组，而是一个哈希表，查询效率更高，执行效率比临时表更高。
嵌套查询
嵌套查询是指在一个查询语句中嵌套了另一个查询语句，嵌套的查询语句称为子查询。子查询的结果可以作为外层查询的条件或数据源。根据子查询的结果和相关性，可以将嵌套查询分为不同的类型。
查询分类
根据结果分类
1、结果是单行单列：
可以将查询的结果作为另一条语句的查询条件，使用运算符判断。
SELECT 列名 FROM 表名 WHERE 列名 = (SELECT 列名/聚合函数(列名) FROM 表名 [WHERE 条件]);2、结果是多行单列：
可以作为条件，使用运算符 IN 或 NOT IN 进行判断。
SELECT 列名 FROM 表名 WHERE 列名 [NOT] IN (SELECT 列名 FROM 表名 [WHERE 条件]);3、结果是多行多列：
查询的结果可以作为一张虚拟表参与查询。
SELECT 列名 FROM 表名 [别名], (SELECT 列名 FROM 表名 [WHERE 条件]) [别名] [WHERE 条件];
-- 查询订单表 orderlist 中 id 大于 4 的订单信息和所属用户 USER 信息
SELECT
*
FROM
USER u,
(SELECT * FROM orderlist WHERE id > 4) o
WHERE
u.id = o.uid;根据相关性分类
- 不相关子查询：子查询不依赖外层查询的值，可以单独运行出结果。
- 相关子查询：子查询的执行需要依赖外层查询的值。
查询优化
物化
1、不相关子查询的结果集会被写入一个临时表，并且在写入时去重，该过程称为物化。
2、存储结果集的临时表称为物化表。
3、系统变量 tmp_table_size 或 max_heap_table_size 为表的最值。
- 小于系统变量时，内存中可以保存，会为建立基于内存的 MEMORY 存储引擎的临时表，并建立哈希索引。
- 大于任意一个系统变量时，物化表会使用基于磁盘的 InnoDB 存储引擎来保存结果集中的记录，索引类型为 B+ 树。
半连接
- 物化后，嵌套查询就相当于外层查询的表和物化表进行内连接查询，然后经过优化器选择成本最小的表连接顺序执行查询。
- 子查询物化会产生建立临时表的成本，但是将子查询转化为连接查询可以充分发挥优化器的作用，所以引入半连接。
- 半连接只是执行子查询的一种方式，MySQL 并没有提供面向用户的半连接语法。
联合查询
UNION 和 UNION ALL 是用于合并多个查询结果集的操作符。
- UNION 是取这两个子查询结果的并集，并进行去重，同时进行默认规则的排序。
- UNION ALL 是对两个结果集进行并集操作不进行去重，不进行排序。
(SELECT 1000 AS f) UNION (SELECT id FROM t1 ORDER BY id DESC LIMIT 2); # t1 表中包含 id 为 1-1000 的数据语句的执行流程：
- 创建一个内存临时表，这个临时表只有一个整型字段 f，并且 f 是主键字段。
- 执行第一个子查询，得到 1000 这个值，并存入临时表中。
- 执行第二个子查询，拿到第一行 id=1000，试图插入临时表中，但由于 1000 这个值已经存在于临时表了，违反了唯一性约束，所以插入失败，然后继续执行。
- 取到第二行 id=999，插入临时表成功。
- 从临时表中按行取出数据，返回结果并删除临时表，结果中包含两行数据分别是 1000 和 999。