MySQL面试题（三）

1.MySQL 中的 Log Buffer 是什么？它有什么作用？

Log Buffer 是 InnoDB 用于临时存储 redo log 的内存缓冲区。作用：临时缓存事务产生的 redo log，减少直接写入磁盘的次数，提升性能。当事务提交、缓冲区满（默认 16MB）或定期（由 innodb_flush_log_at_trx_commit 控制）时，数据会刷新到磁盘的 redo log 文件，确保事务持久性。

2. 为什么在 MySQL 中不推荐使用多表 JOIN？

性能问题：多表 JOIN 需扫描大量数据并建立临时表，尤其是无索引时，效率极低。
复杂度高：SQL 语句冗长，难以维护和优化，且容易触发全表扫描。
锁冲突风险：JOIN 操作可能持有更多锁，延长锁等待时间，影响并发。
分库分表不兼容：分布式环境下，多表 JOIN 跨库操作困难，性能开销大。
建议：尽量减少 JOIN 表数量（最好不超过 3 张），或通过应用层拆分查询。

3.MySQL 中如何解决深度分页的问题？

基于主键定位：利用索引有序性，通过上一页最后一条记录的主键定位，如 WHERE id > 1000000 LIMIT 10，避免全表扫描。
延迟关联：先查主键，再关联表获取字段，如 SELECT t.* FROM (SELECT id FROM table LIMIT 1000000, 10) AS sub JOIN table t ON sub.id = t.id。
限制分页深度：前端限制最大页码，或提示用户使用筛选条件缩小范围。
缓存热门页：对高频访问的分页结果进行缓存，减少数据库查询。

4. 如何在 MySQL 中监控和优化慢 SQL？

监控：
开启慢查询日志：配置 slow_query_log = 1、long_query_time = 1（秒），记录执行超时的 SQL。
工具分析：使用 mysqldumpslow、pt-query-digest 分析慢日志，定位高频慢 SQL。
实时监控：通过 Performance Schema 或第三方工具（如 Prometheus + Grafana）监控 SQL 执行耗时。
优化：
加索引：为 WHERE、JOIN、ORDER BY 字段创建合适索引。
改写 SQL：避免 SELECT *、子查询，优化 JOIN 顺序，拆分大查询。
表结构优化：分表分库，减少单表数据量。

5.MySQL 中 DELETE、DROP 和 TRUNCATE 的区别是什么？

DELETE：删除表中数据，DML 操作，可带 WHERE 条件，逐行删除，记录日志，支持回滚；不释放表空间，索引保持不变。
DROP：删除表（或库），DDL 操作，删除表结构及所有数据，释放表空间，不可回滚；速度快，元数据直接删除。
TRUNCATE：清空表数据，DDL 操作，不记录逐行日志，不可回滚；释放表空间（部分引擎），重置 AUTO_INCREMENT，速度快于 DELETE。

6.MySQL 中 INNER JOIN、LEFT JOIN 和 RIGHT JOIN 的区别是什么？

INNER JOIN（内连接）：只返回两表中匹配条件的记录，无匹配的不显示。
LEFT JOIN（左连接）：返回左表所有记录，右表匹配的记录显示对应字段，无匹配的右表字段为 NULL。
RIGHT JOIN（右连接）：返回右表所有记录，左表匹配的记录显示对应字段，无匹配的左表字段为 NULL。

7.MySQL 中 LIMIT 100000000, 10 和 LIMIT 10 的执行速度是否相同？

不同。LIMIT 10 只需扫描前 10 条记录，速度快；LIMIT 100000000, 10 需扫描前 100000010 条记录，丢弃前 100000000 条，仅返回最后 10 条，扫描行数多，且可能无法利用索引优化，执行速度慢，属于深度分页问题。

8.MySQL 中 DATETIME 和 TIMESTAMP 类型的区别是什么？

存储范围：DATETIME 为 '1000-01-01 00:00:00' 到 '9999-12-31 23:59:59'；TIMESTAMP 为 '1970-01-01 00:00:01' 到 '2038-01-19 03:14:07'。
时区依赖：DATETIME 存储原始时间，不随时区变化；TIMESTAMP 存储 UTC 时间，查询时根据当前时区转换，显示值随时区变化。
存储大小：DATETIME 占 8 字节；TIMESTAMP 占 4 字节，更节省空间。

9. 数据库的三大范式是什么？

