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

MySQL高频面试题及详细答案（实战完整版）一、MySQL基础核心面试题1、MySQL中InnoDB和MyISAM的核心区别？这是面试必考题，核心差异集中在事务、锁、崩溃恢复、索引结构几个方面：1.事务支持：InnoDB支持事务、事务ACID特性，MyISAM不支持事务。2.锁机制：InnoDB是行级锁+表级锁，默认行锁，并发性能高；MyISAM只有表级锁，增删改会锁整张表，并发差。3.崩溃恢复：InnoDB有redolog、undolog，宕机后可以事务回滚、数据恢复，数据安全性高；MyISAM无日志机制，宕机容易造成数据损坏、表丢失。4.外键：InnoDB支持外键约束，MyISAM不支持。5.存储结构：InnoDB数据和索引存在同一个文件；MyISAM分为索引文件、数据文件、日志文件三个独立文件。6.场景选择：日常业务、高并发读写、需要事务的场景用InnoDB；静态数据、只读多、极少修改的场景可以用MyISAM。2、MySQL事务的ACID四大特性分别是什么？1.原子性（Atomicity）：一个事务内的所有SQL操作，要么全部执行成功，要么全部失败回滚，不会出现部分执行的情况。2.一致性（Consistency）：事务执行前后，数据库的数据完整性、约束规则保持一致，不会产生非法数据。比如转账场景，总金额不会凭空增加或减少。3.隔离性（Isolation）：多个事务并发执行时，事务之间相互隔离，互不干扰，不同隔离级别对应不同的并发控制规则。4.持久性（Durability）：事务提交成功后，数据会永久写入数据库，即使服务器宕机、重启，数据也不会丢失。3、MySQL四大事务隔离级别及各自问题、适用场景MySQL默认隔离级别是可重复读（RR），四个级别从低到高依次为：1.读未提交（ReadUncommitted）：可以读取到其他事务未提交的数据。存在脏读、不可重复读、幻读问题，基本不用于生产。2.读已提交（ReadCommitted）：只能读取其他事务已提交的数据。解决脏读，存在不可重复读、幻读问题，Oracle默认级别。3.可重复读（RepeatableRead）：同一个事务内，多次读取同一数据，结果始终一致。解决脏读、不可重复读，存在幻读问题，MySQLInnoDB默认级别，通过间隙锁最大程度规避幻读。4.串行化（Serializable）：最高隔离级别，事务串行执行，完全规避所有并发问题。但并发性能极差，仅用于金融、支付等对数据一致性要求极高的场景。4、脏读、不可重复读、幻读的区别？1.脏读：一个事务读取到了另一个事务未提交的修改数据，后续对方回滚，读取的数据就是无效脏数据。2.不可重复读：同一个事务内，两次读取同一条数据，期间被其他事务修改并提交，导致两次查询结果不一致。核心是数据修改。3.幻读：同一个事务内，两次范围查询，期间被其他事务新增或删除数据并提交，导致两次查询的结果集数量不一致。核心是数据新增/删除。二、MySQL索引核心面试题1、InnoDB索引结构是什么？为什么不用哈希表、二叉树？InnoDB索引采用B+树结构。1.不用哈希表：哈希索引不支持范围查询、排序、模糊查询，仅支持等值查询，且存在哈希冲突，无法满足日常复杂业务查询需求。2.不用普通二叉树：数据量大时树的高度会极高，查询磁盘IO次数多，效率极低，且容易出现斜树问题。3.不用B树：B树每个节点都存储数据和索引，页存储的索引数量少，树高度更高；B+树只有叶子节点存储数据，非叶子节点只存索引，索引密度大、树高度低，IO次数更少，且所有数据都在叶子节点，天然支持范围查询和排序。2、聚簇索引和非聚簇索引的区别？1.聚簇索引：主键索引就是聚簇索引，叶子节点直接存储整行数据。一张表只能有一个聚簇索引，InnoDB表必须有主键，无主键则自动生成隐藏主键。2.非聚簇索引（二级索引）：普通索引、唯一索引都属于此类，叶子节点只存储索引列值+主键ID，不存储整行数据。3.查询差异：通过主键索引查询，直接获取数据，速度最快；通过二级索引查询，先查到主键，再通过主键索引查询数据，这个过程叫做回表。3、什么是覆盖索引？优势是什么？覆盖索引指查询的所有字段都包含在索引中，无需回表查询整行数据。例如：建立索引index(a,b,c)，执行selecta,bfromtablewherea=1andb=2，就是覆盖索引。核心优势：避免回表操作，减少磁盘IO，大幅提升查询效率，是日常优化中最常用的手段。执行计划中Extra字段会显示Usingindex。4、索引失效的常见场景？1.索引列使用函数、运算：如wheredate(create_time)=now()、whereid+1=100。2.隐式类型转换：字符串字段不加引号、数字字段加引号，导致索引失效。3.模糊查询左匹配：like'%xxx'、like'%xxx%'，仅like'xxx%'可以走索引。4.or前后字段未全部建索引：or连接的字段，只要有一个无索引，整体不走索引。5.违背最左匹配原则：联合索引未使用最左侧字段作为查询条件。6.数据量过少、数据倾斜严重：比如查询条件匹配90%以上数据，MySQL会认为全表扫描更快，主动放弃索引。5、联合索引最左匹配原则详解联合索引遵循最左前缀匹配，例如联合索引(a,b,c)。可以走索引的场景：a、a+b、a+b+c、a+c。无法完整走索引的场景：b、c、b+c，没有使用最左侧a字段，索引直接失效。核心原理：联合索引的排序规则是先按a排序，a相同再按b排序，b相同再按c排序，跳过前置字段，后续字段无法利用索引有序性。三、MySQL锁机制面试题1、行锁和表锁的区别及使用场景1.表级锁：锁住整张表，开销小、加锁快，无死锁，但是并发差，全表阻塞。MyISAM默认表锁。适用于静态数据、低并发、批量更新场景。2.行级锁：锁住单行数据，开销大、加锁慢，可能出现死锁，并发性能极高。InnoDB默认行锁。适用于高并发读写、精准更新单条/少量数据的业务场景。补充：InnoDB的行锁是基于索引生效，如果查询条件无索引，行锁会退化为表锁。2、什么是间隙锁、临键锁？作用是什么？二者都是InnoDB可重复读隔离级别下，用来解决幻读的锁机制。1.间隙锁：锁定索引数据之间的空白区间，不锁定已有数据，防止其他事务在间隙内插入新数据。2.临键锁（Next-KeyLock）：索引记录锁+间隙锁，左开右闭区间，是InnoDB默认的行锁算法，完全杜绝幻读问题。注意：等值查询命中唯一索引时，临键锁会退化为记录锁；范围查询会触发临键锁和间隙锁。3、MySQL死锁产生的条件及解决办法死锁四大必要条件：互斥条件、请求保持条件、不可剥夺条件、环路等待条件。生产解决方案：1.统一SQL执行顺序：所有事务更新多张表时，保持固定顺序，避免环路等待。2.缩小事务粒度：事务尽量短小，减少锁持有时间。3.设置超时时间：通过innodb_lock_wait_timeout设置锁等待超时，超时自动释放。4.开启死锁检测：InnoDB自带死锁检测机制，会自动回滚代价最小的事务。四、MySQL日志与底层原理1、redolog和undolog的作用及区别1.redolog（重做日志）：保证事务持久性。事务执行时，先写redolog，再刷盘数据。服务器宕机后，重启通过redolog重写未持久化的数据，恢复数据。属于物理日志，记录数据页的修改。2.undolog（回滚日志）：保证事务原子性和隔离性。记录数据修改前的快照，事务回滚时通过undolog恢复原始数据；同时为MVCC提供数据快照。属于逻辑日志，记录SQL反向操作。2、MVCC多版本并发控制原理MVCC是InnoDB实现无锁读、提升并发性能的核心机制，主要用于可重复读、读已提交隔离级别。核心原理：每行数据都隐藏DB_TRX_ID（事务ID）、DB_ROLL_PTR（回滚指针），通过回滚指针串联历史数据版本，形成版本链。事务查询时，会根据自身事务ID，从版本链中筛选出符合隔离级别的数据快照，实现读取历史版本数据，无需加锁，读写不冲突。读已提交：每次查询都会重新生成快照；可重复读：事务首次查询生成快照，后续全程复用，保证多次读取结果一致。3、binlog日志作用及和redolog的区别binlog是MySQL服务层的二进制日志，所有存储引擎都支持；redolog是InnoDB引擎层日志，仅InnoDB支持。binlog核心作用：主从复制、数据恢复、记录所有增删改操作日志。核心区别：1.层级：binlog服务层，redolog引擎层。2.内容：binlog记录SQL逻辑日志，redolog记录物理数据页修改。3.用途：binlog用于复制、恢复；redolog用于宕机数据持久化恢复。4.写入时机：binlog事务提交后写入，redolog事务执行中实时写入。五、MySQL性能优化面试题1、SQL慢查询优化的通用步骤1.开启慢查询日志，定位慢SQL，记录执行耗时、扫描行数。2.执行explain分析执行计划，查看是否走索引、索引类型、扫描行数、是否回表、是否临时表/文件排序。3.优化索引：新增合适索引、优化联合索引顺序、利用覆盖索引避免回表。4.优化SQL语句：避免select*、避免索引失效场景、拆分大SQL、减少子查询嵌套。5.优化业务逻辑：分页深分页优化、避免批量全表更新、拆分大事务。6.数据库配置优化：调整缓冲池大小、调整连接数、优化刷盘参数。2、explain执行计划核心字段解读1.type：查询类型，优先级从优到劣：system>const>eq_ref>ref>range>index>all。生产必须保证至少range及以上，all代表全表扫描，需要优化。2.key：实际使用的索引，为null表示未走索引。3.rows：预估扫描行数，数值越小性能越好。4.Extra：Usingindex（覆盖索引，最优）、Usingfilesort（文件排序，需优化）、Usingtemporary（临时表，需优化）、Usingwhere（条件过滤）。3、深分页（limit10000,10）优化方案问题：limitoffset过大时，MySQL会先扫描前offset行数据，再丢弃，返回后10条，耗时极高。常用优化方案：1.主键分页优化：select*fromtablewhereid>10000limit10，利用主键索引有序性，避免扫描冗余数据，效率极高，适合主键连续场景。2.子查询优化：先通过子查询查出目标主键，再关联查询数据，减少回表扫描量。3.业务层限制：前端禁止超大页码跳转，只允许上一页、下一页、指定少量页码。4、如何避免SQL出现临时表和文件排序？1.文件排序（Usingfilesort）：SQL中存在orderby、groupby，且无对应索引时触发。优化方式：为排序、分组字段建立联合索引，利用索引有序性替代文件排序。2.临时表（Usingtemporary）：多表关联、distinct、groupby无索引时触发。优化方式：优化索引、简化关联查询、避免不必要的去重和分组。六、高频实战场景面试题1、千万级大表优化方案1.表结构优化：字段类型合理化，用tinyint、varchar替代bigint、char，避免字段冗余；禁用null值，默认给0或空字符串。2.索引优化：精简索引，删除无用、重复索引；核心查询建立覆盖索引，避免回表。3.分库分表：单表数据超千万、亿级数据，采用水平分表，按用户ID、时间分片。4.冷热数据分离：历史冷数据迁移至归档表，业务表只保留热数据，减少表数据量。5.读写分离：主库写、从库读，分担数据库压力。2、MySQL主从复制原理及延迟原因主从复制原理：1.主库（master）：执行增删改SQL，记录binlog日志。2.从库（slave）：IO线程连接主库，拉取主库binlog，写入本地relaylog（中继日志）。3.从库SQL线程：读取中继日志，重放SQL，同步主库数据。主从延迟常见原因：1.主库写入压力大，binlo