MySQL 面试题MySQL基础概念、架构与原理、存储引擎、事务、索引、项目实战、索引进阶、SQL优化、SQL语法、SQ

MySQL 面试题整理

一、基础概念

1. MySQL 的默认端口是啥？

答案： MySQL 的默认端口是 3306。

2. MySQL 哪个版本有 JSON 类型？

答案： MySQL 5.7.8 版本开始原生支持 JSON 数据类型。在该版本之前，JSON 数据只能以 TEXT 或 VARCHAR 类型存储，5.7.8 起可以直接定义 JSON 类型列，并提供了丰富的 JSON 函数（如 JSON_EXTRACT、JSON_ARRAY、JSON_OBJECT 等）进行操作。

3. MySQL 存储金额的话用什么类型？

答案： 推荐使用 DECIMAL（NUMERIC） 类型。例如 DECIMAL(10,2) 表示总共 10 位数字，其中 2 位小数。

不推荐使用 FLOAT/DOUBLE，因为浮点数存在精度丢失问题。例如 0.1 + 0.2 在浮点数中不等于 0.3。

如果金额单位为分（整数），也可以使用 BIGINT 存储，避免小数运算问题。

4. char 和 varchar 的区别？char(10) 和 varchar(10) 的区别？

答案：

特性	CHAR	VARCHAR
存储方式	定长，不足部分用空格填充	变长，按实际长度存储
空间占用	始终占用声明长度的空间	实际长度 + 1~2 字节长度前缀
最大长度	255 字符	65535 字节（受行大小限制）
适用场景	长度固定的数据（如手机号、MD5）	长度不固定的数据（如姓名、地址）
性能	查询效率稍高（固定长度便于定位）	节省空间，但更新可能产生碎片

CHAR(10)：无论存几个字符，都占 10 个字符的存储空间，不足补空格。
VARCHAR(10)：最多存 10 个字符，实际占用 = 字符数 + 长度前缀（1~2 字节）。

5. MySQL 里 unsigned 和 signed 的区别？

答案：

SIGNED（有符号）：默认类型，最高位为符号位，可表示正数和负数。例如 INT SIGNED 范围为 -2147483648 ~ 2147483647。
UNSIGNED（无符号）：没有符号位，只能表示非负数，但正数范围翻倍。例如 INT UNSIGNED 范围为 0 ~ 4294967295。

适用场景：主键自增 ID、年龄等不可能为负数的字段适合用 UNSIGNED。

6. MySQL 的字符串类型都有哪些？

答案：

类型	说明	最大长度
CHAR	定长字符串	255 字符
VARCHAR	变长字符串	65535 字节
TEXT	长文本数据	65535 字节
MEDIUMTEXT	中等长度文本	16MB
LONGTEXT	长文本	4GB
TINYTEXT	短文本	255 字节
ENUM	枚举类型	最多 65535 个值
SET	集合类型	最多 64 个成员
BLOB	二进制大对象	65535 字节
MEDIUMBLOB	中等二进制	16MB
LONGBLOB	大二进制	4GB
TINYBLOB	小二进制	255 字节
BINARY	定长二进制	255 字节
VARBINARY	变长二进制	65535 字节

7. MySQL 的客户端工具你们用的是啥？

答案： 常用客户端工具：

命令行：mysql client（官方自带）
图形化工具：Navicat、DBeaver、DataGrip、MySQL Workbench（官方）
轻量工具：SQLyog、HeidiSQL、Sequel Pro（macOS）
Web 端：phpMyAdmin

二、架构与原理

8. MySQL 分为几层？

答案： MySQL 总体分为四层：

连接层（Connection Layer）：处理客户端连接、认证授权、连接池管理。
服务层（Server Layer）：包含 SQL 解析器、查询优化器、执行器、缓存（8.0 已移除查询缓存）等核心功能。
引擎层（Storage Engine Layer）：插拔式存储引擎架构，负责数据的存储和读取，如 InnoDB、MyISAM 等。
存储层（Storage Layer）：将数据存储在文件系统（磁盘）上，完成与存储引擎的交互。

9. DDL & DML & DCL 有什么区别？

答案：

类别	全称	说明	示例
DDL	Data Definition Language	数据定义语言，操作表结构	CREATE、ALTER、DROP、TRUNCATE
DML	Data Manipulation Language	数据操作语言，操作表数据	INSERT、UPDATE、DELETE
DCL	Data Control Language	数据控制语言，操作权限	GRANT、REVOKE

此外还有 TCL（Transaction Control Language）：COMMIT、ROLLBACK、SAVEPOINT。

10. MySQL 常用查询关键字？

答案：

SELECT：查询列
FROM：指定表
WHERE：行级过滤条件
GROUP BY：分组
HAVING：分组后过滤
ORDER BY：排序
LIMIT：限制行数
DISTINCT：去重
JOIN / LEFT JOIN / RIGHT JOIN / INNER JOIN：连接查询
UNION / UNION ALL：合并结果集
IN / EXISTS / NOT IN / NOT EXISTS：子查询
LIKE：模糊匹配
BETWEEN：范围查询
IS NULL / IS NOT NULL：空值判断

执行顺序：FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY → LIMIT

