MySQL面试题（二）

1.MySQL 中的事务隔离级别有哪些？

MySQL 定义了四种事务隔离级别，从低到高依次为：
读未提交（Read Uncommitted）：事务可读取其他未提交事务的数据，可能出现脏读、不可重复读、幻读。
读已提交（Read Committed）：事务只能读取其他已提交事务的数据，避免脏读，但可能出现不可重复读、幻读。
可重复读（Repeatable Read）：事务内多次读取同一数据结果一致，避免脏读、不可重复读，InnoDB 通过 MVCC 避免幻读。
串行化（Serializable）：事务串行执行，完全避免三种问题，但并发性能极差。

2.MySQL 默认的事务隔离级别是什么？为什么选择这个级别？

MySQL 默认隔离级别是可重复读（Repeatable Read）。选择原因：
平衡安全性与性能：避免脏读、不可重复读，通过 MVCC 机制实现非锁定读，并发性能优于串行化。
解决幻读：InnoDB 引擎在可重复读级别通过间隙锁（Gap Lock）+ 行锁组合，有效防止幻读。
适用多数场景：既满足大多数业务对数据一致性的要求，又不会过度牺牲并发能力，平衡了数据安全与系统吞吐量。

3. 数据库的脏读、不可重复读和幻读分别是什么？

脏读：事务 A 读取到事务 B 未提交的修改数据，若 B 回滚，A 读取的数据无效。
不可重复读：事务 A 多次读取同一数据，期间事务 B 修改并提交该数据，导致 A 两次读取结果不一致。
幻读：事务 A 按条件查询数据，期间事务 B 插入符合条件的新数据并提交，A 再次查询时出现 “新数据”，如同幻觉。

4.MySQL 中有哪些锁类型？

按粒度分：
表锁：锁定整张表，开销小、加锁快，并发度低（如 MyISAM 引擎默认表锁）。
行锁：锁定单行数据，开销大、加锁慢，并发度高（InnoDB 支持，基于索引实现）。
页锁：锁定一页数据（16KB），粒度介于表锁和行锁之间，较少使用。
按功能分：
共享锁（S 锁）：读锁，多个事务可同时持有，阻止写操作。
排他锁（X 锁）：写锁，仅一个事务持有，阻止其他读写操作。
意向锁：表级锁，指示事务将对表中行加 S 锁或 X 锁（如意向共享锁 IS、意向排他锁 IX）。
间隙锁（Gap Lock）：锁定索引区间，防止插入数据，解决幻读。
临键锁（Next-Key Lock）：行锁 + 间隙锁的组合，InnoDB 默认锁机制。

5.MySQL 的乐观锁和悲观锁是什么？

悲观锁：假设并发冲突会发生，访问数据时先加锁（如行锁、表锁），阻止其他事务修改，直至释放锁。实现方式：SELECT ... FOR UPDATE（排他锁）、LOCK TABLES（表锁）。适合写多读少场景。
乐观锁：假设并发冲突少，访问数据时不加锁，更新时通过版本号或时间戳判断数据是否被修改（如 WHERE version = 1），若未修改则更新并递增版本号，否则重试。实现方式：自定义版本字段 + 条件判断。适合读多写少场景。

6.MySQL 中如果发生死锁应该如何解决？

预防死锁：
统一事务中表的访问顺序，避免交叉访问。
控制事务大小，缩短持有锁的时间。
尽量使用低隔离级别（如读已提交），减少锁范围。
检测与处理：
MySQL 自动检测死锁，通过回滚持有锁少的事务解决。
配置 innodb_deadlock_detect 开启死锁检测（默认开启）。
查看死锁日志：SHOW ENGINE INNODB STATUS 分析死锁原因。
应急处理：手动 kill 死锁事务（KILL [进程ID]）。

7. 如何使用 MySQL 的 EXPLAIN 语句进行查询分析？

在 SQL 语句前加 EXPLAIN，执行后获取执行计划，重点关注以下字段：
id：查询中每个操作的标识，判断执行顺序。
select_type：查询类型（如 SIMPLE 简单查询、SUBQUERY 子查询）。
table：涉及的表。
type：访问类型，从优到差为 system > const > eq_ref > ref > range > index > ALL，ALL 表示全表扫描，需优化。
key：实际使用的索引，NULL 表示未使用索引。
rows：预估扫描行数，值越小越好。
Extra：额外信息（如 Using index 表示覆盖索引，Using filesort 表示文件排序，需优化）。

8.MySQL 中 count (*)、count (1) 和 count (字段名) 有什么区别？