第一范式（1NF）：列不可再分，每个字段是原子值（如 “地址” 拆分为 “省”“市” 等）。
第二范式（2NF）：满足 1NF，且非主键字段完全依赖主键（消除部分依赖，如订单表中商品信息需拆分到订单明细表）。
第三范式（3NF）：满足 2NF，且非主键字段不依赖其他非主键字段（消除传递依赖，如用户表中 “地区名称” 不应依赖 “地区 ID”，需拆分地区表）。
目的：减少数据冗余，避免更新异常。

10. 在 MySQL 中，你使用过哪些函数？

字符串函数：CONCAT()（拼接）、SUBSTRING()（截取）、LENGTH()（长度）、TRIM()（去空格）。
日期函数：NOW()（当前时间）、DATE_FORMAT()（格式化）、DATEDIFF()（日期差）。
聚合函数：COUNT()（计数）、SUM()（求和）、AVG()（平均值）、MAX()/MIN()（最大 / 小值）。
逻辑函数：IF()（条件判断）、CASE WHEN（多条件判断）。
数学函数：ROUND()（四舍五入）、ABS()（绝对值）。

11.MySQL 中 TEXT 类型最大可以存储多长的文本？

TEXT 类型分四级，最大存储长度如下：
TINYTEXT：255 字节（2^8 - 1）。
TEXT：65535 字节（64KB，2^16 - 1）。
MEDIUMTEXT：16777215 字节（16MB，2^24 - 1）。
LONGTEXT：4294967295 字节（4GB，2^32 - 1）。
存储超过 64KB 的文本需用 MEDIUMTEXT 或 LONGTEXT，但会影响查询性能。

12.MySQL 中 AUTO_INCREMENT 列达到最大值时会发生什么？

当 AUTO_INCREMENT 列达到最大值（如 INT 类型为 2^31-1），再插入新记录时会触发错误（Duplicate entry '最大值' for key 'PRIMARY'），无法生成新的自增值。解决方式：提前将字段类型改为更大范围（如 BIGINT），或在应用层处理自增逻辑。

13. 在 MySQL 中存储金额数据，应该使用什么数据类型？

推荐使用 DECIMAL 类型（如 DECIMAL (10,2)），而非 FLOAT 或 DOUBLE。原因：
DECIMAL 是精确数值类型，无精度损失，适合金额等对精度敏感的数据。
FLOAT/DOUBLE 是浮点类型，存在精度误差（如 0.1 无法精确存储），可能导致计算错误。
也可将金额乘以 100 转为整数（如分单位），用 INT 存储，避免小数运算。

14. 什么是数据库的视图？

视图是基于 SQL 查询结果的虚拟表，本身不存储数据，仅保存查询定义。作用：
简化查询：将复杂 SQL 封装为视图，简化调用。
权限控制：隐藏敏感字段，只暴露必要数据给用户。
逻辑独立：应用层调用视图，底层表结构变更时，只需修改视图定义，不影响应用。
视图可查询，但更新（INSERT/UPDATE/DELETE）受原表约束限制，部分复杂视图不可更新。

15. 什么是数据库的游标？

游标是数据库中用于遍历查询结果集的数据库对象，适用于逐行处理数据（如存储过程中）。工作流程：
声明游标：定义要遍历的查询语句。
打开游标：执行查询，准备结果集。
提取数据：逐行读取结果集中的数据。
关闭游标：释放结果集资源。
释放游标：删除游标定义。
游标适合处理少量数据，大量数据使用游标会导致性能下降，应尽量避免。

16. 为什么不推荐在 MySQL 中直接存储图片、音频、视频等大容量内容？

性能问题：大容量内容会增大表体积，导致查询、备份、恢复速度变慢，缓冲池被占用，影响其他数据访问。
存储效率低：数据库存储二进制数据不如文件系统高效，且会增加数据库 I/O 压力。
维护困难：更新或删除大容量数据时，操作耗时，易导致锁阻塞。
建议：文件存储在文件系统（如服务器本地、对象存储），数据库仅保存文件路径或 URL。

17. 相比于 Oracle，MySQL 的优势有哪些？

开源免费：无需支付许可费用，降低成本。
轻量级：安装部署简单，资源占用少，适合中小型应用。
性能优秀：针对读多写少场景优化，在 Web 应用中表现出色。
社区活跃：更新迭代快，问题解决方案丰富。
易于扩展：支持主从复制、分库分表，适合分布式架构。
Oracle 优势在复杂事务、高并发和企业级特性，适合大型核心业务。

18.MySQL 中 VARCHAR (100) 和 VARCHAR (10) 的区别是什么？

存储限制：VARCHAR (100) 最多存储 100 个字符，VARCHAR (10) 最多存储 10 个字符，超过限制会截断或报错（依严格模式而定）。
存储长度：均按实际字符数 + 1-2 字节长度标识存储，相同内容（如 5 个字符）占用空间相同。
性能影响：定义过长可能导致内存临时表空间浪费，查询时分配的缓冲区更大，建议按实际需求定义长度。