三、存储引擎

11. MySQL 的存储引擎都有哪些？区别是什么？默认的是什么？

答案：

特性	InnoDB	MyISAM
事务	✅ 支持	❌ 不支持
行锁	✅ 支持	❌ 只有表锁
外键	✅ 支持	❌ 不支持
崩溃恢复	✅ 支持（redo log）	❌ 不支持
MVCC	✅ 支持	❌ 不支持
全文索引	✅ 5.6+ 支持	✅ 支持
存储方式	聚集索引（数据和主键索引存在一起）	非聚集索引（索引和数据分离）
COUNT(*)	需要遍历	有存储行数，直接读取

MySQL 5.5+ 默认存储引擎为 InnoDB。

其他引擎：Memory（内存引擎，重启丢失）、Archive（压缩归档）、CSV、NDB（集群引擎）等。

四、事务

12. 数据库的 ACID 知道吗？

答案：

A（Atomicity，原子性）：事务中的操作要么全部成功，要么全部回滚，不可分割。
C（Consistency，一致性）：事务执行前后，数据库从一个一致状态转换到另一个一致状态，不破坏完整性约束。
I（Isolation，隔离性）：并发事务之间互不干扰，一个事务的中间状态对其他事务不可见。
D（Durability，持久性）：事务一旦提交，对数据的修改是永久的，即使系统故障也不丢失。

InnoDB 通过以下机制实现 ACID：

原子性：undo log（回滚日志）
一致性：是目标，由 AID 共同保证
隔离性：MVCC + 锁机制
持久性：redo log（重做日志）

13. 数据库的事务隔离级别都有哪些？MySQL 默认隔离级别知道吗？会有啥问题？

答案：

隔离级别	脏读	不可重复读	幻读
READ UNCOMMITTED（读未提交）	✅ 可能	✅ 可能	✅ 可能
READ COMMITTED（读已提交）	❌ 解决	✅ 可能	✅ 可能
REPEATABLE READ（可重复读）	❌ 解决	❌ 解决	✅ 可能
SERIALIZABLE（串行化）	❌ 解决	❌ 解决	❌ 解决

MySQL 默认隔离级别为 REPEATABLE READ（可重复读）。

在可重复读级别下，通过 MVCC 解决了大部分幻读问题，但在特定场景下仍可能出现幻读（见下题）。

14. 可重复读事务级别为什么会出现幻读？它是怎么解决脏读和不可重复读的，如果要解决幻读问题需要怎么做？

答案：

为什么会出现幻读？

RR 级别通过 MVCC（快照读）解决了普通 SELECT 的幻读，但以下场景仍会出现：

当前读 + 快照读混用：事务中先用快照读，再用 SELECT ... FOR UPDATE（当前读），当前读会读取最新数据，可能看到新插入的行（幻读）。
先快照读后更新：事务中先查询某行不存在，然后 INSERT 该行时发现主键冲突（说明其他事务已插入），再查询就能看到该行。

如何解决脏读和不可重复读？

脏读：MVCC 中每次读都读取事务开始时的快照版本，不会读到未提交的数据。
不可重复读：同一事务中多次快照读都读取同一版本（Read View 在事务开始时创建），保证结果一致。

如何彻底解决幻读？

使用 间隙锁（Gap Lock）+ 临键锁（Next-Key Lock）：在当前读时，InnoDB 会锁住记录及其间隙，阻止其他事务在间隙中插入新行。
实际做法：将所有 SELECT 改为 SELECT ... FOR UPDATE 或 LOCK IN SHARE MODE（当前读），避免快照读和当前读混用。
或直接使用 SERIALIZABLE 隔离级别（性能差，不推荐）。

五、索引

15. MySQL 都有哪些种类的索引？

答案：

按数据结构分：

B+ 树索引：最常见的索引类型
Hash 索引：Memory 引擎支持，等值查询极快，不支持范围查询
全文索引（FULLTEXT）：用于文本搜索
R-Tree 索引：空间数据索引

按逻辑功能分：

主键索引（Primary Key）：唯一 + 非空
唯一索引（Unique）：值唯一，允许 NULL
普通索引（Normal）：无约束，仅加速查询
组合索引（Composite）：多列组合索引
前缀索引：对字符串列的前 N 个字符建索引

按物理存储分：

聚集索引（Clustered）：数据和索引存储在一起（InnoDB 主键索引）
非聚集索引（Secondary）：索引和数据分离，叶子节点存主键值（二级索引）

16. 主键索引和唯一索引的区别？

答案：

特性	主键索引	唯一索引
唯一性	✅ 值唯一	✅ 值唯一
NULL	❌ 不允许 NULL	✅ 允许 NULL（可多个）
每表数量	只能有 1 个	可以有多个
聚集索引	主键索引默认为聚集索引	非聚集索引
自动创建	建表时定义主键自动创建	需手动创建或约束自动创建

17. MySQL 都有哪些种类的函数？

答案：

字符串函数：CONCAT、LENGTH、SUBSTRING、TRIM、UPPER、LOWER、REPLACE、LEFT、RIGHT、LPAD、RPAD

数值函数：ABS、CEIL、FLOOR、ROUND、MOD、RAND、POW、SQRT

