MySQL数据库面试题完整版目录1. 三大范式2. 数据存储类型2.1 varchar与char的区别2.2 blob和text有什么区别2.3 DATETIME和TIMESTAMP的异同2.4 IN和EXISTS区别3. Select语句完整的执行顺序4. MySQL事务4.1 事务的基本要素ACID4.2 ACID靠什么保证4.3 事务的并发问题4.4 MySQL事务隔离级别4.5 MVCC4.5.1 MVCC是什么4.5.2 MVCC组成4.5.3 MVCC工作原理4.5.4 MVCC解决什么问题5. MySQL索引5.1 索引的定义、类型及使用5.2 创建索引的注意点5.3 索引失效的情况5.4 建立索引的时机5.5 MySQL索引数据结构5.5.1 聚簇索引和非聚簇索引5.6 B树和B树的区别5.7 为什么MySQL索引常用B树5.8 索引数据结构选择依据5.9 MyISAM和InnoDB的区别5.10 最左前缀原则/最左匹配原则6. MySQL锁6.1 乐观锁和悲观锁6.2 死锁问题及排查6.3 死锁解决办法7. MySQL日志8. MySQL高可用/高性能8.1 MySQL高可用方案8.2 主从同步延迟处理9. SQL优化9.1 慢SQL定位与排查9.2 MySQL优化原则10. 实际工作问题10.1 百万级数据删除10.2 大表添加字段10.3 MySQL CPU飙升处理11. 常见面试SQL1. 三大范式第一范式数据表中的每一列每个字段都不可以再拆分。第二范式在第一范式的基础上非主键列完全依赖于主键而不能依赖于主键的一部分。例如订单表里存储商品信息时需将商品ID和订单ID作为联合主键。第三范式在满足第二范式的基础上表中的非主键只依赖于主键而不依赖于其他非主键。例如订单表不能存储用户姓名、地址等用户信息。2. 数据存储类型2.1 varchar与char的区别类型长度特性存储方式性能特点空间利用最大字符数char定长插入数据长度小于设定长度时用空格填充-存取速度快比varchar快约50%可能占多余空间空间换时间255varchar可变长-按插入数据实际长度存储存取速度慢不占多余空间时间换空间655322.2 blob和text有什么区别对比维度BLOBTEXT用途存储二进制数据图片、音频、视频二进制编码存储大字符串数据文章内容、长描述字符集无字符集按字节存储无编码问题有字符集需进行字符编码处理排序/比较依据基于二进制字节值根据字符集校对规则2.3 DATETIME和TIMESTAMP的异同对比维度DATETIMETIMESTAMP表现格式YYYY-MM-DD HH:MM:SSYYYY-MM-DD HH:MM:SS数据组成包含日期时间包含日期时间微秒支持支持秒后6位小数秒支持秒后6位小数秒存储范围1000-01-01 00:00:00 至 9999-12-31 23:59:591970-01-01 00:00:01 UTC 至 2038-01-19 03:14:07 UTC存储空间8字节4字节无微秒/5-8字节有微秒时区影响不受时区影响存储读取值不变受时区影响存储转UTC读取转当前时区默认值无默认值默认当前时间CURRENT_TIMESTAMP2.4 IN和EXISTS区别对比项INEXISTS实现方式外表和内表作hash连接外表loop循环每次循环查询内表效率比较两表相当差别不大子查询表小用IN两表相当差别不大子查询表大用EXISTSNOT IN/NOT EXISTSNOT IN全表扫描不使用索引NOT EXISTS能用到索引通常比NOT IN快3. Select语句完整的执行顺序SELECT语句完整执行顺序FROM-JOIN-WHERE-GROUP BY-HAVING-SELECT-DISTINCT-ORDER BY-LIMIT/OFFSET分步说明FROM确定查询数据来源表JOIN多表时按条件连接表WHERE筛选符合条件的行GROUP BY按指定列分组HAVING筛选分组后的结果SELECT指定返回的列DISTINCT去除重复行ORDER BY对结果集排序LIMIT/OFFSET限制返回行数和偏移量4. MySQL事务4.1 事务的基本要素ACID原子性Atomicity事务操作要么全部成功要么全部失败回滚一致性Consistency事务执行前后数据库数据保持一致状态隔离性Isolation多个事务并发执行时互不干扰持久性Durability事务提交后数据修改永久生效4.2 ACID靠什么保证A原子性undo log日志记录回滚信息事务回滚时撤销已执行的SQLC一致性由其他三大特性业务代码保证I隔离性锁机制共享锁、排他锁 MVCC多版本并发控制D持久性内存redo log修改数据时同时记录到内存和redo log宕机可恢复4.3 事务的并发问题问题描述解决方式脏读读取到另一个未提交事务修改的数据1. 隔离级别设为读已提交2. 读数据加共享锁3. MVCC读取已提交版本不可重复读同一事务两次读取同一数据结果不一致1. 隔离级别提升到可重复读2. MVCC保证事务内数据一致性3. 共享锁至事务结束幻读同一条件查询结果集因其他事务增删记录而变化1. 隔离级别设为串行化2. InnoDB可重复读级别用间隙锁3. 应用层乐观锁丢失更新两个事务同时更新一个覆盖另一个结果1. 更新前加排他锁2. 乐观锁版本号/时间戳检查后更新4.4 MySQL事务隔离级别隔离级别含义解决的并发问题仍存在的问题读未提交可读取未提交事务的数据无脏读、不可重复读、幻读读已提交只能读取已提交事务的数据脏读不可重复读、幻读可重复读同一事务多次读取数据结果一致脏读、不可重复读幻读InnoDB间隙锁缓解串行化事务依次顺序执行脏读、不可重复读、幻读性能差锁竞争多4.5 MVCC4.5.1 MVCC是什么MVCC多版本并发控制是数据库的版本控制机制通过维护数据多个版本支持不同事务并发执行解决脏读、幻读、不可重复读问题。4.5.2 MVCC组成数据版本新版本、旧版本事务ID每个事务唯一ID回滚段存储数据旧版本4.5.3 MVCC工作原理读未提交MVCC无作用读已提交事务开始创建一致性视图查询取最新已提交版本避免脏读但存在不可重复读/幻读可重复读事务开始创建视图且全程不变解决脏读、不可重复读幻读通过MVCC间隙锁缓解串行化MVCC无作用4.5.4 MVCC解决什么问题解决脏读、不可重复读、幻读读写可同时进行提高并发量减少锁竞争读不加锁读写不冲突保证事务隔离性5. MySQL索引5.1 索引的定义、类型及使用定义索引是提高查询速度的数据结构类型主键索引唯一非空每个表仅一个唯一索引值唯一可含NULL表可多个普通索引无限制基础索引类型全文索引用于文本数据全文搜索组合索引多列组合遵循最左前缀原则5.2 创建索引的注意点建在查询频繁的字段上索引数量适量占用空间更新需维护频繁更新的值不做主键/索引小表不建索引离散度低的字段如性别不建索引5.3 索引失效的情况查询条件含or、like、内置函数、运算/-/*/、!/、not in、is null/is not null字符串字段查询未加引号隐式类型转换联合索引不遵循最左前缀原则左右连接时关联字段编码不一致5.4 建立索引的时机表数据量大查询慢多表关联的字段GROUP BY/ORDER BY的字段5.5 MySQL索引数据结构B树B树InnoDB/MyISAM默认哈希HashMemory引擎5.5.1 聚簇索引和非聚簇索引聚簇索引InnoDBB树叶子节点包含完整数据行非聚簇索引MyISAMB树叶子节点存储索引键值主键值查询需回表5.6 B树和B树的区别B树平衡多路搜索树键值分布在整棵树非叶子节点可能找到目标B树B树变形数据仅存于叶子节点非叶子节点仅索引叶子节点通过指针形成有序链表5.7 为什么MySQL索引常用B树磁盘I/O少非叶子节点仅存索引节点存储更多索引树高度低范围查询优叶子节点链表相连便于范围查询查询稳定每次查询都到叶子节点效率稳定红黑树劣势数据量大时树高磁盘I/O多且有变色旋转开销5.8 索引数据结构选择依据精确查找为主哈希索引存储引擎支持时范围查询/排序多B树索引通用场景B树MySQL默认5.9 MyISAM和InnoDB的区别对比维度MyISAMInnoDB事务支持不支持支持ACID外键支持不支持支持索引存储索引与数据分离索引指向物理位置聚簇索引存数据辅助索引指向主键锁机制表级锁并发写差行级锁表级锁并发好崩溃恢复恢复困难易丢数据日志崩溃恢复机制自动恢复存储统计保存总行数查询快不精确需实时计算适用场景读多写少无事务/外键需求写多需数据完整性和高并发5.10 最左前缀原则/最左匹配原则InnoDB联合索引中需匹配前一个/左边的值才能匹配下一个。如创建组合索引(a1,a2,a3)等价于创建(a1)(a1,a2)(a1,a2,a3)6. MySQL锁6.1 乐观锁和悲观锁悲观锁认为数据随时会被修改事务拿到锁后其他事务无法修改只能等待数据库行锁、表锁、读锁、写锁均为悲观锁乐观锁认为数据变动不频繁通过版本号version或时间戳timestamp实现版本号为最常用方式6.2 死锁问题及排查行锁争用导致的死锁原因事务持有锁时间过长行锁争用激烈解决思路减少事务持有锁时间移出不必要操作缩短执行时间示例-- 事务1STARTTRANSACTION;UPDATEusersSETage30WHEREid1;-- 对id1加排他锁-- 事务2STARTTRANSACTION;SELECT*FROMusersWHEREid2FORUPDATE;-- 对id2加排他锁UPDATEusersSETage25WHEREid1;-- 等待事务1释放锁-- 事务1UPDATEusersSETnameJohnWHEREid2;-- 等待事务2释放锁形成死锁事务顺序不一致导致的死锁原因不同事务操作表的顺序不一致解决思路统一事务操作表的顺序示例-- 事务1STARTTRANSACTION;UPDATEtable1SETname1AWHEREid11;UPDATEtable2SETname2XWHEREid21;-- 事务2STARTTRANSACTION;UPDATEtable2SETname2YWHEREid21;UPDATEtable1SETname1BWHEREid11;-- 顺序相反易死锁死锁排查步骤查看死锁日志show engine innodb status;找出死锁SQL分析SQL加锁情况模拟死锁场景分析死锁日志得出解决方案6.3 死锁解决办法统一操作顺序所有事务对表的操作顺序一致如先操作questions→answers→feedback拆分事务大事务拆分为小事务减少锁持有时间设置超时时间通过innodb_lock_wait_timeout参数设置锁等待阈值超时自动回滚7. MySQL日志错误日志error log记录启动、运行、关闭过程定位MySQL问题慢查询日志slow query log记录执行时间超过long_query_time的SQL用于优化一般查询日志general log记录所有数据库请求无论是否执行成功二进制日志bin log记录DDL/DML语句不含select/show事件形式存储InnoDB特有日志重做日志redo log记录事务日志保证持久性回滚日志undo log记录数据回滚信息保证原子性8. MySQL高可用/高性能8.1 MySQL高可用方案读写分离原理将读写操作分散到不同节点搭建主从集群一主一从/一主多从主机负责读写操作从机仅负责读操作主机通过复制同步数据到从机业务端写操作发主机读操作发从机实现方式程序代码封装抽象数据访问层实现读写分离和连接管理分库分表类型说明垂直分库按业务归属将不同表拆分到不同库水平分库按字段策略hash/range将一个库数据拆分到多个库垂直分表按字段活跃性将字段拆到主表/扩展表水平分表按字段策略hash/range将一个表数据拆分到多个表水平分表路由方式范围路由按有序列整形/时间戳分段Hash路由字段Hash运算后分散常用中间件sharding-jdbcMycat分库分表问题事务问题需分布式事务跨库JOIN业务代码中关联查询主从复制过程master写入数据更新binlogmaster创建dump线程向slave推送binlogslave创建IO线程接收binlog写入relay logslave创建SQL线程读取relay log并执行完成同步slave记录自身binlog8.2 主从同步延迟处理原因从库仅一个线程读取binlog主库大并发更新导致SQL积压解决办法写操作后的读操作指定主库读从机失败后重试主机关键业务读写走主机非关键业务读写分离9. SQL优化9.1 慢SQL定位与排查发现慢SQL开启慢查询日志# my.cnf/my.ini配置 slow_query_log 1 slow_query_log_file /var/log/mysql/mysql-slow.log long_query_time 2 # 阈值2秒重启MySQL生效SHOW PROCESSLIST;查看Time列值大的为慢SQL分析慢SQLEXPLAIN分析执行计划关注typeALL全表扫描差、possible_keys、key索引检查验证过滤/排序/连接字段是否有索引示例CREATEINDEXidx_ageONusers(age);锁等待查看查询information_schema.innodb_trx/innodb_locksSQL优化减少复杂子查询/连接添加过滤条件工具辅助pt-query-digest分析慢查询日志pt-query-digest /var/log/mysql/mysql-slow.log9.2 MySQL优化原则分析表适当建立索引单表数据不超200W适时分表用explain分析慢查询FROM子句后表为基础表选记录少的表尽量缩小子查询结果10. 实际工作问题10.1 百万级数据删除删除索引删除无用数据重新创建索引10.2 大表添加字段方案1中间表转换创建临时表复制旧表结构添加新字段复制旧表数据到临时表删除旧表临时表重命名为旧表名缺点可能丢失部分数据方案2主从切换先在从库添加字段主从切换其他节点添加字段10.3 MySQL CPU飙升处理排查步骤top命令确认是否mysqld导致show processlist查看消耗资源的SQL分析SQL执行计划、索引、数据量处理方式kill消耗高的线程观察CPU是否下降调整加索引、改SQL、调内存参数重新执行SQL11. 常见面试SQL例1查询每门课都大于80分的学生姓名数据表namekechengfenshu张三语文81张三数学75李四语文76李四数学90王五语文81王五数学100王五英语90解法1SELECTDISTINCTnameFROMtableWHEREnameNOTIN(SELECTDISTINCTnameFROMtableWHEREfenshu80);解法2SELECTnameFROMtableGROUPBYnameHAVINGMIN(fenshu)80;例2删除学生冗余信息数据表自动编号学号姓名课程编号课程名称分数12005001张三0001数学6922005002李四0001数学8932005001张三0001数学61SQLDELETEtablenameWHERE自动编号NOTIN(SELECTMIN(自动编号)FROMtablenameGROUPBY学号,姓名,课程编号,课程名称,分数);例3查询球队所有比赛组合数据表teamname字段a,b,c,dSQLSELECTa.name,b.nameFROMteam a,team bWHEREa.nameb.name;例4行转列查询原表yearmonthamount199111.1199121.2199131.3199141.4199212.1199222.2199232.3199242.4目标结果yearm1m2m3m419911.11.21.31.419922.12.22.32.4SQLSELECTyear,(SELECTamountFROMaaa mWHEREmonth1ANDm.yearaaa.year)ASm1,(SELECTamountFROMaaa mWHEREmonth2ANDm.yearaaa.year)ASm2,(SELECTamountFROMaaa mWHEREmonth3ANDm.yearaaa.year)ASm3,(SELECTamountFROMaaa mWHEREmonth4ANDm.yearaaa.year)ASm4FROMaaaGROUPBYyear;例5复制表结构源表a新表bMySQLCREATETABLEbASSELECT*FROMaWHERE12;SQL ServerSELECT*INTObFROMaWHERE11;-- 11复制结构数据例6添加及格标记原表courseidcoursenamescore1java702oracle903xml404jsp305servlet80目标结果courseidcoursenamescoremark1java70pass2oracle90pass3xml40fail4jsp30fail5servlet80passSQLSELECTcourseid,coursename,score,IF(score60,pass,fail)ASmarkFROMcourse;例7查询购入2种以上商品的购物人数据表购物人商品名称数量A甲2B乙4C丙1A丁2B丙5SQLSELECT*FROM购物信息WHERE购物人IN(SELECT购物人FROM购物信息GROUPBY购物人HAVINGCOUNT(*)2);例8统计每日胜负次数数据表dateresult2005-05-09win2005-05-09lose2005-05-09lose2005-05-09lose2005-05-10win2005-05-10lose2005-05-10lose目标结果datewinlose2005-05-09132005-05-1012解法1SELECTdate,SUM(CASEWHENresultwinTHEN1ELSE0END)ASwin,SUM(CASEWHENresultloseTHEN1ELSE0END)ASloseFROMinfoGROUPBYdate;解法2SELECTa.date,a.resultASwin,b.resultASloseFROM(SELECTdate,COUNT(result)ASresultFROMinfoWHEREresultwinGROUPBYdate)ASaJOIN(SELECTdate,COUNT(result)ASresultFROMinfoWHEREresultloseGROUPBYdate)ASbONa.dateb.date;例9联合索引生效判断联合索引(a,b,c)WHERE a1 AND b1 AND c1→ 生效WHERE a1 AND c1→ 生效a匹配最左前缀WHERE b1 AND c1→ 失效无aWHERE b1 AND a1 AND c1→ 生效顺序不影响### 总结 1. 文档完整整合了所有MySQL面试题内容修正了原始格式错误如表格排版、代码缩进、标点符号补充了缺失的逻辑内容 2. 按照目录层级结构化梳理每个知识点独立成节关键内容用表格/代码块/列表呈现可读性大幅提升 3. 保留了所有核心考点三大范式、事务、索引、锁、优化、面试SQL等并补充了完整的SQL示例和配置示例可直接用于复习和面试准备。