【mysql】MySQL 数据库 30 道高频面试题（含答案）

MySQL 数据库 30 道高频面试题含答案这套题覆盖了从基础到架构的核心考点建议重点背诵加粗部分。一、基础篇1. MySQL 常用的存储引擎有哪些InnoDB 和 MyISAM 的区别是什么InnoDB默认支持事务、行级锁、外键采用聚簇索引支持崩溃恢复Crash-Safe适合写密集型应用。MyISAM旧版默认不支持事务、只支持表锁、不支持外键非聚簇索引支持全文索引适合读多写少场景。核心区别总结InnoDB 支持事务和行锁MyISAM 不支持InnoDB 崩溃后可恢复MyISAM 容易丢数据InnoDB 并发性能好MyISAM 并发差。2. CHAR 和 VARCHAR 的区别是什么CHAR定长字符串长度固定0-255存储时会用空格填充检索时去掉空格。存取速度快但浪费空间。适合存 MD5 值、手机号。VARCHAR变长字符串长度可变0-65535只占用实际长度1/2字节记录长度节省空间但存取速度稍慢。适合存用户名、地址。3. DROP、DELETE 和 TRUNCATE 的区别是什么DELETEDML 语句逐行删除支持 WHERE可回滚删除后不重置自增列计数器产生 Undo Log速度慢。TRUNCATEDDL 语句清空整表不支持 WHERE不可回滚重置自增列不产生 Undo Log速度快。DROPDDL 语句删除表结构和数据释放表所占空间不可回滚速度最快。4. 什么是视图View定义虚拟表其内容由查询定义一条 SELECT 语句。优点简化复杂 SQL、保护数据隐藏敏感字段、逻辑独立性。缺点性能较差简单视图还好复杂视图可能很慢、修改限制某些复杂视图不能更新。5. UNION 和 UNION ALL 的区别UNION对两个结果集进行并集操作去除重复行。UNION ALL不进行去重。建议如果确定结果没有重复数据或者不需要去重务必使用 UNION ALL因为去重非常消耗 CPU。6. 内连接、左连接、右连接的区别INNER JOIN返回两张表中匹配的记录。LEFT JOIN返回左表的全部记录即使右表没有匹配右表无匹配则补 NULL。RIGHT JOIN返回右表的全部记录即使左表没有匹配左表无匹配则补 NULL。口诀左连左为准右连右为准。7. GROUP BY 和 ORDER BY 的区别GROUP BY用于分组通常与聚合函数SUM, COUNT, AVG一起使用改变原数据集的形态行数变少。ORDER BY用于排序不改变数据集的行数只改变行的顺序。8. MySQL 中 NULL 值的处理NULL 代表未知数据。使用IS NULL或IS NOT NULL判断不能用 NULL。任何值与 NULL 运算结果都为 NULL。聚合函数COUNT(*)除外会忽略 NULL 值。尽量给关键字段设置默认值减少NULL 值。二、索引篇重中之重9. 索引的底层数据结构是什么为什么不用 Hash、二叉树B Tree。不用 Hash虽然等值查询 O(1)但不支持范围查询、不支持排序、模糊查询存在哈希冲突。不用二叉树/红黑树树的高度太高磁盘 I/O 次数多树高logN且无法很好地利用磁盘预读特性。B Tree 优势多路平衡查找树矮胖型减少磁盘 I/O叶子节点形成有序双向链表极大支持范围查询非叶子节点只存 key存储密度高。10. 聚簇索引和非聚簇索引的区别聚簇索引InnoDB索引和数据存放在一起。主键索引的叶子节点存储整行数据。一张表只有一个聚簇索引通常是主键。非聚簇索引二级索引叶子节点存储的是主键值而不是数据地址。回表使用二级索引查询时先找到主键再拿主键去聚簇索引查数据的过程。覆盖索引查询的列恰好是索引列不需要回表。11. 什么是最左前缀原则联合索引(a, b, c)相当于建立了(a),(a, b),(a, b, c)三个索引。生效情况where a1、where a1 and b2、where a1 and b2 and c3。失效情况where b2跳过最左、where a1 and b2范围查询后的字段失效、where a1 and c3跳过了 bc 失效。12. 哪些情况下索引会失效使用OR连接条件除非 OR 前后都有索引。复合索引未遵循最左前缀原则。在索引列上进行运算或函数操作如DATE(create_time)。使用LIKE %abc前导模糊。类型隐式转换如字符串字段不加引号where varchar_col 123。使用!、、NOT IN有时失效。全表扫描比走索引更快时数据量少或区分度低。13. Explain 执行计划中 type 字段的含义system/const表中只有一行记录系统表或主键/唯一索引等值查询最优。eq_ref联表查询中主键或唯一索引关联非常好。ref非唯一索引等值查询良好。range范围查询BETWEEN, IN, 可接受。index全索引扫描比 ALL 好一点因为只扫索引树。ALL全表扫描必须优化。目标至少优化到range最好到ref。14. 索引是不是越多越好不是。缺点占用磁盘空间降低 INSERT、UPDATE、DELETE 的速度需要维护 B 树优化器在选择索引时也会消耗更多时间。不适合建索引的情况表数据太少频繁更新的字段区分度低的字段如性别、状态位。15. 什么是前缀索引对字段的前 N 个字符建立索引而不是整个字段。目的减少索引占用的空间提高索引效率。适用场景TEXT/BLOB 类型或大字段的 VARCHAR。注意无法使用前缀索引做 ORDER BY 和 GROUP BY也无法做覆盖扫描。三、事务与锁篇16. ACID 特性是什么Atomicity原子性事务是不可分割的最小单元要么全做要么全不做Undo Log 实现。Consistency一致性事务执行前后数据库都必须处于一致的状态最终目标由原子性、隔离性、持久性共同保证。Isolation隔离性多个事务并发执行时互不干扰MVCC/Lock 实现。Durability持久性事务一旦提交其结果就是永久性的Redo Log 实现。17. MySQL 的事务隔离级别有哪些READ UNCOMMITTED读未提交会出现脏读、不可重复读、幻读。READ COMMITTED读已提交 RC解决脏读出现不可重复读、幻读Oracle 默认。REPEATABLE READ可重复读 RR解决脏读、不可重复读InnoDB 通过 Next-Key Lock 解决幻读MySQL 默认。SERIALIZABLE串行化最高隔离级别完全串行执行性能最低。18. 脏读、不可重复读、幻读的区别脏读读到其他事务未提交的数据Rollback 了。不可重复读同一事务内两次读取同一条记录数据内容变了被 UPDATE/DELETE。幻读同一事务内两次读取一个范围内的记录结果集行数变了多了或少了几行被 INSERT。侧重不可重复读侧重Update/Delete幻读侧重Insert。19. MVCC 的原理是什么多版本并发控制。核心通过保存数据在某个时间点的快照来实现。实现每行记录后面隐藏两个字段DB_TRX_ID事务IDDB_ROLL_PTR回滚指针。Undo Log用于保存数据的历史版本形成版本链。Read View在事务开始时生成一个 Read View根据规则判断版本链中哪个版本对当前事务可见。RC 级别下每次 SELECT 都生成新的 Read ViewRR 级别下只在第一次 SELECT 时生成 Read View。20. InnoDB 是如何解决幻读的在RR隔离级别下InnoDB 使用Next-Key Lock临键锁。Next-Key Lock Record Lock行锁Gap Lock间隙锁。它锁定的是一个索引区间左开右闭防止其他事务在这个区间内插入新数据从而彻底解决了幻读问题。21. MyISAM 和 InnoDB 的锁机制区别MyISAM只支持表级锁。读锁共享锁 S和写锁排他锁 X。并发度低锁冲突概率高。InnoDB支持行级锁和表级锁意向锁。默认是行锁基于索引实现。并发度高锁冲突概率低。22. 什么是死锁如何解决死锁两个或多个事务在执行过程中因争夺资源而造成的一种互相等待的现象。排查SHOW ENGINE INNODB STATUS;查看最近一次死锁信息。解决/预防设置超时时间innodb_lock_wait_timeout。开启死锁检测innodb_deadlock_detectON默认开启。业务逻辑尽量以相同的顺序访问表和行大事务拆小为表添加合理的索引减少锁的范围。四、日志与底层原理篇23. Redo Log、Undo Log 和 Binlog 的区别日志类型所属层级日志类型主要作用写入方式Redo LogInnoDB 引擎物理日志数据页修改崩溃恢复Crash-Safe循环写Undo LogInnoDB 引擎逻辑日志反向操作事务回滚、MVCC随机写BinlogMySQL Server逻辑日志SQL/行变更主从复制、数据备份追加写24. 两阶段提交XA是什么为了保证 Redo Log 和 Binlog 的一致性因为两者都是事务提交的关键依据。过程InnoDB 写入 Redo Log标记为prepare状态。Server 层写入 Binlog。提交事务InnoDB 将 Redo Log 标记为commit状态。如果崩溃恢复时发现 Redo Log 处于 prepare 状态就去检查 Binlog 是否完整完整则提交否则回滚。25. MySQL 的 WAL 机制是什么Write-Ahead Logging预写日志。核心思想在数据写入磁盘之前先将修改操作记录到日志Redo Log中。好处将随机写磁盘修改数据页变成了顺序写磁盘写日志极大提升了数据库的写入性能。脏页可以后台慢慢刷盘。五、优化与架构篇26. 如何排查慢 SQL开启慢查询日志slow_query_logON设置long_query_time。使用mysqldumpslow或pt-query-digest分析慢日志找出 Top SQL。使用EXPLAIN分析 SQL 执行计划重点看 type, key, rows, Extra。使用SHOW PROFILE分析 SQL 在 CPU、IO 上的消耗。检查 Schema 设计、索引设计、业务逻辑。-- 找出执行次数最多的慢查询高频慢 SQL mysqldumpslow-sc-t10/var/log/mysql/slow.log -- 找出单次执行最耗时的慢查询最慢 SQL mysqldumpslow-st-t10/var/log/mysql/slow.log -- 找出锁等待时间最长的 SQL mysqldumpslow-sl-t10/var/log/mysql/slow.log27. 大表分页优化Limit 100000, 10问题MySQL 需要扫描前 100010 条记录然后丢弃前 100000 条效率极低。优化方案覆盖索引 子查询SELECT * FROM table WHERE id (SELECT id FROM table LIMIT 100000, 1) LIMIT 10;利用自增主键SELECT * FROM table WHERE id 100000 LIMIT 10;前提是 id 连续且无断层。延迟关联先查主键再回表。28. 主从复制的原理主库Master将数据变更写入Binary LogBinlog。从库SlaveI/O Thread 连接到主库读取 Binlog写入本地的Relay Log中继日志。从库SQL Thread 读取 Relay Log重放执行更新数据。核心异步复制默认。29. 主从延迟的原因及解决方案原因主库并发高从库单线程重放SQL Thread从库硬件配置不如主库网络延迟大事务如大批量 DELETE/UPDATE。解决使用并行复制MySQL 5.7 支持基于组提交的并行复制。升级从库硬件。拆分大事务。强制读主库业务妥协。30. 什么情况下需要分库分表分表策略什么时候分单表数据量超过千万级数据库成为性能瓶颈索引效率下降单机磁盘不足。垂直拆分垂直分库按业务拆分用户库、订单库。垂直分表大表拆小表基础信息表 扩展信息表减少单行大小提升 Buffer Pool 利用率。水平拆分数据量大按某种规则Hash、Range、日期将数据分布到多个库/表中。带来的问题分布式事务、跨库 Join、全局主键 ID 生成、分页/排序困难。