日期函数：NOW、CURDATE、CURTIME、DATE_FORMAT、DATEDIFF、DATE_ADD、DATE_SUB、YEAR、MONTH、DAY

聚合函数：COUNT、SUM、AVG、MAX、MIN

条件函数：IF、IFNULL、COALESCE、CASE WHEN

JSON 函数（5.7+）：JSON_EXTRACT、JSON_ARRAY、JSON_OBJECT、JSON_CONTAINS、JSON_MERGE

窗口函数（8.0+）：ROW_NUMBER、RANK、DENSE_RANK、LEAD、LAG、SUM() OVER()

其他：GROUP_CONCAT、CAST、CONVERT、INET_ATON、INET_NTOA

18. MySQL 的视图你用过吗？跟表有什么区别？

答案：

视图（View） 是一个虚拟表，其内容由查询定义，不存储实际数据（除物化视图外）。

特性	视图	表
数据存储	不存储实际数据，只存定义	存储实际数据
更新	有限制（多表关联视图不可更新）	可自由增删改查
性能	查询需执行底层 SQL，可能较慢	直接查询
占用空间	极小（仅存定义）	占用实际存储空间
用途	简化查询、权限控制、数据安全	存储数据

使用场景：

简化复杂查询
对外暴露部分列，隐藏敏感数据
统一数据访问接口

六、项目实战

19. 项目中：MySQL 的表你设计过吗？设计表都有啥规范吗？

答案：

常见设计规范：

命名规范：表名、字段名使用小写字母 + 下划线，见名知意，不用数据库关键字。
主键：每张表必须有主键，推荐自增 ID（BIGINT UNSIGNED）。
字段类型：选择最小够用类型，金额用 DECIMAL，状态用 TINYINT，时间用 DATETIME/TIMESTAMP。
NOT NULL：字段尽量设为 NOT NULL，给出默认值，NULL 值影响索引和统计。
字符集：统一使用 utf8mb4（支持 emoji），排序规则 utf8mb4_general_ci。
索引规范：单表索引不超过 5 个，单个索引字段不超过 5 个，避免冗余索引。
大字段：TEXT/BLOB 类型单独建扩展表，避免影响主表查询性能。
时间字段：创建时间（create_time）和更新时间（update_time）必须包含，update_time 设为 ON UPDATE CURRENT_TIMESTAMP。
逻辑删除：使用 is_deleted 字段做软删除，不物理删除。
外键：不在数据库层面建外键，由应用层保证数据一致性。

20. 项目中：你负责的模块里设计的表里都有哪些字段？

答案：（示例，需结合自身项目回答）

以用户订单模块为例：

字段	类型	说明
id	BIGINT UNSIGNED	主键自增
order_no	VARCHAR(32)	订单编号（唯一索引）
user_id	BIGINT UNSIGNED	用户 ID
product_id	BIGINT UNSIGNED	商品 ID
amount	DECIMAL(10,2)	订单金额
status	TINYINT	订单状态（0待支付 1已支付 2已取消）
pay_time	DATETIME	支付时间
create_time	DATETIME	创建时间
update_time	DATETIME	更新时间
is_deleted	TINYINT	逻辑删除标识

21. 项目中：MySQL 有没有做读写分离？具体怎么实现的？

答案：

实现方式：

主从复制：主库（Master）负责写，从库（Slave）负责读。主库通过 binlog 将变更同步到从库。
代码层面：
- 使用 Spring 的 AbstractRoutingDataSource 动态切换数据源。
- 通过 AOP + 自定义注解，在方法上标注读/写，自动路由到对应数据源。
中间件：
- ShardingSphere-JDBC：客户端侧读写分离，配置主从数据源即可。
- MyCat / ShardingSphere-Proxy：代理层读写分离，对应用透明。
- ProxySQL：MySQL 代理层。

注意事项：

主从同步有延迟（通常毫秒级），对实时性要求高的读操作应走主库。
可通过 SHOW SLAVE STATUS 监控同步延迟。

七、索引进阶

22. MySQL 的表添加索引有规范吗？

答案：

选择区分度高的列：区分度 = 不同值数量 / 总行数，区分度越高索引效果越好。
遵循最左前缀原则：组合索引的列顺序要和查询条件匹配。
覆盖索引：尽量让查询字段在索引中，避免回表。
单表索引数量：建议不超过 5 个，索引越多写入越慢。
避免冗余索引：(a,b) 已包含 (a) 的能力，不需要再单独建 (a)。
频繁更新的列少建索引：每次更新都会维护索引。
索引列不要参与计算/函数：会导致索引失效。
字符串索引可考虑前缀索引：INDEX(name(10))，节省空间。

23. MySQL 的索引失效情况都有哪些？

答案：