19. 在什么情况下，不推荐为数据库建立索引？

表数据量小（如少于 1000 行）：全表扫描比索引查询更快，索引反而增加存储和维护成本。
频繁更新的字段：索引会随数据更新同步修改，频繁更新导致性能开销大。
低基数字段（如性别、状态）：区分度低，索引过滤效果差，可能退化为全表扫描。
临时表或日志表：数据生命周期短，索引维护成本高于查询收益。
频繁删除的表：大量删除后索引碎片化严重，影响性能。

20.MySQL 中 EXISTS 和 IN 的区别是什么？

执行逻辑：EXISTS 是 “存在性判断”，子查询返回 TRUE/FALSE，只要子查询有结果就返回；IN 是 “值匹配”，判断主查询字段是否在子查询结果集中。
性能差异：子查询结果集大时，EXISTS 更高效（只需判断存在）；子查询结果集小时，IN 性能相当或更优。
空值处理：IN 子查询包含 NULL 时，结果可能不符合预期（NULL <> NULL）；EXISTS 子查询含 NULL 时，仍可能返回 TRUE。
示例：

WHERE EXISTS (SELECT 1 FROM t2 WHERE t1.id = t2.id) vs WHERE t1.id IN (SELECT id FROM t2)。

21. 什么是 Write-Ahead Logging (WAL) 技术？它的优点是什么？MySQL 中是否用到了 WAL？

定义：WAL 是一种日志优先写入策略，指数据修改时，先将操作记录到日志（如 redo log），再写入实际数据文件。
优点：1. 保证数据一致性，崩溃后可通过日志恢复；2. 减少磁盘 I/O 次数，日志顺序写入比数据随机写入更快；3. 提高并发性能，无需等待数据刷盘即可提交事务。
MySQL 应用：InnoDB 引擎使用 WAL 技术，通过 redo log 实现，事务提交时先写 redo log，再异步刷盘到数据文件。

22. 你们生产环境的 MySQL 中使用了什么事务隔离级别？为什么？

生产环境通常使用 可重复读（Repeatable Read）。原因：1. 平衡安全性与性能，避免脏读、不可重复读，通过 MVCC 实现非锁定读，并发性能优于串行化；2. InnoDB 在该级别通过间隙锁解决幻读，满足多数业务数据一致性需求；3. 避免读已提交级别可能的主从同步问题（如 binlog 格式为 STATEMENT 时）。对于对一致性要求极高的场景（如金融交易），可能提升至串行化。

23. 为什么阿里巴巴的 Java 手册不推荐使用存储过程？

可移植性差：存储过程依赖具体数据库，迁移到其他数据库（如 MySQL 到 Oracle）需重写。
开发维护难：调试、版本控制、代码复用不如应用层代码方便，团队协作成本高。
性能隐患：复杂存储过程可能导致数据库压力过大，且难以优化。
扩展性差：存储过程运行在数据库服务器，高并发下会占用数据库资源，影响整体性能。
分布式不兼容：分库分表场景下，存储过程跨库执行困难。

24. 如何实现数据库的不停服迁移？

双写同步：
新增目标库，修改应用同时写入源库和目标库。
全量迁移历史数据到目标库，通过校验工具确保数据一致。
切换读流量到目标库，验证无误后停写源库，完成迁移。
基于 binlog 同步：
使用工具（如 Canal、Debezium）解析源库 binlog，实时同步到目标库。
全量迁移后，对比数据一致性，逐步切换读写流量。
中间件路由：通过 Sharding-JDBC 等中间件代理流量，先双写，再平滑切换数据源。
关键：确保数据一致性，分阶段切换流量，预留回滚机制。

25.MySQL 数据库的性能优化方法有哪些？

索引优化：创建合适索引，避免无效索引，定期清理冗余索引。
SQL 优化：避免全表扫描、大事务，优化 JOIN 和子查询，使用覆盖索引。
表结构优化：选择合适字段类型（如用 int 代替 varchar 存状态），拆分大表，使用分区表。
存储引擎优化：InnoDB 调整缓冲池（innodb_buffer_pool_size）、日志大小等参数。
架构优化：主从复制、读写分离、分库分表，减轻单库压力。
硬件优化：使用 SSD 提升 I/O 速度，增加内存提高缓存命中率。
监控与调优：通过慢查询日志、EXPLAIN 定位瓶颈，持续优化。

26.MySQL 中 InnoDB 存储引擎与 MyISAM 存储引擎的区别是什么？