MySQL高频面试题及真实作答（无模板、贴合职场）

MySQL高频面试题及真实作答（无模板、贴合职场）一、MySQL基础语法与概念1、MySQL常用存储引擎有哪些？区别是什么？日常工作中最常用的是InnoDB和MyISAM，还有Memory、Archive等小众引擎。核心区别：InnoDB：MySQL5.5+默认引擎。支持事务、行锁、外键、MVCC，崩溃可恢复，读写性能均衡，适合绝大多数业务表。缺点是占用空间稍大。MyISAM：老版本默认引擎。不支持事务和行锁，只有表锁，不支持外键。查询速度快、占用空间小，但是崩溃容易丢数据，现在基本只用于纯静态、极少修改的日志统计表。Memory：数据存在内存中，重启数据清空，速度极快，适合做临时表、缓存中间数据。2、CHAR和VARCHAR的区别？适用场景？CHAR是固定长度，VARCHAR是可变长度。CHAR：定义多长就占多长空间，尾部空格会截断。存取速度快，适合长度固定的数据，比如手机号、身份证号、状态码。VARCHAR：根据实际数据长度占用空间，会多占用1-2个字节存长度信息。节省存储空间，适合用户名、地址、备注等长短不一的数据。另外VARCHAR(50)代表最多存50个字符，不是字节，中英文都按字符计算。3、TEXT、BLOB适用场景？和VARCHAR区别？VARCHAR适合短文本，最大存储长度有限；TEXT专门存长文本，BLOB存二进制文件，比如图片、文件流。工作中尽量少用大字段，查询时会增加IO开销，拖慢SQL性能，长文本、文件一般存对象存储，数据库只存链接地址。4、MySQL约束有哪些？分别作用？主键（PRIMARYKEY）：唯一且非空，一张表只能有一个主键唯一约束（UNIQUE）：数据唯一，允许为空（可多个null）非空约束（NOTNULL）：字段不能为null默认约束（DEFAULT）：字段不传值时使用默认值外键（FOREIGNKEY）：保证表与表之间数据一致性，项目中一般业务代码控制关联逻辑，很少用外键，避免锁表和性能问题二、索引核心面试题（高频）1、什么是索引？索引的优缺点？索引是数据库为字段建立的有序数据结构，用来快速检索数据，相当于书籍的目录。优点：大幅提升查询、排序、分组速度，减少数据库扫描行数。缺点：占用磁盘空间；新增、修改、删除数据时，需要同步更新索引，会降低写入性能。所以不是所有字段都适合建索引。2、InnoDB索引结构是什么？为什么不用哈希？InnoDB默认索引结构是B+树。不用哈希的原因：哈希索引只能精准匹配=查询，不支持范围查询、排序、模糊查询；且哈希有冲突问题，不适合海量数据场景。B+树优势：所有数据都在叶子节点，层级少、查询稳定；支持范围查询、排序、分页；磁盘IO次数少，读写性能更稳。3、聚簇索引和非聚簇索引区别？聚簇索引：主键就是聚簇索引，叶子节点直接存储整行数据。一张表只有一个聚簇索引，也是查询效率最高的索引。非聚簇索引（二级索引）：普通索引、唯一索引都属于这类，叶子节点只存储索引字段+主键值，查到数据后需要回表查询整行数据。4、什么是回表？什么是覆盖索引？回表：通过普通索引查到主键后，再通过主键索引查询完整数据，多一次索引扫描，会消耗性能。覆盖索引：查询的所有字段都包含在索引中，不需要回表，直接从二级索引就能拿到全部数据，是日常优化高频手段。示例：建立索引(name,age)，查询selectname,agefromuserwherename='xxx'就是覆盖索引。5、最左前缀原则是什么？联合索引遵循最左匹配规则，查询条件需要匹配索引的最左前列字段，索引才会生效。比如联合索引(a,b,c)：wherea=1、wherea=1andb=2、wherea=1andb=2andc=3：索引生效whereb=2、wherec=3、whereb=2andc=3：索引失效日常建联合索引，把高频查询、等值查询字段放前面。6、哪些情况会导致索引失效？联合索引不满足最左前缀索引字段使用函数、运算、类型转换，比如whereleft(name,2)='xx'、whereage+1=18使用!=、<>、notin、notlikelike以%开头，比如%张三or连接的字段有未建索引的字段数据库判断全表扫描比索引更快时，会放弃索引（数据量少、区分度低）三、事务与MVCC（必问）1、事务四大特性ACID？原子性（A）：事务要么全部成功，要么全部回滚，不会出现半截状态一致性（C）：事务执行前后，数据整体状态合法、约束不变隔离性（I）：多个事务并发执行互不干扰持久性（D）：事务提交后，数据永久保存，崩溃不丢失2、MySQL四种事务隔离级别？默认级别？从低到高依次为：读未提交、读已提交、可重复读、串行化。读未提交：能读到其他事务未提交数据，脏读、幻读、不可重复读都存在，基本不用读已提交（RC）：只能读已提交数据，解决脏读，存在不可重复读、幻读可重复读（RR）：MySQLInnoDB默认级别。解决脏读、不可重复读，存在幻读，通过MVCC极大降低幻读影响串行化：最高级别，事务串行执行，无并发问题，性能极差，基本不用3、脏读、不可重复读、幻读区别？脏读：一个事务读到另一个事务未提交的数据不可重复读：同一个事务内，两次查询同一数据，结果不一致（其他事务修改并提交）幻读：同一个事务内，范围查询，前后数据条数不一致（其他事务新增/删除数据）4、什么是MVCC？原理是什么？MVCC即多版本并发控制，是InnoDB实现事务隔离、提升并发性能的核心机制，不加锁实现读写并发。核心原理：每条数据行都隐藏三个字段：事务ID、undo日志指针、roll_pointer通过undo日志保存数据历史版本通过ReadView视图，控制当前事务能看到哪个版本的数据简单说：写操作生成新数据版本，读操作读取历史快照版本，读写不冲突，大幅提升并发性能。四、锁机制面试题1、行锁和表锁的区别？表锁：锁住整张表，开销小、加锁快，并发差，MyISAM全程表锁行锁：InnoDB支持，只锁住操作的行，并发度高，加锁开销大。行锁必须走索引，不走索引会降级为表锁2、意向锁作用？意向锁是表级辅助锁，分为意向共享锁、意向排他锁。作用是快速判断整张表是否有行锁，避免逐行遍历校验，提升加锁效率，用户无需手动操作，数据库自动维护。3、死锁产生条件？如何避免？死锁四大条件：互斥、请求保持、不可剥夺、循环等待。避免方案：统一业务中表的操作顺序，不要交叉更新缩短事务执行时间，避免长事务合理加索引，保证锁粒度为行锁设置超时时间，超时自动释放锁五、SQL优化与实战1、慢查询怎么排查？工作中标准排查流程：开启慢查询日志，设置阈值（一般1秒）通过慢日志定位耗时SQL使用explain分析执行计划，查看是否走索引、扫描行数、是否临时表、文件排序针对性优化索引、改写SQL、拆分大SQL2、explain重点字段有哪些？type：核心字段，性能从优到劣：system>const>eq_ref>ref>range>index>all，日常至少保证range、ref级别，避免all全表扫描key：实际使用的索引rows：预估扫描行数，越少越好Extra：出现Usingfilesort（文件排序）、Usingtemporary（临时表）需要重点优化3、limit大分页怎么优化？普通limit100000,10会先扫描十万行再丢弃，性能极差。优化方案：主键分页：whereid>100000limit10子查询优化：先查主键，再关联查询数据业务层面限制，不允许深度分页，前端做页码限制4、count(*)、count(1)、count(字段)区别？count(*)：统计所有行数，包含null，数据库做过优化，效率最高count(1)：和count(*)性能基本一致count(字段)：只统计字段非空数据，会扫描字段判断null，性能最差日常统计行数优先用count(*)。六、高阶运维与架构问题1、redolog和undolog作用？redolog（重做日志）：保证事务持久性。数据先写日志再落盘，数据库崩溃重启后，通过redolog恢复已提交事务数据，解决崩溃丢数据问题。undolog（回滚日志）：保证事务原子性和MVCC。保存数据修改前的版本，事务回滚时用来恢复数据，同时为读快照提供历史数据版本。2、MySQL主从复制原理？核心是binlog日志同步，分为三步：主库执行增删改操作，记录二进制日志binlog从库IO线程拉取主库binlog，写入本地中继日志从库SQL线程重放中继日志，同步主库数据主从主要作用：读写分离、数据备份、故障切换。3、主从延迟原因及解决办法？常见原因：主库写入量大、大事务、从库单线程回放日志、网络延迟、从库硬件