对索引列使用函数或计算：WHERE YEAR(create_time) = 2024 → 失效，应改为范围查询。
隐式类型转换：字段是 VARCHAR，条件用整数：WHERE phone = 13800138000 → 失效。
使用 NOT、!=、<>、NOT IN、NOT EXISTS：可能导致失效（优化器可能选择全表扫描）。
LIKE 以通配符开头：LIKE '%abc' → 失效；LIKE 'abc%' → 有效。
违反最左前缀原则：组合索引 (a,b,c)，查询条件只有 b 或 c → 失效。
OR 条件中有非索引列：WHERE a = 1 OR d = 2（d 无索引）→ 全表扫描。
索引列使用 IS NOT NULL：某些情况下可能失效。
数据量太小：优化器认为全表扫描更快，不使用索引。
查询优化器评估全表扫描更快：索引选择性差时。

24. 聚集索引和非聚集索引的区别？

答案：

特性	聚集索引	非聚集索引（二级索引）
叶子节点存储	完整行数据	主键值 + 索引列值
每表数量	只能有 1 个	可以有多个
查询方式	直接获取数据	需回表查询（通过主键再查聚集索引）
插入顺序	建议按主键顺序插入，避免页分裂	影响较小
InnoDB 中	主键索引即为聚集索引	非主键索引为二级索引

回表：通过二级索引找到主键值 → 再通过聚集索引找到完整行数据。

25. 联合索引失效的情况有哪些？

答案：

假设索引为 (a, b, c)：

跳过左边的列：查询条件只有 b 或 c，索引失效。
在索引列上使用函数/计算：WHERE UPPER(a) = 'ABC'。
范围查询右边的列失效：WHERE a = 1 AND b > 2 AND c = 3，c 无法使用索引。
使用 !=、<>、NOT IN：该列及后续列索引失效。
LIKE 以 % 开头：该列及后续列索引失效。
隐式类型转换：导致索引失效。
OR 连接非索引列：整个索引可能失效。

26. MySQL 添加索引后为什么查询会变快？

答案：

没有索引时，查询需要全表扫描（逐行比较），时间复杂度 O(n)。

添加 B+ 树索引后：

B+ 树是有序的平衡多路搜索树，查找时间复杂度从 O(n) 降为 O(log n)。
B+ 树的非叶子节点只存键值，叶子节点存数据/指针，每个节点可以存储更多键值，树的高度更低（通常 3~4 层即可存储千万级数据）。
叶子节点通过双向链表连接，范围查询非常高效。
数据库只需读取少量磁盘页（索引页），而非扫描整张表，大幅减少 I/O。

27. MySQL 的索引底层结构是什么？B+ 树缺点是什么？

答案：

MySQL InnoDB 的索引底层结构是 B+ 树（B+ Tree）。

B+ 树的缺点：

维护成本高：插入、删除时需要维护树的平衡（页分裂、页合并），影响写入性能。
空间占用：索引本身需要额外存储空间，索引越多占用越大。
不适合等值单点查询极致场景：相比 Hash 索引，B+ 树需要经过多次节点比较。
主键不宜用 UUID：随机插入会导致频繁页分裂，建议使用自增主键。
索引列更新代价高：更新索引列需要维护 B+ 树结构。

28. B+ 树和 B 树区别，MySQL 为什么用 B+ 树而不用 B 树？

答案：

特性	B 树	B+ 树
数据存储	所有节点都存数据	只有叶子节点存数据
叶子节点链表	无	有双向链表连接
查询稳定性	不稳定（数据可能在非叶子节点）	稳定（都要到叶子节点）
单节点存键数	较少（节点还存数据）	较多（非叶节点只存键）
范围查询	需要中序遍历	直接遍历叶子链表

MySQL 用 B+ 树的原因：

磁盘 I/O 更少：非叶节点不存数据，单个节点能存更多键值，树更矮，查询时磁盘 I/O 次数更少。
范围查询更高效：叶子节点有链表，范围查询只需找到起点后顺序遍历，B 树需要中序遍历。
查询性能稳定：所有数据都在叶子节点，每次查询路径长度相同。

29. 设置 abc 组合索引，判断下面 SQL 语句是否用到索引？

答案： 索引为 (a, b, c)

① SELECT id FROM student WHERE a = 1 AND b = 2 AND c = 3 → ✅ 三个字段都用到了索引，完美匹配最左前缀。

② SELECT id FROM student WHERE a = 1 AND c = 3 → ⚠️ 只有 a 用到了索引，c 无法使用索引（跳过了 b，违反最左前缀原则）。但如果查询的 id 是主键且索引覆盖了 a 和 c，MySQL 8.0+ 的索引跳跃扫描可能优化。

③ SELECT id FROM student WHERE b = 2 AND c = 3 → ❌ 索引失效，a 是最左列，缺少 a 条件，无法使用组合索引（MySQL 8.0+ 索引跳跃扫描在特定条件下可能部分使用）。

30. MySQL 的回表查询知道吗？

答案：

回表是指通过二级索引查到主键值后，再用主键值到聚集索引中查找完整行数据的过程。

示例：

-- name 上有二级索引
SELECT * FROM user WHERE name = '张三';

先在 name 的二级索引 B+ 树中找到 name = '张三' 对应的主键 id = 5。
再到主键的聚集索引 B+ 树中找到 id = 5 的完整行数据。

避免回表的方法：使用覆盖索引，让查询列都在索引中。

31. MySQL 的最左匹配原理了解吗？

答案：

最左匹配原则：组合索引按照定义时的列顺序从左到右匹配，遇到范围查询（>、<、BETWEEN、LIKE）时停止匹配后续列。

