MySQL经典面试题及详细答案（高频完整版）

MySQL经典面试题及详细答案（高频完整版）一、MySQL基础核心面试题1、MySQL中InnoDB和MyISAM的核心区别？这是面试必考题，核心区别集中在事务、锁、索引、崩溃恢复等维度，具体如下：1）事务支持：InnoDB支持事务、ACID特性；MyISAM不支持事务。2）锁机制：InnoDB支持行级锁、表级锁、意向锁；MyISAM只有表级锁，并发读写能力极差。3）外键：InnoDB支持外键约束；MyISAM不支持。4）崩溃恢复：InnoDB依靠redo/undo日志，宕机后可恢复数据，保证数据安全；MyISAM宕机容易导致数据损坏、表出错。5）索引存储：InnoDB主键索引叶子节点存整行数据，二级索引存主键值；MyISAM索引和数据分离，索引叶子节点存数据地址。6）并发性能：InnoDB适合高并发读写场景；MyISAM适合静态、少量读写、纯查询场景。补充：MySQL5.5之后默认存储引擎为InnoDB，生产环境基本全部使用InnoDB。2、MySQL事务的四大特性（ACID）是什么？原子性（Atomic）：一个事务内的所有SQL操作，要么全部执行成功，要么全部回滚，不会出现部分执行的情况。一致性（Consistent）：事务执行前后，数据库的数据完整性、约束规则不变，数据始终处于合法状态。例如转账场景，双方总金额保持不变。隔离性（Isolated）：多个事务并发执行时，事务之间相互隔离，互不干扰，由事务隔离级别控制隔离程度。持久性（Durable）：事务提交成功后，数据会永久写入磁盘，即使数据库宕机、重启，数据也不会丢失。3、MySQL四大事务隔离级别及各自问题？MySQLInnoDB支持四种隔离级别，从上到下隔离性依次增强、性能依次降低，默认级别是可重复读。1）读未提交（ReadUncommitted）：能读取到其他事务未提交的数据。存在问题：脏读、不可重复读、幻读。几乎不使用。2）读已提交（ReadCommitted）：只能读取其他事务已提交的数据。解决脏读，存在问题：不可重复读、幻读。Oracle默认隔离级别。3）可重复读（RepeatableRead）：同一个事务内，多次读取同一数据，结果始终一致。解决脏读、不可重复读，存在问题：幻读。MySQLInnoDB默认级别，通过MVCC极大减少幻读问题。4）串行化（Serializable）：最高隔离级别，事务串行执行，完全隔离。解决所有并发问题，但是并发性能极低，基本不用于生产。4、什么是脏读、不可重复读、幻读？脏读：一个事务读取到了另一个事务未提交的修改数据，后续对方回滚，读取的数据就是无效脏数据。不可重复读：同一个事务内，两次查询同一行数据，结果不一致。原因是中间被其他事务修改并提交。核心是数据修改。幻读：同一个事务内，两次范围查询，返回的行数不一致，出现新增或消失的数据。核心是数据新增/删除。二、索引核心面试题（高频重点）1、InnoDB索引结构是什么？为什么用B+树不用B树、哈希？InnoDB所有索引底层都是B+树结构。1）为什么不用B树：B树每个节点都存数据，页存储的索引字段少，树高度更高，磁盘IO次数更多；B+树只有叶子节点存数据，非叶子节点只存索引键，节点存储索引更多，树更矮，查询效率更高。同时B+树叶子节点有序链表，适合范围查询、排序、分页。2）为什么不用哈希索引：哈希索引不支持范围查询、排序、模糊查询；哈希存在碰撞问题；数据有序性差，而业务中绝大多数查询包含范围、排序条件，B+树适配性更强。2、主键索引、普通索引、联合索引的区别？主键索引（聚簇索引）：每张表只有一个，叶子节点存储整行数据，索引和数据绑定，查询效率最高，不允许为空、不允许重复。普通索引（二级索引）：叶子节点只存储索引字段+主键值，查询时需要先查二级索引拿到主键，再回表查整行数据，也就是回表查询。联合索引：多个字段组合成的索引，遵循最左前缀原则，查询条件需要匹配最左侧字段才能命中索引。3、什么是最左前缀原则？联合索引失效场景？最左前缀原则：联合索引(a,b,c)，查询条件必须包含最左侧的a字段，才能触发索引，依次匹配前缀字段。常见索引失效场景：1）不满足最左前缀，例如联合索引(a,b)，查询条件只写b=xxx；2）索引字段使用函数、运算、类型转换；3）like查询以%开头，如%张三、%三；4）or连接的字段有未建立索引的字段；5）MySQL判断全表扫描比索引查询更快时，会自动放弃索引。4、覆盖索引是什么？有什么作用？覆盖索引指查询的所有字段，全部包含在索引中，不需要回表查询整行数据。例如：建立联合索引(name,age)，执行selectname,agefromuserwherename='xxx'，就是覆盖索引。核心作用：避免回表操作，减少磁盘IO，大幅提升查询性能，是日常优化中最常用的手段。5、回表查询是什么？通过二级索引查询时，只能拿到索引字段和主键值，如果查询语句需要获取索引以外的字段，就需要拿着主键值去聚簇索引中再次查询整行数据，这个过程就是回表。回表会增加一次索引查询，降低效率，优先使用覆盖索引可以规避。三、MVCC多版本并发控制1、什么是MVCC？作用是什么？MVCC即多版本并发控制，是InnoDB实现无锁读、提升并发性能的核心机制，主要用于读已提交、可重复读两个隔离级别。核心原理：数据修改时不直接覆盖原数据，而是生成数据新版本，保存undo日志，不同事务读取不同版本的数据，实现读写不冲突。作用：读操作不加锁，读写并发执行，极大提升数据库并发能力；解决大部分幻读、不可重复读问题。2、MVCC核心组成？1）隐藏字段：每行数据包含DB_TRX_ID（最后修改事务ID）、DB_ROLL_PTR（回滚指针）、DB_ROW_ID（隐藏主键）；2）undo日志：保存数据修改前的旧版本数据，用于数据回滚和版本读取；3）readview：事务开启时生成的读视图，用来判断当前数据版本是否可见。四、锁机制面试题1、InnoDB行锁和表锁的区别？什么时候触发？表级锁：锁住整张表，开销小、加锁快，并发差，所有读写阻塞。行级锁：锁住指定数据行，开销大、加锁慢，并发能力强，只阻塞当前行的操作。触发规则：1）查询条件命中索引，InnoDB使用行锁；2）查询条件未命中索引/索引失效，InnoDB会升级为表锁；3）DDL语句、无where条件的update/delete，默认表锁。2、什么是死锁？如何避免和解决？死锁原理：两个或多个事务互相持有对方需要的锁，同时等待对方释放锁，循环等待，事务永久阻塞。常见场景：事务1锁A等B，事务2锁B等A。解决方式：1）MySQL自动检测死锁，超时后自动回滚其中一个事务；2）统一SQL执行顺序，所有事务操作表/数据的顺序一致；3）减少事务粒度，事务内尽量少操作数据，缩短锁持有时间；4）避免事务中嵌套复杂查询、休眠操作。五、SQL优化高频面试题1、慢查询优化的通用步骤？1）开启慢查询日志，定位执行慢的SQL；2）使用explain分析SQL执行计划，查看索引、扫描行数、锁情况；3）判断是否索引失效、无索引、回表过多；4）优化索引：建立合适联合索引、使用覆盖索引、避免索引失效场景；5）优化SQL语句：避免select*、减少子查询、优化join、避免大事务；6）拆分大SQL、分页优化、分表分库处理超大表。2、explain关键字重点字段解读？type：查询类型，性能从优到劣：system>const>eq_ref>ref>range>index>all，日常目标保证ref/range，杜绝all全表扫描。key：实际命中的索引，为null表示未命中索引。rows：预估扫描行数，数值越小性能越好。Extra：关键提示，Usingindex表示覆盖索引，Usingfilesort表示文件排序（需要优化），Usingtemporary表示临时表（需要优化）。3、limit分页深偏移量优化？问题：limit100000,10需要先扫描10万行再丢弃，效率极低。优化方案：1）主键索引分页：select*fromuserwhereid>100000limit10；利用主键有序特性，避免全量扫描；2）子查询覆盖索引分页：先查主键，再关联查询数据；3）业务层面禁止深分页，限制最大分页页数。六、高阶面试题（分库分表、缓存、日志）1、redo日志和undo日志的作用？区别？redolog（重做日志）：保证事务持久性。事务执行时先写redolog，再刷盘，数据库宕机重启后，通过redolog重做未持久化的事务数据，恢复数据。undolog（回滚日志）：保证事务原子性、实现MVCC。保存数据修改前的版本，事务回滚时用于恢复原数据，同时为读写隔离提供历史数据版本。2、MySQL主从复制原理？1）主库（master）执行SQL语句，记录操作到binlog二进制日志；2）从库（slave）IO线程连接主库，拉取binlog日志，写入本地relay日志；3）从库SQL线程读取relay日志，重放SQL语句，实现数据同步。核心作用：读写分离、数据备份、故障切换。3、主从延迟的原因和解决方案？常见原因：主库写入并发量大，从库单线程回放日志速度跟不上；大事务、大批量数据写入；从库硬件性能弱于主库。解决方案：拆分大事务、避免批量更新；开启从库并行复制；升级从库硬件；读写分离时核心读业务走主库。4、为什么要分库分表？垂直分表和水平分表区别？单表数据量超过千万、亿级后，索引效率下降、查询变慢、锁竞争严重，需要分库分表提升性能。垂直分表：按字段拆分，将大字段、不常用字段拆分到子表，减少单表数据量，提升查询速度。水平分表：按数据规则拆分（id取模、时间、范围），将一张大表拆分为多张结构一致的子表，分散数据压力。七、常见SQL手写面试题1、去重查询（distinct、groupby）--查询不重复的用户名selectdistinctusernamefromuser;--分组去重selectusernamefromusergroupbyusername;2、查询分组最大值、最新数据--查询每个部门最新入职的员工select*fromemployeee1wherecrea