MySQL高频面试题及详细答案（实战版）

MySQL高频面试题及详细答案（实战版）一、MySQL基础概念1、MySQL的存储引擎有哪些？常用的区别是什么？MySQL支持多种存储引擎，最常用的是InnoDB和MyISAM，其余还有Memory、Archive、CSV等，日常开发核心使用前两者。核心区别：InnoDB：MySQL5.5及之后默认引擎。支持事务、行级锁、外键、MVCC多版本并发控制，崩溃可安全恢复。读写并发性能好，适合业务系统绝大多数场景，唯一缺点是占用磁盘空间稍大。MyISAM：老版本默认引擎。不支持事务、不支持行锁、无外键机制，采用表级锁。查询速度极快、占用空间小、资源消耗低，但并发写入性能差，数据库崩溃后无法保证数据完整，现在基本只用于纯静态查询、无更新的历史数据表。Memory：数据存储在内存中，重启数据清空，速度最快，适合临时缓存、临时统计场景。2、char和varchar的区别？各自适用场景char(n)：固定长度字符类型。定义多少长度，就占用多少存储空间，不足长度会自动补空格，查询时自动去除尾部空格。读写效率高，无碎片。varchar(n)：可变长度字符类型。占用空间为实际数据长度+1-2字节长度标识，节省存储空间，读写效率略低于char，会产生少量数据碎片。使用场景：char：适合长度固定的数据，比如手机号、身份证号、性别、固定编码。varchar：适合长度不固定的数据，比如用户名、备注、地址、文章标题等绝大多数文本场景。3、datetime和timestamp的区别存储范围：datetime支持1000-01-01~9999-12-31；timestamp支持1970-01-01~2038-01-19，时间范围有限，2038年后会失效。存储空间：datetime占用8字节，timestamp占用4字节。时区影响：timestamp会跟随数据库时区自动转换，datetime不受时区影响。默认值：timestamp支持自动更新当前时间，可设置数据新增、修改时自动赋值时间，datetime需要手动设置默认值或通过触发器实现。实战开发中，优先用datetime，避免2038时间溢出问题。二、索引核心考点（面试重点）1、什么是索引？索引的优缺点索引是数据库为数据表字段建立的有序数据结构，相当于书籍的目录，用于快速定位数据，避免全表扫描。InnoDB默认使用B+树索引。优点：大幅提升查询、排序、分组、去重的执行效率，减少数据库IO消耗。缺点：占用磁盘存储空间；新增、修改、删除数据时，需要同步更新索引结构，会降低写入性能。因此索引不是越多越好。2、B树和B+树的区别？为什么MySQL用B+树做索引？核心区别：B树：所有节点都存储数据和索引键，叶子节点、非叶子节点都存数据。B+树：非叶子节点只存储索引键，不存储真实数据；所有真实数据都存储在叶子节点；叶子节点通过链表串联，有序连贯。MySQL选用B+树的原因：非叶子节点数据量小，单节点可存储更多索引键，树的层级更低，查询磁盘IO次数更少，速度更快。叶子节点链表有序，范围查询、排序、分页查询效率极高，适配数据库绝大多数查询场景。查询性能稳定，所有数据查询都需要走到叶子节点，单次查询耗时差距小。3、聚簇索引和非聚簇索引的区别聚簇索引（主键索引）叶子节点直接存储整行完整数据。一张表只能有一个聚簇索引，默认基于主键创建，无主键时会自动选取唯一索引，无唯一索引则自动生成隐藏主键。主键查询速度最快，直接命中完整数据。非聚簇索引（二级索引/普通索引）叶子节点只存储索引字段值和对应的主键值，不存储完整数据。一张表可以有多个非聚簇索引。通过普通索引查询数据时，会先查到主键，再通过主键走聚簇索引查询完整数据，这个过程叫做回表。4、什么是覆盖索引？如何避免回表？如果一条SQL查询的所有字段，都包含在建立的索引中，无需回表查询完整数据，直接通过索引即可拿到全部结果，这种索引就是覆盖索引。示例：给user表建立索引(name,age)，执行selectname,agefromuserwherename='张三'，此时触发覆盖索引，无需回表，查询效率极高。核心作用：避免回表操作，大幅提升查询性能，是日常SQL优化的常用手段。5、最左前缀原则是什么？联合索引遵循最左匹配规则，查询条件需要匹配联合索引的左侧连续字段，才能命中索引。示例：建立联合索引(a,b,c)wherea=1、wherea=1andb=2、wherea=1andb=2andc=3：正常命中索引。whereb=2、wherec=3、whereb=2andc=3：无法命中索引，触发全表扫描。wherea=1andc=3：只能命中a字段索引，c字段失效，索引不完整。因此建立联合索引时，要把高频查询、区分度高的字段放在左侧。6、索引失效的常见场景（高频考点）使用or连接条件，若or前后有字段无索引，整体索引失效。索引字段参与运算、使用函数，如whereage+1=18、whereleft(name,2)='张'。隐式类型转换，字符串字段不加引号、数字字段传字符串。使用!=、<>、notin、notlike等反向查询。like以%开头模糊查询，如'%张三'，后置模糊'张三%'可走索引。联合索引不满足最左前缀原则。MySQL优化器判断全表扫描比索引查询更快，自动放弃索引（数据量少、索引区分度低时常见）。三、事务与MVCC1、什么是事务？事务的四大特性ACID事务是数据库执行的最小逻辑单元，一组SQL语句要么全部执行成功，要么全部失败回滚，保证数据一致性。ACID四大特性：原子性(A)：事务不可分割，整体成功或整体回滚。一致性(C)：事务执行前后，数据库数据完整性、约束规则保持一致，不会出现非法数据。隔离性(I)：多个事务并发执行时，相互独立互不干扰。持久性(D)：事务提交成功后，数据永久落地，数据库崩溃也不会丢失。2、MySQL四种事务隔离级别及问题MySQLInnoDB支持四种隔离级别，级别越高，并发性能越低，数据安全性越高，默认级别是可重复读。读未提交：可读取其他事务未提交数据，会出现脏读、不可重复读、幻读，基本不用。读已提交：只能读取已提交数据，解决脏读，存在不可重复读、幻读问题（Oracle默认级别）。可重复读：同一事务内，多次读取同一数据结果一致，解决脏读、不可重复读，存在幻读问题（MySQL默认）。串行化：事务串行执行，完全解决三类问题，无并发能力，性能极低，极少使用。三类并发问题定义：脏读：一个事务读取到另一个事务未提交的脏数据。不可重复读：同一事务内，两次读取同一数据，结果不一致（其他事务中途修改提交）。幻读：同一事务内，两次范围查询，结果条数不一致（其他事务新增/删除数据）。3、MVCC多版本并发控制原理MVCC是InnoDB实现事务隔离、提升并发性能的核心机制，核心是读写不加锁、读快照数据，解决并发读写冲突。核心依赖三个机制：隐藏字段、undolog、readview每条数据都有隐藏字段，记录数据最新修改事务ID。数据被修改时，旧数据存入undolog（回滚日志），形成数据版本链。事务查询时生成readview视图，通过比对事务ID，从版本链中筛选出当前事务可见的历史版本数据。可重复读级别下，事务只会在第一次查询时生成readview，后续复用，保证多次查询结果一致，解决不可重复读，同时通过间隙锁抑制大部分幻读场景。四、锁机制1、行锁和表锁的区别表级锁：锁住整张表，加锁快、开销小，并发能力差，MyISAM默认使用。一旦加锁，其他事务无法读写整张表数据。行级锁：只锁住命中的单行数据，并发能力强，InnoDB默认使用。仅锁定操作行，其他行可正常读写，开销大、加锁慢。2、什么是意向锁？作用是什么？意向锁是InnoDB的表级辅助锁，分为意向共享锁、意向排他锁，是行锁的前置标记。核心作用：快速判断整张表是否存在行锁，无需遍历所有行数据，提升锁冲突检测效率，协调表锁和行锁的并发冲突。3、记录锁、间隙锁、临键锁三者都是InnoDB行锁的细分类型，用于解决幻读问题：记录锁：锁定具体存在的索引行，精准锁定单行数据。间隙锁：锁定两条索引数据之间的空白区间，防止其他事务新增数据，专门解决幻读。临键锁：记录锁+间隙锁的组合，是可重复读隔离级别下默认的加锁方式，锁定当前数据及左侧间隙，彻底杜绝幻读。五、SQL优化与慢查询1、explain执行计划核心字段解读explain用于分析SQL执行逻辑，判断索引是否生效、查询效率是否合理，核心关键字段：type：查询类型，性能从优到劣：system>const>eq_ref>ref>range>index>all。日常开发至少保证达到ref/range级别，all代表全表扫描，必须优化。key：本次查询实际命中的索引，为null表示未走索引。rows：预估扫描的数据行数，数值越小性能越好。extra：额外信息，出现Usingfilesort（文件排序）、Usingtemporary（临时表）代表SQL存在性能问题，需要重点优化。2、常见SQL优化方案避免select*，只查询业务需要的字段，减少数据传输和回表概率。合理建立索引，高频查询字段建索引，遵循最左前缀原则，控制索引数量。避免索引失效场景，杜绝字段运算、函数、反向查询、前置模糊匹配。优化limit分页，深分页（limit10000,10）采用主键偏移查询优化。大表避免join、子查询，可拆分SQL、冗余字段、分表处理。优先使用in、exists，根据数据量选择：小表驱动大表用in，大表驱动小表用exists。杜绝or滥用，多条件or可拆分union查询，提升索引命中率。3、慢查询日志作用与配置慢查询日志用于记录执行时间超过指定阈值的SQL语句，是排查数据库性能瓶颈的核心工具。核心配置：开启慢查询、设置超时阈值（默认10秒，生产一般设置1-2秒）、指定日志存储路径。线上定期分析慢日志，优化低效SQL，避免数据库堆积慢查询导致服务卡顿。六、日志机制与故障恢复1、redolog和undolog的作用与区别redolog（重做日志）核心作用：保证事务持久性，实现崩溃恢复。机制：事务执行时，先写redolog，再刷新磁盘数据。数据库崩溃重启后，通过redolog重放未落地的事务数据，保证数据不丢失。undolog（回滚日志）核心作用：保证事务原子性、实现MVCC多版本控制。机制：数据修改前，记录旧数据到undolog，事务失败时通过日志回滚数据；同时为快照读提供历史数据版本。2、binlog日志作用binlog是MySQL二进制日志，属于服务层日志，所有存储引擎均可使用。核心用途：记录所有数据库增删改操作，用于主从复制、数据恢复、日志审计。三种日志格式：statement（记录SQL语句）、row（记录数据行变更，默认）、mixed（混合模式）。生产环境优先使用row模式，数据精准，避免主从数据不一致。七、高可用与分库分表1、MySQL主从复制原理核心流程：主库写入数据后记录binlog日志，从库实时拉取主库binlog，重放日志语句，实现主从数据同步。三个核心步骤：主库：事务提交后，写入binlog日志。从库IO线程：连接主库，拉取binlog日志，写入本地relay日志。从库SQL线程：读取本地relay日志，执行SQL，同步数据。实战用途：读写分离、数据备份、高可用切换、负载均衡。2、读写分离的思路与问题思路：主库负责新增、修改、删除写操作，从库负责查询读操作，分担主库压力，提升并发能力。核心问题：主从延迟，写入主库后，从库未及时同步，立即查询会读取旧数据。解决方案：核心业务读主库、延迟重试、强制主库查询、优化同步机制。3、分库分表的场景与拆分方式适用场景：单表数据量超千万、单库QPS过高、磁盘容量不足，常规索引优化无法解决性能问题。拆分方式：水平分表：按规则拆分数据，比如按用户ID、时间拆分，表结构一致，数据拆分。垂直分表：按字段拆分，冷热数据分离，大字段单独分表。垂直分库：按业务模块拆分，不同业务独立数据库，解耦并分担压力。常见问题：跨库join、分页排序、分布式事务、ID唯一性问题。八、高频易错面试题1、主键为什么建议用自增ID，不用UUID？自增ID有序递增，B+树索引插入不会页分裂、页合并，写入性能高。UUID无序、字符串长度长，索引占用空间大，插入会导致索引碎片，数据库性能下降。自增ID占用空间小、查询效率高，唯一缺点是容易被爬虫遍历数据，可根据业务场景选择。2、InnoDB为什么必须有主键？InnoDB数据存储依赖聚簇索引，整张表的数据都基于主键索引组织。如果没有手动设置主键，数据库会自动寻找非空唯一索引作为主键，无符合条件索引则自动生成隐藏主键。隐藏主键无法被使用，且会导致索引结构冗余，因此开发中必须手动设