索引 (a, b, c) 的匹配规则：

WHERE a = 1 → ✅ 使用 a
WHERE a = 1 AND b = 2 → ✅ 使用 a、b
WHERE a = 1 AND b = 2 AND c = 3 → ✅ 使用 a、b、c
WHERE a = 1 AND c = 3 → ✅ 使用 a，c 无法使用
WHERE b = 2 AND c = 3 → ❌ 无法使用索引
WHERE a = 1 AND b > 2 AND c = 3 → ✅ 使用 a、b，c 无法使用（b 是范围查询）

注意：WHERE 条件的书写顺序不影响，优化器会自动调整。

32. MySQL 的覆盖索引了解吗？

答案：

覆盖索引：查询所需的所有列都包含在索引中，无需回表查询聚集索引。

示例：

-- 索引 (name, age)
SELECT name, age FROM user WHERE name = '张三';  -- 覆盖索引，不需要回表
SELECT * FROM user WHERE name = '张三';           -- 需要回表

在 EXPLAIN 中，Extra 列出现 Using index 表示使用了覆盖索引。

优点：减少 I/O 操作，提升查询性能。

八、SQL 优化

33. MySQL 的优化方案知道吗？

答案：

表结构优化：

选择合适的字段类型和长度
适度反范式化，减少关联查询
大字段拆分到扩展表
适当冗余减少 JOIN

索引优化：

为 WHERE、ORDER BY、GROUP BY 的列建索引
使用覆盖索引避免回表
遵循最左前缀原则
避免索引失效的场景

查询优化：

避免 SELECT *
小表驱动大表
用 EXISTS 替代 IN（大数据集时）
LIMIT 优化深分页
子查询改为 JOIN

架构优化：

读写分离
分库分表
缓存（Redis）
数据归档

34. MySQL 的 explain 都看哪些指标，这些指标啥含义？

答案：

指标	含义	关注点
type	访问类型	从好到差：system > const > eq_ref > ref > range > index > ALL，避免 ALL
key	实际使用的索引	是否使用了预期索引
key_len	索引使用长度	判断组合索引使用了几个列
rows	预估扫描行数	越小越好
Extra	额外信息	Using index（覆盖索引）✅；Using filesort ❌；Using temporary ❌
possible_keys	可能使用的索引	为空说明没有可用索引
filtered	过滤比例	越高越好，100% 最佳
table	访问的表	-
id	执行顺序	id 越大越先执行

重点关注：type、key、rows、Extra。

35. 慢 SQL 怎么发现？优化的具体步骤？

答案：

发现慢 SQL：

开启慢查询日志：

SET GLOBAL slow_query_log = ON;
SET GLOBAL long_query_time = 1;  -- 超过1秒记录
SET GLOBAL slow_query_log_file = '/var/log/mysql/slow.log';

使用工具分析：mysqldumpslow、pt-query-digest
监控系统：Prometheus + Grafana、阿里云 DAS 等
information_schema：PROCESSLIST 查看正在执行的 SQL

优化步骤：

EXPLAIN 分析执行计划：看 type、key、rows、Extra。
定位问题：全表扫描？索引失效？文件排序？临时表？
优化索引：添加/调整索引，使用覆盖索引。
改写 SQL：避免 SELECT *，优化子查询，调整 JOIN 顺序。
优化表结构：字段类型、拆分大表、归档历史数据。
验证效果：再次 EXPLAIN 对比，压测验证。

36. CPU 打满的情况下，怎么找到慢 SQL？

答案：

SHOW PROCESSLIST：查看当前正在执行的 SQL，关注 Time 值大的、State 为 Sending data / Copying to tmp table 的。
SHOW FULL PROCESSLIST：查看完整 SQL 语句。

information_schema.PROCESSLIST：筛选执行时间长的 SQL：

SELECT * FROM information_schema.PROCESSLIST 
WHERE TIME > 5 AND COMMAND != 'Sleep' 
ORDER BY TIME DESC;

慢查询日志：如果之前已开启，分析日志文件。
OS 层面：top 定位 MySQL 进程，perf top 看热点函数。
紧急处理：KILL <id> 杀掉消耗 CPU 的查询，先恢复服务再分析。

37. SQL 的优化方案知道吗？项目中：你都做了哪些优化？

答案：（常见优化实践）

**避免 SELECT ***：只查需要的列，减少数据传输和回表。
合理使用索引：为高频查询字段建索引，避免索引失效。
覆盖索引：让查询列都在索引中，避免回表。
分页优化：深分页用 WHERE id > last_id LIMIT n 替代 LIMIT offset, n。
批量操作：批量 INSERT 替代循环单条 INSERT。
子查询改 JOIN：某些子查询效率低，改为 JOIN。
大表拆分：垂直拆分（列拆分）和水平拆分（行拆分）。
读写分离：读走从库，减轻主库压力。
缓存：热点数据走 Redis，减少数据库访问。
数据归档：历史数据迁移到归档表。

九、SQL 语法

38. where 和 having 有区别吗？

答案：

