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

MySQL高频面试题及详细答案（实战接地气版）一、MySQL基础核心面试题1、MySQL常用存储引擎有哪些？区别是什么？日常开发用哪个？常用存储引擎主要是InnoDB和MyISAM，还有Memory、Archive等小众引擎，生产环境99%场景用InnoDB。核心区别：InnoDB：支持事务、支持行级锁、支持外键、崩溃可恢复，读写性能均衡，有MVCC多版本并发控制，适合业务数据、订单、用户、交易等核心表。缺点是相比MyISAM，纯查询速度略低。MyISAM：不支持事务、不支持行锁、只有表锁、无崩溃恢复机制。优势是结构简单、纯查询速度快、占用磁盘空间小。缺点是更新、删除操作会锁全表，并发能力极差，现在基本被淘汰。Memory：数据存在内存中，重启数据清空，表锁机制，适合临时缓存、临时统计数据，不存持久化数据。生产建议：所有业务表统一用InnoDB，杜绝MyISAM。2、InnoDB和MyISAM的锁机制区别？InnoDB默认是行级锁，只有在无索引、索引失效、表结构变更时才会退化成表锁。行锁粒度小，并发读写互不冲突，适合高并发业务。MyISAM全程是表级锁，只要有一条数据修改、删除、新增，整张表被锁定，其他所有读写请求都会阻塞，高并发场景下极易出现接口超时、队列堆积。3、MySQL事务四大特性ACID分别是什么？简单解释原子性（Atomic）：一个事务内的所有SQL，要么全部执行成功，要么全部回滚，不会出现部分执行的情况。比如转账，扣钱和加钱必须同时成功或同时失败。一致性（Consistent）：事务执行前后，数据库的数据完整性、约束规则不变。比如唯一索引、外键、字段约束不会被破坏，数据整体合法。隔离性（Isolated）：多个事务并发执行时，互相之间互不干扰，每个事务都有独立的执行空间。隔离级别不同，干扰程度不同。持久性（Durable）：事务提交成功后，数据会持久化到磁盘，就算服务器断电、崩溃，数据也不会丢失。4、MySQL四种事务隔离级别？各自问题和使用场景隔离级别从低到高：读未提交>读已提交>可重复读>串行化读未提交：能读到其他事务未提交的数据，会出现脏读，基本不用。读已提交（RC）：只能读到其他事务提交后的数据，解决脏读，存在不可重复读。Oracle默认隔离级别，适合大部分查询为主的业务。可重复读（RR）：MySQLInnoDB默认级别，解决脏读、不可重复读，存在幻读。通过MVCC机制实现，适合订单、交易、金融核心业务。串行化：最高级别，所有事务串行执行，无并发问题，无脏读、不可重复读、幻读，但性能极差，几乎不用。三个并发问题简单区分：脏读：读到别人未提交的脏数据不可重复读：同一事务内，两次查询同一数据，结果被别人修改提交，数据不一致幻读：同一事务内，范围查询的数据条数，被别人新增/删除提交，条数不一致5、InnoDB如何解决幻读问题？MySQLInnoDB默认RR隔离级别，不能完全杜绝幻读，只能大概率解决。解决手段：MVCC多版本并发控制：普通快照读场景，通过读取历史版本数据，避免幻读现象；Next-KeyLock临键锁：当前读（update、delete、select...forupdate）场景，通过区间锁锁住查询范围，防止其他事务新增数据，解决大部分幻读场景。彻底解决幻读只能用串行化隔离级别，但性能太低，生产不推荐。二、MySQL索引核心面试题（高频重点）1、MySQL索引结构是什么？为什么用B+树不用B树、哈希？InnoDB索引底层是B+树结构。对比原因：为什么不用B树：B树每个节点都存数据，页存储的索引数量少，磁盘IO次数多；B+树只有叶子节点存数据，非叶子节点只存索引键，一页能存更多索引，树的高度更低，查询更快。为什么不用哈希索引：哈希是等值查询快，但不支持范围查询、排序、模糊查询，且哈希冲突会导致性能下降，不适合常规业务查询场景。B+树优势：层级低、IO少、支持范围查询、排序、分页，叶子节点有序串联，遍历数据效率极高。2、聚簇索引和非聚簇索引的区别？InnoDB主键索引就是聚簇索引，唯一索引、普通索引都是非聚簇索引。聚簇索引：叶子节点直接存储整行数据，一张表只有一个聚簇索引（默认主键）。如果没有主键，会自动选唯一索引；没有唯一索引，自动生成隐藏主键。非聚簇索引（二级索引）：叶子节点只存储主键值，查询时需要先通过二级索引找到主键，再通过主键索引查整行数据，这个过程叫回表。3、什么是覆盖索引？为什么要使用覆盖索引？覆盖索引指查询的所有字段都在索引中，不需要回表查询整行数据。举例：建立索引(name,age)，执行selectname,agefromuserwherename='张三'，直接从索引中拿到全部数据，无需回表。优势：彻底避免回表操作，大幅提升查询性能，是日常优化中最常用的手段。4、最左前缀原则是什么？哪些情况会导致索引失效？最左前缀原则：联合索引遵循从左到右匹配的规则，比如索引(a,b,c)，查询条件包含a、a+b、a+b+c可以走索引；没有a，直接查b、c会直接失效。常见索引失效场景（面试必考）：联合索引不满足最左前缀；索引字段使用函数、运算、类型转换，比如whereleft(name,2)='xx'、whereage+1=18；like%xx左模糊、%xx%全模糊（xx%右模糊可以走索引）；or连接的字段有非索引字段，会导致整段索引失效；字段类型隐式转换，比如字符串字段传数字；MySQL优化器判断全表扫描比索引更快，主动放弃索引（数据量少、索引区分度低时常见）。5、索引是不是越多越好？为什么？不是，索引是查询快、增删改慢。原因：索引需要占用磁盘空间，索引越多，占用空间越大；新增、修改、删除数据时，不仅要更新数据，还要同步更新所有关联索引，索引越多，写入性能越差；过多索引会导致MySQL优化器选择索引耗时增加，反而可能选错索引。原则：按需建索引，高频查询字段建索引，低频查询、高频更新字段少建索引。三、MySQL锁与并发面试题1、行锁、表锁、意向锁区别表锁：锁定整张表，粒度最大，并发最差，MyISAM默认锁，InnoDB索引失效时触发。行锁：锁定单条数据，粒度最小，并发最高，InnoDB有效索引下的默认锁。意向锁：意向共享锁、意向排他锁，是表级辅助锁。作用是标记表内是否有行锁，避免整张表被表锁阻塞，提升锁判断效率，不需要手动操作，数据库自动维护。2、什么是死锁？怎么产生？如何避免和解决？死锁定义：两个或多个事务互相持有对方需要的锁，同时互相等待，无限阻塞，无法释放资源。产生条件：互斥条件、请求保持条件、不可剥夺条件、循环等待条件，四个条件同时满足就会触发死锁。常见场景：两个事务更新数据顺序不一致，事务A改1号等2号锁，事务B改2号等1号锁。解决与避免方案：统一业务更新顺序，所有事务更新数据按主键从小到大顺序执行；缩短事务生命周期，不要在事务内做耗时操作、接口调用、循环查询；设置超时时间，超时自动释放锁；MySQL自带死锁检测机制，发现死锁会自动回滚其中一个事务。四、MySQL优化实战面试题1、慢查询如何排查？完整排查流程1、开启慢查询日志，设置阈值（通常1秒），记录执行超时的SQL；2、通过慢日志、showprocesslist定位耗时SQL；3、用explain分析SQL执行计划，查看是否走索引、是否全表扫描、是否回表、扫描行数；4、根据执行计划优化索引、优化SQL写法；5、优化后压测验证，监控执行耗时变化。2、explain执行计划重点字段解释type：核心字段，优先级system>const>eq_ref>ref>range>index>all，日常至少保证range、ref级别，all代表全表扫描，必须优化；key：实际最终使用的索引，为null表示未走索引；rows：预估扫描数据行数，数值越小性能越好；Extra：Usingfilesort（文件排序，需要优化）、Usingtemporary（临时表，需要优化）、Usingindex（覆盖索引，性能优秀）。3、limit分页深度优化方案？比如limit100000,10问题：深度分页会扫描前面十万条数据，再丢弃，耗时极高。优化方案：主键分页优化：先通过主键索引查询起始id，再关联查询数据，避免全量扫描。示例：select*fromtablewhereid>100000limit10；延迟关联：先查主键，再关联查整表数据，减少回表开销；业务层限制：禁止前端深度分页，只提供前几页数据，后端不支持超大页码查询；归档分页：历史冷数据归档到子表，减少主表数据量。4、大表优化方案（千万级数据）合理建索引，避免无效索引、重复索引；字段设计优化，用tinyint、int代替varchar，时间用timestamp，减少字段长度；冷热数据分离，归档历史数据，减少主表数据量；分库分表：水平分表（按时间、用户ID分片）、垂直分表（大字段拆分独立表）；开启分区表，按时间分区管理数据；优化SQL写法，杜绝select*、杜绝深度分页、杜绝索引失效SQL。五、MySQL高频实战问答题1、varchar和char的区别？使用场景char：固定长度，存储速度快，占用空间固定，不足长度自动补空格。适合固定长度数据，比如手机号、身份证号、状态码。varchar：可变长度，节省空间，会额外占用1-2字节记录长度。适合用户名、地址、备注等长短不一的字符串。注意：varchar(50)代表最多存50个字符，不是字节，日常字符串优先用varchar。2、MySQL如何保证数据不丢失？依靠两大日志机制保证数据安全：redolog（重做日志）：事务提交前先写redolog，再刷盘。数据库崩溃重启后，通过redolog重做已提交但未落地的数据，保证数据持久性。undolog（回滚日志）：记录数据修改前的版本，事务失败时通过undolog回滚数据，保证事务原子性，同时支撑MVCC实现。3、MVCC原理是什么？适用场景？MVCC即多版本并发控制，核心是读写分离、读不加锁、写加锁，大幅提升并发性能。原理：通过undolog保存数据历史版本，通过readview判断当前事务能读取的版本，普通查询读取快照版本，不加锁，