MySQL面试题及详细答案（真实职场版）

MySQL面试题及详细答案（真实职场版）一、MySQL基础面试题1、MySQL常用存储引擎有哪些？区别是什么？日常开发最常用的就两个：InnoDB、MyISAM，还有一个Memory临时用。InnoDB（默认）支持事务、行锁、外键、MVCC，数据安全性高有崩溃恢复机制，适合业务读写表索引和数据存在一起，主键索引是聚簇索引MyISAM不支持事务、不支持行锁，只有表锁读写并发差，写操作会锁全表查询速度快，占用空间小，适合静态只读数据、日志归档表Memory数据存在内存，重启全部丢失默认哈希索引，速度极快，只适合临时缓存、临时统计工作结论：线上业务表一律用InnoDB，绝不使用MyISAM。2、CHAR和VARCHAR的区别？怎么选？CHAR：固定长度。定义多长就占多少空间，尾部空格会截断。查询效率高，无碎片。VARCHAR：可变长度。存多少占多少空间，会额外占用1-2字节记录长度，支持更长文本。选型经验固定短长度用CHAR：手机号、身份证、性别、状态码长度不固定用VARCHAR：用户名、地址、备注VARCHAR尽量别超过255，长文本用TEXT3、TEXT、VARCHAR、BLOB区别VARCHAR：最大65535字节，可建索引，查询性能好TEXT：长文本，不能建普通索引（可建前缀索引），查询较慢，适合文章内容、大段备注BLOB：二进制文件，存图片、文件二进制，业务中基本不用，文件统一存OSS，数据库只存URL4、MySQL三大范式是什么？实际开发会严格遵守吗？1NF：列不可再分；2NF：消除部分依赖；3NF：消除传递依赖。真实工作：不会完全死守三范式。范式优点是减少冗余、保证数据一致；缺点是多表关联多、查询慢。实际开发经常适当冗余字段，比如订单表冗余用户名、手机号，减少联表查询，提升性能，以性能优先。二、索引核心面试题（高频）1、InnoDB索引结构是什么？为什么不用哈希？InnoDB所有索引都是B+树结构。不用哈希的原因：哈希索引不支持范围查询（>、<、between、排序）哈希冲突会导致性能不稳定B+树叶子节点有序，适配数据库常用的排序、分页、范围查询场景2、聚簇索引和非聚簇索引区别聚簇索引（主键索引）叶子节点存的是整行数据一张表只有一个聚簇索引查询主键直接拿到数据，速度最快非聚簇索引（二级索引/普通索引）叶子节点存的是主键值通过普通索引查询，需要先查索引拿到主键，再回表查数据，也就是回表查询3、什么是覆盖索引？为什么要尽量用覆盖索引？如果一个索引包含了SQL查询需要的所有字段，就不需要回表，直接从索引取数据，这就是覆盖索引。优势：避免回表IO，大幅提升查询性能，是日常优化最常用的手段。示例：建立索引idx_name_age(name,age)，selectname,agefromuserwherename='xxx'，就是覆盖索引。4、最左前缀原则是什么？哪些情况会失效索引？联合索引遵循最左匹配，从最左边字段开始匹配，跳过第一个字段、中断匹配都会失效。索引失效常见场景（面试必背）联合索引不满足最左前缀索引列使用函数、运算、类型转换（like'%xxx'、or、isnotnull、!=）字段类型和传入参数类型不匹配，隐式转换导致失效MySQL优化器判断全表扫描比走索引更快，主动放弃索引（数据量少、区分度低）5、like查询怎么不走索引？怎么优化？like'%关键词'、like'%关键词%'：索引失效，全表扫描like'关键词%'：可以走索引优化方案前置匹配尽量用xxx%模糊全匹配需求，用ES替代MySQL，不要强行优化MySQL6、主键为什么建议用自增ID，不用UUID？自增ID：有序递增，B+树插入是尾部追加，不会页分裂、页合并，插入性能高，索引紧凑UUID：无序、随机，插入会导致频繁页分裂，索引碎片多，查询和插入性能差，且占用空间更大需要分布式唯一ID可以用雪花ID，替代UUID。三、事务与MVCC高频题1、事务四大特性ACID原子性(A)：事务要么全部成功，要么全部回滚一致性(C)：事务前后数据状态合法，约束不被破坏隔离性(I)：多个事务互不干扰持久性(D)：事务提交后数据永久落地，崩溃不丢失2、MySQL四大隔离级别？默认级别是什么？从低到高：读未提交、读已提交、可重复读、串行化MySQLInnoDB默认：可重复读（RR）各级别问题读未提交：存在脏读、不可重复读、幻读读已提交：解决脏读，存在不可重复读、幻读可重复读：解决脏读、不可重复读，存在幻读（InnoDB通过间隙锁大幅规避）串行化：完全串行，无并发，性能极低，基本不用3、脏读、不可重复读、幻读区别脏读：一个事务读到另一个事务未提交的脏数据不可重复读：同一事务内，两次查同一条数据，结果不一样（被其他事务修改提交）幻读：同一事务内，范围查询，前后数据条数不一致（其他事务新增/删除数据）4、什么是MVCC？原理是什么？MVCC即多版本并发控制，是InnoDB实现无锁读、提升并发性能的核心机制，读操作不加锁。核心原理每条数据隐藏三个字段：事务ID、undo日志指针、roll_pointer通过undo日志保存数据历史版本事务通过ReadView视图，判断当前数据版本是否可见RR级别下，MVCC保证同一事务多次读取数据版本一致，实现可重复读。5、什么是间隙锁、临键锁？解决什么问题？InnoDBRR级别下，为了解决幻读，引入间隙锁+临键锁。间隙锁：锁定索引间隙，禁止其他事务在间隙内插入数据临键锁：索引记录锁+间隙锁，是Next-KeyLock注意：等值查询命中唯一索引时，会降级为行锁；范围查询会触发临键锁，容易导致死锁和锁等待。四、锁机制面试题1、行锁和表锁区别？InnoDB是行锁还是表锁？InnoDB默认是行级锁，但行锁生效前提是走索引。走索引更新：行锁，只锁对应数据，并发高不走索引更新：行锁失效，降级为表锁，锁全表，并发直接拉胯MyISAM全程是表锁。2、共享锁和排他锁共享锁S：读锁，多个事务可以同时加读锁，互不冲突排他锁X：写锁，独占，加写锁后其他事务不能读也不能写普通select是快照读，不加锁；select...forupdate是当前读，加排他锁。3、死锁产生条件？如何避免？死锁四大必要条件：互斥、请求保持、不可剥夺、循环等待。项目中避免死锁的手段统一SQL加锁、更新的顺序减少事务粒度，事务尽量短，不做耗时操作避免一次性锁定过多资源设置超时时间，超时自动释放锁五、SQL优化实战（面试重点）1、Explain关键字重点字段有哪些？分别代表什么？日常只看核心5个字段即可：type：访问类型，优先级system>const>eq_ref>ref>range>index>all。最差是all全表扫描，生产必须优化掉key：实际命中的索引rows：扫描行数，越小越好Extra：Usingfilesort（文件排序，必须优化）、Usingtemporary（临时表，必须优化）、Usingindex（覆盖索引，优秀）possible_key：理论可用索引，仅参考2、Usingfilesort、Usingtemporary怎么优化？Usingfilesort：orderby没有走索引，出现文件排序。优化：让排序字段加入联合索引，利用索引有序特性免排序Usingtemporary：groupby、distinct无索引，生成临时表。优化：分组字段建索引3、分页查询深度偏移慢怎么优化？（limit100000,10）问题：偏移量越大，数据库需要扫描和跳过的数据越多，性能指数级下降。最优优化方案：主键分页（延迟关联）先通过索引查出主键，再关联查询数据，避免全表扫描偏移：select*fromtablewhereid>100000limit10;适合有序自增ID场景。如果是无序分页，业务层面禁止深度分页，限制最大页码。4、join查询优化要点小表驱动大表，小表当驱动表关联字段必须建立索引禁止三张以上表联查查询字段按需获取，禁止select*六、MySQL架构与运维问题1、MySQL逻辑架构三层架构：连接层>服务层（SQL解析、优化、缓存）>存储引擎层注意：MySQL8.0已经废弃查询缓存，不用再考虑缓存优化。2、redolog和undolog作用redolog（重做日志）保证事务持久性。数据先写日志，再刷磁盘，宕机后通过redolog恢复已提交事务数据。undolog（回滚日志）保证原子性+MVCC。保存数据修改前版本，用于事务回滚和多版本读。3、主从复制原理核心三步：主库写操作记录到binlog日志从库IO线程拉取主库binlog，写入本地relay日志从库SQL线程重放relay日志，同步数据常见问题：主从延迟、数据不一致。解决思路：减少大事务、拆分SQL、优化从库参数、避免从库慢查询。4、读写分离思路主库负责：新增、修改、删除（写操作）从库负责：查询（读操作）规避主库读写压力过大问题，提升整体吞吐量，缺点是存在短暂读延迟，核心业务需要读主库。七、真实面试情景题1、线上SQL突然变慢，你怎么排查？我的排查流程（工作真实流程）：先看慢查询日志，定位慢SQLExplain分析执行计划，看是否全表扫描、索引失效、文件排序检查索引是否缺失、是否失效（隐式转换、or、like前缀模糊）查看是否有锁等待、长事务阻塞看表数据量是否暴增，旧数据是否未归档针对性加索引、优化SQL、拆分大事务、归档数据2