特性	WHERE	HAVING
过滤时机	分组前过滤	分组后过滤
聚合函数	❌ 不能使用	✅ 可以使用
索引	✅ 可能走索引	❌ 不走索引
作用对象	行	分组

示例：

-- WHERE 在分组前过滤
SELECT class_id, COUNT(*) FROM student WHERE age > 18 GROUP BY class_id;
-- HAVING 在分组后过滤
SELECT class_id, COUNT(*) FROM student GROUP BY class_id HAVING COUNT(*) > 5;

39. 左连接、内连接、右连接有区别吗？哪种效率高？

答案：

连接类型	说明	结果
INNER JOIN	只返回两表匹配的行	交集
LEFT JOIN	返回左表所有行 + 右表匹配行	左表全保留
RIGHT JOIN	返回右表所有行 + 左表匹配行	右表全保留

效率：INNER JOIN 通常效率最高，因为它只返回匹配行，数据量最少。但实际效率取决于索引、数据量和查询条件，优化器可能将 LEFT JOIN 优化为 INNER JOIN（当 WHERE 条件过滤了右表 NULL 行时）。

40. 多表关联查询怎么实现？

答案：

-- 内连接
SELECT a.name, b.order_no 
FROM user a 
INNER JOIN order b ON a.id = b.user_id;

-- 左连接
SELECT a.name, b.order_no 
FROM user a 
LEFT JOIN order b ON a.id = b.user_id;

-- 多表关联
SELECT a.name, b.order_no, c.product_name
FROM user a
INNER JOIN order b ON a.id = b.user_id
INNER JOIN order_item c ON b.id = c.order_id;

-- 子查询方式
SELECT name FROM user 
WHERE id IN (SELECT user_id FROM order WHERE amount > 100);

-- EXISTS 方式
SELECT name FROM user a
WHERE EXISTS (SELECT 1 FROM order b WHERE a.id = b.user_id AND b.amount > 100);

41. count(*)、count(1)、count(字段)有区别吗？

答案：

写法	说明	是否统计 NULL
COUNT(*)	统计总行数	✅ 统计所有行
COUNT(1)	统计总行数（1 是常量）	✅ 统计所有行
COUNT(字段)	统计该字段非 NULL 的行数	❌ 不统计 NULL

COUNT(*) 和 COUNT(1)：在 InnoDB 中效果相同，都是统计总行数，MySQL 优化了对 COUNT(*) 的处理。
COUNT(字段)：只统计该字段不为 NULL 的行数；如果该字段有索引且为 NOT NULL，优化器可能选择扫描最小的索引。
性能：COUNT(*) ≈ COUNT(1) > COUNT(字段)（字段需要判断 NULL）。

42. select * 和 select 全部字段有什么区别？

答案：

数据传输：SELECT * 返回所有列，如果表有大字段（TEXT/BLOB），网络传输量大。
索引使用：SELECT * 无法使用覆盖索引，必然回表；指定字段可能走覆盖索引。
可维护性：SELECT * 在表结构变更时可能出问题（字段顺序、新增字段）。
可读性：指定字段更清晰，明确知道用了哪些数据。
性能：指定全部字段理论上与 SELECT * 相同（I/O 层面），但 SELECT * 无法利用覆盖索引优化。

结论：即使需要所有字段，也建议显式列出字段名。

43. 表锁和行锁的区别？何时会触发？

答案：

特性	表锁	行锁
锁粒度	整张表	单行或多行
并发度	低	高
开销	小	大
死锁	不会	可能
存储引擎	MyISAM、InnoDB	InnoDB

行锁触发条件（InnoDB）：

通过索引条件检索数据时，加行锁。
如果检索条件没有走索引，行锁会退化为表锁！

表锁触发条件：

LOCK TABLES table_name READ/WRITE
MyISAM 引擎的写操作
InnoDB 中索引失效时的 DML 操作
ALTER TABLE 等 DDL 操作（隐式表锁，通过元数据锁 MDL 实现）

间隙锁（Gap Lock）：在 RR 隔离级别下，InnoDB 对索引间隙加锁，防止幻读。

十、SQL 语句

44. 创建表的 SQL 语句是什么样的？

答案：

