MySQL 基础知识面试题
1. 关系型与非关系型数据库的区别
【题目】
关系型数据库与非关系型数据库有什么区别？
【参考答案】
关系型数据库（RDBMS）基于表格结构，遵循 ACID 原则，支持 SQL 标准，提供事务处理能力，具有固定的模式和数据一致性保证。代表产品：MySQL、Oracle、PostgreSQL。
非关系型数据库（NoSQL）更加灵活，适应键值对、文档、列族、图形等不同数据模型，牺牲部分 ACID 特性以换取水平扩展能力和高性能。代表产品：Redis、MongoDB、Cassandra。
| 维度 | 关系型数据库 | 非关系型数据库 |
|---|---|---|
| 数据模型 | 二维表格 | Key-Value、文档、列族、图 |
| 事务支持 | 支持 ACID | 通常支持 BASE（最终一致性） |
| 扩展方式 | 垂直扩展为主 | 水平扩展为主 |
| 适用场景 | 金融、ERP 等强一致性场景 | 高并发、海量数据、灵活模式 |
【延伸追问】
- 什么场景下会考虑从关系型数据库迁移到 NoSQL？
- 分布式事务在 NoSQL 中如何实现最终一致性？
- CAP 理论在数据库选型中如何应用？
【易错坑点】
- ❌ 误以为 NoSQL 完全不支持事务（实际上像 MongoDB 支持多文档事务）
- ❌ 混淆 BASE 和 ACID 的适用边界
2. 什么是 SQL？SQL 有哪些功能分类？
【题目】
什么是 SQL？SQL 语句分为哪些子类？
【参考答案】
SQL（Structured Query Language）—— 结构化查询语言，是访问关系型数据库的通用语言。
| 分类 | 全称 | 常见语句 | 作用 |
|---|---|---|---|
| DQL | Data Query Language | SELECT | 数据查询 |
| DML | Data Manipulation Language | INSERT、UPDATE、DELETE、MERGE | 数据增删改 |
| DDL | Data Definition Language | CREATE、ALTER、DROP | 定义数据库对象（表、索引等） |
| TCL | Transaction Control Language | COMMIT、ROLLBACK、SAVEPOINT | 事务管理 |
| DCL | Data Control Language | GRANT、REVOKE | 权限控制 |
【延伸追问】
- TRUNCATE 属于哪一类？为什么有些场景下它比 DELETE 更快？
- MERGE 语句（UPSERT）的实现原理是什么？
【易错坑点】
- ❌ 容易漏掉 TCL（事务控制语言）分类
- ❌ 误认为 TRUNCATE 是 DML（实际是 DDL，因为隐式 COMMIT）
3. CHAR 和 VARCHAR 的区别
【题目】
CHAR 和 VARCHAR 类型有什么区别？
【参考答案】
| 特性 | CHAR | VARCHAR |
|---|---|---|
| 长度 | 固定长度 | 可变长度 |
| 存储 | 用空格填充到定义长度 | 按实际长度 + 1~2 字节前缀 |
| 性能 | 略快（固定长度无需计算） | 略慢（需计算实际长度） |
| 适用 | 固定长度编码、MD5、UUID | 姓名、地址等长度不一的字段 |
核心：CHAR(N) 中 N 是字符数，不是字节数。UTF-8 下每个字符可能占 1~4 字节。
【延伸追问】
- VARCHAR(255) 和 VARCHAR(256) 在存储上有什么区别？（提示：长度前缀字节数不同）
- 为什么建议用 CHAR 存 MD5（32 位定长）？
【易错坑点】
- ❌ 混淆字符数和字节数：CHAR(10) 在 UTF-8 下最多占 40 字节
- ❌ 误以为 VARCHAR 一定节省空间（VARCHAR 有额外长度前缀开销）
4. 什么是自增列？
【题目】
什么是自增列（Auto Increment）？它的用途是什么？
【参考答案】
自增列（AUTO_INCREMENT）用于生成自动增长的数字，主要用途是为主键提供唯一值。
sql CREATE TABLE users ( id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(50) );
特性：
- 默认从 1 开始，步长为 1
- 在 InnoDB 中，自增值保存在内存中，重启后通过 SELECT MAX(id) 恢复
- 可自定义起始值和步长：AUTO_INCREMENT = 1000
【延伸追问】
- MySQL 重启后自增列的值会怎样变化？（InnoDB 在 8.0 之前有 bug，重启后可能跳号）
- 自增锁（AUTO-INC Locking）在什么情况下会成为性能瓶颈？
【易错坑点】
- ❌ 误以为自增列一定连续（事务回滚会导致跳号）
- ❌ 认为自增列只能在主键上使用（实际上只要是唯一索引即可）
5. DROP TABLE 和 TRUNCATE TABLE 的区别
【题目】
DROP TABLE 和 TRUNCATE TABLE 有什么区别？
【参考答案】
| 特性 | DROP TABLE | TRUNCATE TABLE |
|---|---|---|
| 删除内容 | 表结构 + 数据 + 索引 + 约束 + 授权 | 仅删除数据 |
| 保留表结构 | ❌ 不保留 | ✅ 保留 |
| 速度 | 快（直接删除 .ibd 文件） | 快（释放数据页） |
| 可回滚 | ❌ 不可回滚（DDL 隐式 COMMIT） | ❌ 不可回滚 |
| 触发器触发 | 不触发 | 不触发 |
TRUNCATE 本质上是先 DROP 表再 CREATE 表，所以比 DELETE 快。
【延伸追问】
- DROP TABLE 后如何恢复数据？（依赖最近的物理备份 + Binlog）
- TRUNCATE 和 DELETE FROM table 不带 WHERE 有何本质区别？
【易错坑点】
- ❌ 误以为 TRUNCATE 可以回滚（InnoDB 下 TRUNCATE 是 DDL，隐式提交）
- ❌ 混淆 TRUNCATE 和 DELETE FROM table_name
6. COUNT(*) 与 COUNT(列名) 的区别
【题目】
COUNT(*) 与 COUNT(列名) 的区别是什么？
【参考答案】
| 函数 | 行为 | NULL 处理 |
|---|---|---|
| COUNT(*) | 统计表中所有行数 | 包含 NULL 行 |
| COUNT(列名) | 统计该列非 NULL 值的行数 | 忽略 NULL |
sql -- 假设表有 5 行，某列有 2 个 NULL SELECT COUNT(*) FROM t; -- 返回 5 SELECT COUNT(col) FROM t; -- 返回 3（忽略 2 个 NULL）
性能：MySQL 8.0 优化后，COUNT(*) 和 COUNT(1) 性能相当。
【延伸追问】
- COUNT(1) 和 COUNT(*) 哪个更快？（MySQL 8.0 中无区别）
- MyISAM 下 COUNT(*) 为何比 InnoDB 快？（MyISAM 缓存了表总行数）
【易错坑点】
- ❌ 误以为 COUNT(*) 会统计所有列，性能更差（实际 MySQL 已优化）
- ❌ 混淆 NULL 和空字符串 ”（空字符串不是 NULL）
7. MySQL 日志文件有哪些？分别介绍作用
【题目】
MySQL 有哪些日志文件？分别介绍它们的作用。
【参考答案】
| 日志类型 | 引擎 | 作用 |
|---|---|---|
| 错误日志（Error Log） | 所有 | 记录启动、运行、关闭时的错误信息，排查问题时优先查看 |
| 慢查询日志（Slow Query Log） | 所有 | 记录执行时间超过 long_query_time 的 SQL，用于定位慢查询 |
| 一般查询日志（General Log） | 所有 | 记录所有对数据库的请求（无论正确与否），性能开销大，平时关闭 |
| 二进制日志（Binlog） | 所有 | 记录所有 DDL/DML 操作（不包含 SELECT），用于主从复制和恢复 |
| 重做日志（Redo Log） | InnoDB | 记录事务修改，保证持久性（崩溃恢复用） |
| 回滚日志（Undo Log） | InnoDB | 记录数据修改前的状态，保证原子性和 MVCC |
【延伸追问】
- Redo Log 和 Binlog 有什么区别？（物理日志 vs 逻辑日志，崩溃恢复 vs 时间点恢复）
- 两阶段提交（2PC）如何保证 Redo Log 和 Binlog 的一致性？
【易错坑点】
- ❌ 混淆 Binlog（Server 层）和 Redo Log（InnoDB 引擎层）
- ❌ 忽略 Undo Log 在 MVCC 中的作用
8. 主从复制原理
【题目】
MySQL 主从复制的原理是什么？
【参考答案】
主从复制流程分为三步：
Master: 写入 → Binlog ↓ (Dump Thread 推送) Slave: IO Thread → Relay Log ↓ (SQL Thread 重放) Slave: 数据更新
- Master 写入 Binlog：Master 上的数据变更记录到 Binary Log
- Dump Thread 推送：Master 创建 Dump 线程，向 Slave 推送 Binlog
- IO Thread 接收：Slave 连接 Master，创建 IO 线程接收 Binlog 并写入 Relay Log
- SQL Thread 重放：Slave 的 SQL 线程读取 Relay Log 并在本地执行
- Slave 记录自己的 Binlog（如果需要级联复制）
【延伸追问】
- 主从延迟的原因有哪些？如何排查？
- 半同步复制与异步复制的区别是什么？
- GTID 复制相比传统复制的优势是什么？
【易错坑点】
- ❌ 误以为 Slave 直接应用 Master 的 Binlog（实际通过 Relay Log 中转）
- ❌ 忽视 Dump Thread 是 Master 端的线程
9. InnoDB 和 MyISAM 引擎的区别
【题目】
MySQL 中 InnoDB 和 MyISAM 存储引擎的区别是什么？
【参考答案】
| 特性 | InnoDB | MyISAM |
|---|---|---|
| 事务 | ✅ 支持（ACID） | ❌ 不支持 |
| 锁粒度 | 行级锁（高并发） | 表级锁（低并发） |
| 外键 | ✅ 支持 | ❌ 不支持 |
| 全文索引 | MySQL 5.6+ 支持 | ✅ 原生支持 |
| 缓存 | 缓存索引 + 数据（Buffer Pool） | 只缓存索引（Key Cache） |
| 行数统计 | 实时 COUNT 需扫描 | 缓存总行数，COUNT 极快 |
| 存储文件 | .ibd（数据+索引） | .MYD（数据）+ .MYI（索引） |
| 默认引擎 | MySQL 5.5+ 默认 | MySQL 5.5 之前默认 |
【延伸追问】
- 为什么 MyISAM 的 COUNT 比 InnoDB 快得多？
- 在哪些业务场景下仍会使用 MyISAM？（只读数据仓库场景）
【易错坑点】
- ❌ 误以为 InnoDB 一定比 MyISAM 好（MyISAM 在只读场景下索引查询更快）
- ❌ 忽略 InnoDB 的 MVCC 机制对并发控制的重要性
10. InnoDB 为什么推荐使用自增 ID 作为主键
【题目】
为什么 InnoDB 推荐使用自增 ID 作为主键？
【参考答案】
InnoDB 使用聚簇索引（Clustered Index），主键索引的叶子节点直接存储行数据。
- 自增 ID 有序插入：新数据追加到 B+ 树末尾，减少页分裂和碎片
- UUID/随机主键：插入位置随机，导致频繁的页分裂、数据迁移和性能下降
- 空间利用：自增 ID（INT 4 字节 / BIGINT 8 字节）比 UUID（36 字节）占用更少的索引空间
`sql — 推荐 CREATE TABLE t (id INT AUTO_INCREMENT PRIMARY KEY, …);
— 不推荐（UUID 主键会导致频繁页分裂） CREATE TABLE t (id CHAR(36) PRIMARY KEY, …); `
【延伸追问】
- 分布式场景下自增 ID 有什么局限性？如何解决？（雪花算法、号段模式）
- 如果使用 UUID 做主键，如何优化 InnoDB 的插入性能？
【易错坑点】
- ❌ 误以为自增 ID 一定连续（事务回滚导致跳号）
- ❌ 忽略 UUID 主键导致索引体积膨胀和页分裂问题
11. 事务的 ACID 特性
【题目】
解释事务的 ACID 四个特性。
【参考答案】
| 特性 | 说明 | 实现机制 |
|---|---|---|
| 原子性（Atomicity） | 事务中的操作不可分割，要么全部成功，要么全部回滚 | Undo Log 记录回滚信息 |
| 一致性（Consistency） | 事务执行前后数据库都处于一致状态 | 由原子性 + 隔离性 + 业务逻辑共同保证 |
| 隔离性（Isolation） | 并发事务之间互不干扰 | 锁 + MVCC（多版本并发控制） |
| 持久性（Durability） | 事务提交后修改永久生效 | Redo Log + 双写缓冲（Double Write Buffer） |
【延伸追问】
- MVCC 如何实现 RC 和 RR 隔离级别？
- 持久性是否意味着数据一定不会丢？（Group Commit、Binlog 刷盘策略影响）
【易错坑点】
- ❌ 误以为一致性是数据库单独保证的（业务逻辑也要保证一致性）
- ❌ 混淆持久性和「立即落盘」（实际有 Buffer Pool 缓冲）
12. 聚集索引与非聚集索引的区别
【题目】
聚集索引（Clustered Index）和非聚集索引（Secondary Index）的区别是什么？
【参考答案】
| 特性 | 聚集索引 | 非聚集索引 |
|---|---|---|
| 叶子节点 | 直接包含完整行数据 | 包含主键值（指向数据的指针） |
| 物理顺序 | 数据按索引顺序物理存储 | 索引顺序与数据物理顺序无关 |
| 回表查询 | 无需回表 | 需要根据主键值回表查询 |
| 数量 | 每张表只能有一个 | 每张表可以有多个 |
InnoDB = 聚簇索引（主键），主键索引即数据。
回表过程：辅助索引 → 找到主键值 → 聚簇索引 → 找到完整行数据。
【延伸追问】
- 什么是覆盖索引？如何避免回表？
- 什么是索引下推（Index Condition Pushdown, ICP）？
【易错坑点】
- ❌ 误以为 MyISAM 也有聚簇索引（MyISAM 的索引都是非聚簇的）
- ❌ 忽略「回表」对查询性能的影响
13. 针对高并发插入优化 MySQL
【题目】
如何针对高并发插入场景优化 MySQL？
【参考答案】
- 使用自增主键：避免随机 I/O 和页分裂
- 调整 Buffer Pool：innodb_buffer_pool_size 设为物理内存的 60%~80%
- 分区表策略：按时间或业务维度分区，分散写入压力
- 批量插入：使用 INSERT INTO t VALUES (…), (…), (…) 代替逐条插入
- 调整刷盘策略：innodb_flush_log_at_trx_commit=2（允许每秒刷盘，提升写入性能）
- 减少索引：高频插入的表避免过多索引
- 使用写入缓冲：先写入队列/缓存，批量刷入数据库
【延伸追问】
- innodb_flush_log_at_trx_commit=1 和 =2 的区别是什么？
- 插入缓冲（Insert Buffer / Change Buffer）的原理是什么？
【易错坑点】
- ❌ 为提升插入性能盲目调大 Buffer Pool（过大导致内存争抢）
- ❌ 忽略 binlog 刷盘策略对写入性能的影响
14. 分库分表设计
【题目】
什么是分库分表？如何设计？
【参考答案】
垂直切分：按业务模块拆分表到不同库（如用户库、订单库） 水平切分：按数据范围/哈希将同一张表的数据分到多个库/表
常用中间件：ShardingSphere、MyCat、Vitess
sql -- 水平分片示例（按用户 ID 取模分 4 张表） -- user_0, user_1, user_2, user_3 -- 路由规则：user_id % 4 = 表后缀
设计要点：
- 分片键选择（按查询频率最高的字段分片）
- 跨分片查询（全局表、ER 分片、广播表）
- 分布式事务（Seata、XA、TCC）
【延伸追问】
- 分库分表后如何实现全局唯一 ID？（雪花算法、号段、Redis incr）
- 如何平滑扩容？（双写迁移、中间件路由切换）
【易错坑点】
- ❌ 过早分库分表（大多数场景单库分区即可）
- ❌ 忽略跨分片 JOIN 和分布式事务的复杂性
15. 主键索引和唯一索引的区别
【题目】
主键索引和唯一索引的区别是什么？
【参考答案】
| 特性 | 主键索引 | 唯一索引 |
|---|---|---|
| NULL 值 | ❌ 不允许 | ✅ 允许（多个 NULL） |
| 数量 | 每张表只能有一个 | 每张表可以有多个 |
| 索引类型 | 聚簇索引（InnoDB） | 非聚簇索引（辅助索引） |
| 物理存储 | 决定数据物理存储顺序 | 不决定物理顺序 |
| 用途 | 唯一标识每一行 | 保证列值的唯一性 |
【延伸追问】
- 没有主键的表，InnoDB 如何组织数据？（选择第一个唯一非空索引，或自动生成隐藏的 ROWID）
- 唯一索引对写入性能有什么影响？（每次写入需额外检查唯一性）
【易错坑点】
- ❌ 误以为唯一索引不允许 NULL（实际上允许，且多个 NULL 不违反唯一约束）
- ❌ 混淆主键约束和 NOT NULL + UNIQUE 索引的区别
16. MySQL 中的 JOIN 操作类型
【题目】
MySQL 中的 JOIN 操作有哪些类型？
【参考答案】
| 类型 | 返回结果 |
|---|---|
| INNER JOIN | 两表中都存在匹配的行 |
| LEFT JOIN | 左表全部 + 右表匹配的行（无匹配显示 NULL） |
| RIGHT JOIN | 右表全部 + 左表匹配的行（无匹配显示 NULL） |
| FULL OUTER JOIN | 两表全部行（MySQL 不支持，用 LEFT + UNION + RIGHT 模拟） |
| CROSS JOIN | 笛卡尔积（两表所有行组合） |
sql -- LEFT JOIN 示例 SELECT a.*, b.* FROM table_a a LEFT JOIN table_b b ON a.id = b.a_id;
【延伸追问】
- JOIN 的性能杀手有哪些？（嵌套循环、无索引关联列、临时表排序）
- 如何通过 EXPLAIN 分析 JOIN 的执行计划？
【易错坑点】
- ❌ 混淆 LEFT JOIN 和 INNER JOIN 的结果行数
- ❌ 忘记在 JOIN 的关联列上建索引
17. 什么是 MySQL 的索引？何时创建索引？
【题目】
什么是 MySQL 的索引？应该在什么情况下创建索引？
【参考答案】
索引是用于加速数据检索的数据结构（InnoDB 使用 B+ Tree）。
应创建索引的场景：
- WHERE 子句中频繁出现的列
- JOIN 关联列（关联条件上的列）
- ORDER BY / GROUP BY 涉及的列
- 覆盖索引可以避免回表查询
- 高选择性列优先（区分度高的列）
不应创建索引的场景：
- 数据量小的表（全表扫描更快）
- 频繁更新的列（索引维护成本高）
- 区分度低的列（如性别只有 2 个值）
- 很少查询的列
【延伸追问】
- 如何通过 EXPLAIN 判断索引是否被正确使用？（type、key、Extra 字段）
- 什么是索引失效？常见的索引失效场景有哪些？
【易错坑点】
- ❌ 索引越多越好（每多一个索引，写入就多一份开销）
- ❌ 在区分度低的列上建索引（如性别列，B+ 树扫描效率不高）
18. 什么是 MySQL 事务？
【题目】
简述 MySQL 中的事务是什么？
【参考答案】
事务是数据库操作的基本单位，是一系列 SQL 操作的集合，这些操作要么全部执行成功，要么全部回滚。
事务具有 ACID 特性（原子性、一致性、隔离性、持久性）。
sql START TRANSACTION; UPDATE accounts SET balance = balance - 100 WHERE id = 1; UPDATE accounts SET balance = balance + 100 WHERE id = 2; COMMIT; -- 或 ROLLBACK;
【延伸追问】
- 隐式事务和显式事务有什么区别？（autocommit 模式）
- 长事务有什么危害？（锁堆积、Undo 膨胀、主从延迟）
【易错坑点】
- ❌ 误以为所有 SQL 都在事务中（MyISAM 不支持事务，DDL 自动隐式提交）
- ❌ 忽略长事务导致 MVCC 版本的堆积问题
19. 什么是 MySQL 视图？
【题目】
什么是 MySQL 中的视图（View）？
【参考答案】
视图是从一个或多个表中派生出的虚拟表，不实际存储数据，只存储查询定义。
`sql — 创建视图 CREATE VIEW v_user_orders AS SELECT u.name, o.order_id, o.amount FROM users u JOIN orders o ON u.id = o.user_id;
— 使用视图（如同查询普通表） SELECT * FROM v_user_orders WHERE amount > 100; `
优点：
- 简化复杂查询
- 隐藏底层表结构，提供安全层
- 逻辑隔离（表结构调整时，视图可保持不变）
限制：
- 性能取决于底层查询（视图本质是封装了子查询）
- 可更新视图有限制（聚合、DISTINCT、子查询等不可更新）
【延伸追问】
- 物化视图（Materialized View）和普通视图的区别是什么？
- 视图的更新（UPDATE VIEW）有什么限制条件？
【易错坑点】
- ❌ 误以为视图能提升性能（视图只是封装的查询，不会缓存数据）
- ❌ 忽略视图的性能开销（嵌套视图可能导致严重的性能问题）
20. 如何备份和恢复 MySQL 数据库？
【题目】
如何备份和恢复 MySQL 数据库？
【参考答案】
逻辑备份： `ash
mysqldump 备份
mysqldump -u root -p —all-databases > backup.sql mysqldump -u root -p db_name > db_backup.sql
恢复
mysql -u root -p < backup.sql mysql -u root -p db_name < db_backup.sql `
物理备份： `ash
Percona XtraBackup（热备，不锁表）
xtrabackup —backup —target-dir=/backup/ xtrabackup —prepare —target-dir=/backup/ `
| 备份方式 | 优点 | 缺点 |
|---|---|---|
| mysqldump | 简单、可跨版本 | 大数据量慢 |
| XtraBackup | 快、热备、增量备份 | 需额外安装工具 |
| Binlog | 时间点恢复（PITR） | 需配合全量备份使用 |
【延伸追问】
- 如何进行时间点恢复（Point-In-Time Recovery）？
- 备份的恢复演练应该多久做一次？
【易错坑点】
- ❌ 只有备份没有验证（备份文件损坏等于没备份）
- ❌ 忽略 Binlog 的保留时间，导致无法 PITR
21. 索引不适合哪些场景？
【题目】
索引在哪些场景下不适合使用？
【参考答案】
- 数据量小的表：全表扫描比索引查询更快（MySQL 优化器会自动选择）
- 频繁更新的列：索引维护（B+ 树调整）开销大于查询收益
- 区分度低的列：如性别（男/女），扫描一半数据不如全表扫描
- 很少查询的列：索引占用空间，没有查询收益
- 长文本/二进制列：索引体积大且意义不大（可使用前缀索引）
【延伸追问】
- 什么是区分度（Cardinality）？如何查看？
- 前缀索引如何设置合适的长度？
【易错坑点】
- ❌ 为所有列都建立索引（严重降低写入性能）
- ❌ 忽略 MySQL 优化器可能不使用索引（即使有索引）
22. 脏读、不可重复读、幻读的解释
【题目】
什么是脏读、不可重复读、幻读？
【参考答案】
| 问题 | 现象 | 产生原因 | 隔离级别解决 |
|---|---|---|---|
| 脏读 | 事务 A 读到事务 B 未提交的数据 | 写操作未提交即被读取 | READ COMMITTED 及以上 |
| 不可重复读 | 同一事务内两次读取同一条记录返回不同数据 | 其他事务修改并提交了该记录 | REPEATABLE READ 及以上 |
| 幻读 | 同一事务内两次查询结果集数量不同（出现新行） | 其他事务插入/删除了记录 | SERIALIZABLE（或 InnoDB 的间隙锁解决） |
sql -- 不可重复读示例 -- 事务 A: SELECT balance FROM accounts WHERE id = 1; -- 返回 100 -- 事务 B: UPDATE accounts SET balance = 200 WHERE id = 1; COMMIT; -- 事务 A: SELECT balance FROM accounts WHERE id = 1; -- 返回 200（不可重复读）
【延伸追问】
- InnoDB 如何通过 MVCC + Gap Lock 解决幻读？
- 在 READ COMMITTED 级别下如何避免不可重复读？
【易错坑点】
- ❌ 混淆不可重复读（同一条记录值变化）和幻读（结果集行数变化）
- ❌ 误以为 REPEATABLE READ 能完全避免幻读（InnoDB 的 Next-Key Lock 实际上解决的是「当前读」下的幻读）
23. SQL 约束有哪几种？
【题目】
SQL 约束有哪几种？
【参考答案】
| 约束 | 说明 | 示例 |
|---|---|---|
| NOT NULL | 字段值不能为 NULL | name VARCHAR(50) NOT NULL |
| UNIQUE | 字段值唯一，允许 NULL | email VARCHAR(100) UNIQUE |
| PRIMARY KEY | 唯一标识每行，不允许 NULL | id INT PRIMARY KEY |
| FOREIGN KEY | 引用另一张表的主键，保证引用完整性 | FOREIGN KEY (dept_id) REFERENCES dept(id) |
| CHECK | 字段值必须满足指定条件（MySQL 8.0.16+ 才真正生效） | age INT CHECK (age >= 0) |
| DEFAULT | 为字段指定默认值 | status INT DEFAULT 1 |
【延伸追问】
- CHECK 约束在 MySQL 8.0 之前为什么无效？（解析时忽略，不报错但也不检查）
- FOREIGN KEY 对性能有什么影响？（每次插入/更新需检查引用表）
【易错坑点】
- ❌ 误以为 MySQL 的 CHECK 约束一直有效（MySQL 8.0.16 前仅解析不执行）
- ❌ 忽略外键对写入性能的影响
24. UNION 和 UNION ALL 的区别
【题目】
UNION 和 UNION ALL 有什么区别？
【参考答案】
| 特性 | UNION | UNION ALL |
|---|---|---|
| 重复行 | 去重（自动 DEDUP） | 包含重复行 |
| 排序 | 会对结果排序（以去重） | 不排序 |
| 性能 | 较慢（需要额外排序去重） | 更快 |
| 内存 | 需要临时表去重 | 直接追加结果 |
`sql — UNION 去重效率低 SELECT name FROM employees_2023 UNION SELECT name FROM employees_2024;
— UNION ALL 效率高（确认无重复时使用） SELECT name FROM employees_2023 UNION ALL SELECT name FROM employees_2024; `
【延伸追问】
- UNION 的去重逻辑是什么？（对所有 SELECT 的列做 DISTINCT）
- 什么场景下应该用 UNION ALL 替代 UNION？
【易错坑点】
- ❌ 随意使用 UNION 而不是 UNION ALL（导致不必要的去重排序开销）
- ❌ 误以为 UNION 不会对结果排序（实际上为去重会排序）
25. 子查询及其用途
【题目】
解释子查询及其用途。
【参考答案】
子查询是嵌套在其他 SQL 查询中的查询，可以返回标量值、单列集合或多列结果。
`sql — 标量子查询（返回单个值） SELECT name, (SELECT MAX(salary) FROM employees) AS max_salary FROM departments;
— 行子查询（WHERE 条件中使用） SELECT * FROM products WHERE price > (SELECT AVG(price) FROM products);
— EXISTS 子查询 SELECT * FROM customers c WHERE EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.id);
— FROM 子句中的派生表 SELECT avg_order.total FROM (SELECT customer_id, COUNT(*) as total FROM orders GROUP BY customer_id) avg_order; `
用途：
- 在 WHERE 条件中比较聚合结果
- 检查数据的存在性（EXISTS / NOT EXISTS）
- 在 FROM 中作为派生表（临时结果集）
【延伸追问】
- 子查询和 JOIN 的性能差异是什么？（子查询在某些场景下会产生临时表，性能低于 JOIN）
- 关联子查询（Correlated Subquery）和非关联子查询的区别？
【易错坑点】
- ❌ 滥用子查询导致性能问题（可用 JOIN 优化时优先使用 JOIN）
- ❌ 忽略了 EXISTS 和 IN 子查询在某些场景下的性能差异
关联文档
- MySQL 复制原理与配置 — 主从复制原理详解
- MySQL binlog 保留时间与清理 — Binlog 管理
- MySQL 高可用方案 — 高可用架构对比
- GTID 集合详解 — GTID 复制原理