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

MySQL高频面试题及详细答案（实战版）一、MySQL基础相关1、MySQL常用存储引擎区别？InnoDB和MyISAM核心差异日常开发中最常用的就是InnoDB和MyISAM，MySQL5.5之后默认引擎为InnoDB，两者核心区别如下：1）事务支持：InnoDB支持事务、事务四大特性、事务隔离级别；MyISAM不支持事务，所有操作都是一次性执行，无法回滚。2）锁机制：InnoDB支持行级锁和表级锁，默认行锁，并发性能高；MyISAM只有表级锁，读写、写操作都会锁住整张表，并发差。3）外键支持：InnoDB支持外键约束，保证数据关联完整性；MyISAM不支持外键。4）崩溃恢复：InnoDB有redo/undo日志，宕机后可以通过日志恢复数据，数据安全性高；MyISAM没有日志机制，宕机容易导致数据损坏、丢失。5）索引结构：InnoDB是聚簇索引，数据和索引绑定存储；MyISAM是非聚簇索引，索引和数据分开存储。6）适用场景：InnoDB适合增删改频繁、并发高、需要事务的业务（订单、用户、交易数据）；MyISAM适合查询多、读写少、无需事务的静态数据（日志、配置台账）。2、MySQL事务四大特性（ACID）原子性（Atomic）：一个事务内的所有SQL要么全部执行成功，要么全部失败回滚，不会出现部分执行的情况。比如转账业务，扣款和加款必须同时成功或同时失败。一致性（Consistent）：事务执行前后，数据库的数据完整性、约束规则不会被破坏。比如转账前后，两人账户总金额保持不变。隔离性（Isolated）：多个事务并发执行时，事务之间相互隔离，互不干扰，不同隔离级别对应不同的隔离效果。持久性（Durable）：事务提交成功后，数据会永久写入数据库，即使服务器宕机、重启，数据也不会丢失。3、MySQL四大事务隔离级别及问题MySQLInnoDB支持四种隔离级别，默认是可重复读（RR），级别从低到高依次为：1）读未提交（ReadUncommitted）：可以读取到其他事务未提交的数据。会产生脏读、不可重复读、幻读，基本不会在业务中使用。2）读已提交（ReadCommitted）：只能读取其他事务已经提交的数据。解决了脏读，但存在不可重复读、幻读问题，是Oracle默认隔离级别。3）可重复读（RepeatableRead）：同一个事务内，多次读取同一数据，结果始终一致。解决了脏读、不可重复读，存在幻读，MySQL默认级别。4）串行化（Serializable）：最高隔离级别，事务串行执行，完全杜绝脏读、不可重复读、幻读，但并发性能极差，几乎不用。4、脏读、不可重复读、幻读的区别脏读：一个事务读取到了另一个事务未提交的修改数据，后续对方回滚，读取到的数据就是无效脏数据。不可重复读：同一个事务内，两次查询同一数据，结果不一致。原因是中间被其他事务修改并提交，侧重「数据修改更新」。幻读：同一个事务内，两次范围查询，结果行数不一致。原因是中间被其他事务新增/删除数据并提交，侧重「数据新增删除」。5、InnoDB如何解决幻读问题？MySQLInnoDB默认RR隔离级别，通过两种机制极大规避幻读：1）MVCC多版本并发控制：普通快照读通过读取数据历史版本，避免读取到其他事务新增的数据；2）临键锁（Next-KeyLock）：当前读（update、delete、select...forupdate）会触发临键锁，锁定查询范围的左右区间，禁止其他事务新增数据。注意：RR级别下无法完全杜绝幻读，极端场景仍会出现，只有串行化级别能彻底解决。二、索引核心面试题1、MySQL索引底层数据结构为什么是B+树，不是B树、二叉树、哈希表？1）排除二叉树：二叉树高度太高，数据量大时树的层级极深，磁盘IO次数多，查询效率低；且平衡二叉树插入删除需要频繁旋转，开销大。2）排除B树：B树每个节点都存储数据和索引，每页存储的索引数量少，树高度更高；B+树非叶子节点只存索引、不存数据，一页可以存储更多索引，树高度更低，IO更少。3）排除哈希表：哈希表只能精准匹配等值查询，不支持范围查询、排序、模糊查询；且存在哈希冲突，数据无序，不适合数据库索引场景。B+树优势：层级低、IO次数少、所有数据都在叶子节点、有序排列，支持范围查询、排序、分页，适配MySQL绝大多数查询场景。2、聚簇索引和非聚簇索引的区别聚簇索引（主键索引）1）叶子节点直接存储整行数据，索引和数据绑定在一起；2）一张表只有一个聚簇索引，默认基于主键创建，没有主键则自动选取唯一索引，无唯一索引则自动生成隐藏主键；3）主键查询效率最高，直接命中整行数据。非聚簇索引（二级索引、普通索引）1）叶子节点只存储索引列值+主键值，不存储整行数据；2）一张表可以有多个非聚簇索引；3）通过普通索引查询时，先查到主键，再通过主键索引查询整行数据，这个过程就是回表。3、什么是覆盖索引？优势是什么？如果一个索引包含了查询需要的所有字段，查询时不需要回表查询主键索引，直接通过二级索引就能拿到全部数据，这种索引就是覆盖索引。优势：避免回表操作，减少磁盘IO，大幅提升查询效率；是日常SQL优化中最常用的手段。示例：建立索引index_name_age(name,age)，执行selectname,agefromuserwherename='test'，触发覆盖索引。4、什么是最左前缀原则？失效场景有哪些？最左前缀原则：联合索引遵循从左到右匹配的规则，查询条件需要匹配索引的最左前列，索引才会生效。例如联合索引(a,b,c)，有效匹配：a、a+b、a+b+c；无法单独使用b、c、b+c触发索引。索引常见失效场景：1）不满足最左前缀原则；2）索引列使用函数、运算、类型转换，如wheresubstring(name,1,2)='xx'、whereage+1=18；3）like以%开头，如like'%test'、'%test%'；4）or连接的条件中有非索引列；5）MySQL预估走索引比全表扫描更慢，自动放弃索引（数据量少、区分度低时常见）；6）字段隐式类型转换，字符串字段传入数字、数字字段传字符串。5、索引为什么不是越多越好？索引可以提升查询效率，但会降低增删改效率。每次新增、修改、删除数据时，不仅要更新数据，还要同步更新所有关联的索引结构，索引越多，维护开销越大。同时索引会占用磁盘空间，过多索引会增加数据库存储压力，降低数据库整体性能。实际开发中只针对高频查询字段建索引。三、锁机制相关面试题1、InnoDB行锁和表锁的区别、触发场景表级锁：锁住整张表，开销小、加锁快、并发差。MyISAM默认表锁，InnoDB在无索引、索引失效场景下会降级为表锁。行级锁：锁住具体数据行，开销大、加锁慢、并发高。InnoDB特有，精准命中索引时触发行锁。行锁降级表锁核心场景：SQL没有走索引、索引失效、查询条件无精准匹配，导致InnoDB无法锁定具体行，只能锁整张表。2、共享锁和排他锁区别共享锁（S锁）：读锁，多个事务可以同时加共享锁，互不冲突。加锁后，其他事务可以读、不能写。一般由普通查询触发。排他锁（X锁）：写锁，独占锁，一个事务加排他锁后，其他事务不能读、不能写。update、delete、selectforupdate会触发排他锁。3、什么是死锁？如何避免和解决死锁？死锁定义：两个或多个事务互相持有对方需要的锁，同时等待对方释放锁，循环等待，导致所有事务永久阻塞。死锁产生条件：互斥条件、请求保持条件、不可剥夺条件、环路等待条件。避免方案：1）统一SQL执行顺序，所有事务操作表、操作数据的顺序保持一致，杜绝环路等待；2）减少事务粒度，事务内不要写无关SQL，缩短锁持有时间；3）避免一次性加多个锁，尽量简化锁场景；4）设置超时时间，超时自动释放锁；5）开启MySQL死锁检测机制，自动回滚代价最小的事务。四、SQL优化与慢查询1、如何定位慢SQL？慢查询优化整体思路定位方式：开启MySQL慢查询日志，设置阈值（默认2秒），执行超时的SQL会被记录；线上也可以通过showprocesslist、performance_schema实时查看慢SQL。优化整体思路：1）先用explain分析SQL执行计划，查看是否走索引、索引类型、扫描行数；2）优化索引，根据查询条件建立合适的单列/联合索引，尽量使用覆盖索引；3）优化SQL语句，避免索引失效场景、避免select*、避免大表join、避免深度分页；4）优化业务逻辑，减少不必要的查询、减少循环查询；5）大表优化：分库分表、冷热数据分离、定期清理冗余数据。2、explain核心字段含义type：查询类型，核心优化指标，性能从优到劣：system>const>eq_ref>ref>range>index>all，日常开发至少保证达到ref/range，all代表全表扫描，必须优化。key：实际最终使用的索引，为null表示未走索引。rows：预估扫描的数据行数，数值越小性能越好。Extra：额外信息，出现Usingfilesort（文件排序）、Usingtemporary（临时表）代表SQL性能差，需要重点优化。3、大表分页查询慢的原因及优化方案慢的原因：limit偏移量越大，数据库需要扫描并跳过的冗余数据越多，比如limit100000,10需要先扫描100010条数据，再丢弃前100000条，开销极大。优化方案：1）主键分页（推荐）：通过whereid>上一页最大idlimit10，避免大偏移量；2）覆盖索引分页：先通过索引查出主键，再关联查询数据，减少回表开销；3）业务层面限制，禁止深度分页，前端做分页页码限制；4）延迟关联、子查询优化。4、join查询优化原则1）小表驱动大表：用数据量小的表作为驱动表，数据量大的表作为被驱动表，减少循环匹配次数；2）关联字段必须建立索引，避免关联时全表扫描；3）避免多表嵌套join，超过3表join尽量拆分SQL；4）join前过滤数据，先通过where缩小数据范围，再做关联。五、MVCC、日志与高可用1、什么是MVCC？实现原理和作用MVCC即多版本并发控制，是InnoDB实现事务隔离、提升并发性能的核心机制，主要用于读不加锁、读写不冲突。实现原理：依赖3个核心要素：隐藏字段、undolog、readview。1）每条数据都有隐藏字段，记录事务ID、回滚指针；2）数据修改时会保存历史版本到undolog，通过回滚指针串联所有历史版本；3）事务读取时生成readview，匹配符合隔离级别规则的历史版本数据。作用：实现无锁读、提升并发性能、支撑RR隔离级别、解决脏读和不可重复读。2、redolog和undolog区别与作用redolog（重做日志）核心作用：保证事务持久性。事务提交后数据先写入redolog，再异步刷盘。数据库宕机重启后，通过redolog重做已提交但未落地的数据，保证数据不丢失。undolog（回滚日志）核心作用：保证事务原子性+实现MVCC。数据修改前先保存原始数据到undolog，事务失败则通过undolog回滚数据；同时保存数据历史版本，供MVCC快照读使用。3、binlog作用及与redolog区别binlog（二进制日志）：属于MySQL服务层日志，所有存储引擎都有。记录数据库所有增删改的SQL逻辑日志，用于主从复制、数据恢复。核心区别：1）redolog是引擎层日志，binlog是服务层日志；2）redolog保证宕机数据安全，binlog保证数据同步和恢复；3）redolog是物理日志（记录数据页修改），binlog是逻辑日志（记录SQL语句）；4）redolog循环写入，binlog追加写入、永久保存。4、MySQL主从复制原理1）主库（master）执行增删改SQL，操作完成后写入binlog日志；2）从库（slave）开启IO线程，实时拉取主库的binlog日志，写入本地relaylog（中继日志）；3）从库开启SQL线程，读取本地中继日志，重放SQL语句，实现和主库数据同步。主从延迟核心原因：主库并发写入，从库单线程重放日志、大事务、网络延迟、从库硬件性能差。六、高频实战场景题1、为什么selectcount(*)比count(字段)快？count(1)、count(*)、count(字段)区别count(*)：MySQL专门做了优化，不会读取字段数据，直接统计行数，忽略null值，效率最高。count(1)：和count(*)效率几乎一致，底层优化后基本无差别。count(字段)：需要遍历所有数据，读取字段值，判断是否为null，不统计null数据，效率最低。总结：统计总行数优先使用count(*)，不要用count(字段)。2、varchar(10)和va