count(*)：统计所有行数（包括 NULL），InnoDB 优化为扫描最小索引，效率高。
count(1)：统计所有行数（包括 NULL），与 count(*) 类似，性能接近，无本质区别。
count(字段名)：统计字段非 NULL 的行数，需逐行判断字段是否为 NULL，性能较差（尤其是大表）。
优先级：count(*) ≈ count(1) > count(字段名)（非主键字段）。

9.MySQL 中 int (11) 的 11 表示什么？

11 表示整数的显示宽度，仅用于查询结果的格式化显示，不影响字段存储范围和实际值。int 类型存储范围固定为 -2^31 ~ 2^31-1（4 字节），即使设置 int (3)，仍可存储 123456 等超过 3 位的整数，显示时不会截断，仅当指定 ZEROFILL 时，不足宽度会补零（如 int (3) ZEROFILL 存储 5 显示为 005）。

10.MySQL 中 varchar 和 char 有什么区别？

存储方式：varchar 是可变长度，仅存储实际数据 + 1-2 字节长度标识；char 是固定长度，不足长度用空格填充。
空间占用：varchar 节省空间（适合长文本、长度不固定场景）；char 浪费空间，但查询效率高（适合短文本、长度固定场景，如手机号、身份证号）。
处理方式：char 存储时自动填充空格，查询时自动去除；varchar 保留尾部空格。
最大长度：varchar 最大 65535 字节（受行总长度限制）；char 最大 255 字节。

11.MySQL 中如何进行 SQL 调优？

索引优化：创建合适索引（WHERE、JOIN、ORDER BY 字段），避免无效索引（函数操作、隐式转换），删除冗余索引。
SQL 语句优化：
避免 SELECT *，使用具体字段（利用覆盖索引）。
优化 JOIN 语句，小表驱动大表，确保关联字段有索引。
避免子查询，改用 JOIN（减少临时表）。
控制分页查询（如 LIMIT 100000, 10 改为基于主键定位）。
表结构优化：选择合适字段类型（如用 int 代替 varchar 存状态），拆分大表（垂直 / 水平拆分），使用分区表。
配置优化：调整缓冲池（innodb_buffer_pool_size）、日志大小（innodb_log_file_size）等参数。
监控分析：通过慢查询日志、EXPLAIN、Performance Schema 定位瓶颈。

12. 如何在 MySQL 中避免单点故障？

主从复制：部署一主多从架构，主库故障时切换到从库。
双主架构：两个主库互为主从，均可读写，一个故障后另一个继续服务。
集群方案：
MGR（MySQL Group Replication）：多节点组成集群，自动故障检测与切换，支持读写分离。
第三方工具：如 Percona XtraDB Cluster、Galera Cluster，提供高可用与数据一致性。
自动切换工具：结合 Keepalived、MHA（Master High Availability）实现故障自动转移，减少人工干预。
定期备份：确保数据可恢复，降低故障影响。

13. 如何在 MySQL 中实现读写分离？

架构设计：一主多从，主库负责写操作，从库负责读操作。
数据同步：通过主从复制机制，确保从库数据与主库一致。
路由实现：
应用层：代码中区分读写操作，写请求发主库，读请求发从库（需处理主从延迟）。
中间件：使用 MyCat、Sharding-JDBC、ProxySQL 等中间件自动路由，透明化读写分离。
注意事项：解决主从延迟（如强制读主库、使用半同步复制），避免从库写入（设置 read_only）。

14. 什么是 MySQL 的主从同步机制？它是如何实现的？

主从同步是指主库数据变更同步到从库，实现数据备份和读写分离。实现步骤：
主库开启 binlog，记录所有写操作（DDL、DML）。
从库启动 I/O 线程，连接主库，请求 binlog 日志。
主库启动 binlog dump 线程，发送 binlog 到从库，从库 I/O 线程将其写入 relay log（中继日志）。
从库启动 SQL 线程，读取 relay log 并执行，重演主库操作，保持数据一致。
核心文件：主库的 binlog，从库的 relay log、master.info（主库信息）、relay-log.info（中继日志位置）。

15. 如何处理 MySQL 的主从同步延迟？

优化主库：
减少大事务，拆分长事务为小事务。
避免频繁 DDL 操作（阻塞 binlog 生成）。
增大 binlog 缓存（binlog_cache_size）。
优化从库：
升级从库硬件（CPU、内存），提高 SQL 线程执行速度。
开启从库并行复制（slave_parallel_workers > 1），多线程执行 relay log。
架构调整：
使用半同步复制（rpl_semi_sync_master_enabled），确保主库写入至少一个从库后再返回。
关键读操作强制走主库，非关键读走从库。
监控与报警：通过 Seconds_Behind_Master 监控延迟，超过阈值触发报警。

16. 什么是分库分表？分库分表有哪些类型（或策略）？