CREATE TABLE `user` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '主键ID',
  `name` VARCHAR(50) NOT NULL DEFAULT '' COMMENT '姓名',
  `age` TINYINT UNSIGNED NOT NULL DEFAULT 0 COMMENT '年龄',
  `email` VARCHAR(100) DEFAULT NULL COMMENT '邮箱',
  `status` TINYINT NOT NULL DEFAULT 1 COMMENT '状态 1正常 0禁用',
  `create_time` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `update_time` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  `is_deleted` TINYINT NOT NULL DEFAULT 0 COMMENT '逻辑删除 0未删除 1已删除',
  PRIMARY KEY (`id`),
  KEY `idx_name` (`name`),
  UNIQUE KEY `uk_email` (`email`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='用户表';

45. 修改表的 SQL 语句是什么样的？

答案：

-- 添加列
ALTER TABLE `user` ADD COLUMN `phone` VARCHAR(20) DEFAULT NULL COMMENT '手机号' AFTER `email`;

-- 修改列类型
ALTER TABLE `user` MODIFY COLUMN `name` VARCHAR(100) NOT NULL DEFAULT '' COMMENT '姓名';

-- 修改列名和类型
ALTER TABLE `user` CHANGE COLUMN `name` `user_name` VARCHAR(100) NOT NULL DEFAULT '' COMMENT '用户名';

-- 删除列
ALTER TABLE `user` DROP COLUMN `phone`;

-- 修改表名
ALTER TABLE `user` RENAME TO `t_user`;

-- 添加索引
ALTER TABLE `user` ADD INDEX `idx_phone` (`phone`);

-- 删除索引
ALTER TABLE `user` DROP INDEX `idx_phone`;

46. 添加索引的 SQL 语句是什么样的？

答案：

-- 普通索引
ALTER TABLE `user` ADD INDEX `idx_name` (`name`);
-- 或
CREATE INDEX `idx_name` ON `user` (`name`);

-- 唯一索引
ALTER TABLE `user` ADD UNIQUE INDEX `uk_email` (`email`);
-- 或
CREATE UNIQUE INDEX `uk_email` ON `user` (`email`);

-- 组合索引
ALTER TABLE `user` ADD INDEX `idx_name_age` (`name`, `age`);

-- 前缀索引
ALTER TABLE `user` ADD INDEX `idx_name` (`name`(10));

-- 主键索引
ALTER TABLE `user` ADD PRIMARY KEY (`id`);

-- 删除索引
DROP INDEX `idx_name` ON `user`;
ALTER TABLE `user` DROP INDEX `idx_name`;

47. 在已有表里添加字段并设置默认值的 SQL 语句？

答案：

ALTER TABLE `user` ADD COLUMN `status` TINYINT NOT NULL DEFAULT 1 COMMENT '状态 1正常 0禁用';

如果需要指定位置：

ALTER TABLE `user` ADD COLUMN `status` TINYINT NOT NULL DEFAULT 1 COMMENT '状态' AFTER `age`;

48. 在表中查重名的 SQL 语句怎么写？

答案：

-- 方法1：GROUP BY + HAVING
SELECT name, COUNT(*) AS cnt 
FROM user 
GROUP BY name 
HAVING COUNT(*) > 1;

-- 方法2：窗口函数（MySQL 8.0+）
SELECT * FROM (
  SELECT *, COUNT(*) OVER(PARTITION BY name) AS cnt 
  FROM user
) t WHERE cnt > 1;

-- 方法3：查找重复的详细记录
SELECT a.* FROM user a
INNER JOIN (
  SELECT name FROM user GROUP BY name HAVING COUNT(*) > 1
) b ON a.name = b.name;

49. 给你一组数据字段只有 name 和 id，SQL 查出 name 重复的字段？

答案：

-- 查出重复的 name 及其重复次数
SELECT name, COUNT(*) AS cnt 
FROM table_name 
GROUP BY name 
HAVING COUNT(*) > 1;

-- 查出重复的完整记录
SELECT * FROM table_name 
WHERE name IN (
  SELECT name FROM table_name GROUP BY name HAVING COUNT(*) > 1
);

-- 查出重复记录中 id 最小的保留，其他为重复数据
SELECT * FROM table_name 
WHERE name IN (
  SELECT name FROM table_name GROUP BY name HAVING COUNT(*) > 1
) AND id NOT IN (
  SELECT MIN(id) FROM table_name GROUP BY name HAVING COUNT(*) > 1
);

50. 一张学生表，如何查询哪个班级的女生人数大于 30？

答案：

SELECT class_id, COUNT(*) AS female_count
FROM student
WHERE gender = '女'
GROUP BY class_id
HAVING COUNT(*) > 30;

51. 一张表，学生 id / 课程 / 成绩，查询任意三门课程都大于 90 分的 SQL 语句？

答案：

-- 方法1：GROUP BY + HAVING
SELECT student_id
FROM score
WHERE score > 90
GROUP BY student_id
HAVING COUNT(DISTINCT course) >= 3;

-- 方法2：如果理解为"所有课程成绩都大于90分且至少3门课"
SELECT student_id
FROM score
GROUP BY student_id
HAVING COUNT(DISTINCT course) >= 3
   AND MIN(score) > 90;

-- 方法3：窗口函数（MySQL 8.0+）
SELECT DISTINCT student_id
FROM (
  SELECT student_id, 
         COUNT(CASE WHEN score > 90 THEN 1 END) OVER(PARTITION BY student_id) AS gt90_count
  FROM score
) t 
WHERE gt90_count >= 3;

十一、分布式与集群

52. 分库分表了解吗？怎么做的？

答案：

分库分表策略：

垂直分库：按业务模块拆分到不同数据库，如用户库、订单库、商品库。
垂直分表：将大字段拆分到扩展表，如商品基本信息表 + 商品详情表。
水平分库：同一表数据按规则分散到不同数据库，如按用户 ID 取模。
水平分表：同一库中按规则拆分多张表，如 order_0、order_1、order_2。

分片键选择：选择查询频率高、分布均匀的字段（如用户 ID）。

分片算法：

取模分片：table_num = user_id % 4
范围分片：按时间或 ID 范围
一致性 Hash：减少数据迁移

中间件：ShardingSphere-JDBC、MyCat。

分库分表后的问题：

跨库 JOIN → 代码聚合或冗余字段
分布式事务 → Seata、消息最终一致性
全局唯一 ID → 雪花算法
跨库分页 → 各分片查询后合并排序

53. 读写分离了解吗？怎么做的？

答案：

（与第 21 题类似，补充以下内容）

读写分离架构：

一主多从：1 个 Master 写，多个 Slave 读。
主从复制原理：Master 写 binlog → Slave 的 IO Thread 拉取 binlog 写入 relay log → Slave 的 SQL Thread 执行 relay log。
同步方式：异步复制（默认）、半同步复制、组复制。

实现方式：

代码层面：Spring AOP + AbstractRoutingDataSource 动态切换数据源
中间件：ShardingSphere-JDBC（客户端侧）、ShardingSphere-Proxy/MyCat（代理层）

注意：主从延迟问题，强一致性读走主库。

54. MySQL 搭建集群了吗？有几个节点？

答案：（示例，需结合实际项目）

常见集群方案：

主从复制：1 主 2 从（1 Master + 2 Slave）
MHA 高可用：1 主 2 从 + MHA Manager（自动故障切换）
MGR（MySQL Group Replication）：3 节点及以上，Paxos 协议保证一致性
InnoDB Cluster：MGR + MySQL Router + MySQL Shell

示例：生产环境通常是 1 主 2 从，通过 MHA 或 Orchestrator 实现故障自动切换。

55. MySQL 主从同步会有什么问题？

答案：

主从延迟：从库应用 binlog 有延迟，读从库可能读到过期数据。
- 原因：从库性能差、大事务、网络延迟、从库单线程执行（5.6 支持多线程并行复制）
- 解决：半同步复制、并行复制、强制走主库
数据不一致：
- 主库执行成功但从库失败（如唯一键冲突）
- 使用 Statement 格式 binlog 时，NOW()、UUID() 等函数值不同
- 解决：使用 Row 格式 binlog
主库宕机数据丢失：异步复制时，主库宕机未同步到从库的数据丢失
- 解决：半同步复制（至少一个从库确认接收后才返回成功）
从库延迟检测：SHOW SLAVE STATUS 查看 Seconds_Behind_Master

十二、其他

56. 事务管理的注解叫啥？

答案：

Spring 中事务管理注解为 @Transactional，标注在类或方法上。

常用属性：

rollbackFor：指定回滚异常类，默认只回滚 RuntimeException
propagation：事务传播行为（REQUIRED、REQUIRES_NEW、NESTED 等）
isolation：事务隔离级别
timeout：超时时间
readOnly：是否只读事务

注意事项：

@Transactional 只对 public 方法生效
同一类中方法调用不经过代理，事务不生效（需用 AopContext 或注入自身）
默认只回滚 RuntimeException，检查异常不回滚

57. 数据库连接池技术都有哪些？你们项目常用的是哪个？

答案：

连接池	特点
HikariCP	目前最流行，Spring Boot 2.x 默认，性能极高，轻量
Druid	阿里开源，监控功能强大，SQL 防火墙，国内常用
DBCP2	Apache 出品，Tomcat 早期使用
C3P0	老牌连接池，性能较差，已不推荐
Tomcat JDBC Pool	Tomcat 内置连接池

常用选择：

Spring Boot 项目默认用 HikariCP（性能最优）
需要监控和 SQL 防火墙功能的用 Druid

58. 项目的数据源配置中一般添加哪些项的配置？

答案：

spring:
  datasource:
    url: jdbc:mysql://localhost:3306/mydb?useUnicode=true&characterEncoding=utf8mb4&useSSL=false&serverTimezone=Asia/Shanghai&allowPublicKeyRetrieval=true
    username: root
    password: 123456
    driver-class-name: com.mysql.cj.jdbc.Driver
    
    # HikariCP 配置
    hikari:
      minimum-idle: 5          # 最小空闲连接数
      maximum-pool-size: 20    # 最大连接数
      idle-timeout: 600000     # 空闲连接超时时间（ms）
      max-lifetime: 1800000    # 连接最大存活时间（ms）
      connection-timeout: 30000 # 连接超时时间（ms）
      connection-test-query: SELECT 1  # 连接测试语句
      pool-name: MyHikariCP    # 连接池名称

或 Druid 配置：

spring:
  datasource:
    type: com.alibaba.druid.pool.DruidDataSource
    druid:
      initial-size: 5
      min-idle: 5
      max-active: 20
      max-wait: 60000
      time-between-eviction-runs-millis: 60000
      min-evictable-idle-time-millis: 300000
      validation-query: SELECT 1
      test-while-idle: true
      test-on-borrow: false
      test-on-return: false
      # 监控配置
      stat-view-servlet:
        enabled: true
        url-pattern: /druid/*
      filter:
        stat:
          enabled: true
        wall:
          enabled: true

URL 常用参数：

useUnicode=true&characterEncoding=utf8mb4：字符编码
useSSL=false：关闭 SSL
serverTimezone=Asia/Shanghai：时区
allowPublicKeyRetrieval=true：允许公钥检索
rewriteBatchedStatements=true：批量操作优化