分库分表是将大数据库或大表拆分为小库小表，解决单库单表数据量过大导致的性能问题。类型：
分库：按业务或数据范围拆分数据库，降低单库压力（如按用户地域分库）。
分表：将单表拆分为多个子表，提高查询效率。
策略：
水平拆分（按行拆分）：将表中不同行数据分到不同表，如按用户 ID 哈希、时间范围拆分。
垂直拆分（按列拆分）：将表中不同列拆分到不同表，如将大字段（text）拆分到单独表。

17. 如果组长要求你主导项目中的分库分表，大致的实施流程是？

需求分析：评估当前数据量、增长速度、瓶颈（查询慢、写入卡），确定分库分表目标。
方案设计：
选择拆分策略（水平 / 垂直，哈希 / 范围拆分键）。
确定分库分表数量，设计子库子表命名规则。
选择中间件（如 Sharding-JDBC、MyCat）或自研路由逻辑。
数据迁移：
全量迁移：将历史数据按规则迁移到新库表。
增量迁移：通过 binlog 同步迁移期间的新数据，确保数据一致。
应用改造：
适配中间件，修改 SQL 语句（如避免跨库 join）。
处理分布式事务、全局 ID 等问题。
测试与上线：
性能测试（查询、写入速度）、数据一致性验证。
灰度发布，监控线上指标，逐步切换流量。
运维支持：建立监控告警，定期优化拆分策略。

18. 对数据库进行分库分表可能会引发哪些问题？

复杂度提升：应用需处理路由逻辑，开发维护成本增加。
跨库操作困难：跨库 join、事务难以实现（需分布式事务，如 2PC、TCC）。
全局 ID 问题：需生成唯一 ID（如雪花算法、UUID），避免主键冲突。
数据迁移风险：历史数据迁移可能导致不一致，增量同步需处理延迟。
扩容难题：按范围拆分时，热点数据可能集中在某分表，扩容需重新分片。
运维成本增加：多库多表监控、备份、故障处理更复杂。

19. 从 MySQL 获取数据，是从磁盘读取的吗？（buffer pool）

不一定。InnoDB 通过缓冲池（buffer pool）缓存常用数据页（16KB），读取数据时：
优先检查缓冲池，若数据存在（缓存命中），直接从内存读取，速度快。
若缓冲池无数据（缓存未命中），从磁盘读取数据页到缓冲池，再返回数据。
缓冲池通过 LRU（最近最少使用）算法管理，淘汰不常用数据页，提高缓存利用率。因此，热点数据通常从内存读取，冷数据从磁盘读取。

20.MySQL 的 Doublewrite Buffer 是什么？它有什么作用？

Doublewrite Buffer 是 InnoDB 中的一块内存区域（大小为 2MB），用于在数据页写入磁盘前做二次备份。作用：
解决部分写失效（partial write）问题：当数据库崩溃时，若数据页写入磁盘过程中断（如仅写入一半），会导致数据页损坏。
工作流程：数据页先写入 Doublewrite Buffer（内存 + 磁盘），再从内存复制到数据文件。崩溃恢复时，若数据文件页损坏，可从 Doublewrite Buffer 磁盘区域恢复完整数据页。
Doublewrite Buffer 牺牲少量性能（额外一次写操作），换取数据页的完整性，是 InnoDB 数据可靠性的重要保障。

21.MySQL 的查询优化器如何选择执行计划？

MySQL 查询优化器基于成本模型选择执行计划，步骤：
解析 SQL 生成语法树，分析表、字段、操作符等。
生成可能的执行计划（如不同索引组合、JOIN 顺序）。
估算每个计划的成本（I/O 成本、CPU 成本），包括扫描行数、索引使用、临时表等。
选择成本最低的执行计划。
影响因素：表统计信息（行数、数据分布）、索引信息、配置参数（如 join_buffer_size）。优化器可能因统计信息不准确选择非最优计划，可通过 ANALYZE TABLE 更新统计信息。

22. 什么是数据库的逻辑删除？数据库的物理删除和逻辑删除有什么区别？

逻辑删除：不实际删除数据，通过新增字段（如 is_deleted）标记数据状态（0 未删，1 已删），查询时过滤已标记数据。
物理删除：直接从表中删除数据，释放存储空间。
区别：1. 数据保留：逻辑删除保留数据，物理删除彻底移除；2. 恢复性：逻辑删除可恢复，物理删除难恢复；3. 性能：逻辑删除会增加表体积，物理删除可能产生碎片；4. 适用场景：逻辑删除适合需保留历史数据（如订单），物理删除适合无价值数据。

23. 什么是数据库的逻辑外键？数据库的物理外键和逻辑外键各有什么优缺点？

物理外键：通过 FOREIGN KEY 约束定义在表结构中，数据库自动维护引用完整性（如禁止删除被引用记录）。
优点：数据库层面保证数据一致性，减少应用层逻辑。
缺点：增加表耦合度，影响性能（尤其大表），分库分表不支持。
逻辑外键：不在数据库定义约束，仅在应用层通过代码保证引用关系（如订单表 user_id 关联用户表）。
优点：降低表耦合，适合分库分表，灵活度高。
缺点：依赖应用层逻辑，可能因代码漏洞导致数据不一致。

24.MySQL 事务的二阶段提交是什么？

二阶段提交是分布式事务中保证数据一致性的协议，分两个阶段：
准备阶段：协调者向所有参与者发送准备请求，参与者执行事务但不提交，返回是否就绪。
提交阶段：若所有参与者就绪，协调者发送提交请求，参与者提交事务；若有参与者未就绪，发送回滚请求，参与者回滚。
MySQL 中，XA 事务（分布式事务）使用二阶段提交，InnoDB 作为参与者支持该协议，但性能较低，实际中多采用最终一致性方案（如消息队列）替代。

25.MySQL 三层 B+ 树能存多少数据？

假设：InnoDB 页大小 16KB，主键为 BIGINT（8 字节），指针 6 字节，每个非叶子节点可存 16*1024/(8+6) ≈ 1170 个索引项。
三层 B+ 树：根节点 1170 个指针 → 第二层 1170 个节点，每个存 1170 个指针 → 第三层（叶子节点）共 1170*1170 ≈ 136 万 个节点。
每个叶子节点（聚簇索引）存约 16*1024/100 ≈ 160 行数据（假设每行 100 字节）。
总数据量：136 万 * 160 ≈ 2.18 亿 行。
实际因数据行大小、索引类型不同有所差异，大致在千万到亿级。

26.MySQL 在设计表（建表）时需要注意什么？

字段类型选择：优先小类型（如用 tinyint 代替 int），避免 NULL（用默认值），金额用 DECIMAL。
主键设计：建议用自增 ID（聚簇索引效率高），避免 UUID（无序导致页分裂）。
索引规划：为查询字段建索引，控制数量，联合索引按最左前缀原则排序。
表大小控制：单表数据量建议不超过 500 万行，提前规划分表策略。
冗余与范式：平衡范式与冗余，适当冗余减少 JOIN（如订单表存用户名）。
特殊字段：逻辑删除字段（is_deleted）、创建 / 更新时间（create_time/update_time）。
存储引擎：默认用 InnoDB（支持事务、行锁），避免 MyISAM。

27.MySQL 插入一条 SQL 语句，redo log 记录的是什么？

redo log 记录的是数据页的物理修改操作，而非 SQL 语句本身。例如插入一条记录时，redo log 会记录 “在某表空间的某数据页中，偏移量 X 处写入了 Y 字节的数据（具体内容）”。这样崩溃恢复时，可通过重做这些物理操作，将数据恢复到崩溃前的状态，保证事务持久性。

28.SQL 中 select、from、join、where、group by、having、order by、limit 的执行顺序是什么？

FROM：指定查询的表。
JOIN：关联其他表，生成临时结果集。
WHERE：过滤临时结果集中的行（在分组前过滤）。
GROUP BY：按指定字段分组。
HAVING：过滤分组后的结果（在分组后过滤）。
SELECT：选择需要的字段。
ORDER BY：对结果集排序。
LIMIT：限制返回的行数。

29. 为什么 MySQL 索引用的是 B+ 树而不是红黑树？

磁盘 I/O 效率：B+ 树是多路平衡树，层高更低（通常 3-4 层），减少磁盘访问次数；红黑树是二叉树，层高更高，I/O 次数多。
范围查询性能：B+ 树叶子节点链表连接，范围查询只需遍历链表；红黑树需多次回溯，效率低。
存储密度：B+ 树非叶子节点只存索引和指针，相同空间存更多节点；红黑树每个节点存数据，存储密度低。
磁盘预读：B+ 树节点大小与磁盘页（16KB）匹配，利用预读特性一次性加载多个节点。

30.MySQL 一张表最多可以有多少列？

MySQL 中，一张表最多可定义 4096 列，但受以下限制：
单行数据总长度：InnoDB 单行数据最大 65535 字节（不包括 TEXT/BLOB 类型），若列过多可能超出限制。
存储引擎：不同引擎可能有额外限制（如 MyISAM 对列数限制更严格）。
实际设计中，表列数应远少于 4096，过多列会导致查询效率低，建议按业务拆分表（垂直拆分）。